Want to create a batch file to execute SQL script

Question

2.00/5 (1 vote)

See more:

, +

Hi..

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.

SQL

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

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

comred

Updated 24-Jan-17 0:26am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mike Meinz · Accepted Answer · 2013-02-28T01:54:00

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
GO
DBCC shrinkdatabase (dbName)
GO

3. Use Windows Task Scheduler to schedule C:\BAT\ShrinkDB.bat

Posted 28-Feb-13 1:54am

Mike Meinz

Updated 30-Jan-14 3:03am

v5

Comments

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
USE [MCX]
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.

Example:
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"