NS<product>Events not be cleaned down -- sql server notification services

J
9/15/2005 4:39:03 AM

Hi Joe,

I've just tried the manual clean up using NSVacuum, and that works fine, it
did 32 in 30 seconds. It's running at the moment, but has approx 900,000
quantums to clean out.

Thanks,
John

[quoted text, click to view]

Joe Webb
9/15/2005 5:57:20 AM

Hi John -

What happens when you disable the scheduled vacuumer and run it
manually?

I'm not suggesting this is a production-level resolution. But I'd like
to see whether the issue is in the vacuumer determining what to get
rid of, or whether the vacuumer is not running at all.

HTH...

--
Joe Webb
SQL Server MVP

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

[quoted text, click to view]

J
9/15/2005 6:00:08 AM

Hi Joe,

Answers to your questions :-

1. Yup, here's the section from the config file:

<Vacuum>
<RetentionAge>P1DT00H00M00S</RetentionAge>
<VacuumSchedule>
<Schedule>
<StartTime>21:00:00</StartTime>
<Duration>P0DT03H00M00S</Duration>
</Schedule>
</VacuumSchedule>
</Vacuum>

2. Yup :)

3. v2.0

4. I'll have to let you know that one tomorrow... I just checked, and the
State returned the time from todays manual run, and the Schedule said:

2, 01/01/1900, 21:00:00, 10800

Thanks,

J
9/15/2005 7:07:07 AM

Hi Joe,

No problem, we're in Southampton, England. So we shouldn't need to adjust
the time :)

I have a thoery as to why it doesn't look like it's cleaning down. It's
taking approx 1hr to clean down 200,000 events in our system. I'm wondering
if it is working, but we're generating more than it can clean down in the
specified time interval.

What do you think?

Thanks,
John

[quoted text, click to view]

Joe Webb
9/15/2005 7:32:18 AM

John -

Okay, so the schedule is the problem.

Forgive the simple questions, but sometimes it's best to start there.

You are specifying 900pm in UTC time right, not local times?

And you're running NSControl update after modifying the ADF?

What version are you running? v2.0 or 2005?

After the vacuumer was supposed to have run, what results do you get
from the following T-SQL statements?

SELECT * FROM dbo.NSVacuumSchedule
SELECT * FROM dbo.NSVacuumState

--
Joe Webb
SQL Server MVP

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

On Thu, 15 Sep 2005 04:39:03 -0700, "J" <J@discussions.microsoft.com>
[quoted text, click to view]

Joe Webb
9/15/2005 8:28:22 AM

Hi John -

Thanks for posting the ADF snippet!

Unless you live along the 0 degree line that more or less crosses
Greenwich, England, I don't think the vacuumer is running when you
think it should.

SQLNS keeps time in UTC, formerly known as GMT. To get local time from
UTC time, you need to add or substract a number of hours (depending on
the time zone) to/from the UTC. For example, the Central Standard Time
has a -6 hour offset from UTC. So if you live in say Chicago, you're
actually running the vacuumer at 300pm (2100 - 600 = 1500).

Could that be your issue?

BTW - here's a link to a site that talks about UTC and the offsets.
http://www.dxing.com/utcgmt.htm

HTH...

--
Joe Webb
SQL Server MVP

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Thu, 15 Sep 2005 06:00:08 -0700, "J" <J@discussions.microsoft.com>
[quoted text, click to view]

J
9/15/2005 8:48:38 AM

Hi,

I'm having trouble with the NS<product>Events table not being cleaned down.
Initially I thought it was because the vacuum event was clashing with the
backup procedure on the server, so I moved the vacuum event to a time 5
hours before hand. Unfortunatley it made no difference, and the number of
events in the table actually increased!

Has anyone got any ideas as to what maybe happening?

The retention age is set to 1 day, and the event will go off at 9pm for 3
hrs every day.

Many Thanks,
John

Joe Webb
9/15/2005 9:32:03 AM

Well, your timezone certainly makes like a bit more simple from this
point of view. :)

Anyway, your theory may very well be correct. Tomorrow morning, check
the result of this T-SQL statement to see how many events and
notifications were removed during vacuuming.

SELECT * FROM dbo.NSVacuumState

BTW - You can also run the

NSSnapshotApplications

stored procedure from the instance database to essentially get the
same info.

If you not cleaning as much as you'd like, you can specify a couple of
vacuuming periods - one before the backups and another after the
backups.

But, I wouldn't really expect the backups to interfere with vacuuming
if they are running simultaneously - other than further taxing the
system resources and making things run a bit slower.

HTH...

--
Joe Webb
SQL Server MVP

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

On Thu, 15 Sep 2005 07:07:07 -0700, "J" <J@discussions.microsoft.com>
[quoted text, click to view]

kate
9/15/2005 1:12:15 PM

hi,

Noticed you are in Southampton. We are just up the road from you in
Portsmouth, so if you want to compare notes on NS applications then let
me know. It is always useful to know more about what kind of apps are
being created.

Kate MBCS

Alert Technologies
http://www.alert-technologies.co.uk
Get your notification services implementation going in minutes not
weeks ......

J
9/19/2005 2:23:02 AM

Hi Joe,

Sorry for the delay, have been off sick.

I couldn't find the stored procedure you mentioned on our installation, so
just ran ther select queries instead...

Anyways, they did go off as normal and did remove quite a few recordas which
is good. I just don't think they're removing enough so we'll scheduling a
second clean up after the backup as you suggested. :)

I found out why the backup was upsetting the clean up as well, it's turns
out they stop all services fro running when it is performed! So that would
of upset it quite nicely.

Thanks for the help,

J
9/19/2005 2:24:04 AM

Hi Kate,

I may well take you up on the offer sometime :)

Thanks,
John

Joe Webb
9/19/2005 5:46:29 AM

John -

[quoted text, click to view]

No problem. Both report basically the same information. BTW - The
NSSnapshotApplications is in the Instance datatabase, not the
Application database.

[quoted text, click to view]

Ah...good! Thanks for posting the follow up!

[quoted text, click to view]

That would definitely upset the apple cart. :)

Cheers!

--
Joe Webb
SQL Server MVP

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

On Mon, 19 Sep 2005 02:23:02 -0700, "J" <J@discussions.microsoft.com>
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server notification services

sql server notification services : NS<product>Events not be cleaned down