SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log Growing Pains


Log Growing Pains

Author
Message
Chad Crawford
 Chad Crawford
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

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

Group: General Forum Members
Points: 18076 Visits: 1737
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
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79488 Visits: 11400
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
Gianluca Sartori
Gianluca Sartori
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

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

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Oleg Netchaev
Oleg Netchaev
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3074 Visits: 1909
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 Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: General Forum Members
Points: 144003 Visits: 18651
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
Learn Extended Events

SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: General Forum Members
Points: 144003 Visits: 18651
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
Learn Extended Events

SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: General Forum Members
Points: 144003 Visits: 18651
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
Learn Extended Events

SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: General Forum Members
Points: 144003 Visits: 18651
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
Learn Extended Events

SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: General Forum Members
Points: 144003 Visits: 18651
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
Learn Extended Events

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