transaction log file is full

[sonajain]

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.

[sdc248]

What query did you use to backup the transaction log? The following should work.

backup log <your database name> with truncate_only

[Jason1]

Check that there is enough space on the disk to back the log up. Otherwise back the log up to a different drive. You can also set the database to auto grow if there is enough disk space.

[Shaily]

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.

[Shaily]

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..sysdata bases
SET status = status | 32768
WHERE name = '<Your Database Name Goes Here>'
IF @@ROWCOUNT = 1
BEGIN
COMMIT TRAN
RAISERROR('emer gency mode set', 0, 1)
END
ELSE
BEGIN
ROLLBACK
RAISERROR('unab le 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('<Y our Database Name Goes Here>','C:\file Path\filename.L DF')
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.

[robertk]

Dump the transaction log using the "no_log" option instead of just the "truncate_o nly" option. This will remove the inactive portion of the transaction log without making a backup copy and will save space additional space by NOT logging the operation. See following example:

dump transaction <your database name> with no_log

Robert from Alentus

[khickyphutz]

Good day to all:

I am just a newbie in MSSQL Server (Currently using SQL Server 2000), I am facing the same problem right now with our production server since our database administrator has resigned. Now, I do not know what to do to reduce the size of the transaction log since it almost have taken all the space in the disk. This might cause our production server to crush and other databases will be affected.

I would like to ask what would be the difference if I will backup the transaction log and If I will shrink the transaction log?

the current size of the transaction log of my database is 65GB and I can only save 9MB if I will shrink it. If I will try to backup the transaction log, There will be a 112 Error or disk space error since I only have 33GB left on my drive.

Now what would I do to reduce the transaction log file size? I do not have any knowledge in SQL Server Scripts.Ü

Hope you guys can help me with this matter.


Thank you and God Bless.

[arunsoni79]

Dear Shaily


When we delete log file then we faced any problem like server not responding. our database not configure . them what would we do.

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..sysdata bases
SET status = status | 32768
WHERE name = '<Your Database Name Goes Here>'
IF @@ROWCOUNT = 1
BEGIN
COMMIT TRAN
RAISERROR('emer gency mode set', 0, 1)
END
ELSE
BEGIN
ROLLBACK
RAISERROR('unab le 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('<Y our Database Name Goes Here>','C:\file Path\filename.L DF')
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.

[shiyasnk]

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.

Best method to reduce log file size is

1.de attach the original database
2.delete/rename log file manullay
3.attach again (.mdf file),log file wil create automatically

[baghul]

When I run into this common issue, I try the following 3 methods in order and they seem to work fine.

1. BACKUP LOG {Enter your DATABASE} WITH NO_LOG

2. Using Enterprise Manager,
select your database, all tasks, shrink database
you will get a new dialog box, click on Files
under database file, select the Log file
click Ok

3. use this TSQL command
DBCC SHRINKFILE (' {Enter your DATABASE} ',TRUNCATEONLY)

Hope that helps

Try FREE Remote DBA service
http://www.datatechnol ogyllc.com/dbaoffer.aspx

[kurmanal]

i have a problem when i renamed my LOG file

Best method to reduce log file size is

1.de attach the original database
2.delete/rename log file manullay
3.attach again (.mdf file),log file wil create automatically

I did this and I encountered a problem. My database used to have two log files, the second of which was not in use.. unfortunately, after detaching the database, i deleted that second log file, and now the database can not be attached back... please help!!!!

Thank you.

[Yogesh Upadhyay]

THis is alternative/workaround solution, you need to make the automatic utility for 1. Stop your SQL server.
2. Delete Your LDF file.
3. Start the SQL server – DO NOT try to access the database yet!

Thanks !