• Home
  • About
    • Disclaimer
  • Archives
  • Tools

How to: shrink an SQL log file.

Dec24
2011
Written by david

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.

VN:F [1.9.20_1166]
please wait...
Rating: 8.0/10 (1 vote cast)
VN:F [1.9.20_1166]
Rating: +3 (from 3 votes)
How to: shrink an SQL log file., 8.0 out of 10 based on 1 rating
Posted in Command Line, How To:, MS SQL 2008, Windows - Tagged CMD, How to, MS SQL 2008
SHARE THIS Twitter Facebook Delicious StumbleUpon E-mail
Similar posts
  • Super Control Panel (God Mode) on Win... — I found it on the web and I think it’s a great tool...
  • Windows Error Reporting (WER) and low... — I don’t use Widows Error Reporting service and it appears...
  • Windows Server 2008 R2, after install... — After installing Service Pack 1 on a few of my Windows 20...
  • Fix Event ID: 13555 — I got this error during a migration test of an SBS2003 to...
  • SBSMonitoring “heavy disk usage... — Had this problem with SBS 2008 server last week. The serv...
« Fortigate rates legitimate web sites as “pornography”
» SBSMonitoring “heavy disk usage”

Categories

  • Command Line (11)
  • Exchange (8)
    • 2007 (4)
    • 2010 (5)
  • Fortigate (10)
  • FreeNAS (2)
  • HmailServer (1)
  • How To: (8)
  • MS SQL 2008 (4)
  • Networking (4)
  • Open Source (1)
  • PowerShell (2)
  • Recovery (1)
  • SBS (8)
    • SBS 2008 (6)
    • SBS 2011 (3)
  • Terminal Server (2)
  • Veeam (2)
  • VMware (6)
  • Windows (18)

Links

  • חי הדפסות – דפוס משי, חולון
  • שלישיה – זה לא מה שחשבתם

Archives

  • March 2018
  • January 2018
  • September 2015
  • January 2015
  • August 2014
  • May 2014
  • December 2013
  • January 2013
  • October 2012
  • September 2012
  • July 2012
  • June 2012
  • May 2012
  • January 2012
  • December 2011
  • November 2011
  • October 2011
  • September 2011
  • July 2011
  • June 2011
  • May 2011
  • April 2011
Site Meter

EvoLve theme by Theme4Press  •  Powered by WordPress How to do.....IT