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, SQL LocalDB 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 is hosting your Lansweeper database by using the ConfigEditor tool. You can follow the instructions in this knowledge base article.
  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 LocalDB or SQL Server, 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 web server service in Windows Services.
    starting the web server service
  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 on the same page.
    deleting event log data
  10. If you're scanning performance information of Windows or Linux computers, lower the Delete performance counter data after XX days setting as much as possible on the same page. Performance data can take up a lot of database space as well.
    deleting performance data
  11. If you're scanning performance information of Windows or Linux computers, reduce the number of machines you're scanning performance data for under Scanning\Performance Scanning
    Performance Scanning menu
  12. Restart the Lansweeper service in Windows Services and wait a few minutes for the automated cleanup options to finish running.
    starting the Lansweeper service
  13. 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.
  14. If your Lansweeper database is hosted in SQL Compact and the performance or database size issues persist, moving your database to SQL LocalDB or SQL Server would be a good option as well. SQL LocalDB and SQL Server offer bigger databases and better performance.

Related Articles