SQL Server Log File size problem

[lauriemc]

A little background on my problem: we have a pilot program in the gov't agency where I work where we are testing a vb.net Windows Application with a LOCAL SQL server database. The people who go out on the road do not have any inter/intranet connection, once they are on the road they have static data. It is used primarily for inquiry purposes, some printing. They also do not have SQL Enterprise or any SQL management tools.

 

The data is refreshed from our mainframe computer in the morning, when they plug into the intranet through their shop. We don't do a backup on the individual laptop, rather we keep a backup on another computer here in the main office. From that computer, if our roving laptop is plugged into the intranet, we can shrink the logfile.

 

I am finding that the size of logfile seems to impact the length of time it takes to download the necessary data from the mainframe. It is a clean wipe, btw, each table is truncated, then reloaded.

 

My question is this: is there any way to programatically shrink that log file from the vb Windows application? I would like to see that done before the download begins, and without our road people having to call me and ask that it be done.

 

Thank you, laurie mc

[PlausiblyDamp]

Using a tool like osql or http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&displaylang=en you could set the recovery mode to simple with code like

[highlight=sql]

ALTER DATABASE SET RECOVERY SIMPLE

[/highlight]

to disable transaction logging. This will impact on the databases recoverability but if it is being treated read only this shouldn't matter.

[lauriemc]

We did this in the database itself, and while it seems to prevent the log file from growing to gigantic proportions, it doesn't prevent it from growing.

 

I did find the kind of tool I wanted, which I put in a stored procedure.

 

The sp code is simply this:

 

DBCC SHRINKDATABASE (dbname, 10)

GO

 

I call it from the program before and after the upload / download, and it works like a charm, shrinks that log file right down.

 

Thanks, laurie mc