Event ID 9002 - Log full

Encountering a large number of 9002 error messages in SQL Server, along with an error asking if you want to terminate or debug the issue, typically indicates that the transaction log for one or more databases has become full. The 9002 error in SQL Server relates to the log file reaching its maximum capacity. Here are steps you can try to follow to resolve this issue:

Check the database status: Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance. Expand the "Databases" folder and check the status of the affected database(s). If a database shows a "Suspect" or "Recovery Pending" state, it indicates a problem with the database.

Identify the full transaction log database(s): Review the error log or the error message details to determine which database(s) have encountered the 9002 error. This will help you focus on resolving the issue for those specific databases.

Free up transaction log space: There are several approaches to freeing up space in the transaction log:

a. Back up the transaction log: If the database is in the Full or Bulk-Logged recovery model, perform a transaction log backup. This action allows the transaction log to be truncated and frees up space for new transactions. Use the following SQL command to perform the log backup:

BACKUP LOG [DatabaseName] TO DISK = 'C:\Backup\TransactionLogBackup.trn'

b. Truncate the transaction log: If you don't need point-in-time recovery and can afford to lose the transaction log backups, you can truncate the transaction log by running the following command:

BACKUP LOG [DatabaseName] WITH TRUNCATE_ONLY

Note: The TRUNCATE_ONLY option is deprecated in newer versions of SQL Server. If you are using a version that no longer supports this option, you'll need to switch to the Simple recovery model temporarily.

c. Increase the transaction log size: If the transaction log frequently runs out of space, you might need to increase its size. You can use SSMS or execute the following command to increase the size of the transaction log file:

ALTER DATABASE [DatabaseName] MODIFY FILE (NAME = 'LogFileLogicalName', SIZE = NewSizeInMB)

Resolve long-running transactions: Long-running transactions can prevent the transaction log from truncating. Identify any long-running or open transactions using the following query:

Copy code
DBCC OPENTRAN

If there are any open transactions that need to be completed or rolled back, address them accordingly.

Monitor log space usage: After implementing the above steps, monitor the transaction log space usage. Ensure that regular transaction log backups are scheduled to prevent it from growing excessively.