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

Trim ldf

I am doing FULL backups at 12:00 AM every night with the below script.  From 6:00 AM until 10:59:59 PM everyday I am running the lower most script every fifteen minutes.  Problems is that the database .ldf is growing and growing.  How do I trim it down prior/or during the Full backup?

BACKUP DATABASE [AdminDB] TO  DISK = N'\\clwfile03\system_backups$\CLWSQLPD01\AdminDB_FULL.Bak' WITH NOFORMAT, INIT,  NAME = N'AdminDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP LOG [AdminDB] TO DISK = N'\\clwfile03\system_backups$\CLWSQLPD01\AdminDB_Log.Trn' WITH RETAINDAYS = 1, NOFORMAT, NOINIT,
NAME = N'Pods-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
0
jsyers
Asked:
jsyers
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
a single transaction log backup will rarely be enough, and in case you don't do it more often, you could as well change the database recovery mode from full to simple (and not do any transaction log backups at all).

so, run the t-log backups every hour (or even more), and the ldf file should stop growing.
0
 
Kevin CrossChief Technology OfficerCommented:
If you recovery model is set to FULL then the log truncation should happen automatically after your BACkUP LoG statement.  Here are some reasons that may delay this from Microsoft:

http://msdn.microsoft.com/en-us/library/ms345414.aspx

Maybe what you are seeing is delayed truncation unless I am missing something in one of the options you have set.
0
 
Kevin CrossChief Technology OfficerCommented:
I agree with Angel.  Thought you indicated you were running the transaction log backup every 15 minutes...which is why I thought it should work as you have it.

Maybe try increasing the time to 1HR as that is what I am using as well and have had no problems with it not truncating.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

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