Please visit my New Blog http://www.sqlserverdba.co.cc/

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

Code:

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


Here 1 is the file ID. To get the fileID you can run
Code:

SELECT file_id, name FROM sys.database_files


inside that DB or
Code:

sp_helpfile

also will do..

WITH TRUNCATE_ONLY option will be removed in a future version of SQL Server. Avoid using it in new development work, and plan to modify applications that currently use it.

Reference : http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/

Mr.Hallengren made a solution for backup, integrity check and index optimization in SQL Server 2005. The solution is based on stored procedures, functions, sqlcmd and SQL Server Agent jobs.

Dynamic selection of databases, e.g. USER_DATABASES.
Database state check.
Robust error handling and logging.
Database backup features.

Download and install the solution in one script.

Click here to browse the article