Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
You didn't really specify, but if you are trying to shrink back to 10 MB, is there even any data left? Are you doing this after completely wiping out the database? If so, and it's something you're doing often with some development database, maybe it would be better to just restore a copy of the database in its initial state. –  Aaron Bertrand Jan 16 at 14:53
2  
Regarding your EDIT: No you can't with DBCC SHRINKDATABASE. However you can with DBCC SHRINKFILE and specify the Targetsize parameter as I did in my answer. Mind you this only works if there insn't any data. By know, I'm curious on what you want to accomplish. –  Edward Dortland Jan 17 at 8:06
    
@EdwardDortland See my edit on what I really wanted to do. –  IT researcher Jan 17 at 9:37
1  
Okay, ALTER DATABASE [Test] MODIFY FILE ( NAME = N'Test', SIZE = 2048MB ) won't work since your 10GB file will have 1 GB data scattered across the the whole file. So you need to use DBCC SHRINKFILE(1,2048) because that command will move all scattered data pages to the beginning of the file. Once that is done, the command can remove the 8GB of empty space at the end of the file because it knows that all the data is at the beginning of the file. It can't be done by 'just' changing the size of the database file. –  Edward Dortland Jan 17 at 9:47
    
Ok. I know that changing the file size will not shrink it. The shrinkdatabse command will shrink the database to a size which is mentioned during creating database(think of scenario where no data exist in database).In my case 3 mb for data and 1 mb for log. What if I was able to change this 3mb and 1 mb value itself? Where this property(initial size) is stored and how shrinkdatabase gets this value?It should be storead somewhere which user can modify or in system datbase which does not be modified by user –  IT researcher Jan 17 at 10:23
show 1 more comment

2 Answers

up vote 6 down vote accepted

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

enter image description here

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 )

enter image description here

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.

share|improve this answer
    
Ok.I think all of you confused with my question or my question itself is confusing!!. My question is how to change the initial size of that database to some other value so that shrinkdatabase or shrinkfile command without size specified will shrink it to the initial size. So now i want to change initial size of database itself.can it be done? –  IT researcher Jan 17 at 6:03
    
U have answered for my question "how come that DBCC SHRINKFILE or DBCC SHRINKDATABASE "know" the initial size" excellentlly. That is what I wanted. This question was more of curiosity for me . Also how to read the file header properties?(You have shown it in your answer.) Also you had doubts like why I want to alter the initial size. That is because if i was able to do it then i can set this value for the database as required and i can simply use shrinkfile command without size and it will shrink to the size i wanted. No need of specifying size for each db every time i do shrink. –  IT researcher Jan 17 at 11:51
    
@ITresearcher, I've added the code to show the fileheader page info. DBCC PAGE. –  Edward Dortland Jan 17 at 12:59
    
Ok.Thank you for a nice explanation –  IT researcher Jan 18 at 4:43
add comment

@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.

share|improve this answer
    
Ok.I think all of you confused with my question or my question itself is confusing!!. My question is how to change the initial size of that database to some other value so that shrinkdatabase or shrinkfile command without size specified will shrink it to the initial size. So now i want to change initial size of database itself.can it be done? –  IT researcher Jan 17 at 6:05
    
Also see my question with edited part –  IT researcher Jan 17 at 6:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.