MSSQLTREK

How to boot directly into your regular desktop Screen in Windows 8.1?

With the new windows 8.1, it is very simple to bypass the new start(Live Tiles) Screen if you want to and directly boot into regular Desktop mode. Let me show you how…

Nope! That’s not a setting in control Panel and not even in your task manager….! Once you login to your windows machine, switch to your legacy Desktop screen and right click on task bar and go to Properties and navigate to “Navigation” tab. Now at the bottom section(Start Screen) select “When I sign in or close all apps on a screen, go to desktop instead of start” as shown below and click Apply->ok. That’s it…You are good to go 🙂

Very Simple huh…!

The server principal already exists – Msg 15025, Level 16, State 2, Line 1

It’s friday and it’s dark and drizzling outside which usually makes me lazy..,:) But I got this one ticket from one of our customers requesting to create a Login which made me active :D. Well, thought creating a login is just a basic routine security request, Went ahead and tried creating the login from SSMS. It failed…saying “The server principal already exists”

Well, I thought the login already exists and verified from SSMS( I didn’t found one…Hmmm Interesting). Assuming might be a bug in SSMS, i tired using T-SQL, same message again…as you can see below.

Basically, I am not seeing the login(Server Principal) but SQL Server says it already has one!!!…To double check I queried sys.server_principals and also used sp_helprevlogin to see if it can show something useful to me. But I had no luck again as you can see below…they are not giving me any useful results when I am querying using Login Name.

Now..It’s time to play with SIDs(SUSER_SID is super useful in this scenario). I queried SUSER_SID passing the login name which I am trying to create, Interestingly it returned a row as you can see below…

Right away I queried sys.server_principals again, but with SID(Use the SID which came from the above query) this time as opposed to Login Name. Results are shown below

Tadaaaaa…..There you go! It returned a different login name which already exsits on the SQL Server with the same SID of the new login which I’m trying to create.

So, I contacted customer saying ‘ Hey, Login A is conflicting with your login, do you happen to know what Login A is?’ I got a response, that was her Old Windows user account(her Lastname got changed recently)

Now all I’ve to do is drop her old login(Remove DB mapping as needed) and create new login, fix DB mapping and permissions. Hope this helps…Happy Friday folks 🙂

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…

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 🙂

The Network folder specified is currently mapped using a different user name and password

Today I was trying to map a network drive on my laptop and received a message saying “The Network folder specified is currently mapped using a different user name and password“. Well, I thought there is one and went into “My Computer”. interestingly….I don’t see it! Well, I rebooted the laptop hoping it would drop any hanging hidden cables. Unfortunately it didn’t do the trick.

BTW, My laptop already has 2 mapped drives(Y$ and Z$) and I’m trying to create a 3rd one pointing to another share on completely different file server.(See below screenshot)

Now, I tried issuing “NET USE” command to see if it shows anything useful. Surprisingly It said, I’ve 3 mapped drives(3rd one being the one which I’m trying to map without any Drive letter being assigned!!!). See below Screenshot…

As you can see there’s one more thing(well, without drive letter assignment) and that’s the exact share which I’m trying to create as a mapped drive on my laptop. Now I know the issue. All we need to do is to Delete that “Hanging from Nowhere” Mapped Drive using “NET USE” and retry to map it.

Syntax for deleting mapped drive using NET USE:

NET USE /DELETE \\My_ServerName\My_Sharename

Boom….It dissapeared as you can see below.

Notice in the above screenshot, that “magical and annoying” share got deleted succesfully and only Y$ and Z$ are present as expected.

Now, I was able to map that share to a new drive without any issues…Hope this helps if you get into same issue. Cheers 🙂

SIMPLE Recovery Model Database waiting on LOG BACKUP!!!!

Recently I’ve encountered a strange issue with one of our databases. This is a SQL Server 2012 Instance(SP1). This is our staging environment and was hosting many databases(Few in SIMPLE and few in FULL Recovery Model).

Note: The Recovery Model for “MODEL” database on this Instance was “SIMPLE”. We kept this way, as we don’t care about Log Backups/Point In time recovery for our Stage Databases.

Issue: Developer complained about error he got with T-Log ran out of space. Well, initially i thought, some one did refreshed this database recently from Production and forgot to chage the database rec model from FULL to SIMPLE. I looked at rec model, It was set to SIMPLE and was never refreshed. Well, now i was almost sure, this is becuase of some active transaction sitting in the log and not letting SQL Server to truncate the log with CHECKPOINTs…

You know what, I was wrong again! When I queried sys.databases for Log_reuse_wait_Desc, I found very interesting and strange behaviour of SQL Server. It was waiting for Log backup to be taken on the database(But, this database is in SIMPLE Recovery Mode) See below for what Am talking about!

Cause: This is a BUG in SQL Server 2012 which got fixed in SP1 CU4.( http://support.microsoft.com/kb/2833645)

Bug Number:

1254695

2830400

(http://support.microsoft.com/kb/2830400/ )

FIX: Database does not follow simple recovery model behavior in SQL Server 2012 after you set the recovery model of the “model” database to “Simple”

Action: Patch(SP1 CU4) the 2012 SQL Server ASAP. (Btw, CU5 for SP1 is out as well).

Quick Fix: I swapped database recovery model to FULL and took a Full backup and switched it back to SIMPLE recovery model and it was back to normal(Also, I changed the Recovery model for MODEL database to FULL on this Instance till we patched this Server).

Hope this helps if you encounter this strangeeeee Issue!