MS SQL Server - TLog out of control

Author

TLog out of control

cbrichards

2006-11-16, 7:12 pm

I just had a transaction log that was growing at about 2 gigs a minute.

It was growing so fast that we felt we had no choice but to stop and restart
the service.

Part of the problem is that we did not know how to track down what the
problem was before taking the drastic measure of stopping and starting sql
server.

What commands or views should one look at in such a situation. Or is there
anything that can be done now to determine what was causing the problem?

I came across the following link:
http://msdn2.microsoft.com/en-us/library/ms176029.aspx

If you have seen this before, please help. It is quite unnerving.

I am running SQL Server 2005, SP1, on Windows 2003.

--
Message posted via http://www.webservertalk.com

Kevin3NF

2006-11-16, 7:12 pm

Tempdb or one of your user databases?

--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm

Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com

"cbrichards" <u3288@uwe> wrote in message news:696376658b393@u
we...
>I just had a transaction log that was growing at about 2 gigs a minute.
>
> It was growing so fast that we felt we had no choice but to stop and
> restart
> the service.
>
> Part of the problem is that we did not know how to track down what the
> problem was before taking the drastic measure of stopping and starting sql
> server.
>
> What commands or views should one look at in such a situation. Or is there
> anything that can be done now to determine what was causing the problem?
>
> I came across the following link:
> http://msdn2.microsoft.com/en-us/library/ms176029.aspx
>
> If you have seen this before, please help. It is quite unnerving.
>
> I am running SQL Server 2005, SP1, on Windows 2003.
>
> --
> Message posted via http://www.webservertalk.com
>

David Browne

2006-11-16, 7:12 pm

"cbrichards" <u3288@uwe> wrote in message news:696376658b393@u
we...
>I just had a transaction log that was growing at about 2 gigs a minute.
>
> It was growing so fast that we felt we had no choice but to stop and
> restart
> the service.
>
> Part of the problem is that we did not know how to track down what the
> problem was before taking the drastic measure of stopping and starting sql
> server.
>
> What commands or views should one look at in such a situation. Or is there
> anything that can be done now to determine what was causing the problem?
>
> I came across the following link:
> http://msdn2.microsoft.com/en-us/library/ms176029.aspx
>
> If you have seen this before, please help. It is quite unnerving.
>
> I am running SQL Server 2005, SP1, on Windows 2003.
>

DMV's can show you what transactions are running and how much log space they
are using.

EG

select
st.session_id,
t.transaction_id,
s.login_name,
s.status session_status,
s.cpu_time session_cpu,
s.logical_reads session_logical_read
s,
s.reads session_reads,
s.writes session_writes,
t. database_transaction
_log_bytes_used,
sql_details.text session_current_sql_
text
from sys. dm_tran_database_tra
nsactions t
join sys. dm_tran_session_tran
sactions st
on st.transaction_id = t.transaction_id
join sys.dm_exec_sessions s
on st.session_id = s.session_id
left join sys.dm_exec_requests req
on s.session_id = req.session_id
outer apply sys. dm_exec_sql_text(req
.sql_handle) sql_details
order by t. database_transaction
_log_bytes_used desc

David

cbrichards via SQLMonster.com

2006-11-16, 7:12 pm

TempDB was out of control.

Kevin3NF wrote:[color=darkred
]
>Tempdb or one of your user databases?
>
>[quoted text clipped - 15 lines]

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...server/200611/1

Sponsored Links