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

Script Database User Role with Permissions

If you have ever tried to copy database security roles that included permissions or securables you might have noticed that SQL’s built-in script generator doesn’t produce a script that includes role permissions.
Here is a script that I found and modified it to work with legacy SQL versions:

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: