For this post we have a guest contributor with some SQL goodness. This will definitely come in handy for us System Center folks as all of the System Center components use SQL. This guest post was written by: Andrew Jackson a SQL expert in the SQL community check him out on the following sites: LinkedIn , google+, his blog. Here is the actual blog post:
Overview
In SQL Server, Every database file is associated with a transaction log that contains all the records of transactions and modifications made by each transaction. The log file plays a very important part as it helps in disaster recovery. The transaction log should be truncated or cleared regularly to keep the size of log file from filling up. One of the common error encountered by the users of the SQL Server is when the transaction log is full, which is possible by various reasons. This blog will be discussing about the Transaction Log Full due to Log Backup.
Problem Statement
The following error message will be displayed like this “The transaction log for database is full due to Log Backup.” It happens when the user is unable to make data entry due to insufficient space. The transaction log file grows very large and consumes too much space over server restricting addition of any data into SQL Tables. The error message is not because of log backup but it actually means the virtual files with the transaction log could not be reused, as it requires log backup. User need to make sure that Log file growth is unrestricted, Storage of log file should have enough space, and regular log backups should be taken.
Possible Solution
There may be several solutions for the situation when the Transaction log file is full such as creating backup or truncate the transaction logs, making the log space available, moving file to another disk drive, increase the log file size or add another log file on different disk.
Since we are talking about the Transaction Log Full Due to Log Backup, we will be performing truncate operation on the transaction log file. Steps that need to be followed to sort the issue are:
- Open Microsoft SQL Server Management Studio in order to connect to the desired SQL server database.
- Select the database, which transaction file needs to be truncated
- Type the T-SQL script below
| USE db_name
GO ALTER DATABASE db_name SET RECOVERY SIMPLE GO DBCC SHRINKFILE (db_name_log,5) GO ALTER DATABASE db_name SET RECOVERY FULL GO
|
- Click on Execute button and run these commands
Another solution is to stop the SQL Server Service and find the location of the transaction file to rename it. When the SQL Service is re-started and logged in to the database, a new log file will be created. If the new log file is not created still and displays the same error user can follow these steps:
- Go to SQL Server Management Studio to connect to the database
- Right-click on the desired database, Go to Tasksà Detachà Browse the location of the file
- Cut the log file and move it to another drive or external hard drive having enough space.
- Use the scripts inside SSMS to attach the SQL database file with a missing log file
“sp_attach_single_file_db @dbname=’db_name’, @physname=’C:\Program Files\Microsoft SQL Server\MSSQL11.InstanceName\MSSQL\DATA\db_name’”
{@dbname=your database name, instance name= your instance name and @physname=physical location of the file}
- New log file will be created that will be usable again.
Conclusion
The blog has been aimed to discuss about the error when the Transaction Logs are full in a log file, restricting any entry in the database. The problem statement has been included with in-depth description of the causes of the error and lastly, the solution steps that need to be followed when the user faces such situations are described. There are solutions like to truncate the transaction log and ways to create the new log file.
