Apr 04

Sort or Search in sp_who2

Here is a simple script that allows you to sort and search for items in the sp_who2 stored procedure. Works with SQL 2005 and up:

 

Mar 14

Cannot alter user dbo

I was recently faced with the task to alter a SQL user’s database account; more specifically speaking  a Login name’s database user name. I ran into this error:

Cannot alter the user dbo. It seems as if SQL server is upset if you attempt to alter the account of the database owner (dbo). To get around this you will need to change the dbo to another account to modify the original user. Use the following code to change the database owner:

 

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: