Log File of Database is FULL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Log File of Database is FULL

Author

Topic

srinivas.alwala
Starting Member

30 Posts

Posted - 2008-08-12 : 05:53:32

Hi,

I have been facing a problem of Log file of DB is full. Back up the transaction log for the database to free up some log space, since last 4 days.

I have used the foll query to update the same. The space available in drive is around 1GB.

---------- Query
USE Database_Name
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE Database_Name
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (DMIS_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE Database_Name
SET RECOVERY FULL;

Output :

Server: Msg 9002, Level 17, State 6, Line 2
The log file for database 'DMIS' is full. Back up the transaction log for the database to free up some log space.
Could not write a CHECKPOINT record in database ID 10 because the log is out of space.

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The same problem arising again. In case anybody has solution for this, Kindly let me know how to prevent this error.

Regards,
Srinivas Alwala

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 05:58:36

You could execute

BACKUP LOG DMIS WITH_TRUNCATE ONLY

to free your log space. But beware that this breaks your restore chain.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources