InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 14:43:55 GMT20http://www.sqlservercentral.com/Forums/Topic1052760-149-1.aspxGood One....;-)Sat, 05 Mar 2011 13:22:57 GMTseven.srikanthhttp://www.sqlservercentral.com/Forums/Topic1052760-149-1.aspxOkay thank you. There seems to be an essential, yet so vague distinction between the [i]initial size[/i] and [i]default size[/i] of a database.Conclusions by this thread:- [i]ALTER DATABASE ... MODIFY FILE[/i] with [i]SIZE[/i] parameter alters only the [i]default size[/i], [b]not[/b] the [i]initial size[/i].- The [i]initial size[/i] is set when you create your database, using the [i]SIZE[/i] parameters.- The only way to change [i]initial size[/i] is to use [i]DBCC SHRINKFILE[/i] with the [i]target_size[/i] parameter. Note: the initial size cannot be increased, ie. specifying a larger size as the target_size has no effect.- A workaround to increase [i]initial size[/i] is to create a new database with larger size, and migrate everything (structure and data)- The behavior of [i]DBCC SHRINKFILE / SHRINKDATABASE[/i] contradicts the documentation: They ignore the [i]default size[/i], but will not shrink below the [i]initial size[/i] (unless you set the target_size parameter)- [b]SQL Server Management Studio GUI adds even more to the confusion:[/b] if you check the properties of a database, the [i]Files[/i] page has a misleading [i]Initial size[/i] column, which actually gives you the [i]default size[/i] value. Changing this value translates to the following T-SQL: [i]ALTER DATABASE [x] MODIFY FILE ( NAME = N'x_Data', SIZE = xKB )[/i]Wed, 23 Feb 2011 18:15:26 GMThemulhttp://www.sqlservercentral.com/Forums/Topic1052760-149-1.aspx[quote][b]hemul (2/21/2011)[/b][hr]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[quote]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 [b][u]ALTER DATABASE[/u][/b].[/quote]So the question is whether there's something wrong the documentation or the command itself?[/quote]Well then, the documention is wrong - not the command.Mon, 21 Feb 2011 17:28:19 GMTJeffrey Williams 3188http://www.sqlservercentral.com/Forums/Topic1052760-149-1.aspxHi, 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[quote]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 [b][u]ALTER DATABASE[/u][/b].[/quote]So the question is whether there's something wrong the documentation or the command itself?Mon, 21 Feb 2011 16:01:24 GMThemulhttp://www.sqlservercentral.com/Forums/Topic1052760-149-1.aspxIf 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 [b][i]initial_size[/i][/b] <> [b][i]default_size[/i][/b] - 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.Mon, 21 Feb 2011 13:36:52 GMTJeffrey Williams 3188http://www.sqlservercentral.com/Forums/Topic1052760-149-1.aspxI must have encountered a bug in SQL Server 2005:When you increase the initial size of a database using [i]ALTER DATABASE[/i], the [i]DBCC shrinking commands[/i] ignores that size.The following code illustrates this:[code="sql"]/* 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 = 4096MBTestdb2_data.mdf = 3MB (expected result: 4096MB)*/DBCC SHRINKDATABASE('Testdb1')DBCC SHRINKDATABASE('Testdb2')GO[/code]Mon, 21 Feb 2011 06:11:24 GMThemulhttp://www.sqlservercentral.com/Forums/Topic1052760-149-1.aspxThanks, 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:[quote][i][b]DBCC SHRINKFILE, target_size:[/b][/i] 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, [u][i][b]or the last size set by using ALTER DATABASE.[/b][/i][/u][/quote]http://technet.microsoft.com/en-us/library/ms189493%28SQL.90%29.aspxFirst 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.Thu, 27 Jan 2011 14:38:26 GMThemulhttp://www.sqlservercentral.com/Forums/Topic1052760-149-1.aspxAs 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.Wed, 26 Jan 2011 15:43:17 GMTJeffrey Williams 3188http://www.sqlservercentral.com/Forums/Topic1052760-149-1.aspxOf course, setting a fixed size with no autogrowth has its own inherent risks.Wed, 26 Jan 2011 11:38:53 GMTPam Brisjarhttp://www.sqlservercentral.com/Forums/Topic1052760-149-1.aspxIf 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).Tue, 25 Jan 2011 14:44:21 GMTDerrick Smithhttp://www.sqlservercentral.com/Forums/Topic1052760-149-1.aspxWhy are you using shrink? Shrinking a database is normally a bad idea. It got that big because it needs to be that big.Tue, 25 Jan 2011 14:33:13 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic1052760-149-1.aspxSorry, 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.Mon, 24 Jan 2011 16:43:44 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1052760-149-1.aspxHi, 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)[quote][i][b]DBCC SHRINKFILE, target_size:[/b][/i] Is the size for the file in megabytes, expressed as an integer. [b]If not specified, DBCC SHRINKFILE reduces the size to the default file size.[/b] The default size is the size specified when the file was created, [b]or the last size set by using ALTER DATABASE[/b].[/quote]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:[code]/* 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 TestdbGO/* 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[/code]Can someone please explain this behavior to me?Thank you!Mon, 24 Jan 2011 16:29:32 GMThemul