Home » 2013 » August

Monthly Archives: August 2013

Cumulative Update – 9 for SQL Server 2012 RTM Is Now Available !


The 9th cumulative update release for SQL Server 2012 RTM is now available. Cumulative Update 9 contains all the hotfixes released since the initial release of SQL Server 2012 RTM.

Those who are facing severe issues with their environment, they can plan to test CU9 in test environment & then move to Production after satisfactory results.

KB Article For CU9 of SQL Server 2012

· CU#9 KB Article: http://support.microsoft.com/kb/2867319

Previous Cumulative Update KB Articles of SQL Server 2012

· CU#8 KB Article: http://support.microsoft.com/kb/2844205

· CU#7 KB Article: http://support.microsoft.com/kb/2823247

· CU#6 KB Article: http://support.microsoft.com/kb/2728897

· CU#5 KB Article: http://support.microsoft.com/kb/2777772

· CU#4 KB Article: http://support.microsoft.com/kb/2758687

· CU#3 KB Article: http://support.microsoft.com/kb/2723749

· CU#2 KB Article: http://support.microsoft.com/kb/2703275

· CU#1 KB Article: http://support.microsoft.com/kb/2679368

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Data Purity issue after upgrading SQL 2000 to later versions


In SQL Server versions prior to 2005, it was possible to have invalid data values into a database. That can lead to incorrect result or issue at time of query execution.

SQL Server 2005 brings new option to the DBCC CHECKDB and DBCC CHECKTABLE commands to solve this issue. “DATA_PURITY”, The new option to check for issues where column values are not valid or out-of-range. In SQL Server 2005 data purity check is enabled by default & performed each time DBCC CHECKDB runs on database. But this is not same for upgraded databases.

For upgraded databases, We need to run below DBCC command on database & In case DBCC CHECKDB with DATA_PURITY got completed successfully, a bit dbi_dbccFlags will be flipped from 0 to 2. Now from future onwards data purity is enabled by default for upgraded databases as well & it will be performed each time DBCC CHECKDB runs.

DBCC CHECKDB with DATA_PURITY

· How to check Data purity for your SQL instance ?

DBCC TRACEON (3604)

GO

CREATE TABLE #DBCC (

PARENTOBJECT VARCHAR(255),

[OBJECT] VARCHAR(255),

FIELD VARCHAR(255),

[VALUE] VARCHAR(255)

)

CREATE TABLE #DBCC2 (

DATABASENAME VARCHAR(255),

PARENTOBJECT VARCHAR(255),

[OBJECT] VARCHAR(255),

FIELD VARCHAR(255),

[VALUE] VARCHAR(255)

)

EXEC MASTER.DBO.SP_MSFOREACHDB

‘USE ? INSERT INTO #DBCC EXECUTE (”DBCC DBINFO WITH TABLERESULTS”);

INSERT INTO #DBCC2 SELECT ”?”, * FROM #DBCC;

DELETE FROM #DBCC’

SELECT DATABASENAME,

(SELECT VALUE FROM #DBCC2 WHERE DATABASENAME = A.DATABASENAME AND FIELD = ‘DBI_DBCCLASTKNOWNGOOD’) LASTGOODCHECKDBDATE,

(SELECT VALUE FROM #DBCC2 WHERE DATABASENAME = A.DATABASENAME AND FIELD = ‘DBI_CREATEVERSION’) DBCREATEVERSION,

(CASE VALUE

WHEN 0 THEN ‘DISABLED’

WHEN 2 THEN ‘ENABLED’

END) DATAPURITY

FROM #DBCC2 A

WHERE FIELD = ‘DBI_DBCCFLAGS’ AND

VALUE = 2 AND

DATABASENAME NOT IN (‘MASTER’,’MODEL’)

DROP TABLE #DBCC

DROP TABLE #DBCC2

GO

Sample Result :-

· Key Points :-

1) In SQL 2005 & above data purity is enabled by default that means dbi_dbccFlags flag value will be 2 only.

2) For Master & Model dbi_dbccFlags flag value will be 0 only

3) For DB upgraded from SQL 2000 or having dbi_createVersion value lower than 611 that means dbi_dbccFlags value will be 0 till the first time DBCC CHECKDB with data purity completed successfully

4) If dbi_dbccFlags flag value is 2 that means data purity is enabled by default

· Possible Error Message 2570 :- Refer :- http://support.microsoft.com/kb/923247

DBCC results for “Object1”.
Msg 2570, Level 16, State 2, Line 1

It’s always recommended to have look on data purity things after DB upgrade from prior versions of SQL Server 2005.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

DMV-13 : Finding locking & blocking……..sys.dm_tran_locks


sys.dm_tran_locks DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-us/library/ms190345.aspx

Returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted.

The columns in the result set are divided into two main groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request.

This DMV is very useful in helping to identify locking and blocking issues on your data­base instances.

Query 1 : Details of currently active locks

SELECT

