Solved

Cannot shrink my Log in SQL2008 R2

Posted on 2011-09-26
30
1,822 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
  • 13
  • 6
  • 5
  • +4
30 Comments
 
LVL 25

Expert Comment

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

Expert Comment

by:dba2dba
Comment Utility
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
Comment Utility
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
 
LVL 17

Expert Comment

by:Chris Mangus
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
Perhaps through Managament Console?
0
 
LVL 17

Expert Comment

by:Chris Mangus
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 1

Author Comment

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

Expert Comment

by:Krzysztof Pytko
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I have read that this instruction doesn´t work in SQL 2008 R2.............DBCC SHRINKFILE('YourTransactionLog', TRUNCATEONLY)
0
 
LVL 10

Expert Comment

by:sqlservr
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
>>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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
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…
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 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

10 Experts available now in Live!

Get 1:1 Help Now