Vault database transaction log getting extrememly large!

lawrep · Post by **lawrep** » Wed May 19, 2004 8:33 am

After importing VSS databases into our Vault server I discovered that the sgvault_log.ldf file in SQL2000 has grown to over 11GB.This has caused big issues with our server running out of space, which in turn caused an import to fail. After freeing up some space I noticed that the log file grew another GB after just logging on as admin user.
Is there any specific reason for the log file to grow so rapidly?
Also is there a way that I can reduce this file size? - I've tried 'shrinking' the log through SQL but without success.

Thanks

Post by **jclausius** » Wed May 19, 2004 8:49 am

Paul:

1) What version of Vault are you using? Have there been a lot (5000+) of checkouts / undo checkouts / checkins?

2) Have you tried to truncate the log?
- Make a complete BACKUP of the database.
- Execute 'BACKUP LOG sgvault WITH TRUNCATE_ONLY'
- Make a complete BACKUP of the database with the truncated log.

lawrep · Post by **lawrep** » Wed May 19, 2004 9:25 am

We are using Vault 2.0.2. There hasn't been that many checkouts etc. We have had to do about 5 VSS imports though, some of which had to be deleted and re-done due to some early problems. Just to add some more info the the actual sgvault database is about 5GB now.

I tried to do the backup of the transaction log through enterprise manager with the 'Remove inactive entires from transaction log' - is this the same? (Although this failed anyway due to more issues with space)

I'm probably being a bit stupid but where do you run the 'BACKUP LOG sgvault WITH TRUNCATE_ONLY' command from - can you do it through Enterprise manager? Sorry but I'm trying to learn all this as I go along mainly because our SQL expert is currently away.

lawrep · Post by **lawrep** » Wed May 19, 2004 9:38 am

We are using Vault 2.0.2. There hasn't been that many checkouts etc. We have had to do about 5 VSS imports though, some of which had to be deleted and re-done due to some early problems. Just to add some more info the the actual sgvault database is about 5GB now.

I tried to do the backup of the transaction log through enterprise manager with the 'Remove inactive entires from transaction log' - is this the same? Although this failed anyway due to more issues with space. Do you know if I could just back up the database, stop the services, delete the log file and then create a new one?

Post by **jclausius** » Wed May 19, 2004 10:37 am

No problem.

1) Start up Query Analyzer. You can find this in the SQL Server program group.
2) Login
3) Paste in the query.
4) Run the query (Query -> Execute; F5 or Alt-X)

Post by **jclausius** » Wed May 19, 2004 10:40 am

I see. Have you run any backups since removing the imported repositories? If not, that would explain the large log file.

One other thing. I noticed you're running Vault 2.0.2, and removing some repositories that didn't import to your liking. I would recommend upgrading to Vault 2.0.3 when you get a chance, create a dummy repository, and then delete the dummy repository. This will free up extra space within your database file.

lawrep · Post by **lawrep** » Mon May 24, 2004 9:03 am

Just to say that after repeated backups of the log and the database the transaction log has now reduced to 1GB - which is a bit more manageable.

Thanks for the help.