Thursday, March 1, 2012

How to reduce a huge MSSCrawlURLLog table

There is a stored procedure which deletes SharePoint Search logs in the WSS_Search database.
This procedure needs one parameter: the number of days to keep crawl log data. This value MUST NOT be NULL.

Exemple:

exec proc_MSS_CrawlReportCleanup 30

Below, the query returning the size of all databases:

SELECT  DB_NAME(DATABASE_ID) AS [DATABASE NAME],      
CAST(( CAST(SIZE * 8 AS FLOAT) ) / 1024 AS VARCHAR) + ' MB' AS [SIZE]
FROM    SYS.MASTER_FILES
order by CAST(SIZE * 8 AS FLOAT) desc

Another query returning the size of all tables for a given database:

DECLARE @table table(Id int IDENTITY(1,1), Name varchar(256))
INSERT INTO @table
SELECT b.name + '.'+ a.name FROM sys.tables a INNER JOIN sys.schemas b ON a.schema_id = b.schema_id
INSERT INTO @table
SELECT '-1'
DECLARE @result table( TableName varchar(256), TotalRows int, Reserved varchar(50), DataSize varchar(50), IndexSize varchar(50), UnusedSize varchar(50))
DECLARE @temp varchar(256)
DECLARE @index int
SET @index = 1
WHILE 1=1
BEGIN
SELECT @temp = Name FROM @table WHERE Id = @index
IF @temp = '-1'
BREAK
INSERT @result( TableName, TotalRows, Reserved, DataSize, IndexSize, UnusedSize)
EXEC sp_spaceused @temp
SET @index = @index + 1
END
SELECT db_name() as DB, c.name+'.'+b.name as [Table], a.* FROM @result a
INNER JOIN sys.tables b ON a.TableName = b.name INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
ORDER BY DB, TotalRows DESC

2 comments: