January 24, 2011 at 4:29 pm
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!
January 24, 2011 at 4:43 pm
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.
January 25, 2011 at 2:33 pm
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
Consultant - Straight Path Solutions
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
January 25, 2011 at 2:44 pm
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).
January 26, 2011 at 11:38 am
Of course, setting a fixed size with no autogrowth has its own inherent risks.
January 26, 2011 at 3:43 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 27, 2011 at 2:38 pm
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.
February 21, 2011 at 6:11 am
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
February 21, 2011 at 1:36 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 21, 2011 at 4:01 pm
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?
February 21, 2011 at 5:28 pm
hemul (2/21/2011)
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?
Well then, the documention is wrong - not the command.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 23, 2011 at 6:15 pm
Okay thank you. There seems to be an essential, yet so vague distinction between the initial size and default size of a database.
Conclusions by this thread:
- ALTER DATABASE ... MODIFY FILE with SIZE parameter alters only the default size, not the initial size.
- The initial size is set when you create your database, using the SIZE parameters.
- The only way to change initial size is to use DBCC SHRINKFILE with the target_size parameter. Note: the initial size cannot be increased, ie. specifying a larger size as the target_size has no effect.
- A workaround to increase initial size is to create a new database with larger size, and migrate everything (structure and data)
- The behavior of DBCC SHRINKFILE / SHRINKDATABASE contradicts the documentation: They ignore the default size, but will not shrink below the initial size (unless you set the target_size parameter)
- SQL Server Management Studio GUI adds even more to the confusion: if you check the properties of a database, the Files page has a misleading Initial size column, which actually gives you the default size value. Changing this value translates to the following T-SQL: ALTER DATABASE [x] MODIFY FILE ( NAME = N'x_Data', SIZE = xKB )
March 5, 2011 at 1:22 pm
Good One....;-)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply