asked on

Growing LDF files

We notice that LDF files keep growing in size and wondering if there is a way to reduce them. We use Backup Exec but have yet figured out a way via this software to reduce the file size.

Any suggestions?

Raja Jegan R

Yes, just schedule to take Full Backups( Best Practice) and Transactional Log Backup ( Required) to run at frequent intervals in order to keep Transactional Log File size in control.

You can schedule Transactional Log Backup using Maintenance plans.

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bnrtech

ASKER

rrjegan17,

Thanks for the quick response.

I guess what I am getting confused on is that I thought I had this happening as part of our nightly backup in Backup Exec. See attached screenshot of how we have that job configured. But it does not appear to have made a difference in the size of LDF files
sql-config.JPG

Raja Jegan R

As per your snapshot attached, you have configured Full Backup which I mentioned earlier.
Check out for any Transactional Log backup in the backup method of the Screenshot attached which should keep your Log file size in control.

bnrtech

ASKER

I found the Transaction Log backup option in the system and setup a job to perform this action. It ran but the LDF files still appear to be the same size. Two of the databases failed the transaction log backup because they were not configured within SQL for full backup. But the other databases sucessfully completed the log file backup and their sizes have not changed.

Maybe is there something else I need to do to refresh the system so the sizes will reduce?

Anthony Perkins

>>It ran but the LDF files still appear to be the same size.<<
Right. It will not shrink the size, but it will allow the space to be reused. If you find that you have to reduce the size after running a backup of the Transaction Log, than consider using the following command to adjust the size:
DBCC SHRINKFILE('YourTransactionLogLogicalNameGoesHere', 2000) --- 2GB adjust as needed

Do NOT do a SHRINKFILE regularly.

>>Two of the databases failed the transaction log backup because they were not configured within SQL for full backup.<<
In order to to a Transaction Log Backup two conditions needs to be met:
1. Database is in Full Recovery Model
2. A Full Backup has run.

bnrtech

ASKER

acperkins

Thanks for this info. I will run the SQL command that you noted past our SQL admin before doing it (which will be Monday).

On the 'Database is in Full Recovery Model' condition you noted, where do we go to verify that a database(s) meet this condition.

Raja Jegan R

>> On the 'Database is in Full Recovery Model' condition you noted, where do we go to verify that a database(s) meet this condition.

* Right click your database in SSMS
* Go to Properties
* Choose Options
* Check Recovery Model field which should have Full Recovery model set.

Anthony Perkins

>>where do we go to verify that a database(s) meet this condition.<<
The fact that the Transaction Log is continually growing almost guarantees that it is set to Full Recovery Model. It also happens to be the default.