Tips to Fix SQL Server Error Code 9002 – Transaction Log Full

SQL server error code 9002 is very frequent error that occurs when transaction log file is full. Whenever the SQL Server transactional log file is run out of space, causes 9002 error. This error can be encounter when database is online or in recovery mode.


If the log file fills while the database is online, it will remains online, In that case user can only read but can not update the database. In addition, if transaction log file fills during recovery, it will mark database as Resource Pending. To tackle both the conditions user needs to free up some space in log file.

How To Manage Space In Transaction Log File & Fix SQL Server Error Code 9002

Transaction log file is a very crucial part of SQL server as it provides log of all activities that are performed on SQL database. It also restore entire SQL database data if any failure occurs in SQL database. Therefore it very important to make transaction log file free from any failure or error. To Troubleshoot the SQL Server Error Code 9002 user can perform following actions:

  • Transaction Log Truncation

    If the user never deletes any transactions logs that are recorded in the log file, it would fill the entire disk space that is allocated to the physical log files. The Log truncation helps in making the space available for reuse in transaction log file. Log truncation removes all inactive virtual file that are created in logical transaction log file to make the space available for new transactions log.

  • Importing Transaction Log File to Another Device

    If the drive having the log file, but disk does not have the sufficient space then user can move that file to another device.

    To do this, firstly user must ensure that the new device has enough space for storing the log file and needs to detach the database associated with the log file by executing sp_detach_db. This will detach a database from log file.

    After detaching, user can move the transaction log files to other device and then can reattach the database using sp_attach_db.

  • Make Log Space Available

    User can also deletes some transactions or entries from transaction log file to make disk space available for new entries.

  • Backup The Log

    Under full or bulk log recovery model, if transaction log is not backed up, then it is necessary to backup the transaction log that will allow database engine to truncate the logs to the point it was last backup.

  • Enlarging A Log File

    User can also increase the size of log file upto 2 TB. User can use MODIFY FILE clause in the ALTER DATABASE and by specifying the SIZE and MAXSIZE. This can be also done by enabling autogrow, which can be done by setting a non-zero growth increment for FILEGROWTH option in ALTER DATABASE statement.

  • By Adding Additional Log File

    One can add another log file a specific database on a separate disk by using ADD LOG FILE clause in ALTER DATABASE statement.

  • By Managing Log File Size

    Using DBCC SHRINKFILE command for shrinking the size of log file of SQL server. once the user will monitor space used by log file with help of DBCC SQLPERF command, they can run DBCC SHRINKFILE command.

Conclusion:

When no space left in transaction log file, the SQL server Error code 9002 is starts prompting which creates huddle in entire working of SQL server. To make space available in SQL log file, user can use different methods that will frees up space in transnational log file. Once the occupied space will become free, it will automatically resolves error code 9002 from the SQL server.

Read These Useful Articles Too...

SQL Server Error 5171...read more

SQL Server Error 3013 Restore Database... read more

SQL Server Fatal Error 832...read more

Error Code 18456 of SQL Server...read more