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. DCPlease 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).<br /><br />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=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
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=’
![Frown :( :(](styles/default/xenforo/smilies/frown.png)
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…
]]>