Couple of weeks ago I was analyzing a server for space and noticed SSISDB database was abnormally huge (this Instance was running just a handful of packages). I noticed couple of internal schema tables in SSISDB were huge (with some hundreds of millions of rows), well that’s not right. There should be SSIS Server maintenance job which SQL server creates to purge older entries based on the retention settings right? My immediate action was to check the retention period set and what’s the status of the job. As I suspected, the job was failing (looks like this has been failing since ages) with below error.
The job failed. The Job was invoked by Schedule 9 (SSISDB Scheduler). The last step to run was step 1 (SSIS Server Operation Records Maintenance).
Execute as Login failed for the requested login ‘##MS_SSISServerCleanupJobLogin##’
Next step is to investigate whats up with the user account which the job is complaining about and I noticed ‘##MS_SSISServerCleanupJobUser##’ is orphaned in SSIS database as shown below.
Compare with how it looks on an Instance where this Job is running successfully.
Well, with this the fix is pretty evident, Map the poor fella to the corresponding login (Syntax shown below).
Once this has been done, I kicked off the job and it ran successfully (It took almost 2 hours to finish – had to purge few millions of records) and the subsequent scheduled runs were quick (Just few seconds as expected)
This morning one of our developers contacted me regarding this error message he got when he tried to design a table from his SSMS.
Also, he reported that his SSMS goes to “Not Responding” state when he tries to edit rows using his SSMS. So…What’s happening?
He was trying to edit a table within a database which is running on SQL Server 2014 version using his SSMS 2012. Now, all he needs is upgrading his client tools to 2014. Remember, you can manage/manipulate older versions of your SQL Servers using your 2014 SSMS, However be prepared to face these annoying issues if you are trying the other way.
Happy weekend folks!
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 🙂
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 🙂
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 🙂
Let me share with you guys, an Error Message what I’ve encountered yesterday. Well, this is not a very detailed post but I hope this will help what to check for when you encounter this Error/Warning Message.
Okay, I was validating a brand new SQL Server Clustered Instance(two node) and I tried to failover the Instance to the other node. But when I try to initiate a failover, I got this Message saying “the operation failed because either the specified cluster node is not the owner of the group, or the node is not a possible owner of the group“.
What’s the Issue: The 2nd Node was not added into this Clustered Instance.(Add Node to Cluster piece was missed by whoever Installed this SQL Server Cluster)
How to Confirm?
Go to the 2nd node and look for SQL Server Binaries for respective Instance. Also you should be seeing SQL Server Instance Being Installed but in Offline Status(assuming this is your Secondary Node at this particular point of time) from your SQL Server Config Manager Oooor you can simply Open Failover Cluster Manager and check for Possible Owners(Not preferred Owners) for this Instance.
Once he added the missing node to the SQL Cluster, everything was back in business as expected.
I know it’s a Quick and a Dirty Post, But I hope this helps!
In this Blog Post, let us see how to fix this Weird error message you might get while Installing SQL Server 2012 on top of Windows Server 2012. I already mentioned about this in the post where I showed how to Install SQL Server 2012 on Win Server 2012, but wanted to come up with a separate post just for this Error because it’s so annoying and frustrating when you see this. Trust me guys…This Error needs special Attention 🙂
Okay, Let’s say you are all ready for deploying SQL Server 2012 on top of Windows Server 2012 as your boss is pushing you towards SQL Server 2012(Which is awesome :)). You started your Installation and somewhere in the middle of your Installation Process, you get this annoying message that Installation Failed. Well, you are on Windows server 2012 which comes with brand new .Net Framework and you are not expecting any framework Issues(Initial setup Checklist gives no Errors on this). So what is it missing? The Answer is “.NET 3.51 Payload“. Yes, we must manually enable .NET 3.51 Payload in Windows Server 2012.
Look at the below Screenshot carefully at the Bottom left where it is asking for .Net Framework 3.5 to be enabled.
So, now you know why we need to pay attention while Installing SQL Server 😀
Anyways…..For this you need to have Windows media handy or should be connected to Internet, OR you can enable this automatically by enabling Remote Management which you might not do on your Production Boxes.
So, How to Fix this Annoying thing? Yes, you are correct if you said PowerShell (Few of you might thought about DISM or Just GUI for adding this feature as you used to do in Win Server 2008R2….YMMV!)
Note: Even the Error Message says, go and enable this feature using Server Manager GUI, it might fail!!!….Avoid GUI for fixing this. Instead use awesome PowerShell guys. I’ll show you powershell way of doing this…
1. Insert your Windows Media in your DVD Drive(in My case it’s D:)
2. Open Powershell as Admin and type “Install-WindowsFeature Net-Framework-Core -Source D:\Sources\sxs” without double Quotes and hit enter.
Look for Exit Code “Success”! That’s it Guys…The Rest should be Flawless!