Data being written to LDF and periodically flushed to mdf causing dB to stop processing
-
Setup:
SQL Server 2005 (64bit)Clean dB instance created using all default settings except recovery mode is set to Simple
Description:
- A server we wrote is receiving messages at a rate of 65 per sec - each message will result in about a dozen sql statements being executed (selects, updates, inserts)
- After about 18 hrs we noticed a slight decrease in the number of messages being processed
- We stopped everything and rebuilt the database indexes
- After the rebuild we started seeing a red line like the one in the graph below (this line represents the number of messages processed per sec by our server) – you can clearly see how the server stops processing msgs every 45secs for about 10 secs
- We discovered the database is now writing to the ldf file and then periodically flushing to the mdf file – see pink line in the graph below.
You can clearly see the correlation between the log file(s) being written to and our message processing capability. As I said it seems the dB is writing to the ldf file then periodically flushing data out to the mdf file and while it is doing this it can’t execute any SQL statements from our server, so the server stops processing msgs until all the data is written to the mdf file
Any ideas why the dB has decided to start writing to the ldf file and then periodically flushing to the mdf file even though we are in simple mode?
Why would creating the index have caused this to happen?
How do we fix it so we don't have this problem of the dB not responding while it's flushing to the mdf file
Thanks for any help you can give
-
It's a misunderstanding that LDF files are not used in SIMPLE mode. Every transaction is recorded in LDF files, regardless of the recovery mode. But if your database is in SIMPLE mode, the space being used is directly marked as "can be overwritten". That's the reason why the log space used line is dropped.
Wilfred
The best things in life are the simple things -
could you give the following information:
- memory for MSSQL server
- where data and logfiles are placed (on same disk?)
- How heavily is tempdb used and where are those tempfiles placed
- is the number of tempfiles = numbers of CPU ?
Wilfred
The best things in life are the simple things -
What you're seeing here is SQL Server doing a CHECKPOINT.
Have a look at this article for a description:-
http://msdn.microsoft.com/en-us/library/ms189573.aspx
As you are running in SIMPLE recovery, I assume you are willing to lose data in the event of a problem!!
If your server is struggling during a checkpoint, then I suspect you may have I/O problems. Add a few disk counters to your performance monitoring, e.g sec/write etc for your database disk(s) and see what these are like, particularly during the checkpoint.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply