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.

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.

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.

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!

PASS DBA VC – June 13, 2012

This week I am speaking for the PASS DBA VC on Wednesday June 13 at 2:00 PM Eastern (1:00 PM Central, 12:00 PM Mountain, and 11:00 AM Pacific). You can also use this link to check times in your area. World Clock

Don’t Panic – Keep Calm And Organize Your DR Plan
Disaster Recovery 101

Chances are sooner or later you will experience a disaster of some sort. Having the knowledge and skills needed to recovery after a disaster is one of the most important skills a DBA needs to have. Practicing those skills will make you more confident and help you handle yourself during the recovery process instilling calm in others.

I will discuss what it means to have a recovery plan, what a Service Level Agreements and what you need to know about them go through several scenarios demonstrating how to recover from each one.

Registration Link: https://www.livemeeting.com/lrs/8000181573/Registration.aspx?pageName=4zxpn664m95z034q

Meeting Link: https://www.livemeeting.com/cc/8000181573/join?id=FZT8Q4&role=attend

Registration is not required to attend the meeting. However, it is if want to participate in the raffle for a $50 Amazon gift certificate.

Cheers!

Msg 601, Level 12, State 3, Line 6

Here is is the full error.

Msg 601, Level 12, State 3, Line 6
Could not continue scan with NOLOCK due to data movement.
Msg 3167, Level 16, State 1, Line 6
RESTORE could not start database ‘database_name’.
Msg 3013, Level 16, State 1, Line 6
RESTORE LOG is terminating abnormally.

Problem

A project I support needs to migrate 5 databases that were not originally upgraded/migrated due to customer requirements. All of the other 150+ database already migrated have had several updates. New columns were added and some removed, new tables and other such changes, you get the idea. I did some comparisons to determine what needs to be done to upgrade the databases to the newest version and generated a few scripts. (Since the developers apparently did not track their changes) While attempting  a restore this morning I ran into the above error.

Investigation

I did my usually consultation with my DBA buddy *oogle and found a few articles/posts on a few forums but they really did not align with my scenario.

Then I remembered yesterday while working remotely I was downloading the databases from an FTP site and my VPN connection kept dropping. So I had to keep downloading the database repeatedly until I had the entire database. This is one of the database I downloaded when I was having the connection issue. Turns out I did not get all of the file even though the size was the same as the original.

Since I was in the office this morning I decided to download all of them again to make sure I had good copies of all of the files I needed to restore. The second time around everything worked as it should and no errors were displayed.

Conclusion

The databases apparently did have some corruption from the connection continuously dropping when down loading the copies.  Downloading them again fixed the error above. When you get an error on a database make sure you look at all the steps you performed up to the point of the error. Sometimes fixing the error can be as simple as getting a good copy of the backup you are trying to restore.

Cheers!

Presenting at CACTUSS

I don’t usually write about myself but this one is different. I am doing a little shameless self promotion.

I’ll be giving my first ever SQL presentation tomorrow at CACTUSS in Austin. Am I scared? Hell yes! Will I survive? There is no doubt in my mind. I plan on having fun and doing the best I can. If or when I make a mistake I will adjust and move on.

You see, it may be my first SQL talk but I am no stranger to public speaking. I am an elected official of a fraternal organization in our church and monthly I get up in front of a bunch of grumpy old men and run the meeting. I don’t always know the answers but, I keep my cool (usually) and work through any issues the best I can.

My presentation “Maintenance Plans or Not” is based on replacing the “canned” Maintenance Plans in SQL Server Management Studio with Jobs running Powershell Scripts. There is still some  work that needs to be done to it as I move forward with the concept but I have a start and will continue to improve.

The ideaor concept is to have “options”. If you, as a DBA, want to manually create the Maintenance Plans, no problem. If you want to use T-SQL O.K. If you want to use Powershell, then (wink, wink, nudge, nudge) I have scripts for that! All projects and server installs are not the same. They can be very similar but, this way we have several options and are not “locked” into only one solution.

So, if you are in the Austin metropolitan area and want to have some free food and want to heckle me then stop on by tomorrow night.
Here are the details and the CACTUSS web site.
http://cactuss.org/

We meet at:
Quarry Oaks II
10900 S. Stonelake Blvd.
Suite 225
Austin, TX 78759
5:30PM to 6:00PM – What problems are you having?
6:10PM to 7:00PM – Feature presentation
7:00PM 7:30PM – Q&A follow up and discuss next meeting.

Cheers!

Error running SQL 2008 SMO SQLBackup

The actual error I was getting is this.

Exception calling “SqlBackup” with “1” argument(s):

I am going to explain what I was doing, the cause of the error and the fix. The purpose of this little exercise is to inform and hopefully reduce the stress level of others.

A little back ground.

I have been working with powershell creating scripts to replace our maintenance plans. So, naturally backups were on the list. After creating the scripts at home (yeah, I’m like that, work is never done. But can you call this work?), testing and confirming they worked. I took them to work to start replacing different tasks in the Maintenance Plans with Jobs being run by SQL Agent.

Since we backup system, user, and user logs (if Recovery Model set to “Full”) I created three seperate Jobs and set a scheduled for the jobs to run. I then moved to the next task to be replace in the Maintenance plans; removing old backup files.

I finished writing the script and during testing needed to add some database files (bak) and log (trn) into the directory to confirm testing results. I ran the backup job no problem. When I ran the log backup, *BAM* the job failed. Reading through the error message in the Job History as usual was very informative, not! So I decided to take a took at the script in powershell ISE.

Again, I ran the backups with no error, but every time I ran the log backup I got this error.

Exception calling “SqlBackup” with “1” argument(s): On server “MyServerName”

What the heck was going on here? It ran fine on my machine at home. (Never heard that one before, hug)

I searched on Google, but did not find much help there. There were a few things but again as usual they were similar but different. So I kept digging. I then turned to my next source of good information Twitter. For those of you that don’t use twitter (What are you waiting for?) there are several help tags that all the smart (and cool) people monitor. Like #sqlhelp for SQL Server related problems, issues or questions and #poshhelp for the same related to Powershell. Unfortunitaley there wasn’t much help there either.

Then I got a direct message (DM), again for those of you not on twitter, that is a private message. It was from Aaron Nelson, a.k.a. SQLvarient ( blog | twitter ). He asked me if I wanted some help with the error. Know Aaron, (through twitter, I haven’t actually met him. Yet) I promptly took him up on the offer and sent him my scripts.

Long story short he said they worked on his testing environment. One thing he did ask. Was I loading in the plugins for SQL server (more on those later). He said he had seen weird behavior in the past and loading them into the profile fixed his issue, besides it couldn’t hurt. So, loaded the plugins and no luck, same error. Aaron then said something that sparked the light bulb, “It’s probably some kind of security “feature” that causes the problem.”

I search Google again, this time for SQLBackup (this is using SMO) and securiy related issues. I found a post from someone saying they had the same error and after setting the correct permissions on the database the error went away. SQL BOL (books online) stated the following:

To run the SqlBackup method, users must have BACKUP DATABASE or BACKUP LOG permissions on the database, or be a member of the db_owner and db_backupoperator fixed database role and the sysadmin fixed server role.

Am I reading that correctly? To create Backups I need permissions in addition to my sysadmin permissions? Hmm. Confusing to say the least. I sent an email to our Microsoft Rep and then we talked on the phone. He agreed, the wording is confusing and suggested doing some additonal testing related to permissions to confirm.  We are working with him and our security group to update post install scripts that remove Guest CONNECT permissions to databases. I asked if he thought that may be causing the issue, he said, “That is a strong possibility”.

So, armed with this knowledge, I went to the server I was originally testing my scripts on and granted connect permissions for the user I was logged in as to msdb.

Then I tested the log backup script and it ran without any errors. YeeHaa! (that’s a Texas thing)

So there you have it. If you are getting the error above, you may want to check permissions for the user you are logged in as and see if you have guest connect permissions on msdb. Removing these permissions can cause all kids of issues.

If you are removing GUEST CONNECT permissions based on old/bad information in BOL like we are I suggest you read more about why it is a bad idea from Buck Woody’s (blog | twitter ) post. Here are a few additional links to information you can check here and here about this as well.

I hope this helps someone else if/when they run into this issue.

Cheers!

Using Powershell SQL Database Backup and Compression

Recently I have been working with Powershell, alot. My goal is to create powershell scripts to replace the maintenance plans we have at work. I know maintenance plans, yuck! But they are better than having nothing at all.

Anyway, I wanted to demonstrate a really cool feature only recently add to SQL server in 2008 version. You may have read about database compression before but I am going to show you some hard numbers and let you decide if this is a feature you want to start using.

This script is from SQLvarient aka, Aaron Nelson’s ( blog | twitter ) powershell presentations.

Using this script:

##################################################################################
## This script backs up all user databases
## The orginial code for this came from Aaron Nelson @SQLvarient
## Modified 9/25/2010
## By AJ Mendo @SQLAJ
##################################################################################

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMOExtended”) | out-null
# define the instance name
$instance = ‘Server_Name’
$s = new-object (“Microsoft.SqlServer.Management.Smo.Server”) $instance

#We define the folder path as a variable
$bkdir = “D:\Backup”
$dbs = $s.Databases
foreach ($db in $dbs)
{
if($db.IsSystemObject -eq $False) #We don’t want to backup the tempdb database
{
$dbname = $db.Name
$dt = get-date -format yyyy_MM_dd_HH_mm #We use this to create a file name based on the timestamp
$dbBackup = new-object (“Microsoft.SqlServer.Management.Smo.Backup”)
$dbBackup.Action = “Database”
$dbBackup.Database = $dbname
$dbBackup.Devices.AddDevice($bkdir + “\” + $dbname + “_db_” + $dt + “.bak”, “File”)
$dbBackup.SqlBackup($s)
}
}

Here is a screen shot of the size of the files after we run this script.

Now we will add the compression Property to the script.

If you add this line:

$dbBackup.CompressionOption = “1”

above the last line in the code

$dbBackup.SqlBackup($s)

Then run the code again and look at the sizes of the databases now.

Much smaller like 25% smaller. Now that is impressive and can save on disc space.

Hopefully you can or already use this feature on your SQL Server 2008 or 2oo8 R2.

Cheers!

Nightmare on your server

I recently discoverd a nightmare on one of the servers I have taken over from another DBA in our group. The find was really by accident. Even though, I did so some preliminary documentation on the server.

You see, currently we use maintenance plans when setting up our servers. I actually have this on my list to fix. That will be another post and I am trying to put together a presentation for my first talk at our local user group, but I am getting off the subject if this post. 

As I mentioned we use maintenance plans here. We also have an installation document for installs. I took over a project from the DBA that created the documentation, so I figured  this person followed their own document. Silly rabbit tricks are for kids. 

The maintenance plans are for system and user database. Each plan has we have 4 differnet subplans. One for the full backup, one transaction logs, one to check database integrity, and one to rebuild the indexes. What I discovered was this DBA included the last two steps into one.

Rebuild indexes, update statistics, and (wait for it, wait for it) shrink the database. Oh, no not that. It never occurred to me when seeing all the angels walking the halls around here without wings. If you don’t know what I mean, then you need to get on Twitter. But again, I am getting off the subject.

I found this little tidbit as a result of an error in the logs. I looked back through the history of the jobs and *fail*, *fail*, success, *fail*… So I removed the old failing subplan and created two seperate maintenance plans. Once for Datatbase Integrity Checks and one for Rebuilding Indexes. This was several weeks ago now and guess what? No more errors in the logs.

Let this be a lesson to you. When taking over servers from others go through it with a fine toothed comb and make sure things are setup the way you want them setup. Keeping in mind project requirements and SLA agreements.

Hope this helps. Let me know if you have any questions and check back. I’ll have some addition posts about replacing the maintenance plans.

Cheers!