run sql script with batch file

• lking-854368

  SSC Rookie

  Points: 31

  More actions

  August 19, 2008 at 1:38 pm

  #197203

  I have a sql script to shrink the transaction logs. I have a job set to run the script nightly after the databases are backed up. The job runs with no issues. Occasionally the logs get big enough to create disc space alarms and the script needs to be run manually, which is also not an issue if you are logged in. However, I would like to be able to have a batch file run the script automatically when the alarm is generated. The monitoring software can run the batch file. The problem is when the batch file calls the script, Management Studio then prompts for Windows or sql authentication. Is there any way to run the batch file without putting the credentials in plain text?

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  August 19, 2008 at 2:13 pm

  #860269

  If the monitoring software is running under a domain account you should be able to use a Trusted connection.

  A second note is that you should not be shrinking your log files. All this is doing is slowing your server down because it just needs to reclaim that space through autogrowth throughout the day. If your database is in Full or Bulk-Logged Recovery mode you should be taking regular log backups (hourly) which will free space in the logs so you do not grow as much. If you do not need to be able to restore to a point in time you should set your database to Simple recovery, which will cause the SQL Server to automatically free up space in the log files after a checkpoint operation.

  You should check out this article: http://technet.microsoft.com/en-us/magazine/cc671165.aspx

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question