SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Archives: April 2010

What is a Good Way to Quickly Disable SQL Agent Job Schedules?


I recently blogged about a Stored Procedure to Sequentially Run SQL Agent Jobs and have been meaning to blog about this script as a follow-up because this is very helpful for converting from SQL job schedules to sequential job schedules while still having a back out path. The question… Read more

0 comments, 396 reads

Posted in Adventures in SQL on 22 April 2010

Whats a Good Rule for Max Degree of Parallelism?


I am one of those people that believe that anything worth doing is worth having a script to do it. Setting the Max Degree of Parallelism is no exception. In this post I will go through the script I use as well as the metrics I watch to make… Read more

0 comments, 510 reads

Posted in Adventures in SQL on 20 April 2010

How Do I Find SSIS Packages that Exist in Multiple Folders?


If you are using SQL Storage for your SSIS packages and have multiple folders to make life easier then there is no doubt you have run into this before. A package gets moved to production in what appears to be the right folder but the requestor reports that the… Read more

0 comments, 267 reads

Posted in Adventures in SQL on 19 April 2010

How Do I Kill All Sessions from a Windows Group?


The stored procedure I am posting today will kill all sessions for users that are members of a given domain group. I use this stored procedure to keep ad-hoc (write down Access) users out of the way of nightly builds on my data warehouse. I have created two jobs… Read more

0 comments, 403 reads

Posted in Adventures in SQL on 14 April 2010

T-SQL Tuesday #005 – Self Service Performance Information


Today I am taking a little detour from the scripts I have been posting to take part in “T-SQL Tuesday #005 – Reporting“.

My T-SQL Tuesday #005 entry allows selected users of a server to see what is going on with it and react accordingly, freeing members… Read more

0 comments, 383 reads

Posted in Adventures in SQL on 13 April 2010

How Can I Tell if a Windows Login has Rights to My Server?


Here is another utility stored procedure that I use. This stored procedure will tell you how a user has access to your server. This is a great stored procedure to use for things like server dashboards where you only want people to see the dashboard for servers that they… Read more

0 comments, 289 reads

Posted in Adventures in SQL on 12 April 2010

Script to Create A TempDB File Per Processor


Trying to keep the streak alive so here is my 5th blog post in 5 days. This one is another script and by now you are probably wondering if I am either really incredibly lazy or just hate screwing up. The answer to both is an unqualifed YES.


Read more

0 comments, 1,520 reads

Posted in Adventures in SQL on 9 April 2010

What Do I Need to Do After Moving a Database to SQL 2005/2008?


I recently moved a database from SQL 2000 to SQL 2005. It was really just a matter of detaching the database, copying the files, attaching it on the new server, flip the compatibility mode, fix the user accounts, and run a few queries. Thats it? Could it have… Read more

0 comments, 349 reads

Posted in Adventures in SQL on 8 April 2010

How Do I Identify Invalid or Unneeded Logins?


I was looking at one of my servers and was caught off-guard by how many individual users had been granted rights. To make it worse I recognized a few user names as former coworkers. Clearly time for some house cleaning, now time to find a script.

The Process

I… Read more

0 comments, 455 reads

Posted in Adventures in SQL on 7 April 2010

Is Anybody Using That Stored Procedure?


From time to time I get asked to check if a stored procedure is still used in preparation for dropping it. Last week I tweeted my wish for a DMV that would give me the usage statistics for stored procedures to which Aaron Bertrand (Blog|Twitter)… Read more

0 comments, 435 reads

Posted in Adventures in SQL on 6 April 2010

Get Users By Connection


I was recently working a production issue that required me to log in via the Dedicated Administrator Connection (DAC). When I tried to log in I was greeted with the following message:

Could not connect because the maximum number of ’1′ dedicated administrator connections already exists. Before a new…

Read more

0 comments, 323 reads

Posted in Adventures in SQL on 5 April 2010