Reduce file size of SQL Express database log file (Database is using the simple recovery model)

Hi Experts,

We are running a server with SQL server express which has the recovery model set to simple and the log file (.ldf file) for one of the database has been growing in file size significantly and appears to be growing a fair amount each day & eating into free disk space. Currently the log file is 17.6gb and the database file is 2.10GB.

Initial size (mb) of the database is 2161 and the log file is 18117
Autogrowth / Maxsize of the database is By 1mb, unlimited and the log file is by 100mb, limited to 2097152. Note we did have the log file set to autogrowth by 10% a few days ago but changed to 100mb hoping this would hel, however the log file still seems to be growing too much.

Under the general properties of the Database it says the last database log backup - None, the last database backup is today so not sure if this has something to do with the situation, since we are using the simple recovery model maybe that why it says the logs have never been backed up?

Any recommendations on something we can do to reduce the log file size?, ideally we dont want to impact performance of the database if this can be avoided?

Thanks
sherlock1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
You should try to shrink the log file. You may do it in both SSMS and by T-SQL command. All details are described e.g. here: https://www.mssqltips.com/sqlservertutorial/3311/how-to-shrink-the-transaction-log/
sherlock1Author Commented:
Thanks for this suggestion - I believe shrinking the log file can impact performance? but in this situation do you suggest this would be a good idea?

can the shrinking be done whilst users are using the database? or should it be run outside of business hours?
pcelbaCommented:
Shrink just reduces the file size if there is an unused space. It is one time operation which can take some time but you may do it during business hours. The shrinking itself may cause delays for other users. How many users are using your database simultaneously?

The impact to performance is not significant after the shrinking. The only delays are expected when the database or log file must "autogrow" because the file size change can take time. For that reason is recommended to have the file size constant since beginning. But we are talking about SQL Express which is not suitable for critical applications and some delays are acceptable.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Scott PletcherSenior DBACommented:
First you need to find out if/why SQL is not overwriting the existing log disk space, as it should normally do in SIMPLE mode, rather than adding disk space to the log.

SELECT log_reuse_wait_desc, * FROM sys.databases WHERE name = '<your_db_name_here>'

If that returns 'NOTHING', then you can just shrink the log:

USE <your_db_name_here>
CHECKPOINT;
DBCC SHRINKFILE(2, 1024)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Do you know which transaction/operation is causing the size to grow?
For example, I have seen code that begins a transaction, loads a couple of hundred MB of rows, updates one row and then commits the transaction. The net effect in this case is that if you have hundreds of users (or automation tests running with each CI build) the log will grow a few MBs every time a record is updated and consume a lot of disk space.
sherlock1Author Commented:
Thanks all for the further feedback.

pcelba - I believe there are only up to 5 users using the database at the same time, often only 2 or 3 users at a time.

Scott - I run the query and "NOTHING" was returned  so i run the shrink job on Friday on the log file, this reduced the log file size to about 1MB. it took only about 25 seconds to complete.
Now on Monday the log file had grown again to about 17GB, again i run the query and "NOTHING" was returned, Few users should have been using the application that uses the datbabase in question over the weekend (if any) so i suspect it may have grown in file size just today (Monday)

Nakul - Your feedback is interesting and could well be similar to whats happening No i dont know what transaction / operations could be causing the size to grow, the application is used to scan postal packages (by using hand held barcode scanners) and stores various information in the database. I dont know much about the application it self.


I guess I could run a shrink shrink on a daily basis but i suspect this would be far from ideal and wouldn't really solve the underlining issue
sherlock1Author Commented:
Just to update you on this I did the shrink job again (2nd time) and the log file size reduced to about 1MB, then in the timeframe of only about 10 minutes the log file size has already increased to 2.10GB! so something seems to be going on causing the log file to grow so quickly and most users should not be using the application for the day now in fact at the moment it doesnt look like any users are connected to the DB in activity monitor in SQL management studio
Scott PletcherSenior DBACommented:
If you ran the command I suggested, the log should have shrunk to 1GB, not 1MB.

As to the log growth, it's usually an INSERT of a large amount of data.  If you have the SQL Agent running with Express (if that's available in your Express version and you're using it), it could be a schedule job doing it.

Otherwise it's usually a very long-running transaction.  The trans log is sequential, and as long as a trans is incomplete, the log will keep growing until it's resolved, either with a COMMIT or a ROLLBACK.

Since you aren't seeing a long-running tran either, At that point, you might have to resort to running a perfmon trace to see what is writing so much to the db.
pcelbaCommented:
You should check following:
- how often do you rebuild indexes? (namely the clustered ones)
- check number of open transactions (DBCC OPENTRAN)
- check the IMPLICIT_TRANSACTIONS setting (e.g. in connection string  or by SELECT @@OPTIONS & 2)
- also  check whether users are disconnecting properly (sp_who2  should be enough)
sherlock1Author Commented:
Thanks for the further feedback

Scott- I have now run the command you suggested and yes its now just over 1GB
I can see the SQL agent services listed within the services console on the SQL express server
but its disabled.


pcelba - for this SQL express server I dont think there is anything that is rebuilding the indexes
I run the command you suggested and currently no open trsactions or implicit transactions and nothing stands out on users that are not disconnected

However thanks for these comamnd sugegstions. I will run them over a course of time over the coming days and see if it shreds any light on the possible cause of this issue.

For your info i have created a follow up question here if your interested and able to assit that would be good:
https://www.experts-exchange.com/questions/29095397/Batch-file-to-shrink-the-log-file-of-a-SQL-server-express-database.html#questionAdd
sherlock1Author Commented:
The log file is not growing at the moment, so perhaps running the command you provided scott has helped but i will monitor, unless it needed a few passes or shrinking the log file
sherlock1Author Commented:
Thanks all for your help on this. thought on this one it would be best to split the points out as you've all helped with this
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.