Solved

how shrink sql 2005 log files

Posted on 2015-01-08
14
179 Views
Last Modified: 2015-01-09
Hi,

we have a Server 2003 with SQL2005 installed.
the database is 50 GB but I have 2 ldf files that are 250GB in size.
HDD space is running out because of that.
I'm aware that these are the log files, I backed up the whole databse and also only the protocol in SQL management studio and shrink the database and protocol, but it did not help.  
What can I do now?

Regards
Lennox
0
Comment
Question by:lenn0x
  • 4
  • 4
  • 3
  • +3
14 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40537443
Back the log files up in the maintenance plan. Or set the recovery mode to simple.
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40537445
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40537520
What for you need 2 ldf files?
Transaction log files are written sequentially so the 2nd file would be only used after the 1st one is full.
0
 
LVL 5

Expert Comment

by:NARANTHIRAN
ID: 40537604
Use the following command to reduce the log file size
OpenSQL Query Analyzer and type  the following command,
and run the command until i get the msg
"DBCC execution completed. If DBCC printed error messages, contact your system administrator"

Dbcc shrinkdatabase (databasename,0)
backup log databasename with no_log
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40537607
I'd disagree with shrinking the database. It serves no great purpose, in fact it can degreade performance. The database performs better if it has free space in it. If you back the log file up it will commit everything to the database and the log file will shrink.

http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40537696
Dbcc shrinkdatabase (databasename,0)
You don't need to shrink a database because of the transaction log file.
I'd disagree with shrinking the database. It serves no great purpose, in fact it can degreade performance.
And that's why. I'm 200% with Lee Savidge.
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 250 total points
ID: 40538215
Have a read of this article: http://www.experts-exchange.com/Database/MS-SQL-Server/A_11077-How-to-shrink-a-bloated-log-file.html

Backing up your DATABASE is not enough to stop your log file growing.  You must backup your log files.  If you're just doing full backups, your log file will continue to grow.

It is only after a log backup that the space in the TLog is marked as able to be reused.

If you have the space on your backup device, I would recommend backing up the transaction log, then shrinking the logfile (not the database file).

As a last resort ONLY would I switch to simple, shrink the log, the switch back to full and immediately take a full backup.

Just be aware that switching to simple recovery mode will break your recovery chain and you lose the ability to perform point in time recovery (although it sounds like you're not taking log backups anyway).

I have discovered though, through practical application of this, that sometimes you need to run backup your log twice, before the shrinking of the logfile really gives you any noticeable savings of space.  I really can't explain why that is so, but it is something I've noticed in doing this in the past.

Most of the above is covered in the article I linked, have a read and please come back with any questions.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40538229
I have discovered though, through practical application of this, that sometimes you need to run backup your log twice, before the shrinking of the logfile really gives you any noticeable savings of space.  I really can't explain why that is so, but it is something I've noticed in doing this in the past.
It's because during the first transaction log backup there was not commited transactions in the tlog. Active transactions can't be released.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40538789
Given that the log files are huge, I don't see the point in actually backing them up.  You'll never use them to forward recover anyway.

Since you're still in SQL 2005, you can resolve this the easy way:
BACKUP LOG <database_name> WITH TRUNCATE_ONLY

DBCC SHRINKFILE ( 2, 20480 )

You can remove the second log file as soon as it's not in use.  As noted above, there is no point in having two log files.

If the db is in full recovery mode, run a full backup as soon as you can.
0
 
LVL 1

Author Comment

by:lenn0x
ID: 40540016
thanks you for your answers.

what I did until now:

backed up the whole db several times. (just to be sure)
then I backed up the transaction log, only one time.

now if I go to "shrink file/Protocol" in SQL Manager I see that the available free space is 175610,31 MB (91 %).
but if I click okay to shrink it, it dows not shrink, only a very small amount. 175388,97 MB (91 %)

So now I will take a second backup of the transaction logfiles, as Steve Wales says.

Just to be sure: I do backup using SQL Manager, right-click on the database and choose "Task/Backup" and choose "Full" or Transaction Protocol" - that is okay, isn't it?
0
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 250 total points
ID: 40540020
Execute the command from a query window:

https://support.microsoft.com/kb/907511

DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS

Specify a target size of say 10mb
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40540023
With which file are you working on? The first log file or the second one?
Also check for long running processes and jobs.
0
 
LVL 1

Author Comment

by:lenn0x
ID: 40540040
the second log file is not important, it is from another database that some special "boss person" here has copied to that directory.  As you all said it is only one ldf file.
0
 
LVL 1

Author Comment

by:lenn0x
ID: 40540089
thank you so much!
that did it.

DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS

Thank you!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query resolving a string conversion issue 26 39
the term "Features Not Supported in the Next Version of SQL Server" 12 30
Sql query 107 29
MS SQL Pivot table help 4 14
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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 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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

867 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

20 Experts available now in Live!

Get 1:1 Help Now