Log file is full

Question

Post reply

Log file is full

kiranmca24

Old Hand

Points: 389
More actions
November 6, 2009 at 9:36 pm

#213553

if the logfile is 100 percent full and there is no harddisk space,in this time is it possible to shrink the logfile?if it is not possible how can we resolve this problem?
Regards,
KiranKumar P

Viewing 8 posts - 1 through 7 (of 7 total)

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

sanketahir1985 SSCarpal Tunnel Points: 4473 More actions · Answer 1

offcourse u can shrink .

only user shouldn't be using that database.

Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......

lokhande.deepali SSC Eights! Points: 833 More actions · Answer 2

Hi,

To resolve the problem backup your database;

Backup log database with truncate_only

this will truncate the non active logs from your log file then try to shrink your database Log file.

Before doing this confirm your Database Backup avalability

Rd,

Deepali

arun.sas SSChampion Points: 11831 More actions · Answer 3

Hi Sanket,

In the BOL for shrink files shows: “The database being shrunk does not have to be in single-user mode; other users can be working in the database when the file is shrunk. You do not have to run the instance of SQL Server in single-user mode to shrink the system databases”. Ref: http://msdn.microsoft.com/en-us/library/ms189493.aspx

But you say that “user shouldn't be using that database”, any specific reason?

Paul White SSC Guru Points: 150467 More actions · Answer 4

sanketahir1985 (11/6/2009)
offcourse u can shrink .
only user shouldn't be using that database.

Of course? But the log is 100% full...how do you propose to shrink it?

There is no restriction on users being in a database for shrinking.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 5

lokhande.deepali (11/7/2009)
To resolve the problem backup your database;
Backup log database with truncate_only
this will truncate the non active logs from your log file then try to shrink your database Log file.
Before doing this confirm your Database Backup avalability
Deepali

I think you mean 'backup your log' not database. I hope so anyway.

The answer is to backup the log (perhaps to a network device). This should allow the normal checkpoint process to free up space in the log file. Once things are back to normal, only in exceptional circumstances should you consider shrinking the physical log file - and only after determining why it needed to grow so large in the first place.

By the way:

Backup log with truncate_only is deprecated in 2005, and removed from 2008.

The preferred method is to change the recovery model to simple.

But neither of those should be required here - after all, we don't want to break the recovery chain unnecessarily - that would be silly 🙂

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Please read through this: http://www.sqlservercentral.com/articles/64582/

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

sanketahir1985 SSCarpal Tunnel Points: 4473 More actions · Answer 7

hiii Arun,

i mean to say if database log file is being used by any of the user ,it won't allow to shrink the log file

Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......