Solved

Shrink and defrag the ldf file

Posted on 2014-12-18
16
212 Views
Last Modified: 2014-12-22
Hi Experts,

we have a very big SQL logfile (LDF).
This is always filled with 1-5%.
Is it possible to shrink the file ?

And I need to defrag the LDF File, is this also possible ?
0
Comment
Question by:Eprs_Admin
  • 5
  • 3
  • 3
  • +3
16 Comments
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
To shrink the logfile, go into Management Studio, right-click on the DB, Tasks, Shrink Datafile - the remainder should be obvious.
After that, you should not need to run a defrag anymore, but you can always do as long as MSSQL is not running.
0
 
LVL 12

Expert Comment

by:Ammar Gaffar
Comment Utility
Hi,
Big log files caused mainly by database recovery model, in your case I guess it configured "Full", for more information about database recovery models in SQL  Server you can refer to this link:
http://msdn.microsoft.com/en-us/library/ms189275.aspx

For shrinking you can follow instructions in this link:
http://help.fogcreek.com/8686/how-to-shrink-sql-server-transaction-logs

I recommend to use test server, or take a full backup of your existing database before you try to do anything, this is my personal rule whenever I need to deal with such databases (size wise).

Good Luck
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
At 5% used I don't think it's a question of the recovery model...
0
 

Author Comment

by:Eprs_Admin
Comment Utility
ok now I know how to shrink.
I think it is better, to do this when no one works right ?

Can you also explain the way how to defrag ?
0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
Defrag makes only sense when you have plenty free disk space. Run defrag when you have low load on your server. Cause while running it you will some performance degradation.

But first of all, you should evaluate what is the cause for the large log file. Cause when it is due to regular processing, then it's better to allocate a larger log file after the defrag run.
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
To defrag, stop the MSSQL instance and use any arbitrary defrag tool,  including the Windows integrated one.
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
Comment Utility
To defrag, stop the MSSQL instance and use any arbitrary defrag tool,  including the Windows integrated one.
Don't defrag SQL Server files. You'll mess everything.
Fragmentation in databases occurs in data pages and engines already provide a tool for defragmentation that is rebuild indexes.
Also, you are talking about log files. Basically you don't need to read log files, only write on them and log files don't has data pages so you can't do nothing about it but shrink.
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Vitor is correct in parts. MSSQL does its best to prevent / handle fragmentation inside of the datafiles. Whether it uses info about the physical layout of the file I don't know. But to say file defragmentation does mess up the DB is plain wrong. True is that you should always start a defrag inside the DB with the DBMS' own means after a file defragmentation.

And again, after truncating the log file you won't need a defrag of it.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
Defragmenting the "physical" file can help. It depends on what storage it is placed and how this defragmentation change the order on that device. Thus when it improves sequential reads, then it is okay to defragment the files.

E.g. consider a SQL Server running a VMWare (ESX) and also the files are in this image. Then defragmentation of the "physical" files (what Windows - the guest) sees, does not help. Cause ESX does its own handling.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
But to say file defragmentation does mess up the DB is plain wrong.
I dare you to test a physical defragmentation and after that check the index defragmentation:
-- Check the index fragmentation 
SELECT  [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'DBName'), OBJECT_ID (N'TableName'), 1, NULL, 'LIMITED');

Open in new window

And it's better to check the index fragmentation before you run the physical defragmentation so you can compare the values.
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
True is that you should always start a defrag inside the DB with the DBMS' own means after a file defragmentation.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
Comment Utility
It is possible to shrink the log file, but you should only do so with line commands, never from the GUI:

USE [db_name]
DBCC SHRINKFILE ( 2, <desired_size_of_file_in_mb> )

For example, if the log file is current 16GB and you want to shrink it to 2GB, you would use 2048 as the value.  

However, for the very first shrink, it's very likely a good idea to fully shrink the log and then grow it back, to reduce physical fragmentation in the log file:

DBCC SHRINKFILE( 2, 1 )
ALTER DATABASE db_name MODIFY FILE ( NAME = log_file_name, SIZE = 2GB )

If the log doesn't shrink, wait and try again.  If it continues to not shrink, you'll need to look at the last open transaction on that db and check for replication or other processes that might force the log records to stay in use.

You shouldn't keep constantly shrinking the log file, though.  If it grows back to that size, leave it that size.  You would have to look at the recovery model and/or the code to figure out why the log is growing.

You'll never need to defragment in the log in SQL Server as it's a sequential file.  You can make the log file more contiguous physically on disk, which might help performance, esp. if the log file has a large number of different physical extents.  I use "contig.exe", since (1) it's free, and (2) it can be safely run even while SQL is executing.  But there are lots of other physical file tools available.
0
 

Author Comment

by:Eprs_Admin
Comment Utility
USE [db_name]
DBCC SHRINKFILE ( 2, <desired_size_of_file_in_mb> )

Sorry, can you explain the command ?
Where to put the database name ?
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
As I suspect you already know now, you have to already be in the context of the db you want to hrink.  That is, the db name does not go in the command itself, you must already be USEing that db:

USE [db_name_to_be_modified] --<-- set to the db name with the log file that needs shrunk / resized
DBCC SHRINKFILE ( 2, <desired_size_of_file_in_mb> )
0
 

Author Comment

by:Eprs_Admin
Comment Utility
and 2 means the ldf file ?
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Yes.  You can use the logical file name instead if you prefer.  Usually it's something like db_name_log.  You can get the logical names from the first column of this result:

EXEC db_name.sys.sp_helpfile
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

743 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

15 Experts available now in Live!

Get 1:1 Help Now