Clearing tables to free up space and improve performance

In general, the cleanup options found in the web console under Configuration\Server Options should keep the size of your Lansweeper database within limits. Cleanups are performed when the Lansweeper Server service is started and subsequently every 24 hours. If you're scanning a lot of Windows data however and storing a lot of historical information, a more radical cleanup of your Lansweeper database may at some point be required.

If Lansweeper is using more memory, processor resources or disk space than desired, or if web console or service performance is not like it used to be, clearing some of your largest database tables is recommended. Uncontrolled database growth will negatively impact performance and may even cause you to reach your database server's built-in size limit, which in turn can lead to errors like the ones below. (SQL Compact and SQL Server Express databases are limited in size by Microsoft.)

The database file is larger than the configured maximum database size. This setting takes effect on the first concurrent database connection only. [ Required Max Database Size (in MB; 0 if unknown) = 4091 ]
Could not allocate space for object <name of database object> in database 'lansweeperdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

To free up space in your database and improve performance, do the following:

  1. Determine which database server (SQL Compact or SQL Server) you're using by verifying whether the file below exists on the server hosting your database. If this file exists, you're using SQL Compact. If it doesn't, you're using SQL Server.
    Program Files (x86)\Lansweeper\SQLData\lansweeperdb.sdf
  2. Stop the Lansweeper Server service in Windows Services on all of your scanning servers.
    stopping the Lansweeper service
  3. Stop your web server service in Windows Services. Keep in mind that this will log everyone out of the console. Your web server service is either IIS Express or World Wide Web Publishing Service (IIS).
    stopping the web server service
  4. Run the following tool on your Lansweeper server:
    Program Files (x86)\Lansweeper\Tools\DatabaseMaintenance.exe
    running the Lansweeper Database Maintenance tool
  5. Hit the Truncate logs button, then Shrink and then Compact (if highlighted). Each operation may take a while to complete.
    This step will clear the tblNtlog and tblNtlogMessage tables in your database, deleting all Windows event log information from your database. Event log data generally takes up the most space. You can still scan new events afterwards.
  6. If your Lansweeper database is hosted in SQL Server and not SQL Compact, open the Script Execution tab of the tool and execute the script below. This will rebuild all indexes in the database. Keep in mind that this script may take a while to complete.
    execute PsysRebuildAllIndexes
  7. Restart the Lansweeper and web server services in Windows Services.
    starting the Lansweeper and web server services
  8. Scanning non-error events from your Windows computers' Event Viewers quickly increases the size of the database. Disable scanning of any event types you don't need in the following section of the web console: Configuration\Server Options
    Server Options menu
    disabling event types
  9. Lower the Delete eventlog entries after XX days setting as much as possible under Configuration\Server Options
    deleting event log data
  10. If your database is still too large or performance is not sufficiently improved, you can try running a database script to clear additional tables. You can view database table sizes in the last tab of the aforementioned DatabaseMaintenance tool and run scripts from this tool as well.
    Be careful when running database scripts! Consult the database dictionary, which explains what each table stores, and always create a database backup first. Don't run scripts if you're unfamiliar with SQL or don't know what a specific table stores.
  11. If your Lansweeper database is hosted in SQL Compact and the performance or database size issues persist, moving your database to SQL Server would be a good option as well. SQL Server offers bigger databases and better performance.

Related Articles