How to monitor logs for all databases on MSSQL

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005

Part of the DBA's [database system administrator] job is to monitor the logs of the databases. The logs are almost as important as the data itself. They allow you to recover and/or roll back to known, stable states.

  1. You can look at the file size through Windows Explorer.
  2. You can see some log info through the SQL Management Studio > Database > right click Properties > Files
  3. You can see the log size and more important the percentage used with TSQL.

Monitor the logs

  1. DBCC SQLPERF(LOGSPACE) WITH no_infomsgs;
  2. go
Database Name                       Log Size (MB) Log Space Used (%) Status
----------------------------------- ------------- ------------------ -----------
master                              0.4921875     85.71429           0
tempdb                              0.7421875     66.71053           0
model                               0.4921875     42.06349           0
msdb                                0.7421875     65.26316           0
ReportServer                        0.7421875     67.17105           0
ReportServerTempDB                  0.7421875     51.77632           0
AdventureWorksDW                    1.992188      29.21569           0
AdventureWorks                      1.992188      28.03922           0
AdventureWorksLT                    1.992188      23.43137           0

(9 row(s) affected)

To expand or change the logs parameters in SQL Server Management Studio:

  1. Select the database, right-click > Properties
  2. Select Files
  3. Specify how the file should grow by clicking (…) in the Autogrowth column.
    1. Select the Enable Autogrowth check box.
    2. Specify the growth by fixed increments in Mb. or
    3. Specify the growth by fixed increments in %.
  4. Specify the maximum file size limit

When the log runs out of disk space, THAT'S IT! KABOOM!