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 13

Difference Between ALTER DATABASE WITH ROLLBACK IMMEDIATE and WITH NO_WAIT

For quite some time I didn’t know the difference between ROLLBACK IMMEDIATE and NO_WAIT when altering a database. These are similar in nature, but perform very different tasks. Lets look at the differences:

  • ROLLBACK IMMEDIATE
      Specifies whether to roll back after the specified number of seconds or immediately
  • NO_WAIT
      Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, the request will fail.

If at first glance you still don’t understand the difference don’t fret…I didn’t either. Here is the major difference:

ROLLBACK IMMEDIATE will take all uncommitted transactions and running queries and roll them back immediately. NO_WAIT is a more polite method; it will wait for transactions to commit or roll back and running queries to complete before the ALTER DATABASE statement executes.

You can read more about the differences on Microsoft’s MSDN site:
http://msdn.microsoft.com/en-us/library/bb522682.aspx.
Definitions above are taken from there.

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 11

Elevate User Permissions

Scenario: I’ve recently came across a user that had SQL on a server. The user was on the instance, but they had limited permissions. In essence this created a user that could login the SQL engine but had no access to the databases or the ability to add permissions to their account even though the account was a local administrator.

Here are the steps I took to elevate the user’s privileges:

  1. Start the instance in Single User Mode:
  2.  

  3. Grant user new permissions:
  4. Restart SQL in “normal” mode and login through Management Studio

It’s that simple :)

**I feel it is important to note that the user performing the SQLCMD query must be a local administrator.

Feb 07

Grant xp_cmdshell to user

Today’s topic is regarding the xp_cmdshell feature. There are certain circumstance that require xp_cmdshell to be used, even though there are inherent security risks. Here are some MSDN articles that provide some insight into xp_cmdshell and some example uses:

Now to the good stuff:

  1. Ensure that xp_cmdshell is enabled on your server
  2.  Grant the xp_cmdshell permission to the user:
  3.  Configure a xp_cmdshell proxy account. This account should have the CONTROL SERVER securable assigned:

 

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