March 11, 2010 at 3:18 am
Let's keep in mind that the query I provide for recording the size is for SQL 2005 and 2008 (as stated in the article). To make this work on SQL 2000, you would have to use the script (or something of that nature) that Raj provided in his second post to this thread.
That said, go ahead and continue to digress 😉
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 3:22 am
Paul White (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.
Good point. As the method currently is, and depending on volume - there may already be plenty of information to sift through to find the offending code.
BTW, it might be nice if you acknowledged the efforts made by the author in putting this article together.
Thanks again. 😎
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 3:24 am
CirquedeSQLeil (3/11/2010)
Let's keep in mind that the query I provide for recording the size is for SQL 2005 and 2008 (as stated in the article). To make this work on SQL 2000, you would have to use the script (or something of that nature) that Raj provided in his second post to this thread.That said, go ahead and continue to digress 😉
[troll] Does anyone use 2000 any more? [/troll]
Sadly, I cannot digress much longer - I have to review your review of my article, Jason. Back later, perhaps.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 11, 2010 at 3:31 am
Paul White (3/11/2010)
Does anyone use 2000 any more?Sadly, I cannot digress much longer - I have to review your review of my article, Jason. Back later, perhaps.
Unfortunately, I recently took ownership of a lot of 2000 boxes. I guess I should work out a solution for them too 😉
I won't be back for several hours - I need to get some sleep sometime before the workday begins.:doze:
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 3:35 am
I am not really all that concerned about how you run your databases.
You said yourself it was a digression - and therefore not related to the article under discussion.
One thing I fail to understand is your questions about my digression are ok. But my replies arent. 🙂 . No problem. I have met people like this, and I am sure I will.
Regards,
Raj
March 11, 2010 at 3:41 am
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.
Regards,
Raj
March 11, 2010 at 3:50 am
You also do not mention why the work-arounds in the KB articles will not work for you.
Workarounds do work. But they are temporary and the issue can re occur on the same database and also on some other databases. Again apologies sir for digressing. But I had no choice.
Regards,
Raj
March 11, 2010 at 4:01 am
arr.nagaraj (3/11/2010)
Workarounds do work. But they are temporary and the issue can re occur on the same database and also on some other databases.
Nonsense. The issue, workarounds, and permanent fixes are perfectly clear.
I would encourage you to check what product versions you are actually running.
If you struggle with this task, hire a professional consultant.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 11, 2010 at 4:07 am
arr.nagaraj (3/11/2010)
One thing I fail to understand is your questions about my digression are ok. But my replies arent. 🙂 . No problem. I have met people like this, and I am sure I will.
It might pay you to be less sensitive. I am just saying that I am not interested in the details of how you run your databases. I might do things differently, but none of that is relevant to the article. You brought it up, as a digression. I have read what you have to say, and made my comments. You will meet people in life who don't agree with you, and maybe know more than you do.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 11, 2010 at 4:24 am
It might pay you to be less sensitive.
Genuinely agree. Thanks for the advice.
I am just saying that I am not interested in the details of how you run your databases. I might do things differently, but none of that is relevant to the article. You brought it up, as a digression. I have read what you have to say, and made my comments. You will meet people in life who don't agree with you, and maybe know more than you do.
Agreed. I was replying to the question you raised and I felt my reply was certainly relevant to your question. Let us agree to disagree on this one.
Thanks.
Regards,
Raj
March 11, 2010 at 4:41 am
You are welcome. 😎
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 11, 2010 at 4:44 am
Nonsense. The issue, workarounds, and permanent fixes are perfectly clear.
I would encourage you to check what product versions you are actually running.If you struggle with this task, hire a professional consultant.
Product versions are fine (sp2 + cu 11) as said already. The steps I mentioned are performed as a precautionary measure. Thanks for your help. 🙂
Regards,
Raj
March 11, 2010 at 6:48 am
Nice article Jason. I'll be interested in what you find from Event Notifications.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 11, 2010 at 7:24 am
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
March 11, 2010 at 7:54 am
Jason,
Thanks for a good article, I enjoyed reading it.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
Viewing 15 posts - 16 through 30 (of 48 total)
You must be logged in to reply to this topic. Login to reply