Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Best way to create a weekly email report for SQL Server Expand / Collapse
Author
Message
Posted Monday, March 15, 2010 10:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 11, 2013 2:14 PM
Points: 31, 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.
Post #883102
Posted Monday, March 15, 2010 10:18 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 20, 2013 9:25 AM
Points: 381, Visits: 357


- 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!
Post #883115
Posted Monday, March 15, 2010 11:06 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 17,609, Visits: 15,468
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #883159
Posted Monday, March 15, 2010 10:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:32 AM
Points: 31,018, Visits: 15,457
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
Post #883534
Posted Tuesday, March 16, 2010 9:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 19, 2014 8:59 AM
Points: 23, Visits: 162
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
Post #884377
Posted Friday, March 19, 2010 2:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 19, 2013 11:53 AM
Points: 17, Visits: 559
Do you still help with this? I can share my script and .rdl if you want. Let me know.
Post #886719
Posted Wednesday, April 21, 2010 6:04 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:30 AM
Points: 403, Visits: 982
I do need your script. If possible post it
Post #907653
Posted Thursday, April 22, 2010 7:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 1,208, Visits: 3,194
Yes, I am interested too.
Post #908582
Posted Thursday, April 22, 2010 8:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 11, 2013 2:14 PM
Points: 31, 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.
Post #908637
Posted Thursday, April 22, 2010 10:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 19, 2013 11:53 AM
Points: 17, Visits: 559
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.


  Post Attachments 
Drive_Space_Report.zip (184 views, 8.06 KB)
Post #908852
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse