Shrink the database only up to its initial size which is set after creating database

Question

In SQL server every database has a property Initial Size (MB) which can be seen in properties of database in SSMS. By default it will be 3 MB for mdf and 1 MB for ldf file.

So now if create a new database then it will be set to default size(i.e 3 MB for mdf and 1 mb for ldf file). But after creating the database I change the initial size to some other value say For mdf 10 MB and ldf 5 MB.(In real-time some database administrator may want to change initial after creating database)

But now if shrink the database it will shrink beyond the initial size I have set after creation(consider that no data is there in database otherwise it will shrink to its actual content size). It will shrink till the initial size it had during creation of database(i.e 3mb for mdf and 1 mb for ldf). I was expecting to shrink till the initial size which I have set(i.e 10 mb for mdf and 5mb for ldf). Can it be possible to do? If possible then how?

In some article i saw like dbcc SHRINKFILE can be used to shrink beyond initial size and dbcc SHRINKDATABASE cannot shrink beyond initial size. But I want to shrink only till the initial size to which I have set.

Note: I know that shrink is bad and should not be done.But I want to know how it can be done? If sql server considers only the initial size set during creation of database then what is the use of setting the initial size after creating database?

EDIT:

From microsoft shrink related article: Using DBCC SHRINKDATABASE you can not shrink database below its initial size.

My question is Can I change the initial size of existing database itself?

EDIT:

I have about 5 sql server instance. All the database where created with default size of 3 mb for data and 1 mb for log(about 3 years back). Now all the databases are in the range of 100-1000 mb. I wont shrink the database normally as it is not good practice. But some database grow more than 10 GB(mdf file itself as i insert so much data in it). So when database is large I will shift unimportant and old contents from this database to another server as it is not required in that server for me. So that database size reduces from 10 GB to 1 GB. Now the remaining 9 Gb left unused. Now I want to specify database size to be reduced to 2 GB so that i will get 1 GB free space(Also 2 Gb is the ideal size for this database), Which can be used to avoid autogrowth till next 1 Gb of data is filled. Also i have set 100 MB auto-growth for this kind of large database which has more writes. So instead of specifying size as 2 GB in shrinkfile command,if it was possible to change initial size to 2 GB (which shrink considers) then it would have been better. During this database creation there was no plan about size ,autogroth etc. As it was created about 3 years back. Now i am planning to set autogrowth etc. I cant again create new database with new initial size and shift data. So I was asking whether I can change the Initial size for existing database?

Answer 1

Initial size is not just 3MB, it is taken from the model database (if not specified during the creation of your user database.) So assuming you haven't specified a initial size during the creation of your user db and you haven't altered the model database file sizes after you have created your userdb you can do the following:

--Create testDB
CREATE DATABASE [TEST_100]
GO


--grow your database file size
ALTER DATABASE [Test] MODIFY FILE ( NAME = N'Test', SIZE = 100MB )
GO


--switch context
USE [TEST_100]
GO



--Find size of modelDB mdf file, this is your initial file size used for the userdb
DECLARE @TargetFileSize int

SELECT  @TargetFileSize = (size * 8 / 1024)
FROM    sys.master_files 
WHERE   database_id = 3 --model database
AND     file_id= 1 --first file is mdf, assuming you have a model with just one mdf. If you have multiple files, change for the one you need to find.


--shrink the the first file of your current database to the target size that you just found.
DBCC SHRINKFILE (1,@TargetFileSize)

EDIT

Okay, some extra info is needed after your edits and comments.

First of all. I feel that the "Initial size" label that you see when you look at the file properties in SSMS is a misnomer. Basically, your intial size is just a concept. It's the first size that is used during creation of the database. You can either explicitly specify this in the CREATE DATABASE statement, or you can have SQL Server implicitly copy it from the model database by ommiting that information during creation.

However, once the database is created, from a DBA perpective there is no such thing as a "initial size" there is only one property visible for a DBA and that is: the actual size. Even the "Initial size" property in SSMS just shows actual size, not the initial size.

Well how come that DBCC SHRINKFILE or DBCC SHRINKDATABASE "know" the initial size then? Even after you have changed the size. Interesting question.

The first page of a dattabase file is the file header page. In there you have, amongst others, 2 properties: size and minsize.

At creation of the file, both file header properties get filled with the inital value:

DBCC TRACEON(3604)
--parameters for DBCC PAGE: (Dbname, fileID, pageID, outputTypeID)
DBCC PAGE('Test_100',1,0,3)with tableresults

Both sizes are in the amount of data pages. In this case. 288 data pages.

Now if I alter the file size:

ALTER DATABASE [test_100] MODIFY FILE ( NAME = N'test', SIZE = 50MB )

You can see that the "size" property is changed to reflect the new size. However, the "MinSize" property still contains the "Initial" size. It's the minimal size to which the shrink command will go.

However, having said all this. I still don't understand why you want to complicate things by first altering the initial size and then shrink to that initial size. Instead of just shrinking directly to a targetsize.

Anyway, to answer your question. The "initial" size is not exposed as a property to the user/dba.

Answer 2

@Edward has a great answer regarding how to shrink your file based on the initial size set in model. I'll answer this:

what is the use of setting the initial size after creating database?

And assume you meant:

what is the use of setting the initial size when creating database?

This can be extremely useful for both data and log files when you know in advance what your database size should be. If you are creating two databases, one for configuration data and one for transactional data, they are likely going to have very different growth patterns. So rather than take the terrible, terrible, terrible defaults (of both size and growth settings) from model, it makes a lot more sense to always customize this per database depending on its needs (even if you have changed the terrible defaults to something more reasonable, that usually isn't going to be one-size-fits-all). And you can always manually autogrow a file later, outside of busy periods, to accommodate changes to your initial predictions. Rather than just let the nature of the changes to data collection guide growth using your initial, now incorrect, settings.

One of your long-term goals for creating a database should be to minimize or completely eliminate any and all file size change events. It's not always possible to be 100% accurate in terms of capacity planning, but you should have some ballpark in mind for, say, how much data you expect to collect over the next month, year, etc. This can be a little tougher for logs, but once you know how much data you expect, you can make a rough calculation for log size with a formula like this (this idea came from Geoff Hiten and actually just crossed my desk yesterday):

(Largest index * 1.25) 
+ 
(largest amount of log created during reindex run * 2.1)

So, if your largest index is 4 GB, and your last reindex created 2 GB of log, then 4 * 1.25 + 2 * 2.1 = 9.2 GB log file. This will give you a log file that can handle your typical rebuild - including a rollback, should that occur - without having to grow the physical file.

Optimal autogrowth settings are a little trickier to predict, because it's a balance. You want to minimize the number of times the files will grow, but also minimize the time it takes to grow each time. This means you don't want a growth size of 1 MB, ever. Let's pretend you have a transaction that is going to insert 15 MB of data. It is much better to grow by 50 MB once to accommodate that growth (and the next two transactions just like that, too), than to grow in 15 independent growth events. And you probably don't want a growth size of 20 GB either, since this can take too long on its own. For log files particularly, since they can't benefit from instant file initialization, erring toward the smaller size is better, but I don't know that there is any optimal magic number without knowing the average size of your typical transaction as well as the performance characteristics of the storage where the log file resides.

Again, the whole point is to avoid growth and shrink events like the plague.

While sometimes necessary, both growth and shrink events are detrimental to the performance of your database. A growth - even with instant file initialization enabled, which is only possible for data files, since log files have to be zeroed out anyway - requires that all transactions pause while the file(s) are expanded. A shrink usually causes fragmentation which - particularly on slow I/O - can really be felt during reads of large tables. With SSDs this is becoming less and less relevant, and it's not so important when all of your data fits in memory. But barring both of those things...

What I don't get about people constantly shrinking their files is - what for? If your data or log file grew to that size once, it will grow to that size again. Freeing up the space in the meantime seems futile to me. What are you going to use the space for in the meantime? You can't put anything there because you need to leave the space free so that SQL Server can grow the file again - then you can shrink it again - then it can grow again. And so on.

For data files you need to allocate the space you're going to need long-term, not today. For log files you need to manage the space used within them by being in the right recovery model and implementing a proper backup strategy for that recovery model.

If you are shrinking files outside of an emergency or abnormal event, you need to change the way you're doing things.

• http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
• https://sqlblog.org/2009/07/27/oh-the-horror-please-stop-telling-people-they-should-shrink-their-log-files
• http://www.straightpathsql.com/archives/2009/01/dont-touch-that-shrink-button/
• http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/

Answer 3

I Assume that your log file was created with certain initial size, and now you want to decrease the initial size of ldf file?

For example MyDatabase log file was created with started Initial size 10240 MB (10 GB), but now I want to decrease this initial size for some reason (perhaps..to utilize more space) .

Unfortunately.. you cannot decrease your initial size of your Log (*.ldf) file with DBCC SHRINKFILE or ALTER DATABASE syntax while your Database State is Online.

But you can do that job with Delete & Recreate your log file, and this process require your database set to offline. This is the only way to decrease your log initial size lower than the initial size when your DB was created first time.

Here some link you need further research http://blog.sqlauthority.com/2010/04/26/sql-server-attach-mdf-file-without-ldf-file-in-database/

http://www.mytechmantra.com/LearnSQLServer/How-to-attach-database-without-a-transaction-log-file-in-SQL-Server.html