March 11, 2010 at 7:57 am
Thanks Jason, I pulled some nice stuff from your article and now have a quick reference for log growth issues!
March 11, 2010 at 8:45 am
Good stuff Jason.
@medelstein From the (incredibly helpful, eh?) error, you're running the code on a SQL 2000 box, or in the context of a DB that set to the 8.0 compat mode. That error is really the Engine saying I-don't-understand-DMVs.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
March 11, 2010 at 8:46 am
medelstein (3/11/2010)
I tried creating the runninSqlStatements stored procedure and it gives me a syntax error on this line:CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt
with this error:
Msg 102, Level 15, State 1, Procedure RunningSQLStatements, Line 9
Incorrect syntax near '.'.
APPLY requires 2005, compatibility level 90.
edit:
...or what the other Paul said 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 11, 2010 at 9:01 am
Very nice article, Jason! Added to my briefcase.
-- Gianluca Sartori
March 11, 2010 at 9:34 am
This is a very good article Jason, thank you.
When recovery model is full, proper handling of the log file growth is of utmost importance, and your article is going to be a great help. It can be somewhat frustrating to run dbcc loginfo() just to discover that there are thousands of segments in the beast yet the value of the last record's status is 2 and, therefore, it cannot be immediately tamed, so it is nice to have a way to quickly identify who done it.
Oleg
March 11, 2010 at 9:40 am
arr.nagaraj (3/11/2010)
No. I prefer Jason's approach. Logging every small transaction is just pointless in the context of this article, as I have made clear already. You will just make it harder to find the information you need if and when a crisis occurs.
BTW, it might be nice if you acknowledged the efforts made by the author in putting this article together.
Apologies Jason for not acknowledging on time. It was a good article. But, I have tried a similar thing before and I did do a few changes to what you have posted which I was trying to point out.
No problems. Again, if you deem it necessary to modify - feel free.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 11, 2010 at 9:41 am
medelstein (3/11/2010)
Hello,I tried creating the runninSqlStatements stored procedure and it gives me a syntax error on this line:
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt
with this error:
Msg 102, Level 15, State 1, Procedure RunningSQLStatements, Line 9
Incorrect syntax near '.'.
Here is the entire sql I'm using:
Create Procedure RunningSQLStatements
as
Begin
Insert Into AuditTSQL (LoginName,SPID,DBName,ParentQuery,Wait,Program,HostName,StartTime,InsertedTime)
SELECT sp.loginame as [UserName],sp.spid as SPID , DB_NAME(sp.dbid) ,qt.text ,wait_type ,sp.program_name ,Hostname ,start_time ,convert(varchar(19),GETDATE(),120)
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
Inner Join sys.dm_exec_sessions es On er.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt
WHERE es.is_user_process = 1
AND er.session_Id NOT IN (@@SPID)
ORDER BY sp.dbid, sp.loginame
End
Thanks,
Mike
What version of sql server?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 11, 2010 at 9:42 am
Paul Randal (3/11/2010)
Good stuff Jason.@medelstein From the (incredibly helpful, eh?) error, you're running the code on a SQL 2000 box, or in the context of a DB that set to the 8.0 compat mode. That error is really the Engine saying I-don't-understand-DMVs.
Thanks Paul
Also, thanks for answering the question for medelstein.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 11, 2010 at 9:43 am
Gianluca Sartori (3/11/2010)
Very nice article, Jason! Added to my briefcase.
Thank you.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 11, 2010 at 9:45 am
Chad Crawford (3/11/2010)
Thanks Jason, I pulled some nice stuff from your article and now have a quick reference for log growth issues!
Thank you. I actually started out using this method as an audit of sql statements being run by certain logged in users. I am sure you could probably also find other reasons to employ this.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 11, 2010 at 9:45 am
Oleg Netchaev (3/11/2010)
This is a very good article Jason, thank you.When recovery model is full, proper handling of the log file growth is of utmost importance, and your article is going to be a great help. It can be somewhat frustrating to run dbcc loginfo() just to discover that there are thousands of segments in the beast yet the value of the last record's status is 2 and, therefore, it cannot be immediately tamed, so it is nice to have a way to quickly identify who done it.
Oleg
Thanks Oleg.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 12, 2010 at 6:41 am
Jason, excellent article, in particular your second method for tracking growth. Thanks for taking the time to develop this and then share it with the community.
In your SQL running code do you feel there is any advantage to be had in trapping the actual SQL running at the time rather than the last batch, which I think yours traps. I am thinking of something like this (I take no credit for this code)
SELECT SDER.[session_id], SDER.[request_id],SDER.[statement_start_offset],
SDER.[statement_end_offset],
CASE
WHEN SDER.[statement_start_offset] > 0 THEN
--The start of the active command is not at the beginning of the full command text
CASE SDER.[statement_end_offset]
WHEN -1 THEN
--The end of the full command is also the end of the active statement
SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, 2147483647)
ELSE
--The end of the active statement is not at the end of the full command
SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, (SDER.[statement_end_offset] - SDER.[statement_start_offset])/2)
END
ELSE
--1st part of full command is running
CASE SDER.[statement_end_offset]
WHEN -1 THEN
--The end of the full command is also the end of the active statement
RTRIM(LTRIM(DEST.[text]))
ELSE
--The end of the active statement is not at the end of the full command
LEFT(DEST.TEXT, (SDER.[statement_end_offset]/2) +1)
END
END AS [executing statement],
DEST.[text] AS [full statement code]
FROM sys.[dm_exec_requests] SDER CROSS APPLY sys.[dm_exec_sql_text](SDER.[sql_handle]) DEST
WHERE SDER.session_id > 50
ORDER BY SDER.[session_id], SDER.[request_id]
---------------------------------------------------------------------
March 12, 2010 at 9:13 am
george sibbald (3/12/2010)
Jason, excellent article, in particular your second method for tracking growth. Thanks for taking the time to develop this and then share it with the community.In your SQL running code do you feel there is any advantage to be had in trapping the actual SQL running at the time rather than the last batch, which I think yours traps. I am thinking of something like this (I take no credit for this code)
Yes, it is beneficial to trap the actual running code. I will have to compare and contrast the two.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 27, 2010 at 7:50 pm
That'll teach me... look at the SSC homepage everyday because I might have missed the email. That's the only excuse I have for not seeing this article until today and I'm sticking to it. 😛
Nice article and methods, Jason.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 27, 2010 at 7:53 pm
Jeff Moden (5/27/2010)
That'll teach me... look at the SSC homepage everyday because I might have missed the email. That's the only excuse I have for not seeing this article until today and I'm sticking to it. 😛Nice article and methods, Jason.
Thanks Jeff.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 31 through 45 (of 48 total)
You must be logged in to reply to this topic. Login to reply