It is sometimes difficult for other IT people to understand the constraints that DBAs have to work under. So often the ideal or obvious solution is frustratingly out of reach because of 'political' reasons that seem baffling to the spectator. Francis Hanlon describes a typical incident: It is essential reading for anyone wishing an insight of the problems that DBAs face

On the Trail of the Expanding Databases
I look after a server that contains three identical production databases, used by three different health care clinics. Growth has always been fairly rapid but recently, I noticed that these databases were growing at a rate that far exceeded the norm. They were part of a purchased package, and I knew the vendor was in the process of implementing an interface from these databases to another system. Was this abnormal growth a side-effect of something they'd done while implementing this new interface, or completely unrelated?

It was time to investigate.


Monitoring Database Growth
Now, you might ask, how did I know that the database growth was accelerating at a greater rate than it had in the past? There are several available methods of tracking database growth but the one I use most often is based on a script I found at SQLServerCentral:

http://www.sqlservercentral.com/scripts/Maintenance+and+Management/30217/

I won't go more into specifics except to say that it important not only to know how big is your database today but how big was it last month, and the month before, so that proper space planning is possible.

Of course, in this case, the database was experiencing abnormal growth so my careful planning was moot, and I was running out of free space. Monitoring Table Space

Armed with the knowledge that my database was growing rapidly, I needed to gather more specific information. What was causing the rapid growth and did it seem likely to continue or was it just a one-time data load event?

I needed to check the size of all the tables in the database and find out if any new tables had been created, or if there were any old tables being more heavily used as a result of the vendor's new interface. Was the database experiencing overall growth amongst all the tables, or just in some history tables, or were there specific interface tables that were rather large?

I spoke to the vendor and was told that they were making no schema changes. What they were doing was implementing a stored procedure that wrote to several existing tables that, in the absence of this new interface, would remain unpopulated.

On SQLTeam.com, I discovered a script that shows a breakdown of space used by table, including the amount of free space. This script works well in both SQL 2000 and SQL 2005, although I often change the script to print the output by the unused column, as opposed to the table_name column.

Using this script, I discovered that one of the tables affected by the vendor's changes was the major culprit in the growth of the database. Although it contained only 194,000 rows, in terms of physical space it was hogging 9.2 GB, of which 8.5 GB was marked as unused. However, I also found other problem tables that were not affected by the vendor's changes. The next worst offender, with 23,000 rows, took up 710 KB of storage and was marked as having 620 KB of unused space. Using this same script, I found out that two of my other databases that were completely unaffected by the vendor's changes were also exhibiting, to a lesser degree, abnormal growth of unused space. It would seem the vendor's actions had helped me to notice the growth of unused space but were not the only cause of it. Some urgent action was required.

Reclaiming Unused Space
I continued to research. Several searches on terms such as 'reclaim space in SQL' returned results that pointed toward the DBCC CLEANTABLE command, which is said to 'reclaim space from dropped variable-length columns in tables'.

Well, given that I hadn't dropped any columns, I guessed that this was unlikely to work. However, I couldn’t see what harm it could do either so I gave it a try on a restored version of our production database that I put on a test box. Having run the command I found that (surprise, surprise) virtually no space was reclaimed.

My next thought was to run a DBCC DBREINDEX on this table to fix the unused space. Again, this had little effect. The index fragmentation improved but it never was very bad to begin with since indexes get defragged everyday in this database. In any case, my objective was to reclaim space and the DBCC DBREINDEX reclaimed very little space so it was not what I was after. Clustered versus Non-Clustered Indexes

During these investigations, I noticed that each of the offending tables did not have a clustered index. What if I made the primary key a clustered index then changed it back to a non-clustered index so the vendor couldn't say I changed their schema?

On the 23K row table, I scripted out the primary key index, which was a unique non-clustered index, dropped it and re-added it as a clustered index, then dropped it again and returned it to its previous state:

ALTER TABLE [dbo].[HL7Message] DROP CONSTRAINT [PK_HL7Message]
ALTER TABLE [dbo].[HL7Message] ADD CONSTRAINT [PK_HL7Message] PRIMARY KEY CLUSTERED
( [HL7MessageID] ASC
)WITH FILLFACTOR = 90
ALTER TABLE [dbo].[HL7Message] DROP CONSTRAINT [PK_HL7Message]
ALTER TABLE [dbo].[HL7Message] ADD CONSTRAINT [PK_HL7Message] PRIMARY KEY NONCLUSTERED
( [HL7MessageID] ASC
)WITH FILLFACTOR = 90


The results were excellent – all unused space was reclaimed – and the schema was back the way it was before I did anything.

One table done; now for the big one! However, for the 194K-row table, the trick did not work as well as it did on the first table. The space used by the table shrank from 9 GB to 5.4 GB, of which 4.9G was still unused. I needed to reclaim more space. Text versus Varchar versus Text in Row

I was intrigued to know why dropping then recreating the indexes on one of the problem tables reclaimed all of the unused space and on the other one only reclaimed half of it

One difference seemed to be that the 194K-row table had a column defined as type TEXT, whereas the other table was using varchars. Was there a problem with text columns and space? I started searching and found a couple of forum postings that made reference to text and wasted space, and these led me to a Microsoft knowledge based article, called FIX: TEXT/IMAGE Data Storage Space Not Reclaimed Correctly. This article describes a space issue with tables having a text column and indicates the solution is to "Use a SELECT INTO statement to transfer the whole table to a new table in the same database". Now, I did notice that the article described this as a problem with SQL Server 7 and I was running SQL Server 2000. However, when I looked at the database properties I discovered the compatibility level was set to SQL Server 70.

So, I created a script, shown below, to define the 194K row table, and indexes, exactly the same as the original. Before running this script, I renamed the original table (and indexes) to tableOLD. Next, I ran the script to recreate my new table and copied all the rows from the old, renamed table, into the new one.

Full article...