My Favorite Blogs

Friday, June 15, 2012

Cannot find server certificate with thumbprint Errors

When I'm trying to setup log shipping for a database, I got the below certificate error and I understood that the database got TDE enabled and encrypted with Master key (private key). After struggled for a while and got the steps to fix it.

So before restore that database on the secondary database backup the certificate with master key (private key) and restore it on the secondary  server. Without certificate with Master key on any server and if you try to restore the TDE enabled database you will encounter the below errors.

Executed as user: Domain\ServiceAccount. Cannot find server certificate with thumbprint '0x62A3B87C122C170DFEEBFC6AFCB14B4E5BB5543B'. [SQLSTATE 42000] (Error 33111)  RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

Backup the certificate with master key on the Primary Server
BACKUP CERTIFICATE  [EncryptionCertificate] 
TO FILE = 'Certificate File path'
WITH PRIVATE KEY (FILE = 'Master Key File path.dat', ENCRYPTION BY PASSWORD = 'password')

Restore the certificate with master key password on the Secondary Server
CREATE CERTIFICATE [EncryptionCertificate]
FROM FILE='Certificate File path'
WITH PRIVATE KEY ( FILE  'Master Key File path.dat' , DECRYPTION BY PASSWORD = 'password')

This will resolve the issue and can restore the database with out any errors.

Thursday, May 3, 2012


This article talks about the five important SQL metrics when you need to help in solving performance problems.

Saturday, February 4, 2012

In SQL Management Studio resgiter multiple servers using Powershell

Recently our team builted around 60+ DR servers in our new datacenter and I thought to register them in SQL management studio, of course it will take some time to register them all together and thought that if a script does the same that would be wonderful.

After searching the indernet for sometime I found a peice of code and tweaked it to register a server and getting an input parameter from a text file.

Here is the code.
param (
 [string] $filename
 add-pssnapin SqlServerProviderSnapin100
add-pssnapin SqlServerCmdletSnapin100
  $readbuffer=get-content -path $filename
 foreach ($line in $readbuffer)
write-output "$i : $line"
sl "SQLSERVER:\SQLRegistration\Database Engine Server Group\SQLDBOps" ;
##remove-Item $(Encode-Sqlname "$line") -Force
new-Item $(Encode-Sqlname "$line") -itemtype registration -Value “server=$line;integrated security=true”;
remove-pssnapin SqlServerProviderSnapin100
remove-pssnapin SqlServerCmdletSnapin100

In the path name I’ve specified \SQLDBOps is which I’ve created under Registered ServersàDatabase EngineàLocal Server Groups and the \SQLRegistration\Database Engine Server Group is the default existing Local Servers Groups.
Code snap

Save the above script in a poweshell file as RegisterServer.ps1 and save your SQL instance names in a txt file as below.

Open the Powershell command window and navigate to the path where the script and text file exists and execute the file like  .\RegisterServer.ps1 SQLSrvrs.txt, the servers will be registered under your group as below.

Saturday, January 7, 2012

Transactional Log Full on Mirrored with Log shipped database

Today I worked on a Transactional log full issue on one of the critical production database server, the database involved in this issue is configured both Mirroring and Log shipping.  From the mirrored database a scheduled job generating snapshot for every 30 mins.  The production database is involved high volume of ecommerce OLTP, everything were working fine till midnight and after some changes happened in the front end database started receiving huge volume of data and the Tlog backup size turned to 20GB+ from 500MB normal value, this continued for 3 hours and the logs have been reduced and back to normal but the mirroring didn’t caught up so fast it was crawling till the morning 7.30AM and went into disconnected state.

The Tlog full issue started at 1.00AM and my fellow DBA assumed that issue was causing by a trim job which runs every 8 hours and the 1AM schedule usually runs more than 6hrs, but that was not the true cause of the issue, it was mirroring, the mirroring was absolutely slow, since the transactions were not transferred to the mirrored database, the unsent logs staying in the principal and waiting to be sent, of course this is causing not to flush the transactional logs though the log shipping Tlog backup job running every 5 minutes.
Finally figured out the issue, since the mirroring went into disconnected state I ended up to break the mirroring completely and changed the database recovery model from FULL to SIMPLE, which brought the Tlog usage 99% to less than 1% drastically. Then I shrunk the log to required size.

Once all back to normal I setup the mirroring and log shipping once again... oh yeah a day work for a big database... :)

When I did the investigation,  used the sp_dbmmonitorresults  stored procedure and dbm_monitor_data system table on msdb database on the principal database instance.

As per the below table, the mirroring was fine till 7.32AM and it broken 7.33AM, if you see the unsent_log column value in the table it started piling up.

sp_dbmmonitorresults dbname, 4, 0;

Mirroring the following table also has almost same but little more, you can see the log_flush_rate and send_queue_size columns started increasing from 7.33AM.

SELECT * FROM dbm_monitor_data
where database_id=5

Thursday, January 5, 2012

Msg 15110, Level 16, State 1, Line 1 The proposed new database owner is already a user or aliased in the database

              Today I got an user request to change  database owner for couple of databases on a lab environment. The user tried to change the owner by himself  but he got "Msg 15110, Level 16, State 1, Line 1" error, because the user name is already exists in the database which is erroring out. I looked into that and tried to change the db owner and got the same error as below.

-- Step 1
sp_changedbowner 'SQL\DBOPS'
Msg 15110, Level 16, State 1, Line 1
The proposed new database owner is already a user or aliased in the database.

 Based on the above error I decided to  drop the user from the db but I got different error now as below.

drop user [SQL\DBOPS]
Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.

 Based So I checked that the user owns which schema on the db using the below statement and found that the user have a schema with the same name which is blocking to drop the user.

-- Step 2
select * from information_schema.SCHEMATA
where schema_owner ='SQL\DBOPS'


In order to drop the schema, first I've to remove/change the schema owner  to something else, so I've to  run the below statement, the below statement change the schema owner to dbo from SQL\DBOPS user.

--Step 3
Alter authorization on SCHEMA:: [SQL\DBOPS]  to dbo
Command(s) completed successfully.

 Run the below SELECT statement again to make sure no other schemas owned by the user.  
--Step 4
select * from information_schema.schemata
where schema_owner ='SQL\DBOPS'
--Result empty

Now I'm good to drop the user and change the owner of the db. 
--Step 5
drop user [SQL\DBOPS]
Command(s) completed successfully.

--Step 6
sp_changedbowner 'SQL\DBOPS'
Command(s) completed successfully.

Issue resolved now... :)