Oct 15

SQL Job Runtime

Recently I received a request to gather run-time information about a particular SQL scheduled job. They wanted to know how long a specific job was running. I started digging through the msdb.sysjobhistory table and found some strange values for the columns. After searching the Internet I wrote this query that fit my needs:


Jun 14

Cannot generate SSPI context

I received this error message: “Cannot generate SSPI context” while attempting to connect to a SQL server:

The server had DHCP enabled so the address had the potential to be different every time the machine restarted.

Solution: This was an easy one…using the Fully Qualified Domain Name for the connection string allowed me to connect to the server. Another solution would be flushing DNS on client attempting to connect.

Jun 13

Dynamic Log Shrink (DBCC Shrinkfile)

Recently I worked with a customer that was running their databases in simple mode. During the night they would run various bulk load jobs. This would cause the transaction logs to grow quite large (even though the database is set to simple mode each transaction must be logged). This in turn caused disk space issues.

A simple solution I created was to shrink the transaction logs on a daily basis using this dynamic script:


Jun 11

Foreign Key Relations

One of my colleagues wanted a textual way of viewing foreign key relations. It prompted me to write this script:


Jun 10

List Stored Procedures for All Databases

I recently needed a way to check all databases on an instance for a Stored Procedure that was created in the last 3 days. I created this script using the sp_MSforeachdb “undocumented” stored procedure to query each database:


Jun 06

Manually Failover Database Mirroring

Use this command to manually failover a mirrored database. The command must be run using the master database (hence the use master preface):


Jun 05

Backup with Email Alert

Here is a clean way to perform a one-time differential backup. I also needed the server to email me when the backup was complete.

I used these two MSDN articles to create this script:

Apr 26

GRANT Schema Permissions

Background: I had a user that needed to be able to read, but not execute, stored procedures in the DBO schema. I used these methods below to achieve this task.

There are two methods to granting schema permissions in SQL server.

  • One is through T-SQL commands:

Apr 12

Checking for sysadmin on an Instance

Here is a simple script that checks for sysadmins on an instance: