SQL Server

Msg 5030, Level 16, State 12, Line 1

I like to be preventive or proactive in SQL server maintenance (or PM for short). As part of the PM I have in place I run database integrity checks weekly. If there are issues when running the check(s) I get an email with enough details such as database name and server to make things a little easier when investigating further. I ran into an error the other day I wanted to share what I found and how I fixed it.

Problem

This is an example of the email I received this morning:

Daily DBCC CHECKDB Error: TestDB on server Server_Name 

My initial reaction was “Oh, no!” (I actually used a few other more colorful adjectives I won’t repeat)

The first thing I did was to copy the backup from last night to a test server and ran CHECKDB to see what I could see and make sure we did not have any corruption. After running the check and finding no issues I started to investigate further. I know running CHECKDB can be resource intensive (one reason, if you can, to off load that maintenance task to a “non-production” server). I wanted to make sure there weren’t any jobs running at the same time. Finding none, I wanted to see what the actual error was. Not finding anything related to the error in the logs I opened a query window and ran the following command and received the following results.

Msg5030-Level16-State12-Line1

Now I think it is important to note here the server in question is still being configured/tested before deploying to production so I was able to run CHECKDB on the server directly (and since I confirmed no issues were present already).

Solution

It appears there is a bug for this particular issue. Bug #: 489609 (SQLBUDT)

It seems if you get this error you have potentially one of two problems.

  • At least one other connection is using the database against which you run the DBCC CHECK command.
  • The database contains at least one file group that is marked as read-only.

I was fairly sure the database did not have a “read-only” file group but I checked just to make sure because this project has too many cooks in the kitchen (too many people with sysadmin access). I confirmed no read-only file groups were present.

sp_helpfile_info

I know there are other connections (several in fact) to this database. So we found out what caused the error. Now, how do we fix it?

We can either off load this task to another server. If we do that, we will need to build out a process of either copying a backup to another server or we can back up the database to a different server, restore and run CHECKDB.

Another option is to create a snapshot. Run CHECKDB against the snapshot we create and then drop the snapshot when finished.

Before_DBSnapShots

The code below is an example of how to go about doing this.

createsnapshot

After_DBSnapShots

runCheck

CHECKDB_results

dropSnapshot

Conclusion

No matter how much preventive maintenance you do on your servers/databases corruption will happen at some point. Running integrity checks is one way to help combat corruption and be aware of it as close to the time it occurred as possible. The sooner you know the more options you will have to remedy the issue. To read more about this error and how to work around it see this article. http://support.microsoft.com/kb/928518

Cheers!

Advertisements

sp_send_dbmail Options

Recently one of my projects had a requirement to send email from SQL Server to certain recipients. A developer asked if they could use sp_send_dbmail  and I said yes, I use it all the time and didn’t think too much about it until I got an email from her mentioning an issue they were having. Researching the problem more I found a solution I was not aware of and wanted to share it.

Problem

The problem they were having was with the way the “From” address was resolving. They wanted it to read “Do Not Reply” but what was happening was the email header resolved to the Server name it was sent from.

Researching the sp_send_dbmail  did not provide much insight so I started experimenting a little. One option I provided to them was the use of Powershell. I use that on one of my servers and the “From” email resolves to what I name it in the script. I provided the simple 7 lines of code as an example and suggested I could help them work it since she was not familiar or comfortable with using Powershell.

This is the Powershell code I use for sending email.

powershell script

I suggested she send me the stored procedure she was planning on using so I could see what she was doing and potentially convert to Powershell. If not the entire stored procedure I envisioned at the very least the sending of the email function.

Solution

While I was waiting on her reply I continued to experiment with the sp_send_dbmail  and discovered this little trick.

DO NOT REPLY

Conclusion

Almost every problem, issue or requirement will have multiple ways to solve it. What the best solution for you may not work for someone else. It may not work each time you need to use it where you work. Discovering different uses or ways of doing the same thing is one of the reasons I love what I do. Sharing what I have learned is one way I try to pay it forward.

Cheers!

Get-Deadlock counts

Deadlocks can be captured several different ways. When using TF1222, the deadlock information is dumped into the SQL Error logs. Getting that information out is not always as easy as capturing the information however.

I created this simple PowerShell script to get the counts and only the counts of how many deadlocks occurred since the logs were recycled. I recycle my logs every night at 12:00 midnight.

The script can be modified as needed for your needs. To use it now just copy and paste and change the ‘your_server_name’ to your actual server name.

Get_Deadlock_count

Download the file from here.

Cheers!

Finding SQL Server Analysis Services listening port

I have not worked with SQL Server Analysis Server very much. That is apparently changing as of now and I am still undecided on how I feel about that. Don’t get me wrong. I really enjoy working in SQL and will keep an open mind as my learning path continues. My heart however is with the main SQL engine.

During the server setup and configuration we record basic information on the server. One of the pieces of information I needed was what port Analysis Server listens on. I had no idea. So how do we determine what port?

Search for ASConfiguration

There is a chance you may find SSAS configuration by searching for msmdredir.ini.

search

You may also try looking in this directory

emptyfolder

If you can’t find the ini file anywhere you may find it via the Process ID and netstat.

Finding Process ID

We need to start with finding the process ID that SSAS is using.

configmanager

Using the Configuration Manager we can find what ID we need to look for.

Now open a command prompt. Start > Run > cmd

open_cmd

Now with the following commands we can use netstat to find what port SSAS is listening on.

netstat_host

Reviewing the output we find what we are looking for. The port is 2383.

netstat_host_result

Using the following commands we can output the information to a text file for whatever purposes you require.

netstat_output

And the output should look something like this.

netstat_output_result

Conclusion

