big deletes transaction log full
-
Hello I'm trying to run some big delete statements and keep running into the following exception: Error - The transaction log for database 'SomeDatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases, Line: 16 I've already created a 20GB log file for it, still failing
dev
-
Hello I'm trying to run some big delete statements and keep running into the following exception: Error - The transaction log for database 'SomeDatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases, Line: 16 I've already created a 20GB log file for it, still failing
dev
I know this is a long shot, however I would look for an explanation in the log_reuse_wait_desc column in sys.databases, more particularly at line 16. And if that does not help, at least tell us what you see there. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, and update CP Vanity to V2.0 if you haven't already.
-
Hello I'm trying to run some big delete statements and keep running into the following exception: Error - The transaction log for database 'SomeDatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases, Line: 16 I've already created a 20GB log file for it, still failing
dev
Are you using transactions ? If so, maybe you need to change your algorithm so that you are not deleting so many rows at one time. Something like: select key from mytable into #Temp1 Loop begin transactioin delete some rows where select top 1000 from #Temp1 delete same rows from #Temp1 Commit Until No more rows in #Temp1 Otherwise, if this is part of a one-time data cleanup, then you might want to A) Get your users off the database, so that you can do system maintenace. 1) Take a full backup of the database 2) switch the recover mode of the database to "simple" 3) Perform your delete 4) Take another full backup 5) Put the database back into the recovery mode you had before 6) Allow your users back into the database Just some ideas. Good luck. :thumbsup:
-
Hello I'm trying to run some big delete statements and keep running into the following exception: Error - The transaction log for database 'SomeDatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases, Line: 16 I've already created a 20GB log file for it, still failing
dev
Devvy, look at David's answer, understand it and implement the idea, he has identified problem and told you the solution. Managing really large transactions can be a complete PITA.
Never underestimate the power of human stupidity RAH