September 10, 2006 at 5:56 am
If a transaction log has blown out and filled the array where it resided - lets say it went unchecked for 8+ hours, and the SQL service was restarted.
Whats the possible impact on that database when in full recovery mode?
TIA
Dave
September 10, 2006 at 10:04 am
SQL should be able to commit all the xaction to a point just before SQL was restarted (assuming this was not a very long running uncommited xaction).
If you have recovery model to full the it is better you take Xaction log backup often.
Thanks
Sreejith
September 11, 2006 at 1:24 am
Hi
restart of service can take a while when tlog is big...but it will complete eventually and indeed commit all transaction in db.
JP
September 11, 2006 at 6:56 am
To avoid this issue in future better script a job that checks Transaction Log,
have code for Transaction log backup with truncate_only
followed by shrink log file and full DB backup.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
September 11, 2006 at 7:03 am
You may want to check how your application is designed too. We had a bad situation once where the log file was huge and the SQL Service was stopped. The database could not fully recover because thousands of clients were trying to continuosly connect. The application was designed to re-connect without setting any kind of limit. Anyway, we had to change the server's IP, let the database recover, then change the IP back. I never thought this type of situation could happen until it did 🙂
September 12, 2006 at 2:14 am
If you stop the stop the service when tranaction log is full, then depending upon the recovery interval configured for your server, there can be less or many tranactions that are yet to be committed. Next time when you start your server, the completed (but uncommitted)tranactions will be rolled forward, and the uncomplete tranactions will be rolled back. Depending upon the number of such tranactions, the recovery process might take more time.
For the FULL recoveery model, increase the frequency of yout tranaction log backup, but do not use truncate_only, as it will not actually backup the database but truncate the log file (and you will not be able to benefit from keeping the database in FULL recovery mode)
Pankaj
Pankaj Khanna
Database Administrator - SQL Server 2000
Keep hope to keep you intact...
September 12, 2006 at 4:29 am
Thanks for taking the time to reply - much appreciated.
D.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply