Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Log Growing Pains Expand / Collapse
Author
Message
Posted Thursday, March 11, 2010 7:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 2,394, Visits: 18,029
Thanks Jason, I pulled some nice stuff from your article and now have a quick reference for log growth issues!
Post #881026
Posted Thursday, March 11, 2010 8:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
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
Post #881076
Posted Thursday, March 11, 2010 8:46 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:53 PM
Points: 9,928, Visits: 11,194
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #881079
Posted Thursday, March 11, 2010 9:01 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 4,609, Visits: 11,010
Very nice article, Jason! Added to my briefcase.

--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #881103
Posted Thursday, March 11, 2010 9:34 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:33 AM
Points: 1,676, Visits: 1,760
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
Post #881130
Posted Thursday, March 11, 2010 9:40 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:31 PM
Points: 17,964, Visits: 15,967
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.


@Jason,
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
Post #881133
Posted Thursday, March 11, 2010 9:41 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:31 PM
Points: 17,964, Visits: 15,967
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
Post #881135
Posted Thursday, March 11, 2010 9:42 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:31 PM
Points: 17,964, Visits: 15,967
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
Post #881137
Posted Thursday, March 11, 2010 9:43 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:31 PM
Points: 17,964, Visits: 15,967
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
Post #881139
Posted Thursday, March 11, 2010 9:45 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:31 PM
Points: 17,964, Visits: 15,967
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
Post #881140
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse