Reduce Log file size

You need to back up your transaction log (ldf file) and then perform a shrink. The backup of the transaction logs needs to be done in the same way as you backup the bank (mdf file). In case you do not, then this behavior you are observing will happen.

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:

A detail: the backup of the logs of transaction can be done with online banking and being accessed.

If you do not need to log transactions, change Recovery Mode to Simple. In this scenario, the logs will not be generated and thus you do not need to back up.

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 have active transaction logs. A possible scenario it would be a full backup every day and hourly transactions backup.

Already a simpler application bank, can only have the full backup without the need for transaction logs.

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

It's been a while since I developed this script to decrease the data log of the databases.

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

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 attack only with the file MDF in the folder.

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

Obvious: Do While no one is using database

1. Full Backup.
2. right-click on the base and then detach.
3. access the directory that is the database file, usually is: C:\Program Files \ Microsoft SQL Server\MSSQL10_50.MSSQLSERVER \ MSSQL \ DATA may change depending on the version;
4. rename the log file that has the extension .ldf;
5. No Management Studio, right click on database, Attach, selects the database file, removes the row from the log that it appears at the bottom and clicks OK.

If you are sure that you do 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, putting 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;