Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Windows SQL-Server , +
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 28-Feb-13 0:54am
comred1.2K
Edited 28-Feb-13 0:57am
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 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 365
1 Sergey Alexandrovich Kryukov 329
2 CPallini 270
3 Afzaal Ahmad Zeeshan 204
4 DamithSL 194
0 OriginalGriff 5,515
1 DamithSL 4,451
2 Maciej Los 3,902
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,175


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