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

Monitoring on a Budget Expand / Collapse
Author
Message
Posted Thursday, November 22, 2007 1:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 21, 2014 1:33 AM
Points: 268, Visits: 518
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
Posted Thursday, November 22, 2007 2:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 24, 2014 7:34 PM
Points: 42, Visits: 713
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
Posted Tuesday, November 27, 2007 3:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 24, 2014 7:34 PM
Points: 42, Visits: 713
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
Posted Tuesday, November 27, 2007 6:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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
Posted Tuesday, November 27, 2007 7:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 3, 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
Posted Tuesday, November 27, 2007 7:51 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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
Posted Tuesday, November 27, 2007 7:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 3, 2011 9:45 AM
Points: 13, Visits: 49
Wow, that's cool :)
Post #426397
Posted Tuesday, November 27, 2007 6:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 24, 2014 7:34 PM
Points: 42, Visits: 713
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
Posted Tuesday, January 29, 2008 2:54 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 20, 2014 6:48 PM
Points: 1,136, Visits: 699
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
Posted Thursday, February 10, 2011 3:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 10:44 AM
Points: 18, Visits: 231
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 »

Add to briefcase ««12

Permissions Expand / Collapse