Reduce log file size

You need to back up your transaction log (ldf file) and then run a shrink. Transaction log backups need to be done in the same way that you back up the database (mdf file). If you do not, then the behavior you are observing will happen.

Example of a command to back up the 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 through the interface:

Onedetail:Transactionlogbackupscanbebackedupandaccessedonline.

Ifyoudonotneedtransactionlogging,changeRecoveryMode(orrecoverymode)toSimple.Inthisscenario,logswillnotbegeneratedsoyoudonothavetobackup.

Note, however, that it all depends on the type of bank you are dealing with. Example: It is recommended that an ERP software bank have active transaction logging. One possible scenario would be a full day-to-day backup and hourly transaction backup.

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

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

It's been a while since I've developed this script to slow down the database logging.

Denrt 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

Try the following:

• Make a copy of your bank;
• Run% of%

Another option would be to unzip the database and try to attach only with the dump tran SEUBANCO with no_log dbcc shrinkdatabase (SEUBANCO,0) file in the folder.

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

  

Obvious: Doing while nobody is using the database

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 following:

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;