SQL Server Express Performance breaks with large Logfiles

Question

Hello all, we run since 2 years a small application on SQL Server 2005 Express Edition the Database has gown from 75 MB up to nearly 400MB within this time, the there isn't a big amount of data.

But the log file has been arrived at 3,7GB now without changing Hardware, table structure or Program code we noted that the Import processes which required 10-15 minutes are now arrived at a couple of hours.

any idea where could be the Problem? Depends it on the log file may be? The 4GB Lock of Express Edition bear only on data files or also on log files?

Additional Informations: There isn't any RAID on the DB Server, There doesn't work concurrent users (only one user is logged in while the import process).

Thanks in Advance Johannes

Answer 1

From MSDN:

"The 4 GB database size limit applies only to data files and not to log files. "

SQL Server Express is also limited in that it can only use 1 processor and 1GB of memory. Have you tried monitoring the processor/memory usage while the import is running to see if this is causing a bottleneck?

Answer 2

That the log file is so large is completely normal behavior; in the two years you have been running; sql has been keeping track of the events that happen in the database as it goes along its business.

Normally you might clear these logs off when you take a backup (as you most likely dont need them anyway.) If you are backing up all you need to change the sql script to checkpoint the logfile (its in books online) depending on how you are backing up your milage may vary.

To clear it down in the immediate make sure no one is in using the database; open management studio express find the database and run

backup log database_name with truncate_only
go
dbcc shrinkdatabase('database_name')