Shrink and defrag the ldf file

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 ?
Eprs_AdminSystem ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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
Ammar GaffarSoftware EngineerCommented:
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
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
At 5% used I don't think it's a question of the recovery model...
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Eprs_AdminSystem ArchitectAuthor Commented:
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
ste5anSenior DeveloperCommented:
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
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
To defrag, stop the MSSQL instance and use any arbitrary defrag tool,  including the Windows integrated one.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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
ste5anSenior DeveloperCommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
True is that you should always start a defrag inside the DB with the DBMS' own means after a file defragmentation.
0
Scott PletcherSenior DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eprs_AdminSystem ArchitectAuthor Commented:
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
Scott PletcherSenior DBACommented:
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
Eprs_AdminSystem ArchitectAuthor Commented:
and 2 means the ldf file ?
0
Scott PletcherSenior DBACommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.