Removing Tickets and Bloat from Spiceworks

Spiceworks has an SQLite database to store all the information about tickets, but use it for a while, and soon you will end up with a huge file which causes Spiceworks to slow down or even crash. The solution? Sweep out the old.

Getting Candidate Tickets to Delete

Firstly, let’s remove the unwanted (old, closed) tickets from the database (if you want to keep them, you can use the backup database in a second instance). To start, let’s find out what range(s) of ticket numbers you have.

You’ve got many options here but you could either:

  • Create/use a report in the GUI to show a list of closed tickets, ordered by ID. Then, take the smallest and greatest for your range. Or,
  • Go directly to the database backend (see DB Browser below).
  • Lastly, you could see which was the latest ticket ID (that would be your max) and then use the range: 1 to that max number.

I’m sure there are many other ways, but hopefully one of these will suffice. You should now either have a start an end number for the tickets, or a whole set of of them, if there were any gaps in the sequences.

Shut Down server

We’re going to shut down Spiceworks next and to do that you will need an administrator shell on the server that your installation resides. Start this and then type the following command:

> net stop spiceworks

This should fairly quickly stop the server. By the way, there are methods you can use which use the GUI, but ultimately, the command line is best in my opinion because you are closer to the core commands that are often really run, anyway, and don’t have to contend with any HTTP timeouts etc.

OK, the server has been removed from the running processes. Let’s now go to the directory Spiceworks is installed in, and there, you should see a folder named db. Take a copy of that and store it somewhere safe - it contains the SQLite database file, backups and other files which you may need if things turn sour.

Ticket Deletion

You might have seen that in the main folder there is a file named ticket_delete.exe; that’s the command which is going to do all the work for us so let’s move onto that. Run this command next:

> ticket_delete

Notice that there are no command line options (which is a shame) so you will now be using this tool interactively. After some possible errors (see below in the Gotchas), you should view something like this:

Spiceworks Ticket Loop Tool
---------------------------

1. Delete_single_ticket
2. Delete_ticket_range
3. Find_high_comment_tickets
4. Remove_comments_for_single_ticket
5. Remove_comments_from_mailer_daemons
6. Fix_User_Notifications
7. Close_ticket_range
8. Quit
Please make a choice

Here, type in 2 (since we are going to be removing a range of tickets). Now press enter. The next prompt is which ticket number to start at, then which ticket number to end with. Following that is a confirmation prompt (do check that carefully!) and finally, whether you should only remove closed tickets. I would definitely type c to this.

This is the output I got from running it for tickets between 1647 and 1650.

Please make a choice 2
Please enter the starting ticket number
1647
Please enter the end ticket number
1650
You will be deleting tickets 1647 to 1650 inclusive, correct?
Please enter [y/n]: y
Delete only (c)losed or (a)ll tickets in range? (closed keeps any open or waiting tickets in the range)
Please enter [c/a]: c
deleting closed tickets from 1647 to 1650 inclusive
Removing activities for ticket 1647
Removing activities for ticket 1648
Removing activities for ticket 1649
Removing activities for ticket 1650
found 4 tickets from range 1647 to 1650
Cleaned up activities tables
Disconnecting from database
Changes have been completed
press any key to return to the main menu

You need to do this for each range of tickets that you want to remove.

On our database which is about 450MB is size, it takes about 3 seconds to remove each ticket. That’s slow, I know, but this is definitely the safest option.

DB Browser (SQLite)

Right, onto a utility now. This is a great little tool which will allow you to interact with SQLite databases. You can download it here so go ahead and do that before installing it.

Opening the Database

So now that’s available, let’s run it and open up the database file. Press Control-O, or go to File > Open Database… or even if you feel a little adventurous, click on the Open Database button. Browse to the SQLite database file (spiceworks_prod.db on my system). and open it.

Listing Closed Tickets

Click on the Execute SQL tab (1) then paste this command into the editor (2):

1
2
3
4
5
select id, closed_at
from tickets
where status = 'closed'
and closed_at < '2018-09-01'
order by id desc

Now click the run command (3) and see the output (4).

You can see the format of the date - just change that to whatever you prefer. With this you can now scroll up and down to see the ticket ids you may want to remove.

Compacting the Database

One more useful thing. Let’s compact that database. To do this, simply go to the Tools menu and run Compact Database.

I suspect, under the hood, it is really doing using the vacuum command but either way, it achieves our aim.

Copy the resulting database file back into the source location should you need to.

Bringing Server Back Up

Almost done; now we just need to start the server again.

> net start spiceworks

Browse to your Spiceworks website after a few minutes and double-check everything is fine. Hopefully, you should now be ticket-less and the installation should be purring like a spicey Ferrari.

Gotchas

Before I end, it’s worth being aware that Spiceworks has it’s own server which is created when the Spiceworks service is run. That’s a little counter intuitive - you might think it’s in IIS but alas, no. Take a look at the running processes using the Task Manager to see what I mean; you should easily find it.

Running ticket_delete may list some errors about how how it can’t find a few executables as it starts up; that hasn’t led to any known problems for me, so ignore those.

Don’t be tempted to remove the tickets using the backend. Sure, you will find the ticket table and can create a query to remove those, but what about those pesky associated tables - is your knowledge of the schema good enough to ensure you have really removed all remnants of the ticket? Do you have time anyway? Are there any other clean-up tasks that you don’t know about? Play it safe and use ticket_delete.

Does it matter if you try and delete tickets that aren’t there? By that I mean, if you had tickets #1-#9, no #10, but #11-#20? I think you could just put the range 1-20 in and delete those and it will be fine.


Hi! Did you find this useful or interesting? I have an email list coming soon, but in the meantime, if you ready anything you fancy chatting about, I would love to hear from you. You can contact me here or at stephen ‘at’ logicalmoon.com