Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Monitoring on a Budget


Monitoring on a Budget

Author
Message
Nolan Schoonraad
Nolan Schoonraad
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 565
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.
Garrick-249518
Garrick-249518
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
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.
Garrick-249518
Garrick-249518
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
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.
Bert Carles
Bert Carles
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1800 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
giorgio-459258
giorgio-459258
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 49
Hi
Where's that so promised Excel spreasheet that you talked about? Wink
The numbers in the tables don't mean anything without a proper explanation and Excel sheet as you've promised Wink
Bert Carles
Bert Carles
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1800 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
giorgio-459258
giorgio-459258
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 49
Wow, that's cool Smile
Garrick-249518
Garrick-249518
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
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!
Bob Griffin
Bob Griffin
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1167 Visits: 702
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
Christi Nicholson
Christi Nicholson
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 247
Did you ever get the SSRS piece working? I would be interested in seeing how you got that piece to work.

Thanks,
Christi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search