Introduction:
SQL Server has a very important file called Log file, that records all transactions and modifications made in SQL Server data. But when the SQL Server transaction log becomes full, it will start creating a problem and start showing SQL Server Error 9002. The log file will start getting filled when database is in online and in recovery mode. If the log file is fully filled when the database is online, it will keep the database in online status. The online status of database implies that users can only read the data but cannot update it.
Whereas if transaction log file fills during recovery, the database engine will mark the database as RESOURCE PENDING. To access and update the database user needs to make log space available by truncating the data of Log file. In this article we will discuss the different Scenario behind SQL Server transaction log full.
Importance Of SQL Server Transaction Log File:
SQL Server transaction log file is a very important component of the database, because it is responsible for getting the database back if any system failure occurs in the database. In addition, the transaction log file is also required for bringing the database back to a consistent state.
Therefore it is very crucial for the SQL users to keep their transaction log file safe from any damage.
Log File Truncation
Log file truncation frees up the space in the log file and make the space available that can be reused by the transaction log. Log truncation is required to keep the log file free from filling. Log truncation cut offs the all inactive virtual files from the logical transaction log of a SQL Server database to make the space available for Physical transaction log. If the user will not cut off the inactive files using truncation, it would eventually fill all the disk space that has been allocated for physical transaction log files.
To avoid such problem truncation process will occurs automatically after the following events:
- In the simple recovery model
- In the full recovery model
Manage The Size Of Transaction Log File To Resolve Error 9002
User can also manage the size of transaction log files. It is possible for the users to physically shrink or expand the physical Transaction log file of a SQL Server database.
- For managing the space of transaction log file, user must have to monitor the space currently in use. User can use DBCC SQLPERF command to find the used space.
- After monitoring, user can shrink the size of Transaction log files. By shrinking the size of log file of SQL Server database, it will migrate the data from the specified file to other files in the same filegroup. For this user can use DBCC SHRINKFILE command.
How To Troubleshoot The Error 9002?
There are many other methods that will help the user for the resolution of error 9002
- By creating the backup of transaction log file on other storage space and allow Database Engine to truncate the log file that will frees up space for new entries.
- If the log file has not enough space, user can move the transaction log file to a hard drive with sufficient space.
- User can increase the size of a transaction log file upto 2 TB, it can be done by increment the single growth and enable autogrowth using ALERT DATABASE statement.
Conclusion:
After going through the Scenario behind SQL Server transaction log full, user can fix the all issues that arises when the log file becomes full. In addition user can also fix the error 9002 by following above mentioned instructions.
EmoticonEmoticon