Monitoring Databases  «Prev  Next»

Lesson 7 Monitoring the transaction log
Objective Monitor the transaction log.

Monitor SQL-Server transaction log

SQL Server provides fewer tools to monitor the transaction log, compared to the tools to monitor the rest of the database. You have already learned how to monitor the transaction log size with SQL-EM. To monitor the log size with SQL, you use the dbcc sqlperf command. The syntax is as follows:
dbcc sqlperf(LOGSPACE)

When running dbcc sqlperf, SQL Server generates the following output:

Database diagram of the pet store schema
  1. The name of the database.
  2. The total size allocated for a log in the database.
  3. The percentage of the log space that is used.
  4. This will always read

dbcc sqlperf Output

Interpreting in the output

When reviewing this output, look at the log space percentage. If the log space used is high, you should either dump your transaction log or make sure that the logs can automatically grow. In most cases, if you have a properly sized transaction log, you will want to dump the log.
In the next lesson, you will learn how to maintain a table’s statistics.

dbcc-commands

Click the Exercise link below to test your knowledge of the dbcc commands presented in this module so far.
Dbcc Commands