Feb 27

Definition of Transaction (ACID)

Just a quick bit of knowledge:

In the context of a transactional database, regardless of type (MSSQL, Oracle, MySQL, etc.), the following rules must apply to any transaction:

  1. Atomic: the entire transaction must be committed. If a single part of the transaction fails the entire transaction must fail.
  2. Consistent: each transaction that is committed to the database must not violate any keys, relations or constraints. In other words, a transaction in not allowed to leave a database in an inconsistent state.
  3. Isolation: a transaction must be isolated from any other transaction running at that time. Transactions running concurrently must be executed as if they were sequential. If two transactions can/need to run concurrently they will need to operate isolated from the data that the other is manipulating.
  4. Durable: once a transaction is committed it must become a permanent part of the database.  It must be stored on a medium that can be powered off and the data will remain (i.e. Hard Disk, SSD, SAN, etc.). Durable also means that a confirmation of the committed transaction is provided.
Feb 22

Backup Detected Log Corruption

I’ve been dealing with several data corruption issues for the last few days. It all started with a loss of a clustered disk and when it was brought back online there were a few Suspect and Recovery Pending databases. Here is how I was able to solve that issue: Database in Suspect or Recovery Pending State

For a few other database CHECKDB returns no error for and FULL backups complete successfully; but when performing transaction log backups we receive this error:

Along with these error in the SQL Server Logs:

No need to stress…Take a deep breath. There is a simple solution for this issue, and it turns out there is no data loss occurring on the database. Here are the steps:

Continue reading

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

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 18

Cannot Connect to WMI Provider

Today a client came to me with this error:

When running SQL Server Configuration Manager he received this error: “Cannot Connect to WMI Provider. You do not have permission or the server is unreachable.

I’ve handled this type of case before and a simple restart for the SQL server instance solved the issue, but that wasn’t the case today.

It’s important to note that this machine was 64-bit and the solution was to use the x86 (32-bit) shared components of configuration manager.

Solution:

  1. Open a CMD prompt, and Run as Administrator.
  2. Navigate to the shared components directory of the instance.

    • May vary based on you configuration and version.
    • UPDATE: If the mofcomp.exe file does not exist in the shared directory see instructions below.

  3. Run the following command (This will vary depending on your version on SQL server):

    • May vary based on you configuration and version.
You should see a message similar to the following:

UPDATE:

If the mofcomp.exe file does not exist in the SQL shared directory server perform a search for the file in the following directory: C:\Windows\winsxs

The result will be something similar to this: C:\Windows\winsxs\wow64_microsoft-windows-wmi-core.resources_31bf3856ad364e35_6.1.7600.16385_en-us_3b112a3415ba87ce

Replace the location for Step # 2 with the results of the search. Example:

Feb 15

SQL 2012 F5 Keyboard Shortcut Refresh

For some unknown reason, Microsoft decided to change the global refresh key (F5) in SQL 2012 to debug. This has caused me countless headaches when trying to refresh a database in the Object Explorer. Here is how I went about changing F5 from debug to refresh:


  1. In SQL Server Management Studio, click Tools, then click Options
  2. Under Environment, expand Keyboard then select Keyboard nested underneath:

  3. Search from View.Refresh and highlight.
  4. Under Press shortcut keys: press F5:

  5. Click Assign, the screen should look like this:

  6. Click OK and you’re done!

Stupid debug

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 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.