asked
Data Purge and then Database Shrink
I'm supporting a SQL 2008 R2, 1.5 TB database that I need to purge or rather delete nearly 3 billion unwanted records from. I restored a copy of the db in Test and removed the 3 billion records. The data was deleted from two heap tables used for archiving older data. The properties of the db show 1.5 "Size" and 1.4 TB of "Space Available". To me this means the db is now only about 100 GB in size. I am well aware that shrinking a database is not best practice for regular maintenance however it is available for scenarios such as this, after having removed a large amount of data. I do not believe the db will ever grow larger than 1 TB ever again, maybe 500 GB in ten years. The question is, considering the amount of unused space or "Space Available", are there tricks to help shrink the db or other options for making the db smaller? I've been running a Shrink in the Test environment for about two weeks and it's hardly made a dent in the resize. Also, there are no Blob data types, there are two VARCHAR(MAX) fields but I ran max(datalength(fieldname)) against them and nothing was more than 166 bytes. Thanks.
Thanks for helping John. The space is in the data file. The Test db is also running in Simple. I've run a couple different scripts individually. I have tried these two: DBCC SHRINKFILE (N'DataFile', 1000000) and also DBCC SHRINKDATABASE ('DBName', 10) DataFile - CurrentSizeMB = 1,502,145 FreeSpaceMB =1,400,882 LogFile - CurrentSizeMB = 4,000 FreeSpaceMB = 3,773 Also, I just noticed we have one field that is XML data type. I believe this is BLOB / LOB. Unsure how this is stored and impacts the shrink in 2008 R2. I ran a query for max(datalength()) on this field and the result was 1822 bytes. Not that large.
18 People are following this question.
