You are the database administrator for your company. You manage all the SQL Server 2008 databases in yourcompany. The Prod_details database is the main database accessed by the company’s users. The head officereceives data from other branches of the company. After the data is verified by employees of the auditdepartment, the database is updated with this information. Some employees in the audit department complain they cannot update data in certain tables. You suspect thatother database users are holding locks on these tables.
You must identify the oldest transaction in the database and the SQL Server logins associated with thetransaction.
Which statements or functions should you use to obtain the desired results? (Choose two. Each answerrepresents a part of the solution.)
A.
the DBCC OPENTRAN statement
B.
the DBCC ROWLOCK statement
C.
the USER_NAME function
D.
the SUSER_SNAME function
E.
the SUSER_SID function
F.
the USER_ID function
Explanation:
You should use the DBCC OPENTRAN statement and the SUSER_SNAME function. The DBCC OPENTRAN statement is used to obtain the details of the oldest transaction in the database. This statement returns thesecurity identification number (SID), the server process ID (SPID), the name, and the start time of the transaction.The SID value returned can be used to obtain the SQL Server login associated with the transaction. The SUSER_SNAME function is used to obtain the SQL Server login associated with the SID. When you pass the SID returned by the DBCC OPENTRAN statement to the SUSER_SNAME function, you can obtain the SQL Server login associated with the oldest transaction in the database. You should not use the DBCC ROWLOCK statement because this statement does not provide information aboutthe oldest transaction in the database or the SQL Server login associated with the transaction. The DBCCROWLOCK statement was valid in the earlier versions of the SQL Server, but is not supported in SQL Server2008. You should not use the USER_NAME
function because the USER_NAME function does not provide theinformation required in this scenario. The USER_NAME function returns the database user name associated with the user identification number passed as an argument. You should not use the SUSER_SID function because this function does not provide the information required inthis scenario. The
SUSER_SID function returns the SID associated with the database user name passed as anargument. You should not use the USER_ID function because this function does not provide information required in thisscenario. The USER_ID function returns the user ID associated with the database user name passed as anargument.Objective:
Monitoring and Troubleshooting SQL ServerSub-Objective:
Identify concurrency problems.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 > DBCC (Transact-SQL) > DBCC OPENTRAN (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 > SUSER_SNAME (Transact-SQL)