• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

Managing size of Transaction log file under SQL 2008R2

Hi,
I am backing up transaction log every hour. Sometimes log grows to 10GB as there are some heavy transaction flowing thru the system. This happens sometimes so I don't want to schedule a backup every 15/30 min.
Is there a way to carry backup of log and then truncate it once it grows over 2GB.  This way I ma making log switch on size dependent than time.

Thanks
0
crazywolf2010
Asked:
crazywolf2010
  • 3
  • 2
1 Solution
 
DcpKingCommented:
Have you considered running an SSIS job every 15 minutes during the peak times, and having the SSIS job look at the T-Log and, if the size if over a certain limit, starting a T-Log backup? That way you can keep your log relatively small and be protected.
0
 
crazywolf2010Author Commented:
Hi,
Could you please let me have details on how to develop such script??

Thanks
0
 
DcpKingCommented:
Firstly, take a look at the File Properties Task in PragmaticWorks' Task Factory. That lets you discover the size of a file, so you can point it at the t-log to see how big it is.

Then, you already know the code to back up and truncate the log: if you've been doing it manually then get to the moment where you press the OK button and look for a button labelled "Script" - that'll give you the code that you can put into a proc and run from an Execute SQL Task.

Finally, put these into a package and use the agent to run it every 15 mins ...
0
 
crazywolf2010Author Commented:
Hi,
Task factory costs 999$ which is pretty steep.
Can you suggest any other way please? Any powershell script will be great.

Thanks
0
 
DcpKingCommented:
Take another look at TaskFactory - there's a subset that's free, and the File Properties task is in the free subset!  Of course, you could pay for the better version, at almost $1500 :)  

Or you could write your own task - it you're into C# then they're not that difficult:

http://geekatwork.wordpress.com/2010/03/05/my-first-ssis-custom-task/

http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/76439/

hth

Mike
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now