The monitoring system has raised an alert that a drive on a server that hosts SQL Server instances is low on space. It was found that a transaction log file has grown very large and the file cannot be shrunk as there is no free space available in the file. What steps should i take to determine what is causing the log file to grow?
3
-
1Please, edit your question adding the version of SQL Server as it might be helpful. What is the recovery mode of the database in question? Is there any open transaction runing for a long time on that database? What command did you use to shrink the file?– RonaldoOct 17, 2019 at 13:04
-
1And what are the contents of the log file.– harrymcOct 17, 2019 at 13:15
-
This question on DBA.SE has a very comprehensive answer for this question dba.stackexchange.com/q/29829/52344– AMtwoOct 29, 2019 at 13:06
Add a comment
|
1 Answer
If the transaction log is growing out of control please make sure are running transaction log backups that truncate the log.
Long running open transactions may be a cause of growing transaction logs. Use DBCC OPENTRAN statement of SQL Server Management Studio (SSMS) to check the longest running transactions on SQL Server instance. If there is an open transaction then, it will offer session_id (SPID) of the connection, which has transaction open. You can then use that SPID and the sp_who2 stored procedure to see to what connection the transaction belong. You can kill that SPID.
After that, you can switch the recovery model of the database from FULL to SIMPLE, and then try to shrink the database. You can put the database back to FULL, but you need to schedule execution of full backups and transaction log backups or the issue will happen again.
Another possible cause of transaction log growing unexpectedly, make sure the database is not participating on replication/mirroring and the communication between this database and the other get lost. If for some reason, the link between the two databases is affected, the transaction log will continue to grow on the principal database until it receives acknowledgement from the mirrored database. As a result the transaction log will grow and not re-use space.
