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!!!…

1

 “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:(

2

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

3

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.

1

T-SQL:

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Tst_Restore_SS')
DROP DATABASE Tst_Restore_SS
GO
CREATE DATABASE Tst_Restore_SS ON (
NAME = Tst,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SREE\MSSQL\DATA\Tst_Restore_SS.ss')
AS SNAPSHOT OF Tst_Restore;

Now you can see I’ve a snapshot created.

2

See below for the Properties(files section).

3

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
GO
ALTER DATABASE Tst_Restore SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE Tst_Restore SET ONLINE
GO

-- Reverting Tst_Restore to Tst_Restore_SS

RESTORE DATABASE Tst_Restore
FROM
 DATABASE_SNAPSHOT = 'Tst_Restore_SS';
 GO

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”

Query:
SELECT feature_name FROM sys.dm_db_persisted_sku_features;
GO

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

3

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.

Demo:

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.

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

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!