-
Recent Posts
Recent Comments
- Arnold Lieberman on A Busy/Accidental DBA’s Guide to Managing VLFs
- Update Statistics fails with Error Number:–1073548784 “Could not allocate space for object ‘dbo.SORT” – ReplTalk on Stored Procedure to Sequentially Run SQL Agent Jobs
- steve kirchner on Whats a Good Rule for Max Degree of Parallelism?
- Bipin on Stored Procedure to Sequentially Run SQL Agent Jobs
- Bipin on Stored Procedure to Sequentially Run SQL Agent Jobs
Archives
Categories
Meta
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 logins, sp_validatelogins, SSC, validation, xp_logininfo
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 Profiler, SSC, sys.dm_exec_procedure_stats, Tracing
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 DAC, Dedicated Admin Connection, Dedicated Administrator Connection, SSC
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
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
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
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 Development, Disaster Recovery, DR, HA, High Availability, Production, QA, Release Management, SSC, Staging, Testing
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
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 Agent, Job, SQL 2005, SQL Agent, SQL Agent Job Status, SSC, tsql
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