June 24, 2014 at 10:11 pm
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 ?
June 25, 2014 at 12:23 am
Do log backups more often.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 25, 2014 at 1:02 am
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 ?
June 25, 2014 at 1:16 am
http://www.sqlservercentral.com/articles/Administration/64582/
http://www.sqlservercentral.com/articles/Transaction+Logs/72488/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply