October 3, 2009 at 8:46 pm
Does SQL allocate the 'needed' free space in a Database, or is this based on configuration or in the DBA's skilled hands?!?
Thanks
Dan
October 3, 2009 at 10:31 pm
SQL Server will allocate a space to database if you had autogrowth setting on. Also , It will only extents the space when needed its not going maintain the free space say like 30%. I would prefer you to monitor your database growth over the period and have atleast 20-25% free space in the database for good maintenance practice
October 4, 2009 at 6:19 am
Prabhakaran
In case of the data & index separated into 2 filegroups as mdf and ndf files, is the 20-25% free space recommendation apply to both?
Dan
October 4, 2009 at 6:22 am
Yes
October 4, 2009 at 5:17 pm
The most common reason for database file (not log file) expansion in a mature database is reindexing. As an index rebuild requires a new copy of the index to be created that will co-exist with the existing index for a short period, each file needs enough free space to hold a second copy of the largest index in that file (this can be circumvented by using "with drop existing", but then there's the trade-off of the longer duration).
As always, there's lots of provisos with this statement, the most common being:
1. If data is added in sorted order (eg. a clustered index on the datetime) then index fragmentation will be very slow, so the need to rebuild an index will be very infrequent, which in turn may mean that the database file fills before the index needs rebuilding.
2. Heaps never need reindexing.
The log file is much more difficult to predict because open transactions aren't cleared by a log backup (or checkpoint in Simple recovery). Again however, the largest transaction for a majority of databases is the rebuild of the largest index, so your log file will need to be that size plus around 20% for "overhead". Of course, this also depends on several things, the most important being the frequency of the log backups and the database recovery mode.
So having said all that, nothing beats monitoring your databases. Track the file size and used size of your database files and the size of the log file (you're unlikely to see when the log file is full because that is generally transient, and it doesn't really matter anyway).
Set up your database and log files to auto-grow but also extrapolate the captured size/usage data and manually grow the files in advance of when an auto-growth might be triggered whenever possible: our rule-of-thumb is to expand files once each year, but that needs to be balanced against the cost of disk, etc. That way you can grow the files in appropriate chunks but you've got the auto-growth as a safety-valve should something unexpected happen. Now that SQL2k5 and above don't need to zero-out database files it's less of an issue, but growing by larger chunks is better than growing by lots of little chunks (especially log files, but I won't go there: this is already getting overly long) and learning your database "patterns" is one of the most important things you can do.
October 4, 2009 at 6:26 pm
Glenn
Thanks for stressing the importance of watching the log and data file sizes. If you have a tSQL code for this that does a good job, kindly share. If not, I will script one soon. I have one for the TempDB but really I should be working on it to improve it in what it collects.
What is zero-out database files?
Also, I learnt the importance of log file size in relation to Reindex needs. Thanks so much for that explanation.
A question on your note on the log file.
Doesn't Simple Recovery Checkpoint automatically clear the transactions when the log is 70% full; and even in FULL recovery method, after the log backup, the checkpoint will clear off the Transactions from the log, right?
Thanks
Dan.
October 4, 2009 at 11:18 pm
G'day Dan,
In answer to your questions:
1. To monitor the size of files use the "sys.files" DMV. To monitor the usage of files, I use "dbcc showfilestats".
2. "Zero-out" the files is the initialisation of the files, writing zeroes to every page of the newly-allocated space. Prior to SQL2k5 this was required, but SQL2k5 and above don't need to do that so long as the SQL Server service account has " Perform Volume Maintenance Tasks" rights on the server.
3. The log can only be emptied up to the start of the earliest open transaction (or the earliest non-replicated transaction if the database is a publisher). This is true regardless of the recovery model; the only thing that differs is the "trigger" for clearing the log file. In Simple recovery the trigger is a checkpoint, whereas in Full or Bulk-logged it is the next checkpoint after a log backup. How full the log file is has no bearing on what is cleared: SQL Server will clear out everything it can every time it is cleared.
You can't rely on Simple recovery to keep your log file small. For example, if you do a Bulk Insert or an index rebuild when the log file is small & empty and there's no other activity, the log file will grow to the same size whether the recovery model is Simple or Full: the only difference will be that it will get cleared once the load is finished in Simple mode, whereas in Full mode it won't get cleared until after the next log backup.
October 6, 2009 at 6:12 am
Glenn
i did not know Instant initialisation was also called "Zero-out". You gave a good explanation of the difference Full and Simple in terms of log handling.
thx
Dan
October 6, 2009 at 3:30 pm
G'day Dan,
"Zero-out" is one of those jargon terms that I probably should avoid in the future in such replies. I know what I mean, but that doesn't mean everyone does, and different terms can have very different meanings to other people, especially in a world-wide forum (eg. here in Oz a "thong" is an item of footwear).
Anyway, glad to be of help, and thanks for the feedback.
Glenn
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply