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.


SQL Server Database Snapshots – Things to remember!

In this blog post, let’s see what SQL Server database Snapshots are and how they can be created and how to revert to a DB Snapshot and few important things to keep in mind before making use of them.

Microsoft introduced database snapshots in SQL Server 2005 as an enterprise only feature(I believe, that’s true even with SQL 2014…but I could be wrong). The basic concept of a database snapshot is to create a read-only, transactionally consistent(as of the moment of snapshot creation) static view of a given database. Even though they are named as “DB Snapshots”, they have got nothing to deal with Snapshot replication or Snapshot Isolation.

When could a DB Snapshot be useful?
Most common use case:  Let’s say your DEV/Testers are doing some testing(which makes whole bunch of changes to your database) in your test environment and they want the database to be reverted to a state how it was prior to the testing once they are done. A Snapshot works awesome in this case. Reverting is waaaay faster than restoring your database(especially when you are dealing with huge databases).

See this for more info on how they work behind the scenes, when will they grow in size, other use cases and Gotchas to remember while using Snapshots. Let me brief a few important things here.

  • DB Snapshots are not substitutes for your DB backups. Irrespective of whether you’ve snapshots or not, you should have healthy restorable backups in place. Don’t treat DB Snapshots as DR or HA solution.
  • Snapshots will introduce IO overhead, depending on workload on your Source Database.
  • You can’t backup a DB Snapshot and can’t attach/detach a snapshot; you can’t create snapshot for System Databases.
  • No GUI for creating Snapshot(Only T-SQL).
  • You can’t drop Original Source Database as long as a referring snapshot exists for that database.
  • No FAT32 support(Snapshots work on NTFS Sparse files technology).
  • Since they are read-only, static copies…new users can’t be added.
  • Make sure you’ve enough space on the drive holding your Snapshot file. If the drive runs out of space, Snapshot will be marked as “SUSPECT” by SQL Server.
  • Reverting to a Snapshot will break Log chain. Take either a Full or a Diff Backup(assuming you’ve got your latest Full backup) to bridge the Gap in your log chain) immediately after you revert. If not, all the log backups will fail from now on…
  • If your DB is not in SIMPLE recovery mode, Take a log backup before you perform Reversion to secure everything since your last log backup.
  • Full Text Catalogs will be lost once you revert your database. Be careful, if your database uses Full Text features as this could be an unpleasant surprise.
  • Both the source DB and the snapshot will be unavailable when the actual reversion process is in progress.
  • Source DB should be Online,No read only file groups should be present in your Source DB to be able to revert from a snapshot.
  • If your source DB gets corrupted, You can’t use Snapshot for reverting. ( See, they simply can’t replace your Backups)

Now it’s Demo time 🙂
I’ve a database called “Tst_restore” which has 3 tables and this will be my source database for this demo purpose. I’ve created Snapshot as shown in the below screenshot.



IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Tst_Restore_SS')
NAME = Tst,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SREE\MSSQL\DATA\Tst_Restore_SS.ss')

Now you can see I’ve a snapshot created.


See below for the Properties(files section).


As you can see, it has got no ldf file. (If you’ve noticed, while creating the Snapshot I didn’t provided ldf file in my syntax. Yes, that correct…you should provide only (all of) the data files, not the log file while creating DB Snapshot)

Revert a Database to a snapshot:

Now, let’s see how to revert a database to a Snapshot. Assuming something went wrong and you want to revert to snapshot which you’ve taken in the above step, you can use below syntax.

USE master

-- Reverting Tst_Restore to Tst_Restore_SS


As you can see above, to be able to revert we need exclusive access to the database(same as restore). Hence I am taking DB offline and bringing it back online immediately.
Note: Don’t forget to bridge the gap in backup chain right after you revert to a Snapshot. As I’ve already mentioned reverting to a Snapshot will break Log chain. Log backups will fail if you forget this step.

Hope this is informative…!

RESTORE DATABASE is terminating abnormally.Msg 905, Level 21, State 1, Line 1.

Recently one of my friends(Rookie DBA, Just started his career as a Jr DBA) called me and mentioned about this error he encountered while he was trying to restore a database(2008) from prod to Non-Prod Environment and was wondering how to make it work.

“RESTORE DATABASE is terminating abnormally.Msg 905, Level 21, State 1, Line 1.

Database ‘TstDB’ cannot be started in this edition of SQL Server because it contains a partition function ‘TstPF1’. Only Enterprise edition of SQL Server supports partitioning.”

Well, as you can see, the message thrown by SQL Server is pretty much self explanatory. The database is using Partioning which is Enterprise only Feature and hence you can’t restore this database to standard edition or Express edition. The solution is to drop all the partition schemes and partition functions prior to taking a backup and use that backup to restore on any Non-Enterprise edition(Except Developer Edition) instance! That’s the reason you should be very careful if you are using enterprise only features in any of your production databases. For example TDE is an Enterprise only feature, CDC is an enterprise only feature. Having any of these features turned ON on your database will make the DB restore process to Non-Enterprise editions(Except Developer Edition) little painful.

So, Is there anyway to retrive all the Enterprise Only features enabled on any given database? Yes, you can query the DMV “sys.dm_db_persisted_sku_features”

SELECT feature_name FROM sys.dm_db_persisted_sku_features;

For example, I’ve a test database which has Data Compression and CDC enabled and the result is as shown below…


Btw, For looping through all the databases to identify enterprise only features enabled, you can write a simple loop or simply make use of “sp_MSforeachdb”.

Cheers! Happy weekend 🙂

How to Move a Mirrored Database MDF and LDF Files?

Couple of days ago, I had a conversation with one of my colleagues, on how to move database files from one location to another which is has DB Mirroring enabled. In this blog post, let’s see that in action..

Let’s consider the below Scenario:

1. You’ve a Large Database(let’s say 500+ GB DB) which is participating in DB Mirroring as a ‘Principal’ Database.

2. That’s the only database sitting on this Instance, a Dedicated Instance for your Application(In other words, detaching-attaching DB will cause almost the same impact as SQL Services Restart)

3. You want to Move your MDF and LDF files to another LUN (because of space constraints or you are doing SAN Expansion or something else)

4. You do not want to Break Mirroring completely, because you do not want to re-configure everything from scratch…which takes considerable amt. of time and not acceptable to your Business, running without DR for that long period of time.

5. What is the option we have in this case?

Solution: First thing to note, we can’t detach a Database while it is participating in Database Mirroring. You’ll get the below error if you try to do so..

“The operation cannot be performed on database “Mirror_tst” because it is involved in a database mirroring session.
ALTER DATABASE statement failed.”

so what to do now..? Remember, we’ve ALTER DATABASE…..MODIFY FILE….to do this trick.


I’ve a database called ‘Mirror_tst’ which is being mirrored from PROD Instance to DR Instance as you can see below. Also, please note the current database file locations in the below screenshot(As of now, they are in default location)

Now, Let’s update the database file(s) location in system catalogs to “C:\Backs\” folder. For this I’ll issue the below commands.

MODIFY FILE(NAME= mirror_tst,FILENAME=’C:\Backs\mirror_tst.mdf’)
MODIFY FILE(NAME= mirror_tst_log, FILENAME= ‘C:\Backs\Mirror_tst_log.ldf’)

and i received below messages:

The file “mirror_tst” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “mirror_tst_log” has been modified in the system catalog. The new path will be used the next time the database is started.

So, the database file locations were modified in system catalog, all we have to do now is Stop SQL Service, move the physical files and start SQL Services. Let me do that!

I issued SHUTDOWN WITH NOWAIT on my PRINCIPAL Server as you can see below.( Be very cautious with this statement, understand what exactly you are doing before running this statement on your Prod Instance, believe me guys, Running this single statement unknowingly might cost you, loosing your Job!!..)


As you can see SQL is stopped and now, I moved the Physical files to ‘C:\Backs” folder as you can see below.

Now, I’m starting SQL Services and let’s see what happens to our database and more importantly to our Mirroring Status, keeping my fingers crossed 🙂

Once Restarted, the SQL Server was happy to bring up this Mirrored Database and our Mirroring Process is running happily without any issues as you can see below:) 🙂 Notice Physical files new location and the Mirroring Status now. Everything is pretty Normal:)

Hope this post helps,if you get into same scenario….Cheers!

Diagnosing Database Restores – Trace Flag 3004

Recently one of my fellow DBA had an issue with a Database Restore, where he was restoring a database using a full backup(from GUI) and the wizard was progressed upto 100% and it stayed there for more than 30 Minutes and he pinged me to see what’s going on!! Well, The actual issue was the database which he took the backup had 16000+ VLF’s!!(Very Very Bad sign of Log file sizing) and the log file size was close to 78 GB and there was a hugeeee open transaction(which SQL will Undo while restoring the Backup). Note he was creating a brand new database, So SQL will be creating brand new files and remember transaction log file will be Zero Initialized always as apposed to Data files(If Instant File Initialization is enabled on your Instance).

Our focus in this post is not regarding VLF’s and how Log file should be managed, but to identify what’s happening behind the scenes with your Database Restore and kind of identifying current Phase of restore process. For this you can make use of  Undocumented trace flag 3004 along with 3605 to get the events logged in our Error Log. You can enable this trace flag either globally or at session level. (No need of  touching your start up parameters for these trace flags) Please see the below Screenshot.

For demo purpose, Let’s restore a Full Backup of a database(it has an Open Transaction) and let’s see how our Error Log looks like with this trace flag being enabled.

I’ve restored the DB,  see the Error Log in the below Screenshot.

Focus on these steps where you can note the actual time SQL Server took for Zeroing your Log File, Redo,Undo and all other required Information.

1.Zeroing C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\DATA\3005_Demo_1.ldf
from page 1 to 20584 (0x2000 to 0xa0d0000)
2.Restore: Waiting for log zero on 3005_Demo
3.Zeroing completed on
C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\DATA\3005_Demo_1.ldf
4.Restore: LogZero complete
5.Starting up database ‘3005_Demo’.
6.The database ‘3005_Demo’ is marked RESTORING and is in a state that does not allow recovery to be run.
7.Starting up database ‘3005_Demo’.
8.Recovery is writing a checkpoint in database ‘3005_Demo’ (19).
9.Recovery completed for database 3005_Demo (database ID 19) in 9 second(s)
(analysis 20 ms, redo 1801 ms, undo 7690 ms.)
10.Restore is complete on database ‘3005_Demo’. The database is now available.
Database was restored: Database: 3005_Demo, creation date(time): 2011/08/23(23:16:47), first LSN: 38:19:1 blabla

Hope this is informative!

SQL Server System Databases

SQL Server System Databases are very simple and pretty small (msdb might be huge in some cases if we are not properly maintaining it) databases at a very high level, but the most(i mean it) important databases within SQL Server.  Why am i stressing the word “the most Important”??? Because SQL Server Stores it majority of the configuration information in these Databases besides storing some in Registry  and few configuration(ini/xml) files and SQL needs them in a healthy shape to be in running state. So what are the System Databases in SQL Server?

MasterMODELMSDBTEMPDBRESOURCE Database  and Distribution(I would call it as a pseudo System Database 😉 You will see this DB only if you’ve configured replication). Let me explain what each database does in brief…Let’s start with Master Database.

Master:  The Very first Database loaded by SQL Server on Service Start. If if finds any issue bringing up this database, entire SQL Server Instance will be down. Master Database stores very critical information regarding Instance configurations such as CPU info, Memory configurations, other sp_configure settings etc. It stores the Information(Meta Data) of all other databases on the respective instance. Stores Logins information and all other Instance scoped objects(Server Objects) such as Linked Servers, ENDPOINTS etc…

Model: This little guy acts as a TEMPLATE Database. whatever settings you configure on this database, they will be reflected on any other new database you create on this Instance(Classic Example – Setting Model Database Recovery Model to SIMPLE on your DEV Server, making sure that all the databases which you are going to create from now on will be in SIMPLE Rec mode on this Intance). If you create any object(let’s say a user table) inside Model database, all the new databases which you create from now on contains the same object! So…..Basically you can use this guy for your standardization!

MSDB: Well, this guy stores all the critical information any thing related to Automation. Soo….anything which you do with SQL Agent, it gets stored or it updates MSDB Database in one or the other form. All your Agent Jobs, Maintenance Plans information, Backup/Restore Events/Alerts/DB Mail information, even SSIS packages(if you are doing MSDB Deployments) and So on…..! Very Very Important and heavily used by Agent Service.

TEMPDB: The busiestttttttttttttt System Database out of all!  Ignoring TempDB configuration will heavily impact your SQL Server performance.  As the name implies…it’s a temporary Database where all the temporary objects will be created, Cursors use TempDB, Row Versioning is done in TEMPDB(SNAPSHOT ISOLATION), ONLINE INDEX REBUILDS(choosing Store/Sort Results in TEMPDB), Memory Spills occur a lot to TEMPDB with Hash Joins and Hash Recursions and even SORTS and many more things heavily uses TEMPDB.  Interesting thing to note regarding this Database is, It’ll be “Wiped Out” entirely when we shutdown SQL Server and Brand new TEMPDB will be created at Start up of SQL Server.  So….there’s no concept of  “CRASH RECOVERY ON TEMPDB” in SQL Server/You can’t take a Backup of TEMPDB.

RESOURCE DATABASE: This guy will always be in “STEALTH” Mode hiding from us. We can not see this database from our SSMS. We’ve to actually go to our File System to look at the Data and log files of this Database. It is a Read-Only Database containing “System Objects” of SQL Server. This guy is very very important while we are upgrading our SQL(applying Hotfixes/Service Packs/ CU’s etc)which many of the DBA’s ignore to take backup before applying Patches!!!

I believe that’s enough information to understand how critical these Databases are! For more information on these Databases check BOL….MSFT has tons and tons of very useful information on these Databases.

Backups on System Databases:

Well, now let’s see how can we secure these Databases. In other Words how to design a Disaster Recovery strategy on these System Databases. I’ve seen few(TBH Many) cases where a DBA ignores Backing up the System Databases….Very Very Bad!! Trust me guys…you’ll pay the penalty someday or the other if you ignore backing up these databases!

Before proceeding any further, let me tell you something…You Should never Ignore backing up and performing frequent Consistency Checks on your System Databases. In a worst case scenario where you have to rebuild your entire server, you would need them to be restored without any issues. ( Imagine a case where you have 100+ Jobs and 300+ Logins and 20-30 Linked Servers and you don’t have your System Database Backups….It’s scary enough just to imagine right?? ) FYI I’ve shown how to Restore Master and MSDB Database here.

Note: You’ve to know your SQL Server Instance Collation Information to rebuild your SQL Server(Rebuilding Master DB). I’m saying…rebuilding master, not Restoring your master database in this collation context. I’ll show you how to rebuild entire SQL Server in later posts.

Master Database  – Take Full backup as often as you do for your User Databases. You can’t take Log backup for Master Database. Only Full backups are allowed on Master Database.

Model Database – Well, it’s your call! if you are using it, you can take backups..if not you can ignore. I personally feel taking Model backups is just a safe bet and the size of the Model Database backup will be usually in few Kilo Bytes, which is almost negligible!

MSDB Database – You’ve to take regular Backups. You can perform all types of Backups on MSDB(Full/Diff/Log). Plan accordingly!

TEMPDB – You can’t Backup TEMPDB Database. As i mentioned above, SQL will recreate tempdb on every service restart.

Resource Database –  This should be our part of Backup Strategy(whenever possible). At least once a week or bi-weekly! You should not ignore backing up this especially when you are patching/upgrading your SQL Server. So..where should we locate this hidden Database?? The Answer is in your SQL Server Program Files(location where you installed your SQL Server Binaries) starting SQL Server 2008, not in the Data file(s) Directory anymore”.  You can see in the Below Screenshot for the default location of this Database files on one of my Instance

So, in my case the path is “C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\Binn”.
For backing up this Database, all we need to do is, Just copy these two files and place them in your secure backups location. So…We can’t do a SQL Backup on this Database, the trick is to copy the files at File System Level.

That’s pretty much it Guys!! Hope this is useful and informational…