Tracking Database File AutoGrowth with Event Notifications

Question

Post reply

Tracking Database File AutoGrowth with Event Notifications

Jonathan Kehayias

One Orange Chip

Points: 26778
More actions
November 6, 2012 at 12:01 am

#270462

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[/url]

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

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

Learn to play, play to learn !

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[/url]

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

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

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

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 2

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 SSC Enthusiast Points: 151 More actions · Answer 3

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

Thanks

Carl

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 4

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 SSCommitted Points: 1594 More actions · Answer 5

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

Neha05 Default port Points: 1494 More actions · Answer 6

Neha05

Default port

Points: 1494

December 24, 2012 at 12:23 pm

#1571349

Nice article.

quackhandle1975 SSChampion Points: 11055 More actions · Answer 7

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

Thank you.

qh

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

hawg SSCertifiable Points: 6044 More actions · Answer 8

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!

----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

PFlorenzano-641896 SSCommitted Points: 1643 More actions · Answer 9

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

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 10

Hi Pete,

Here is a stored procedure that I use for notifying me on an existing database if any object is created, modified, etc.

Hope it helps and I understood your question correctly. Of course this USP needs to be part of the rest of object for notifications purposes.

(see attached)

And, thank you again Jonathan, I have been using this methodology database management, failed logins, autogrowth and DBuser Events..

Cheers,
John Esraelo

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 11

I went ahead and created a table in one of the databases and since the USP I attached earlier looks over the server then generated a notification right away and sent it to me.

-- =====================================================

to me

Audit DB Mgmt Event Occured::

ApplicationName Microsoft SQL Server Management Studio - Query

DatabaseName ADMIN

DBUserName dbo

EventSubClass Alter

HostName D003951

LoginName johne

NTDomainName

NTUserName

ObjectName dbo

OwnerName dbo

ServerName companySQL

SessionLoginName johne

StartTime 2013-12-11T12:28:17.190

Success 1

TextData create table removemenow (field1 int null)

Cheers,
John Esraelo

PFlorenzano-641896 SSCommitted Points: 1643 More actions · Answer 12

PFlorenzano-641896

SSCommitted

Points: 1643

December 11, 2013 at 1:44 pm

#1672749

Thank you John,

I'll give it a try.

Pete

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 13

Pete,

BTW:: I have all my objects pertaining this whole event notification process in my MSDB.. this way I don't have to worry about the permissions and restrictions.

just thought to share this with you

Cheers,
John Esraelo

PFlorenzano-641896 SSCommitted Points: 1643 More actions · Answer 14

I'm also utilizing MSDB, my issue is the notifications aren't working for existing databases, even though service broker is enabled on these database. Doesn't make sense to me, but I'll figure it out.