InfoPath Dev

DBXL is working great.  Just one question.  The log file is ~21 GB while the database file is only 250MB.  Is that typical?

Do you have history enabled?  If so, this could be a very big contributing factor.

Depending on the size of documents being submitted and whether history is enabled could increase the log files substantially.  You will need to periodically truncate your transaction log depending on your recovery requirements.

I checked and I don't have history enabled.  I only have Permissions checked on the Edit page of the Admin form.  How would I truncate?  I still might need to go in and update a form from months ago.  Would you recommend creating a second form that I could query the database to pull in info, update, and then save?  If the Document row is deleted, would that screw up any Doc ID? From opening the dbo.Document table, I noticed that the DataExtractedPlainText column has all the data from the forms.  Say I delete DocID 505 that has Scott's patient info from Kent on Feb 23rd.    Each DocID is a unique combo of (Therapist, Clinic, and Date).  In order to update that info on the SQL Server, I would use my form to query the SQL server for those 3 criteria, make whatever changes I need, and submit back to DBXL?  But wouldn't DBXL create a new record of that data so I would end up with 2 instances of Scott's patient info from Kent on Feb 23rd?  Or would I query that info, delete the rows in SQL, and then submit that info again?

Hi Jeff,

I'm not sure how to truncate, but if you right-click the database in SQL Server Management studio, there is a Tasks -> Shrink -> Files option.  That might do the trick.

As for deleting Documents, you should never ever delete directly from the DBXL database tables.  Doing so could cause catastrophic results.  If you open DAT and delete a Document from the Document Catalog view it will clean everything out: all related tables in DBXL plus all tables that you have shredded to.

If you are looking to save space, you can change the "EnableFullTextSearch" configuration option in web.config from "true" to "false".  This will break searching from CatBase and a couple other functions, but if you arn't using these (which I don't think you are), then it should be fine.

I'm not sure what you're talking about with querying, deleting, and resubmitting, but I think if you just work from within DAT with delete and/or reshred you should be fine.

I hope this answers your questions!

The size of the transaction log is strictly a factor of database update activity.  Deleting documents will not reduce the transaction log size, but will actually increase it. 

The transaction log is a key to database recoverability and just needs to be managed to maintain its size.   You would normally truncate your log file at the time of a full backup.  You can also perform log file only backups at frequent intervals (15-30 minutes) to narrow the window of possible data loss.

Here are a few links to relative articles on managing transaction logs:

http://msdn2.microsoft.com/en-us/library/ms955998.aspx

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8105239c-b657-48c1-9991-17159081b280.htm

http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci980079,00.html

http://www.2000trainers.com/sql-server/sql-server-transaction-logs/

http://www.dotnetspider.com/kb/Article2083.aspx

 

Thanks for the info Jim.  Managing Transaction logs and doing the DBCC SHRINKFILE did the trick.