Tracking Database File AutoGrowth with Event Notifications

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jonathan Kehayias » Tracking Database File AutoGrowth with Event...

Tracking Database File AutoGrowth with Event Notifications

Rate Topic

Display Mode

Topic Options

Author

Message

Jonathan Kehayias

Posted Tuesday, November 06, 2012 12:01 AM

SSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742

Comments posted to this topic are about the item Tracking Database File AutoGrowth with Event Notifications

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs

Post #1381400

ALZDBA

Posted Tuesday, November 06, 2012 1:27 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:17 AM
Points: 6,862, Visits: 8,049

Great article, Jonathan.

For alerting and recording all auto grow of your databases, that is the way to go.

Just for the record, if readers are saving default trace files: they also capture db growth information.
Downside of using these trace files: you may miss one/some if the 20MB size is bering reached before your checking interval and they roll forward before you were able to copy them.

( and of course profiler being depricated ... )

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help

- How to prevent a sore throat after hours of presenting ppt ?

"press F1 for solution", "press shift+F1 for urgent solution" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #1381434

John Esraelo-498130

Posted Tuesday, November 06, 2012 1:11 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:10 PM
Points: 518, Visits: 920

Hi Johnathan,
I have been using a method that once I found on internet if now here at sqlservercentral and has helped me identifying certain elements and information on one or more DBs in my environment and notifying me via the DBMail separately ..

<code>
ALTER PROCEDURE [dbo].[USP_CollectDBInformation_Display]
@DBName nvarchar(32) = '_N/A'
AS
BEGIN
SET NOCOUNT ON;

select servername,
databasename,
sum(filesizemb) as FilesizeMB,
[Status],
RecoveryMode,
sum(FreeSpaceMB)as FreeSpaceMB,
sum(freespacemb)*100/sum(filesizemb) as FreeSpacePct,
InputDate,
year(InputDate) TheYear,
month(InputDate) TheMonth,
day(InputDate) TheDay,
datepart(qq, InputDate) TheQuarter
from dbinformation
where filesizemb > 0 and
databasename like
case @DBName
when '_N/A' then '%%'
else '%' + @DBName + '%'
end

group by servername,databasename, [Status], RecoveryMode, InputDate
END
</code>

and then the email of course
<code>

Exec msdb.dbo.sp_send_dbmail
@profile_name = 'LocalServer'
,@recipients = 'JohnE.sql@gmail.com'
,@query = ' your statement goes here '
,@Subject = 'DB Information'
,@Attach_query_result_as_file = 1;
</code>

Albeit, I like your method and definitely going to examine it in my test environment and I know that I am going to like it..
well done!

Cheers,
John Esraelo

Post #1381697

CarlDemelo

Posted Wednesday, November 07, 2012 10:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 12, 2013 4:52 PM
Points: 3, Visits: 42

Very informative and well written Jonathon. Your articles are always spot on and a true value.

Thanks

Carl

Post #1382088

John Esraelo-498130

Posted Wednesday, November 07, 2012 12:39 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:10 PM
Points: 518, Visits: 920

Jonathan Kehayias (11/6/2012)

Comments posted to this topic are about the item <A HREF="/articles/autogrowth/93229/">Tracking Database File AutoGrowth with Event Notifications</A>

Jonathan,
Tested the routine, not that I had doubts but had to follow the protocol in the environment of course, and like it very much.
Now, I have 2 methods for monitoring.
awesome..

Cheers,
John Esraelo

Post #1382135

sugnu iu

Posted Monday, November 12, 2012 3:09 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 8:32 AM
Points: 32, Visits: 467

is the duration in milliseconds?. very interesting article!! bravo!

Post #1383575

Neha05

Posted Monday, December 24, 2012 12:23 PM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, April 15, 2013 11:42 PM
Points: 424, Visits: 55

Nice article.

Post #1399968

quackhandle1975

Posted Saturday, December 29, 2012 11:07 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:53 PM
Points: 1,612, Visits: 591

Another one for the briefcase, this knowledge makes the life of the Prod DBA a lot easier! Satisfied

Thank you.

qh

SQL 2K acts like a spoilt child - you need to coax it round with lollipops.

Post #1401192

hawg

Posted Monday, February 11, 2013 1:54 PM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:51 AM
Points: 435, Visits: 509

Sorry that I'm a little late to the party but this was an excellent article to help me get this monitoring setup in our environment. It has already helped tremendously.

With that said, I was wondering if anyone has tried to capture more data than just what is in the article? In particular, I am looking the "Filename" column that is part of the DATA FILE AUTO GROW EVENT CLASS. According to tall of the documentation I can find, this is supposed to be the logical file name of the file being extended. Whenever I try to retrieve it, I get nothing.

Using the code similar to that in the article, I try to pull the Filename using the following:

@FileName = @message_body.value('(/EVENT_INSTANCE/Filename)[1]', 'varchar(500)')

This is not a problem for most databases but I came across a database today that fired this event and when I went to look at the files to make the appropriate changes, I found that it had several data files. I have no way of knowing which file grew.

Thanks!

----------------------------------------------------------

01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011

Post #1418625

« Prev Topic | Next Topic »

Permissions