February 11, 2011 at 2:20 pm
In SQL 2005 we use this script to truncate Trans logs.
dump tran dbname with truncate_only
This is no longer a available in SQL 2008. What would be the equivalent command or how do I
accomplish the same thing SQL 2008?
February 11, 2011 at 3:30 pm
set recovery mode to simple
issue a checkpoint
set recovery mode to full
take a full or differential backup
The last step is vital because you have broken your log chain and will not be able to roll through log backups for recovery. Avoid truncating your log in production scenarios so I would question why you want to do this?
dump is also deprecated so use backup, and use log rather than tran.
---------------------------------------------------------------------
February 11, 2011 at 4:24 pm
Better still, since you can't use point in time recovery while truncating transaction logs, just set your database to simple recovery and leave it there. Better that than having to fight with incorrect backups.
If you really do want to keep your database on full recovery, then you should just want to work with the transaction log and transaction log backups so that you can ensure that point in time recovery. That's the best approach.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 12, 2011 at 1:26 am
Why are you truncating your transaction logs?
Please read through this - Managing Transaction Logs[/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
February 14, 2011 at 9:03 am
My database is 9gig, my log file is 50gig and growing. I back up my database 3 times daily to tape and every 1/2 hour to disk on another server.
The thinking is I don't need to save unneeded transactions.
I'm new to this so thanks for anything you can offer.
February 14, 2011 at 10:54 am
If three times a day backup is adequate and there is no need for point in time recovery, set the database to simple recovery and leave the logs alone.
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
February 15, 2011 at 1:56 am
ma29 (2/14/2011)
My database is 9gig, my log file is 50gig and growing. I back up my database 3 times daily to tape and every 1/2 hour to disk on another server.
What sort of backups are we talking here? If the database is in full recovery mode and you're not performing transaction log backups then it isn't surprising you've got a 50Gb log file!
February 15, 2011 at 5:44 am
I wrote this article on log backups just so I could use it in situations like this. Go here and read this[/url]. Hopefully it will help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 17, 2011 at 1:37 pm
Thanks to all for the information.
Right now in a test environment we set up a maintenance plan that does a full backup on Sunday nights, Differentials every 2 hours and Tran logs every 20 min. We are in full recovery mode and all backups will eventuall go to directories on the recovery server. This way we can do point in time restores and add to that a tail log.
Hopefully this solves the problem of the trans logs growing out of control. Once we are satisfied with the performance in the test env. we will put this into production.
How does that sound, do you think the interval between Full, Diff and Log work?
Lastly is there anything that can be done to clear out destination directories of backup files that are not needed, besides manually deleting them or writing a script to clear them out?
February 17, 2011 at 1:46 pm
Every 2 hours is a little often for diff backups. Done so that you can restore the database quickly to any point?
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply