Home » SQL » Working with DBCC LOG Command In SQL Server

Working with DBCC LOG Command In SQL Server

👤 Andrew Jackson | Modified: May 8th, 2023|SQL | 3 Minutes Reading

Introduction

SQL server consists of a set of commands that are being used to analyze and check the database consistency. Such command set is known as Database Consistency Checker (DBCC) command. It does not require any special request for processing, can be processed easily by just mentioning a command. The command examines the file structure of database too.

In this blog, we are going to discuss some useful DBCC log commands, in detail.

Need for DBCC Log Command

  • Used in Space Allocation: The command allocates tables and database continuous space for execution or performing transactions. By using this command, table or database will get exact amount of space (needed) in SQL server for processing and finishing the transactions.
  • Used in Fixing SQL Errors: These set of commands are widely used in resolving common SQL Server error. They fix the errors like Error 601, consistency errors, etc., in a trouble-free manner.
  • Examine Database Integrity: The log commands perform the checking operation in database and its respective tables, for validating the data integrity.
  • Solving Corruption in Database: One way to repair and recover the corrupted database is by using DBCC commands. This command tries to troubleshoot all encountered errors, which are caused due to corruption in database log files.

Different DBCC Log Commands Description

There exist several useful DBCC log commands, which are used to analyze, provide information about database, etc. Description about some of the log commands is mentioned below:

DBCC LOGINFO

  1. The command is being used for over-viewing the structure of log.
  2. The command provides you with an information about virtual log files that are inside your transaction log.
  3. For executing this command, type DBCC LOGINFO in query window and press Enter.
  4. As an output, a tabular window will appear.
  5. Amongst the several columns, the primary column is Status. This field determines the portion of log which is currently in use.
  6. Moreover, there is an another major column i.e. CreateLSN, which creates the virtual log files with growth in database. If the designing of database is inappropriate, then it will lead to generation in garbage value and that value will be shown in CreateLSN column.
  7. Several log files are not observed, but they are viewed at the time of determining the transaction performance. However, the occurrence of log operation is successive therefore, insertion of multiple log files (at once) will not determine the performance of transactions.

DBCC SQLPERF (LOGSPACE)

    1. This the other command of DBCC, which provides statistics about transaction log memory, which is in use by current database.
    2. The syntax of this command is given below:

DBCC SQLPERF

  1. After executing this command, a tabular window will be displayed. This window will have multiple fields like Status, LogSpaceUsed,etc.
  2. The working of Status field of DBCC SQLPERF (LOGSPACE) is same as DBCC LOGINFO Status field.
  3. The DatabaseName field maintains the name of the database, whose transaction log files or tables are in use.
  4. The LogSpaceUsed is the memory occupied by database during its transaction processing (in MB).
  5. Moreover, there is also a field that is useful for time tracking. Such field is named as RecordStamp, which is generally useful for CryptoCurrencyMeasure database.

Conclusion

After having a brief discussion about some useful DBCC log commands, one can wind up with the fact that these log commands plays an important role in SQL server application. SQL server user should have a brief knowledge about such command because these help in fixing errors that commonly occur in SQL. In addition, the commands monitor & track the information related to logs of a database that is currently in use.