Dec 01

Backup Restore Progress

I cannot take credit for this script, but I use it all the time:

 

Source

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:

 

Feb 10

Cursor Template

Most of the time when I use a cursor I find myself using the same template. I am always writing the same code so I figured I’d save myself 3 minutes each time I use a cursor and store the code here:

 

Jan 23

Check for Sysadmin

I needed a way to check for the sysadmin role on an instance. I came up with this small query that does that:

 

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 25

Reseed Identity for Table

I’ve designed an integration job that deletes all data from a table and imports new records each night. The table has an identity column in it, so the value (or seed) will always be growing.

Here is the easiest way to reseed the identity column for a table:

This will force the next inserted identity value to be 1 (0+1=1 Smile).

 

Feb 21

Remove Mirroring and Setting Secondary Database Online

Scenario: The primary server participating in a database mirroring configuration has gone offline. It has been decided that there not enough time to troubleshoot bringing the primary server online and the mirror server will now assume the responsibility of the primary server.

Use this command to manually remove database mirroring and make the secondary database available for use.

Note: this will break mirroring, essentially creating 2 independent databases.

Feb 20

Sizes of ALL Database on an Instance

Revised 8/15/14:

Created a script that combined both syntax for SQL 2000 and 2005+ as well as showing used space and free space:

 

Older post published 2/20/13:

————————————————————————————————————————–

I needed a simple way to show the sizes of each database on a given instance, as well as their total size. This was the solution I came up with:

SQL 2000 Syntax: Continue reading