Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

DBCC SHRINKFILE shrinks below specified file size Expand / Collapse
Author
Message
Posted Monday, January 24, 2011 4:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 28, 2012 6:16 AM
Points: 12, Visits: 118
Hi, I'm trying to figure out why DBCC SHRINKFILE doesn't adhere to the specified file size of my database.
I want my data file to have a fixed size of 4096 megabytes, no more, no less.
So I use ALTER DATABASE to specify the new size.
But when I tried to shrink my database, it shrunk below the minimum size. Bummer!
I started investigating how the shrink command works.

Here is what Microsoft says about DBCC SHRINKFILE: (http://technet.microsoft.com/en-us/library/ms189493%28SQL.90%29.aspx)
DBCC SHRINKFILE, target_size: Is the size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size. The default size is the size specified when the file was created, or the last size set by using ALTER DATABASE.


However, my experience is that the shrink command doesn't behave this way.
I have put together the following T-SQL code to illustrate this:

/* Create database, with default size of 4096 megabytes. */
CREATE DATABASE Testdb ON PRIMARY
(NAME = N'Testdb_data', FILENAME = N'C:\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Testdb_data.mdf' , SIZE = 4096MB, FILEGROWTH = 0)
LOG ON
(NAME = N'Testdb_log', FILENAME = N'C:\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Testdb_log.ldf' , SIZE = 1024KB, FILEGROWTH = 10%)
GO

/* Use the new database */
USE Testdb
GO

/* Shrink the data file, no size specified = use default file size.
Size won't go below 4096 megabytes, Windows Explorer confirms this.
Repeatedly executing this command doesn't change the data file size.
This is expected behavior. */
DBCC SHRINKFILE (N'Testdb_data')
GO

/* Shrink the data file to 1024 megabytes.
Size does indeed change to 1024 megabytes, Windows Explorer confirms this.
This is expected behavior. */
DBCC SHRINKFILE (N'Testdb_data', 1024)
GO

/* Alter database, change default data file size to 4096 megabytes again.
Data file grows from 1024 to 4096 megabytes, Windows Explorer confirms this.
This is expected behavior. */
ALTER DATABASE Testdb MODIFY FILE (NAME = N'Testdb_data', SIZE = 4096MB, FILEGROWTH = 0)
GO

/* Shrink the data file, no size specified = use default file size. (ie. 4096 megabytes)
Data file shrinks to 1024 megabytes again, Windows Explorer confirms this.
Why??? This is confusing to me. I explicitly set the default size to 4096 megabytes using ALTER DATABASE below.
Expected behavior: data file should not shrink below default size of 4096 megabytes. */
DBCC SHRINKFILE (N'Testdb_data')
GO

Can someone please explain this behavior to me?
Thank you!
Post #1052760
Posted Monday, January 24, 2011 4:43 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:22 PM
Points: 20,680, Visits: 32,279
Sorry, lost. You tests show that if you don't specify the size, it won't go below the default size. If you indicate a size that is smaller that it shricks it to the size specified.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1052764
Posted Tuesday, January 25, 2011 2:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:11 AM
Points: 10,193, Visits: 13,121
Why are you using shrink? Shrinking a database is normally a bad idea. It got that big because it needs to be that big.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1053542
Posted Tuesday, January 25, 2011 2:44 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 12:57 PM
Points: 575, Visits: 692
If you want the database to stay at exactly 4gb, then just set its initial size to that and disable autogrowth. There is no need to shrink it after that (which is also very bad for the database, as mentioned).
Post #1053549
Posted Wednesday, January 26, 2011 11:38 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 1, 2014 7:26 AM
Points: 908, Visits: 2,804
Of course, setting a fixed size with no autogrowth has its own inherent risks.
Post #1054100
Posted Wednesday, January 26, 2011 3:43 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:26 AM
Points: 4,358, Visits: 9,536
As everyone else has already stated - you shouldn't be shrinking the data file.

With that said, to answer your question - DBCC SHRINKFILE is working as expected. When you grow a data file, the initial size does not change. The initial size of the file is defined when you initially create the data file, or when you explicitly shrink the data file using the target size.

In your outline, when you performed the shrink to 1024 - then grew the file back to 4096 the initial size of the file is still 1024. When you performed the last shrink without a target size it was able to shrink to the initial size of the file.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1054278
Posted Thursday, January 27, 2011 2:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 28, 2012 6:16 AM
Points: 12, Visits: 118
Thanks, I know I shouldn't be shrinking my database. I'm just trying to figure out how shrinking works and how it relates to the initial/default file size.

I argue that DBCC SHRINKFILE is not working as expected. Let me quote Microsoft again:
DBCC SHRINKFILE, target_size: Is the size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size. The default size is the size specified when the file was created, or the last size set by using ALTER DATABASE.

http://technet.microsoft.com/en-us/library/ms189493%28SQL.90%29.aspx

First I execute ALTER DATABASE to change the default size to 4096 megabytes.
Then I execute DBCC SHRINKFILE without specifying target_size.
DBCC SHRINKFILE ignores the "last size set by using ALTER DATABASE" and shrinks to 1024 megabytes.
For me this raises a couple of questions:
-How do you change the "initial size" of a database after you created it?
-When you execute ALTER DATABASE and specifies a new SIZE, does that change the "initial size"?
-Documentation refers to "default size", is that the same as the "initial size"?

Thanks.
Post #1054949
Posted Monday, February 21, 2011 6:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 28, 2012 6:16 AM
Points: 12, Visits: 118
I must have encountered a bug in SQL Server 2005:
When you increase the initial size of a database using ALTER DATABASE, the DBCC shrinking commands ignores that size.
The following code illustrates this:

/* Create Testdb1 with initial size = 4096MB */
CREATE DATABASE Testdb1 ON PRIMARY
(NAME = N'Testdb1_data', FILENAME = N'C:\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Testdb1_data.mdf', SIZE = 4096MB, FILEGROWTH=1024KB)
LOG ON
(NAME = N'Testdb1_log', FILENAME = N'C:\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Testdb1_log.ldf', SIZE = 1024KB , FILEGROWTH=10%)
GO

/* Create Testdb2 with initial size = 3MB */
CREATE DATABASE Testdb2 ON PRIMARY
(NAME = N'Testdb2_data', FILENAME = N'C:\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Testdb2_data.mdf', SIZE = 3MB, FILEGROWTH=1024KB)
LOG ON
(NAME = N'Testdb2_log', FILENAME = N'C:\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Testdb2_log.ldf', SIZE = 1024KB , FILEGROWTH=10%)
GO

/* Increase initial size of Testdb2 to 4096MB */
ALTER DATABASE Testdb2 MODIFY FILE (NAME = N'Testdb2_data', SIZE = 4096MB)
GO

/*
Shrink both databases. Here is the result:
Testdb1_data.mdf = 4096MB
Testdb2_data.mdf = 3MB (expected result: 4096MB)
*/
DBCC SHRINKDATABASE('Testdb1')
DBCC SHRINKDATABASE('Testdb2')
GO

Post #1067063
Posted Monday, February 21, 2011 1:36 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:26 AM
Points: 4,358, Visits: 9,536
If you truly think this is a bug (it isn't), then put in your issue at Connect and let Microsoft know.

Now, the reason this is not a bug is because initial_size <> default_size - and the only way the initial size is defined is either:

1) When the database is created...
2) When the size is changed using DBCC SHRINKFILE with the target_size parameter.



Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1067302
Posted Monday, February 21, 2011 4:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 28, 2012 6:16 AM
Points: 12, Visits: 118
Hi, thanks for your input. If this is the expected behavior of the shrink command, then the documentation is wrong or misleading:
http://msdn.microsoft.com/en-us/library/ms190488(v=SQL.90).aspx
The database cannot be made smaller than the minimum size of the database. The minimum size is the size specified when the database is originally created, or the last size explicitly set by using a file size changing operation such as DBCC SHIRNKFILE or ALTER DATABASE.

So the question is whether there's something wrong the documentation or the command itself?
Post #1067354
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse