Home » Posts tagged 'SQL 2008 R2'

Tag Archives: SQL 2008 R2

Database Snapshot failed due to disk space issue


Database snapshot is really a useful feature. As we know, Snapshot database file will keep increasing parallel to the DML operations on database. But if disk containing snapshot file run out of space then what will be the impact on new transactions, database & snapshot?

Microsoft design snapshot in very well manner. If Snapshot failed to records the data changed by DML transaction due to disk space issue then your snapshot will become unusable & reaches to suspect mode and on the other hand your actual database will keep running un-impacted.

Snapshot database cannot be recovered from suspect state so you need to drop & recreate the snapshot database.

1) While running below transaction, We got error that unable to write on snapshot file due to disk space issue.

2) We check data in table to check id transaction completed successfully or failed due to snapshot issue. We found transaction is completed & database is running fine.

3) After that, When we try to select snapshot database, We got error that database is suspect mode.

4) We also try to bring snapshot database in emergency mode to see if we can troubleshoot it but We got error that this operation can not be performed on snapshot database.

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

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

Cumulative Update – 9 for SQL Server 2008 R2 Service Pack 2 Is Now Available !


The 9th cumulative update release for SQL Server 2008 R2 Service Pack 2 is now available for download at the Microsoft Support site. Cumulative Update 9 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 CU8 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 CU9 of SQL Server 2008 R2 SP2

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

Previous Cumulative Update KB Articles:

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

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

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

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

DMV-15 : Pending I/O requests……..sys.dm_io_pending_io_requests


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

Returns a row for each pending I/O request in SQL Server.

It’s a very simple DMV used to see all pending I/O requests & there description.

You can combine this DMV with DMF – sys.dm_io_virtual_file_stats to see I/O pending requests with database files. You should run this query multiple times to check if the same files or drive letters consistently coming up on the top. If this is the case that means you facing I/O bottlenecks for that file or drive letter.

Query 1 : Details of I/O pending requests against each DB file

SELECT

DB_NAME(MF.DATABASE_ID) AS [DATABASE],

MF.PHYSICAL_NAME,

IPIR.IO_TYPE,

SUM(IPIR.IO_PENDING) TOTAL_PENDING_IO,

SUM(IPIR.IO_PENDING_MS_TICKS) TOTAL_PENDING_MS_TICKS,

SUM(VFS.NUM_OF_READS) TOTAL_READS,

SUM(VFS.NUM_OF_WRITES) TOTAL_WRITES

FROM

SYS.DM_IO_PENDING_IO_REQUESTS AS IPIR

INNER JOIN

SYS.DM_IO_VIRTUAL_FILE_STATS(NULL,NULL) AS VFS

ON IPIR.IO_HANDLE = VFS.FILE_HANDLE

INNER JOIN

SYS.MASTER_FILES AS MF

ON VFS.DATABASE_ID = VFS.DATABASE_ID

AND VFS.FILE_ID = MF.FILE_ID

GROUP BY MF.DATABASE_ID, MF.PHYSICAL_NAME, IPIR.IO_TYPE

ORDER BY SUM(IPIR.IO_PENDING)

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

How to Check SQL Server Evaluation Version Expire Date ?


SQL Server Evaluation Edition is available free of cost. It’s a trial version & valid for limited time of period. The expiration date is always 180 days from the initial installation date.

The following query will give you the expiration date of evaluation instance :

SELECT

@@SERVERNAME SERVERNAME,

CREATE_DATE ‘INSTALALTIONDATE’,

SERVERPROPERTY(‘EDITION’) ‘Version’,

DATEADD(DD, 180, CREATE_DATE) AS ‘EXPIRY DATE’

FROM SYS.SERVER_PRINCIPALS

WHERE SID = 0X010100000000000512000000

SID 0X010100000000000512000000 is associated with login “NT AUTHORITYSYSTEM” & which is created at the time of installation only.

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

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

DMV-14 : Which Table has Max. rows & Size……..sys.dm_db_partition_stats


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

Returns page and row-count information for every partition in the current database.

This DMV gives useful information about rowcount, free & reserved space of each table in database. It can be used for analysis purpose while adding new indexes, doing index Maintenance or checking the biggest table of database.

Query 1 : Details of table with row count & space used

SELECT

SS.NAME AS SCHEMANAME,

SO.NAME AS OBJECTNAME,

SI.NAME AS INDEXNAME,

PS.RESERVED_PAGE_COUNT*8 TOTAL_SPACE_CONSUMED_KB,

PS.USED_PAGE_COUNT*8 USED_SPACE_KB,

(PS.RESERVED_PAGE_COUNT – PS.USED_PAGE_COUNT)*8 FREE_SPACE_KB,

CASE

WHEN PS.INDEX_ID IN (0,1) THEN PS.ROW_COUNT

ELSE NULL

END AS ROW_COUNT

FROM

SYS.DM_DB_PARTITION_STATS PS

INNER JOIN SYS.OBJECTS SO ON SO.OBJECT_ID = PS.OBJECT_ID

INNER JOIN SYS.SCHEMAS SS ON SS.SCHEMA_ID = SO.SCHEMA_ID

LEFT JOIN SYS.INDEXES SI ON SI.OBJECT_ID = PS.OBJECT_ID

AND SI.INDEX_ID = PS.INDEX_ID

WHERE

SO.IS_MS_SHIPPED = 0

Sample Result

Remarks

1. To use this DMV, User required VIEW DATABASE 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/)

Cumulative Update – 8 for SQL Server 2008 R2 Service Pack 2 Is Now Available !


The 8th cumulative update release for SQL Server 2008 R2 Service Pack 2 is now available for download at the Microsoft Support site. Cumulative Update 8 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 CU8 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#8 KB Article: http://support.microsoft.com/kb/2871401

Previous Cumulative Update KB Articles:

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

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

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

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

DMV-11 : T-Log space used by transaction……..sys.dm_tran_database_transactions


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 (http://mssqlfun.com/)

DMV-10 : Does my database contain edition specific features?……..sys.dm_db_persisted_sku_features


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…)

Follow

Get every new post delivered to your Inbox.

Join 130 other followers