CODEWITHSUNDEEP
sql-server-2008
Learner
Learner

Reputation: 786

How Safe is it to shrink the database, to free up the physical space - Sql Server

While looking to solve the Disk Space Issue, I have been through several posts and found that Shrinking the database is one option while some others are Deleting Error Log files and transaction log files. But,

I found the first option controversial where in order to free up the unused space SQL uses an ugly process and results in Index fragmentation that affects performance in the long run. Meaning that after deallocating the space we are giving authority to the Operating system do what it needs to with it.

So, would it be fair enough to remove the shrink option from the list of the available options?

Upvotes: 3

Views: 14553

Answers (1)

Rahul Tripathi
Rahul Tripathi

Reputation: 172608

This is true that shrinking a database is not recommended. You can understand it like this when you shrink the database then it leads to increase in fragmentation now to reduce the fragmentation you try to rebuilt the index which will eventually lead to increase in your database size.

You can run the query to test it by yourself

SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
GO
-- Shrink the Database
DBCC SHRINKDATABASE (ShrinkIsBed);
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
GO
-- Rebuild Index on FirstTable
ALTER INDEX IX_SecondTable_ID ON SecondTable REORGANIZE
GO
-- Name of the Database and Size
SELECT name, (size*8) Size_KB
FROM sys.database_files
GO
-- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
GO

Query source from here

You can check Don’t Touch that Shrink Database Button!

What Happens when you Shrink a Database?

When you click that shrink database button (or leave a DB in autoshrink, or schedule a job to perform shrinks), you are asking SQL Server to remove the unused space from your database’s files.The process SQL uses is ugly and results in Index fragmentation that affects performance in the long run. You’ve deallocated that space and are letting the O/S do what it needs to with it. If you have a growing database (as the majority of production databases tend to be), this means that that database will grow again. Depending on your autogrowth settings (another pet peeve for another post) this growth will probably be more than necessary and you will end up shrinking again… At best this is just extra work (shrink grow/shrink grow) and the resulting file fragmentation is handled alright by your I/O subsystem. At worse this is causing that index fragmentation I mentioned, file fragmentation, interrupting what would have otherwise been contiguous files and potentially causing I/O related performance problems. Really though, you are wasting time and introducing index fragmentation.

Upvotes: 6

Related Questions