I have a SQL Server 2008 database that has a data file of some 2GB in size, but the log file is over 8GB. With pre-2008 databases I could use the 'Backup log' and the
TRUNCATE_ONLY option but this is no longer available with 2008 and later databases.
I do have a script that truncates the log file:
ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC shrinkfile('MyDatabase_log', 1)
ALTER DATABASE [MyDatabase] SET RECOVERY FULL WITH NO_WAIT
This truncates the log file completely, but my question is: Does this affect performance?
I perform two Full backups daily so the log should not really be necessary as far as data roll-forward is concerned.