Solved

Very Large LDF

Posted on 2008-06-10
12
1,324 Views
Last Modified: 2010-04-21
We have a SQL database whose MDF is 791MB and LDF is 194GB.  I cannot shrink this file beyond a couple GB.  I believe the size is due to it being a production database that is queried all day by the majority of our organization.  Is this a normal file size for an LDF?  I noticed that the file is set to unrestricted growth.  I am afraid to restrict the growth because I don't want the users to take a performance hit.  What to do?  Thanks!
0
Comment
Question by:rjander77
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 13

Expert Comment

by:rickchild
ID: 21752643
If you don't need log backups you can set the relevant database to "Simple" recovery model.

If you do need log backups and are in Full recovery model then you should be backing up the Transaction Logs, then the file will be shrinkable.
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 300 total points
ID: 21752847
Don't restrict the growth...and I wouldn't consider switching to simple mode.  Make sure in your db maint plans that regular backups of the tlog are made.  Backing up the log removes inactive transactions.  Then, if needed, you can shrink the file using DBCC SHRINKFILE.
0
 

Author Comment

by:rjander77
ID: 21752907
Thanks to you both for your suggestions.  I have edited the Maintenance Plan to include the transaction logs and will report back tomorrow (backups run nightly) with the results.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 60

Expert Comment

by:chapmandew
ID: 21752918
You may want to include a step that shrinks the files as well...not necessary, but will help with your big file at first.
0
 

Author Comment

by:rjander77
ID: 21752936
Thanks, that step was already included - which is why I was so perplexed to begin with.  I had it set to Full recovery mode with reindex and shrink operations following.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21752949
It wasn't shrinking because the log files weren't being backed up...once you do that, you're all good.  :)
0
 
LVL 22

Expert Comment

by:dportas
ID: 21752986
Don't put the shrink task into the maintenance plan. If you do log backups regularly then shrinking regularly is a very bad idea.

You may want to do a one-off shrink if the log has grown excessively due to not being backed up but that doesn't mean you should modify the maintenance plan.
0
 

Author Comment

by:rjander77
ID: 21753021
Thanks.  I'll see what it looks like tomorrow, and if it's all under control, I'll take the shrink operation out of the daily maintenance plan and leave it on the weekend maintenance plan.
0
 
LVL 22

Assisted Solution

by:dportas
dportas earned 200 total points
ID: 21753690
0
 
LVL 4

Expert Comment

by:roshkm
ID: 21758070
You might have already tried this but still followin these steps:

DBCC SHRINKFILE(<File Name>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)

Or:
you can detach the database and attach it, but you will be lossing any replication configurations done.

Or: (i followed this method, which gave me a permanent soln)
You can rectify it by Properties->Transaction Log-> Check Automatically grow file.

If you are not taking a back up of Transaction log, Properties->Options->Check AutoShrink, Model: Simple, for decreasing the log file size. Log file in Simple Model will be cleared off after every successful transaction.
It will take some time for the log file to shrink.

When it is time that u take back up, u can reset it and restart the job that takes the back up..

Cheers,
Rosh K. M.
0
 

Author Closing Comment

by:rjander77
ID: 31465786
Thanks!
0
 

Author Comment

by:rjander77
ID: 21778652
Ran into a few space issues when backing up the 200GB trxn file, but after I got that cleared, it worked just fine!  The trxn file is now 70 MB, and I have restricted growth to 2 GB.  I figure that's more than enough for a 700MB database.

Thanks!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Update in Sql 7 37
Server running out of memory - Something is consuming all the available memory 17 60
SQL Error - Query 6 25
T-SQL: New to using transactions 9 25
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question