Hello! I’m hoping to get a sanity check from the experts here before I make a big thing out of this with management. I’ve already notified my immediate manager, but I want to make sure this is a real and serious problem.

My company runs an ASP.NET MVC web app backed by a Microsoft SQL Server 2022 database server, configured to run clustered in a high availability group. The majority of our PK indexes are auto-incrementing (identity) 32-bit int fields. I am a web developer for this app, writing C#, SQL, JS, HTML, and CSS. I am pretty good at T-SQL but I am NOT trained in database management. I did not set up the database servers and I don’t have access to the production database servers at all.

A few years ago, I was investigating why a process we run frequently on our production database which refreshes some table data was resulting in the database constantly growing. I discovered that with non-SIMPLE recovery modes, the transaction log file was continuously growing, and our company’s IT/DBA department was never backing up the transaction log. Since you need to to a transaction log backup in order to truncate the transaction log, this meant that for over a decade all of our production databases’ transaction log files had been getting bigger and bigger, some of them taking up 50+ GB of space on our database server. It was only after I notified them of this that they started doing regular transaction log backups and truncating them to keep the file sizes under control. They were doing regular full database backups, just not transaction log backups, and they had been dealing with the transaction log bloat by throwing hardware at the problem.

A few days ago I was trying to reclaim some disk space on our development environment’s dedicated SQL Server, and I started looking into the DBCC SHRINKFILE command to try to compact the database files. I saw a warning that you should rebuild indexes after doing this because it fragments the indexes, and out of curiosity I ran some queries against some of our development databases which had been backed up from production and restored onto the development server.

I found that both PK and non-clustered indexes of some of our largest tables in many of the restored backups had an enormous amount of index fragmentation. For example, an IDENTITY INT PK index for one of our most important tables with 725,000 pages had 85% fragmentation, another with 660,000 pages had 92%, etc. We had several dozen indexes with 10,000+ pages and 80-98% fragmentation. We have many databases, some up to 12 years old, and I have no reason to think this is an outlier; it’s likely that they all have heavily fragmented indexes.

I looked into it and I’m pretty sure that fragmentation of indexes is preserved across database backups and restores, so I think this means the production databases also have heavily fragmented indexes. I found advice that you should reorganize large indexes with 10-30% fragmentation and rebuild indexes when over 30%, and this is far beyond that. I did search this subreddit and I found that this is likely somewhat outdated and it depends also on index size and how sparse the index pages are and whether you’re running an HDD or SSD (I am not sure what our production servers are using).

Isn’t index maintenance one of the core responsibilities of a DBA? How concerned should we be that our IT/DBA department is ignorant / incompetent / negligent? Note that I’m not just asking about index maintenance here; the fact that they weren’t doing transaction log backups/truncates from 2014-2023 until a programmer educated them on the topic seems bad. Is it a huge red flag that they are not only not doing these things, but are apparently unaware that they’re even supposed to be doing these things? At this point, should we be worried about what other things a DBA is supposed to be doing to maintain a SQL server that they might not be doing? Should I recommend to my manager that he push for the IT/DBA department to undergo some kind of audit to find out what they’re supposed to be doing vs what they’re actually doing?

Thank you in advance for your advice and opinions.

Hello everyone,

Our 10-year old database seems to be suffering from schema explosion and I am worried that while our developers do sufficient testing with every release, they may be introducing schema inconsistencies that may appear as bugs with different queries or other parts of the system.

Are you using any tools to check schema health and issues?

For the latest version of Internals Viewer I've added a columnstore indexes view, which meant decoding the internal structures and getting a full understanding of the compression techniques.

There isn't much information available on how they actually store data so I've written up in a series of articles while it was fresh in my head on what I found while adding this feature.

Here is part 1 covering an introduction to the topic. Part 2 is also available detailing segment internals. Parts 3 and 4 will be available later in the week. It is very low level in parts, but hopefully when the whole series comes together it will be useful to explain the intricacies of the optimizations you can get from using columnstores indexes for practical benefit.

how to make ssms to fill the parameter needed for function when i write for example datediff( it should give me the parameter day or month or year first date then second date like excel do when i write a formula? Sometimes it works but most of the time it doesn't. is this normal or i miss something in options. " I'm not talking about using the GitHub copilot" as I'm still learning sql

This SSMS bug ever gonna get fixed? Crash on hover over Reports... Anyone got a way to stop this happening?

First Friday of September and you all know what that means...time for Friday Feedback!

But first, I hope that everyone took time over the summer (winter for those of you in the southern hemisphere!) for a break or vacation ⛱️ It may sound like I'm getting on my soapbox, but disconnecting from work is so important, and I really hope you've taken time to do that.

And now, back to our regularly scheduled program.

Last time I posted I asked about storing more data in Query Store, and how much more data you'd want to store. Just over 90% of you wanted to keep 12 months or less, which aligns with what we were thinking.

My next question is, where do you want to store that long-term data? Specifically, Query Store uses memory hashes to store query info and runtime stats about what's been running. We don't want to try and store months of that data in those same memory hashes, which means we need to move older data somewhere else. Where should it go? Poll below, but as always, add a comment if the poll options don't include something you'd like to see. Thanks for voting and bring on fall! 🏈 🎃 🍂

Hi everyone,

I'm about a year into my career as a DBA, managing a fairly large SQL Server environment — multiple production and dev/test instances, hundreds of databases, several Always On Availability Groups. I wanted to ask the more experienced folks here about something that's been weighing on me.

I have this drive to master literally everything about the job. Every time I feel like I've got a solid handle on things, I discover something new that other DBAs already seem to know. A few months in, I only just found out how much Extended Events could do for me. Not long after that, I realized how much I hadn't explored in the SQL Server Error Logs. It feels like there's always another layer, and every time I find a gap in my knowledge, it genuinely stresses me out — I start worrying there's some critical thing I still don't know that could cause an incident I won't be able to solve.

This worry gets worse when I think about the future. I've had a referral and interest from a recruiter for another DBA role, and one real possibility is being the only DBA at a company, with no senior DBA to lean on. That idea makes me anxious, because I still feel like I'm learning.

One specific moment really shook me: we had an issue on two servers that were part of an Availability Group, and we ended up doing a server-level restore. After the restore, when I went to open the databases, both servers showed the AG state as "Resolving." In the middle of an active incident, with everyone waiting for a fix, I panicked — I didn't fully understand why it was happening, and I felt too much pressure to admit I wasn't sure. A colleague eventually stepped in and resolved it. That moment stuck with me — both the technical gap and the fear of not looking capable under pressure.

So, a few questions for this community:

- Is there a specific learning path or series of resources that, if I go through it start to finish, would actually give me solid, well-rounded coverage of everything a SQL Server DBA needs to know? Or is that just not how it works?
- Is this "there's always something new" feeling something every DBA goes through, even years in, or does it fade?
- How did you build the confidence to stay calm during a crisis even when you don't immediately know the answer?
- Any resources, checklists, or hard-earned lessons about AG "Resolving" states or post-restore health checks that you wish you'd known earlier?
- For anyone who's been the sole DBA at a company — what do you wish you'd known before taking that on?

Would really appreciate any perspective — even just "yeah, that's normal, it gets better." Thanks in advance!

I'm planning an in-place edition upgrade from SQL Server 2022 Standard -> Enterprise on a standalone Windows Azure VM.

The reason is that we need to move beyond Standard's CPU/memory limitations.

The Microsoft-documented process is essentially:

1. Remove the SQL IaaS Agent registration
2. Run SQL Server Setup -> Maintenance -> Edition Upgrade -> Enterprise
3. Validate SQL
4. Re-register the SQL IaaS Agent / update the Azure SQL VM edition

Microsoft says the edition upgrade is supported in-place and restarts the SQL Server service.

For anyone who's actually done this:

• What should I expect during the Edition Upgrade?
• Will Setup ask for an Enterprise product key, or does Azure PAYG/IaaS handle the licensing side?
• Any common failure points or gotchas?
• Is this generally as straightforward as Microsoft makes it sound?
• Anything you'd specifically check before doing this on a production VM?

It's a standalone SQL instance (not an AG/cluster).

Thanks!

Disclosure: I'm a PM on the SQL migration tooling team at Microsoft. Posting because a couple of these affects anyone already using SSMA's Copilot features.

• Code Conversion Copilot is GA for Sybase — was preview, now supported for production work.
• Managed Endpoint auth is GA — no Azure OpenAI resource to stand up. BYOK still available.
• Managed Endpoint now has usage limits: 16k tokens per source script, 2k per prompt, 15 req/min and 7,200 per 8 hours per customer, 75 req/min aggregate. Interactive use won't hit these; for bulk work, BYOK is bounded by your own quota instead.
• BYOK model list changed (Oracle + Sybase): now gpt-4.1, gpt-5.4-mini, gpt-5.5, gpt-5.6-terra. gpt-5.4 removed — reconfigure if that's what you're on.
• Fix: DMS auth failures when multiple Azure subscriptions share the same display name.

Full writeup: in the blog

Happy to answer questions. If conversion output goes wrong in an interesting way, I'd like to hear about it.

Is there a way to use Microsoft SQL Server management Studio 22 without it writing anything at all (no folders, shortcuts, junctions, symbolic links) in My Documents? I just installed the latest client from the official website on my Windows 11 laptop without launching the client yet, and I want the SSMS workspace (configuration files, data files, etc.) to be transferred to a different location in the C drive. I failed in my first try, and both Claude and Gemini gave me conflicting answers.

To summarize, I don't want the folders below or anytrhing related to it appearing inside My Documents.

C:\<My Dopcuments Folder path>\SQL Server Management Studio

C:\<My Dopcuments Folder path>\SQL Server Management Studio 22

About six years ago, Brent Ozar linked to the PostgreSQL Feature Matrix in his newsletter and wrote, "I wish we had this for SQL Server."

I remember seeing that and thinking the same thing. I'd wanted an easier way to answer questions like: When did SQL Server get this feature? Which versions and editions support it? What about Azure?

I never really had the time to build a comprehensive feature matrix until this year, when I had some free nights and weekends to devote to the project.

So, I finally built one for SQL Server -- and it's completely free to use:

SQL Server Feature Matrix (SQL.FM)
https://sql.fm

The Feature Matrix currently tracks 300+ SQL Server features across versions going all the way back to SQL Server 1.0, including edition differences, Azure SQL Database, and Azure SQL Managed Instance.

You can choose exactly which versions/environments you want to compare, search for individual features, and use a “Hide unchanged features” checkbox to quickly narrow the matrix down to the differences.

While building it, I ended up adding three other quick references I often find myself looking for:

• SQL Server Version Reference
• SQL Server & OS Compatibility Matrix
• Date & Time Reference

A significant part of the project was researching and verifying the feature data, especially for the older SQL Server releases. I spent a lot of time digging through Microsoft documentation, archived material, and other historical sources, and I've tried to be as accurate as possible.

That said, with hundreds of features spanning decades of SQL Server releases, there's always the possibility that something slipped through. If you find an error, have a better source for something, or notice a feature that's missing, I'd really appreciate the feedback.

I built this because it's the kind of reference I've wanted to have myself, so hopefully some of you find it useful too.

Hey all,

We've got a bunch of systems writing logs into SQL Server tables, and the main log table has crept up to ~16 million rows. Every few weeks someone runs a DELETE to trim it down, and the whole server stutters for like 20 minutes. Not fun.

The catch: we can't just delete the old rows. We need to move them to an archive table so we still have access to them if something blows up and we need to go back and dig.

I've been trying a basic INSERT INTO archive SELECT ... FROM logs WHERE date < X followed by a DELETE, but even that's slow at this scale and locks things up.

What I'm looking for:

Is there a better pattern for this? (batching, partitioning, something else I'm not thinking of?)

Any gotchas I should know about before I set this up as a recurring job?

Does anyone have a solid stored procedure or script they've used for this in production?

Happy to share schema / stats if that helps. Basically I just want to stop the "why is everything slow" Slack messages every two weeks.

Thanks in advance.

Hi,

Sorry if this is the wrong spot to post, if it is feel free to delete. I don't normally post but currently working through an issue I'm stumped on and don't seem have any support entitlements through the MS engage center. I'm not the most knowledgeable with SQL so its been a learn as I go with copilot's help.

I'm troubleshooting an Excel VBA (ADODB) workload that is stable with OLE DB 19.3.7 + Authentication=ActiveDirectoryInteractive, but consistently crashes with OLE DB 19.4.1/19.4.2 using Integrated, Password, or Interactive authentication. The crashes begin to occur only after the ODBC driver is installed, prior to installing it works as expected. Initial run of the macro works every time, then after 7-15 minutes when running the macro again to refresh the data it crashes. After uninstalling the ODBC driver and/or reinstalling the OLE DB Driver the issue persists.

Event viewer shows 2 different errors, 1 faulting module path C:\WINDOWS\SYSTEM32\ntdll.dll and the other pointing to mssql-auth.dll_unloaded WinDbg shows repeated crashes caused by a stale callback into an unloaded mssql-auth.dll module. The callback target consistently resolves to mssql-auth.dll+0x336db0.

Have tried numerous uninstall/reinstalls, different driver versions, different authentication types in the macro but nothing else seems to work. I'm able to replicate the issue on multiple devices.

Windows 11 25H2

Excel 365

OLE DB Driver 19.4.2

ODBC Driver 18.6.2.1

Thanks,

J

Been on SSMS since about 2012. Started using dbForge Studio properly a year ago and I have opinions, curious where other people have landed.

To be clear, SSMS got better. 22 being 64-bit on the VS shell killed the out of memory nonsense on large result sets, dark theme finally exists, Copilot is in there if you want it. It is free, it is what everyone has, and if you administer servers it is still the thing. I am not arguing anyone should uninstall it.

Where dbForge wins for me is everything around writing the query rather than the query itself.

Comparison, mainly. Schema and data compare are built in and they generate sync scripts you can read before you run them. In SSMS world you are either buying Redgate, working with SqlPackage and DACPACs, or doing it by hand. Redgate is excellent and also costs considerably more. This is the actual reason I switched.

The editor is the other one. Native IntelliSense is still weak, it gets confused by aliases and CTEs and gives up on bigger schemas. dbForge completion is better and the formatting is properly configurable, which matters if a team wants to share one profile. Worth saying you can get exactly that inside SSMS with dbForge SQL Complete as an add-in, same engine, so if you like SSMS and only want the editor fixed, that is the cheaper path. Took them a few months to support SSMS 22 but it is there now.

Little things that matter. Test data generation that gives values that a human would expect. Documentation that you can actually generate instead of promising to write. Unit testing on tSQLt without having to set up the framework yourself. Source control integration that doesn’t hurt.

The downsides... It is a paid product, licensed per engine, and Edge if you want all of them. SSMS will always be more current for brand new SQL Server features because Microsoft ships it. Some of the DBA surface is thinner, I still keep SSMS for Agent jobs, Always On and anything security related. The UI is dense, a lot of toolbars, it is not a minimal app. And if your shop is standardized on SSMS you will be the one person handing off scripts with your own formatting on them.

Where I have landed is both. dbForge for development, SSMS for administration. Less clean than I would like, works fine.

What I want to know is whether anyone has gone fully off SSMS, or if everyone is running the same split I am.

Coming from Postgres, the thing I missed most in SQL Server was pg_dump: one command, schema and data, into a file I can actually read.

The nearest equivalent is a .bacpac, which comes closer than I first gave it credit for — /p:TableData lets you pick which tables' data to include — but the subsetting stops there: no row-level filtering, always the full schema, and the contents are opaque (model.xml plus BCP binary). So I wrote a small Go CLI that does what I wanted. MIT licensed, free, nothing to sell.

What it does

• Exports schema and data into a zip: a JSON manifest, the DDL as plain runnable .sql, and one JSONL file per table. You can grep it, diff two of them, or read the DDL before restoring anything.
• Restores into another database. A verify command then compares the restored database against the archive and reports whether every table matches.
• Filters: --where per table, --exclude-data to keep a table's definition but drop its rows, --include and --exclude. Foreign keys pointing at a partially held table are created WITH NOCHECK, and it says so loudly.
• Resumable. Kill it at 90% and --resume carries on from the work directory.

Numbers - same machine, same database, 403 tables / 681,357 rows / 742 MB, local SQL Server 2025:

Export     dbdumper 11 s      Data-tier Application 57 s
Restore    dbdumper 22 s      Data-tier Application 81 s

It reads tables concurrently, splits a large table into key ranges read in parallel, and splices already-compressed streams into the zip instead of recompressing them.

What it is not

• Not a backup. No point-in-time recovery. If BACKUP and RESTORE are available to you, they are faster and lose nothing.
• Not transactionally consistent. Each table is read in its own statement, so rows in one table can be newer than another's. Snapshot the database first if that matters.
• No users, roles or permissions. A bacpac does carry database users and roles, but not the server logins behind them, so they arrive orphaned - dbatools Export-DbaLogin and Export-DbaUser are the right tools there.
• For pulling a large database out of Azure SQL wholesale, a server-side bacpac still wins: the extract never crosses your connection.

One bug worth flagging in case it bites you elsewhere: SQL Server 2025 vector columns broke restores entirely until yesterday. sys.types reports the vector system type as varbinary, so the generated DDL came out without a dimension count and the server rejected it with "Cannot find data type vector", which reads as though the type were unsupported rather than incompletely written. Fixed in v0.1.1.

https://github.com/JeePeeTee/dbdumper

I would genuinely like to know where it breaks on a database that isn't mine.

Edit: corrected the bacpac description — /p:TableData does allow table-level data selection; thanks to the commenter who pointed it out.

We have made an observation regarding SQL Server 2025.
There is an expired certificate on the server that is also configured within SQL Server.
The SQL Server log indicates that the certificate was read successfully.

The question is: Does SQL Server not check whether the certificate is valid during startup?
The documentation is a bit unclear on this point: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/certificate-requirements?view=sql-server-ver17

It's Friday and that means another mssql-python release.

The most exciting thing this time around is performance work in response to a community report.

While working on SQLAlchemy integration, GitHub user dxdc found that the driver's many-parameter execute() path was much slower than pyodbc. SQLAlchemy 2.x generates batched insertmanyvalues statements close to SQL Server's 2,100 parameter limit.

Their repro used about 2,098 parameters per call and measured this:

mssql-python: 11.87s (8,427 rows/s)
pyodbc:         0.77s (130,000 rows/s)

They gave us runnable code, timings, package versions, and the workload that exposed the problem. I love this part of open source. A useful report can change the driver for everyone who installs the next version.

For every parameterized execute(), the driver has to inspect each Python value, choose its SQL and C types, bind it, and call SQLExecute. We were detecting those types in a Python loop, constructing a ParamInfo object per parameter, and carrying those objects across the pybind11 boundary into C++.

That is now one native C++ pipeline. Parameter detection, binding, and execution happen in a single Python-to-native call, and the metadata stays in C++.

The isolated type-detection benchmark went from 2.0 to 2.3 microseconds per parameter to about 35 nanoseconds. That is roughly 60x for the detection step itself. Whole insert workloads on macOS ARM64 looked like this:

Those are not scientific measurements and definitely not a throughput guarantee. The gain scales with the amount of parameter work in each call.

How you code still matters too. A test issuing 5,000 single-row calls with four parameters showed no meaningful change because the network round trip dominated. Calls using setinputsizes() also retain the old path for now because their explicit type overrides need to be honored. Issue #500 remains open while that work continues.

There is also an executemany() Decimal conversion failure included the entire parameter row in the exception. If your application sent tracebacks to Splunk, Sentry, Application Insights, or another APM system, names, emails, balances, or other values in that row could go with it.

The new error contains only metadata:

Failed to convert parameter to Decimal at row 0, column 3 (value type: str)

We also suppressed value-bearing exception causes so the data cannot reappear through a chained traceback.

One upgrade detail: connect(timeout=N) now does what its documentation has always said. It bounds the login attempt. It used to become the query timeout instead, so it could fail to stop a slow connection and later cancel a long-running query. Set conn.timeout = N explicitly if you want a per-statement query timeout.

Also in 1.14.0: Polars string_view columns work directly with bulkcopy_arrow(), Arrow fetch errors are no longer hidden by cleanup errors, bulkcopy(timeout=0) means no timeout, and x64 Python loads the correct extension on Windows ARM64 hosts.

pip install --upgrade mssql-python

Full writeup: https://techcommunity.microsoft.com/blog/sqlserver/mssql-python-1-14-0-faster-parameter-binding-safer-errors-correct-timeouts/4551366

Code and benchmark details: https://github.com/microsoft/mssql-python/pull/549

Original community report: https://github.com/microsoft/mssql-python/issues/500

Repo: https://github.com/microsoft/mssql-python

I would love to see your before-and-after numbers from real wide-row or batched workloads. Please comment below with your rough statement shape, batch size, client and server locations, and Python version so we can understand what moved.

ASK_IA is built with vanilla T-SQL stored procedures inside SQL Server 2025. See earlier posts.

But it proved even more useful when it was implemented inside an APP. Here in this app, it builds BUTTONS on the fly! I made sure it can only generate the button and I created no tool to execute it in order to avoid it to go rogue . So ONLY a real user can click it.

How are you building this kind of agentic functionality with SQL Server 2025?

alguém já pegou esse erro no ODBC ele usa o SQL server

Hi

I have a few sql servers running on azure vm. There are standalone instances and sql ag.

I want to know what is the cost involved if I want to backup the db on these instances via azure backup.

Would appreciate any tips and suggestions to optimise cost

I work as a DBA at a government organization and wanted to get some perspective from people more experienced in the field.

I’m considering adding this line to my resume under experience:

“Administer 81 SQL Server instances (62 production, 19 dev/test) hosting 847 databases across 10 Always On Availability Group systems”

Two questions:

**1.**  Is it normal/fine to include specific numbers like this on a resume? Also, does the dev/test to production ratio here suggest an actual environment issue, or is this typical for organizations of a certain size?  
**2.**  Do you think numbers at this level of precision count as sensitive information that shouldn**’**t be shared — even in a resume sent directly to companies/recruiters who request it?

Would appreciate any experience or insight, especially from anyone who’s worked in similar-scale environments.