11

I have to run quite a few long-running queries (rebuilding indexes, updating large sets of data) for my database. Is there an alternative to running the query in SQL Server Management Studio and checking on it every hour or so? I would like to be emailed or sent a message when it's done, but don't know the best tool for this.

4 Answers 4

5

Following Gaius post: You can create an .SQL script which does what you need with use db in front of the script -> create a SQL Agent job of Operating system type which calls the script:

sqlcmd -E -S SERVERNAME -i"c:\YOURSCRIPT.sql" -o"C:\YOURSCRIPT_LOG.log"

Add new step and use msdb.dbo.sp_send_dbmail procedure to send email. This feature can be customized to display inside the mail a specific query from SQL tables to confirm the execution of the script... for example dbcc showcontig of your rebuild indexes.

2

Yes, just use xp_sendmail. You can send a pre-set message, or the results of a SQL statement just as easily. This feature has been available since SQL Server 6.5, however it is due to be obsoleted in Denali - if this is going to be a permanent part of your operation, then you should use Database Mail which is much more "enterprise".

1

Have you got the option to use SQL jobs? You can do notifications and the like through there. As far as getting intermittent notifications, that would require some code within the stored procedures etc.

1

I always write messages to an "EventLog" table. When processing large amounts of data, I manage the data in chunks, and write status updates to the EventLog after every chunk.

When I want to check on the progress of the long running process, I simply query the EventLog table.

Example of output:

-------------------
-- My Big Update --
-------------------
Started: 2011-05-03 10:00:00

Records to Process: 1,000,000
Chunks: 200

---Chunk 1---
Attempting to update MyTable
Records Updated:   5000
Records Remaining: 995,000
Throughput: 4210 records per second

---Chunk 2--
Attempting to update MyTable
Records Updated:   5000
Records Remaining: 990,000
Throughput: 3555 records per second

---Chunk 3--
etc.

I also have columns in the EventLog table to track when the message was written, what process wrote the message, etc. Sorry for not including that info in my example.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.