Automate SQL Query File

serg2626 used Ask the Experts™
I have an SQL 2005 Server and have a query which truncates my log files that I run manually whenever they get large in size. I would like to be able to save that query to a .sql file and have task scheduler run it every month or week on its own. How can I configure it so that I can use task scheduler to point to the .sql file and automatically run the query at a given date and time? I'm not sure how to get it to authenticate using a specific username and password etc. Please provide step by step instructions on doing this.

Thank you!

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Do you want to truncate a table that have log entries or to truncate the physical log file of a database?
Also, do you want to make a backup of the log/table before truncating it?


Marco André
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
>> have a query which truncates my log files that I run manually whenever they get large in size

do you mean you are truncating log files often...
Then its not a recommended practice at all and would suggest you do the following:

Create separate Maintenance plans for each of the activities listed below:

1. Full Backup - say Daily
2. Transactional Log Backup - say every 15 mins or 30 mins
3. Index Rebuild and Reorganize tasks - weekly
4. Update Statistics task - Daily

taking Transactional Log backup as per task 2 would help keeping your Transactional Log file size in control.

What rrjegen17 suggested is a good practice. If disk space is a constraint for taking frequent log backups. And in case if you don not need point in time recovery. You can change recovery model to simple. But make sure you have full backups. I recommend this only if your database is not critical and if it is fine for you, if you can recovery last night backup.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial