5

We are a small trading company. We use a software for invoicing and stock keeping which uses SQL Server as a database. Our SQL Server database is 3.3 gb in size. However our LDF file is 173 gb. Both the software and the SQL Server is setup on the same server which we have in our office. We are running out of disk space also it is taking a lot of time for us to FTP the daily backups we get and I wanted to ask how can I reduce the size of our LDF file. I have read several articles and suggestions by other on the net suggesting either of the following:

  1. Disabling / Tweaking autogrowth/maxsize for the LDF file.
  2. Shrinking the LDF file
  3. Changing recovery model to Simple.

I have also read a lot of 'no, don't do it' articles for each of them. I am really confused. How can I reduce the size of the LDF file?

I am also attaching some images for the properties of my DB.

enter image description here enter image description here enter image description here

Improve this question
0

2 Answers 2

Reset to default
7

You are not backing up your transaction log file. This is visible if you look at your first screenshot:

first screenshot

The general tab show you that:

Last Database Log Backup : None

If you don't backup your transaction log file, then the transactions are not marked for deletion and will accumulate in the transaction log file and eventually your transaction log file will fill up your disk.

There are two solutions:

Solution 1

  1. Back up your transaction log file using the tools you are already using.
  2. Then you can shrink the transaction log file to a smaller size using the GUI in SSMS (Right click on LOGO_DB | Tasks | Shrink | Files)
    • First release the unused space in your transaction log file by selecting the File type : Log: Release Unused Space - Shrink File
    • Then shrink the actual transaction log file with the following options (open the Shrink dialog again, select File type : log): Reorganize pages before releasing unused space Select a size which is just above the (Minimum is xxx MB) which is displayed in the dialog.
  3. Ensure you have a regular transaction log backup scheduled.

Alternatively, you can use the DBCC SHRINKFILE.... commands like this:

USE [LOGO_DB]
GO
DBCC SHRINKFILE (N'logo_db_log' , 0, TRUNCATEONLY)
GO
USE [LOGO_DB]
GO
DBCC SHRINKFILE (N'logo_db_log'  , 4096)
GO

This still requires you to have a regular transaction log backup in place.

Solution 2

If you don't require the database to be in FULL Recovery Model, then switch to SIMPLE.

  1. Switch the database to the SIMPLE Recovery Model in the Options section.
  2. Perform a FULL backup of your database.
  3. Then you can shrink the transaction log file to a smaller size using the GUI in SSMS (Right click on LOGO_DB | Tasks | Shrink | Files):
    • First release the unused space in your transaction log file by selecting the File type : Log: Release Unused Space - Shrink File
    • Then shrink the actual transaction log file with the following options (same dialog again, File type : Log): Reorganize pages before releasing unused space Select a size which is just above the (Minimum is xxx MB) which is displayed in the dialog.

Warning
If you switch to the SIMPLE Recovery model, then you will be unable to restore the database to a given point-in-time. However, seeing as you aren't performing TLOG backups, you won't be able to do that currently anyway.

You might want to have a look at Ola Hallengren's - Maintenance Solution for a complete set of maintenance jobs (Backups, Index Maintenance, Database Checks, ...) or have a consultant look at your setup.

Improve this answer
2
  • this is great. Might you possibly give a little more info about "Select a size which is just above the (Minimum is xxx MB) which is displayed in the dialog." i.e. given a certain size in "Currently Allocated" have you got a rough calc. you use to derive the shrink size? Or do you just e.g. use a few more MB than the min? i.e. the image above seems to show 100MB .ldf getting shrunk to 4GB so little confused. Just looking for some "rule of thumb". Thanks
    – MemeDeveloper
    Commented Feb 15 at 12:20
  • 1
    @MemeDeveloper Well you can't shrink to zero as in the example screen shot (Minimum is 0 MB) and the transaction log file is going to grow anyway. If your database is huge (1TB) and you are performing TLog backups in FULL recovery model, then 10240 MB (10 GB) might be a good value. Then again it might not be. You have to observe the growth of the TLog file over a couple of weeks to find out a good value.
    – John K. N.
    Commented Feb 16 at 6:43
3
  • If you are in Full or Bulk_Logged recovery model, then you need to do regular Log backups. If you are in Full or Bulk_Logged recovery and don't do Log backups, then there is nothing that will empty ("truncate") the Transaction Log - so the Log file with grow and grow and grow...
  • If you don't want to do Log backups, then you will set the Recovery Model to Simple.

After you have sorted out above, you will perform a shrink of the Log file.

Which Recovery Model you should use will depend on what level of granularity you need for backup restores. If you need closer to realtime backups than the Full backup, then continue to use the Full Recovery Model, and begin taking Log backups. Otherwise, if the last Full backup is as granular of a restore point as you need, switching to Simple Recovery Model is likely what you should do.

Here's an article I wrote on the topic: https://karaszi.com/large-transaction-log-file

Improve this answer
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.