|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
waittype=WRITELOG
Hi
In my server (MSSQL 2000 sp4) very often I see long time of executing query with waittype=WRITELOG Recovery is set to SIMPLE and there is no autoShrink. Auto increment file size (log) is set to 10% What can be the reason of this problem? Marek "Marek Wierzbicki" <marek.wierzbickii***@azymuttttt.pl> wrote in message This wait type means that a session is has finished a transaction or news:dvep4k$1loj$1@news2.ipartners.pl... > Hi > > In my server (MSSQL 2000 sp4) very often I see long time of executing > query with waittype=WRITELOG > > Recovery is set to SIMPLE and there is no autoShrink. > > Auto increment file size (log) is set to 10% > > What can be the reason of this problem? autocommit statement and it's waiting for the results to be flushed to the log file. At the end of a transaction or statement that changes data the changes must be written to the log and the log committed to disk. This is the only part of the change that has to hit the physical disk, and it's a major performance consideration. In SIMPLE recovery mode the transaction log is written just the same, except it's truncated when the database is checkpointed. At checkpoint the commited data is flushed to the database files and the log records are no longer needed in SIMPLE mode. David > This wait type means that a session is has finished a transaction or OK> autocommit statement and it's waiting for the results to be flushed to the > log file. > At the end of a transaction or statement that changes data the changes Is the data must be deleted from log file?> must be written to the log and the log committed to disk. This is the > only part of the change that has to hit the physical disk, and it's a > major performance consideration. > > In SIMPLE recovery mode the transaction log is written just the same, > except it's truncated when the database is checkpointed. At checkpoint > the commited data is flushed to the database files and the log records are > no longer needed in SIMPLE mode. Marek Make sure your log file is on a different physical drive than the data,
preferably a Raid 1 or 10. -- Show quoteHide quoteAndrew J. Kelly SQL MVP "Marek Wierzbicki" <marek.wierzbickii***@azymuttttt.pl> wrote in message news:dvep4k$1loj$1@news2.ipartners.pl... > Hi > > In my server (MSSQL 2000 sp4) very often I see long time of executing > query with waittype=WRITELOG > > Recovery is set to SIMPLE and there is no autoShrink. > > Auto increment file size (log) is set to 10% > > What can be the reason of this problem? > > Marek > > > Make sure your log file is on a different I know it, but today its on the same disk as data file> physical drive than the data, > preferably a Raid 1 or 10. Marek > I know it, but today its on the same disk as data file Hence, the waits on log I/O. Also, it's better to size the log to accommodate needs (largest transaction) and let growth kick in only as a safety net. -- Hope this helps. Dan Guzman SQL Server MVP >> I know it, but today its on the same disk as data file OK. So I need to large my matrix> > Hence, the waits on log I/O. > Also, it's better to size the log to > accommodate needs (largest transaction) > and let growth kick in only as a > safety net. Marek  
Other interesting topics
SQL 2000 on W2k fragmentation
TSQL help to delete Performance: Primary Key = int/nvarchar/guid/... How do I get my local data in the database on the Internet? Memory allocation in MS-SQL 2000 instances Windows says SQL is installed, but it's not! Why do Ado.net don't use execution plan that Query Analyzer Switching off logging on varbinary column Multiple jobs on one schedule annoying SQL Server error message |
|||||||||||||||||||||||
