help emergency: updates

Showing posts with label updates. Show all posts

Friday, March 30, 2012

Icon in System Tray Flashing Red

MSSQL 7.0
All updates and service packs
NT 4 All updates and Service packs
I noticed something today that I never noticed before. In
the system tray, it shows MSSQL server with the green
arrow which indicates that it is running, but every 7
seconds or so, I get a red flash just below the green
arrow. More like a dot that flashes red at regular
intervals. I have never noticed this before and was
wondering if theis is normal. Everything seems to up and
running well.
Thanks
JohnThe red flashing dot is actually a sign that things are up and running. When
the dot flashes SQL Server Service Manager polls the Server to make sure it
is still up. You can change the polling interval inwhen you right click the
icon and choose Options.
--
Jacco Schalkwijk
SQL Server MVP
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:530501c3e4ea$6a3da190$a501280a@.phx.gbl...
> MSSQL 7.0
> All updates and service packs
> NT 4 All updates and Service packs
> I noticed something today that I never noticed before. In
> the system tray, it shows MSSQL server with the green
> arrow which indicates that it is running, but every 7
> seconds or so, I get a red flash just below the green
> arrow. More like a dot that flashes red at regular
> intervals. I have never noticed this before and was
> wondering if theis is normal. Everything seems to up and
> running well.
> Thanks
> John|||Thank you! I new it was something like that, I had just
never noticed it before :-)
>--Original Message--
>The red flashing dot is actually a sign that things are
up and running. When
>the dot flashes SQL Server Service Manager polls the
Server to make sure it
>is still up. You can change the polling interval inwhen
you right click the
>icon and choose Options.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"John" <anonymous@.discussions.microsoft.com> wrote in
message
>news:530501c3e4ea$6a3da190$a501280a@.phx.gbl...
>> MSSQL 7.0
>> All updates and service packs
>> NT 4 All updates and Service packs
>> I noticed something today that I never noticed before.
In
>> the system tray, it shows MSSQL server with the green
>> arrow which indicates that it is running, but every 7
>> seconds or so, I get a red flash just below the green
>> arrow. More like a dot that flashes red at regular
>> intervals. I have never noticed this before and was
>> wondering if theis is normal. Everything seems to up and
>> running well.
>> Thanks
>> John
>
>.
>

Icon in System Tray Flashing Red

quote:

> MSSQL 7.0
> All updates and service packs
> NT 4 All updates and Service packs
> I noticed something today that I never noticed before. In
> the system tray, it shows MSSQL server with the green
> arrow which indicates that it is running, but every 7
> seconds or so, I get a red flash just below the green
> arrow. More like a dot that flashes red at regular
> intervals. I have never noticed this before and was
> wondering if theis is normal. Everything seems to up and
> running well.
> Thanks
> John

|||Thank you! I new it was something like that, I had just
never noticed it before :-)

quote:

>--Original Message--
>The red flashing dot is actually a sign that things are

up and running. When

quote:

>the dot flashes SQL Server Service Manager polls the

Server to make sure it

quote:

>is still up. You can change the polling interval inwhen

you right click the

quote:

>icon and choose Options.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"John" <anonymous@.discussions.microsoft.com> wrote in

message

quote:

>news:530501c3e4ea$6a3da190$a501280a@.phx.gbl...
In[QUOTE]
>
>.
>

Wednesday, March 28, 2012

I/O tracking for a Sql Table?

I'm looking for a way to monitor I/O(select, delete, & updates) to a particular SQL Table for a period of time. Any suggestions?SQL Profiler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_86ib.asp)?

-PatP|||SQL Profiler would be usefull to you !|||Thanks for tip. I'm new to SQL Server 2000, but learning fast! Thanks againsql

Monday, March 26, 2012

I'm Stumped (Complex Joins With and Updates)

My head is spinning trying to figure this one out. Ok. Here is the schema...

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [snmpPoller_HostConfig](

[id] [int] IDENTITY(1,1) NOT NULL,

[friendlyName] [nvarchar](50) NOT NULL,

[hostname] [nvarchar](255) NOT NULL,

[community] [nvarchar](255) NOT NULL,

[version] [int] NOT NULL,

[queryTimeout] [int] NOT NULL,

[isActive] [bit] NOT NULL,

[passedSanityCheck] [bit] NOT NULL CONSTRAINT [DF_snmpPoller_HostConfig_passedSanityCheck] DEFAULT ((0)),

CONSTRAINT [PK_snmpPoller_HostConfig] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [snmpPoller_TargetConfig](

[id] [int] IDENTITY(1,1) NOT NULL,

[hostId] [int] NOT NULL,

[description] [nvarchar](50) NOT NULL,

[oid] [nvarchar](255) NOT NULL,

[interval] [int] NOT NULL,

[lastPollTimestamp] [datetime] NOT NULL,

[nextPollTime] AS (dateadd(second,[interval],[lastPollTimestamp])),

[isActive] [bit] NOT NULL,

[isPolling] [bit] NOT NULL,

CONSTRAINT [PK_snmpPoller_TargetConfig] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

ALTER TABLE [dbo].[snmpPoller_TargetConfig] WITH CHECK ADD CONSTRAINT [FK_snmpPoller_TargetConfig_snmpPoller_HostConfig] FOREIGN KEY([hostId])

REFERENCES [snmpPoller_HostConfig] ([id])

Ok. So I have this query

WITH WorkTable ( Tag, Parent, [Host!1!hostId],

[Target!2!targetId], [Host!1!hostname], [Host!1!community],

[Host!1!version], [Host!1!timeout], [Target!2!oid], [Target!2!interval]

)

(

SELECT DISTINCT

1 AS Tag,

NULL AS Parent,

H.id AS [Host!1!hostId],

NULL AS [Target!2!targetId],

hostname AS [Host!1!hostname],

community AS [Host!1!community],

version AS [Host!1!version],

queryTimeout AS [Host!1!timeout],

NULL AS [Target!2!oid],

NULL AS [Target!2!interval]

FROM snmpPoller_HostConfig H, snmpPoller_TargetConfig T

WHERE H.id = T.hostId

AND t.isPolling = 0

AND t.isActive = 1

AND h.isActive = 1

AND t.lastPollTimestamp < { fn NOW() }

AND h.passedSanityCheck = 1

UNION ALL

SELECT DISTINCT

2 AS Tag,

1 AS Parent,

hostId AS [Host!1!hostId],

T.id AS [Target!2!targetId],

NULL AS [Host!1!hostname],

NULL AS [Host!1!community],

NULL AS [Host!1!version],

NULL AS [Host!1!timeout],

oid AS [Target!2!oid],

interval AS [Target!2!interval]

FROM snmpPoller_HostConfig H, snmpPoller_TargetConfig T

WHERE H.id = T.hostId

AND t.isPolling = 0

AND t.isActive = 1

AND h.isActive = 1

AND t.lastPollTimestamp < { fn NOW() }

AND h.passedSanityCheck = 1

)

SELECT * FROM WorkTable

ORDER BY [Host!1!hostId], [Target!2!targetId]

FOR XML EXPLICIT, ROOT ('Hosts')

That generates this xml

<Hosts>

</Host>

</Host>

</Hosts>

Now the problem is that the xml that I generate is to ‘big’. When I submit a get request to the host, I can only fit 128 OIDs into the request PDU. My query, as it is, returns many hosts with upwards with 1000 targets. So question number 1 is how do return the top 128 targets for each host?

Now here is the real problem. I am replacing a working design in an effort to pick up a scale point. The current design has a dispatcher that will hand off the hostId to a work queue where it is picked up by a ‘thread’. This thread will then execute this sql.

SqlCommand^ command = connection->CreateCommand();

command->Parameters->AddWithValue("@.hostId", hostId);
command->CommandText =

"DECLARE @.resultTable TABLE("+

" targetId int NOT NULL,"+

" hostId int NOT NULL,"+

" oid nvarchar(255) NOT NULL"+

")"+

""+

"UPDATE TOP(128) snmpPoller_TargetConfig"+

" SET isPolling = 1 "+

" OUTPUT INSERTED.id, INSERTED.hostId, INSERTED.oid INTO @.resultTable"+

"WHERE (isPolling = 0) AND (isActive = 1) AND (id = @.hostId) AND (lastPollTimestamp < { fn NOW() })"+

""+

"SELECT * FROM @.resultTable";

SqlDataReader^ reader = command->ExecuteReader();

So rather than make (host * (number of targets / 128)) calls per dispatch cycle, I need to make it all in one call.

So SQL gurus…. What’s the kung-fu? How do I get all my hosts with the top 128 targets AND set a flag on all targets in one atomic shot (I believe this ‘one-shot’ statement can be built off the with(), but I’m nothing but confused at this point)

Paul

Need SQL Advice? http://sqladvice.com
Need RegEx Advice? http://regexadvice.com
Need XML Advice? http://xmladvice.com

is it possible to use ROW_NUM() ranking function within your query and then get the outputs based on row num. Also if you paste your sample data it shall aid the understanding|||

I actually just came up with this. It works just fine, but I'd like to consolidate it down to one statement. Any thoughts?

BEGIN TRANSACTION

DECLARE @.buffer TABLE

(

Tag int,

Parent int,

[Host!1!hostId] int,

[Target!2!targetId] int,

[Host!1!hostname] nvarchar(255),

[Host!1!community] nvarchar(255),

[Host!1!version] int,

[Host!1!timeout] int,

[Target!2!oid] nvarchar(255),

[Target!2!interval] int

);

WITH WorkTable ( Tag, Parent, [Host!1!hostId],

[Target!2!targetId], [Host!1!hostname], [Host!1!community],

[Host!1!version], [Host!1!timeout], [Target!2!oid], [Target!2!interval]

)

(

SELECT DISTINCT

1 AS Tag,

NULL AS Parent,

H.id AS [Host!1!hostId],

NULL AS [Target!2!targetId],

hostname AS [Host!1!hostname],

community AS [Host!1!community],

version AS [Host!1!version],

queryTimeout AS [Host!1!timeout],

NULL AS [Target!2!oid],

NULL AS [Target!2!interval]

FROM snmpPoller_HostConfig H, snmpPoller_TargetConfig T

WHERE H.id = T.hostId

AND t.isPolling = 0

AND t.isActive = 1

AND h.isActive = 1

AND t.lastPollTimestamp < { fn NOW() }

AND h.passedSanityCheck = 1

UNION ALL

SELECT DISTINCT

2 AS Tag,

1 AS Parent,

hostId AS [Host!1!hostId],

T.id AS [Target!2!targetId],

NULL AS [Host!1!hostname],

NULL AS [Host!1!community],

NULL AS [Host!1!version],

NULL AS [Host!1!timeout],

oid AS [Target!2!oid],

interval AS [Target!2!interval]

FROM snmpPoller_HostConfig H, snmpPoller_TargetConfig T

WHERE H.id = T.hostId

AND t.isPolling = 0

AND t.isActive = 1

AND h.isActive = 1

AND t.lastPollTimestamp < { fn NOW() }

AND h.passedSanityCheck = 1

AND t.id IN(

SELECT TOP(128) id FROM snmpPoller_TargetConfig

WHERE isPolling = 0 AND isActive = 1 AND lastPollTimestamp < { fn NOW() }

AND hostId = h.id

)

INSERT INTO @.buffer

SELECT * FROM WorkTable

UPDATE t SET isPolling = 1

FROM snmpPoller_TargetConfig t,

@.buffer b

WHERE

t.id = b.[Target!2!targetId]

SELECT * FROM @.buffer

ORDER BY [Host!1!hostId], [Target!2!targetId]

FOR XML EXPLICIT, ROOT ('Hosts')

COMMIT TRANSACTION

Sorry, I can't post the data, the schema should be suffecient. Just say there for 100 hosts records and 750 target records per host.

|||

Please take a look at the OUTPUT clause. You can just perform the UPDATE statement and get the relevant columns (even expressions) from the inserted/deleted tables into a table variable for example. You can then do the SELECT against the table variable. Please take a look at my blog post below for one such example:

http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx

Friday, March 30, 2012

Icon in System Tray Flashing Red

Icon in System Tray Flashing Red

Wednesday, March 28, 2012

I/O tracking for a Sql Table?

Monday, March 26, 2012

I'm Stumped (Complex Joins With and Updates)

help emergency

Blog Archive

About Me