Link to home
Start Free TrialLog in
Avatar of aatishpatel
aatishpatel

asked on

SQL Server log file full

Hello,
I have a message that says  Database zbc log file is full. what should i do now.  help needed asap
Avatar of chapmandew
chapmandew
Flag of United States of America image

you can do a back or truncate your log...you'll probably need to truncate though, it sounds.  

first, do this

backup log yourdatabasename with truncate_only

now do this

dbcc shrinkfile('yourdatabasename_log', 0)

now, set your database recovery mode to simple so that you don't need to worry about this anymore...you're obviously not needing to recover to a point in time.
HTH,
Tim
Avatar of aatishpatel
aatishpatel

ASKER

but for safety purpose how do i backup the .lfd files. and if i shrink it the data will be loat right?
if you truncate the log w/o backing it up, it will be lost (inactive transctions).  You can modify your maintenance plan to include log backups, or just simply set your recovery model to simple...which is probably the best option for you.
that's the error message that the log is full please help


Step ID            13
Server            IHS-MAIN-DB-13
Job Name            P58 - Daily Maintenance
Step Name            poststep: ng_reconfigure to reset computer
Duration            00:00:00
Sql Severity            17
Sql Message ID            9002
Operator Emailed            
Operator Net sent            
Operator Paged            
Retries Attempted            0

Message
Executed as user: IHS\SqlAdmin. The transaction log for database 'P58' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases [SQLSTATE 42000] (Error 9002)Unable to open Step output file.  The step failed.


so now what exactly should i do.
I've already told you how to fix the problem.
You've already made several posts for the same problem...are you just not understanding what we're telling you to do?  

https://www.experts-exchange.com/questions/24414858/SQL-Server-lof-giles-issue.html?anchorAnswerId=24408796#a24408796
sorry friend but i am a newbe and sometimes having a hard time to understand. But i am following to what you said. thanx anyways
sure...just follow the statements I showed above and you'll be all good.
the article is wrong...no reason to shrink the file before you do a truncate on the backup.
hi chapman,

Why one can't shrink the file when it gets full?
>>Why one can't shrink the file when it gets full?<<
Because it will not shrink.  How is that for a reason?

In order for you to be able to shrink the transaction log you must first remove the active transactions.  This is done by using BACKUP LOG...
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks.
thanx