April 8, 2008 at 12:11 am
Hi All,
The company I work for uses MS SQL Server 2000 EE to store their customer information. The database size is now around 14GB. The database file is itself is of 13 GB. Can someone help how can I shrink the database file after deleting records(70% records) to recover disk space.
Thanks in advance..
April 8, 2008 at 12:24 am
DBCC ShrinkDatabase or DBCC ShrinkFile
You can find all the details for both in Books online.
Be aware that a DB does need some empty space to function properly, so maybe shrink it down so there's 10-15% free.
Also, make sure you rebuild all your indexes after the shrink. Shrink shuffle database pages around. It can cause really bad fragmentation
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 8, 2008 at 8:01 am
Just a tip - When you have really bad fragmentation, with the databases offline (SQL server services are stopped), you can run the system tool "Disk Defragmenter". It'll actually improve performance. What I do first is a full database backup of all DBs, just to be safe, then defrag. Time wise for the process will depend on disk size and amount of fragmentation. It's not something we do often but we do do it from time to time, usually during an upgrade of the application.
-- You can't be late until you show up.
April 9, 2008 at 3:28 am
There are 2 issues - SQL table & index fragmentation, and NTFS disk fragmentation.
To reduce the size of your database, I would look at a) delete the unwanted data, b) rebuild all indexes, c) DBCC SHRINKFILE ... truncateonly. This will leave your DB with the smallest amount of space it needs to operate efficiently, and with all your indexes having the least possible fragmentation. Gail is right in saying that just shrinking your DB can leave it badly fragmented internally.
To deal with the NTFS fragmentation, do what tcrosby suggests.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 9, 2008 at 3:38 am
tosscrosby (4/8/2008)
Just a tip - When you have really bad fragmentation, with the databases offline (SQL server services are stopped), you can run the system tool "Disk Defragmenter".
That will fix file-level fragmentation, where the disk sectors comprising the data and/or log files are scattered across the disk
It won't fix index fragmentation, which is where the pages comprising the leaf-level of the indexes are not contiguous and in order.
Repeated data file grow and shrink (like when auto grow and auto shrink are on) can lead to external (file level) fragmentation.
Data file shrinks (when run without the truncate_only option) causes internal (index) fragmentation as the shrink process works by moving pages that are towards the end of the file to open spaces earlier in the file. It can, in worst cases, completely reverse the order of pages in an index.
There are numerous other causes of internal fragmentation as well.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2008 at 6:55 am
I agree with both Gail's and Ed's posting. I run the disk defrag maybe once a year if I've got the application offline for an upgrade (meaning I'm in the office on the weekend!), just to cleanup the NTFS side of things. I should have included a blurb about the internal fragmentation and index rebuild but figured that was already stated. Index rebuilds, without question, should be run with some regularity to keep things running smooth and, as pointed out, especially after you've manipulated your data files. As with shrinkfiles, I guess if you're purging a lot of data, there may a point to it. I rarely shrink files - usually a log on one of my dev environments. I personally don't consider it a "best practice". If disk is sufficient and your file grew to that size because of normal business, I don't even look at it. I do monitor my databases and make sure there is sufficient room so we're not auto-growing during business hours.
-- You can't be late until you show up.
April 16, 2008 at 4:34 am
Thank you all ...I will now try to shrink the database as per your advices..:)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply