May 5, 2011 at 8:01 am
Is it possible to execute a procedure across different databases at the same time? This isn't adhoc( i can do it in parallel if adhoc:-) ) , should be scheduled as a sql agent job. I know i could do it in serial using a cursor but i am not sure if i can schedule a job to run a single proc across different databases at the same time on same server. I am using sql 2005. Any ideas?
May 5, 2011 at 11:30 am
Steps in a job is executed in a serialized manner. So no, a single job cannot execute multiple steps in parallel.
Could you perhaps create an SSIS package? SSIS can execute T-SQL scripts in parallel.
May 5, 2011 at 11:35 am
No but you could create 1 job per db, then use sp_start_job. Which will then make it parrallel.
May 5, 2011 at 11:54 am
I go with PowerShell and use remoting and then you can thread it and fire it on every server, all at the same time, no issues. Here's a small blog post[/url] I wrote on it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 6, 2011 at 10:26 am
Nils Gustav Stråbø (5/5/2011)
Steps in a job is executed in a serialized manner. So no, a single job cannot execute multiple steps in parallel.Could you perhaps create an SSIS package? SSIS can execute T-SQL scripts in parallel.
i like the idea of SSIS. Can you provide some more details on how to get this done?
May 6, 2011 at 4:38 pm
Have you worked with SSIS before? You can execute as many tasks in parallel as you like in SSIS, which is one of the very nice things about it. You'll have to create multiple tasks, I think, one per database. Maybe someone with more experience knows how to enumerate a list of databases on your server and then spawn multiple, parallel tasks (one per DB). I don't think it's possible.
Once you have the SSIS package, you just create an agent job as usual and point the step to the SSIS package instead of a T-SQL task.
What is it you are trying to run? Is it just an SP?
Rich
May 9, 2011 at 8:45 am
rmechaber (5/6/2011)
Have you worked with SSIS before? You can execute as many tasks in parallel as you like in SSIS, which is one of the very nice things about it. You'll have to create multiple tasks, I think, one per database. Maybe someone with more experience knows how to enumerate a list of databases on your server and then spawn multiple, parallel tasks (one per DB). I don't think it's possible.Once you have the SSIS package, you just create an agent job as usual and point the step to the SSIS package instead of a T-SQL task.
What is it you are trying to run? Is it just an SP?
Rich
Yes i have worked with SSIS before. Forgot to mention one thing, the number of databases change every week, so i do not want to go and change my SSIS every week? Is there a way to get dynamically list of db's and fire just ONE PROC across all db's in pararllel?
May 9, 2011 at 8:57 am
sqldba_icon (5/9/2011)
rmechaber (5/6/2011)
Have you worked with SSIS before? You can execute as many tasks in parallel as you like in SSIS, which is one of the very nice things about it. You'll have to create multiple tasks, I think, one per database. Maybe someone with more experience knows how to enumerate a list of databases on your server and then spawn multiple, parallel tasks (one per DB). I don't think it's possible.Once you have the SSIS package, you just create an agent job as usual and point the step to the SSIS package instead of a T-SQL task.
What is it you are trying to run? Is it just an SP?
Rich
Yes i have worked with SSIS before. Forgot to mention one thing, the number of databases change every week, so i do not want to go and change my SSIS every week? Is there a way to get dynamically list of db's and fire just ONE PROC across all db's in pararllel?
Gotcha. I don't know of any way to spawn multiple parallel tasks based on a dynamic set of database names, but I'm not an advanced SSIS developer. Maybe someone else knows of a way. I'm guessing that, if it's possible, it would have to be done programmatically somehow, not through a standardized drag-and-drop SSIS task.
Post back if you get an answer to this, I think others would be interested, too.
Rich
May 9, 2011 at 9:01 am
Grant as suggested powershell and I assume you can do that there.
I've suggested create job on the fly and start job (always async). Won't matter how many dbs because you'll either use a loop or sp_MSForEachDB.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply