How to move AlwaysON AG Databases ?

Back in 2011, we have seen how to move a database which is participating in DB Mirroring here. In this blog post, let’s see how to move a database which is participating in a AG to a new drive(location). In my AG setup,I’ve three replicas(2 near Replicas-Sync mode and 1 far replica sitting in a different data center-Async mode). The database which I will be moving to a new location is “sales”. See below for current paths.


Now we shall move these files to below mentioned new location (In this post, let’s see method two mentioned below):


Method one:

Remove the database from AG.
Detach the database.
Move the files physically to new location(s).
Attach the database.
Rejoin the database in your AG.

Method Two:
In this method we shall see how to move files without removing the database from AG.

Steps to perform in sequence:

Suspend data movement for the database which you are working on to all replicas.
Logically move the database files(On all the Replicas).
Stop the SQL Server services. – This step will create an outage for all other databases residing on this instance.
Now move the physical MDF and LDF files to your new location.
Start the SQL service from config manager.
Resume data movement.

Before proceeding any further, I made sure the new path exists on of my  replicas.



2. Run this on all the replicas.


Now…I stopped SQL Services and moved physical files to new locations and started SQL Server.

3. Now resume data movement.


Voila…Now it’s all set as per my requirement.


That is it folks for today! Have fun…


SQL Server 2012 SP2 CU7 – @@Version

Today I was testing CU7 for SQL Server 2012 SP2 and as usual after installing CU on our test server, as a practice I was reviewing the Error Log and interestingly it mentioned CU7 during the startup! Let me show you what am saying…See below screenshot on a server running SQL Server 2012 SP1+CU4. Till date SQL Server error log never revealed CU# in the start up entries…It just says the Service pack level which you’ve got.


Now with the CU7 for SQL 2012 SP2, that’s no longer the case 🙂 See below screenshot:


Even @@version from T-SQL is revealing the CU installed.


SQL Server 2012 SP2 RTM got released :)

Yesterday, microsoft released Service Pack 2 for SQL Sever 2012. They made Slipstream Installation packages available as well to the customers 🙂

And hey, you know what….CU1 for SQL Server 2012 SP2 is scheduled to release in July 2014. It’s time to upgrade all your SQL Server 2012 Instances to SP2!

SP2 can be downloaded from

For the list of bugs which are fixed in this Sevice pack, visit

Couple of very critical bugs which got fixed: ( they are critical to me, YMMV) 

SQL Server crashes when LDF file for Tempdb is full.
Rollback causes SQL Server Database to go to Suspect mode.
No error message when Log backup fails in SQL Server 2012.


SQL Won’t come up after Installing Service pack!!

Hello folks! In this blog post, I would like to share a strange experience which I’ve never experienced till today while Installing Service packs for SQL Server.

Well, I’m in the process of Installing new SQL Server(2012) on Windows Server 2012. Installed RTM…Everything went well. Now I kicked off applying Service pack 1 for SQL Server 2012. Erkk…..SQL services won’t come up and the event viewer says “Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.” and the one which is shown below!!!…


 “Script level upgrade for database ‘master’ failed because upgrade step ‘u_tables.sql’ encountered error 25641, state 0, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.”

Whatttt….?? Really? Nothing useful found in the SQL Error logs as well 😦 My immediate thought was to run Repair and I kicked off the repair wizard. It took its own sweet time. 25 mins passed and I got this stupid message:(


Agian, Nothing useful found in the SQL Error logs 😦 End result is it couldn’t fix it 😦


This is not good…!

So what’s the work around/Fix for this issue which worked for me in this case?

I changed the Service account for SQL Server to Local System and Boom…it worked 🙂 SQL Server DB Engine Service started and the build number has been updated as well! Once am done with Installing Service pack and the CU, I changed it back to the appropriate domain account(as it was earlier).

So, this sounds to me like some missing permissions issue but am not 100% sure what exactly it was looking for. Please let me know if you’ve encountered this issue in the past and what was the resolution.

Importance of reviewing your SQL Server Error Logs.

Few weeks ago I posted a poll here asking for how often do you review your Error logs. As promised, here comes the follow up post:)

Unfortunately, 45.45% out of 11 people said “Never and I’ll open Error log only If I want to investigate something” . IMHO, this is not a good practice(Especially if you want to be an outstanding DBA). Please don’t get me wrong…Am not saying that you are not an awesome and outstanding DBA! ( Well, People call you a DBA because you are already outstanding 🙂 )

Okay…….let  me say this guys, Start reviewing your Error logs daily as the first thing when you get into your cubicle/desk/office. Trust me, there will be lot of surprises(at least a few) based on number of Servers(Instances I should say) you manage. SQL Server Error log is really one of the best buddies a SQL DBA has got for free besides default trace 🙂 . I simply love these two things in SQL Server which provide so much useful information in case of investigating something(At no additional cost/overhead).

A simple real life use case: SQL error log records all the failed login attempts by default, if you keep an eye(or even Just glance over it) you should know if there are any unwanted/unsafe login attempts(Which are failed) hitting your SQL Instance. For example…I see quite a few times, incoming connections from a particular host attempting to connect as “sa”. Error Log records IP address of the machine and the time stamp of course. I can just do a NSLOOKUP and check with respective owner of the machine on what’s happening. This is just one useful scenario, there are tons of use cases which you can come up with!

Believe me guys, there will be lot of surprises for you and lot of things you’ll discover about your own SQL Server Instance which you don’t know, just by reviewing your error logs on daily basis for a week or two!

Most of the times, I hear people saying ” I am responsible for managing multiple Instances, I really don’t have time to get into each of my SQL Server and review error log. It will eat up my entire day“. Well, you really don’t have to do this. All you have to do is just setup a job and automate which basically reads all the error logs from all of your servers and generates a fancy report and sends you an email.(All you need is to just create a simple SSIS package and SSRS for fancy reports) or may be Powershell could help you.

If you just have handful of SQL Instances to manage, see this blog post.

Basically, This will allow us to be “Proactive” and not being “reactive”.  Preventing something bad happening to your database as opposed to fixing will really make you stand tall in front of your management and your peers. Am not saying, by reviewing Error logs daily you can literally prevent any issue from being happening. But you will be definitely able to reduce number of “OOPS…” moments!
IMHO, Even though part of DBA job is to troubleshoot/fix database related issues, our prime time goal as a DBA should be  trying to prevent those issues from occurring at the first place!
Let me know if you need any help automating this process. I will be glad to point you in the right direction.

Cheers! Happy friday 🙂

Reviewing SQL Server Error Log – A Better approach!

How many of you have a habit of reviewing Error Log(s) of the SQL Server(s) which you support on a daily basis(At least on regular basis, if not daily)? If you are not doing that, go ahead and start making it as a practice from “Right now”. You can thank me later for this suggestion 😀

If you’re already doing that,then you are awesome…! But, let me ask this. How many times you get annoyed reviewing your error log(s), especially if you are looking for a specific error. Well, you can apply filter or you can simply query your error log from T-SQL. But what if you are only looking for all the severe Errors, total error count, Errors by frequency all at one place? Yes, you can write your own query and use SSRS for generating a fancy report. But Wait……What if I say SSMS already has this cool report inbuilt for us?

In this blog post I will show you a better way to decipher your current Error Log for Errors by using that hidden report.
It’s called “Number Of Errors” Report and you can get to this report by right clicking on “Management” Node -> Reports -> Standard Reports->Number of Errors in your SSMS as shown below.a

How it looks? See below…

As you can see, at a glance I know I’ve got 6 Severe Errors(Sev 18 or above) and 5 Moderate errors in my current error log. See below screenshots for how neat the info is once I expand those nodes.

Severe Errors:

You can see I’ve got the same 824 Error repeated 6 times. ( Well, I tried attaching the same corrupted ldf file 6 times for this demo)

Moderate Errors:

All errors by Frequency:

Is in’t nice? Personally, I liked this report…and thought of sharing what I’ve discovered with you folks 😀
Please ignore if you are already aware of this nifty report, if not…well, you know it now!

SQL Server Discovery Report – Know What SQL components you’ve got Installed at a glance

Let’s assume you’ve got a new SQL Server under your support as a primary DBA and you’ve decided to investigate and document all the SQL Server components/features installed on this Server. What are your options? Will you go to Config manager and look at Services for Installed Services. Well, what about Client components? You can pull build levels/Version(SP/CU) easily for your database engine using T-SQL, what about other services? What about your client tool(s) version…Chances are they will be same as your DB Engine(but they could be different based on how and who patched the SQL Servers)? What if you’ve got multiple Instances of SQL Server with different versions…?

Well, we’ve something called “Installed SQL Server features Discovery Report” under Tools in your Installation Center which provides all this information in a single place 🙂 .  Go to the Installation Center from your start->All Programs->Microsoft SQL serverXXXX->Configuration tools->SQL Server Installation Center. Go to Tools and click on discovery report as shown below.


After few seconds, I got below report which gives me basic information on what all I’ve got Installed related to SQL Server on this Server with the build numbers, Edition, clustered/StandAlone etc all at single shot (You can see I’ve got 2 Named Instances, 2008 and 2012) which is pretty useful IMO.


Hope you guys find this nifty tool useful! Cheers…