SGVault DB is growing HUGE

tjams · Post by **tjams** » Tue Aug 29, 2006 1:11 pm

Hello,

We've been having issues connecting to Vault for the past couple of days. I logged on to our server and noticed that we were out of disk space on the server where the vault DB is hosted. It turns out that it's because of the Vault DB. sgvault.mdf is around 20 Gigs which seems about right. But the sgvault_log.ldf file is nearly 40 Gigs. Is there something in particular that can cause this or some periodic maintenance that we should be doing to avoid it?

By the way, we are running Vault 3.1.8 on SQl 2005.

Thanks,
Tom

Post by **jclausius** » Tue Aug 29, 2006 2:07 pm

Check your Recovery Model within SQL Server.

If you are not making nightly backups of the log file, place the sgvault database in simple recovery mode, but still make sure you make regular backups.

tjams · Post by **tjams** » Tue Aug 29, 2006 3:09 pm

Backing up the DB and truncating the logs was just the ticket...thanks.

However, it appears we are still having issues. I can connect to Vault and it seems to retrieve the repository structure fairly quickly, but when I attempt to actually perform an operation (for instance checking out a file) it takes several minutes or hangs and never completes at all.

Any idea what might be happening?

Thanks,
Tom

tjams · Post by **tjams** » Tue Aug 29, 2006 3:21 pm

I just noticed that just from two of us trying to connect to vault, that the db log file went from almost nothing after truncating to over 5 Gigs. Could that be a clue?

tjams · Post by **tjams** » Tue Aug 29, 2006 3:23 pm

And it's continuing to grow. Now up over 7 Gigs with no new activity. The only thing happening is my last check out that hung about 15 nminutes ago. Seems to be stuck in some kind of loop or something.

tjams · Post by **tjams** » Tue Aug 29, 2006 3:55 pm

I allowed the hung check out process to run for about an hour before killing it. During that time, the sgvault_log.ldf file grew over 17 Gigs. It seemed to stop growing when I killed the process. Can you please advise on what we should try next?

Thanks,
Tom

Funk · Post by **Funk** » Tue Aug 29, 2006 3:59 pm

Change the recovery profile to simple. The log file is then erased after a successful commit. Presently, it appears that the db is logging every transaction (this is the default on new db's in SQL). Great if you are an international bank. Not so great for source control.

Funk · Post by **Funk** » Tue Aug 29, 2006 4:04 pm

jclausius wrote:Check your Recovery Model within SQL Server.

If you are not making nightly backups of the log file, place the sgvault database in simple recovery mode, but still make sure you make regular backups.

I repeated this post for emphasis, not because I didn't read the thread

Funk · Post by **Funk** » Tue Aug 29, 2006 4:16 pm

Truncating the db is different than setting the recovery model.

To confirm, the recovery model is set to simple? If so, then there appears to be a huge commit taking place as the log file grows as needed to handle the data for the commit. Is someone inadvertantly commiting an entire server to source control?

tjams · Post by **tjams** » Tue Aug 29, 2006 4:19 pm

I don't think you're understanding what I'm trying to tell you. The log file isn't growing through normal use. It grew 17 Gigs over the course of one hour with a single attempted transaction (a checkout of a single small file) from 1 user. That transaction hung. No one else is using the vault service at this time.

I will change the recovery model to simple and backup again to truncate the log again as you suggest. But I suspect I'll still have the same inability to conduct business through Vault as before albeit with a smaller log file.

tjams · Post by **tjams** » Tue Aug 29, 2006 4:32 pm

Sorry, didn't see your last reply there. I am the only one using Vault at the moment. I attempted to change over to Simple Recovery, but now the database says "(In Recovery)" next to it in SQL Management Studio so I have to wait until it's done to do anything.

Could this be an issue with my local cache perhaps? The only operations I attempted were two check outs of individual files.

tjams · Post by **tjams** » Tue Aug 29, 2006 6:37 pm

Ok. The DB is now in Simple Recovery mode and the log file is down around 500 KB. However, I am still unable to use Vault. I am able to login and retrieve repository structure, but when I attempt an operation, everything just hangs. Please advise as this is costing us a great deal of time.

Post by **jclausius** » Tue Aug 29, 2006 10:05 pm

Is there any indication of what may be happening in the Vault Server Log ?

If nothing is there, you may want to use the Admin Tool and place the Server Log into Debug Mode to gather more data on what may be happening.

Post by **jclausius** » Tue Aug 29, 2006 10:09 pm

We may also want to look at this from a SQL Profiler point of view. Perhaps we can get a better understanding of0 what is happening by the stored procs.

tjams · Post by **tjams** » Wed Aug 30, 2006 10:45 am

Turns out that onbe of our developers had inadvertently managed to check out thousands of files. I un-did all of those and everything seems fine now.

Thanks.