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
I have a message that says Database zbc log file is full. what should i do now. help needed asap
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('yourdatabasena me_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
first, do this
backup log yourdatabasename with truncate_only
now do this
dbcc shrinkfile('yourdatabasena
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
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.
ASKER
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.
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
https://www.experts-exchange.com/questions/24414858/SQL-Server-lof-giles-issue.html?anchorAnswerId=24408796#a24408796
ASKER
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.
have a look at the link below.
http://www.sqlhub.com/2008/05/sql-server-truncate-transaction-log.html
http://www.sqlhub.com/2008/05/sql-server-truncate-transaction-log.html
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?
>>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...
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
thanks.
ASKER
thanx
http://sqlservernation.com/blogs/admin/archive/2009/05/17/why-is-my-log-file-so-big.aspx