Solved

Cannot shrink my Log in SQL2008 R2

Posted on 2011-09-26
30
1,893 Views
Last Modified: 2012-05-12
I have a MDF file with 30GB and the LDF is 74 GB!!!!, the database is in full mode. I have programmed a complete backup every day at 22:00 and a backup of transaction log every hour from 08:00 to 21:00. The LDF file grows up more than 1GB every day.

Looking for a solution i´ve found the following Script

USE mydatabase;
GO

ALTER DATABASE mydatabase
SET RECOVERY SIMPLE;
GO

DBCC SHRINKFILE (mydatabase_Log, 1);
GO

ALTER DATABASE mydatabase
SET RECOVERY FULL;
GO


But it doesn´t work. I obtain the message: " Cannot shrink log file %d (%s) because of minimum log space required."
I still have a lot of free space in the disk taht contains the ldf file, but i need a solution that works.

Thanks a lot
0
Comment
Question by:j_meca
[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
  • 13
  • 6
  • 5
  • +4
30 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 36600018
Back the log up first then run this script.
0
 
LVL 8

Expert Comment

by:dba2dba
ID: 36600138
Change the below command: (Making the log size to 4G which is a reasonable log size instead of 1 MB as in your script)

DBCC SHRINKFILE (mydatabase_Log, 4096);
GO

Also, If you plan to setup the db in full recovery mode, you need to enable log backups which helps you in point in time recovery.

http://www.reliasoft.com/support/rs40005.htm

http://www.simple-talk.com/sql/database-administration/database-maintenance-plans-in-ssms-an-overview/


If you dont setup log backups , you need to leave the DB in Simple recovery.


Thanks,
0
 
LVL 1

Author Comment

by:j_meca
ID: 36600205
As I explain in my post, of course, i have programmed log backups every hour with de database in full mode.
Ok, I am going to try with a log size of 4 GB and i´ll tell you. I´ve to wait until 22:00 to do it.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 17

Expert Comment

by:Chris Mangus
ID: 36600589
It sounds like you're trying to shrink the logfile to a size smaller than it was created at which, I believe, you can't do.
0
 
LVL 10

Expert Comment

by:sqlservr
ID: 36708381
for a 30 GB database atleast 10% of its mdf file has to be allocate for its ldf file so use the below script

USE mydatabase;
GO
checkpoint
ALTER DATABASE mydatabase
SET RECOVERY SIMPLE;
GO

DBCC SHRINKFILE (mydatabase_Log, 3072);
GO

ALTER DATABASE mydatabase
SET RECOVERY FULL;
GO
0
 
LVL 1

Author Comment

by:j_meca
ID: 36708424
I´ve read that shrinking ldf file can causes database inconsistency... is it true?
I´m going to try the script with your modifications and i´ll tell you the result, thanks a lot
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 36710536
I've never heard that shrinking an ldf can cause inconsistency and I've been working with SQL Server since 4.21.
0
 
LVL 1

Author Comment

by:j_meca
ID: 36710797
Thank you, I think there are a lot of people who write in the forums without enough knowledge.
When the work day ends I will execute the script, I've done in the test database and it worked well.
Thanks again. If all works ok i´ll tell you and I close the question.
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 36711285
Personally, I don't shrink logs unless something has blown them up to a size that is abnormal for the particular database.  If your logs are sizing up to 1 gig a day I wouldn't shrink it any smaller than 2 gigs, just for safety sake.  If you shrink it under 1 gig you're going to take a performance hit when the log expands back out.

Set the logs to 2 gigs, do your tlog backups and then just leave them alone.

For what it's worth, I'm not aware of any "best practice" for setting log size, such as the quoted 105 figure above.  It all depends on the amount of UPDATE/INSERT/DELETE activity your database encounters.  I've seen very small databases that have quite large log files and it's normal for that particular database.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36711958
>>I´ve read that shrinking ldf file can causes database inconsistency... is it true?<<
No inconsistency, just transaction log fragmentation, which results in lousy performance.  Read Kimberley Tripp's article on the subject.

>>For what it's worth, I'm not aware of any "best practice" for setting log size, such as the quoted 105 figure above.  It all depends on the amount of UPDATE/INSERT/DELETE activity your database encounters.<<
Absolutely.  There is no magic number, it all comes from experience.
0
 
LVL 1

Author Comment

by:j_meca
ID: 36712750
Oh my God!!! it doesn´t work. I obtain the message: " Cannot shrink log file %d (%s) because of minimum log space required." again. I have tried with 4GB, 3 GB, 16GB, 24GB .......

I don´t know what to do!

Any idea? Remember SQL 2008 R2....
0
 
LVL 1

Author Comment

by:j_meca
ID: 36712785
Perhaps through Managament Console?
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 36713884
Management Studio is going to run the same SQL command to try the shrink.

Let's try a couple things.  Can you please run sp_helpdb and post the output?  Also run Select name, log_reuse_wait_desc from sys.databases and post that output.

And, are you absolutely certain that they database is in SIMPLE mode?  If it is in SIMPLE mode, issue a CHECKPOINT command and then try the DBCC SHRINKFILE again.
0
 
LVL 1

Author Comment

by:j_meca
ID: 36715634
I don´t know if the database is in simple mode when the script runs, i suposse that it works: the database goes to in simple mode, then I shrink and finally the database goes to full mode.
It could be implemented step by step and in the Managament Studio checking that the database changes, first, to simple and after the shrink to full mode.

I attach the screens with the two outputs:

capture-1.jpg
capture-2.jpg
0
 
LVL 39

Expert Comment

by:Krzysztof Pytko
ID: 36716244
I have recently the same issue. I needed to cut log and I couldn't. I dig in the Internet and after read a lot of SQL-2008R2 related pages I wrote a simple script for that.

You may try to use on your SQL server. Run it on SA user

 
ALTER DATABASE [DatabaseName] SET RECOVERY SIMPLE
USE [DatabaseName]
DBCC SHRINKFILE([DatabaseName_log], 1)
ALTER DATABASE [DatabaseName] SET RECOVERY FULL

Open in new window


Regards,
Krzysztof
0
 
LVL 1

Author Comment

by:j_meca
ID: 36716272
It is like mine, just change the first two sentences of order?
0
 
LVL 39

Expert Comment

by:Krzysztof Pytko
ID: 36716286
and database name in square brackets :)

[DatabaseName]

run script on SA user (SQL user)

It worked for me :) (exactly I did it on monday)

Krzysztof
0
 
LVL 1

Author Comment

by:j_meca
ID: 36720276
I'm definitely and absolutely desperate, nothing works. Step by step, checking that the database goes into simple, returning to full mode ..... anyway, the ldf file is the same size.

help me please!
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 36742244
It sounds like you may have (or had) Replication or Log Shipping setup, either that or your have Open Transactions that prevent you from shrinking the database.  If you are convinced this is not the case.  Then here is what you need to do:
ALTER DATABASE YourDatabaseName SET RECOVERY SIMPLE
Confirm that the database is using Simple Recovery Model
Issue some 2 or 3 CHECKPOINT
DBCC SHRINKFILE('YourTransactionLog', TRUNCATEONLY)
Verify the Transaction Log File is small.
ALTER DATABASE YourDatabaseName MODIFY FILE (NAME =  YourTransactionLog, SIZE = 4000) -- Change 4GB as appropriate
ALTER DATABASE YourDatabaseName SET RECOVERY FULL
Do a full backup, as the above has killed any possibililty of a point-in-time restore.

Do not do this on a scheduled basis as some have suggested.  This is only for emergencies.

Good luck.
0
 
LVL 1

Author Comment

by:j_meca
ID: 36813486
I have read that this instruction doesn´t work in SQL 2008 R2.............DBCC SHRINKFILE('YourTransactionLog', TRUNCATEONLY)
0
 
LVL 10

Expert Comment

by:sqlservr
ID: 36813767
Why ‘truncate_only’ is not a recognized backup option
check this link
http://www.brentozar.com/archive/2009/08/backup-log-with-truncate-only-in-sql-server-2008/
0
 
LVL 1

Author Comment

by:j_meca
ID: 36813941
Well, after reading a lot of articles i´ve found the possible reason,
When  I checked the sys.databases log_reuse_wait_desc column and it shows "replication" as the reason the log won't allow shinking. The problem is that there is no replication (publisher or subscriber) on the database or on the server.

So... why my database shows REPLICATION??, how can i remove it?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36815018
>>DBCC SHRINKFILE('YourTransactionLog', TRUNCATEONLY) <<
Then you have read incorrectly.  Let me spell it out for you "TRUNCATEONLY" <> "TRUNCATE_ONLY"

If you want me to take the time to copy and paste from SQL Server BOL so that you can read something you have not bothered to do, I can do that, too.  

So it is obvious that this whole situation is not as urgent as you paint, so I guess you don't need my help after all.

Good luck.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36815036
>>Well, after reading a lot of articles i´ve found the possible reason<<
Duh, I told you that here http:#a36742244.  Here it goes again:
It sounds like you may have (or had) Replication or Log Shipping setup,
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36815053
>>why my database shows REPLICATION??, how can i remove it? <<
Now that you have "discovered" the problem, I suggest it is time to close this long drawn out question and start a new one.  Make sure to include a link to the new thread here so that we can give you the solution there.
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 36815061
Also was the point behind me asking for the output of 'elect name, log_reuse_wait_desc from sys.databases '
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36815109
I agree.  Oh well, as I stated this problem could not have been all that urgent or they simply don't want to help themselves.
0
 
LVL 1

Author Comment

by:j_meca
ID: 36815253
the output of 'elect name, log_reuse_wait_desc from sys.databases ' is up,  in this "long drawn" and the screen shows that the database was "REPLICATION".

Well, i got to remove it, this night i´ll try to run the script to shrink, and if it works i´ll close it tomorrow.
Thanks a lot
0
 
LVL 1

Author Comment

by:j_meca
ID: 36815331
I am so sorry acperkins!!! of course, you told me!! but I assumed that the database had not replication. In fact do not yet know how he could get in that state unless I have made, side effects of something???

Sorry again!!
0
 
LVL 1

Author Closing Comment

by:j_meca
ID: 36815362
Partially probably because i´m not a DBA and I assumed that a database couldn´t have replication setup without my intervention
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…

749 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