Solved

How to reduce the size of .LDF file?????

Posted on 2002-04-03
15
11,600 Views
Last Modified: 2010-05-18
I would like to know how can i reduce the size of .LDF file?????
0
Comment
Question by:Kelvsat
  • 5
  • 3
  • 3
  • +3
15 Comments
 
LVL 15

Expert Comment

by:simonet
ID: 6917301
The LDF file is the LOG gile.

Before and AFTER reducing it's size, I suggest you backup the entire database.

Now to reduce it using Enterprise Manager:

- Open Enterprise Manager and expand the "databases" leaf in the Server you where the database resides

- Right click on the desired database

- Select All tasks -> Shrink database

- since you only want to shrink the log file, click on "Files..."

- Select the log file

- Select "Compress pages and then truncate free space from the file"

- Click ok

If using transact-SQL, you can use DBCC SHRINKFILE



That's all it takes.

Yours,

Alex
0
 
LVL 15

Expert Comment

by:simonet
ID: 6917304
By the way,  here's an example of using DBCC SHRINKFILE to truncate the free space from the log file:

Supposing the Log (.LDF) file is named CDColl_Log.LDF, then the command goes like this:


DBCC SHRINKFILE (CDColl_Log, TRUNCATEONLY )

Yours,

Alex
0
 

Author Comment

by:Kelvsat
ID: 6919743
dear simonet,
     I have tried ur idea, but irealize the physical size of the .LDF file doesn't reduce. Why???
0
 
LVL 15

Expert Comment

by:simonet
ID: 6919826
Ok. maybe - just maybe - the file is already in its minimun allocated size. To check the allocated size open Enterprise Manager, Select the server and the database, right click the database and select "Properties"

In the Transaction Log page/tab, check what the Space allocated for this file is. Notice that the file cannot be shrunk to anything smaller than this number.

You can also use this page to determine how the log file is allowed to grow.

Another option (to the one I posted previously), is to entirely clean up the log file. However, before doing so, make you have a fresh backup of your DB.

Here's how to do it using Enterprise Manager:
- Open Enterprise Manager and expand the "databases" leaf in the Server you where the database resides

- Right click on the desired database

- Select All tasks -> Shrink database

- since you only want to shrink the log file, click on "Files..."

- Select the log file

- Select "Shrink file to " and then specify the minimun size allowed.

- click ok.

Yours,

Alex
0
 

Expert Comment

by:gchavezr
ID: 6960329
This utility permit you to reduce in a few minutes the Log file for a database.

Run 3 minutes approximately if doesn´t stop you must stop manually, drop table t1 an see the size of log file.

--
 
dbcc shrinkfile(1,notruncate)
dbcc shrinkfile(2,truncateonly)
create table t1(char1 char(4000))
go
declare @i int
declare @byContador smallint
set @byContador = 1
select @i=0

while @byContador < 10000
 begin
   while @i<10000
     begin
       insert into t1 values ('a')
       select @i=@i+1
     end
   truncate table t1
   backup log tempdb with truncate_only
  set @byContador = @byContador + 1
 end
go  
drop table t1

0
 
LVL 35

Expert Comment

by:David Todd
ID: 6961459
Hi,

The quickest way to shrink a log file is to (carefully) detach the database, delete/move/rename the log file, and reattach the database when SQL will create an empty log file.

Regards
  David
0
 

Expert Comment

by:gchavezr
ID: 6961468
RUN THE(MY)UTILITY IS QUITE SAFE.....
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Expert Comment

by:gchavezr
ID: 6961480
This utility automatically forces checkpoint to database and recalculates the logfile size at the same time.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 6961495
Hi,

gchavezr:
On occassions this doesn't result in the log file shrinking immediately, and maybe not all the way back to the original size.

I try this when my clients don't have the time to let me detach the database.

Regards
  David
0
 

Accepted Solution

by:
gchavezr earned 100 total points
ID: 6961600
I proved this utility many, many times. I recommended because I have the proff that this works very well. Please see this URL:

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=19989

and tell me what is your opinion...

Note: if you push on link Listing 1 you could see the code that I give you.

Regards
gchavezr




0
 
LVL 35

Expert Comment

by:David Todd
ID: 6961613
Hi gchavezr,

I checked out the link - thanks.

Like I said I've used both. Perhaps not with the shrinkfile lines at the top. I've taken a note of that and will try that next time.

Thanks for your input.

Regards
  David
0
 

Expert Comment

by:gchavezr
ID: 6961647
Was a pleasure, my friend

gchavezr




0
 

Expert Comment

by:sukesh
ID: 7260621
Hi Kelvsat,
I hope ur issue is solved by now with the above recommendations. If not read below...

Explanation:
The technique explained above doesn't work in all the cases like it happened with me.  If the database ur talking abt is using Replication(Publisher) and ur log file size is shooting-up, then its bcoz of some error in Replication.

Solution: (issue this command from Profiler)
Backup log Truncate_only

If it gives an error saying that pending replication exists... Use the following command to reset these pending replication. With this you can manually tell the server that a transaction has been replicated.

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1

and then go ahead Take a Full Backup(during which the log will be cleared) and Shrink the DB thru Enterprise Manager.

WOW !!! It worked...my 50MB DB bcame 1GB in a day...and this article is the result of that...

Rgds
Sukesh
0
 

Author Comment

by:Kelvsat
ID: 7300780
Sorry it's not at my case.
0
 
LVL 4

Expert Comment

by:MSSystems
ID: 23865786
Is your database running in a production environment?
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

744 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

14 Experts available now in Live!

Get 1:1 Help Now