How 11GB of SQL Server Data Mysteriously Disappeared, and How I Ended Up Better Off than Before

Note: If you try this, and your database does shrink, please write me and let me know. It would be interesting to see how common a problem this is. webmaster@sql-server-performance.com

Reader's Feedback

We have had similar problem in some tables in SQL Server 7.0 that were regularly  loaded and deleted with data. Our databases are smaller than the ones in the article, but we would notice that these tables were suddenly 300MB when they should have only been 1-2 MB. Using TRUNCATE TABLE prevented the growth, but it's not really an option for all of the tables. Running the DBCC's didn't help us either. I think I managed to trace it to a bug in SQL Server 7.0  by scouring Google and other newsgroups. The bug is fixed in SQL 2000, but not all of our databases have been upgraded yet.

One other thing that I discovered, which solved the problem, was adding an index to the table. We didn't add indexes to these tables initially because we didn't want the overhead, but since indexes (even non-clustered ones) keep track of the data pages, it would get rid of the empty pages. In a test that I did, adding a non-clustered covering index cut the table growth by about 90%. And the processes even ran quicker. Another example of why indexes are important.

Deborah Melkin
Database Administrator
Eze Castle Software

*****

I saw something like this happen to our 100+ GB database about a year ago. We had a table that had 3 rows in it, all fields where CHAR, INT and VARCHAR. Nothing big. The table showed a size of almost 4GB. I ran all the DBCC's on it and couldn't get the size to come back down. Then I put a clustered index on it and that brought it back down to where it should have been.

Thanks,
Shannon

*****

I had the same problem about one year ago with my SQL Server 7.0 servers. After we upgraded to SP3, the size of our databases became much large then before. I did some investigation and found out that the databases had a lot of free space marked as used. I spent a lot of time trying to shrink them, using DTS to re-import the data, rebuild indexes, and all other stuff to get rid of that free space and had no success. I called Microsoft and they recommended to run an undocumented trace flag for my own risk as a startup parameter. Since running with the trace flag, the problem went away. The trace flag is T1180. They did not explain to me what that trace does, but it is working pretty good.

If somebody have the same problem they can use that flag.

Thanks,
Simon

[Brad: This article has some information, although incomplete, on the T1180 trace flag.]