You are employed as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Your duties at Domain.com are the administrating of a database named CK_Data that is situated on a SQL Server 2005 computer named Certkiller -DB03. As the administrator you receive a warning that the drive on which the CK_Data log file is situated is near capacity. The transaction log is backed up every five minutes. You notice that this is steadily growing. You suspect that an uncommitted transaction may be the reason and you want to investigate it. You want to identify both the server process ID and the start time of the oldest active transaction in CK_Data.
What should you do?
A.
The first thing you need to do is open a query window.
Then you should connect to the master database.
Thereafter you should perform the following statement:
SELECT TOP 1 spid, last_batch
FROM sys.sysprocesses
WHERE dbid = db_id(‘CK_Data’) AND open_tran > 0
ORDER BY last_batch
B.
You need to connect to the CK_Data database.
Then you should execute DBCC OPENTRAN.
Thereafter you should view the SPID and Start time rows.
C.
You need to connect to the master database and execute DBCC OPENTRAN.
Then you must view the SPID and Start time rows.
D.
You need to open the Activity Monitor in the SQL Server Management Studio.
Then you can select the Process Info page and apply the following filter settings:
Database = CK_Data
Open Transactions = Yes
View the Process ID and Last Batch columns.
Explanation:
The DBCC OPENTRAN command gives you the information you need by displaying information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions.
Syntax
DBCC OPENTRAN
[( [ ‘database’ | database_id | 0 ] ) ]
[WITH TABLERESULTS] [, [NO_INFOMSGS] ]
]