Sharepoint saves most data in SQL data files, so it’s important to have regular care for the SQL DB’s. Last time I have enormous big file of Search Crawl Store DB. For 2 site collections using together 36 GB data, the Crawl DB was 69 GB and that’s not normal.
What’s in Search Crawl SQL database anyway?
Inside are information about status, time etc. found by crawling.
average size for crawl = 0,046 * (content SQL DB)
So in my case it should be the Search Crawl DB big not more than 1,6 GB.
How to reduce size of Crawl Search SQL DB?
From SharePoint 2010 we can use Health Analyzer for reducing big sized Crawl Search DB. If you for any reason stop crawling scheduled job or remove crawled content then sometimes indexing generates too big search database.
Or it might be fragmented indices. In this case just found rule Search in Health Analyzer and set the rule, that will automatically defrag if many fragmented indices are found.
Search Crawl database often have plenty of empty space. If with defragmentation we didn’t get wanted result, than you can restore crawl db  in 2 different ways.
In SharePoint admin console:
- Central Administration found Search Administration
- Stop All Crawl in content Sources
- In Index reset undo Deactivate search alerts during reset in click on Reset Now
In SQL server:
- Found Search DB Crawl and open All Tasks – Shrink – Database: undo Reorganize files before releasing unused space
- Again for All Tasks – Shrink – Files: undo Release unused space files before releasing unused space
That’s it. After this procedure I can managed to reduce size of the db Search Crawl from 69 GB to 320 MB. It’s so more normal result and again plenty of space on SQL server disk.
Why this happens?
Crawl doesn’t like hard stop of its running job, so at restart creates many empty spaces.
Regular taking care of Search Crawl database and other SharePoint databases improves performance of SQL Server and SharePoint Server!