Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
drsloat
Starting Member
45 Posts |
 Posted - 2007-02-06 : 13:15:30
|
| I'm having a problem that is occuring very consistantly every 3 minutes. After noticing that queries were taking a long time to execute I ran a profile for execution taking longer than 250 ms. Every 3 minutes there are about 2-3 seconds where query execution jumps to over 1 second for a number of different queries and then the execution times go back to normal levels.I'm looking for some ideas of what to look into. Could there be some lock on a transaction log (Recovery model Simple)?These options are set: Auto update statistics, torn page detection, auto shrink, auto create statistics.Thanks for any help/suggestions. |
|
|
propanecan
Yak Posting Veteran
60 Posts |
Posted - 2007-02-06 : 15:39:08
|
| Is there a query that only appears to run every 3 minutes? Perhaps this is a blocking issue? |
 |
|
|
drsloat
Starting Member
45 Posts |
Posted - 2007-02-06 : 16:48:25
|
| The log file was set to grow by 10 MB, and it seems that it was these events that were causing my problems. I changed the settings to grow the log by 100 MB and things seem to be working better. I'm not sure if there is something new that is causing my log files to grow at a faster rate, or if this was happening before and no one noticed. |
|
|
|
Kristen
Test
22859 Posts |
|
|
drsloat
Starting Member
45 Posts |
Posted - 2007-02-06 : 17:42:46
|
| I did think about that, but the log files were more like 300 MB, so I have to think that the space is getting reclaimed. We have the log backed up with TRUNCATE_ONLY nightly, and since we are in simple recovery mode i would think we pretty much start over every day.Another potential problem that I found was that the AutoShrink property of the database was turned on. I don't want to have that set, but it could be shrinking my log file during the day? I'm not sure if AutoShrink shrinks the log as well as the DB.Regardless.. I'll be keeping a close eye on the log file size :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-07 : 03:46:37
|
| yes, AutoShrink may have been having an effect. Best to turn that off.For example: the log was growing a bit, and then getting shrunk, and then having to grow again ...Kristen |
|
|
|
|
|
|
|
|
Long running queries every 3 minutes