Click here to Skip to main content
14,739,311 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more: , +

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.
Updated 24-Jan-17 1:26am

1 solution

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
vijay kumar sahu 24-Jan-17 6:27am
After reading all the above comments by the experts I have tried it but seems like
it's not working for me. Could anyone check if I am doing something wrong.

Here is the bat file with command.
sqlcmd -S .\sqlexpress -E -i "C:\Vijay Sahu\Official\DB Queries\MCX_Test.bat" -o "C:\Vijay Sahu\Official\DB Queries\log.txt"

Bat file contains this command
insert into test (CurrentDateTime) values (getdate())

And my project ConnectionString=
<add name="MCX" connectionString="Data Source=GH-85HKG32\SQL2012;Initial Catalog=MCX;Integrated Security=True" providerName="System.Data.SqlClient"/>

Can someone help me out as soon as possible bcz of the deadline of the project. Actually
I am looking for this solution bcz my shared webshoting doesn't allow me to create a sql job agent...
Mike Meinz 24-Jan-17 7:56am
See SQLCMD Utility Documentation[^]

1. You specified the wrong server name in the SQLCMD parameters.
2. If you are not logged onto the same computer as the SQL Server, then you may not be able to use -E (Trusted Connection) and will have to specify the Username and Password using -U and -P parameters.

sqlcmd -S GH-85HKG32\SQL2012 -U <username> -P <password> -i "C:\Vijay Sahu\Official\DB Queries\MCX_Test.bat" -o "C:\Vijay Sahu\Official\DB Queries\log.txt"

Also, I suggest renaming the file that contains the SQL statements from "C:\Vijay Sahu\Official\DB Queries\MCX_Test.bat" to "C:\Vijay Sahu\Official\DB Queries\MCX_Test.sql" to avoid confusion. Use file extension ".bat" for batch command files and ".sql" for files containing SQL statements.

No such word as "bcz".
I think you mean "because."
vijay kumar sahu 25-Jan-17 8:02am
Ohhh!!! Yes its "because".

I have tried this below code and it's working fine for me.

osql -S GH-85HKG32\SQL2012 -E  -i "C:\path\Test.sql" -o "C:\path\defragIndexesLog.txt"

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900