January 28, 2009 at 3:00 am
There comes a time when the log file is full and that the select query cannot execute.
what is the best way to deal with this situation?
January 28, 2009 at 3:33 am
nabajyoti.b (1/28/2009)
There comes a time when the log file is full and that the select query cannot execute.
:unsure:
I assume you mean the transaction log is full.
If that's the case you probably have your database in FULL recovery mode, but haven't scheduled regular transaction log backups.
If all my assumtions are correct, you either can change the recovery model to simple or take a transaction log backup. If my assumptions are wrong, please provide more information.
[font="Verdana"]Markus Bohse[/font]
January 28, 2009 at 5:17 am
How do i find that it is in full recovery mode?
How do i change the recovery model to simple or take a transaction log backup?
January 28, 2009 at 6:41 am
nabajyoti.b (1/28/2009)
How do i find that it is in full recovery mode?
How do i change the recovery model to simple or take a transaction log backup?
Reading those questions I really think you either need a good book about administrating SQL Server databases or a DBA who can do it for you. Anyway here's an article which explains the basics about transaction logs, recovery model and how to manage them.
http://www.sqlservercentral.com/articles/64582/
You can change the recovery model by running the following statement:
ALTER DATABASE myDatabase SET RECOVERY SIMPLE
just replace myDatabase with the name of your database.
[font="Verdana"]Markus Bohse[/font]
January 28, 2009 at 7:44 am
nabajyoti.b (1/28/2009)
How do i find that it is in full recovery mode?How do i change the recovery model to simple or take a transaction log backup?
Is there a DBA there? Or did you just inherit the job?
I'd suggest you start by reading thorugh Books Online on recovery models, the differences between them, the reason you might want to run in full. Then read through the sections on backups and recovery, especially log backups, especially the reasons you need them.
As well as the article that Markus referred to, read through this - http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
Once that's out of the way, find out (if you don't already know) what the allowable data loss for this database is in case of a disaster. Is recovering to the previous day's full backup acceptable, or must the recovery be much more complete?
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply