Best way to create a weekly email report for SQL Server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Best way to create a weekly email report for...

12 posts, Page 1 of 2

1 2 »»

Best way to create a weekly email report for SQL Server

Rate Topic

Display Mode

Topic Options

Author

Message

mirde

Posted Monday, March 15, 2010 10:02 AM

SSC 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

GTR

Posted Monday, March 15, 2010 10:18 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 3:15 PM
Points: 389, Visits: 351

- 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

SQLRNNR

Posted Monday, March 15, 2010 11:06 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 18,858, Visits: 12,443

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 2008

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

Steve Jones - SSC Editor

Posted Monday, March 15, 2010 10:02 PM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:09 AM
Points: 31,526, Visits: 13,864

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

gorachandindia 30156

Posted Tuesday, March 16, 2010 9:52 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, June 06, 2013 5:35 AM
Points: 23, Visits: 135

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

amc-885860

Posted Friday, March 19, 2010 2:19 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 31, 2013 10:55 AM
Points: 16, Visits: 558

Do you still help with this? I can share my script and .rdl if you want. Let me know.

Post #886719

balasach82

Posted Wednesday, April 21, 2010 6:04 AM

Old Hand

Group: General Forum Members
Last Login: Monday, June 10, 2013 6:23 PM
Points: 311, Visits: 757

I do need your script. If possible post it

Post #907653

Pei Zhu-415513

Posted Thursday, April 22, 2010 7:20 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:40 PM
Points: 1,053, Visits: 2,800

Yes, I am interested too.

Post #908582

mirde

Posted Thursday, April 22, 2010 8:16 AM

SSC 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

amc-885860

Posted Thursday, April 22, 2010 10:54 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 31, 2013 10:55 AM
Points: 16, Visits: 558

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. Wink

ps. I hope I attached the .RDL correctly.

Post Attachments

Drive_Space_Report.zip (163 views, 8.06 KB)

Post #908852

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions