We are looking at ways to reclaim some space as the Purge function is not working in Vault yet and we have no idea when this will be fixed so...
I was going through anything I could uninstall and delete etc and then I noticed something and I am not sure if this is supposed to be like this but the Vault DB is 13.4 Gb in size. The KnowledgeVaultMaster.mdf db is only 70Mb but the KnowledgeVaultMaster_log.ldf (located in C:\Program Files\Microsoft SQL Server\MSSQL10.AUTODESKVAULT\MSSQL\DATA) is 11.6 Gb in size!. Is this not just a log file and is there no way to reduce its size?
I did run a Vault mainetenance script as per the advanced config guide but nothing changed really...
Our Vault 2013 stats are as follows:
Number of files: 694553
Database Size: 13.4 Gb
Filestore size: 513.75 Gb
Largest version: 414
AVG number of versions: 5.2
Total size of Vaults (SQL Databases): 24.51 Gb - This is what got me looking as the Vault DB size is only 13.4Gb
Anyway any advice would be appreciated!
It sounds like the recovery model for the knowledge master may be incorrect. It should be set to simple - otherwise you can experience log growth as described. Here are some steps for checking the recovery model of the database (from sql management studio):
Thanks for feedback. After running this query I get Model: Full
SELECT name, recovery_model_desc
WHERE name = 'model' ;
Do I need to change it? How do I change it?
Yes - you will want to change the recovery model to simple in order to shrink the log file. Here is a sql query to do this:
ALTER DATABASE KnowledgeVaultMaster SET RECOVERY SIMPLE
You may need to perform a backup before the log shrinking takes affect.
Thanks for the response.
May I ask how this could have happened or got to this state in the first place? Is there a step during installation where this can be set?
Ok and then lastly are you saying that I need to actually start a backup of Vault using the ADMS console, for the shrinking to kick in? Am I understanding you correctly?
Ok so all I need to do is run the query from SQL management studio. Would I need to do a backup first?
Normally when we create the databases, this option is set - however this wasn't always the case in earlier versions of the product. Possibly when the database was migrated the knowledge master may have been overlooked. In any case, once you run the query you should be good from that point on.
So my recommendation would be to run the query. Then (when convenient) run the ADMS console backup - once the backup is run you should see a reduction in the log file size. There should be no need to run a backup before running the query.
Hope this helps,
Ok the backup just finished successfully and the log file is still the same size. Does it take a while or should it reduce in size immediately?
My understanding is that the log should have reduced size immediately. From the link I posted earlier:
After switching to the simple recovery model
Discontinue any scheduled jobs for backing up the transaction log.
Ensure periodic database backups are scheduled. Backing up your database is essential both to protect your data and to truncate the inactive portion of the transaction log.
I am very puzzled as to why changing the recovery model and subsequent backup wouldn't have reduced the log size.
We could try running a shrink on the log (see the sql management studio steps in the following link). However based on the previous article and information in this link, manually shrinking the log is not normally necessary.
Access a broad range of knowledge to help get the most out of your products and services.