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.
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:
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.
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.
User can also deletes some transactions or entries from transaction log file to make disk space available for new entries.
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.
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.
One can add another log file a specific database on a separate disk by using ADD LOG FILE clause in ALTER DATABASE statement.
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.