MSSQLFUN

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

Returns information about transactions at the database level.

Some time , T-log space is hugely consumed by database & we are not aware that which transaction is culprit for this. This is useful DMV in such conditions to check T-log space used by each transaction on database.

Query 1 : Query to check Transaction log space used by each transaction will complete detail

SELECT

ST.SESSION_ID,

DT.TRANSACTION_ID,

DB_NAME(DT.DATABASE_ID) DB_NAME,

DATABASE_TRANSACTION_BEGIN_TIME TRANSACTION_BEGIN_TIME,

CASE DATABASE_TRANSACTION_TYPE

WHEN 1 THEN ‘READ/WRITE’

WHEN 2 THEN ‘READ ONLY’

WHEN 3 THEN ‘SYSTEM’ END AS TRANSACTION_TYPE,

CASE DATABASE_TRANSACTION_STATE

WHEN 1 THEN ‘NOT INITIALIZED’

WHEN 3 THEN ‘TRANSACTION NO LOG’

WHEN 4 THEN ‘TRANSACTION WITH LOG’

WHEN 5 THEN ‘TRANSACTION PREPARED’

WHEN 10 THEN ‘COMMITED’

WHEN 11 THEN ‘ROLLED BACK’

WHEN 12 THEN ‘COMMITED AND LOG GENERATED’ END AS TRANSACTION_STATE,

SP.HOSTNAME,

SP.LOGINAME,

SP.STATUS,

SP.LASTWAITTYPE,

SQLT.TEXT,

DATABASE_TRANSACTION_LOG_RECORD_COUNT LOG_RECORD_COUNT,

(DATABASE_TRANSACTION_LOG_BYTES_USED + DATABASE_TRANSACTION_LOG_BYTES_RESERVED )/1024 TOTAL_LOG_SPACE_USED_KB,

DATABASE_TRANSACTION_LOG_BYTES_USED LOG_BYTES_USED,

DATABASE_TRANSACTION_LOG_BYTES_RESERVED LOG_BYTES_RESERVED

FROM

SYS.DM_TRAN_DATABASE_TRANSACTIONS DT JOIN

SYS.DM_TRAN_SESSION_TRANSACTIONS ST

ON DT.TRANSACTION_ID=ST.TRANSACTION_ID

JOIN

SYS.SYSPROCESSES SP

ON SP.SPID = ST.SESSION_ID

CROSS APPLY

SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) SQLT

WHERE

DT.TRANSACTION_ID > 1000 AND ST.SESSION_ID >50

Sample Result

Remarks

1. Meaning of int value for database_transaction_type column

· 1 = Read/write transaction

· 2 = Read-only transaction

· 3 = System transaction

2. All possible state of transactions for database_transaction_state coulmn

· 1 = The transaction has not been initialized.

· 3 = The transaction has been initialized but has not generated any log records.

· 4 = The transaction has generated log records.

· 5 = The transaction has been prepared.

· 10 = The transaction has been committed.

· 11 = The transaction has been rolled back.

· 12 = The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted.

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

4. If column database_transaction_begin_time has NULL then transaction is read only otherwise read write.

5. 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 ()

sys.dm_db_persisted_sku_features (Introduced in SQL Server 2008) DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-in/library/cc280724.aspx

Some features of the SQL Server Database Engine change the way that Database Engine stores information in the database files. These features are restricted to specific editions of SQL Server. A database that contains these features cannot be moved to an edition of SQL Server that does not support them Use the sys.dm_db_persisted_sku_features dynamic management view to list all edition-specific features that are enabled in the current database.

There are four features that captured by this DMV :

· Data Compression

· Partitioning

· Transparent Data Encryption (TDE)

· Change Data Capture

DBAs can identify all edition specific features that are enabled within a user database by using the sys.dm_db_persisted_sku_features dynamic management view.

Query 1 : Check Edition Specific features of select database

SELECT

DB_NAME() DBNAME,

FEATURE_NAME

FROM SYS.DM_DB_PERSISTED_SKU_FEATURES

Remarks

1. To use this DMV, User re (more…)

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

Returns a row for all common language runtime (CLR) tasks that are currently running. A Transact-SQL batch that contains a reference to a CLR routine creates a separate task for execution of all the managed code in that batch. Multiple statements in the batch that require managed code execution use the same CLR task. The CLR task is responsible for maintaining objects and state pertaining to managed code execution, as well as the transitions between the instance of SQL Server and the common language runtime.

sys.dm_clr_tasks DMV is applicable to you if you have enabled the CLR on your SQL Server instance, and you are using at least one CLR assembly loaded in any of one user databases on your SQL Server instance.

You should look for rows having forced_yield_count column value above zero or that have a last_wait_type of SQLCLR_QUANTUM_PUNISHMENT. This will point that the task previously exceeded its allowed quantum & caused the SQL OS scheduler to intervene and reschedule it at the end of the queue. Value of Column forced_yield_count shows the number of time that this has happened.

If you noticed this, you should talk to your developer for this. This could cause issue for you SQL Server.

How to enable CLR?

EXEC SP_CONFIGURE ‘CLR ENABLED’,1

GO

RECONFIGURE

GO

How to disable CLR?

EXEC SP_CONFIGURE ‘CLR ENABLED’,0

GO

RECONFIGURE

GO

Query 1 : FIND LONG RUNNING SQL/CLR TASKS

SELECT

OS.TASK_ADDRESS,

OS.[STATE],

OS.LAST_WAIT_TYPE,

CLR.[STATE],

CLR.FORCED_YIELD_COUNT

FROM SYS.DM_OS_WORKERS AS OS

INNER JOIN SYS.DM_CLR_TASKS AS CLR

ON (OS.TASK_ADDRESS = CLR.SOS_TASK_ADDRESS)

WHERE CLR.[TYPE] = ‘E_TYPE_USER’;

Remarks

1. 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/)

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

Returns space usage information for each file in the database.

It’s most commonly used DMV to check total used & available free space in database. Before SQL Server 2012, it applicable only to the tempdb database

Key Columns :-

• database_id – identifies the database on basis on database id
• unallocated_extent_page_count – Total number of pages that are in unallocated extents (8 contiguous 8K pages) that are reserved in the file but not currently allocated to objects. Unused pages in used extents (having any active data) will not be included in this total.
• version_store_reserved_page_count – Number of pages reserved to support snapshot isolation transactions
• mixed_extent_page_count – Number of extents that have pages of multiple types (user objects, internal objects, version store, Index Allocation Map (IAM) pages, etc.)

Query 1 : Calculate total, used & unused in databases

SELECT

DB_NAME(SU.DATABASE_ID) DBNAME,

MF.PHYSICAL_NAME,

SU.ALLOCATED_EXTENT_PAGE_COUNT*8/1024 ALLOCATED_EXTENT_SIZE_MB,

SU.TOTAL_PAGE_COUNT*8/1024 TOTAL_SIZE_MB,

SU.UNALLOCATED_EXTENT_PAGE_COUNT*8/1024 UNALLOCATED_EXTENT_SIZE_MB

FROM

SYS.DM_DB_FILE_SPACE_USAGE SU

JOIN SYS.MASTER_FILES AS MF

ON MF.DATABASE_ID = SU.DATABASE_ID

AND MF.FILE_ID = SU.FILE_ID

Query 2 : Calculate Free space in TempDB

SELECT

UNALLOCATED_EXTENT_PAGE_COUNT/128 [FREESPCAE
(MB)]

FROM

SYS.DM_DB_FILE_SPACE_USAGE

Remarks

1. User required VIEW SERVER STATE permission on the server.

2. sys.dm_db_file_space_usage did not include the LDF file size whereas SP_Spaceused include LDF file size while calculating total database size.

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

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

The 7th cumulative update release for SQL Server 2012 RTM is now available. Cumulative Update 7 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 CU7 in test environment & then move to Production after satisfactory results.

KB Article For CU7 of SQL Server 2012

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

Previous Cumulative Update KB Articles of SQL Server 2012

· 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/)

The 6th cumulative update release for SQL Server 2008 R2 Service Pack 2 is now available for download at the Microsoft Support site. Cumulative Update 6 contains all the hotfixes released since the initial release of SQL Server 2008 R2 SP2.

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

To other, I suggest to wait for SP3 final release to deploy on your production environment, to have consolidate build.

KB Article For CU5 of SQL Server 2008 R2 SP2

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

Previous Cumulative Update KB Articles:

• CU#5 KB Article: http://support.microsoft.com/kb/2797460
• CU#4 KB Article: http://support.microsoft.com/kb/2777358
• CU#3 KB Article: http://support.microsoft.com/kb/2754552
• CU#2 KB Article: http://support.microsoft.com/kb/2740411
• CU#1 KB Article: http://support.microsoft.com/kb/2720425

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

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