user query that make a user query log growth quickly !

Dear all,

right now tried to find out which query make a user database's log growth quickly but not in tempdB ( I can use whoisactive for that purpose), what script/method you will use ?

dbcc opentrans(<user database>) ;

?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargAssociate Principal EngineerCommented:
Hello,

The log space used by the some process of sql server.

you can identify that using

*SP_WHO2 command

*Activity Monitor

To find the exact log size on DISK  DBCC SQLPERF(LOGSPACE)

There might be long running queries. you can find our using
SELECT spid, status, loginame=SUBSTRING(loginame,
1,12),hostname=substring(hostname, 1, 12),
      blk=CONVERT(char(3), blocked),dbname=SUBSTRING(DB_NAME(dbid),
1, 10), cmd, waittype
FROM master.dbo.sysprocesses
  WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

Open in new window


A long-running transaction - the log cannot be cleared until the transaction commits or rolls back. You can use DBCC OPENTRAN to show you the oldest active transaction

You can find out the long running trasactions:

sp_who2

SELECT open_tran FROM master.sys.sysprocesses WHERE SPID=<blocking SPID number>

Go
marrowyungSenior Technical architecture (Data)Author Commented:
"sp_who2

SELECT open_tran FROM master.sys.sysprocesses WHERE SPID=<blocking SPID number>

Go"

I am using both but how can I find out how many active log a query is consuming ?

"*SP_WHO2 command

*Activity Monitor"

I run sp_who2 everyday I don't see it show out what query hold that large amount of active transaction log.

In which the activity monitor show me this, I am watching that and it don't show which query take out so many open/active transaction log.

"A long-running transaction - the log cannot be cleared until the transaction commits or rolls back. You can use DBCC OPENTRAN to show you the oldest active transaction"

yeah, I understand, the problem here is the log of a user database full , 80GB, in few hours ! only today
!

so the query you show above shows the sizes of the ACTIVE part of the user database log sizes and match it with each STILL ACTIVE/BLOCKED user queries ?
marrowyungSenior Technical architecture (Data)Author Commented:
NO.. the query don't even shows the active log size take up by a user queries !

just spid, status, loginname, hostname , blk dbname , cmd and waitype!

I usually run this:

SELECT spid, waittype as "Type of resource the connection is waiting on", lastwaittype as "Current Type of resource the connection is waiting on of a SPID" ,
 waittime as "the number of milliseconds waiting for that type", 
 waitresource as "detailed information about the specific resource that the SPID is waiting on",
 loginame, program_name, hostname as "From host", login_time, last_batch as "last check from us", DB_NAME(dbid) AS "Against database" , 
(SELECT text FROM sys.dm_exec_sql_text(sql_handle))as "Query executed" 
FROM master..sysprocesses WHERE  open_tran > 0 

Open in new window


to find out which is in user in MEMORY but not in transaction log.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

marrowyungSenior Technical architecture (Data)Author Commented:
hi,

what I wan to do is, if the log of a user database is full, I want to find out which query make this happen and why the query can make the log full.

any idea?
Vitor MontalvãoMSSQL Senior EngineerCommented:
right now tried to find out which query make a user database's log growth quickly but not in tempdB ( I can use whoisactive for that purpose), what script/method you will use ?
I would prefer to run a trace on it, since any query that you could have will need to be run many times for refresh purpose and don't keep an history of what happened on the database.
Also with a trace you can have the information of how may rows has been affected so you can identify quickly massive operations on data.
marrowyungSenior Technical architecture (Data)Author Commented:
I am sorry what trace and how to create that?

I think there can be DMVs and script for me to find out the real time active transaction log figure on which query eat up that part.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Until MSSQL 2008R2 you can use MSSQL Profiler to capture activity in an instance.
From MSSQL 2012 and later versions you should use Extended Events since impact less on your databases.
marrowyungSenior Technical architecture (Data)Author Commented:
"From MSSQL 2012 and later versions you should use Extended Events since impact less on your databases."

yeah, heard about that and EV can also help on detecting deadlock

"Until MSSQL 2008R2 you can use MSSQL Profiler to capture activity in an instance."

but when the log was full, it already too late ?

