Feb 19

SP_Send_Mail Common Settings

There are some common setting that I find myself using when sending mail via the sp_send_dbmail command. I like these two features the best:

  1. @blind_copy_recipients
  2. @from_address, which you can use to change the sender’s display name
    • This feature (variable) is only supported in 2008 and above.

I’ve included an example of what I usually include in my send_mail calls:

Feb 14

Check SQL Version

My preferred way of getting a SQL version with an easy to read output:

 

Feb 13

Database in Suspect or Recovery Pending State

Today one of the SQL 2008 R2 clusters that I manage lost a shared disk. The disk happened to be the DATA directory for SQL server. When the disk came back online it performed a chkdsk, and determined that there was corruption on the disk. After logging into the SQL instance I found a database in a Suspect state and another in a Recovery Pending State.

Losing a disk is a scarring thing, especially when the databases on that disk don’t come back online in a healthy state. The resolution is fairly strait forward for both situations:

  1. Set the database into EMERGENCY mode and turn on SINGLE USER mode:
  2. Check the database for potential errors:
  3. At this point, based on the errors you received, you need to decided what course of action need to be taken. Errors regarding loss of data might constitute restoring the database from backup; on the other had errors regarding bad indexes and partitions you might consider allowing DBCC CHECKDB to correct the issues. Ultimately the end of the DBCC CHECKDB results will tell the you minimum repair level to correct the issues that it discovered. Use the command below that corresponds to the results of your CHECKDB:
  4. After CHECKDB is complete the database should be in a healthy state. Now you can bring it back to multi-user mode:

  5.  
Feb 12

Set Database Offline

Here is the syntax to set a database to offline:

Additionally, here is the syntax to bring a database online:

When altering the state of any database you will want to run the query on a database that is different then the one your are altering.

Feb 06

Row Count for all Tables in a database

Here is an easy way to get the row count for all tables in a database:

Note: Because of the use of the sys.tables and sys.partitions catalog views, the above code will work for SQL 2005 and newer.

Feb 04

For Each Database (EXEC sp_msForEachDB)

Here are some useful examples of the use of the undocumented sp_msForEachDB command:

Basic:

Quotes are complicated :

Note the number of single quotes needed to print is 4 (normally 2).

Feb 01

Database Stuck Restoring…

There are some cases when using backup/recovery tools where the tool will not mount the database after the restore. This will leave the database in a Restoring mode. Use this command to set the database back to normal:

This can also occur when you are restoring transactions logs on top of a full backup. Once all the logs have been restored this command should be run.

Jan 29

Find and Replace in a Column

Here is a quick and simple way of finding a value in a particular column and removing it:

This looks for a quote () in the column named ID and removes it.

Jan 23

Bulk Insert

Bulk Insert is a fairly common function, but not so common that I can remember the syntax every time. Here I’ve posted the version I use most:

Note that Bulk Insert does not support skipping headers. The source file should not include headers otherwise the load will fail.

More about Bulk Insert:

Transact-SQL : BULK INSERT

Jan 22

Grant execute permissions to ALL stored procedures in a database

I’ve found many scenarios where there are certain users or groups that require execute permissions for all stored procedures in a database. Rather then granting access to all the objects individually, not to mention new objects that are created after you’ve granted access, I prefer to create an executor role and assign their user account to the role. The following works for SQL 2005 and above:

Then grant the db_executor role to their user account. Done!