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


Best way to create a weekly email report for SQL Server


Best way to create a weekly email report for SQL Server

Author
Message
mirde
mirde
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 151
Hi,

I have been asked to create a automated "report" of some sort to provide some kind of statistics on our SQL Server 2005.

Basically, something we can take a look weekly to see how the server is, and growing trend.. something I would want to see is:

- Max user connections
- CPU %
- Disk Space Usage
- SQL Error Log (if any)

Anything else that anyone can suggest as a good metric to keep track of? Also, what would be the best way to archive this? Anyone have a similar thing running on their SQL box?

Thanks.
GTR
GTR
Say Hey Kid
Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)

Group: General Forum Members
Points: 711 Visits: 368
- Max user connections

I am not sure this possible

- CPU %

SCOM
- Disk Space Usage

SCOM or SQL 2008 Data collection or performance dash board
- SQL Error Log (if any)

Set up traces

EnjoY!
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32112 Visits: 18551
Backup summary.

Avg Disk Reads / Writes


SCOM could get you some of the info you need. Other options are to use PowerShell, SMO, tsql scripts to build a custom in-house app that could track and email this stuff.

Another option would be to use the dynamic management views to gather information, and then use SSRS, SSIS, or TSQL script to send the report.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62102 Visits: 19101
Short summary of how I used to do this

- Create small db on every instance to hold data.
- write scripts to gather metrics, separate table for each metric. Use a separate job to schedule each set of metrics as needed (some daily, some hourly, etc.)
- Put a report table in the db that just holds char fields, an instance name, and an ordering field
- Write a proc that "builds" a report, putting lines of data in the report table, ordering them as needed, and including the instance name. This gives you a report daily, on each server.
- Write a script (SMO/Powershell/LinkedServers/etc) to roll up all reports from all instances to one central server.

Have that central server email you the report, ordering by instance, and then ordering column.

It's flexible, you can add requirements as needed.

You can also do the final export in SSRS.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
gorachandindia 30156
gorachandindia 30156
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 242
You might have to monitor multiple server and multiple instances if this is true then

1. Create a centralize monitor server (SQL server with single instance)
2. Create a table and store the SQL server inventory detail as explained below
SQLInventory
1. ServerID
2. ServerName
3. InstanceID
4. InstanceName
5. SQLVer
6. Enable_monitoring

SQLInvDetail
1. InstanceID
2. DatbaseID
3 DatabaseName
4. Enable_monitoring

Create another table that will store the information about the server and instance resource
usage
InstResource
1. ServerID
2. InstanceID
3. Fromtime
4. Totime
5. CPUusage
6. MemUsgae

DbRessource
1.InstnaceID
2.DBID
3. File1
4. GrowthMB

3. Now Create a SQL job that will connect to each server and collect the information and stored them in the InstanceRessource and DBressource table

Note; You can enable and disable monitoring for a particular Instance and User by setting using Enable_monitoring

Schedule the job to run as you needed

Now we can use the SSRS to design your report.

Hopes this gives you some idea. Let me know if this works

Cheers,
Gorachand Mohapatra
amc-885860
amc-885860
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 560
Do you still help with this? I can share my script and .rdl if you want. Let me know.
balasach82
balasach82
SSC Eights!
SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)

Group: General Forum Members
Points: 821 Visits: 1080
I do need your script. If possible post it
Pei Zhu-415513
Pei Zhu-415513
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1618 Visits: 4083
Yes, I am interested too.
mirde
mirde
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 151
amc, that would be great if you can post yours.

I have postponed this actually, but need to have something ready by end of next week.
amc-885860
amc-885860
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 560
HI, sorry it so long to respond.
Actually, the how to create this Drive (disk) Space Report came from Sql Server Central.

Right here is the link:
http://www.sqlservercentral.com/scripts/Administration/67064/

And I attached my .RDL, so I hope this helps you guys.

It wasn't too hard at all. Just have to make alot of data sources to all the servers I wanted to monitor. Have fun. Let me know if you have any questions. ;-)

ps. I hope I attached the .RDL correctly.
Attachments
Drive_Space_Report.zip (229 views, 8.00 KB)
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