Skip to main content
added 246 characters in body
Source Link
Craig Efrein
  • 9.7k
  • 12
  • 62
  • 97

Have you checked to make sure there isn't an open transaction that's still running on the production server?

SELECT
es.session_id
,er.blocking_session_id
,er.start_time
,es.status
,es.login_name
,DB_NAME(er.database_id) as database_name
,es.host_name
,es.program_name
,er.command
,es.reads
,es.writes
,es.cpu_time
,er.wait_type
,er.wait_time
,er.last_wait_type
,er.wait_resource
,CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS transaction_isolation_level
,OBJECT_NAME(st.objectid, er.database_id) as object_name
,SUBSTRING(st.text, er.statement_start_offset / 2,
(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), st.text)) * 2
ELSE er.statement_end_offset END - er.statement_start_offset) / 2) AS query_text
,ph.query_plan
FROM sys.dm_exec_connections ec
LEFT OUTER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_requests er ON ec.connection_id = er.connection_id
OUTER APPLY sys.dm_exec_sql_text(sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(plan_handle) ph
WHERE ec.session_id <> @@SPID
AND er.status = 'running'
ORDER BY er.total_elapsed_time desc

As long as a transaction is open and modifying data, the LDF will continue to grow, even if you perform a transaction log backup.

Several options :

A. Rollback the transaction (cancel the query, from the client running it)

or

A. Kill the session with (KILL {SPID})

  1. Then perform a transaction log backup

  2. Shrink the log file with DBCC SHRINKFILE

Note : The Query listed above is a replacement for sp_who2 from Brent Ozar.

Update 7/12 16:20

In SSMS, connect to your instance,

  1. Right click on your database
  2. Select Tasks
  3. Select Shrink
  4. Select Files
  5. Under File Type, select Log

Can you please send back the value in Available free space?

Have you checked to make sure there isn't an open transaction that's still running on the production server?

SELECT
es.session_id
,er.blocking_session_id
,er.start_time
,es.status
,es.login_name
,DB_NAME(er.database_id) as database_name
,es.host_name
,es.program_name
,er.command
,es.reads
,es.writes
,es.cpu_time
,er.wait_type
,er.wait_time
,er.last_wait_type
,er.wait_resource
,CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS transaction_isolation_level
,OBJECT_NAME(st.objectid, er.database_id) as object_name
,SUBSTRING(st.text, er.statement_start_offset / 2,
(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), st.text)) * 2
ELSE er.statement_end_offset END - er.statement_start_offset) / 2) AS query_text
,ph.query_plan
FROM sys.dm_exec_connections ec
LEFT OUTER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_requests er ON ec.connection_id = er.connection_id
OUTER APPLY sys.dm_exec_sql_text(sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(plan_handle) ph
WHERE ec.session_id <> @@SPID
AND er.status = 'running'
ORDER BY er.total_elapsed_time desc

As long as a transaction is open and modifying data, the LDF will continue to grow, even if you perform a transaction log backup.

Several options :

A. Rollback the transaction (cancel the query, from the client running it)

or

A. Kill the session with (KILL {SPID})

  1. Then perform a transaction log backup

  2. Shrink the log file with DBCC SHRINKFILE

Note : The Query listed above is a replacement for sp_who2 from Brent Ozar.

Have you checked to make sure there isn't an open transaction that's still running on the production server?

SELECT
es.session_id
,er.blocking_session_id
,er.start_time
,es.status
,es.login_name
,DB_NAME(er.database_id) as database_name
,es.host_name
,es.program_name
,er.command
,es.reads
,es.writes
,es.cpu_time
,er.wait_type
,er.wait_time
,er.last_wait_type
,er.wait_resource
,CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS transaction_isolation_level
,OBJECT_NAME(st.objectid, er.database_id) as object_name
,SUBSTRING(st.text, er.statement_start_offset / 2,
(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), st.text)) * 2
ELSE er.statement_end_offset END - er.statement_start_offset) / 2) AS query_text
,ph.query_plan
FROM sys.dm_exec_connections ec
LEFT OUTER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_requests er ON ec.connection_id = er.connection_id
OUTER APPLY sys.dm_exec_sql_text(sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(plan_handle) ph
WHERE ec.session_id <> @@SPID
AND er.status = 'running'
ORDER BY er.total_elapsed_time desc

As long as a transaction is open and modifying data, the LDF will continue to grow, even if you perform a transaction log backup.

Several options :

A. Rollback the transaction (cancel the query, from the client running it)

or

A. Kill the session with (KILL {SPID})

  1. Then perform a transaction log backup

  2. Shrink the log file with DBCC SHRINKFILE

Note : The Query listed above is a replacement for sp_who2 from Brent Ozar.

Update 7/12 16:20

In SSMS, connect to your instance,

  1. Right click on your database
  2. Select Tasks
  3. Select Shrink
  4. Select Files
  5. Under File Type, select Log

Can you please send back the value in Available free space?

Source Link
Craig Efrein
  • 9.7k
  • 12
  • 62
  • 97

Have you checked to make sure there isn't an open transaction that's still running on the production server?

SELECT
es.session_id
,er.blocking_session_id
,er.start_time
,es.status
,es.login_name
,DB_NAME(er.database_id) as database_name
,es.host_name
,es.program_name
,er.command
,es.reads
,es.writes
,es.cpu_time
,er.wait_type
,er.wait_time
,er.last_wait_type
,er.wait_resource
,CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS transaction_isolation_level
,OBJECT_NAME(st.objectid, er.database_id) as object_name
,SUBSTRING(st.text, er.statement_start_offset / 2,
(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), st.text)) * 2
ELSE er.statement_end_offset END - er.statement_start_offset) / 2) AS query_text
,ph.query_plan
FROM sys.dm_exec_connections ec
LEFT OUTER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_requests er ON ec.connection_id = er.connection_id
OUTER APPLY sys.dm_exec_sql_text(sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(plan_handle) ph
WHERE ec.session_id <> @@SPID
AND er.status = 'running'
ORDER BY er.total_elapsed_time desc

As long as a transaction is open and modifying data, the LDF will continue to grow, even if you perform a transaction log backup.

Several options :

A. Rollback the transaction (cancel the query, from the client running it)

or

A. Kill the session with (KILL {SPID})

  1. Then perform a transaction log backup

  2. Shrink the log file with DBCC SHRINKFILE

Note : The Query listed above is a replacement for sp_who2 from Brent Ozar.