SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Bert Carles / Monitoring on a Budget / Latest Posts

RE: Monitoring on a Budget

Christi Nicholson — Thu, 10 Feb 2011 15:05:54 GMT

Did you ever get the SSRS piece working? I would be interested in seeing how you got that piece to work.Thanks,Christi

RE: Monitoring on a Budget

Bob Griffin — Tue, 29 Jan 2008 14:54:22 GMT

Nice article. I just implemented the server stats portion of this today. It's working fine. I've been looking for a good historical performance monitor for my databases. Thanks!Rober

RE: Monitoring on a Budget

Garrick-249518 — Tue, 27 Nov 2007 18:58:41 GMT

Hi,One more thing to watch out. In the monitoringserver.sql, add in the use tempdb in the beginning of the script.use tempdb -- add in this go -- add in thisif exists (select * from tempdb.dbo.sysobjects where id = object_id(N'[dbo].[DayDBStat]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table tempdb.[dbo].[DayDBStat]GObecause the OBJECTPROPERTY function is in the context of current database. If the default database (of login) is not tempdb and for some reason the previous copy of DayDBStat table was not drop you will get the job fail complaining object (DayDBStat) already exists. Alternatively, if you don't want to set the context of tempdb then replace the entire block above with IF object_id('tempdb.[dbo].[DayDBStat]') IS NOT NULLbegin drop table tempdb.[dbo].[DayDBStat]endEnjoy!

RE: Monitoring on a Budget

giorgio-459258 — Tue, 27 Nov 2007 07:56:24 GMT

Wow, that's cool :)

RE: Monitoring on a Budget

Bert Carles — Tue, 27 Nov 2007 07:51:23 GMT

I started working on it this morning. I should have something available no later than mid-week.

RE: Monitoring on a Budget

giorgio-459258 — Tue, 27 Nov 2007 07:35:48 GMT

HiWhere's that so promised Excel spreasheet that you talked about? ;)The numbers in the tables don't mean anything without a proper explanation and Excel sheet as you've promised ;)

RE: Monitoring on a Budget

Bert Carles — Tue, 27 Nov 2007 06:37:50 GMT

That makes sense. I've run into that myself before.

RE: Monitoring on a Budget

Garrick-249518 — Tue, 27 Nov 2007 03:05:10 GMT

[quote][b]Bert Carles (11/20/2007)[/b][hr]Perhaps that restriction pertains to a #TEMP table? All I know is the DTS works as coded.[/quote]I finally figure it out.Somehow, the DTS Transform task won’t auto detect and populate the column names or types in the transformation tab unless it can find the physical table in the source.Here's the tricks, go to QA and connect to the source DB instance and run this: CREATE TABLE tempdb.[dbo].[DayDBStat] ( [SrvName] [varchar] (50) NOT NULL , [DBName] [varchar] (128) NOT NULL , [LogFileName] [varchar] (128) NOT NULL , [SampDate] [datetime] NOT NULL , [ReadQty] [bigint] NOT NULL , [WriteQty] [bigint] NOT NULL , [ByteReadQty] [bigint] NOT NULL , [ByteWriteQty] [bigint] NOT NULL , [IOStallMSQty] [bigint] NOT NULL ) ON [PRIMARY]GOthis will create a perm table in tempdb.Now go back to the DTS Transform task and type select * from tempdb.dbo.DayDBStatand click the transformation tab and da da... it works.

RE: Monitoring on a Budget

Garrick-249518 — Thu, 22 Nov 2007 02:57:06 GMT

[quote][b]Bert Carles (11/20/2007)[/b][hr]Perhaps that restriction pertains to a #TEMP table? All I know is the DTS works as coded.[/quote]I still getting error invalid object name 'tempdb.dbo.DayDBStat' at the same Transform Data Task when I click on the transformation tab. I couldn't understand why?My SQL2000 version is 8.00.2187.

RE: Monitoring on a Budget

Nolan Schoonraad — Thu, 22 Nov 2007 01:34:34 GMT

Thanks for the article Bert.I don't often use DTS so I went ahead and did the same thing in SSIS. I followed the article just as it is written without much hassle. The only real snag I had was to create a connection to the tempdb in the connection manager (this is interesting as I had never thought of it before and has helped solve another problem I was having). I have only tested it on my development server, but so far it seems to work great.

RE: Monitoring on a Budget

Bert Carles — Tue, 20 Nov 2007 05:27:37 GMT

Perhaps that restriction pertains to a #TEMP table? All I know is the DTS works as coded.

RE: Monitoring on a Budget

Garrick-249518 — Mon, 19 Nov 2007 21:03:52 GMT

I mean at source tab in the Transform Data Task for DB Stats monitoring with the TSQL:select * from tempdb.dbo.DayDBStat

RE: Monitoring on a Budget

Garrick-249518 — Mon, 19 Nov 2007 20:50:40 GMT

I thought you can't query temp table as source in Transform Data task as stated here[url=http://technet.microsoft.com/en-us/library/aa215269(SQL.80).aspx][/url][i]When using temporary tables in the Transform Data task, the Data Driven Query task, or the Execute SQL task in DTS Designer, be aware that you cannot use a Transact-SQL statement or stored procedure that calls a temp table as your source.[/i]

RE: Monitoring on a Budget

Bert Carles — Mon, 19 Nov 2007 10:40:47 GMT

I debated putting the Excel spreadsheet in the article. Ultimately, I decided not to since, once the raw data is in a table, you can present in pretty much any format you choose. I'm not very good with Excel either, but give me a few days, and after the holiday, I can post a "How I did it with Excel" follow up. I've also been working with a SQL 2005 equivalent. The internals are pretty much the same, but the presentation has much more potential with SSRS and SSAS...Perhaps another followup article?This was my first stab at technical writing. I've already learned a great deal from your responses, thank you! I'll definitely try to be more detailed in the future.

RE: Monitoring on a Budget

giorgio-459258 — Mon, 19 Nov 2007 10:23:22 GMT

That looks very nice BUT where is that Excel spreadsheet then? ;)It would be really nice if you posted here also so we can have a look at final product... :-)And to be honest I am not very good and Excel and all that graphs stuff so it would be rather nice to see how you're doing the whole "thing"Cheers

RE: Monitoring on a Budget

Bert Carles — Mon, 19 Nov 2007 10:21:55 GMT

You're right. I really need to spend some time converting this over to SQL 2005 and expanding it beyond SQL Server.

RE: Monitoring on a Budget

Steve Jones - SSC Editor — Mon, 19 Nov 2007 09:26:07 GMT

That calls for a part 2, Bert!:D

RE: Monitoring on a Budget

Bert Carles — Mon, 19 Nov 2007 05:52:05 GMT

To be quite honest, I hadn't thought of monitoring counters outside of SQL Server. You've sparked my interest.We did add code to track disk space when we were trying to justify additional SAN space. The overall process is the same; simply capture today's disk utilization and subtract yesterday's from it to get a daily increase/decrease.

RE: Monitoring on a Budget

K. Brian Kelley — Mon, 19 Nov 2007 03:47:10 GMT

This is a nice write-up. I know a few folks who could use this. On a related note, how are you moitoring the performance counters outside of SQL Server, such as CPU usage, pages/sec, etc.? Or are you strictly concentrating on SQL Server?

Monitoring on a Budget

Bert Carles — Sun, 18 Nov 2007 00:02:30 GMT

Comments posted to this topic are about the item [B]Monitoring on a Budget[/B]