How can we resolve Log full message in SQL Server 2008

Question

Post reply

How can we resolve Log full message in SQL Server 2008

bharat sethi

SSCommitted

Points: 1831
More actions
November 20, 2011 at 9:51 am

#248138

How can we resolve Log full message in SQL Server 2008?
Pl. guide.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

see

http://www.sqlservercentral.com/articles/64582/

---------------------------------------------------------------------

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 2

bharat sethi (11/20/2011)
How can we resolve Log full message in SQL Server 2008?
Pl. guide.

How big is the current log file?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Please read this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jnuqui SSCommitted Points: 1585 More actions · Answer 4

switch to simple recovery model. Backup database then shrink the log.

Sample below:

USE testdatabase

DBCC shrinkfile ( Employees_log, 1)

DO BACK UP FIRST!

mind you that if your using FULL recovery this will really eat up log. so plan things accordingly and make sure you have space for backup plans like this.

===============================================================

"lets do amazing" our company motto..

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

jnuqui (11/21/2011)
switch to simple recovery model. Backup database then shrink the log.
Sample below:
USE testdatabase
DBCC shrinkfile ( Employees_log, 1)
DO BACK UP FIRST!
mind you that if your using FULL recovery this will really eat up log. so plan things accordingly and make sure you have space for backup plans like this.

No. No. No!!!

Firstly that has consequences. (no mention of broken log chain or need for a backup after switching back to full recovery)

Second, there's no way of knowing at this point if the log is full because of a lack of log backups or something else. That's like telling someone who's car won't start to get petrol (gas) without checking whether maybe the battery is flat.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass