You are the SQL administrator for your company. A SQL Server 2008 instance named SQL_Prod resides on acomputer named Server1.
SQL_Prod contains three databases named Accounting, Research, and HR .
You need to obtain a list of transactions that are occurring in the HR database.
The information must include thetransaction ID, transaction state, and transaction type. What should you do?
A.
Query the sys.dm_tran_database_transactions dynamic management view in the Accounting database.
B.
Query the sys.dm_tran_database_transactions dynamic management view in the HR database.
C.
Query the sys.dm_tran_database_transactions dynamic management view in the Research database.
D.
Query the sys.dm_tran_database_transactions dynamic management view in the SQL_Prod instance.
Explanation:
You should query the sys.dm_tran_database_transactions dynamic management view in the HR database.This dynamic management view (DMV) returns the transaction ID, database ID, transaction start time, transactiontype, transaction state, and other information on the transactions for a database. You should not query the sys.dm_tran_database_transactions
DMV in the Accounting or Research database.You need to obtain the information for the HR database, not for the Accounting or Research database. You should not query the
sys.dm_tran_database_transactions DMV in the SQL_Prod instance. The sys.dm_tran_database_transactions DMV returns values for a particular database, not for all databases on theinstance. DMVs are either used against a server or database. Using server-scoped DMVs requires the VIEW SERVERSTATE permission. Using database-scoped DMVs requires the
VIEW DATABASE STATE permission.Objective:
Optimizing SQL Server PerformanceSub-Objective:
Collect performance data by using Dynamic Management Views (DMVs).References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online >Database Engine > Technical Reference >Transact-SQLReference > System Views (Transact-SQL) > Dynamic Management Views and Functions (Transact-SQL)>Transaction Related Dynamic Management Views and Functions (Transact-SQL) >sys.dm_tran_database_transactions (Transact-SQL) TechNet > TechNet Library > Server Products and Technologies > SQL Server >SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > System Views (Transact-SQL) > Dynamic Management Views and Functions (Transact-SQL)