April 28, 2005 at 4:54 pm
Have a client on Sql2kSP3a on Win2kSP4. They back up their transaction logs every 15 minutes. (dB is not that big, maybe 350k-1.5mb per log backup). Our application constantly polls the database every 15 seconds looking for any new jobs to run. If it finds a job, it executes a command to the O.S., waits for a return status, and then updates a table with the new status - successful, error, what have you.
The transaction log frequency has been increased recently to its current level - every 15 minutes. Once that happened, they started to experience situations where the application was not able to write the update to the table. The process that was launched was successful, but it couldn't write the update to the table. There were no blocking locks (viewed via Enterprise Manager and sp_who2), and I didn't see any other exclusive locks on objects - it seemed to just die and leave the application in a 'hung' state.
Has anyone encountered this - transaction log backup causing conflicts of any type with basic database access?
Application creates a JDBC connection to the database, if that helps.
TIA for any thoughts.
April 28, 2005 at 7:44 pm
I was called in to help with a situation a few years ago where they were using the SQL agent for either Backup Exec or ArcServe for their backups. The client's application would freeze up during the transaction log backups. Turns out that someone had checked the box to do DBCC database integrity checks each time the transaction log job ran. Once that was turned off, everything was fine.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
April 29, 2005 at 5:25 am
I dont see any problems running T-log backups every 15 minutes and that too for such a small size. You could use SQL Profiler to see wht kind of statements are running to catch hold of sumthing unknown.
Hope this helps.
--Kishore..
April 29, 2005 at 8:12 am
I agree - I have a hard time imagining that 15 minute backups of a tlog are unusual. However there does seem to be a direct correlation between the increase in frequency and the increase in application 'hangs'. Even more to the point, the times of the application hangs often correspond with the kickoff of a backup. I will look at the possibility that someone has checked the option to run a DBCC package. But this is just strange.
May 13, 2005 at 3:29 pm
Doing some more research found out that there is a process running every 2 hours - a batch process. This causes the backups after that point to be large - 350-400mb. The option to "Verify the integrity of the backup upon completion" is checked. Looking at BOL, I see what it does at a high level, however with a large transaction log, is there any way that this verification is causing a performance hit?
May 18, 2005 at 8:05 pm
Could be the result of a bug MS recently fixed post sp3a? Here's the info:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply