?
Solved

Shrink and defrag the ldf file

Posted on 2014-12-18
16
Medium Priority
?
241 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +3
16 Comments
 
LVL 70

Expert Comment

by:Qlemo
ID: 40506713
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
ID: 40506716
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 70

Expert Comment

by:Qlemo
ID: 40506728
At 5% used I don't think it's a question of the recovery model...
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:Eprs_Admin
ID: 40506735
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 35

Expert Comment

by:ste5an
ID: 40506768
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 70

Expert Comment

by:Qlemo
ID: 40506774
To defrag, stop the MSSQL instance and use any arbitrary defrag tool,  including the Windows integrated one.
0
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 total points
ID: 40506831
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 70

Expert Comment

by:Qlemo
ID: 40507140
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
 
LVL 35

Expert Comment

by:ste5an
ID: 40507156
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 51

Expert Comment

by:Vitor Montalvão
ID: 40507222
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 70

Expert Comment

by:Qlemo
ID: 40507236
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:
Scott Pletcher earned 1000 total points
ID: 40507423
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
ID: 40513065
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:Scott Pletcher
ID: 40513175
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
ID: 40513201
and 2 means the ldf file ?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40513225
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

AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

762 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