Click here to Skip to main content
12,405,303 members (63,235 online)
Rate this:
Please Sign up or sign in to vote.
See more: Windows SQL-Server , +

As you know, MS-SQL server generates transaction log file. After so much transaction it becomes huge. I want to shrink those and release the memory after regular interval of time.

I know how to do it using sqlcmd.

Server_Name -U username -P passsword
ALTER database dbName SET RECOVERY simple
DBCC shrinkdatabase (dbName)

Now I want to execute this script automatically. I am thinking to use task scheduler so that I can trigger these commands on specific time.

Regards and Thanks.
Posted 27-Feb-13 23:54pm
Updated 27-Feb-13 23:57pm

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

I have never needed to run the ShrinkDatabase command but I have other BAT files that I use for scheduled maintenance of my database. Below is an example using your SQL commands.

1. Create C:\BAT\SHRINKDB.BAT containing these statements:
cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn
SQLCMD -S YOURCOMPUTERNAME\SQLEXPRESS -E -w 166 -e -i C:\BAT\ShrinkDB.sql -o C:\BAT\ShrinkDB.log
After execution, C:\BAT\ShrinkDB.log will contain the output of SQLCMD.

2. Create C:\BAT\SHRINKDB.SQL containing the SQL commands:
ALTER database dbName SET RECOVERY simple
DBCC shrinkdatabase (dbName)

3. Use Windows Task Scheduler to schedule C:\BAT\ShrinkDB.bat
phil.o 28-Feb-13 9:16am
My 5! ;)
Just ensure that the account the task is running under has sufficient rights on the database to execute these commands.
Member 10435696 30-Jan-14 6:46am
Your 1st Point Says Create C:\BAT\SHRINKDB.BAT
But i cant find BAT Folder in C drive...Plz help how to create BATCH file.I want to schedule a procedure for daily execution at midnight
Mike Meinz 30-Jan-14 7:22am
Click "New Folder" and create a BAT folder or a folder with whatever name you like. I use BAT because it is short and easy to remember.
Member 10435696 30-Jan-14 7:33am
k Thanks for reply and what you store in ShrinkDB and how you do it??
Member 10435696 30-Jan-14 7:55am
SQLCMD -S YOURCOMPUTERNAME\SQLEXPRESS -E -w 166 -e -i C:\BAT\ShrinkDB.sql -o C:\BAT\ShrinkDB.log

plz explain -E -W & 166 -E
Mike Meinz 30-Jan-14 8:40am
The Microsoft documentation for SQLCMD explains the parameters. See SQLCMD Utility.

-E means use Trusted Connection
-e means echo the input to the output log
-w 166 means use 166 as the width of the output log
-i means that the input SQL command filename follows (C:\BAT\Shrinkdb.sql)
-o means that the name of the output log file follows (C:\BAT\Shrinkdb.log)

See 2. in Solution 1. That is an illustration of the contents of the SHRINKDB.SQL file that is read by SQLCMD. I used Notepad.exe to create both ShrinkDb.SQL and ShrinkDB.BAT
Member 10435696 30-Jan-14 23:58pm
k thanks..U have resolved my issue..Thanks

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.160721.1 | Last Updated 30 Jan 2014
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100