How to: shrink an SQL log file.

Once in awhile I meet an sql based program that won’t work or stop respond to client requests until I flush the SQL database log file.
I wanted to create a script so I can run it through the scheduled tasks and get rid of this annoying task.

This is what I needed to do:
To shrink SQL log file on a scheduled base I did the following:
create a text file and change the extension to sql (Example: Shrink_log.sql)
add this lines to the script file:

use DB_Name
go
dbcc shrinkfile (DB_log,TRUNCATEONLY)
go

change the DB_Name to your database name and the DB_log to your database log file name.
create a scheduled task to run the script file on a regular base:

osql -E -S ServerName -i c:\SQL-Script\Shrink_log.sql

You can use OSQL at a command prompt to execute the contents of a file using this syntax:

osql -U YourUserName -P YourPassword -S ServerName -d DatabaseName -n-1 -i DriveLetter:SQLFileNameAndPath.sql -o DriveLetter:LogFile.txt

Switches
 -U: login ID for the specified server
 -P: password for the login ID
 -S: server name
 -d: database upon which the script will be executed
 -n: removes numbering and the prompt symbol (>) from the output file
 -i: the .SQL file name (including drive letter)
 -o: an output file that details how the script executed (if at all)

One more thing, that for this script really works, you need to change the database recovery model from full to simple by:
1. open enterprise manager / SQL Server Management Studio.
2. right click on the database you want to shrink.
3. click on properties.
4. go to options tab.
5. in the middle you will see recovery model change it to “simple” then click on “ok” and try to shrink the database.

Enjoy.