How to Monitor Transaction Log File Usage in SQL Server

SQL Server
  • MOn a SQL server, a task record is a significant part used to follow all changes made to a site. Work logs can be extremely huge and consume a great deal of circle space, which can influence execution. It is essential to screen the utilization of the log document and make a move depending on the situation to forestall issues.

There are two sorts of SQL Server log records: dynamic log and idle log. A practical log is the one at present utilized by SQL Server. Idle log is one that contains information for past tasks. At the point when the work is finished, it is composed on both dynamic and latent logs.

You can see the size of your work signs in SQL Server Management Studio (SSMS). To do this, grow the server hub, extend the Management organizer, and snap-on SQL Server Logs.
In the image beneath, you can see that my information document is 21.6 GB.

Work log size will keep on becoming for however long there is no constrained action. At the point when the undertaking is finished, it is taken out from the log record. On the off chance that you have a great deal of non restricting undertakings, you might have to make a move to let loose space in the log record. See RemoteDBA.

One method for doing this is to cut a log document. Cutting the log document will eradicate everything made in the record and reset it to its unique size. This should be possible utilizing T-SQL or SSMS.

To limit login document utilizing SQL, associate with your site utilizing SSMS and issue the accompanying order:

  • IssueLIST MyDatabase TO DISK = ‘MyDatabase.trn’ AND FORM, NOINIT, NAME = ‘
    • This will make a reinforcement duplicate of the undertaking log. You can cut the log record by giving the accompanying order:
    • USE MyDatabase
    • GO
    Lessen the log record to 1 MB
    • DBCC SHRINKFILE (MyDatabase_log, 1)
    • GO
    • You can likewise restrict the log record utilizing SSMS. To do this, right-click on the data set and select Tasks> Minimize> Website. This will open the Shrink Database window.
    • Click the Files tab and select the log document you need to limit.
    • In the Minimize Action segment, select Minimize Only.
    • Click OK to limit log document.
    • One more method for opening up space in a work record document is to take reinforcement duplicate. This will erase all that made in the log record and permit it to be reused. To back up, interface with your site utilizing SSMS and issue the accompanying order:
    • MYDatabase BUILDING LOG IN DISK = ‘MyDatabase.trn’ AND FORM, NOINIT, NAME = ‘My Website Log’, JUMP, NOWWIND, NOUNLOAD,
    • Math = 10
    • This will make a reinforcement duplicate of the errand log. You can cut the log document by giving the accompanying order:
    • USE MyDatabase
    • GO
    Diminish the log document to 1 MB
    • DBCC SHRINKFILE (MyDatabase_log, 1)
    • GO
    • You can likewise remove a portion of the reinforcement duplicate utilizing SSMS. To do this, right-click on the information base and select Tasks> Save duplicate. This will open a Database window.
    • Click on the Options page and select Trim log when done. This will erase all that made in the log record.
    • Click OK to make reinforcement duplicate of the classification.
    • Screen record logging is a significant piece of dealing with a SQL server site. By doing whatever it may take to limit or make reinforcement duplicate of the log document, you can let loose space and further develop execution.


How frequently would it be advisable for me to eliminate my login document?
It is smart to cut the log document routinely, one time each week. This will keep the document size-controlled and assist with further developing execution.

Could I at any point erase the log record?
No, you can’t erase the log record. SQL Server requires this record to follow every type of effort. On the off chance that you erase a record, you will lose all information on your site.

For what reason is my log document so huge?
There are a couple of justifications for why your action log can be so enormous.


All in all, it is vital to screen the utilization of the log record and also do whatever it takes to forestall issues. Diminishing the log record or taking reinforcement duplicates can likewise assist with opening up space and also further developing execution.

SQL Server Data Sets has a trade log that records all that is accomplish and also the reason for trading data created by any trade. This contains adequate information to address all changes made to the data report as a component of any different trades. To see data on the trade log of a particular informational collection, there is an unapprove SQL server request called DBCC LOG. This order is utilize to distinguish the trade sign based on specific data. DBCC login request consequently shows log information and also this request won’t give you any article information.


Alex George

Learn More →