Question about dbcc shrinkfile

Question

Question about dbcc shrinkfile

SimonLiew

SSCrazy Eights

Points: 8304
More actions
April 16, 2012 at 12:58 am

#254100

Folks,
Please note that this question is for educational purpose, not that I would perform shrink database.
According to msdn, quote : if a database was originally created with a size of 10 MB and grew to 100 MB, the smallest size the database could be reduced to is 10 MB, even if all the data in the database has been deleted
http://msdn.microsoft.com/en-us/library/ms189035(v=sql.105).aspx
I've tested by following steps
1. Created an initial database with 10MB data, and 100MB transaction log
2. Set auto-growth of 10MB to data file
3. Created a table, and perform insert
create table simon (col1 int, col2 char(2000))
set nocount on
declare @i int = 1
while @i < 100000
insert into simon values (@i, 'asdfaljglsjdfgljsdlfjglksjdfgkl')
4. Truncate table simon
5. Ran shrinkfile to 9MB ie. dbcc shrinkfile(1, 9)
This seem to be working fine. Data file is now 9MB
6. Ran shrinkfile again to 7MB i.e. dbcc shrinkfile(1, 7)
This seem to be working fine too. Data file is now 7MB
From msdn description, I shouldnt be able to shrink the database below 10MB. I'm really intrigued by this article. Am I doing something wrong or understand the article wrongly? I've tested with different initial database sizes and all cases I was able to shrink the database below the initial specified size.
Version of my SQL is Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Comments much appreciated.
Thanks,
Simon
Simon Liew
Microsoft Certified Master: SQL Server 2008

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

That MSDN article could certainly be made a lot less ambiguous.

On the one hand, it's true, you can't shrink a database below its original size using DBCC SHRINKDATABASE (which is what the article refers to).

On the other hand, you can shrink a database below its original size using DBCC SHRINKFILE, which http://msdn.microsoft.com/en-us/library/ms189493.aspx clearly states in the first paragraph.

SimonLiew SSCrazy Eights Points: 8304 More actions · Answer 2

Thanks, gotcha.

I hardly use dbcc shrinkdatabase for shrinking dev database and only ever use shrinkfile. So, I didnt thought the article was specifically referring to shrinkdatabase.

Simon Liew
Microsoft Certified Master: SQL Server 2008

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 3

Although your question has already been well answered, it might be helpful for you to know that using SHRINKFILE frequently with a database that will grow beyond limits over and over again, will result in fragmentation on your disks at the OS-level and eventually (depending on the "amount" it grows/versus how much it's shrunk), it will result in poor performance.

Disk fragmenting a single large disk takes a long time...try defragging an entire array... :w00t:

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

SimonLiew SSCrazy Eights Points: 8304 More actions · Answer 4

Do you defrag SAN? We don't. Any special utility that you're using?

Simon Liew
Microsoft Certified Master: SQL Server 2008

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 5

No we don't have a SAN and we have our Tech Services department perform the defrag. This has only ever happened once and it took an entire weekend to perform so since that "incident", we size out DB files accordingly and RARELY shrink the files.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

SimonLiew SSCrazy Eights Points: 8304 More actions · Answer 6

Thanks. I was just curious because few sources include our SAN vendor advices us against defragging SAN.

Simon Liew
Microsoft Certified Master: SQL Server 2008

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

I don't know, I defragged files on our SAN's. There were times it was the only way to reclaim sufficient contiguous disk space for other databases.

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 8

Simon-413722 (4/16/2012)
Please note that this question is for educational purpose, not that I would perform shrink database.

Simon-413722 (4/16/2012)
I hardly use dbcc shrinkdatabase for shrinking dev database and only ever use shrinkfile.

ROFLMAO! 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SimonLiew SSCrazy Eights Points: 8304 More actions · Answer 9

Jeff Moden (4/17/2012)
Simon-413722 (4/16/2012)
Please note that this question is for educational purpose, not that I would perform shrink database.
Simon-413722 (4/16/2012)
I hardly use dbcc shrinkdatabase for shrinking dev database and only ever use shrinkfile.
ROFLMAO! 😛

Those dev database are personal use in nature 😀 Currently cramming for MCM this coming Jul or Aug.

Simon

Simon Liew
Microsoft Certified Master: SQL Server 2008