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

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'

CATALOG_NAME	SCHEMA_NAME	SCHEMA_OWNER	DEFAULT_CHARACTER_SET_CATALOG	DEFAULT_CHARACTER_SET_SCHEMA	DEFAULT_CHARACTER_SET_NAME
DBOps_UsageAndHealthDB	SQL\DBOPS	SQL\DBOPS	NULL	NULL	iso_1

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.

--succeeded

Issue resolved now... :)

Msg 7391, Level 16, State 2, Line 8

Today I worked with a developer who was trying to query some data from a remote instance and inserting into a local instance, he told me that he created the linked server locally for the abc-sqlops01 instance and made sure that all fine. And also he specified that the test connection of the linked server was succeeded and simple SELECT statement is working fine but when he tried to run a stored procedure he is getting the errors as bellow.

Executed statement:

INSERT INTO datalist

EXEC [abc-sqlops01].Updatelist.dbo.[GetUpdatelist] 'opstestdata'

Error:

OLE DB provider "SQLNCLI" for linked server "abc-sqlops01" returned message "The partner 

transaction manager has disabled its support for remote/network transactions.".

Msg 7391, Level 16, State 2, Line 8

The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "abc-sqlops01" was unable to begin a distributed transaction.

Work Around:

 After digging though found that this sproc is creating a temp table on the linked server and inserting data on it and he is inserting that data locally. This action is validated by MSDTC security settings, which I found not set.  Once it fixed the stored procedure executed without any errors.

Start àAdministrative Tools àComponent Services

This will open the Component Services console

Expand the Component Services àDistributed Transaction Coordinator

                Right click on the Local DTC àProperties

                On the Local DTC Properties window, click on the Security Tab

                                Check Network DTC Access under Security Settings

                                In the Transaction Manager Communication box, Check Allow Inbound and Allow Outbound  (or check either one based on your business requirement)  and check the required Authentication method as well as required. In my case I checked “No Authentication Required”.

Refer below screen shot for reference.

Backup and Restore completion progress check on SQL 2005 and above

It is easy to check the percentage of  completion of backup or restore operations on SQL 2005 or above versions where as in the older versions we’ve to use STATS[=Percentage] to check. In the newer versions MS introduced DMVs to monitor the progress of a task as well as numerous other DMVs to check various SQL activities.  Here sys.dm_exec_requests can be used to check the percentage completion as bellow.

SELECT    percent_complete, start_time,

DATEADD(ms,estimated_completion_time,GETDATE()) As EstimatedCompletionTime

,DATEDIFF(hh,GETDATE(),DATEADD(ms,estimated_completion_time,GETDATE())) HoursToGo

,datediff(hh,Start_time,GETDATE()) TotalHoursCompleted

,command, b.name

FROM sys.dm_exec_requests a

INNER JOIN sys.databases b ON a.database_id = b.database_id where session_id=88

Since the db size is small the HoursToGo and TotalHoursCompleted columns are 0 else you get in hours. 

The same statement can be used to check the below tasks as well.

1.      DBCC CHECKDB

2.      DBCC CHECKTABLE

3.      DBCC SHRINKDB

4.      DBCC SHRINKFILES

5.      DBCC INDEXDEFRAG

6.      ALTER INDEX REORGANIZE

7.      ROLLBACK

Error when the database snapshot refreshing [Error: 913, Severity: 16, State: 1]

Today on one of our SQL Instance I observed that the below event was occurring last couple of days and I’ve been asked to look into it. I found that there is pattern in the event occurrence, it happened every 5minutes continuously. I started the sql trace from my jump box and trapped logs for 30 minutes and found that on the instance there is a job which is refreshing a database snapshot every 5 minutes and the same time an user sproc hits the same snapshot and trying to pull some data which causing to raise this event.


The snapshot job scheduled to run every 00.05.00.000th minute and the user calling the sproc on the snapshot at 00.05.01.000th minute, there is just 1 second difference but the job takes 30 seconds to complete and the database snapshot is inaccessible state at that time when the user trying to access. That’s when the below event occurs.

SQL Events

2011-02-05 09:25:02.250 spid59 Error: 913, Severity: 16, State: 1.

2011-02-05 09:25:02.250 spid59 Could not find database ID 8. Database may not be activated yet or may be in transition. Reissue the query once the database is available. If you do not think this error is due to a database that is transitioning its state and this error continues to occur

To avoid these logs in the SQL errorlog either I’ve to request the user to change his job schedule or I can change the snapshot refresh job schedule. In this case I changed in the snapshot refresh job schedule to start 40 seconds after so the snapshot can be accessed by the user’s sproc and after refresh job will start.

