Big Log file that won't shrink....

I have a fairly simple DB, One Log file, one data file, its got half a dozen tables in it that are used for a web based app that allows users to look each other up (think online telephone directory with a few extra bits and you are there), there's a data feed that adds new lines each day as users data changes or new people get added. But this is a couple of hundred or so lines a day, the tables hold about 40,000 data lines as we never delete data just retire it.

The problem is the main data file is about 200Mb, the Log file however is 3.8Gb. Now we did import and re-import various bits initially and clear down tables and such. But I don't understand why i can't shrink the log file. I've tried everything I can think of. I've even detached and re-attached the DB. Still no joy.

The file size/usage is

data file 200Mb size in use 117Mb

log file 3749.99Mb size in use 3719.89Mb

Anyone have any suggestions for things to look at?

many thanks

Steve

Hi,

look here:

http://www.aspfaq.com/show.asp?id=2471

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Best way to copy a sql db between machines?

SQL server 2005 developer version

I have about a dozen db's scattered on 3 machines and want to
consolidate all of them on my desktop with the data intact.

What's the easiest, best way to accomplish this?

Can I backup on one and restore on another?

Yes you can RESTORE from one machine to another. You cannot combine databases via RESTORE, though. You would have 12 databases on one machine.

|||No, I do not want to consolidate them , just copy them from one machine to another so the data is the same everywhere.

I tried the restore and it errs out because the pc name is different
from the pc name of the db I backed up.

Seems rather limiting.

I also tried the export/import with flat file delimited but it errs out as well. I double checked the table entry details and the db's are identical. The target db is empty of data.

These are small db's but not so small I want to renter the data.

|||

Check out one of the RESTORE command options - [ WITH MOVE ...]

Refer to Books Online, Topic: RESTORE

|||I got the restore to finally work by creating the dir that it wanted.

My db's were in
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

and sql server insisted upon :
C:\Program Files\Microsoft SQL Server\MSSQL\Data

Once I created that dir the restore worked just fine.

No where I could see to change the path for the restore either.
Pretty stupid if you ask me but that's what wizards are for.

Thanks for trying guys
|||A few days ago someone pinged me to do the same, this is what I sent him over:

-If you restore the files using the RESTORE DATABASE command, use with WITH MOVE options to place the files somewhere else on your computer. For more information about the WITH MOVE option, look in the BOL of SQL Server.

-Prefering the GUI to restore the files ? Then point to the backup device and navigate to the Options page within the restore dialog. Change the settings of Restore database files as (which actually means database files and logfiles) to point to the new directory. (It will contain by default the directories which were used during the backup process, by changing the directories within the dialog, it will create the script behind the scenes which uses the WITH MOVE option)

Jens k. Suessmeyer.

http://www.sqlserver005.de