Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2009-04-14 : 11:17:18
|
| I have a job that will drop and recreate a set of views. However, if someone is already using the view, then the job hangs and waits for the user to finish. However, if they are running a particularly intensive query, this can take forever.What can I do to set the job proc as the one to take priority? Therefore, its tough titty if anyone is using the view. The view will be dropped and the user will have to wait until teh view is recreated before running their query again.Is the answer something like setting the DB in single user mode?Hearty head pats |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-04-14 : 13:11:37
|
| hmm, it's not the user will just have to wait, you would have to KILL their session to unlock the resource. That could be an ugly error on the frontend or at least confusing for the user.What is the idea behind altering the views? Is it a live/non-live db sort of thing where you are running rollups on the non-live version then altering views to point to the new live db?Any chance of improving the view statements (or underlying tables) so that a given request doesn't take "forever" ?Be One with the OptimizerTG |
 |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-04-15 : 09:04:29
|
| Hi thereThanks for your reply.Basically, the views are to group a set of tables into a single logical table. Rather than use SQL Server Partitioning, we are employing a technique where we use procedures to manually create seperate tables on a daily basis. Once the tables have been populated, we 'switch' the table into the view by dropping and recreating it, to include the new table.In theory, this all occurs throughout the night, so there should not be anyone using the system. However, there are occasions where we need to perform the nightly process in the day (to load back dated data, for instance). Its not an ideal solution, but it works ok at the moment. however, the main issue is the forementioned one, where if someone is using the system, then we cannot perform the required admin duties.If I kill the session, what exactly would happen to the users connection? How would I go about doing this?ThanksHearty head pats |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-04-15 : 13:25:12
|
| well one way to (manually) kill their session would be to use "sp_who2 active" to see which (if any) sessions are blocking your session. if so you can issue this command to kill their session: "KILL <spid>". [spid] and [blkby] are columns returned by sp_who2.to see the command that a given spid is currently running you can use: dbcc inputbuffer(<spid>)>>If I kill the session, what exactly would happen to the users connection?I guess that depends on your system implementation. I suspect that it won't be handled gracefully.Be One with the OptimizerTG |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-04-15 : 13:50:51
|
Why are not the views recreated when the new table is added?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
|
|
How to set the priority of a process/proc/job