Typically, when I’m faced with a SQL Server problem, I perform research until I discover a solution. I have many sources for research… here are a few (and the order that I typically follow) for troubleshooting specific error codes and messages:
- [master].[dbo].[sysmessages] (SQL Server 2000) or [master].[sys].[messages] (SQL Server 2005 and later)
- The Microsoft Knowledge Base
- Books Online: SQL Server 2000, SQL Server 2005, or SQL Server 2008
- Search engine
- Newsgroups and forums
- Any of several colleagues
Regardless of the source, I submit query after query and constantly refine my search terms. After sifting through enough results I will have the answer. Sometimes it’s presented to me in complete form, other times I must synthesize the solution from several partial answers. I have been remarkably successful as a DBA just from honing my research skills.
As my knowledge of SQL Server deepens, there have been those rare occasions in which I come up with an answer of my own. An escalation call on Saturday led to such an occurrence.
NOTE: This escalation call dealt with a SQL Server 2000 instance, so that is the version that I’ll be targeting with the various links.
A customer had rebuilt all of the indexes in a ~300 gigabyte database and then they were surprised when the data files grew. One of the data files had doubled in size but was only using half of the space. This was leading to free space alerts on the volume hosting a few data files (the alert threshold was 15% and the growth resulted in only 8% free space on the drive). The customer requested that we shrink the files.
I’m not going to get into the many reasons that free space in a data file is beneficial or the many issues with shrinking data files.
I decided to start by running DBCC SHRINKFILE with the TRUNCATEONLY argument. I received error 3140: “Could not adjust the space allocation for file ‘%ls’.”
As usual, I began to research the error message using the above-mentioned resources. I found many people asking about the issue, but the best answer applied only to SQL Server 7.0.
I’m not sure why, but I decided to stop researching and try to figure it out myself. Since the error message relates to space allocation, I decided to run DBCC CHECKALLOC. The command finished quite quickly with “CHECKALLOC found 0 allocation errors and 0 consistency errors in database ‘RedactedDatabaseName’.” at the end of the output. I considered running the full-blown DBCC CHECKDB when I had an idea… what if the problem was a variation on the SQL Server 7.0 bug that was still present in SQL Server 2000? Since the known bug related to index allocation discrepancies, I wondered if running DBCC UPDATEUSAGE would help. Other than the somewhat trivial server resources that it would take to report and correct inaccuracies in the [master].[dbo].[sysindexes] table, it couldn’t hurt, right? So I gave it a try. DBCC UPDATEUSAGE took eight minutes and twenty-one seconds to run and made 238 corrections. A subsequent execution of the DBCC SHRINKFILE command (with the TRUNCATEONLY argument) finished in six seconds and returned 5.72 gigabytes to the file system. My hunch paid off.
It was my own understanding of indexes and experience with the DBCC UPDATEUSAGE command that ultimately provided the solution to this problem! It’s so gratifying when answers come as much from me as any other source–a situation that grows more common all the time.
As far as the escalation call, I wrote an email to the customer and laid out five reasons not to shrink the file any further. The customer agreed and the disk space threshold is being changed from 15% down to 10% (which is perfectly reasonable in this case).
Finally, two PSAs:
- Please consult an experienced database professional before implementing widespread index maintenance. Index maintenance is very important but it is also costly and can lead to disk space issues (data file and transaction log growth). Improper fill factor values for your workload may actually make performance worse.
- Please consult an experienced database professional for database file capacity planning. In a well designed/managed system autogrow should not occur (being a fail-safe mechanism only) and files should not be shrunk.
