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
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Is shrink logged required?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

melaniemayfield
Starting Member

United Kingdom
2 Posts

Posted - 09/03/2013 :  18:05:31  Show Profile  Reply with Quote
I have a server with multiple databases which have mirroring configured as well as logshipping configured to another location. The primary database is in FULlL recovery, my concern is the database log file, I have read that you need to do regular log back ups to ensure the database log file does not grow (I assume that logs shipping will take care of this?) , however the log file seems to be growing daily, do I need to shrink the log file periodically on the primary? Can you shrink the log file on a database which has mirroring configured?

Any help would be great as all of my research seems to be contradicting itself!

Many Thanks in advance....Melanie

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 09/03/2013 :  18:10:10  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yes log shipping should be taking care of this, but you need to monitor the jobs to make sure it still happens.

Do NOT shrink the log file periodically. Shrinking the log should be done manually and only in rare cases, such as a large purge and you know you won't need that space for months to come.

How often is log shipping backing up the transaction log and are those jobs successful? How big are the data files and how big are the ldf files?

The log growth is likely due to index rebuilds or large transactions from the application. You just need to backup the tlog often enough to account for it. How often is dependent upon your system. We backup our tlogs every 15 minutes. We increase that to every 5 minutes if we have an upgrade happening where we'll be doing a lot of changes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

melaniemayfield
Starting Member

United Kingdom
2 Posts

Posted - 09/03/2013 :  18:21:23  Show Profile  Reply with Quote
Thanks Tara, the logshipping is configured to backup every 15 minutes , our largest DB file is 52105 MB (PRIMARY Filegroup) and the log lie is 20126 MB, should we always see an increase in the ldf file?

Kind Regards

Melanie
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 09/03/2013 :  19:22:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
That log file size sounds fine for that data file size.

The ldf file should not necessarily grow, but it depends on your system. It depends on the largest transaction, which is generally rebuilding indexes. Once you've hit the largest transaction (we can't answer that for you), you will likely not see more growth or very little.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
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