We use SQL Server 2005. We have created a SQL Server Login called RUNSQLQUERYUSER. We have the following questions:
1. How to limit RUNSQLQUERYUSER from executing SQL queries using SQL Server Management Studio between 9:00 AM and 5:00 PM? How to implement that? Therefore if RUNSQLQUERYUSER runs a query at 8:00 AM we let them
but after 9:00 AM we don't let this user execute queries.
2. If RUNSQLQUERYUSER runs a SQL query before 9:00 AM but the query takes more than 30 minutes we need to stop it.
3. If RUNSQLQUERYUSER runs a SQL query before 9:00 AM but the query is still running at 9:00 AM we need to stop it.
To emphasize what Gail and Lowell have already written...
Gail has the near perfect solution for #1. It's simple and it's effective. Killing SPIDs may cause a problem, though. You'll see more on that in a second.
#2 and #3 are a different story. As Lowell stated, rollbacks from killing a query (SPID) can take a whole lot longer than you might expect. In fact, if the query relates to a linked server or has something like an OPENQUERY in it, SQL Server has a fault that may cause a rollback that runs forever until the next bounce of the service. These are easily identified as a rollback that has done 0% work but shows no work being accomplished in the log. The big problem with these rollbacks is that they will typically consume the resources of an entire CPU and they can't be fixed without bouncing the service.
Further, killing long running queries doesn't really address the root problem of that issue. The root problem is that it IS
a long running query and it needs to be fixed. If the users need to run the same query every night, then helping them by writing an optimized stored procedure and scheduling it for them would be the way to go.
If the users typically write nonrepeatable ad hoc queries a lot and they take a long time to run, then consider creating a "reporting" server that's kept up to date either by SQL Server replication or, better yet, SAN replication.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs