Tag Archives: SSC

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. Here is the script I came up with to do the analysis. Continue reading

Posted in User Management, Utilities | Tagged , , , , | Leave a comment

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. This post outlines some different ways to get after that information and how sometimes it can go horribly wrong. Continue reading

Posted in Tracing and Profiler | Tagged , , , | 5 Comments

Get Users By Connection

Here is some TSQL to get who is connected to a SQL 2005 server by connection type, including a filter to focus on the Dedicated Administrator Connection (DAC). Continue reading

Posted in User Management | Tagged , , , | Leave a comment

Managing the Size of the Security Cache (TokenAndPermUserStore) in SQL 2005

In an earlier post I walked through the diagnosis of a problem with TokenAndPermUserStore bloat in SQL 2005. In this post I will go through what I did to work through the issues and the results of those efforts. Continue reading

Posted in Security Cache | Tagged , , , | 1 Comment

My Adventures with the SQL 2005 Security Cache

This post describes the process of diagnosing a lesser known performance issue in SQL 2005 and higher on servers with a lot of physical memory. The performance issue is caused by security cache bloat and is very difficult to miss unless you know what you are looking for. Continue reading

Posted in Security Cache | Tagged , , , | Leave a comment

Why Do My SQL 2008 Compressed Backups Shrink?

Recently a coworker was doing some research with SQL 2008 Backup Compression on a very large database. During the testing they noticed that the backup file shrunk rather than grew. It turns out this behavior is intentional and beneficial. Continue reading

Posted in Backup | Tagged , | 3 Comments

Why Do I Need All These Servers?

Working in a large corporate environment I am frequently getting requests for a new server to do this or that. Running through the usual questions to determine scope and scale, the price can get high pretty quickly. Inevitably, talk turns to ideas on how to cut the cost while still delivering a robust platform. Continue reading

Posted in General | Tagged , , , , , , , , , , | 2 Comments

Stored Procedure to Sequentially Run SQL Agent Jobs

Here is another one of my utility scripts. Lately it seems like all I am blogging about lately is utility scripts, but I have a ton of them and need to get them documented. This stored procedure is used to run a series of SQL Agent jobs sequentially. It is one of my favorite stored procedures because it clears up a lot of headaches for me without having to spend a bunch of money or turn my maintenance schedules over to another team. Continue reading

Posted in Utilities | Tagged | 9 Comments

Stored Procedure to Get SQL Job Execution Status

Ever need a programmatic way to figure out if a SQL Agent job is running? I did. After spending a while searching on Google I did not turn up anything I liked so I turned to Profiler and started looking at what calls SQL Management Studio uses. After a bit of poking around I was able to come up with a stored procedure that I could call. Continue reading

Posted in Utilities | Tagged , , , , , , | 6 Comments

Using sp_ExecuteSql to Run Dynamic SQL

Dynamic SQL should be considered a highly specialized tool of last resort and used properly. Misuse can lead to your server and the data stored on it no longer belonging to you. Continue reading

Posted in Dynamic SQL, Security | Tagged | Leave a comment