Click here to Skip to main content
11,706,323 members (45,551 online)
Rate this: bad
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
Edited 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 at 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 at 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 at 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 at 30-Jan-14 7:33am
k Thanks for reply and what you store in ShrinkDB and how you do it??
Member 10435696 at 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 at 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 at 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
0 OriginalGriff 142
1 Sergey Alexandrovich Kryukov 137
2 ppolymorphe 71
3 Mika Wendelius 70
4 chainerlt 70
0 OriginalGriff 9,050
1 Sergey Alexandrovich Kryukov 8,417
2 CPallini 5,189
3 Maciej Los 4,726
4 Mika Wendelius 3,636

Advertise | Privacy | Mobile
Web03 | 2.8.150819.1 | Last Updated 30 Jan 2014
Copyright © CodeProject, 1999-2015
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