SQL Server Performance Forum – Threads Archive

System/DB locks for 4-15 seconds

Hello – I’m running Permormance monitor on my SQL-2000 DB log file activity and disk activity. During somewhat high activity on the DB, about every 80-90 seconds or so the disk activity (%disk time) goes ballistic as well as the ‘Avg disk sec/read’ for a period of 4 to 15 seconds. The overriding problem is that the system seems to lock up during this time and the DB is not accessable. This is not acceptable in my live real-time environment. After this activity, the size of the db log file is almost always reduced. There seems to be some process out there hell-bent on maintaining the log file to a certain maximum size, and kicks in in a big way every now and then to bring it down. I am not runing any kind of replication, although I used to do transactional. There are no obvious signs that anything relating to that is still active. I would like to understand what is happening here and how to fix it. Ultimately I would like to use Transactional Replication to maintian a hot-backup on the DB. Thank you. DC

Please explain placement of database data & log files and RAID levels used.
Also confirm SQL Memory settings. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

From the sounds of it the log file is possibly being auto-shrunk. I can’t imagine that this is happening on every checkpoint as truncating the log file shouldn’t lock up the database – shrinking it could do though. When you say that the log file is reduced in size I assume you mean the physical size of the log file and not just the amount of free space in it. Run sp_dboption on the given database and check the autoshrink param is off. If that doesn’t work, run a profiler trace to see what is happening every 90 seconds or so. If it is none of these things could it be regular log backup? Sounds a bit strange that a log backup would cause this behaviour but the fact that the read time (and not the write time) is affected seems a bit strange for it to be a shrink database event. Anyway, before we can fix the problem we need to find out what’s causing it so check the various things I’ve suggested and see what that brings up. Karl Grambow www.sqldbcontrol.com

More info: The DB and log files are on a RAID 1 drive – same physical drive as the OS (different partitions). I’m planning to move them to separate physical drives later. The ‘size’ of the DB and log files seem to be much bigger than the actual data in them. Do I need to shrink the files, i.e. some sort of paging problem? How would I safely do that? Thanks.

Your answer leads me to believe that the database is not being shrunk, given that you suggest that the files are bigger than the amount of data in them. This in itself isn’t a problem and I doubt is the cause of your problems here. One thing that has come to mind (and in particular your mention of the high reads on the disk) is Anti-virus software. That’s one occasion when I’ve seen SQL Server performance really badly affected in the way you described. Turn off your anti-virus software (if you have any) and see if that makes a difference. Make sure you stop the services. Ultimately, you need to configure the anti-virus software so that it excludes the scanning of all database related files (.mdf, .ndf, .ldf, full-text indexes, etc). If you don’t have anti-virus then try running a profiler trace anyway. It might show up a few things.

quote:Originally posted by dclarkesql More info: The DB and log files are on a RAID 1 drive – same physical drive as the OS (different partitions). I’m planning to move them to separate physical drives later. The ‘size’ of the DB and log files seem to be much bigger than the actual data in them. Do I need to shrink the files, i.e. some sort of paging problem? How would I safely do that? Thanks.

Karl Grambow www.sqldbcontrol.com

The following was returned on the ‘options’ request: trunc. log on chkpt. The DB has 2000MB allocated, the log file has 1100MB allocated. DC

If you’ve still got problems after you turn off anti-virus (see my previous post) then you could try changing the the recovery mode to full (if it’s on simple). I’d be really surprised (but interested) to see if the problem is caused by the truncating of the log. In full recovery mode the log will continue to grow so don’t forget to switch back to simple recovery mode (or better still, implement some log backups <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />) <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by dclarkesql The following was returned on the ‘options’ request: trunc. log on chkpt. The DB has 2000MB allocated, the log file has 1100MB allocated. DC <hr height="1" noshade id="quote"></blockquote id="quote"> Karl Grambow www.sqldbcontrol.com

There’s a billion options on the SQL profile trace. Suggestions on setting to find out who is doing what during the problem times? Thanks. DC

Sorry about that – my bad[:I]. I’ve got to shoot off now but maybe someone else can follow up on this. If not I should be around in about 3 hours so I’ll try and follow up then. If nothing else try and play around with the recovery models and see if that makes a difference. Catch up with you later.

quote:Originally posted by dclarkesql There’s a billion options on the SQL profile trace. Suggestions on setting to find out who is doing what during the problem times? Thanks. DC

Karl Grambow www.sqldbcontrol.com

Check:
http://www.sql-server-performance.com/reducing_locks_hints.asp
http://www.sql-server-performance.com/blocking.asp
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Right I’m back. Just got badly beat at a game of squash so I’m in a bad mood[<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />!]. Ok, let’s start at the beginning. I’ll go through the steps that I’d perform, some of which you might have already performed (or are not relevant) but I’ll repeat them anyway. 1) Check that auto-shrink is not on. Right-Click on the db in EM and select properties. On the options tab you’ll find the Auto-Shrink property. 2) In the same place, note the recovery model. I suspect that the recovery model is simple but it might be full. In either case, if the above change didn’t help, change the recovery model to either simple or full. 3) If that still doesn’t work switch off any anti-virus software (for now) or at least configure it so that it doesn’t scan database-related files. This should be done in any case. 4) If still no luck, open up profiler and start a new trace. Click on the Event tab and remove the Event Classes from the Right-Hand window so that you’re left with just RPC:Completed and SQL:BatchCompleted. Click on the filter tab and expand the DatabaseId node, followed by the Equals node and enter the database id for the corresponding database. If you don’t know the databaseid, run select * from master.dbo.sysdatabases to get the id. Run the trace and see if you find anything whenever the problem occurs. 5) Check any schedule jobs to see if you have anything that runs every 90 seconds or so. 6) Don’t forget to change the recovery model back to what it was before you started. To be honest I was hoping that the problem wouldn’t need profiling because it seems strange that the database is made unavailable for 15 seconds or more. That’s why I thought initially it could be auto-shrink. Either way, the plan with the profiler is that you just want to capture basic stuff at first to see if you can spot anything. The good thing with this problem is that it happens at regular intervals so it will make it easier to spot. Let’s see what that turns up. Hopefully it will help. <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by SQLDBcontrol Sorry about that – my bad[:I]. I’ve got to shoot off now but maybe someone else can follow up on this. If not I should be around in about 3 hours so I’ll try and follow up then. If nothing else try and play around with the recovery models and see if that makes a difference. Catch up with you later. <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by dclarkesql There’s a billion options on the SQL profile trace. Suggestions on setting to find out who is doing what during the problem times? Thanks. DC <hr height="1" noshade id="quote"></blockquote id="quote"> Karl Grambow www.sqldbcontrol.com <hr height="1" noshade id="quote"></blockquote id="quote"> Karl Grambow www.sqldbcontrol.com

Auto shrink is off. Recovery method is Simple. Stopped virus protection without affect. I’ll run the trace when I get the events. Thanks – I’ll get back to you with the results. DC

Sounds like some type of autogrow/shrink operation, possibly on the tempdb…

]]>