Re: MSSQL$SBSMONITORING - problem since KB948110 install
- From: Chris <Chris@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 28 Jul 2008 09:52:02 -0700
Hi Duncan,
Many many thanks for posting a reply, running the SQL command in SQL
Management Studio has fixed the problem for me.
Regards
Chris
"Duncan McC" wrote:
In article <2FDC6BDC-065B-4F0E-9088-078F60492C27@xxxxxxxxxxxxx>,.
Chris@xxxxxxxxxxxxxxxxxxxxxxxxx says...
I have had SBS Monitoring and Reorting running fine for months but the last
week it has not produced a single daily performance report. The email is
sent but just says 'Page cannot be dispayed'. Same error when I try running
it in Server Management.
I've tried the suggested solutions such as restarting the server, which in
the past has resolved the issue, but still the same error. I'm sure it has
happened since installing the KB948110 update to MSDE 2000 SQL databases.
When I upgraded to R2, I recall the SBSMONITORING instance didn't support
SQL 2005, has this changed now? Can I run it from SQL 2005 instead?
If not, does anyone have any similar issues with this update?
Event Viewer shows an error (Event ID:1) just after 6am when email is due:
Server Status Report:
URL: http://localhost/monitoring/perf.aspx?reportMode=1&allHours=1
Error Message: The specified item could not be found in the database.
Stack Trace: at
Microsoft.UpdateServices.Internal.DatabaseAccess.AdminDataAccess.ExecuteSPGetComputerById(String id)
at
Microsoft.UpdateServices.Internal.BaseApi.ComputerTarget.GetById(String id)
at Microsoft.UpdateServices.Internal.BaseApi.UpdateEvent.GetComputer()
at
Microsoft.SBS.UpdateServices.DataProvider.GetUpdateInstallationCount(DateTime
start, DateTime end, String GroupName)
at
Microsoft.SBS.UpdateServices.StatusPage.Utility.GetInstalledItemCountByGroup(DataProvider dataProvider, WSUS_GROUPS groupID)
at Microsoft.SBS.UpdateServices.StatusPage.Utility.GetUpdateSettings()
at usage.frmPerf.renderReportWorker()
at usage.frmPerf.renderReport()
Any help much appreciated.
Thanks
Have you tried a database cleanup and optimise?
Can't seem to find the MS page I got the script from, but it was in the
WSUS area somewhere. Here's the sql script anyways...
USE SUSDB;
GO
SET NOCOUNT ON;
-- Rebuild or reorganize indexes based on their fragmentation levels
DECLARE @work_to_do TABLE (
objectid int
, indexid int
, pagedensity float
, fragmentation float
, numrows int
)
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @numrows int
DECLARE @density float;
DECLARE @fragmentation float;
DECLARE @command nvarchar(4000);
DECLARE @fillfactorset bit
DECLARE @numpages int
-- Select indexes that need to be defragmented based on the following
-- * Page density is low
-- * External fragmentation is high in relation to index size
PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(),
121)
INSERT @work_to_do
SELECT
f.object_id
, index_id
, avg_page_space_used_in_percent
, avg_fragmentation_in_percent
, record_count
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL,
'SAMPLED') AS f
WHERE
(f.avg_page_space_used_in_percent < 85.0 and
f.avg_page_space_used_in_percent/100.0 * page_count < page_count - 1)
or (f.page_count > 50 and f.avg_fragmentation_in_percent > 15.0)
or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0)
PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar
(20))
PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(),
121)
SELECT @numpages = sum(ps.used_page_count)
FROM
@work_to_do AS fi
INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and
fi.indexid = i.index_id
INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id =
ps.object_id and i.index_id = ps.index_id
-- Declare the cursor for the list of indexes to be processed.
DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do
-- Open the cursor.
OPEN curIndexes
-- Loop through the indexes
WHILE (1=1)
BEGIN
FETCH NEXT FROM curIndexes
INTO @objectid, @indexid, @density, @fragmentation, @numrows;
IF @@FETCH_STATUS < 0 BREAK;
SELECT
@objectname = QUOTENAME(o.name)
, @schemaname = QUOTENAME(s.name)
FROM
sys.objects AS o
INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE
o.object_id = @objectid;
SELECT
@indexname = QUOTENAME(name)
, @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END
FROM
sys.indexes
WHERE
object_id = @objectid AND index_id = @indexid;
IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR
(@fragmentation < 30.0)
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' +
@schemaname + N'.' + @objectname + N' REORGANIZE';
ELSE IF @numrows >= 5000 AND @fillfactorset = 0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' +
@schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)';
ELSE
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' +
@schemaname + N'.' + @objectname + N' REBUILD';
PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' +
@command;
EXEC (@command);
PRINT convert(nvarchar, getdate(), 121) + N' Done.';
END
-- Close and deallocate the cursor.
CLOSE curIndexes;
DEALLOCATE curIndexes;
IF EXISTS (SELECT * FROM @work_to_do)
BEGIN
PRINT 'Estimated number of pages in fragmented indexes: ' + cast
(@numpages as nvarchar(20))
SELECT @numpages = @numpages - sum(ps.used_page_count)
FROM
@work_to_do AS fi
INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and
fi.indexid = i.index_id
INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id =
ps.object_id and i.index_id = ps.index_id
PRINT 'Estimated number of pages freed: ' + cast(@numpages as
nvarchar(20))
END
GO
--Update all statistics
PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)
EXEC sp_updatestats
PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)
GO
--
Duncan
- References:
- Re: MSSQL$SBSMONITORING - problem since KB948110 install
- From: Duncan McC
- Re: MSSQL$SBSMONITORING - problem since KB948110 install
- Prev by Date: Re: Event 421 After Removing WSUS
- Next by Date: Application as service
- Previous by thread: Re: MSSQL$SBSMONITORING - problem since KB948110 install
- Next by thread: Re: MSSQL$SBSMONITORING - problem since KB948110 install
- Index(es):
Relevant Pages
|
