how to shrink an ldf file

[ful56_uk]

hi

 

how to shrink an ldf file in sql server 2008? one of my db ldf file is 68GB any way to reduce this?

[TechMonkey]

there is a script to do it but I don't have it to hand right this sec. I'll hunt it down.

[TechMonkey]

Found it

 

Batch File:

@echo off
if .%1==. goto DisplayUsage
if .%2==. goto DisplayUsage
echo shrinking log file for database %2
echo.
echo backup log %2 with no_log      >  "%temp%\shrink.sql"
echo dbcc shrinkdatabase (%2, 0)    >>  "%temp%\shrink.sql"
echo.                               >>  "%temp%\shrink.sql"
echo.
osql -S %1 -d %2 -Usa -P -n -i"%temp%\shrink.sql"
echo.
goto Exit
:DisplayUsage
echo Shink the log file of a SQL Database v1.00
echo.
echo Usage:  ShrinkDBLog \SIMS sims
echo.
echo Where:
echo    Server\Instance        is the SQL Server and the SQL Server Instance if one is present
echo    DatabaseName  SQL database name to shrink, eg sims
echo.
echo Examples:
echo     ShrinkDBLog AdminPC\SIMS sims
echo     ShrinkDBLog AnotherPC sims
echo.
pause
:Exit

 

Readme

1. Edit the batch file (ShrinkDBLog.bat) by adding the SA password after -P in the OSQL command line :

e.g. -Ppassword

2. Run the batch file from the command prompt using the following command (where  is the appropriate database [sims or ccsfms]) :

shrinkdblog \escc 

e.g. shrinkdblog server3\escc sims