x

can not shrink db

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.

more ▼

asked Oct 13, 2012 at 09:57 AM in Default

avatar image

pits
830 89 92 95

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered Oct 13, 2012 at 10:32 AM

avatar image

Fatherjack ♦♦
43.7k 79 97 117

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, 2012 at 08:14 AM pits
 SELECT  log_reuse_wait_desc from sys.databases

What result do you get for the MIBONE database?

Oct 14, 2012 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, 2012 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, 2012 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, 2012 at 06:26 AM pits
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 14, 2012 at 02:18 PM

avatar image

JohnM
12.3k 3 7 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2

asked: Oct 13, 2012 at 09:57 AM

Seen: 945 times

Last Updated: Oct 16, 2012 at 01:52 PM

Copyright 2016 Redgate Software. Privacy Policy