November 10, 2008 at 9:30 am
My ERP system database size is currently 44GB and at the end of the week the transaction log becomes 30GB. I manually perform at dbcc shrinkfile once a week to try and control the DB growth. Here is the job I setup:
backup log [010] with truncate_only
dbcc shrinkfile (DEFAULTDB_log, 10)
Once I perform this job I perform a complete backup of the database. At the end of the week the transaction log gets back up to 30GB. What gives?
November 10, 2008 at 9:38 am
Obviously, your ERP database is using the Full Recovery model. In this model, if you are using Full, Differential, and Transaction Log backups appropriately, you can recover your database to a specific point in time if needed.
You need to setup appropriate backup procedures. The only way to keep your transaction log file from constantly growing is to schedule periodic transaction log backups, as this will truncate the log files (it WON'T shrink the transaction log). I'm not going to tell you how often you should run your full or differential backups, but you should run transaction log backups at least every hour. More frequently if log file growth is still too much. You should not, however, shrink your transaction log files after establishing your transaction log backups unless you have an extraordinary growth occur due to unusual database activity. The constant growing and shrinking of the transaction log file will result in disk fragmentation and impact system performance.
November 10, 2008 at 9:38 am
Firstly, read through these two articles:
http://www.sqlservercentral.com/articles/64582/
http://sqlinthewild.co.za/index.php/2008/07/23/recovery-model-and-transaction-logs/
What recovery model is this DB in (I'm guessing full) and how often do you backup the transaction log?
What is considered acceptable data loss for this system, say in the case of complete drive failure?
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
November 10, 2008 at 9:55 am
This DB is in Full Recovery mode. I'm backing up the Transaction Logs every 4 hours. If there was a catastrophic failure, 1 hour of lost data would be acceptable. Which means I need adjust my maintenance plan right to every hour? With doing that I could control my transaction log growth? I'm going to read your articles now.
November 10, 2008 at 10:02 am
It depends. Is there anything else going on with the database that you haven't told us, like replication? There could also be some long running transactions that keep the log files from truncating during the transaction log backups.
Knowing Gail, I'm sure she has the queries you need to identify long running transactions. I'd have to do some research for those.
November 10, 2008 at 10:17 am
I have not told you that I'm also using an offsite backup service (Evault) to backup the database over the internet daily. Evault is successfully backing up the DB every day so I wouldn't think Evault would be preventing the the Transaction log from writing to disk after a backup. But I shouldn't assume that. Just an F.Y.I. also, I'm performing a complete DB backup everyday as part of the Maintenance Plan as well.
November 10, 2008 at 10:29 am
Lynn Pettis (11/10/2008)
Knowing Gail, I'm sure she has the queries you need to identify long running transactions. I'd have to do some research for those.
On SQL 2000 the only way is with OpenTran
DBCC OPENTRAN
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
November 10, 2008 at 10:33 am
Marker (11/10/2008)
This DB is in Full Recovery mode. I'm backing up the Transaction Logs every 4 hours. If there was a catastrophic failure, 1 hour of lost data would be acceptable. Which means I need adjust my maintenance plan right to every hour?
Yup.
With doing that I could control my transaction log growth? I'm going to read your articles now.
Providing there isn't something else preventing the log from been reused.
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
November 10, 2008 at 10:57 am
Thanks! I'll look into the DBCC OPENTRAN function.
November 10, 2008 at 11:45 am
When running dbcc opentran on my database there is a active transaction:
Oldest active transaction:
SPID (server process ID) : 144
UID (user ID) : 1
Name : ClearChangeBits
LSN : (37328602:581:1)
Start time : Nov 10 2008 2:00:29:240AM
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Does this clear things up as to why my Transaction logs are getting truncated when a backup is being performed?
November 10, 2008 at 12:00 pm
I'm going to answer my own question. This active transaction was a process started by my online backup software via Evault. The process was hung up due to low bandwidth on my network. Once I stopped the process the open transaction was cleared. Thanks for all the help everyone. Hope this helps someone in the future.
November 10, 2008 at 12:08 pm
If the difference between the start time and the current time is small, then no. If it's several hours then you need to worry.
First thing, change the log backup frequency and see if that keeps the log smaller. You can use DBCC SQLPERF(LogSpace) to see if a log backup does reduce the space used in the log file.
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
November 10, 2008 at 12:13 pm
Gila,
I have one last question to get some closure on this subject. I ran a dbcc sqlperf(LogSpace) on my database and found that the Log Size is currently 30GB. The Log Space Used is only 13%. What is the best way to reduce the Log Size to a more reasonable size so I'm not using 30GB of my disk?
November 10, 2008 at 12:18 pm
dbcc shrinkfile.
Only shrink it to the size you need for peak size between log backups. Not what's used now.
November 11, 2008 at 12:31 am
Marker (11/10/2008)
I ran a dbcc sqlperf(LogSpace) on my database and found that the Log Size is currently 30GB. The Log Space Used is only 13%.
What's the % used right before a log backup during the busiest time of the day (or night)?
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 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply