Post reply

Shrink data file to a smaller size

  • February 3, 2012 at 5:15 pm

    #391475

    I have a database of vendor product, the database data file size is about 13 gb.

    We want to clean up some history tables. The vendor send us a script that deletes some history data of some tables, but after that, I tried to do a shrink file, but I still get:

    database_namedatabase_sizeunallocated space

    SurveyorDB13919.19 MB3.23 MB

    I have also attached a report from SSMS, can I shrink the database to a smaller size?

    What the vendor send to us is below: but I am a little afraid to run this. From the size, do you think we can shrink it to a smaller size or we cannot, for example 8 gb, according to the report attached?

    dbcc shrinkfile(SurveyorDB_Data, 1)

    go

    dbcc shrinkfile(SurveyorDB_Log, 1)

    go

  • February 4, 2012 at 12:24 pm

    #1442767

    The shrink file will only be able to remove the unallocated space from your data files - it won't help much here. I'd suggest running an index rebuild on the tables you've removed data from, then running the report again to see how much unallocated space you have. You can then run the SHRINKFILE command to return the freed space back to the file system. I'd suggest leaving plenty of space in your database files though to prevent the need for autogrowth.

    Running the shrink file without specifying TRUNCATEONLY will cause fragmentation in your database, reducing performance. Try with TRUNCATEONLY first, but you might not free as much disk space. If you run the SHRINKFILE command without TRUNCATEONLY, I suggest rebuilding all your indexes afterwards. Rebuilding the indexes might grow your data file again.

    See here for more info:

    http://technet.microsoft.com/en-us/library/ms189493.aspx

    Hope this helps,

    David

    DBA Dash - Free, open source monitoring for SQL Server

  • February 6, 2012 at 8:03 am

    #1443164

    David has some good advice. I would personally determine the database size, allow for 3-4 months data growth, and then make sure you can reindex your largest table. Then shrink to that size, if you need to shrink. Once you've shrunk, I'd reindex anyway, just to be sure that things were optimally laid out.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply