Impact of stopping service when trans. log is full

  • 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

  • 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

  • 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

  • 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

  • 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 🙂

  • 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...

  • 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