Skip to main content
Finish unfinished sentence
Source Link
Csaba Toth
  • 239
  • 3
  • 9
  1. How can I shrink my database? What files do I shrink?: You can shrink the files individually by the DBCC SHRINKFILE command you mention. It depends on your server how many files your database consists of. A simple database has one database file and one transaction log file.
  2. What should my considerations be while doing this?: the shrink affects your index fragmentation, see 3rd point. Also note that you don't want to shrink the database file to a size which is the minimal possible, because in a real-world environment it'll grow anyway. So I would tune the size (in your example you gave 7 megabytes) in a manner that you'd leave 10%-20% free space within the database file, because it'll be filled anyway in the production environment, and you can save some auto-growth cycles that way. So the actual number needs careful calculation. Also note that the "big space freeup" you performed would bloat up the transaction log file even more than the space you gained within the DB file. Also, the actual space gain you may experience will be less than what you mathematically expect! So let's say you mathematically freed up 12 gigs, then you maybe able toand that means gigabytes less in case of a 12GB mathematical shrink only!
  3. Should I do anything after?: As I mentioned earlier, you want to reindex those indexes which fragmentation got distorted as a result of the SHRINK's changes. I haven't experimented enough if you need to do anything special about query statistics.
  4. What if it is a large database? Can I shrink it in smaller increments? The SHRINK operation can be interrupted any time, and you can continue later. I'd advise to perform it on an off-line database if possible. By interrupting and contuniung it'd would go forward the same shrink size though. Theoretically you can shrink in smaller increments by specifying a less tight target size instead of 7 megabytes, but I'd say that if you are performing it in production, then just give it one go. As you see there are issues with the index fragmentation and possible transaction log growth. So I'd go through this just one time.
  1. How can I shrink my database? What files do I shrink?: You can shrink the files individually by the DBCC SHRINKFILE command you mention. It depends on your server how many files your database consists of. A simple database has one database file and one transaction log file.
  2. What should my considerations be while doing this?: the shrink affects your index fragmentation, see 3rd point. Also note that you don't want to shrink the database file to a size which is the minimal possible, because in a real-world environment it'll grow anyway. So I would tune the size (in your example you gave 7 megabytes) in a manner that you'd leave 10%-20% free space within the database file, because it'll be filled anyway in the production environment, and you can save some auto-growth cycles that way. So the actual number needs careful calculation. Also note that the "big space freeup" you performed would bloat up the transaction log file even more than the space you gained within the DB file. Also, the actual space gain you may experience will be less than what you mathematically expect! So let's say you mathematically freed up 12 gigs, then you maybe able to shrink only
  3. Should I do anything after?: As I mentioned earlier, you want to reindex those indexes which fragmentation got distorted as a result of the SHRINK's changes. I haven't experimented enough if you need to do anything special about query statistics.
  4. What if it is a large database? Can I shrink it in smaller increments? The SHRINK operation can be interrupted any time, and you can continue later. I'd advise to perform it on an off-line database if possible. By interrupting and contuniung it'd would go forward the same shrink size though. Theoretically you can shrink in smaller increments by specifying a less tight target size instead of 7 megabytes, but I'd say that if you are performing it in production, then just give it one go. As you see there are issues with the index fragmentation and possible transaction log growth. So I'd go through this just one time.
  1. How can I shrink my database? What files do I shrink?: You can shrink the files individually by the DBCC SHRINKFILE command you mention. It depends on your server how many files your database consists of. A simple database has one database file and one transaction log file.
  2. What should my considerations be while doing this?: the shrink affects your index fragmentation, see 3rd point. Also note that you don't want to shrink the database file to a size which is the minimal possible, because in a real-world environment it'll grow anyway. So I would tune the size (in your example you gave 7 megabytes) in a manner that you'd leave 10%-20% free space within the database file, because it'll be filled anyway in the production environment, and you can save some auto-growth cycles that way. So the actual number needs careful calculation. Also note that the "big space freeup" you performed would bloat up the transaction log file even more than the space you gained within the DB file. Also, the actual space gain you may experience will be less than what you mathematically expect, and that means gigabytes less in case of a 12GB mathematical shrink!
  3. Should I do anything after?: As I mentioned earlier, you want to reindex those indexes which fragmentation got distorted as a result of the SHRINK's changes. I haven't experimented enough if you need to do anything special about query statistics.
  4. What if it is a large database? Can I shrink it in smaller increments? The SHRINK operation can be interrupted any time, and you can continue later. I'd advise to perform it on an off-line database if possible. By interrupting and contuniung it'd would go forward the same shrink size though. Theoretically you can shrink in smaller increments by specifying a less tight target size instead of 7 megabytes, but I'd say that if you are performing it in production, then just give it one go. As you see there are issues with the index fragmentation and possible transaction log growth. So I'd go through this just one time.
Source Link
Csaba Toth
  • 239
  • 3
  • 9

  1. How can I shrink my database? What files do I shrink?: You can shrink the files individually by the DBCC SHRINKFILE command you mention. It depends on your server how many files your database consists of. A simple database has one database file and one transaction log file.
  2. What should my considerations be while doing this?: the shrink affects your index fragmentation, see 3rd point. Also note that you don't want to shrink the database file to a size which is the minimal possible, because in a real-world environment it'll grow anyway. So I would tune the size (in your example you gave 7 megabytes) in a manner that you'd leave 10%-20% free space within the database file, because it'll be filled anyway in the production environment, and you can save some auto-growth cycles that way. So the actual number needs careful calculation. Also note that the "big space freeup" you performed would bloat up the transaction log file even more than the space you gained within the DB file. Also, the actual space gain you may experience will be less than what you mathematically expect! So let's say you mathematically freed up 12 gigs, then you maybe able to shrink only
  3. Should I do anything after?: As I mentioned earlier, you want to reindex those indexes which fragmentation got distorted as a result of the SHRINK's changes. I haven't experimented enough if you need to do anything special about query statistics.
  4. What if it is a large database? Can I shrink it in smaller increments? The SHRINK operation can be interrupted any time, and you can continue later. I'd advise to perform it on an off-line database if possible. By interrupting and contuniung it'd would go forward the same shrink size though. Theoretically you can shrink in smaller increments by specifying a less tight target size instead of 7 megabytes, but I'd say that if you are performing it in production, then just give it one go. As you see there are issues with the index fragmentation and possible transaction log growth. So I'd go through this just one time.

We all know that it is not advised to do SHRINK regularly anyway. I try to leave out all the warnings and disclaimers you probably know anyway. Backup, and don't do this at home if possible :)

Bonus: in replication environment if you perform this on the publisher database, it won't cause the subscriber databases to shrank (which may have the size problem because they are Express editions).

Finally, my reindex script:

USE YourDBName

DECLARE @TbName VARCHAR(255)
DECLARE @FullTbName VARCHAR(255)
DECLARE @IxName VARCHAR(255)
DECLARE myCursor CURSOR FOR
    SELECT OBJECT_NAME(dmi.object_id) AS TableName,i.name AS IndexName
    FROM sys.dm_db_index_physical_stats(14, NULL, NULL, NULL , 'LIMITED') dmi
    JOIN  sys.indexes i on dmi.object_id = i.object_id and dmi.index_id = i.index_id
    WHERE avg_fragmentation_in_percent > 30
    ORDER BY avg_fragmentation_in_percent
OPEN myCursor
FETCH NEXT FROM myCursor INTO @TbName, @ixName
WHILE @@FETCH_STATUS = 0
BEGIN
    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA = 'dba' AND TABLE_NAME = @TbName)
BEGIN
        SET @FullTbName = 'dba.' + @TbName
        IF (@ixName IS NULL)
        BEGIN
            PRINT 'Reindexing Table ' + @FullTbName
            DBCC DBREINDEX(@FullTbName, '', 0)
        END
        ELSE
        BEGIN
             PRINT 'Reindexing Table ' + @FullTbName + ', Index ' + @IxName
             DBCC DBREINDEX(@FullTbName, @IxName, 0)
        END
    END
    FETCH NEXT FROM myCursor INTO @TbName, @ixName
END
CLOSE myCursor
DEALLOCATE myCursor

The only variable in this is the 14, which can be obtained by issuing select DB_ID('YourDBName'), and the script assumes that you are interested only in the tables in the dba.* schema.