This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Few of SQL Server DBAs are frequently executing Shrink on database or data files. Why? because they are gaining more free space after shrink.
You can find N number of theory on, why you should not shrink your database or data file in SQL Server?
Instead of big theory, I am sharing full practical demonstration on this. You should not shrink your database or data files otherwise it generates massive fragmentation for your tables and indexes. Still, you can shrink your log file, but after full backup only.
Create a test database and use it:
|
1 2 3 4 5 |
CREATE DATABASE Test_Shrink GO Use Test_Shrink GO |
Create a sample table:
|
1 2 3 4 5 |
CREATE DATABASE Test_Shrink GO Use Test_Shrink GO |
Create second sample table with clustered index:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE tbl_test2 ( id INT IDENTITY(1,1) ,tag CHAR (7000) DEFAULT 'dbrnd' ) GO CREATE CLUSTERED INDEX pk_test2 ON tbl_test2 (id); GO |
Insert few sample records in both the tables:
|
1 2 3 4 5 |
INSERT INTO tbl_test1 DEFAULT VALUES; GO 1500 INSERT INTO tbl_test2 DEFAULT VALUES; GO 1500 |
Check the average fragmentation of Index:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats ( DB_ID (N'Test_Shrink'), OBJECT_ID (N'tbl_test2'), 1, NULL, 'LIMITED' ) GO --The result is: --0.33% |
Drop one sample table to test Shrink:
|
1 2 |
DROP TABLE tbl_test1; GO |
Shrink your database:
|
1 2 |
DBCC SHRINKDATABASE (Test_Shrink); GO |
Check the fragmentation of index:
You can find below result like 99.6% fragmentation for Index. Before shrink database, it was just 0.33%.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats ( DB_ID (N'Test_Shrink'), OBJECT_ID (N'tbl_test2'), 1, NULL, 'LIMITED' ) GO --The result is: --99.6% |
Now, REBUILD your index to remove the fragmentation:
|
1 2 |
ALTER INDEX pk_test2 ON tbl_test2 REBUILD GO |
Check the fragmentation of index:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats ( DB_ID (N'Test_Shrink'), OBJECT_ID (N'tbl_test2'), 1, NULL, 'LIMITED' ) GO --The result is: -- 0% |
Leave a Reply