Clearing Space Used By .LDF files

Posted on 2003-11-13
Last Modified: 2012-06-27
There is a data warehouse running on SQL Server 2000. During migration I see that Warehouse_Log.LDF has become 5+ GB in size. I did a checkpoint in Query Analyzer and shrank the database from the enterprise manager and yet this value does not go down in size.

What can I do to free up this space


Question by:shariq
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9743757
Please maintain your open questions:

1 04/23/2003 50 Output Table Data Into Fixed Length Form...  Open 3rd Party Applications and Development Tools
2 09/09/2003 75 Convert From Oracle to MS-SQL  Open Microsoft SQL Server


Accepted Solution

kauf earned 50 total points
ID: 9744029
Hello Shariq,
If it is a secondary Transaction log you can resize / delete it by:

And then modifing the data in enterprise Manager.
i once tried to shrink a primary file, but only was able to do this through detaching and attaching the database.

Expert Comment

ID: 9744038
Ah yes, first - check that you've not checked automatically grow in the transaction log options to prevent even more size...
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


Assisted Solution

htarlow earned 50 total points
ID: 9744060
If the database recovery type is full or bulk logged, backup the logfile to shrink it.
LVL 34

Assisted Solution

arbert earned 50 total points
ID: 9750158
"grow in the transaction log options to prevent even more size... "

agree with the above suggestions to backup with truncate_only and then shrinking the log file.  HOWEVER, I would not suggest you turning the auto grow off opn the logfile--just bite you in the butt later when you have big inserts or updates....

Expert Comment

ID: 9752767
in an emergency case, you can set a temporary (secondary) transaction log anytime (this you can delete after BACKUP LOG... w/o problems)
LVL 34

Expert Comment

ID: 9752863
"in an emergency case, you can set a temporary (secondary) transaction log anytime (this you can delete after BACKUP LOG... w/o problems) "

why go through the extra effort.  MS SQL Server best practices say that the log file should be set to auto grow....

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

867 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now