Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

4 Answers 4

up vote 4 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

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".

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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