Hi, 

SQL 2008R2 

Have a load of (Sharepoint) databases that have really large 150GB log files which are set to simple recovery.  What is the quickest way of shrinkign these?

I have been doing the following but I wonder if there is a quicker way:

• Full Backup
• Set to FULL recovery 
• Full Backup 
• Backup log with truncate 
• Shrink database 
• Shrink logfile 
• Set back to Simple.

Seems like I have to do two full backups for this to work, each one takes 45 mins and there are over 100 databases to do.

Actually I think I will leave these in FULL recovery mode and do hourly log backup/truncate. 

Any thoughts?

Cheers

 

• Edited by The real Slartibartfast Friday, January 30, 2015 2:10 PM typo

Since the DBs are in SIMPLE recovery mode, all you need is a manual checkpoint before a shrink (but you don't really need to shrink if the TLog file is going to grow again to that size). Unless you have a long running transaction, a manual "Checkpoint" command is all that is needed prior to shrinking:

Checkpoint

DBCC Shrinkfile(TLogFileLogicalName, XXXXX)

I have been doing the following but I wonder if there is a quicker way:

• Full Backup
• Set to FULL recovery 
• Full Backup 
• Backup log with truncate 
• Shrink database 
• Shrink logfile 
• Set back to Simple.

Seems like I have to do two full backups for this to work, each one takes 45 mins and there are over 100 databases to do.

Actually I think I will leave these in FULL recovery mode and do hourly log backup/truncate.

Full backup does issues a checkpoint but has no affect on transaction log. You are doing BLUNDER with your databases so please stop it. Above will not help you at all

Check

what is initial size of Log file

What is autogrowth.

What does below query return for sharepoint databases

select log_reuse_wait_desc from sys.databases

Hello,

Please read the procedure provided by Kimberly on the following article.

http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

• Edited by Alberto MorilloMVP, Moderator Friday, January 30, 2015 7:04 PM

I dont think shrinking is going to help you as soon the log files are going to grow.

I would think a better strategy would be to do below

• Issue a checkpoint
• Shrink to a decent limit using dbcc shrinkfile
• Change to Full recovery mode
• Schedule transaction log backups every 1 hour or 30 minutes (depending on how much tlog is getting utilized )

Since db's are in simple recovery mode, you dont have to take such a long route to shrink. And in simple recovery mode, you dont have control on the log file, it grows as much as transactions need space and you will have to do frequent shrinking which is not good.

Hi, 

Picking one of the average databases, the MDF= 97GB and the LDf=87GB.  

The initial size for this logfile was 84GB, seems high to me but I didn't set any of these up.  

log_reuse_wait_desc shows the string "NOTHING" for this database_id. 

Autogrowth is 100MB. 

Space is now a huge issue on this server and I'm struggling to get extra disks added to the VM, so I thought to free up some space.  I know shrinking logs regulary is not always a good idea, but needs must on this occasion.  

Besides, why would a LDF be almost the same size as an MDF, especially if it's almost 100GB. 

 

The initial size for this logfile was 84GB, seems high to me but I didn't set any of these up.  

log_reuse_wait_desc shows the string "NOTHING" for this database_id. 

Autogrowth is 100MB. 

The problem here is incorrectly configured Log file size with *Initial* size( which is not actually initial size)  of 84 G so you normally cannot shrink beyond it this is the issue. Can you try alter database command to change file size

ALTER DATABASE db_name MODIFY FILE ( NAME = N'mylog', SIZE = xx, FILEGROWTH = 0)

Please try this command. Here XX is the size you want

You need to rectify the initial file size. Use ALTER DATABASE ...

So the logfile size cannot be shrunk below it's initial allocation? 

I normally set initial allocation to be about 20-25% of the MDF.  Having the MDF & LDF about the same size sounds excessive to me.  

The problem here is incorrectly configured Log file size with *Initial* size of 84 G so you normally cannot shrink beyond it this is the issue. Can you try alter database command to change file size

So what would the steps be? 

• Full backup 
• Log backup/truncate
• Alter database 
• Shrink files

http://social.technet.microsoft.com/wiki/contents/articles/22661.sql-server-misleading-database-initial-size-label.aspx

SQL Server do not remember the initial size of the log file. The GUI speaks rubbish when it present the file size ad "initial" - it is actual the current size the GUI presents. All you need to do to shrink the log in simple mode is SHRINKFILE, CHECKPOINT (in the database) and repeat those until you have desired size.