When does a database data file get written to?

Question

I was using perfmon to monitor writes per second on a LUN containing a single SQL2005 database data file. I was under the assumption that database files are only written to during checkpoints or by the lazy writer process, is this assumption correct? as something seemed to be writing to the LUN when the checkpoint and lazy writer processes were inactive.

[EDIT]

The writes seem to happen every 3 seconds or so and is 510 bytes long? I have my suspicions that it's not SQL writing to the LUN but some OS process.

[EDIT]

This write is actually hitting all my LUNs simultaneously - so can't be SQL.

I'm wondering if it is the lazy writer process but Perfmon doesn't have enough precision to show it. — SuperCoolMoss, Sep 17 '09 at 10:51
Recovery model doesn't affect when the DB file gets written to. It affects how long log records are retained, how much gets logged in bulk-operations and what operations truncate the log. — GilaMonster, Sep 17 '09 at 11:43
you wouldn't think so, but it does. Saw a session where David Lean looked at how read/writes to disk performed with different recovery models. Will ask him if he can post his slides — Nick Kavadias, Sep 19 '09 at 03:17

score 2 · Answer 1 · answered Sep 21 '09 at 14:45

2

My guess is it's the OS updating the atime on the DB files.

answered Sep 21 '09 at 14:45

pjc50

1,720
10
12

Mark Allison · Answer 2 · 2009-09-17T08:39:25.927

1

Are you sure this LUN only contain data files? Where are your transaction log files located? Perhaps a log flush was occurring when writing to the transaction log? This usually happens within milliseconds of a write being committed (depending on load of course).

What is your recovery interval set to? This has a bearing on frequency of checkpoints.

[EDIT]

Perhaps you could use one of the sysinternals tools to diagnose what exactly is writing to your disk? Maybe perfmon is lying to you? :-)

edited Sep 17 '09 at 08:39

answered Sep 17 '09 at 07:47

Mark Allison

2,098
7
26
45

The database file is isolated on this one LUN, the transaction log is on another. I also measured checkpoint pages / sec on Perfmon and the writes to the LUN don't correlate. – SuperCoolMoss Sep 17 '09 at 08:16

score 1 · Answer 3 · answered Sep 17 '09 at 11:46

http://sqlinthewild.co.za/index.php/2009/06/26/the-lazy-writer-and-the-checkpoint/

The lazy writer can be writing quite a lot if SQL is under memory pressure. If both checkpoint pages/sec and lazy writer pages/sec are 0, there should be no writing occuring. Definitely see if one of the sysinternals tools can diagnose what process is writing.

Edit: There's also something known as the eager writer. From what I recall it has to do with bulk operations (bcp, BULK INSERT)

score 1 · Answer 4 · answered Sep 17 '09 at 16:40

Do you have any paging files setup on this LUN or and backup software which makes use of this LUN for shadow copying? We had a case where the backup software's would come in and make use of LUNs for shadow copying filling up the drive, so we had to explicitly exclude certain LUN's to prevent this scenario. You might wish to check.

When does a database data file get written to?

4 Answers4