In a current project, we’ve been having problems with search indexing. By problems, I mean massive hairy problems.

We’re not using SharePoint search or FAST so the knowledge we have in that space can’t be brought to bear in this case.

I won’t bore you with all the background, but suffice it to say we wanted to understand on a content database by content database basis how many changes were taking place in a given 24 hour period so that this could be cross-referenced to indexing data for sanity checking purposes.

Although doable through PowerShell (this is a MOSS estate) it’s way simpler using some T-SQL against the content database we are interested in.

Usual caveats about “directly accessing the database” apply…

IF OBJECT_ID('tempdb..#echistory') IS NOT NULL DROP TABLE #echistory
CREATE TABLE #echistory (DBname SYSNAME, ECRows INT, DaysAgo INT, ECDate DATETIME)
GO

DECLARE @start INT
SELECT @start = 1

-- you can insert a USE statement here if desired

WHILE @start <= 20
BEGIN
INSERT INTO #echistory
SELECT db_name(), COUNT(*), @start, (cast(floor(cast(getdate() as float )) as datetime) -@start)
FROM [EventCache]
WITH (NOLOCK)
WHERE [eventtime] between (cast(floor(cast(getdate() as float )) as datetime) -@start) and (cast(floor(cast(getdate() as float )) as datetime) -(@start-1))
SELECT @start = @start + 1
END

SELECT * FROM #echistory

more to follow…