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.
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:
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
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
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
Please enter the end ticket number
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
or even if you feel a little adventurous, click on the
File > Open Database…
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):
select id, closed_at
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
menu and run
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.
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.
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
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.
Written by Stephen Moon
email: stephen at logicalmoon.com