January 17, 2005 at 11:46 am
In my database maintenance plans, I want to do optimizations and integrity checks once a week when the plant is closed and offline.
When I try to schedule them to run, they fail because of a persitant connection to a database (or table).
What is the best way to ensure that these plans run each time they are scheduled? Do I need to put the server in single user mode? If so, how do I do this via the scheduler and then put it back in user made when it is done?
Any help is appreciated.
January 17, 2005 at 12:40 pm
If "...the plant is closed and offline", I'd first want to know what on the system is up, running, and using the databases in a way that defeats fairly routine maintenance!? Identify this, then see if you can get it turned off during your maintenance window--and if not, why not. Arbitrarily and summarily killing connections is rarely a good idea, as it may be running critical monthly reports, important operational maintenance routines, offsite queries, or who knows what. (Killing connections when you know and understand the ramifications is, of course, completely acceptable.)
You do have a formally scheduled and supported maintenance window, right? If not, when could you expect to get your maintenance done?
Philip
January 17, 2005 at 1:01 pm
Journeyman:
Thanks for the prompt response. As for your questions:
I'd first want to know what on the system is up, running, and using the databases in a way that defeats fairly routine maintenance!?
I have a utility (production monitor) that is supposed to be shut down during plant down time, this may not be done. Some users may not log out of their Citrix connections. Other than that, no one should be on the system during the maintenance periods I have 'windowed'. If these processes are running, killing their sessions is not a problem.
I can also set a time where remote logons are prohibited (during maintenance periods). If a user is logged on remotly, I can automatically log them off. Procedures can be published to let users know that maintenance is under way during specific periods.
I can control the Citrix connections through idle timeout controls, and I can schedule the production monitor to shut down automatically. This is not a problem.
To ensure that no-one is on the system so that these processes complete, is there a way, or is it advisable, to put the server into single user mode, and then return it to production mode after maintenance is complete? Are there other options I may be missing?
January 17, 2005 at 8:02 pm
My "what's running" points were all CYA issues. If you've got your superior's backing to your decisions, cool.
Personally, I've always been leery of putting SQL Server into single-user mode. What happens if you configure everything, and someone else sneks in during that last split-second?
One trick I've done (for Production deployments) to block all database access is to mess with the SQL logins. All NT logins (group and user) can be eastily set (scripted, of course) to "deny access", and just as easily reset when you're done. SQL authentication's trickier; for them, I set the password to random gibberish, then when done reset it back to the proper password. This, of course, requires you're knowing the proper password.
This blocks any and all new non-SA connections. (You of course don't have ANY applications or random users with SysAdmin rights.) I'd wait a few minutes, giving our website connections a chance to finish and time out, and then I'd go in and kill the lingerers. This too could be scripted, though all my days I'll be nervous about arbitrarily killing someone's connection. (Much more fun phoning someone up and berating them for keeping a connection open during long-planned downtime...)
There may be other and better tactics. It's a bit script-intensive and hands on, but it might work for you.
Philip
January 18, 2005 at 6:46 am
This is what I expected, although I was hoping to glean a trick or 2 from the list.
Thanks anyway, I am already implimenting the suggestions above.
January 18, 2005 at 7:30 am
I'm guessing that the maintenance plan job is failing when it attempts to put the database into single-user mode and is unable to because there are database connections.
If you have the "attempt to repair any minor problems" option selected for the maintenance plan's integrity checks, it will attempt to put the database into single-user mode. If you do not have this option selected, it will not.
January 18, 2005 at 2:26 pm
Aghh... that's probably what I was missing.
Good catch!! thanks!
January 18, 2005 at 3:04 pm
If the connections that are causing problems are simply inactive connections of users who failed to log out, simply killing the connection shouldn't be an issue. And since the plant is closed and offline, there should be no attempts at new connections. With this in mind, Phillip's suggestion of "messing with the logins", which is a good suggestion, in your case wouldn't be necessary.
Steve
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply