A Practical Guide to SharePoint 2013

A Practical Guide to SharePoint 2013
A Practical Guide to SharePoint 2013 - Book by Saifullah Shafiq

Wednesday, November 21, 2012

Truncate WSS_Logging DB


One day you try to access your SharePoint site and notice that site does not load and instead throws an error. You investigate the error and find out that it has something to do with the WSS_Logging database. It is a common problem and I have blogged about it before as well. I just cannot find that blog post anymore (smiles). I have changed three or four blogs over the last couple of years. Anyway, getting back to the point. If you are facing this issue, truncate your WSS_Logging DB and you should be good to go.

1.       Open your SQL UI. Right click WSS_Logging DB and go to Reports > Disk Usage OR Reports > Standard Reports > Disk Usage.


Figure 1: Disk Usage

2.       In the disk usage report, expand the second node to see current disk usage. It should be full.


 


3.       Right-click WSS_Logging B and select “New Query” and paste the following script in the window and then hit F5 to execute it. NOTE: This script was copied from the following link:


Author is Jonathan Bainbridge.

Script:

·         DECLARE @TableName AS VARCHAR(MAX)
DECLARE table_cursor CURSOR
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME LIKE '%_Partition%'
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQLText AS NVARCHAR(4000)

SET @SQLText = 'TRUNCATE TABLE ' + @TableName

EXEC sp_executeSQL @SQLText

FETCH NEXT FROM table_cursor INTO @TableName
END
CLOSE table_cursor
DEALLOCATE table_cursor
 

 

Different people have written different scripts to truncate the table. This one is a tested solution and works perfectly.

4.       After executing the query, go back to the disk usage report and now you should see that the database is empty. Open your SharePoint site and it should work.