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.
it does not let Atachar without the log file, on the command line, displays the following error Incorrect syntax next to the keyword 'Tran'.
– Rafael
just complementing, I switched to simple to perform the tests, until then it was full and with backup also full daily, I do not have separate backup of logs, and the base began to grow that way from a few days to here
– Rafael
I believe that in the next backup the
log
will decrease.– rubStackOverflow