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!

Msg 2767, Level 16, State 1, Line 2 Could not locate statistics

The other day when tuning some indexes on a project I was being my usual lazy self and I dragged/dropped the column name into the query window. After executing DBCC SHOW_STATISTICS I was confronted with the following error message.

could not locate statistics

Wait, what? Why am I getting this error? I know there are statistics. So why do you get this error? I am not sure to be completely honest but I can tell you there is a very simple fix. Remove the brackets at the beginning and end of the column.

remove_brackets

Then execute DBCC SHOW_STATISTICS and you should be fine.

results

Hopefully the helps reduce some of the stress you have related to the indexes you are tuning.

Cheers!

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!

SQL Saturday Denver

September is my favorite month out of the year. It is usually a seasonal transition month, going from hot summer days to cooler fall days depending on where you live. I also have several family members who were born in this month.

The weather

The weather is one reason why I am excited to be attending SQL Saturday in Denver on September 28th.  I lived in and around the Denver area in the 80’s and even met my wife there. I really liked living there with the mountains, fresh air and beautiful weather. The trip will bring back fond memories of friends and family. There is a restaurant called the Blue Bonnet Café on South Broadway I am planning on fitting into the schedule. They have an out of this world Chimi Changa.

Pre-Cons

SQL Saturday Denver is offering 3 all day pre-cons on September 27th from 9-5.

Real World SSIS: A Survival Guide/Tim Mitchell

SQL Server Internals from the Practical Angle/Dmitri Korotkevitch

Predictive Analytics in the Enterprise/Carlos Bossy

Remember these are not part of the SQL Saturday registration and a separate fee. The fee includes all day training, breakfast, lunch and snacks. This is a great opportunity for an all-day training event. Go here to check them out and register fast there are limited seats and they are filling up.

Presenting

My session Daily Checklist and Server Monitoring is one in a series I have called the DBA Survival Guide. I am looking forward to sharing and learning. It has been a while since I attended my last SQL Saturday. Another area I am looking forward to is meeting with new and hanging with old friends from the SQL Community. It should be a fun session and event over all.

Speaker Line up

There are 6 tracks BI, DBA, Dev/DBA, More DBA, Power BI, and Professional Development. With 8 confirmed MVPs and countless others who should be, presenting a wide variety of sessions sure to meet anyone’s list of new things to learn or brush up on. Take a look at the schedule here. If you haven’t registered yet and you are even thinking of attending you need to register. Today!

Conclusion

I have very good memories of living in Denver. I do pass through almost every year with my family during spring break as we head into the mountains but this trip will be much different. This trip will be about learning, sharing, connecting with other cool people in the SQL Community. I am sure when the weekend is over I will be adding the memories to the good side of the ledger. If you are attending SQL Saturday Denver stop and introduce yourself.

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!