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


Tracking Database File AutoGrowth with Event Notifications


Tracking Database File AutoGrowth with Event Notifications

Author
Message
Jonathan Kehayias
Jonathan Kehayias
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13051 Visits: 1819
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
ALZDBA
ALZDBA
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60083 Visits: 9186
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


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont 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 :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
John Esraelo-498130
John Esraelo-498130
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2879 Visits: 1030
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
CarlDemelo
CarlDemelo
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 92
Very informative and well written Jonathon. Your articles are always spot on and a true value.

Thanks

Carl
John Esraelo-498130
John Esraelo-498130
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2879 Visits: 1030
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
sugnu iu
sugnu iu
Right there with Babe
Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)

Group: General Forum Members
Points: 737 Visits: 559
is the duration in milliseconds?. very interesting article!! bravo!
Neha05
Neha05
SSC Eights!
SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)

Group: General Forum Members
Points: 828 Visits: 60
Nice article.
quackhandle1975
quackhandle1975
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5867 Visits: 1244
Another one for the briefcase, this knowledge makes the life of the Prod DBA a lot easier! Satisfied

Thank you.

qh

Who looks outside, dreams; who looks inside, awakes. – Carl Jung.
hawg
hawg
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2752 Visits: 892
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
PFlorenzano-641896
PFlorenzano-641896
Right there with Babe
Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)

Group: General Forum Members
Points: 724 Visits: 602
Hello Jonathan,

Thank you for providing this article. I have a question on how to add this functionality to an 'existing' database within my organization.

For instance, the code you provided within the article works great when creating a new database, but how would this work with an existing database? I've enabled service broker on the database, but for some reason, no email are coming through when running an execution loop test.

Thanks,
Pete
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