32

I have a database which has a 350 MB data file (.mdf) and a 4.9 GB log file (.ldf). The recovery model is set to FULL.

When I try to shrink the log file, it's not shrinking.

I know shrinking a database is not good and it should not be done. But still i am trying to do it for shrinking the log file.

When I ran

DBCC SQLPerf(logspace) 

I found that the log size is 4932 MB and Log space used is 98.76%!

Then I tried this command

USE <databasename>;
DBCC loginfo;

Now almost all VLFs are "status 2" which means all are in use.

I tried to take a log backup and then shrink the log file. Shrinking didn't reduce the size.

I changed the recovery model to SIMPLE and tried shrinking again, but this also didn't help.

I checked for open transactions

DBCC opentran (database);

and found that no transaction is open now.

What is stopping me from shrinking the log file? How can I solve this?

0

6 Answers 6

18

Here is the answer to my own question.

Run the below query to get information about the log file's reuse wait:

SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'DBName'

I got the following output:

log_reuse_wait_desc
-------------------
REPLICATION 

There were some replication-related objects remaining in the database, even after removing the replication.

To remove the replication from the database, sp_removedbreplication can be used. But it didn't work for us as replication was not active at the time and actually replication had been removed long before.

The solution was to import the database contents to another database using the import option of SQL Server.

1
  • I had the same problem and used this to see that there was an active transaction in the db. log_reuse_wait_desc gave ACTIVE_TRANSACTION. As soon as the transaction completed the shrink worked just fine.
    – squillman
    Jun 1, 2017 at 14:52
12

Read How to Shrink SQL Server log for an explanation how the circular nature of the log may prevent shrink after truncation. Is possible that you log's last LSN point into a VLF that is at the tail of the LDF. Counter intuitively you must advance the log, by generating log writes, to allow it to shrink.

0
11

Steps for shrinking the log are going to be:

Backup transaction log through either SSMS or T-SQL and then perform a shrink.

commands for SSMS are under the tasks if you right click the database name.

BACKUP LOG <Databasename> TO DISK = N'<path\database_log.ldf';
GO

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

You will probably have to do this multiple times.

If there is a transaction or job blocking the action, use Activity monitor to identify the process and kill it, or use the SQL Agent job activity monitor to end the job.

source: http://support.microsoft.com/kb/907511

3
  • But problem i encountered is different.Please see my answer below
    – Navaneet
    Apr 30, 2013 at 14:33
  • Glad to hear you got it figured out, thanks for the update!
    – Cougar9000
    Apr 30, 2013 at 14:51
  • 1
    Incorrect syntax - an equals sign is missing: BACKUP LOG <Databasename> TO DISK = N'<path\database_log.ldf'; Apr 17, 2019 at 9:06
2

I have found that I have to perform 2 or 3 backups of both the database and the transaction log to get the transaction log to actually reduce in size. I have a database that was created with Full recovery model. Every night it performs backups of the database and the transaction log but inevitably the transaction log seems to continually grow over 2-3 weeks. When the remaining disk space gets to 1GB I will see that the transaction log is about 30GB. I followed the steps recommended by Microsoft and after the 4th or 5th iteration of backing up both the database and the transaction log the transaction log will finally release its extra space and shrink. Then I go back and delete the multiple backups I have created.

2
  • I think you are doing something wrong. If you take backup of log properly then unused log should be truncated. The commands given in my question may help you to solve the problem.
    – Navaneet
    Jan 7, 2014 at 6:40
  • I approve this, same happen for me just now - only after second log backup it allowed me to perform shrink
    – ujeenator
    Sep 27, 2021 at 14:25
0

You need to create a backup first, dependent on the backup model that is set up for the database before you can shrink the database.

You can try running this:

USE <databasename>
GO

BACKUP DATABASE <databasename> TO DISK '<absolute path goes here>\<databasename>.bak';
GO

Or you can do that from SSMS and use the graphical tools available (see here for details: http://msdn.microsoft.com/en-us/library/ms187510.aspx)

Once you've backed up your database you can compress it. However, shrinking the database is not a good idea since heavy index fragmentation will occour and searching for data will become slow.

Hope this helps.

5
  • I know how to do backup and truncate the log and reduce log file size. But for this database i am having problem.I just ran query select log_reuse_wait_desc from sys.databases where name = 'dbname' and found that replication is causing the problem .But i dont have replication set on it. So how to remove the repliaction from this db which is shown in log reuse wait_desc?
    – Navaneet
    Apr 30, 2013 at 12:44
  • Which SQL Server version are you using? Apr 30, 2013 at 12:58
  • Replication might be set as a Job, so open up the SQL Server Agent folder, and Expand the Jobs folder, check to see if there is a replication job set up and if so turn it off by right-clicking and selecting Stop job Apr 30, 2013 at 13:01
  • If you are using SQL Server 2005 and up then sp_removedbreplication 'DB_NAME' will remove replication. For sql server 2000 .. refer to blogs.msdn.com/b/repltalk/archive/2010/11/17/…
    – Kin Shah
    Apr 30, 2013 at 13:14
  • But problem i encountered is different.Please see my answer
    – Navaneet
    Apr 30, 2013 at 14:34
-10

My work around for the Replication that is blocking shrinking log file is:

  1. Set DB Recovery Model to Simple
  2. Take DB offline
  3. Create backup of log file (just in case)
  4. Delete log file
  5. Bring DB online

In my case it worked. After bringing DB online log was created automatically and it's size was 512kb instead of 70GB. But this is only a workaround. The root problem is not resolved. In my case we are using replication.

1
  • 5
    This is terrible advice, never delete your transaction log, all sorts of problems can come from this such as corruption
    – Tom V
    Aug 31, 2015 at 8:53

Not the answer you're looking for? Browse other questions tagged or ask your own question.