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…