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

pits gravatar image

pits
830 83 91 92

(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

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

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

JohnM gravatar image

JohnM
6.9k 1 3 7

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1

asked: Oct 13, 2012 at 09:57 AM

Seen: 801 times

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