Reduce Log File Size

Question

I’m in a saga with my database, today I have an 11GB mdf file and a 77GB log.
I have already searched in several forums, applied some reduction procedures via Management (Shrink) and via command like SHRINKFILE, the database is already as simple. but nothing makes it reduce, I need help.

Answer 1

You need to backup your transaction log (ldf file) and then run a Shrink. The backup of transaction logs needs to be done in the same way as you backup the database (mdf file). If you don’t, then this behavior will happen that you’re observing.

Example of a command to back up transaction logs:

BACKUP LOG [Mobius] TO  DISK = N'G:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\log.trn' WITH NOFORMAT, NOINIT,  NAME = N'Mobius-Log de Transações  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

The same can be done by the interface:

One detail: the transaction logs can be backed up with the online database and accessed.

If you don’t need to log transactions, change the Recovery Mode (or Recovery Mode) to Simple (simple). In this scenario, logs will not be generated and thus you do not need to backup.

Note, however, that it all depends on the type of bank you are dealing with. Example: it is recommended that a bank of an ERP software has an active transaction log. A possible scenario would be a complete backup every day and backup of hourly transactions.

A simpler application database, however, can only be backed up completely without the need for transaction logs.

The advantage of logs made every hour (for example), is the possibility to restore a backup up to a certain time. In this case, just restore the full backup and, on top of it, restore the hourly backups of the transaction logs to the desired time.

Answer 2

It’s been a while since I developed this script to decrease database log.

Denrto the cursor has a line for each version. The 2008 version also works for 2012

declare @cmd nvarchar(4000)
declare @bd varchar(100)
declare @file nvarchar(100)
declare @size nvarchar(100)
declare pap_log cursor read_only forward_only for 

SELECT 
    db_name(sf.dbid) as [Database_Name],
    sf.name as [File_Name],
    (sf.size/128.0 - CAST(FILEPROPERTY(file_name(fileid), 'SpaceUsed') AS int)/128.0) AS 'Available_Space_MB'

FROM    master..sysaltfiles sf
WHERE   groupid = 0
and db_name(sf.dbid) not in('model')
ORDER BY    Available_Space_MB  DESC



open pap_log
fetch next from pap_log into @bd,@file,@size
while @@fetch_status = 0
begin 
/*2005*/
--set @cmd='backup log '+@bd+' with no_log ;use '+@bd+';dbcc shrinkfile(['+@file+'],0);'
/*2000*/
--set @cmd='backup log '+@bd+' with no_log ;use '+@bd+';dbcc shrinkfile('+@file+',0);'
/*2008*/
set @cmd='use '+@bd+';dbcc shrinkfile('+@file+',0);'
exec sp_executeSQL @cmd
declare @filepath varchar(100)
print ''
print @bd
print rtrim(ltrim(@file+' '+@size))
select @filepath=filename from master..sysaltfiles where name=@file
print @filepath
print ''
fetch next from pap_log into @bd,@file,@size
end
close pap_log
deallocate pap_log

Answer 3

Test the following:

• Make a copy of your bank;
• Execute dump tran SEUBANCO with no_log dbcc shrinkdatabase (SEUBANCO,0)

Another option would be to untie the bank and try to Tach only with the file MDF in the briefcase.

Answer 4

If you are sure that you will not need the log, that is, you only want to reduce the size of the log file, you can do the procedure below:

1 - Check the name of your log file by running the code below:

-- Mostra as informações de seus arquivos
USE [NomeDoBanco]-- Alterar nome do banco
GO
EXECUTE sp_helpfile
GO

2 - Run the command below by placing the name of your log file:

USE [NomeDoBanco];

-- Obs.: Só é possível truncar o log se o banco estiver no modo Simple
ALTER DATABASE [NomeDoBanco] SET RECOVERY SIMPLE WITH NO_WAIT;

-- Limpa o arquivo de log.  
DBCC SHRINKFILE (NomeDoArquivo_log, 1); -- Atenção: Colocar o nome do arquivo de log e tamanho que se quer reduzir 

-- Volta o Banco para o modo FULL se for o caso
ALTER DATABASE [NomeDoBanco] SET RECOVERY FULL WITH NO_WAIT;

Answer 5

I took the same problem recently and solved it this way:

Obvious: Do while no one is using the database

1. Backup Full.
2. Right click on the base and then detach.
3. Access the directory that gets the database file, usually it is: C: Program Files Microsoft SQL Server MSSQL10_50.MSSQLSERVER MSSQL DATA can change depending on the version;
4. Rename the log file that has the . ldf extension;
5. In Management Studio, right-click database, Attach, selects the database file, removes the log line that appears below and click OK.