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 |
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 |
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 | select id, closed_at |
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