My Favorite Blogs

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... :)