Hi,
The database transaction log file is full. I cannot delete the log file. when I try to take backup it throws error. Any other way so that I can write more transaction without any problem. Please reply matter is urgent.
Hi Sona,
I too have faced the same problem. In my case there were 2 causes.
1)The size of log file was restricted to 2 MB and the log had already grown to this size. By taking the backup of the Log the size could be decreased. If the disk is out of space it would again give an error during backup.
If you are not out of disk space try either of the two
i) Take a backup of the log
ii) Increase the size of the log file.
2)If the size of the logfile has grown and occupied the whole disk space than you can do this work around. You can do this also in case the LDF file (the transaction log file) is deleted or corrupted.
DBCC REBUILD_LOG command can be run in SQL to rebuild the log file from the actual database.
The first issue with the DBCC REBUILD_LOG command was that the database needed to exist in MSSQL before this can be run. You can get around this by creating a dummy database as follows:
1. Stop your SQL server.
2. Delete Your LDF file.
3. Start the SQL server – DO NOT try to access the database yet!
Your MDF file is present in MSSQL but since there is no transaction log file SQL will throw a wobbly if you try to access the database. What you need to do next is run a series of SQL commands to set the database to emergency recovery mode. This is done via the SQL Query Analyzer under the Master database.
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
BEGIN TRAN
UPDATE master..sysdatabases
SET status = status | 32768
WHERE name = '<Your Database Name Goes Here>'
IF @@ROWCOUNT = 1
BEGIN
COMMIT TRAN
RAISERROR('emergency mode set', 0, 1)
END
ELSE
BEGIN
ROLLBACK
RAISERROR('unable to set emergency mode', 16, 1)
END
GO
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO
You now need to restart SQL server. Returning to the SQL Query Analyser run the following command
DBCC REBUILD_LOG('<Your Database Name Goes Here>','C:\filePath\filename.LDF')
ALTER DATABASE <Your Database Name Goes Here> SET MULTI_USER
GO
DBCC CHECKDB ('<Your Database Name Goes Here>')
This will then rebuild the transaction log to the path you specified in the REBUILD_LOG command and your all done!
Again be aware that you may lose some transactional integrity since the LDF file is an integral part of the database. This method can also be used if the LDF file has been accidentally removed.
12