CASE DTL.REQUEST_SESSION_ID

WHEN -2 THEN ‘ORPHANED DISTRIBUTED TRANSACTION’

WHEN -3 THEN ‘DEFERRED RECOVERY TRANSACTION’

ELSE DTL.REQUEST_SESSION_ID END AS SPID,

DB_NAME(DTL.RESOURCE_DATABASE_ID) AS DATABASENAME,

SO.NAME AS LOCKEDOBJECTNAME,

DTL.RESOURCE_TYPE AS LOCKEDRESOURCE,

DTL.REQUEST_MODE AS LOCKTYPE,

ST.TEXT AS SQLSTATEMENTTEXT,

ES.LOGIN_NAME AS LOGINNAME,

ES.HOST_NAME AS HOSTNAME,

CASE TST.IS_USER_TRANSACTION

WHEN 0 THEN ‘SYSTEM TRANSACTION’

WHEN 1 THEN ‘USER TRANSACTION’ END AS USER_OR_SYSTEM_TRANSACTION,

AT.NAME AS TRANSACTIONNAME,

DTL.REQUEST_STATUS

FROM

SYS.DM_TRAN_LOCKS DTL

JOIN SYS.PARTITIONS SP ON SP.HOBT_ID = DTL.RESOURCE_ASSOCIATED_ENTITY_ID

JOIN SYS.OBJECTS SO ON SO.OBJECT_ID = SP.OBJECT_ID

JOIN SYS.DM_EXEC_SESSIONS ES ON ES.SESSION_ID = DTL.REQUEST_SESSION_ID

JOIN SYS.DM_TRAN_SESSION_TRANSACTIONS TST ON ES.SESSION_ID = TST.SESSION_ID

JOIN SYS.DM_TRAN_ACTIVE_TRANSACTIONS AT ON TST.TRANSACTION_ID = AT.TRANSACTION_ID

JOIN SYS.DM_EXEC_CONNECTIONS EC ON EC.SESSION_ID = ES.SESSION_ID

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(EC.MOST_RECENT_SQL_HANDLE) AS ST

WHERE

RESOURCE_DATABASE_ID = DB_ID()

ORDER BY DTL.REQUEST_SESSION_ID

Sample Result

Remarks

1. Column request_session_id display the session ID owns the lock or raise request.

If value display is -2 indicates that the request belongs to an orphaned distributed transaction.

If value display is -3 indicates that the request belongs to a deferred recovery transaction, such as, a transaction for which a rollback has been deferred at recovery because the rollback could not be completed successfully.

2. Column request_owner_type details :-

· TRANSACTION = The request is owned by a transaction.

· CURSOR = The request is owned by a cursor.

· SESSION = The request is owned by a user session.

· SHARED_TRANSACTION_WORKSPACE = The request is owned by the shared part of the transaction workspace.

· EXCLUSIVE_TRANSACTION_WORKSPACE = The request is owned by the exclusive part of the transaction workspace.

· NOTIFICATION_OBJECT = The request is owned by an internal SQL Server component.

3. To use this DMV, User required VIEW SERVER STATE permission on the server.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

DMV-12 : Retaion in Session & Transaction……..sys.dm_tran_session_transactions


sys.dm_tran_session_transactions DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-us/library/ms188739.aspx

Returns correlation information for associated transactions and sessions.

This DMV helps in correlating the relation between session & transaction. We can get several details about transaction by correcting it will other DMVs & system catalogs.

Query 1 : Query to check Transaction & Session details

SELECT

ST.SESSION_ID,

ST.TRANSACTION_ID,

DB_NAME(SP.DBID) DB_NAME,

CASE IS_USER_TRANSACTION

WHEN 0 THEN ‘SYSTEM TRANSACTION’

WHEN 1 THEN ‘USER TRANSACTION’ END AS USER_OR_SYSTEM_TRANSACTION,

CASE IS_LOCAL

WHEN 0 THEN ‘DISTRIBUTED TRANSACTION’

WHEN 1 THEN ‘LOCAL TRANSACTION’ END AS TRANSACTION_ORIGIN,

SP.HOSTNAME,

SP.LOGINAME,

SP.STATUS,

SP.LASTWAITTYPE,

SQLT.TEXT

FROM

SYS.DM_TRAN_SESSION_TRANSACTIONS ST

JOIN

SYS.SYSPROCESSES SP

ON SP.SPID = ST.SESSION_ID

CROSS APPLY

SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) SQLT

Sample Result

Remarks

1. Meaning of int value for is_user_transaction column

a. 1 = The transaction was initiated by a user request.

b. 0 = System transaction.

2. All possible state of transactions for is_local coulmn

a. 1 = Local transaction.

b. 0 = Distributed transaction or an enlisted bound session transaction.

3. To use this DMV, User required VIEW SERVER STATE permission on the server.

4. TRANSACTION_ID > 1000 & SESSION_ID > 50 is used for user transactions & sessions.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)