WRITELOG lock on databases set to SIMPLE recovery

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • joshsackett
    #1

    WRITELOG lock on databases set to SIMPLE recovery

    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

    Tags: None
    • Erland Sommarskog
      #2
      Re: WRITELOG lock on databases set to SIMPLE recovery

      joshsackett (joshsackett@gm ail.com) writes:[color=blue]
      > 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?[/color]

      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, esquel@sommarsk og.se

      Books Online for SQL Server SP3 at
      Microsoft SQL Server
      http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
      Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

        Comment

        • Erland Sommarskog
          #3
          Re: WRITELOG lock on databases set to SIMPLE recovery

          joshsackett (joshsackett@gm ail.com) writes:[color=blue]
          > 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?[/color]

          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, esquel@sommarsk og.se

          Books Online for SQL Server SP3 at
          Microsoft SQL Server
          http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
          Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

            Comment

            • joshsackett
              #4
              Re: WRITELOG lock on databases set to SIMPLE recovery

              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

                Comment

                • joshsackett
                  #5
                  Re: WRITELOG lock on databases set to SIMPLE recovery

                  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

                    Comment

                    Previous template Next
                    Working...