Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log Growing Pains


Log Growing Pains

Author
Message
Chad Crawford
 Chad Crawford
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2438 Visits: 18645
Thanks Jason, I pulled some nice stuff from your article and now have a quick reference for log growth issues!
Paul Randal
Paul Randal
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2161 Visits: 1714
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
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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 :-D



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
spaghettidba
spaghettidba
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5730 Visits: 13305
Very nice article, Jason! Added to my briefcase.

--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 1807
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
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21063 Visits: 18258
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

SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21063 Visits: 18258
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

SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21063 Visits: 18258
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

SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21063 Visits: 18258
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

SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21063 Visits: 18258
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search