1. Transaction log is full
2. Transaction log cannot shrink
In these two scenarios, main concern is why transaction log cannot be reused. If log is waiting to do the log backup/checkpoint/active transaction, it cannot be shrunk. So firstly we need to find out why the log cannot be reused and then solve the problem.
--Check log used space-- dbcc sqlperf(logspace) --Check log reuse wait type-- select log_reuse_wait_desc,* from sys.databases --Check if there is active transaction-- dbcc opentran
If transaction log is full, making log space can be reused doesn't decrease the size of physical log file size. We need to manually shrink the log file size.
--Find the logical name of database’s log file-- USE [Your database] GO SELECT Name AS LogicalName, filename AS PhysicalFile FROM sys.sysfiles GO --SHRINKFILE command-- USE [Your database] GO -- Shrink the truncated log file to 8 MB-- DBCC SHRINKFILE (LogicalName_Log, 8); GO
DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.