• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2302
  • Last Modified:

Cannot shrink my Log in SQL2008 R2

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
j_meca
Asked:
j_meca
  • 13
  • 6
  • 5
  • +4
1 Solution
 
Lee SavidgeCommented:
Back the log up first then run this script.
0
 
dba2dbaCommented:
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
 
j_mecaAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Chris MangusDatabase AdministratorCommented:
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
 
Ramesh Babu VavillaCommented:
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
 
j_mecaAuthor Commented:
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
 
Chris MangusDatabase AdministratorCommented:
I've never heard that shrinking an ldf can cause inconsistency and I've been working with SQL Server since 4.21.
0
 
j_mecaAuthor Commented:
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
 
Chris MangusDatabase AdministratorCommented:
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
 
Anthony PerkinsCommented:
>>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
 
j_mecaAuthor Commented:
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
 
j_mecaAuthor Commented:
Perhaps through Managament Console?
0
 
Chris MangusDatabase AdministratorCommented:
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
 
j_mecaAuthor Commented:
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
 
Krzysztof PytkoActive Directory EngineerCommented:
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
 
j_mecaAuthor Commented:
It is like mine, just change the first two sentences of order?
0
 
Krzysztof PytkoActive Directory EngineerCommented:
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
 
j_mecaAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
j_mecaAuthor Commented:
I have read that this instruction doesn´t work in SQL 2008 R2.............DBCC SHRINKFILE('YourTransactionLog', TRUNCATEONLY)
0
 
Ramesh Babu VavillaCommented:
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
 
j_mecaAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
Anthony PerkinsCommented:
>>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
 
Anthony PerkinsCommented:
>>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
 
Chris MangusDatabase AdministratorCommented:
Also was the point behind me asking for the output of 'elect name, log_reuse_wait_desc from sys.databases '
0
 
Anthony PerkinsCommented:
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
 
j_mecaAuthor Commented:
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
 
j_mecaAuthor Commented:
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
 
j_mecaAuthor Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 13
  • 6
  • 5
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now