Databases Reference
In-Depth Information
Monitoring log space usage statistics with
DBCC command
Every database has a transaction log associated with it. A transaction log records every
DML activity that can be used by SQL Server to recover a database. If the recovery model
of the database is full and the frequency of DML operations is very high on the database,
this transaction log file can grow very quickly. Even if the recovery model is simple and the
database is published for transactional or merge replication; this can cause the log to blow
up as well. If the transaction log is not backed up regularly and the transaction file has been
allowed to grow unlimitedly, then it can even occupy all your hard disk space and turn your
databases down, which prevents all DML operations from functioning on databases and
your application goes down as well. As a DBA, you should regularly monitor log space usage
statistics to prevent any situation that can cause such downtime issues.
When you are working as a DBA, it should be one of your important responsibilities to monitor
the size of log files of your database to make sure that you do not run out of space and your
database server is not down.
SQL Server provides different DBCC commands for database administration. DBCC SQLPERF
is one of those commands that can be helpful in monitoring the size of the log files. In this
recipe, to get log space usage statistics on all databases, we will use the
DBCC
SQLPERF
command. Apart from retrieving log space usage statistics, the
DBCC
SQLPERF
command
is also used to reset wait and latch statistics.
DBCC
SQLPERF
accepts one argument,
LOGSPACE
, when it is used to get log space usage
statistics. However, it also accepts other arguments; they are used to reset the wait and
latch statistics.
Useful columns that the
DBCC
SQLPERF
command returns for log space usage statistics are
as follows:
F
Database
Name
F
Log
Size
(MB)
F
Log
Space
Used
(%)
Getting ready
This recipe will show you how to monitor log space usage statistics for all databases using the
DBCC
SQLPERF
command.
The following is the prerequisite for this recipe:
F
An instance of SQL Server 2012 Developer or Enterprise Evaluation edition
Search WWH ::
Custom Search