Now the events completely stopped occurring.

SQL Server Process completion in percentage in SQL Server 2005 and above versions

Checking percentage completion of backup or restore tasks in SQL 2000 and earlier versions are required to use monitoring option BACKUP DATABASE ... WITH STATS [= percentage]. But in SQL 2005 and later versions one of the dynamic management views (DMVs) sys.dm_exec_requests can be used to check the percentage of completion of a task, not only backup and restore, this dmv helps to troubleshoot performance issues as well but here I’m writing only how to ensure how much percentage completed a specific task.


I’ve used the below query to check how much percentage the database backup completed also calculated the estimated and elapsed time in minutes. The default value for estimated_completion_time and total_elapsed_time are in milliseconds.

SQL Statement

SELECT percent_complete , (estimated_completion_time/1000)/60 Estimated_completion_time_Mins , (total_elapsed_time/1000)/60 Total_Elapsed_Time_Mins ,DB_NAME(Database_id) DBName ,* FROM sys.dm_exec_requests WHERE session_id=3576


The above example query result set is below,  BACKUP DATABASE has been completed 66.56% and the current estimatated completion time is 6 mins (actual value is in milliseconds) from now and Total elapsed time is 12mins .

The above SQL statements can be used to check the percentage completion for the following tasks as well.


1. BACKUP and RESTORE
2. DBCC CHECKDB and CHECKTABLE
3. DBCC SHRINKDB and SHRINKFILES
4. DBCC INDEXDEFRAG
5. ALTER INDEX REORGANIZE
6. ROLLBACK

A network-related or instance-specific error occurred while establishing a connection to SQL Server

Last week I was installing SQL Server 2008 R2 on a 64 bit Windows 2008 box, after the installation I suppose to do a post installation check that includes connectivity test both local and remotely. I ensured that the SQL services are running under expected network domain account. When I tried to connect the SQL instance remotely the SQL kept throwing connection error and I couldn’t get into the instance and the below error popped up.


Error Thrown

TITLE: Connect to Server
------------------------------
Cannot connect to SQL2008INS01.
------------------------------

ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10060&LinkId=20476

-----------------------------
BUTTONS:

OK
------------------------------

After reading some knowledge bases and forums I understood the follows. In Windows Server 2008 and Vista the default dynamic port range for outgoing connections has been increased, the default start port is 49152 and end port is 65535 where as the earlier versions has between 1025 through 5000. By default the SQL Server establish it connections through TCP port 1433 and the windows firewall will accept connections.

In windows server 2008 the firewall is on and blocking all remote connections by default. This ensures that Windows 2008 does not leave the TCP port open by default not the SQL 2008 installation. We have to create a firewall rule to allow the server to send and receive traffic from SQL Server.

So make sure to add this step in our SQL 2008 post installation step on Windows Server 2008 to open the TCP port 1433.

By default, the Windows Firewall blocks all incoming unsolicited network traffics, we need to configure firewall rules for program, port or system/app services to connect a server remotely.

Following are steps to configure firewall rules for TCP port 1433 to accept SQL Server connection request remotely.

Step 1: Go to Start Administrative Tools  click on Windows Firewall and Advanced Security, on Windows Firewall and Advanced Security page, right click on Inbound Rules and click on New Rule(Refer Fig1).

Fig 1

 
Step 2: The New Rule will navigate to Rule Type window, here you can chose either program, port , predefined or a custom rules. Make sure you have chosen Port and click Next(Refer Fig2).



Fig2


Step 3: Now you are on the Protocol and Ports settings window, make sure the rules applies to TCP and type 1433 on Specific local ports and click Next(Refer Fig 3).

Fig 3

 Step 4: After specifying the port number 1433, you have to specify the action to be taken by Windows Firewall when it receives a connection that matches with the specified conditions, so choose “Allow the Connections” and click Next to move further (Refer Fig 4).

Fig 4

Step 5: Now choose the profile to apply the rule. Since most of the business allows their employees to work remotely and these three profiles will be used most common. Choose all three and move next (Refer Fig 5)

Fig 5

Step 6: Finally specify the name of the Firewall rule and Finish it(Refer Fig6).

Fig 6

Reference

1) The default dynamic port range for TCP/IP has changed in Windows Vista and in Windows Server 2008

2) Windows Server 2008 Default firewall Settings

3) How to: Configure a Windows Firewall for Database Engine Access

4) How do I open the firewall port for SQL Server on Windows Server 2008 programmatically?