Solved

Clearing Space Used By .LDF files

Posted on 2003-11-13
9
294 Views
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

Thanks

Shariq
0
Comment
Question by:shariq
9 Comments
 
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

Thanks,
Anthony
0
 

Accepted Solution

by:
kauf earned 50 total points
ID: 9744029
Hello Shariq,
If it is a secondary Transaction log you can resize / delete it by:
BACKUP LOG [DatabaseName] WITH TRUNCATE_ONLY

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.
0
 

Expert Comment

by:kauf
ID: 9744038
Ah yes, first - check that you've not checked automatically grow in the transaction log options to prevent even more size...
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 3

Assisted Solution

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

Assisted Solution

by:arbert
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....
0
 

Expert Comment

by:kauf
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)
0
 
LVL 34

Expert Comment

by:arbert
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....
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Slow Connectivity over ODBC 8 32
How do I assign a function with a parameter as the RowSource of a combo box? 9 42
string fuctions 4 25
SQL Improvement  ( Speed) 14 26
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

816 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

11 Experts available now in Live!

Get 1:1 Help Now