How Monitor Sharepoint Search Service / Crawl Activity

Unfortunately, if the Sharepoint Search Service breaks or stops crawling sites, nobody finds out until a user complains that documents are missing from their searches. Until NOW!

Since there is no feedback generated when the Sharepoint 3.0/2007 Search Service fails, a problem that affects the daily (or hourly, etc.) crawl of the site will go unnoticed since the search function itself still works. However, since new documents and pages are not being crawled, the search function will be less accurate with each passing day. A status of "Started" on the "Windows SharePoint Services Help Search" service in the Central Administration console means nothing since it remains "Started" regardless of whether crawls are actually taking place.

Here's what can be done: Go to your SQL server (or Sharepoint server if they are one and the same) and add the following stored procedure to your search database (usually called WSS_SEARCH_SERVERNAME). This procedure will check the MSSCrawlHistory table:

---

CREATE PROC sp_notifyifnocrawl
AS
/* 4/3/2008 - Knowledge Pup - Confirms that there has been Crawl activity in last seven days. If not, e-mails recipient below */
DECLARE @messagehtml NVARCHAR(MAX) ;
IF (SELECT count(*) FROM msscrawlhistory
WHERE DATEDIFF(dd,requesttime,getdate()) < 3 /* crawl activity in last 3 days */
and status = 11 ) = 0 /* Status 11 = Completed crawl */
BEGIN
SET @messagehtml =
N'Sharepoint Search Warning' + CONVERT(char, GETDATE()) + '' +
N'No Sharepoint Search crawl activity in last 3 days' +
N'Please confirm that search service is still operational';
EXEC msdb.dbo.sp_send_dbmail @recipients='user_here@domain_here.com',
@subject = 'Sharepoint Problem: no search crawl activity',
@body_format = 'HTML',
@body = @messagehtml,
@profile_name = 'SQL_email_profile_here';
END

---

(Note that this example uses SQL 2005, and a different e-mail technique will need to be substituted for other versions)
Modify the line that defines "@recipients" to reflect the corrent e-mail address, and change the "@profile_name" line at the end to point to a SQL mail profile that has been previously created.

Next, create a job under SQL Server Agent that will run this stored procedure once a day. If the crawl process has been dead for over three days you will get an e-mail notifying you of this fact.

How to Purge the Sharepoint Search Database

If you need to stop and restart the Windows SharePoint Services Search Service in SharePoint 3.0/2007, you will be required to first completely purge the database and all stored procedures manually. Here's a quick way to do it.

When attempting to stop and restart the Windows SharePoint Services Search Service by simply clicking on "stop" and then "start" (seems reasonable), you will be confronted with this error after filling out the settings form:

WSS_Search_SERVERNAME on SERVER contains user-defined schema. Databases must be empty before they can be used. Delete all of the tables, stored procedures and other objects or use a different database.

This happens because SharePoint wants to build the search database from scratch after the service has been stopped. One solution is to just drop the database and re-create it, but that can be a big hassle if you have a separate database server in your SharePoint farm and everything is set up just the way you like it. It's hard to say why SharePoint wants to make such a big deal out of staring the service, but I'm sure there is a reasonable explanation. Anyway, here is a quick script you can run against the database after stopping it. This will delete all of the tables and stored procedures (there are no views). You will need to run it at least three times to take care of all of the table dependencies... and please make sure you run it against the right database or significant anguish could result!)

---

/* Delete tables and procedures from WSS_Search_* database
run this set of commands several times to ensure that all dependences removed
WARNING: wipes out tables and stored procedures! Make sure you run it against the right database!
Knowledge Pup - 2008 */
exec sp_msforeachtable "drop table ? print '? has been dropped.' "
declare @dropproc nvarchar(max)
declare @numprocs int
declare @pos int
declare @procs table (rowcnt int identity (1,1) primary key not null, pname varchar(100))
insert into @procs select name from sysobjects where xtype = 'P'
select @numprocs = count(*) from @procs
select @pos = 1
while @pos <= @numprocs
begin
select @dropproc = 'drop procedure ' + pname from @procs where rowcnt = @pos
execute sp_executesql @dropproc
select @pos = @pos + 1
end

---

Managing SharePoint 3.0 Alert Subscriptions

Sharepoint provides you with the handy function of subscribing to alerts generated when list content is added or changed. But what if you need to manage and review your alerts for the entire site?

In a calendar, documents library, or other list, you can choose to receive e-mail notifications for changed items by visiting the Actions menu and selecting "Alert Me”. Furthermore, if you want to modify your existing alerts, you can go to this same screen and then click on "View my existing alerts on this site". The problem comes when you, as an administrator of a site, want to review and manage everyone’s alerts.

Time to crack open Query Analyzer (or SQL Server Management Studio) and look at your content database (usually WSS_Content_SiteNameHere). The ImmedSubscriptions table houses all of the immediate alerts that have been set up by users, which you can delete or tweak if necessary. Here are some of the useful table columns:

Id: GUID for the alert
ListId: links to the AllLists table
UserId: links to the UserInfo table
UserEmail: email address of the alert recipient
SiteUrl, WebUrl, ListUrl: determines the location of the list to which the alert is associated
Alert Title: arbitrary name for the alert

Making SharePoint Links Open in a New Window

Out of the box, Sharepoint Links lists don't give you the ability to open links in a new window (i.e. no access to the "target" attribute of the link). But a quick SharePoint Designer (or FrontPage) trick can fix that.

If you need your links in a Links list to open in a new window, you can do the following:

- Open the view document for the list in Sharepoint Designer (e.g. /Lists/Links/AllItems.aspx)
- Right-click on the List View Web Part (WebPartPages:ListViewWebPart) and Click on the option that says, "Convert to XSLT Data View"
- Right-click on the text of one of the links and choose "Hyperlink Properties"
- Click the "Target Frame" button on the right and pick "New Window"
- Click OK and then OK!
- Save the document

You'll notice that this is an all-or-nothing fix: all present and future links on this page will open in a new window.