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, February 21, 2011 5:28 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1067367
Posted Wednesday, February 23, 2011 6:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 28, 2012 6:16 AM
Points: 12, Visits: 118
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 )
Post #1068620
Posted Saturday, March 5, 2011 1:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 9:16 AM
Points: 38, Visits: 446
Good One....
Post #1073764
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse