Error message when you execute a linked server query in SQL Server: "Timeout Expired"

Hi Viewers, In our production environment today we received following error.

Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the provider because a resource limit was reached. [OLE/DB provider returned message: Timeout expired] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute returned 0x80040e31: Execution terminated by the provider because a resource limit was reached.].

Actually this error message received in one of the job history. Inside of the job is having the statements which will run the update or DML operation on one of the Linked server. There we got the error.

We tried a lot and finally we got the following KB article workarounds. It was very helpful.

Solution:
http://support.microsoft.com/en-us/kb/314530


-- By Shivasagar V

How to restore the missing Windows Installer cache files and resolve problems that occur during a SQL Server update

Today I have received this error while installing SQL service pack. This error usually comes due to missing windows installer cache files. Some of my windows team people are removed soome cache files due to space issue in C: drive. 

I have followed the workarounds which are there in below KB article. Now issue has been resolved. Thanks to microsoft KB article.

Solution: http://support.microsoft.com/en-us/kb/969052#Script

-- By Shivasagar V

Reducing or Minimizing Deadlocks

Anyway deadlocks cannot be avoid completely, following steps are useful to minimize the chance of generating a deadlock. If deadlocks are less automatically increase transaction throughput and reduce system overhead.
Small transactions are

• Rolled back, undoing all the work performed by the transaction.
• Resubmitted by applications because they were rolled back when deadlocked.

Steps to reducing deadlocks:

1. Access the objects in the same order
2. Avoid user interaction with in the transactions
3. Always keep transactions are short and in one batch
4. Better to use a lower isolation level.
5. Use snapshot isolation.
6. Use bound connections.
7. Better to use a row versioning-type isolation level
8. Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.

-- By Shivasagar V

Trace flag 1118

TRACE FLAG -1118:

Trace flag 1118 forcefully guides SQL server engine to allocates uniform extent instead of mixed page allocations. The trace flag is generally used to support in TEMPDB scalability by avoiding SGAM and other allocation contention points.

SQL Server 2008 has optimized mixed extent allocation behaviour. so that it is reducing the need for trace flag 1118 and the contention on SGAM(s).   The same logic was also added to SQL Server 2005 in cumulative update and KB article 936185.

If you have SQL Server 2008 or SQL Server 2005 and the fix applied and are still encountering TEMPDB contention consider trace flag 1118 for resolving the contention.


-- By Shivasagar V

Trace flags 1024 and 1222

Hi Viewers, this post is regards getting deadlock information/details into error logs.

Trace flag 1204
Returns the resources and types of locks participating in a deadlock and also the current command affected.
Scope: global only

Trace flag 1222
Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.
Scope: global only

--By Shivasagar V

DBCC CHECKDB

Hi viewers, today I am going to provide some clarification about DBCC CHECKDB.

Generally DBA's are using this command to check the database health check.

DBCC CHECKDB:
Runs DBCC CHECKALLOC on the database.
Runs DBCC CHECKTABLE on every table and view in the database.
Runs DBCC CHECKCATALOG on the database.
Validates the contents of every indexed view in the database.
Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
Validates the Service Broker data in the database.

REPAIR_ALLOW_DATA_LOSS:
Tries to repair all reported errors. These repairs can cause some data loss.

REPAIR_FAST:
Maintains syntax for backward compatibility only. No repair actions are performed.

REPAIR_REBUILD:
REPAIR_REBUILD does not repair errors involving FILESTREAM data

-- by Shivasagar V