WRITELOG lock on databases set to SIMPLE recovery

[joshsackett]

Hi All,
I have a 2MB database set to SIMPLE recovery. This database is used
only to generate new keys to web users. It has two columns - UID and
LASTDATE. The UID column is only updated when users log in or log out
(INSERT & DELETE). The LASTDATE column is updated every time they
switch between pages.

Please don't ask for the reason why we do it this way, it just IS.

The maximum amount of rows in this table at any given time is about
2000. There is a non-clustered index on the UID column.

My problem is that all throughout the day I have constant blocks by the
WRITELOG waittype. I know my disk I/O is very slow (will be upgraded
soon) but this is the only database on which these blocks occur. What
is writing to the transaction log if I have the DB set for SIMPLE
recovery?

Thanks,
Josh

[Erland Sommarskog]

joshsackett (jo*********@gmail.com) writes:

My problem is that all throughout the day I have constant blocks by the
WRITELOG waittype. I know my disk I/O is very slow (will be upgraded
soon) but this is the only database on which these blocks occur. What
is writing to the transaction log if I have the DB set for SIMPLE
recovery?

Simple recovery is not the same as no recovery. SQL Server always
writes to the transaction. It first write to the transaction log,
and later to the data tables. The point is that if the server
crashes before the data is written to the table, the transaction
can be rolled forward, if commit record was written. Else the
transaction will be rolled back.

The difference to full and bulk-logged recovery, is that in simple
recovery, SQL Server will automatically truncate the transaction
log every now and then. But truncation is never past the point of
the oldest open transaction.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

joshsackett (jo*********@gmail.com) writes:

My problem is that all throughout the day I have constant blocks by the
WRITELOG waittype. I know my disk I/O is very slow (will be upgraded
soon) but this is the only database on which these blocks occur. What
is writing to the transaction log if I have the DB set for SIMPLE
recovery?

Simple recovery is not the same as no recovery. SQL Server always
writes to the transaction. It first write to the transaction log,
and later to the data tables. The point is that if the server
crashes before the data is written to the table, the transaction
can be rolled forward, if commit record was written. Else the
transaction will be rolled back.

The difference to full and bulk-logged recovery, is that in simple
recovery, SQL Server will automatically truncate the transaction
log every now and then. But truncation is never past the point of
the oldest open transaction.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[joshsackett]

Thanks Erland, I appreciate the help. I reexamined the application and
found that it writes an XML output to the table as well every time a
user changes the page. So my 2000 row table is actually about 6MB in
size and is constantly writing large chunks of data. The 31MB
transaction log gets to about 80% full and then flushes.

I suppose my only recourse is to get the faster disk system in.

Thanks again,
Josh

[joshsackett]

Thanks Erland, I appreciate the help. I reexamined the application and
found that it writes an XML output to the table as well every time a
user changes the page. So my 2000 row table is actually about 6MB in
size and is constantly writing large chunks of data. The 31MB
transaction log gets to about 80% full and then flushes.

I suppose my only recourse is to get the faster disk system in.

Thanks again,
Josh