Monitoring Databases  «Prev 

dbcc sqlperf output

SQL Performance Output
SQL Performance Output

  1. Database name – The name of the database.
  2. Log Size –The total size allocated for a log in the database.
  3. Log Space Used (%) - The percentage of the log space that is used.
  4. Status – This will always read “0”

DBCC SQLPERF (LOGSPACE) command:

To determine the size and percentage of used space within the transaction log, use the DBCC SQLPERF (LOGSPACE) command: 
DBCC SQLPERF (LOGSPACE);

To monitor the amount of free space on the server's disk drives
To monitor the amount of free space on the server's disk drives, you can use the undocumented extended stored procedure xp_fixeddrives:

Note: Because xp_fixeddrives is an undocumented stored procedure, there is no support for this, and it can be removed from SQL Server at any time.
Cross-Reference: For more information about configuring the data and transaction log files for autogrowth and setting the maximum file sizes, refer to SQL-Server documentation.

Shrinking the database

Unless the database is configured to automatically shrink in the background, the file space that is freed by deleting unused objects and rows will not be returned to the disk operating system. Instead, the files will remain at the largest size to which the data file may have grown. If data is regularly added and removed, then constantly shrinking and growing the database would be a wasteful exercise. However, if disk space is at a premium, a large amount of data has been removed from the database, and the database is not configured to automatically shrink, then the following commands may be used to manually shrink the database. The database can be shrunk while transactions are working in the database.