MS SQL 2005 Transaction Logs and DB size

[kkiedrowski]

This is a two part question.
1. For SQL express versions the size limit is 4 gb. is this 4gb the log file and data file?

2. For the log file is there anyway to disable or clear out the logs on a regular basis. The problem we are running into is our VB program runs and end of year procedure and even if the log is set to simple mode I have seen it (the log file) grow from 11 MB to 20 GB during this process. Unfortunately I am not the developer so I am not sure how or why it is writing so much to the log file.

Any insight would be great and I also apologize for any key information that is missing from this post.

 

[SQLBill]

1. I believe that is the entire database size.

2. The log file needs to keep track of every change made to the database until it is "accepted". If it's not accepted, the log is used to rollback all of the changes. The transactions are accepted by COMMITing the data. So, options are to have the process do the transactions in batches and COMMIT after each batch. Or if you don't care about it using the space and aren't running out of space and your only concern is getting the space back, you can schedule a DBCC SHRINKFILE command. Syntax is DBCC SHRINKFILE ('logfilename', size in MB to shrink to) - DBCC SHRINKFILE ('mydb_log', 100).

Some people advise against SHRINKFILE or SHRINKDATABASE as it can cause fragmented databases. Also, check your databse properties to see if AutoShrink is set.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[mrdenny]

People don't advise against SHRINKFIILE and SHRINKDATABASE because it can fragment the database, but because it will fragment the database and specifically the Virtual Log Files within the Transaction Log.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog