July 29, 2008 at 5:30 pm
Hi, I am a newbie DBA for our company. We have 2x SQL servers and 2-dozen or so databases. About half are the databases for off-the-shelf applications, several are the result of our in-house developer's custom applications (VB.NET) used across the business, and a few are data dumps from old systems (historic financial, payroll, etc type information) that has been loaded into SQL for viewing via SQL Reporting Services (read-only).
I have a few databases (one particularly so) that tend to grow their log files by huge amounts on a day-to-day basis. For example the one I am focusing on is approx 500MB data but the log file grows by about 1000MB (1GB) per day, and will easily fill up the log drive if left to its own devices over time.
All user databases are set to full recovery, auto-grow db and log, unrestricted size, and autoshrink enabled. They are all looked after by a nightly maintenance plan that does the following (in this order) Check integrity, full backup to disk, log backup to disk, shrinks the database 50MB/10%free, Rebuild index, update statistics, history cleanup, maintenance cleanup (to delete backups over 2weeks old).
I was under the impression that the nightly log backup should release the completed transactions from the log and the shrink database should be able to shrink/truncate the log (either the maintenance step, or the auto-shrink DB setting), but it doesn't.
So now I am considering adding another step to the maintenace plan to specifically shrink this log file. I was planning to use the following code to do it:
use mydb
backup log mydb to disk='f:\mssql2005\backups\mydb_log_backup.bak'
dbcc shrinkfile (mydb_Log,10)
But in testing, this doesn't work. The backup works fine, however the shrinkfile comes back with an error:
Cannot shrink log file 2 (mydb_Log) because all logical log files are in use.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
So reading up on this message I am suggested to check the open transactions, and the log_reuse_wait_desc value, so I run:
dbcc opentran mydb
select name, recovery_model_desc, log_reuse_wait_desc from sys.databases
which in turn reports:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(and)
mydbFULLNOTHING
Which shows me that nothing is stopping the shrink... So my questions are:
a) why can't I shrink the log...
b) how can I automatically keep the log under control using a maintenance plan or some db settings perhaps.
We would like to have point-in-time recovery so I dont want to use truncate_only.
I know about 'dont shrink the log' and am not interested in this train of thought. My concern is not performance, nor is it about keeping the log as tiny as possible at all times, it is simply to keep it under control so the drive doesn't fill up and take down all the other databases. That said I don't want to specifically limit the size of any of the files either as I believe they should all be able to 'share' the available space if the need occurs any of them to grow suddenly for whatever reason, but they should be trimmed back again that night to their 'normal' size. I'll be blowed if my company would be willing to add a couple of GBs disk space per day to the SQL logs drive just cos 'shrinking the log is a bad idea'... 🙂
I am fairly new to SQL (have done SQL Admin course and that's about it) so feel free to point out anything I may have misunderstood or I'm obviously wrong about. Cheers.
July 29, 2008 at 8:49 pm
First, shrinking the database and/or the transaction log is a bad idea. Performing this nightly is even worse, as it is going to cause file level fragmentation and will just grow again.
Please review the following to get additional information about why you should not shrink:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Second, to 'control' the size of your transaction log you have to backup the transaction log on a regular schedule. Depending upon your business requirements, that could be every hour, every 30 minutes or even every 15 minutes. Frequent backups will keep the transaction log at a reasonable size and you won't need to shrink the log file.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 29, 2008 at 9:08 pm
As mentioned above, DO NOT SHRINK the database or log file. It's unnecessary.
You should allocate space in the data file to handle growth for the next few months, and then revisit it and manually grow it periodically to handle the next 3-6 months.
As mentioned above as well, the log backups are important to free space in the log file. The log file shouldn't need to grow to shrink either. Once you perform the backup, the "free" space is reused for the next set of log transactions.
July 29, 2008 at 9:41 pm
This one particular database log file is growing approx 1GB per day, it continues to grow till the disk is full despite log and database backups. There is no way this should be allowed to happen, and no way I want to cap it at a fixed size, because when it hits that size, the app will fall over with 'log full' until its truncated anyway (wont it?).
If it's manually truncated it only grows to ~1GB in the first day before being backed up again, so why does it need to grow to 2GB the next day, then 3GB the next and so on?
How do you suggest I solve this without shrinking the log, and without adding another couple of hundred GB to the server? I don't get why it is expected that I should be throwing gigs and gigs of disk space to a log file for a database thats only half a gig. If the database is fully backed up overnight, what should be in the log immediatly after? I would expect it only needs to have transactions that have happened since that last backup, i.e. nothing!?
Sorry, I'm frustrated and confused as this makes no sense!
EDIT:
Just a side note, I found an article that said to run this command:
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files;
which I ran and it says that of the current 7.7GB log file, 6982 MB is 'Available', which indicates the log backup has removed the inactive transactions, however the file is still large. Does this help shed any light on my problem?
July 30, 2008 at 1:10 am
Since everyone's beaten me to the 'don't shrink', I'll just touch on other areas
You said the DBs are in full recovery. Do you have log backups running and, if so, how often?
What is the recoverability requirements of these databases? If the hard drive dies and you have to restore elsewhere, how much data loss is acceptable?
Do you have replication?
Do you have database mirroring?
Full backups don't truncate the transaction log, so what is in the transaction log after a full backup depends on when the previous log backup was.
Oh, and log backups don't shrink the file, they just remove unused data within the file. Repeatedly growing and shrinking the log file causes log fragmentation (many virtual log files) and possibly external file level fragmentation as well. Both ot those make your backups and recovery time longer than necessary.
Give this a read, it may help with some concepts - Recovery model and transaction logs[/url]
And maybe this on why shrinking databases is a really bad idea. Read the two articles linked at the bottom as well. Shrinking databases[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2008 at 3:47 pm
Cheers everyone, I have shrunk the log successfully by running the log backup, then the shrinkfile several times over until the shrink worked without error, as suggested on another site (due to the active portion of the log wrapping the end of the file).
This has allowed me to free up the 8GB of space currently wasted on this one log file (90% of which was empty space). The log backup task in the maintenance plan was not working it seems because when I do it manually the active portion of the log does clear to be nearly all free space and the file does not need to grow as it reuses the free space instead. So once I figure out why the maintenance plan log backup step is not working (and fix it) then the log should only grow to it's max daily size then stay there, instead of growing forever till the disk is full.
Shrinking the log was required in this case to bring the log back to a realistic size, and was the sole purpose of my post. Noone here managed to provide the solution and instead all preached the "don't shrink" doctrine without actually understanding my situation. Its all well and good to say "don't shrink", but when the log drive is full of 100's of GBs of logs (that just grow and grow), shrinking is a required step in the remedy. Of course a maintenance plan to regularly back up the logs is also required, but I did say that I had this (I wasn't aware it wasn't working however) in my orignal post. 🙂
Thanks
Matthew
July 30, 2008 at 4:04 pm
Matthew O'Gorman (7/30/2008)
Cheers everyone, I have shrunk the log successfully by running the log backup, then the shrinkfile several times over until the shrink worked without error, as suggested on another site (due to the active portion of the log wrapping the end of the file).This has allowed me to free up the 8GB of space currently wasted on this one log file (90% of which was empty space). The log backup task in the maintenance plan was not working it seems because when I do it manually the active portion of the log does clear to be nearly all free space and the file does not need to grow as it reuses the free space instead. So once I figure out why the maintenance plan log backup step is not working (and fix it) then the log should only grow to it's max daily size then stay there, instead of growing forever till the disk is full.
Shrinking the log was required in this case to bring the log back to a realistic size, and was the sole purpose of my post. Noone here managed to provide the solution and instead all preached the "don't shrink" doctrine without actually understanding my situation. Its all well and good to say "don't shrink", but when the log drive is full of 100's of GBs of logs (that just grow and grow), shrinking is a required step in the remedy. Of course a maintenance plan to regularly back up the logs is also required, but I did say that I had this (I wasn't aware it wasn't working however) in my orignal post. 🙂
Thanks
Matthew
Matthew, with all due respect - if you are only going to be backing up the transaction log one a night, what good is it? Why not just change the database to simple recovery and forget about backing up the transaction log?
I know, because you want to be able to recover to a point in time. But, you really need to consider how that is going to happen in the event of a disaster. What would happen if you lost the drive that contains your database and transaction log? Let's say that it happens one hour prior to your scheduled backup. You will have just lost 23 hours worth of data - and no way to recover it because it has not been backed up and copied to tape and/or another drive.
You really need to review this and determine how often you need to be backing up the transaction log, and it really should be something less than once a day.
I agree that shrinking the transaction log was necessary for you, but you will probably find yourself in this situation again if you don't take care of backing up the transaction log much more frequently than you are now.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 30, 2008 at 5:04 pm
Yes, I agree, now that the logs are manageable size we can increase their backup interval. The original mandate was just a daily backup with no point in time recovery available, but I can just tell that if there is a problem with the database that the powers that be will not really be happy with last night's data so now I need to look at being able to provide point in time recovery. With the log files so huge I thought this was unobtainable till I could keep them under control, but now that I understand how it works and what is required I can work toward achieving that.
However I now need to delve into the world of log-shipping or replication as we currently only backup to disk on the server (then to tape) and in theory we could loose all the backups to a disaster scenario anyway. This will undoubtedly present me with more frustrations and challenges in the months to come. 🙂
Currently the 2x servers are independant and are running different versions of SQL (2000 and 2005) however I am in the process of moving the SQL 2000 databases to 2005 and then we can recomission the old 2000 server as a secondary 2005 server. However SQL is just a tiny portion of my job so I don't often get time to do all the things I want to do and have to squeeze them into my day-to-day stuff.
Cheers again
Matthew
July 30, 2008 at 7:13 pm
Matthew O'Gorman (7/30/2008)
Shrinking the log was required in this case to bring the log back to a realistic size, and was the sole purpose of my post. Noone here managed to provide the solution and instead all preached the "don't shrink" doctrine without actually understanding my situation. Its all well and good to say "don't shrink", but when the log drive is full of 100's of GBs of logs (that just grow and grow), shrinking is a required step in the remedy. Of course a maintenance plan to regularly back up the logs is also required, but I did say that I had this (I wasn't aware it wasn't working however) in my orignal post. 🙂
As with all the others, it sounded, even to me, like you wanted to shrink the log every night and you got some really good advice about not doing that and why. At no time, in your original post, did you even hint that shrinking the log would be a one time thing to get control of the situation. All you said was that you already knew about not doing "it" and you didn't really care... that set the wrong tone for your problem. Remember, now that you've correctly identified the problem and the solution, that hind sight is 20/20 😉
Anyway, very happy you found a solution. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply