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?
bnrtechAsked:
Who is Participating?
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.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
And this can help you create Transactional Log Backup Maintenance plans:

http://technet.microsoft.com/en-us/library/ms189647.aspx
http://technet.microsoft.com/en-us/sqlserver/dd430301.aspx

Just choose the backup type as transactional log and file type as *.trn instead of *.bak in the steps given over there.
0

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
bnrtechAuthor Commented:
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
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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.
0
bnrtechAuthor Commented:
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?
0
Anthony PerkinsCommented:
>>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.
0
bnrtechAuthor Commented:
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.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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.
0
Anthony PerkinsCommented:
>>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.
0
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.