August 30, 2012 at 8:48 am
mike.whorley (8/30/2012)
Thanks all !!!Running a 2nd backup and then a shrink in EM worked a treat 🙂
virtual119.367211.94245
So, now that you have effectively shrunk a log that will inevitably grow again and is building fragmentation... What do you plan on doing in the future? You still haven't figured out why your log grew to be so big in the first place.
Jared
CE - Microsoft
August 30, 2012 at 9:09 am
You could try Activity Monitor, See which spid is using the most CPU/Memory
Use the dmv sys.sysprocesses
Script 1
Check Status of active transactions
SELECT
tat.transaction_id [TransactionID],
tat.transaction_begin_time [TranBeginTime],
CASE tat.transaction_type
WHEN 1 THEN 'Read/Write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
WHEN 4 THEN 'Distributed transaction'
END [TranType],
CASE tat.transaction_state
WHEN 0 THEN 'Not completely initialized'
WHEN 1 THEN 'Initialized but not started'
WHEN 2 THEN 'Active'
WHEN 3 THEN 'Ended(read-only transaction)'
WHEN 4 THEN 'Commit initiated for distributed transaction'
WHEN 5 THEN 'Transaction prepared and waiting for resolution'
WHEN 6 THEN 'Committed'
WHEN 7 THEN 'Transaction is being rolled back'
WHEN 8 THEN 'Rolled back'
END [TranStatus],
tst.session_id [SPID],
tst.is_user_transaction [IsUserTransaction],
s.[text] [MostRecentSQLRun]
FROM
sys.dm_tran_active_transactions [tat]
JOIN sys.dm_tran_session_transactions [tst]
ON tat.transaction_id = tat.transaction_id
JOIN sys.dm_exec_connections [dec]
ON [dec].session_id = tst.session_id
CROSS APPLY sys.dm_exec_sql_text([dec].most_recent_sql_handle) s
ORDER BY
[TranBeginTime]
Script 2
SELECT request_session_id, COUNT (*) num_locks
FROM sys.dm_tran_locks
GROUP BY request_session_id
ORDER BY count (*) DESC
If it is using the logfile it will be deleting,updating or inserting so it must be locking resources.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply