Monitoring on a Budget

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Bert Carles » Monitoring on a Budget

20 posts, Page 2 of 2

««1 2

Monitoring on a Budget

Rate Topic

Display Mode

Topic Options

Author

Message

Nolan Schoonraad

Posted Thursday, November 22, 2007 1:34 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 267, Visits: 452

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.

Post #424867

Garrick-249518

Posted Thursday, November 22, 2007 2:57 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:51 PM
Points: 42, Visits: 612

Bert Carles (11/20/2007)

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

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.

Post #424898

Garrick-249518

Posted Tuesday, November 27, 2007 3:05 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:51 PM
Points: 42, Visits: 612

Bert Carles (11/20/2007)

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

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]
GO

this will create a perm table in tempdb.

Now go back to the DTS Transform task and type

select * from tempdb.dbo.DayDBStat

and click the transformation tab and da da... it works.

Post #426247

Bert Carles

Posted Tuesday, November 27, 2007 6:37 AM

SSCommitted

Group: General Forum Members
Last Login: Thursday, July 31, 2008 9:29 AM
Points: 1,800, Visits: 154

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

Take care,

Bert

"Speculations? I know nothing about speculations. I'm resting on certainties. I know that my Redeemer lives, and because He lives, I shall live also." - Michael Faraday

Post #426346

giorgio-459258

Posted Tuesday, November 27, 2007 7:35 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, January 03, 2011 9:45 AM
Points: 13, Visits: 49

Hi
Where'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 ;)

Post #426375

Bert Carles

Posted Tuesday, November 27, 2007 7:51 AM

SSCommitted

Group: General Forum Members
Last Login: Thursday, July 31, 2008 9:29 AM
Points: 1,800, Visits: 154

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

Take care,

Bert

"Speculations? I know nothing about speculations. I'm resting on certainties. I know that my Redeemer lives, and because He lives, I shall live also." - Michael Faraday

Post #426389

giorgio-459258

Posted Tuesday, November 27, 2007 7:56 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, January 03, 2011 9:45 AM
Points: 13, Visits: 49

Wow, that's cool :)

Post #426397

Garrick-249518

Posted Tuesday, November 27, 2007 6:58 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:51 PM
Points: 42, Visits: 612

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 this

if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'[dbo].[DayDBStat]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table tempdb.[dbo].[DayDBStat]
GO

because 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 NULL
begin
drop table tempdb.[dbo].[DayDBStat]
end

Enjoy!

Post #426672

Bob Griffin

Posted Tuesday, January 29, 2008 2:54 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:51 PM
Points: 1,132, Visits: 664

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

Post #449104

Christi Nicholson

Posted Thursday, February 10, 2011 3:05 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:01 AM
Points: 18, Visits: 170

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

Thanks,
Christi

Post #1062335

« Prev Topic | Next Topic »

20 posts, Page 2 of 2

««1 2

Permissions