SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 SQL 2008 Maintenance Plan - Truncate Log
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

smh999
Starting Member

2 Posts

Posted - 01/20/2010 :  09:59:15  Show Profile  Reply with Quote
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 SHRINKFILE

We 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)

USA
7020 Posts

Posted - 01/20/2010 :  10:01:46  Show Profile  Reply with Quote
Not a good plan.

You should schedule transaction logs backups to run 24x7 at short intervals, like every 15 minutes.


CODO ERGO SUM
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/20/2010 :  10:21:15  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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 Shaw
SQL Server MVP
Go to Top of Page

smh999
Starting Member

2 Posts

Posted - 01/21/2010 :  04:09:08  Show Profile  Reply with Quote
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.

Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/21/2010 :  04:57:46  Show Profile  Reply with Quote
"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 offline
Make TLog backup (tiny, small and quick)
Restore TLog backup on new server (quick operation) using RECOVERY to make the new server database available

Backup the TLog every 15 minutes, and then your maximum data loss is 15 minutes.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000