Database size monitoring report

[web4fun]

I am currently running SQL Server 2005 on a Win2k3 server and need to accomplish the following:

1) Monitor and obtain a report periodically on the sizes of our Sharepoint databases on this server.

2) Have the report sent to me via e-mail (I understand that this can be done via SQL Mail)

Question is, does anyone know how to setup an automated job to accomplish what I've mentioned above?

Thanks in advance.

 

[ptheriault]

Here is an example of what you're looking for using database mail. You will have to change a few parameters.
SQL mail is a left over from 2000.

EXEC msdb.dbo.sp_send_dbmail     
    @profile_name = 'Default', --or name of your mail profile
    @recipients = 'youremail@yourdaim.com', 
    @query = 'SELECT *
              FROM [YourDB].dbo.sysfiles',
    @attach_query_result_as_file = 1,   
    @subject = 'Size Report'

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[web4fun]

Thank you Paul...I will give it a go. And thanks for correcting me regarding SQL Mail vs. Database Mail.

 

[ptheriault]

Np,
You can also put the report in a table instead of an attachment.

[COLOR=blue]DECLARE[/color] @tableHTML  NVARCHAR([COLOR=#FF00FF]MAX[/color]) ;

[COLOR=blue]SET[/color] @tableHTML =
    N[COLOR=red]'<H1>File Size Report</H1>'[/color] +
    N[COLOR=red]'<table border="1">'[/color] +
    N[COLOR=red]'<tr><th>File ID</th><th>Size</th>'[/color] +
    N[COLOR=red]'<th>Name</th><th></tr>'[/color] +
    [COLOR=#FF00FF]CAST[/color] ( ( [COLOR=blue]SELECT[/color] td = fileid,       [COLOR=red]''[/color],
                    td = [COLOR=blue]size[/color], [COLOR=red]''[/color],
                    td = [COLOR=blue]name[/color], [COLOR=red]''[/color]
              [COLOR=blue]FROM[/color] YOURDB.dbo.sysfiles
              [COLOR=blue]FOR[/color] XML PATH([COLOR=red]'tr'[/color]), [COLOR=blue]TYPE[/color] 
    ) [COLOR=blue]AS[/color] NVARCHAR([COLOR=#FF00FF]MAX[/color]) ) +
    N[COLOR=red]'</table>'[/color] ;

[COLOR=blue]EXEC[/color] msdb.dbo.sp_send_dbmail 
    @profile_name = [COLOR=red]'Default'[/color],
    @recipients=[COLOR=red]'you@yourdomain.com'[/color],
    @subject = [COLOR=red]'file sizes'[/color],
    @body = @tableHTML,
    @body_format = [COLOR=red]'HTML'[/color]

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[web4fun]

Fantastic...as always Paul you are a wealth of knowledge and always willing to share. I really appreciate it sir.

 

[ptheriault]

I'm glad I can help.


- Paul
- If at first you don't succeed, find out if the loser gets anything.