Reducing the size of the SQL History database
jboyer
Posts: 12 Bronze 2
in SQL Prompt
I am looking for a way to trim the database based on 'server' or database. For exampl, delete all the history for server 'ThisServerIsDELETED'
Having used Sqlprompt for many years, I have some dust I need to 'clean'.
Having used Sqlprompt for many years, I have some dust I need to 'clean'.
Tagged:
Answers
@jboyer
Thank you for reaching out on the Redgate forums with your SQL Prompt / SQL History query.
There have been some recent changes with Tab History moving to the new SQL History functionality.
It includes some auto-trimming of historic versions of queries to improve performance and reduce file size of the database.
At present this is all automatic with no user interaction.
I am aware that there are some changes coming in future releases that will better trim the version history when there are no changes made and better tune the auto-trimming.
There are not any plans, currently, to allow for manual configuration of this trimming function.
If this is a feature that may be of use for you I would suggest making a request on our UserVoice page.
UserVoice is a feature request platform that allows users of our products to request features & functionality that is viewed directly by our product & development teams
https://redgate.uservoice.com/forums/94413-sql-prompt
As possible workarounds; it is possible to rename or move the sqlhistory.db file and have a new instance created.
You would lose the existing information in SSMS however the db file can be viewed by tools like SQLite Browser (https://sqlitebrowser.org/dl/) and then the new file would start fresh with new queries as you use it.
The sqlhistory.db file is found in C:\Users\<user>\AppData\Local\Red Gate\SQL Prompt 10\SqlHistory.db
To modify it first close SSMS, rename or move the file and then restart SSMS and SQL Prompt will build a new db file.
It may be possible to modify this db file using external tools, however we would suggest keeping a backup in case of corruption and that any changes made directly would be at your own discretion and risk.