Tags

This is in response to the question about detailing how DBCC SHRINKDB works. The confusion typically arises from the fact that in DBCC SHRINKDB, Target Percentage is not Target Size that database should be shrunk to but rather Target Percent Free Space in Database Post Shrink Operation.

It needs one more reminder that DBCC SHRINKDB or SHRINKFILE will induce fragmentation and are not recommended in Production Systems that typically backend OLTP type of data usage patterns.

For Syntax and Books Online explanation please refer below link:

http://msdn.microsoft.com/en-us/library/aa258287(SQL.80).aspx

How we approach before we plan for ShrinkDB:

Think of each data file (assuming there are multiple data files) in the database as 100%. In the data file there will

* User Data, Meta Data all falling under Data.

* Free space for future use for both User Data and Meta Data.

Sum of these should be 100% (irrespective of the size of Data file)

To reiterate further, say we have a database of 100 GB with 30 GB Data and 70 GB Free, it is 30% filled with Data and 70% Free Space.

When we try to Shrink using DBCC SHRINKDB command (not ShrinkFile), we are only Shrinking Free Space (%) from Data File and we are not Shrinking Data Portion.

So, in above example our data % was 30 and if DBCC SHRINKDB was run with 80% as Target Percent, DBCC SHRINKDB would not shrink database but would return a message that it could not shrink. Reason being

30% of Data + 80% of Free spaces amounts 110% which is not mathematically possible.

Math behind calculations:

As regards to exact math, we tried with different permutations and came up with best possible match that suits results. May be a Microsoft team can only answer that.. but our postulate is something as below..

D = Data Size in the Data File.

X = Target Data File Size post Shrink Percentage.

P = Percentage Specified in DBCC SHRINKDB operation.

D + (X * P / 100) = X

Say for example in our case

Data File is 10 MB with 6 MB Data and 4 MB free Space. If we Shrink this Database with DBCC SHRINKDB with Target Percentage of 25% then

D (6 MB) + X (we do not know this Yet) * P (25) / 100 = X

=> 6 + .25X = X => 6 = .75 X = 8 MB.

So post Shrink Operation data file size should be 8 MB.

Now let us say we shrunk Database with DBCC SHRINKDB with Target of say 50% (Remember our Free space was only 40%). As per our math

=> 6 + .5X = X => 6 = .5X = 12 MB (File should be expanded and not Shrunk!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! :)) atleast DBCC SHRINKDB does not expand file size for a Shrink Operation. 

Disclaimer: This one is fairly accurate for large databases but not for smaller ones.. And we are not sure about

* If such calculations are made in Pages (Database pages of 8KB)

* Converted to Bytes or Kilo Bytes for Calculation Purposes

* Is it Integer or Float Math

Other Things to Keep in Mind:

=> Database can not be shrunk to smaller than it was originally created. Like the mistake once we did, Create a database of 100 GB initial size which was never used and now shrink would not work as it was the size with with database was created.

During the course of working on this article found one interesting UNDOCMENTED DBCC COMMAND which seems very useful. Refer this blog http://blogs.msdn.com/psssql/archive/2008/03/24/how-it-works-dbcc-shrinkdatabase-shrinkfile-tabular.aspx

Getting the proof:

Below are logical steps that would be needed to perform the test

1. Create a Database, 2. Create 3 Tables, 3. Fill the data in these tables in Alternative manner so that data pages from these tables are interleaved, 4. Drop or Truncate 2 of the tables  (remember only drop and truncate would enable database to reclaim space from object not delete operations). Shrink Database.

 

Create Database TestShrinkDatabase
on 
(
   Name = 'TestShrinkDB',
   FileName = 'C:\TestShrinkDB.mdf',
   size = 10 MB
)
log on
(
   Name = 'TestShrinkDB_log',
   FileName = 'C:\TestShrinkDB.ldf',
   size = 10 MB
)
go
Use TestShrinkDatabase
go
--Create Table 1
Create table Test(i char(4000))
go
--Populate Table 1
Declare @i int
Set @i = 1
While @i < 100
Begin
   Insert into Test  Values(@i)
   Set @i = @i  +1
End
--Create Table 2
Create table Test_Dummy_1 (i char(4000))
go
--Populate Table 2
Insert into Test_Dummy_1
select * from Test
--Populate Table 1 again
Declare @i int
Set @i = 101
While @i < 1000
Begin
   Insert into Test  Values(@i)
   Set @i = @i  +1
End
go
--Create and Populate Table 3
Select * into Test_Dummy_2 from Test
go
--Populate Table 2 (Beware of this go 10.. this is what filled up our database)
Insert into Test_Dummy_1
Select * from Test_Dummy_1
go 10

--Populate Table 3 (Beware of this go 10.. this is what filled up our database)
Insert into Test_Dummy_2
Select * from Test_Dummy_2
go 5
--Populate Table 1 again
Insert into Test
Select * from Test
go 

To find out database properties we would use

* SP_SpaceUsed, Database Properties Screen Shots, and Database File Properties Screen shots and use Shrink file option not for Shrinking but just for checking Data usage.

Database Properties after Data is loaded:

Output of sp_spaceused:

database_name        database_size      unallocated space

TestShrinkDatabase       7162.19 MB                   0.22 MB

reserved           data               index_size         unused

4701984 KB         4700648 KB         608 KB                     728 KB

Database Properties:

image

Database File Sizes:

image

We see that Database is around 7 GB (as said we filled too much data and you can reduce if needed) with data file around 4.5 GB and Log file around 2.5 GB.

Post table Drop (Table 2 and Table 3) operation:

Output of SP_Spaceused:

database_name          database_size      unallocated space

TestShrinkDatabase               7162.19 MB         4091.80 MB

reserved           data               index_size         unused

512200 KB          511464 KB                 584 KB             152 KB

Database Properties:

image

Database File Sizes:

image

See the UnAllocated space from SP_Spaceused it is around 4 GB and Data only around 511 MB (highlighted in red bold)

As per our calculation we have data around 501 MB and if we Shrink with Target Percent of 50 then

D (501) + .5 X = X ==> 501 = .5 x = 1 GB for Data File.

Shrink with 50%

image

 

Post Shrink Operation:

Sp_SpaceUsed:

database_name       database_size      unallocated space

TestShrinkDatabase      1020.56 MB                   500.19 MB

reserved           data               index_size         unused

512192 KB          511464 KB          576 KB             152 KB

Database Properties:

image

Data Files:

image 

To summarize again, when running DBCC SHRINKDB,

=> Target Percentage is Target free space that needs to be present post shrink operation

=> DBCC ShrinkDB will only Shrink files to their Initial Size (Size specified when creating Database or When Alter Database with different size)

=> Sum of Data Percentage and Free Space Percentage should not be greater than 100%

Hope this article achieves the goal of explaining the how DBCC SHRINKDB works, in case of any comments please feel free to send mail to gurucb@hotmail.com

Advertisements