SQL-полезное (Усечение LOG файла)

SQL SERVER – Shrinking Truncate Log File – Log Full

Sometime, it looks impossible to shrink the Truncated Log file. Following code always shrinks the Truncated Log File to minimum size possible.

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

====================================================================
Another best way to truncate log file with out taking transactional backup would be,

1. Change recovery model from Full to Simple ( using Interfaces) and then change it back to Full. By doing this your log file will be truncated.

2. Run DBCC ShrinkFile ( ‘LogfileName’ , 1)

By doing this, you will definitely loose your transactional log data that was stored in log file. But this will truncate Log file with out taking any backup.
USE temp;
GO
– Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE temp
SET RECOVERY SIMPLE;
GO
– Shrink the truncated log file to 100 MB.
DBCC SHRINKFILE (temp_Log, 100);
GO
– Reset the database recovery model.
ALTER DATABASE temp
SET RECOVERY FULL;
GO