April 2, 2014 at 5:11 pm
Hi all,
We will soon be migrating to SS2K8, and before we cutover we need take our DB's offline/into Single User mode. In order to ensure consistency we need to ensure that process waits until any running have finished.
I have prepped a small script for the task, but would appreciate any advice on how to go further...bear in mind please the box is SQL 2K so I can't use new functionality!!!!!
Declare IntFlag (int)
Set IntFlag = 0
While IntFlag <> 0
Begin
Select IntFlag =
(
Select Count (*)
From sysjobhistory
Where run_status > 0
)
WAITFOR DELAY '00:00:10'
Else
Set Database 'YourDatabaseNameHere' Offline etc...
End
Thanks,
Jake
April 3, 2014 at 11:17 am
Could be mistaken, but I'm fairly sure that when you execute an ALTER DATABASE SET OFFLINE, it will automatically wait until all processes are finished running before it takes the database offline, unless you explicitly say WITH ROLLBACK IMMEDIATE.
Your script is also looking just at jobs - if that is your concern, then why not schedule it to go offline when all jobs are finished? Should be able to tell easily enough from your scheduler.
April 3, 2014 at 12:13 pm
If you stick with the loop you might consider that the code inside will never execute...
Declare IntFlag (int)
Set IntFlag = 0
While IntFlag <> 0
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 3, 2014 at 12:51 pm
Don't you have a Migration window during which you do the cut-over ? Usually you should pick up a window when none of the jobs \ user processes are running. Or communicate the users when are going to the upgrade and that there's going to be downtime ?
--
SQLBuddy
April 3, 2014 at 2:16 pm
kramaswamy (4/3/2014)
Could be mistaken, but I'm fairly sure that when you execute an ALTER DATABASE SET OFFLINE, it will automatically wait until all processes are finished running before it takes the database offline, unless you explicitly say WITH ROLLBACK IMMEDIATE.Your script is also looking just at jobs - if that is your concern, then why not schedule it to go offline when all jobs are finished? Should be able to tell easily enough from your scheduler.
Well the jobs finish at varying times. This way avoids guesswork and minimises unnecessary waiting, the backups simply start 10 seconds after the last job finishes or at 6.30am, whichever is the sooner.
You write "script is also looking just at jobs"...is there anything else it should consider?
April 3, 2014 at 2:24 pm
Well, as I said, unless you use an ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE, the SET OFFLINE will wait until all open processes are finished executing before setting the database offline.
I don't know how your database is set up, but if you have applications or users connecting to it, who are running queries, and who keep those queries active, your SET OFFLINE will possibly be stuck running for quite a long time before it finds that window where everything is finished, in order to take it offline.
That being said, it seems like your plan is to take the database offline during a period of downtime, so I this may not be a major problem.
April 3, 2014 at 2:29 pm
Sean Lange (4/3/2014)
If you stick with the loop you might consider that the code inside will never execute...
Declare IntFlag (int)
Set IntFlag = 0
While IntFlag <> 0
Thank you, well spotted!! While Intflag = 0 it is.... 🙂
Also now I think about it, the script is going to be bundled into a scheduled job, which of course will be running, so the job running count will have to be no higher than 1, not merely higher than zero, for the backup process to run.
April 3, 2014 at 2:48 pm
kramaswamy (4/3/2014)
Well, as I said, unless you use an ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE, the SET OFFLINE will wait until all open processes are finished executing before setting the database offline.I don't know how your database is set up, but if you have applications or users connecting to it, who are running queries, and who keep those queries active, your SET OFFLINE will possibly be stuck running for quite a long time before it finds that window where everything is finished, in order to take it offline.
That being said, it seems like your plan is to take the database offline during a period of downtime, so I this may not be a major problem.
o
Indeed it is, we are coming in on a Saturday for the cutover, and while we're all being paid a day's fees to do so, naturally none of us want to stick around the entire time, which also has the benefit of allowing us more time in case of any error/mishap.
As for users, it is principally a 9-5.30, weekday business, and all application users will be issued warnings well before that weekend, written in no uncertain terms, that any work they have performed before the cutover MUST be saved by COB Friday, or lost forever. I said 'principally, we have thousands of users, so there will doubtless be a handful of stragglers working late and/or into the weekend. They are free to ignore that warning in so doing, however, I also have scripts that force absolutely all users (bar myself) out, so one way or the other, at 6.30 or soon thereafter the DB's will be offline.
I will not hesitate to add a 'rollback' statement, and if anyone has a problem with "lost" work on Monday, they can smooch my glutes.
🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply