Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
smh999
Starting Member
2 Posts |
 Posted - 2010-01-20 : 09:59:15
|
| Hi All,We noticed recently that the transaction log for one of our databases had grown over time to 4gb (Database itself is 1gb).We have solved this for now by running DBCC SHRINKFILEWe would like to have our maintenance plan do this, but cannot see an option anywhere.Can someone please advise if this is possible?Also - Just to check the logic here, we will backup the database, then truncate the log, then the next business day starts, so at any time we can, if required restore the previous nights DB backup then use the transaction log to restore to a point in time???Thank you,Simon. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-01-20 : 10:01:46
|
| Not a good plan.You should schedule transaction logs backups to run 24x7 at short intervals, like every 15 minutes.CODO ERGO SUM |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-20 : 10:21:15
|
| If you backup the database and then truncate the log (which you can't do anyway in SQL 2008), you won't be able to take any log backups and your only recovery option in the case of a problem would be the previous full backup. Definitely not a good plan.Shrinking the log regularly is also not a good idea. If it really needs to be that size it will just grow back to that size and when the log grows, everything slows down.--Gail ShawSQL Server MVP |
|
|
|
smh999
Starting Member
2 Posts |
Posted - 2010-01-21 : 04:09:08
|
| Hi,Ok, understood, thanks. I clearly missed a key point in that if the server is destroyed for what ever reason we would of course lose the transaction log.The problem is that the transaction log is growing day by day.Does backing up the transaction log also shrink it?Surely it shouldn't just grow and grow?Any further advice appreciated!Thank you,Simon. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 04:57:46
|
| "Does backing up the transaction log also shrink it?"No, but it makes the space used by the part that has been backed up available for re-use - so basically, if you back it up regularly, it will get to an equilibrium size.If you haven't been backing it up you will need to back it up and then do a one-time shrink to get it back to a more sensible size. Once it reaches equilibrium it should then stay at that size - if it grows suddenly investigate (might be someone did a massive one-time DELETE ...). You may find that housekeeping routines (REINDEX etc.) put more pressure on the size of the TLog than any of your normal day-to-day transactions - if that causes unacceptable size of TLog you'll have to set up a housekeeping strategy to work around it.The loss of your server would mean the loss of your TLog, so if you can copy the Tlog backups to another location soon after they are created that is prudent.However, TLog backup is often useful in other disasters:How did all the records get deleted?Database is corrupted (Make a final TLog backup, restore from a FULL backup before corruption occurred, then all Tlog backups since - usually possible to recover the database with no data loss). Need to move to another machine with minimum downtime:Take full backup, restore on new machine (with NORECOVERY)Take old server offlineMake TLog backup (tiny, small and quick)Restore TLog backup on new server (quick operation) using RECOVERY to make the new server database availableBackup the TLog every 15 minutes, and then your maximum data loss is 15 minutes. |
|
|
|
|
|
|
|
|
SQL 2008 Maintenance Plan - Truncate Log