1

I have a work place portal built on SharePoint 2010 and the SQL Server drive space is full. I am using SQL Server 2008 R2. Of the 800 GB disk space, the log files have taken approximately 730 GB. There are many huge log files and what I want to know is:

  1. How do I reduce the sizes of the log files?
  2. Which log files can I reduce?
  3. Can anybody help with a step by step guide on how to do this? This is a production server.
Emmanuel
  • 13
  • 3

1 Answers1

0

If your databases are in full recovery mode you will need to backup the transaction logs, when that is done you can shrink the log files and setup a regular log backup to minimize growth and keep good backups. I would reccomend that you read this: https://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx but first things first.

The first step is to find the largest log files which you can shrink without backups - So you have to find databases which are in simple recovery mode:

select d.name, d.recovery_model_desc as recovery,d.log_reuse_wait_desc as wait_desc
,f.name as [LogicalName]
,CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB]
from sys.databases d
 inner join sys.master_files f on d.database_id = f.database_id
 where f.type = 1
 order by [Total Size in MB] desc

This will give you an output similar to this:

name    recovery    wait_desc   LogicalName Total Size in MB
dba     FULL        NOTHING     dba_log     4693.50
dbb     SIMPLE      NOTHING     dbb_Log     1024.00

For each file which is in SIMPLE recovery and not having wait_desc = "active transaction" you can simply do 

use database [NAMEFromQuery];
GO
DBCC SHRINKFILE (N'LogicalNameFromQuery' , Truncateonly);

And then figure out why the log is growing.

Now for the databases in FULL Recovery mode you can do

ALTER DATABASE [NAMEFromQuery] SET RECOVERY SIMPLE WITH NO_WAIT

and then truncate the log as above to get out of trouble but remember which databases were in full recovery model.

Now read the article linked above and start making good backups.

Spörri
  • 4,734
  • 15
  • 28