none
Full log disk, cannot cancel query

    Question

  • hello, on production database, I try to do a query that I know will take much time/log, but my estimations where wrong, so the log disk were full, no error displayed on screen or rollback... so I wait about 1 hour after that. 

    Then I try to cancel the query, it´s on rollback action, but it doesn´t finish either. 

    when I try to kill the process, I got the error: 

    SPID 125: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds. 

    it was like that for about an our. 

    so what to do now?

    the query that make this mess was a simple insert into a new table, but the origin table seems to be too big for the log file. 


    Gilberto H.

    Friday, July 12, 2013 7:56 PM

Answers

  • No! Do not shutdown (yet)! If you do, recovery process will be launched on startup and you will stuck again because it has not enough space still, and you are just loosing time.

    Add another log file on the other disk which has plenty of space, and grow it immediately to at least a size of that table+indexes. Then shutdown. Recovery process should now successfully rollback that insert.

    Several advices: monitor free space of the log drive! It can suddenly grow 30GB within 3minutes, so you better have at least that much free space on log disk. Do not do very large transactions. Do the job piece by piece, e.g. 4000 rows at a time. Or, add a secondary log file that will fit the operation and drop that file afterwards.

    transaction log automatically reserves space for rollback to succeed. But if disk is full it can't reserve that space upfront and if rollback is issued or transaction hits the log top, transaction is doomed.

    read my series of blog posts about transaction log here: blog.sqlxdetails.com

    Friday, July 12, 2013 9:45 PM
  • Please do not shut down the computer, let it finish..... Try open another window and issue backup log file...

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, July 14, 2013 4:21 AM
    Answerer
  • Ok this is what happend, 

    Before taking any decition, I went to the maths, and check for Table size and index size, to try to calculate the amount of space I will require for the log. 

    The space need was over the free space of the server, so a shutdown was required. 

    We analize the options of shutdown with no wait or normal shutdown. 

    at the end we decide that the normal shutdown will be the best, since it will add ckeckpoint to try to avoid any transaccion loss. 

    So we shutdown the SQL servide and restart it. of course it enter to recovering mode (120minutes of eating my nails). 

    at the end it goes back online, so I ran the queries to clear log, it was about 500 checkpoint created on the logs, so I ran it over and over just to get ride of the log and restore it to normal size. 

    Maybe was not the best option, I appreciate everybody for the help. 



    Gilberto H.

    Tuesday, July 16, 2013 3:41 PM

All replies

  • Hi Gilberto,

    When your log disk is full, a rollback or kill is not going to help. You need more log space to do the rollback.

    What might help is runnig the command "shutdown with nowait" from management studio or a SQL prompt. Do not try to stop the service, because it will try to stop gracefully, which is not going to work. After the "shutdown with nowait", maybe you are able to restart the service. The unfinished very big transaction might be able to roll back. I say "might" because I am not 100% sure. This is why I never set my transaction log on autogrow. You need to implement a good backup strategy, including transaction log backups, plus alerts that fire on, say 60% and 80% transaction log filled. The alerts should execute a transaction log backup. Still, very large transactions will be a problem, but if that happens, your hard disk will not be filled for 100% with a gigantic log file.

    Friday, July 12, 2013 8:09 PM
  • Shutdown with nowait is the best option here.
    Friday, July 12, 2013 8:23 PM
  • No! Do not shutdown (yet)! If you do, recovery process will be launched on startup and you will stuck again because it has not enough space still, and you are just loosing time.

    Add another log file on the other disk which has plenty of space, and grow it immediately to at least a size of that table+indexes. Then shutdown. Recovery process should now successfully rollback that insert.

    Several advices: monitor free space of the log drive! It can suddenly grow 30GB within 3minutes, so you better have at least that much free space on log disk. Do not do very large transactions. Do the job piece by piece, e.g. 4000 rows at a time. Or, add a secondary log file that will fit the operation and drop that file afterwards.

    transaction log automatically reserves space for rollback to succeed. But if disk is full it can't reserve that space upfront and if rollback is issued or transaction hits the log top, transaction is doomed.

    read my series of blog posts about transaction log here: blog.sqlxdetails.com

    Friday, July 12, 2013 9:45 PM
  • Maybe you do not have to shutdown the whole sql instance just because of one database. Try to set database offline (after adding log file of course). Then put db back online.
    Friday, July 12, 2013 9:50 PM
  • What is @@version?

    What is the recovery mode?  FULL? SIMPLE?

    Can you add file(s) to the log?


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration


    Friday, July 12, 2013 10:14 PM
    Moderator
  • Please do not shut down the computer, let it finish..... Try open another window and issue backup log file...

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, July 14, 2013 4:21 AM
    Answerer
  • Log backup wont help. It wont clear the log because we have long-running transaction that makes large portion of the log active. VLF with active transaction cannot be marked as free (cleared) and cannot be reused, so log must grow. That is true regardless of recovery model. That would happened even in SIMPLE recovery model. Log backup in full and bulk logged recovery model, and checkpoint in simple recovery model will trigger the log clearing process, but it wont clear anything in this case.
    Sunday, July 14, 2013 7:40 AM
  • as everyone said let it get rollback, as you can see the progress status in the errorlog, if you are going to do forcefully that May lead other issues.

    Usually  it is better to do in Test server then Implement on Prod server(if both are alike(h/w & s/w) & up to date of sync) because this type of an approach makes the business to make go down & need to await to get it recover for an long time.


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Sunday, July 14, 2013 8:13 AM
  • Ok this is what happend, 

    Before taking any decition, I went to the maths, and check for Table size and index size, to try to calculate the amount of space I will require for the log. 

    The space need was over the free space of the server, so a shutdown was required. 

    We analize the options of shutdown with no wait or normal shutdown. 

    at the end we decide that the normal shutdown will be the best, since it will add ckeckpoint to try to avoid any transaccion loss. 

    So we shutdown the SQL servide and restart it. of course it enter to recovering mode (120minutes of eating my nails). 

    at the end it goes back online, so I ran the queries to clear log, it was about 500 checkpoint created on the logs, so I ran it over and over just to get ride of the log and restore it to normal size. 

    Maybe was not the best option, I appreciate everybody for the help. 



    Gilberto H.

    Tuesday, July 16, 2013 3:41 PM
  • Thanks for sharing Gilberto. Was that long rubning command a query or ins/upd//del? What is the "query to clear the log" you were running over and over? What recovery model db is in?
    Tuesday, July 16, 2013 6:42 PM
  • Maybe a not so interesting observation.

    - If you do something with all records in a table, the amount of log needed when you rollback just before you were trying to commit might be almost twice the space of the table plus its indexes (as long as no other transactions are interleaved). This is because the rollback also uses up log space. This log space only will become available after the rollback succeeded.

    • Edited by Chris Sijtsma Thursday, July 18, 2013 8:58 PM First post not complete
    Thursday, July 18, 2013 8:57 PM