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