How to cleanup WebInspect database for the top tables consuming large amount of storage

Question

We're experiencing storage used-up-issue for the MS SQL Server (used by WebInspect). Currently, the top tables which consume most of the storage are: 
 "Session", "SessionComplationID" and "ScanStatistics" 
 Is it possible we can have a SQL statement to delete the old records (like older than one month) from those tables?

Ethan Bell · Answer

Historically the SessionParameter table will be the largest because it stores ALL session data recorded during a scan. This table is used for tracking the parameters of a session. 
 Here is a query that will help you determine which scans have the largest number of sessions. These scans have the largest number of sessions (not necessarily largest regarding volume) to target for deletion or export is: 
 SELECT ScanID, COUNT(SessionID)AS "# of Sessions" FROM dbo.SessionParameter GROUP BY ScanID ORDER BY "# of Sessions" DESC 
 Here is an example result from my lab. 
 
 As Hans mentioned, the recommendation is to delete any scans not needed. If you need to keep all scan for archival or auditing purposes, you can either: 
 
 Export the scan(s) and store them in cold storage 
 Create multiple databases based on a set period (month, quarter, year) and only have the latest active on the scanner(s) with the others stored in cold storage.

Have you looked at shrinking the database. The way MSSQL works is it will hold/reserve the &ldquo;available&rdquo; space even after deleting data. There are some pros and cons to shrinking a database. Please review https://am2.co/2016/04/shrink-database-4-easy-steps/#:~:text=FROM%20sys.database_files%20dbf%20LEFT%20JOIN%20sys.filegroups%20fg%20ON,decide%20to%20move%20on%20and%20shrink%20the%20files . 
 
 This site lists how to shrink the database 
 It also contains a query that will give you the amount of space that &ldquo;may&rdquo; be recovered (FreeSpaceMB) after executing the shrink command. 
 Here is a link to a very informative blog post by Brent Ozar (MSSQL Expert) on https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/ regarding shrinking the database

Menu

Fortify Discussions

How to cleanup WebInspect database for the top tables consuming large amount of storage

Tags:

Top Replies

Resources