SQL Server Performance Forum – Threads Archive

Transaction Log Filling Up

Presently I have a text file with approx 10,000 update statements. When these are pasted into and run in query analyzer, the transaction log fills up along w/ the hard drive after the script runs for a LONG time. Does anyone know if query analyzer has some quirk about running too many statements in one script. There are no loops, so there is no reason for it to continuously run statements. I believe the 10,000 updates may be repeated indefinitely. Any help would be appreciated! Regards,
Brian

I can’t follow you.
If the problem is transaction log fills up, what recovery model do you have in database?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Depends on how much data is getting affected by each update. BTW, have u tested this update with some other tool like OSQL.exe before singling out Query Analyzer? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Put a PRINT after each one and set your Options to scroll results as received. This isn’t a Query Analyzer issue. You need to figure out how much the log grows with each transaction. You should be able to see how it’s running and compare that to the growth ratio by doing this. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

During the executin of these statement run DBCC SQLPERF(LOGSPACE) for the %age factors of Transaction logs. (Derrick, move the post to General DBA section for relevancy) Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thank you all for the responses … I have been away, so I appologize for the delay. The recovery model in the db is full. Presently it is set to grow at 10%. I have not tested this update with a tool like QSQL.exe … I had not heard of that actually. I will look into it. Derrick, that sounds like a good idea … in fact we began doing something similar to find out how much the log is growing. I was not around for the end of that test, so I will have to check and see if that has been found out. Satya, I will try your suggestion as well. Thanks for your responses! Regards,
Brian

Previously I’d executed a script file which has 25000+ lines with UPDATE statement.. without any issues on QA. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>