As we can see, there are a few different ways to track down what port SSAS is listening on. The default port for SSAS is 2383. As most of you know, the port this component listens on can be changed. The evidence in the screen shot confirms that. Knowing how to find the information you seek as it relates to SQL Server and it components is an important part of a DBAs job.

Cheers!

Database in Transition: Error 952

I got a call the other day from a night shift DBA with an issue. Seems he tried to contact the primary DBA for the server but they didn’t answer their phone and I was the unlucky knucklehead to answer mine. As it turns out, it wasn’t a big deal and gave me a good topic for a post.

Issue

The DBA in question had an Incident ticket he needed to work on for a Project Manager a little heated due to overtime being scheduled and they were behind in getting things ready for the work. We needed to restore a database from the 3 AM backup over the database they were going to work on. One thing to note, our evening and night shifts work both Oracle and SQL. I did that in my past life and remember it is not always easy to switch modes from one to the other especially when a manager is constantly calling you to see if you were done with the task.

Error

The DBA who called explained he was getting an error when trying to restore a database. I asked if he was first putting it in single user mode. His response, “No”. I explained he needed exclusive access to the database to restore it. I then walked him through navigating to the database Properties window by right clicking on the database name.

He then said he was getting an error when trying to open the properties window. The error was:

“Database in Transition” Error 952.

After logging on myself and looking in the logs I found this.

takeoffline

It seems when he tried to restore the first time and was unsuccessful he tried to take the database offline.

I then explained we needed to use a query to make sure nothing was blocking us.

findBlockingSPID

This is a very basic script to find the blocking SPID.

After tracking down the SPID causing the issue ( and killing it) we were able to open the properties window, script out the following command(s) for future reference/use and restore the database without issues.

setSingleUser

If you are not familiar with how to script out the above T-SQL code you can follow these basic steps to do so.

In the Object Explorer expand the server and expand the database folder. Then find the database you want to use and right click the database. A menu opens, select Properties at the bottom. Once the database properties window opens, navigate to the Options page and scroll to the bottom. Under State group there is a setting called “Restrict Access” change that to SINGLE_USER and script out to a new window by clicking the “Script” button at the top of the window next to “Help“.

There are a few comments on forums that mentioned restarting the SQL Server service. While that will work (since it kills all sessions) it may not be the best solution especially if you are on a production server and can’t restart the service. Blindly restarting a server is not usually the best option.

Conclusion

There are (almost) always multiple ways to solve an issue. Coming up with the best solution sometimes comes with experience or finding the correct blog post/forum entry that works best for your situation.

Cheers!

How to get useful information from sys.dm_os_performance_counters

This DMV can be very useful to get information about SQL server. I need to point out a few key points before going too far into how we can use it. While it does return a wealth of information it does not return any counters outside of SQL Server for obvious reasons. The data returned can be a little confusing as well. Without further ado let’s get into it.

sys.dm_os_performance_counter

chart_small

See the Note on the column named cntr_value

For now let’s take a look at the cntr_type information returned and see if we can clarify that a bit. Using the below query to get the counter types.

distinct_cntr_type_query

Now we have our cntr_type but we need to figure out what these numbers mean.

cntr_type

If we look on msdn we find information to help understand…hopefully 🙂

PERF_COUNTER_LARGE_RAWCOUNT: 65792: Decimal: This value does not need calculations is the same as PERF_COUNTER_RAWCOUNT (65536) but is 64-bit for larger numbers values

PERF_COUNTER_BULK_COUNT: 27266576: Decimal: Average number of operations completed during each second of the sample interval. This counter type is the same as the PERF_COUNTER_COUNTER type, but it uses larger fields to accommodate larger values.

PERF_AVERAGE_BULK:

537003264: Decimal:

1073874176: Decimal:

Number of items processed, on average, during an operation. This counter type displays a ratio of the items processed (such as bytes sent) to the number of operations completed, and requires a base property with PERF_AVERAGE_BASE as the counter type.

PERF_LARGE_RAW_BASE: 1073939712: Decimal: This counter type collects the last observed value. It is the same as the PERF_RAW_BASE counter type except that it 64-bit and uses larger fields to accommodate larger values.

query2

Using the query above written by Amit Banerjee twitter | blog

The below image is what we will see in the results. Still not the useful data we are after but we are getting closer.

query2_return

Now on the first image there is a Note we need to be aware of.

For per-second counters, this value is cumulative. The rate value must be calculated by sampling the value at discrete time intervals. The difference between any two successive sample values is equal to the rate for the time interval used.

Keeping that in mind, if we look at the cntr_type 272696576 definition it is an “each second” value. We can run the query below gather the values run it again in say 1 second and determine our value for one second.

query_per_second

As you can see this DMV has some very useful information but at face value can be a little confusing. Only after digging deeper into  the details on the cntr _type and doing additional querying and calculations can we get useful data.

Cheers!

Reference:
sys.dm_os_performance_counter
http://technet.microsoft.com/en-us/library/ms187743(v=sql.105).aspx

WMI Performance Counter Types (Windows)
http://msdn.microsoft.com/en-us/library/windows/desktop/aa394569(v=vs.85).aspx

A lighthearted look at our jobs

I need some help and I am reaching out to the community for some feedback. I am looking at gathering information along the lines of something like this.

If you aren’t checking to see if backups ran last night…

…You might be doing it wrong

If you aren’t reviewing SQL and or Server logs

…You might be doing it wrong

If you aren’t practicing restoring your backups

…You might be doing it wrong

Hopefully you get the idea and what I am trying to compile. Add comments and if I use your input proper attribution will be mentioned, that is, if I already don’t have it myself or someone else has not already provided the feedback.

Now to get things started.

If you don’t leave a comment here…

…You might be doing it wrong

Cheers!