SQL Server scheduler/worker is dsigned such that they are Cooperative and the scheduler expects that the workers yield cooperative. However, the API calls such as External modules, Extended procedures etc. may never yield and as a result the worker will...

Indexed views can cause deadlock when two or more of the participating tables (in the indexed view) is updated/inserted/deleted from two or more sessions in parallel such that they block each other causing a deadlock scenario. The deadlock scenario...

Refer the link for SQL Server 2005 features supported by each of the edtiion http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx For SQL Server 2008 features supported by each of the edtiion http://msdn.microsoft.com/en-us...

declare @xactid bigint declare @transactionid nvarchar ( 28 ) select @xactid = transaction_id from sys . dm_tran_current_transaction select @transactionid = [transaction id] from fn_dblog (null,null) where [Xact ID] = @xactid select * from...

select database_id ,object_id ,index_id ,partition_number ,leaf_insert_count ,leaf_delete_count ,leaf_update_count ,leaf_ghost_count ,nonleaf_insert_count ,nonleaf_delete_count ,nonleaf_update_count ,leaf_allocation_count...

SELECT t . [text] AS [Adhoc Batch or Object Call] , SUBSTRING ( t . [text] , ( qs . [statement_start_offset] / 2 ) + 1 , (( CASE qs . [statement_end_offset] WHEN - 1 THEN DATALENGTH ( t . [text] ) ELSE qs . [statement_end_offset] END - qs...

USE master SELECT a . [value] AS [dbid] , ISNULL ( DB_NAME ( CONVERT ( INT , a . [value] )), 'Resource' ) AS [DB Name] , SUM ( qs . [execution_count] ) AS [Counts] , SUM ( qs . [total_worker_time] ) / 1000 AS [Total Worker Time (mSecs)] ...

SQL Server 2005 SP2 has an issue with modifying Agent Job's through Management Studio if the @@servername exceeds 30characters For more info on the issue refer http://support.microsoft.com/kb/940269 If you do not wish to apply a hotfix or upgrade...

SQL Server 2005 and later versions expect the service accounts be changed using Configuration manager and not through Services.msc The configuration manager does perform other activities such as adding the service accounts to the Groups and this way...

To be able to view the SQL Server Resource Database A) SQL Server must be started in a Single user Mode Start with -m flag in configuration manager Even if the Server is started in Single user mode, the database will still not be visible in sys...

SQL Server accuracy of milliseconds is 1/300 th of a second and the possible values are .000, .003 and .007. Any datetime value in SQL Server with milliseconds will be rounded to one the nearest of these three values. The table lists all possible last...

The steps below is the logical flow of how a SQL statement is processed A) Cross Join the tables. If the query involves more than a table, the first two tables are cross-joined and the result set is then joined with the third table and so on and...