can not shrink db

Question

Hello Experts, my ldf is running out of space,the db is in simple recovery mode...

the db size is 1.8 tb,

database is not getting shrink,there is one recent open transaction also there are couple of process running on the is db...

can you please advise.

Answer 1

0

If you cannot kill the process (you need to understand what the process is doing and whether it can be killed before simply killing it) then you have the option of increasing the logfile size or adding a new log file based on a different drive if you have no space on the drive currently used. This will only be a stop-gap solution to allow the long running transaction to complete.

Once you identify why the transaction was running so long then you can decide on whether the logfile needs to be shrunk again

answered Oct 13 '12 at 10:32 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

Thanks Jonathan, There is no open transactions. No blockings. Error message : The transaction log for database 'MIBONE' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases...how to do this? I already performed COMMIT and CHECKPOINT . I can not backup log with TRUNCATE ONLY since db is in simple revocery mode. The ldf is growing...can you please help.

Oct 14 '12 at 08:14 AM pits

SELECT  log_reuse_wait_desc from sys.databases

What result do you get for the MIBONE database?

Oct 14 '12 at 09:51 AM Fatherjack ♦♦

NOTHING ACTIVE_TRANSACTION LOG_BACKUP ACTIVE_TRANSACTION NOTHING NOTHING NOTHING NOTHING NOTHING NOTHING NOTHING NOTHING NOTHING NOTHING NOTHING REPLICATION NOTHING NOTHING CHECKPOINT NOTHING NOTHING LOG_BACKUP NOTHING NOTHING NOTHING

How can i see these two active transactions?

Oct 14 '12 at 01:48 PM pits

So, to see the status for the MIBONE database you need to either select the database name too, or filter on the database name

SELECT  name, log_reuse_wait_desc from sys.databases

or

SELECT  log_reuse_wait_desc from sys.databases WHERE name = 'MIBONE'

Oct 15 '12 at 07:43 AM Fatherjack ♦♦

Thank you FatherJeck, I can see one active transaction using above statement, but how can i find the spid of the same active transaction? I need to kill the same...is there any athorised command by microsoft to know the spid of that active transaction?i dont see any anything in dbcc opentran.

Oct 16 '12 at 06:26 AM pits

show 2 more replies

(comments are locked)

Answer 2

To find the open transaction, you can use this script, written by Paul Randal:

SELECT s_tst.[session_id],
   s_es.[login_name] AS [Login Name],
   DB_NAME (s_tdt.database_id) AS [Database],
   s_tdt.[database_transaction_begin_time] AS [Begin Time],
   s_tdt.[database_transaction_log_record_count] AS [Log Records],
   s_tdt.[database_transaction_log_bytes_used] AS [Log Bytes],
   s_tdt.[database_transaction_log_bytes_reserved] AS [Log Rsvd],
   s_est.[text] AS [Last T-SQL Text],
   s_eqp.[query_plan] AS [Last Plan]
FROM sys.dm_tran_database_transactions s_tdt
   JOIN sys.dm_tran_session_transactions s_tst
      ON s_tst.[transaction_id] = s_tdt.[transaction_id]
   JOIN sys.[dm_exec_sessions] s_es
      ON s_es.[session_id] = s_tst.[session_id]
   JOIN sys.dm_exec_connections s_ec
      ON s_ec.[session_id] = s_tst.[session_id]
   LEFT OUTER JOIN sys.dm_exec_requests s_er
      ON s_er.[session_id] = s_tst.[session_id]
   CROSS APPLY sys.dm_exec_sql_text (s_ec.[most_recent_sql_handle]) AS s_est
   OUTER APPLY sys.dm_exec_query_plan (s_er.[plan_handle]) AS s_eqp
ORDER BY [Begin Time] ASC;

Borrowed from here: http://www.sqlskills.com/blogs/paul/post/Script-open-transactions-with-text-and-plans.aspx

can not shrink db

Follow this question