so no way using DMV or command to find out why that query take so much log and still didn't commit ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
When the log is full the database don't allows any write operation but read only.

so no way using DMV or command to find out why that query take so much log and still didn't commit ?
I think there's but can't remember the correct one now.
marrowyungSenior Technical architecture (Data)Author Commented:
"When the log is full the database don't allows any write operation but read only."

yeah, but if that query still there and for some reason still active, we can't shrink the log as the transaction still consider as active! we have to kill it first before we can shrink the log.
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can always shrink the log but the active transaction will always stay in the transaction log until you commit or rollback them.
With this I want to say that active transactions aren't a shrink log operation's blocker.
marrowyungSenior Technical architecture (Data)Author Commented:
"You can always shrink the log but the active transaction will always stay in the transaction log until you commit or rollback them."

yeah, so by shrinking the log, the inactive one gone, but I want to know which active query take the transaction log and don't release it, then I can study if the transaction never commit.
marrowyungSenior Technical architecture (Data)Author Commented:
but how can this: DBCC SQLPERF(LOGSPACE) find out, the active transaction log of a query on how many space it take of a particular user database  ?
Mark WillsTopic AdvisorCommented:
You need to see the transactions, so, use the DBCC OPENTRAN as previously suggested.

There is also the dynamic management view sys.dm_tran_active_transactions (there are a couple of these )

From those you can get the SPID and through SQL profiler can seek out the SPIDS and the SQL they are running.

Or, could write a query (rather dig it up from prior attempts) that uses the DMV's. Along the lines of : https://sqltune.wordpress.com/2011/07/12/identify-queries-that-consume-a-large-amount-of-log-space-in-sql-server/

Unfortunately there isn't much you can do to retrospectively analyse what happened. For that, you are better off getting a proven Transaction Log Reader (Redgate, ApexSQL, Toad for SQL, and others).

there are a couple of freebies that you could search for. One I have been meaning to try is the codeplex one : http://internalsviewer.codeplex.com/ but cannot say one way or another as to abilities.

Hope that helps a bit. Sounds like you are in a tough space.

Best (probably) is to get some alerts on "sudden" log growth so you have time to investigate the open transactions. Bit old fashioned, but it does work.
marrowyungSenior Technical architecture (Data)Author Commented:
"Unfortunately there isn't much you can do to retrospectively analyse what happened. For that, you are better off getting a proven Transaction Log Reader (Redgate, ApexSQL, Toad for SQL, and others)."

):):)

excellent, don't tell me you are selling tools, but of course I knew you are not!

So not much a DMV can do you mean...

"Hope that helps a bit. Sounds like you are in a tough space.
"
no...... but third party application after upgrade can gives this shit !

I will just pinpoint it using whoisactive and kill that spid, then shrink it the log.

"Best (probably) is to get some alerts on "sudden" log growth so you have time to investigate the open transactions. Bit old fashioned, but it does work."

you mean by Disk monitoring tools?
Mark WillsTopic AdvisorCommented:
Yes, or, use something like DBCC SQLPERF(logspace) in a stored procedure and schedule that to run on a regular basis saving the results

something like:

-- create a table to store results

create table tbl_dbcc_sqlperf_logspace (id INT IDENTITY (1,1),logDate datetime DEFAULT GETDATE(), databaseName sysname,logSize decimal(18,7), logSpaceUsed decimal(18,7), status decimal(2)) 
GO 

-- create the DBCC procedure so we can make use of it as a SP

create proc usp_dbcc_sqlperf_logspace
as
   DBCC SQLPERF(logspace)
GO

-- create a procedure to capture DBCC results,  store that data and to report results

create proc usp_dbcc_sqlperf_get_stats (@elapsed_hours int)
as
begin
 
   declare @hours int = 0
   declare @dt datetime
   IF isnumeric(@elapsed_hours) = 1 set @hours= ISNULL(@elapsed_hours,0)
   set @dt = dateadd(hour,@hours*-1,getdate())

   insert into tbl_dbcc_sqlperf_logspace (databaseName,logSize,logSpaceUsed,status)
   exec usp_DBCC_SQLPERF_logspace
   ;

   select a.databasename, a.logsize-b.logsize as [GROWTH_IN_SIZE(MB)],
          a.logSpaceUsed-b.logSpaceUsed as GROWTH_IN_SPACE,
          datediff(s,b.logdate,a.logdate) as ELAPSED_SECONDS,
          '-->' as DETAILS,* 
   from tbl_dbcc_sqlperf_logspace a
   inner join tbl_dbcc_sqlperf_logspace b on a.databaseName = b.databaseName  
   where (select max(id) from tbl_dbcc_sqlperf_logspace c where a.databaseName = c.databaseName and c.logDate < @dt) = b.id
   and   (select max(id) from tbl_dbcc_sqlperf_logspace c where a.databaseName = c.databaseName) = a.id 
   ;
end
GO

-- now run (or schedule to run) and check results 
-- (1st time, wont have results because it compares runs)
-- the NULL is a param for number of hours 
-- so you can check this run with say a couple of hours ago by entering a 2  (or 1 or 3 or 6 etc) 

exec usp_dbcc_sqlperf_get_stats NULL

Open in new window

marrowyungSenior Technical architecture (Data)Author Commented:
I read this one: https://sqltune.wordpress.com/2011/07/12/identify-queries-that-consume-a-large-amount-of-log-space-in-sql-server/

and the code is:

——————————————————————————————-
 — Description: T-SQL to find queries that consume a large amount of log space in SQL Server
 — Source: KB # 317375
 — Author: varun.dhawan
 ——————————————————————————————-
 SELECT dtst.session_id                                                       AS
        ‘SPID’,
        CAST(Db_name(dtdt.database_id) AS VARCHAR(20))                        AS
        ‘database’,
        der.command,
        Substring(st.TEXT, ( der.statement_start_offset / 2 ) + 1,
        ( (
        CASE der.statement_end_offset
        WHEN -1 THEN Datalength(st.TEXT)
        ELSE der.statement_end_offset
                                                                       END –
        der.statement_start_offset ) / 2 ) +
        1)                                                                    AS
        statement_text,
        Coalesce(Quotename(Db_name(st.dbid)) + N’.’ + Quotename(
        Object_schema_name(st.objectid,
                 st.dbid)) +
                 N’.’ + Quotename(Object_name(st.objectid, st.dbid)), ”)     AS
        command_text,
        der.wait_type,
        der.wait_time,
        dtdt.database_transaction_log_bytes_used / 1024.0 / 1024.0            AS
        ‘MB used’,
        dtdt.database_transaction_log_bytes_used_system / 1024.0 / 1024.0     AS
        ‘MB used system’,
        dtdt.database_transaction_log_bytes_reserved / 1024.0 / 1024.0        AS
        ‘MB reserved’,
        dtdt.database_transaction_log_bytes_reserved_system / 1024.0 / 1024.0 AS
        ‘MB reserved system’,
        dtdt.database_transaction_log_record_count                            AS
        ‘Rec count’
 FROM   sys.dm_tran_database_transactions dtdt
        JOIN sys.dm_tran_session_transactions dtst
          ON dtdt.transaction_id = dtst.transaction_id
        JOIN sys.dm_exec_requests der
             CROSS APPLY sys.Dm_exec_sql_text(der.sql_handle) AS st
          ON dtst.session_id = der.session_id
 ORDER  BY 8 DESC 

Open in new window


but when I try to run it, it complains about:

“Msg 102, Level 15, State 1, Line 22
 Incorrect syntax near ‘–’.

 which is
 END –
 and SSMS also compliain about:

“Msg 102, Level 15, State 1, Line 23
 Incorrect syntax near ‘der’.

 any word missing in between :

” ELSE der.statement_end_offset
 END –
 der.statement_start_offset ) / 2 ) +”

Open in new window


any problem?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Check if the single quotes are the correct character.
And what's your SQL Server version?
marrowyungSenior Technical architecture (Data)Author Commented:
2014, you can try to do that in your PC, I do it on my local SQL server.

you can parse it without running it and you should see error !
Vitor MontalvãoMSSQL Senior EngineerCommented:
That CROSS APPLY doesn't seem correct to me.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this:
SELECT dtst.session_id                                                       AS
        'SPID',
        CAST(Db_name(dtdt.database_id) AS VARCHAR(20))                        AS
        'database',
        der.command,
        Substring(st.TEXT, ( der.statement_start_offset / 2 ) + 1,
        ( (CASE der.statement_end_offset
				WHEN -1 THEN Datalength(st.TEXT)
				ELSE der.statement_end_offset
			END) / 2 ) +
        1)                                                                    AS
        statement_text,
        Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(
        Object_schema_name(st.objectid,
                 st.dbid)) +
                 N'.' + Quotename(Object_name(st.objectid, st.dbid)), '')     AS
        command_text,
        der.wait_type,
        der.wait_time,
        dtdt.database_transaction_log_bytes_used / 1024.0 / 1024.0            AS
        'MB used',
        dtdt.database_transaction_log_bytes_used_system / 1024.0 / 1024.0     AS
        'MB used system',
        dtdt.database_transaction_log_bytes_reserved / 1024.0 / 1024.0        AS
        'MB reserved',
        dtdt.database_transaction_log_bytes_reserved_system / 1024.0 / 1024.0 AS
        'MB reserved system',
        dtdt.database_transaction_log_record_count                            AS
        'Rec count'
 FROM   sys.dm_tran_database_transactions dtdt
        JOIN sys.dm_tran_session_transactions dtst ON dtdt.transaction_id = dtst.transaction_id
        JOIN sys.dm_exec_requests AS der 
			CROSS APPLY sys.Dm_exec_sql_text(sql_handle) AS st
        ON dtst.session_id = der.session_id
ORDER  BY 8 DESC 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"That CROSS APPLY doesn't seem correct to me. "

I can't see why people on that page keep saying yeah it is working, yeah it is working.
marrowyungSenior Technical architecture (Data)Author Commented:
do you know if there is anyway to list the table name too ?

wait.. what have to done to fix the query ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
The CROSS APPLY had "der." prefix and I think that was wrong.
Also in the CASE statement there was an extra reference to der.statement_end_offset.
marrowyungSenior Technical architecture (Data)Author Commented:
when I try it in your way, I still get a lot of red line:

error
any reason you can see?
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's because you need to replace the bad characters with single quotes:
N'.'
'MB ...'
'Rec count'
Vitor MontalvãoMSSQL Senior EngineerCommented:
Btw, why didn't you copy the code from my comment?
I could see some differences still.
marrowyungSenior Technical architecture (Data)Author Commented:
"Btw, why didn't you copy the code from my comment?"

nono, just want to try it one by one myself and see why I got it wrong.

"That's because you need to replace the bad characters with single quotes:
 N'.'
 'MB ...'"

I did it and I see some more change by you:

e.g.

you change "JOIN sys.dm_exec_requests  der " to
"JOIN sys.dm_exec_requests AS der "

can't see why we don't need it here:

JOIN sys.dm_tran_session_transactions dtst
Vitor MontalvãoMSSQL Senior EngineerCommented:
The 'AS' was only me testing. Is irrelevant.
What's relevant is:
( (CASE der.statement_end_offset
                        WHEN -1 THEN Datalength(st.TEXT)
                        ELSE der.statement_end_offset
                  END) / 2 ) +
        1)  AS   statement_text,


and:
CROSS APPLY sys.Dm_exec_sql_text(sql_handle) AS st

plus replace the single quotes where are appropriate.
marrowyungSenior Technical architecture (Data)Author Commented:
ok, tks,
marrowyungSenior Technical architecture (Data)Author Commented:
"The 'AS' was only me testing. Is irrelevant."

"CROSS APPLY sys.Dm_exec_sql_text(sql_handle) AS st"

I am just wondering why this "JOIN sys.dm_tran_session_transactions dtst" do not need AS.

not important anyway, right?  

so just this:

( (CASE der.statement_end_offset
                         WHEN -1 THEN Datalength(st.TEXT)
                         ELSE der.statement_end_offset
                   END) / 2 ) +
         1)  AS   statement_text,

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes. The AS keyword it's a more explicity way to define an alias. It can be used for columns and tables but isn't necessary as you can see.
marrowyungSenior Technical architecture (Data)Author Commented:
you all very good helper.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.