{"id":202,"date":"2016-04-26T12:05:35","date_gmt":"2016-04-26T11:05:35","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=202"},"modified":"2016-04-26T13:12:05","modified_gmt":"2016-04-26T12:12:05","slug":"shrinking-large-half-empty-mdf","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2016\/04\/26\/shrinking-large-half-empty-mdf\/","title":{"rendered":"Shrinking large half-empty MDF"},"content":{"rendered":"

Shrinking your database is not a best practice (sometimes can be acceptable) but it might be trickier than you expect Once again I’ve found my inspiration browsing linkedin SQL Server groups<\/a>. Love it!<\/p>\n

First I have to say I’m a big fan of DBCC SHRINK, I use it all the time… <\/p>\n

NO, that was a bad joke. <\/p>\n

This case is possible one of the unique scenarios where you might want to SHRINK your database, because as we all know, SHRINK is evil.<\/p>\n

 
\nThe scenario<\/strong><\/p>\n

You have a large database and you’ve done a massive cleanup, so the result is that half your data file is empty and your sysadmin is pushing you to give that precious space back.<\/p>\n

Up to here everything sounds reasonable, you’re not using that space and you don’t have plans to use it (important!!), that can be because the database is no longer active for new transactions but still need to be available or some other reason.<\/p>\n

The problem comes when you see that SHRINKing the database does not work as you’d expect, so no much space is given back to the OS.<\/p>\n

There is an additional thing to consider, thanks to how SHRINK works, every time you run it you get a very very fragmented database which might hurt the performance of your application.<\/p>\n

 
\nCreate the playground<\/strong><\/p>\n

I always like showing you real examples (simplified) so you can get a better understanding of what is going on, so let’s go prepare the field for the battle.<\/p>\n

\r\n--==========================================================================\r\n-- Create the playground\r\n--==========================================================================\r\nUSE [master]\r\nGO\r\n\r\nIF DB_ID('DBCC_SHRINKFILE') IS NOT NULL BEGIN \r\n\tALTER DATABASE [DBCC_SHRINKFILE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\n\tDROP DATABASE [DBCC_SHRINKFILE] \r\nEND\r\nGO\r\n\r\nUSE [master]\r\nGO\r\n\r\nCREATE DATABASE [DBCC_SHRINKFILE]\r\nGO\r\n\r\nUSE [DBCC_SHRINKFILE]\r\nGO\r\n\r\nSELECT IDENTITY(INT, 1, 1) AS Id, o.*\r\n\tINTO dbo.random_objects\r\n\tFROM sys.objects AS o\r\n\t\tCROSS JOIN sys.objects AS o2\r\n\t\tCROSS JOIN sys.objects AS o3\r\n\r\nALTER TABLE dbo.random_objects ADD CONSTRAINT PK_random_objects PRIMARY KEY (Id)\r\nGO\r\n\r\nSELECT * \r\n\tINTO dbo.random_objects_NEW\r\n\tFROM dbo.random_objects\r\n\r\nALTER TABLE dbo.random_objects_NEW ADD CONSTRAINT PK_random_objects_NEW PRIMARY KEY (Id)\r\nGO\r\n\r\n-- See file size and usage\r\nSELECT CONVERT(DECIMAL(10,2), df.size * 8. \/ 1024) AS Size_MB\r\n\t\t, CONVERT(DECIMAL(10,2), FILEPROPERTY(df.name, 'SpaceUsed') * 8. \/ 1024) AS SpaceUsed_MB \r\n\tFROM sys.database_files AS df\r\n\tWHERE df.type_desc = 'ROWS'\r\nGO\r\n<\/pre>\n
 
\nNow you have 2 tables, one to be deleted and another to be kept, so let’s drop that useless table.<\/p>\n
\r\n--==========================================================================\r\n-- We do some cleanup, old tables are removed\r\n--==========================================================================\r\n\r\nDROP TABLE dbo.random_objects\r\n-- Empty space at the begining of the file\r\n\r\n-- See file size and usage, size still the same, usage is less so there is some empty space\r\nSELECT CONVERT(DECIMAL(10,2), df.size * 8. \/ 1024) AS Size_MB\r\n\t\t, CONVERT(DECIMAL(10,2), FILEPROPERTY(df.name, 'SpaceUsed') * 8. \/ 1024) AS SpaceUsed_MB \r\n\tFROM sys.database_files AS df\r\n\tWHERE df.type_desc = 'ROWS'\r\nGO\r\n\r\n<\/pre>\n
 
\nFirst Approach<\/strong><\/p>\n
At this point if we are certainly sure we will not need that space never again, we might want to SHRINK our database (or data file) to see if it does what is says on the tin<\/p>\n
\r\n--==========================================================================\r\n-- First approach, \r\n--==========================================================================\r\n\r\n-- SHRINK\r\nDBCC SHRINKDATABASE([DBCC_SHRINKFILE])\r\n\r\n-- See file size and usage, size still the same, usage is less so there is some empty space\r\nSELECT CONVERT(DECIMAL(10,2), df.size * 8. \/ 1024) AS Size_MB\r\n\t\t, CONVERT(DECIMAL(10,2), FILEPROPERTY(df.name, 'SpaceUsed') * 8. \/ 1024) AS SpaceUsed_MB \r\n\tFROM sys.database_files AS df\r\n\tWHERE df.type_desc = 'ROWS'\r\nGO\r\n\r\n-- SHRINK = Fragmentation\r\nSELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')\r\nGO\r\n<\/pre>\n
 
\nSo after all this effort, we need to rebuild our index<\/p>\n
\r\n-- Remove fragmentation\r\nALTER INDEX PK_random_objects_NEW ON dbo.random_objects_NEW REBUILD\r\n\r\n-- We have save spaced from the dropped table, but still 50% of the file is empty\r\nSELECT CONVERT(DECIMAL(10,2), df.size * 8. \/ 1024) AS Size_MB\r\n\t\t, CONVERT(DECIMAL(10,2), FILEPROPERTY(df.name, 'SpaceUsed') * 8. \/ 1024) AS SpaceUsed_MB \r\n\tFROM sys.database_files AS df\r\n\tWHERE df.type_desc = 'ROWS'\r\nGO\r\n<\/pre>\n
 
\nYou can see that although we managed to get some space back, we are still wasting half the size, which was used to REBUILD our index and remove the fragmentation.
\nThat probably will happen on the next maintenance window because we are proactive DBAs and do regular index maintenance, right??<\/p>\n
 
\nSecond Approach<\/strong><\/p>\n
So how to overcome this problem, the approach above will make our data file to become a yo-yo, grow-shrink, grow-shrink and so on, and the space will never be given back.<\/p>\n
But there is another approach you might want to try. First you might want run the first script again to reset everything. <\/p>\n
\r\n--==========================================================================\r\n-- Second approach, \r\n--==========================================================================\r\n\r\n-- Create a new FILEGROUP\r\nALTER DATABASE [DBCC_SHRINKFILE] ADD FILEGROUP [USER_DATA]\r\n\r\n-- Add a file\r\nALTER DATABASE [DBCC_SHRINKFILE] \r\nADD FILE (\r\n\tNAME = N'DBCC_SHRINKFILE_user_data'\r\n\t, FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQL2014\\MSSQL\\DATA\\DBCC_SHRINKFILE_user_data.ndf')\r\nTO FILEGROUP [USER_DATA]\r\n\r\n-- Rebuild your tables in the new filegroup\r\nUSE [DBCC_SHRINKFILE]\r\nGO\r\n\r\nCREATE UNIQUE CLUSTERED INDEX PK_random_objects_NEW ON dbo.random_objects_NEW ([Id] ASC)\r\nWITH (DROP_EXISTING = ON)\r\nON [USER_DATA]\r\nGO\r\n\r\n-- Now shrink the file which is now almost empty\r\nDBCC SHRINKFILE(DBCC_SHRINKFILE)\r\n\r\n-- Now both files take just as much as they need\r\nSELECT df.name\r\n\t\t, CONVERT(DECIMAL(10,2), df.size * 8. \/ 1024) AS Size_MB\r\n\t\t, CONVERT(DECIMAL(10,2), FILEPROPERTY(df.name, 'SpaceUsed') * 8. \/ 1024) AS SpaceUsed_MB \r\n\tFROM sys.database_files AS df\r\n\tWHERE df.type_desc = 'ROWS'\r\nGO\r\n<\/pre>\n
 
\nThis approach as you can see, allows you to move all your data from one filegroup to another, so you can SHRINK the mdf file once the user data is somewhere else.<\/p>\n
And what is better, you will get brand new indexes without any fragmentation, so this is a win-win approach I’d say.<\/p>\n
One more little thing, it’s a good idea to leave the PRIMARY filegroup for metadata only, so you can run this in case you forget specifying the filegroup when creating tables or indexes.<\/p>\n
\r\nALTER DATABASE DBCC_SHRINKFILE MODIFY FILEGROUP [USER_DATA] DEFAULT\r\n<\/pre>\n
 
\nThanks for reading and feel free to throw me any question you may have.
\n 
\n 
\n <\/p>\n","protected":false},"excerpt":{"rendered":"
Shrinking your database is not a best practice (sometimes can be acceptable) but it might be trickier than you expect Once again I’ve found my inspiration browsing linkedin SQL Server groups. Love it!…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,19],"tags":[34,5,25],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/202"}],"collection":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/comments?post=202"}],"version-history":[{"count":1,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/202\/revisions"}],"predecessor-version":[{"id":204,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/202\/revisions\/204"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=202"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=202"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=202"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}