We help IT Professionals succeed at work.
Get Started

Managing the Size of log files (.ldf) on SQL Server

369 Views
Last Modified: 2012-08-13
Hey Guys,

I have a SQL 2005 server which has not really been maitained by our DBA. I have now taken over the task to trying to get this SQL server is as good of a shape as possible before we eventually migrate it.

My question arises in regards to the log files and probably has several parts to it, so I will try and break it down to what I already know and whats the best way to go about finding a solution.


Problem: I have several databases, some range from 256 Megs and others are at 2 GIGS. Each of these databases however have a ridicoulously large log file. The 256 mb file is around 30 GB and the 2 GB db is around 135 GB.


Now initially before my coworker had left, he had setup the agent for SQL Server in backup exec, to first run the DB backups followed by logs to truncate them. Because of the size of the log files, these backups are taking notoriously long. Now upon investigation, at first I was very initially confused w/ the defintion of truncating and shrink. I now think i understand the difference, where truncating does not nessarily reduce file space but keeps a record of the transactions that are passed / old since the last full db backup. Shrinking physically reduces the file by removing the free space from the log file.

Now I can easily solve my problem by running the shrink command on the dbs, but before I went ahead and did that, I did a bit of research and am now aware that SHRINKING is a big no, or at least that's what a number of people say. It removes the contingous filing structure of a db, causes a need for indexes to be rebuilt and overall creates a general performance hit. Not to mention that as soon as the db is required to grow again, it will and it shall.


So my question are:

How do I shrink my .ldf files w/out using the shrink command and suffering a performance penalty?

Do you guys recommend backing up my db's with SQL Agent from backupexec or maintenance plans?

How do i guage the maximum file size growth for my DB?

Can I set alerts when my db is near the maximum file size?

How do i reindex dbs and defragment db's so they are always performing optimally?


----

Thanks for all the help. BTW this is my first dive in SQL or any form of DB's so please excuse the confusion.
Comment
Watch Question
Database Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 11 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE