Click here to Skip to main content
13,090,678 members (45,326 online)
Rate this:
 
Please Sign up or sign in to 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.

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
comred1.2K
Updated 24-Jan-17 0:26am
v2

1 solution

Rate this: bad
 
good
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
GO
DBCC shrinkdatabase (dbName)
GO


3. Use Windows Task Scheduler to schedule C:\BAT\ShrinkDB.bat
  Permalink  
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"

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 |
Web04 | 2.8.170813.1 | Last Updated 24 Jan 2017
Copyright © CodeProject, 1999-2017
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