Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

LDF AND TRN FILE KEEP GROWING Expand / Collapse
Author
Message
Posted Tuesday, June 24, 2014 10:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 2:46 AM
Points: 3, Visits: 5
Can someone help me with this ?

As is -
Version - SQL Server 2005 Standard SP2
SQL DB Recovery Model = FULL
I run Transaction Log backup at 1PM and 1AM daily, using below script
BACKUP LOG @DatabaseName TO DISK = @Path

I run Database backup at 1:15AM daily, using below script
BACKUP DATABASE @DatabaseName TO DISK = @bakfile WITH NOFORMAT, INIT, SKIP, REWIND, NOUNLOAD

Observation -
Despite the transaction log backup every 12 hours, my LDF file keep growing. The TRN backup file size is also growing, to almost the size of the backup BAK file.
This is odd because there isnt a lot of transactions to the database within a 12hours windows. The DB backup file size is about right, but I was expecting the LDF to be kept under control, and TRN files size to be much smaller.

What has not been configured correct ? Can anyone check if my Transaction Log backup script is correct ? Do I have to add more parameters, e.g WITH INIT ?

Post #1585732
Posted Wednesday, June 25, 2014 12:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
Do log backups more often.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1585757
Posted Wednesday, June 25, 2014 1:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 2:46 AM
Points: 3, Visits: 5
thanks, but i still think the amount of transactions in the database should not warrant the current size of the LDF or TRN file.

How about my transaction log backup script - "BACKUP LOG @DatabaseName TO DISK = @Path" , is this command sufficient ?
Post #1585768
Posted Wednesday, June 25, 2014 1:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
http://www.sqlservercentral.com/articles/Administration/64582/
http://www.sqlservercentral.com/articles/Transaction+Logs/72488/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1585771
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse