March 12, 2012 at 11:22 pm
So I ran into an issue today where my transaction log for one of my databases was 35GB. I thought that when I was doing my Full database backup that the records would be committed to the database from the transaction log backup and then truncated. But apparently this only happens when you do a Transaction Log backup.
So.... is this something basic that I just overlooked? Should this be done on a regular basis? What kind of situations drive the need for this to occur, or shouldn't this have occurred in the first place?
Thanks.
March 12, 2012 at 11:28 pm
Well apparently, the transaction log backup didn't truncate the logs.
Still 35GB... Is it safe for me to now shrink transaction log with dbcc shrinkfile?
March 12, 2012 at 11:50 pm
Transaction log backup truncates the log entries, but does not shrink the log file.
You need to shrink the file manually.
Yes, it should be safer.
March 13, 2012 at 12:44 am
Thanks.
I used SSMS to Shrink the transaction log file. It had 99% free and has been reduced to 1.2GB.
So... I guess my new question is, what is the best practice for this kind of thing. Obviously it's possible for this to blow out to 35GB again... do I schedule a regular transaction log shrinking? Is this a safe thing to do? Is it recommended that I do this?
Or is there a process that I'm missing that will prevent this from happening?
March 13, 2012 at 2:00 am
Best practice:
1) Pre-size the file.
2) Avoid auto-growth by manually pre-sizing the file required size.
3) Avoid file shrinking.
4) Take log-backup regularly by creating a maintenance plan or job
5) Create an alert to send you an email, if log file grows to abnormal size
For more info read the article "Managing Transaction Log" by Gail Shaw found here http://qa.sqlservercentral.com/articles/Administration/64582/
March 13, 2012 at 3:31 am
Draelith (3/13/2012)
do I schedule a regular transaction log shrinking? Is this a safe thing to do? Is it recommended that I do this?
No, yes and no. Please read through the article that Suresh posted.
It's nothing to do with when the transactions are written to the data file. That will happen around the time that the change was made, when the modified data pages in memory are written to disk (the only time the log is read to make changes to the DB is during crash recovery or when restoring a database).
In full recovery model the log records are retained in the log until backed up. This is so that you can take log backups in order to be able to restore the database to any point in time should you need to. That's the whole point of full recovery model.
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
March 13, 2012 at 7:23 pm
Thanks for the input. I've read through the article and think I'm a step closer to where I want to be.
Now, this database only has 1 table and has an import stored procedure that builds all the data in the table. The only reason why I'm backing it up is because I want that stored procedure backed up and it's always good to have a backup anyway.
With that in mind, I've changed the recovery model to simple and then did a full backup. I then shrunk the log file and it's now sitting at a cozy 1MB. So that's all well and good for a database that's only used for stats.
My issue now is I still don't understand when the log file is actually shrunk in size when the database is in Full recovery mode. Many of my production databases have 3 or 4 GB log files. I backup the transaction log every hour and do differential backups every 3 hours and a full backup every night. Is there something more I should be doing here? Are these log files going to keep growing unless I impose a restricted log file growth?
Also, what effect will that have? What happens if log file gets to max file growth? Transactions won't be recorded in the log?
March 13, 2012 at 10:19 pm
1) If you don't need point-in-time restore, you can keep the database in SIMPLE recovery model.
2) If log files get to max SIZE, database becomes read-only. That is, only SELECT works. No UPDATE/DELETE.
3) Become familiar with the full and simple recovery models and how backup/restore works.
These are my observations. I hope others will give some more better suggestions.
March 14, 2012 at 1:29 am
Draelith (3/13/2012)
My issue now is I still don't understand when the log file is actually shrunk in size when the database is in Full recovery mode.
They shouldn't be regularly shrunk at all.
Many of my production databases have 3 or 4 GB log files. I backup the transaction log every hour and do differential backups every 3 hours and a full backup every night. Is there something more I should be doing here?
No, sounds about right
Are these log files going to keep growing unless I impose a restricted log file growth?
Not unless there's something else broken (replication, mirroring, open transactions, etc). They should reach a steady size (or be set to a steady size) and stay there.
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
March 14, 2012 at 9:46 am
Not unless there's something else broken (replication, mirroring, open transactions, etc). They should reach a steady size (or be set to a steady size) and stay there.
Check other maintenance jobs for index rebuilds/reorgs. These types of maintenance tasks will cause your log file to grow while running (for the larger indexes) and its unavoidable unless you size it accordingly (as so many other have already stated) - in my opinion, your LDF files should be sized appropriately as to allow for the room needed to rebuild your largest index in that database
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply