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)
DECLARE @start INT
SELECT @start = 1
-- you can insert a USE statement here if desired
WHILE @start <= 20
INSERT INTO #echistory
SELECT db_name(), COUNT(*), @start, (cast(floor(cast(getdate() as float )) as datetime) -@start)
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
SELECT * FROM #echistory
more to follow…