SQL Server Performance Forum – Threads Archive

Tuning Batch procedures

I have ‘n’ number of procedures which are scheduled in jobs(SQL Agents) at nights.
Iam having a problem that when it inserts records(may be lakhs of records),if the job gets terminated abnormally then i am not in a position to determine which procedure got terminated and how many records inserted as well as how many records are pending to insert.
Note: i haven’t run the profiler.
Please help me out.

Configure sql job output file and print statement after each procedure completed…
You can run the insert in a transaction so that if it fail it rollback…means all or nothing…
If your destination tables has the date column then you can check the rows inserted based on the date…or if you take a rowcount before inserting write to a table or file then incase of failure you can check the current count to get the difference… MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

The best way to tackle such rollback and commit as suggested use the insert operation within a transaction mode. Reviewhttp://msdn2.microsoft.com/en-us/library/aa213064(sql.80).aspx and books online too.http://technet.microsoft.com/en-us/library/ms345124.aspx fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

]]>