SQL Log File (too big!)

[CardTracker]

I am using Microsoft .NET 2008, and the SQL server package bundled with it (I think it's SQL Express), and I need to disable and/or delete log files.


The database I am currently using is 20MB or so, and the log is approaching 120MB. Since I'm exporting this database to other computers, I need to make this log file as small as possible.

I'd prefer to delete the current log file, but I'll settle for disabling the log and starting over, since I've heard that can cause some trouble. I need to start over soon so anyway, and restructure everything anyway, I'd just like to solve the log file issue first. Also, all the data is backed up in an excel sheet.

I've looked around a bit, but all the instructions I've found are for SQL Server 2000, or a similar situation.

Any help would be greatly appreciated.

Thanks!

[Alsvha]

I suspect you are talking about the transaction log files? And not the SQL Server log file as that isn't needed to restore databases.

The transaction log file, can be truncate/shrink, if you set the database recovery model to "simple", if it is "full" or "bulk-logged".
You can do this in a number of ways, I usually use DUMP TRANSACTION <db-name> WITH NO_LOG.

If it is "simple", you'll also only log minimal things in your transaction log, but you'll not be able to restore to a point in time if you database crashes, but only to the last time the backup file was made.
It is rarely a good idea to run a production database as simple though, so keep the above in mind, in case it crashes, if you do not want to loose data.

This worked in SQL 2000 and 2005, so I guess it should work in 2008

[CardTracker]

I'm using .NET 08, and I don't have a command line control for the database (at least not that I've found)

I'm not worried about loosing the data at all, because I have a backup stored in an excel file. This isn't a production database exactly, but it's a database used to run a program.

Updates will be rare (3-4 times a year), and I enter all the information in the excel sheet first, because I need to process the information a bit, and add some custom information.

Additionally, I'm exporting this program and database to friends using CDs and rapidshare downloads, so I want to make the package as small as possible.

I hope that clears some things up. Thanks for any help!


PS: If my sig isn't showing up, the details of the project I'm working on can be found at cardtracker.blogspot.com

[Alsvha]

You can still send "command line" SQL to the database, if nothing else using your application.
Just execute it as a query. You might need the SQL to alter the recovery model, but a quick google should be able to provide that.

[CardTracker]

OK, time to try this again.

I know the following two things:

1) The code I want to use to limit the size of the log file is:

ALTER DATABASE YourDatabaseName
SET RECOVERY SIMPLE

2) This parameter must be changed before the log file gets big, otherwise it's pointless.




Let's assume I know NOTHING ELSE about databases in general, or how to use them through .NET



I open a project, and open the database connection list. The toolbar is on the left and says "Server Explorer" at the top.

I navigate to the database I need to modify, using dropdown hide/show boxes (with + or - on them)

I navigate to a table.

I open the table and click the "SHOW SQL PANE" button. I type the above code.

I revive the following error:
"The ALTER DATABASE SQL construct or statement is not supported."



Now what? I've been trying to figure this out for hours, but I still have no idea what's going on.

Again, I'm using .NET 2008, and need to reduce the size of my Log file.

[dee-u]

You could download the SQL Server Management Studio Express to manage SQL Server express databases...