Clearing SSAudit Table

If a customer is using SQL Express, and they run out of space in their database (SQLExpress has a 10gb limit) or if they have something looping in GlobalAction that causes their SSAudit table to blow up and become massive, you can truncate that table, though all document history will be lost. If you wish to preserve some of the data, for example, saving everything within the last 90 days, you can run the below sql scripts to move what you want to preserve to a temp table, truncate everything in SSAudit, and then reimport the data you want to keep. You must rebuild sql indexes after you do this, as it will cause heavy fragmentation. Also note, before any SQL scripts are run, you should take a full backup of the database. Also note, if the ssAudit table is really large, you may need to run this script in chunks. To run in chunks, open several different script tabs in ssms. Run Part 1 in its own tab, wait for it to complete, then part 2 in a separate tab. Once Part 2 completes, Run Part 3 in the same tab you ran part 1, then Part 4 in the same tab as Part 2. Lastly, run Part 5 in the same tab as Parts 1 and 3.

Use [DBNAME]
SELECT IID, DocId, ArchiveID, UserName, [Action], [Date]
INTO #AuditBackup 
FROM SSAudit 
WHERE [Date] >= 'DATEYOUWANTTOUSE';

-- 2. Clear the original table
-- This clears the 10GB limit room. 

TRUNCATE TABLE SSAudit;

-- 3. Move the data back from the temp table
-- We enable IDENTITY_INSERT so your original 'IID' values are preserved
SET IDENTITY_INSERT SSAudit ON;

INSERT INTO SSAudit (IID, DocId, ArchiveID, UserName, [Action], [Date])
SELECT IID, DocId, ArchiveID, UserName, [Action], [Date]
FROM #AuditBackup;

SET IDENTITY_INSERT SSAudit OFF;

-- 4. Shrink the database to reclaim physical disk space
-- Replace 'YourDatabaseName' with the actual name of your DB
DBCC SHRINKDATABASE (N'YourDatabaseName');

-- 5. Cleanup
DROP TABLE #AuditBackup;