ihost
asked on
TSQL Syntax To Automate Database Task
Hi,
We have several MSSQL2005 and 2008 servers with hundreds of small databases. Most databases contain a table called "EventLog". In some cases these tables grow to millions of records and cause bloat or cause databases to reach maximum file size constraint limit.
I'm looking for a TSQL scrip that can be executed on the MSSQL server using scheduled task that could loop through each hosted database and run a TRUNCATE command on the "EventLog" table.
Currently we use external process to perform this task, however I would like to simplify this so it can be included with the daily backup routine.
We have several MSSQL2005 and 2008 servers with hundreds of small databases. Most databases contain a table called "EventLog". In some cases these tables grow to millions of records and cause bloat or cause databases to reach maximum file size constraint limit.
I'm looking for a TSQL scrip that can be executed on the MSSQL server using scheduled task that could loop through each hosted database and run a TRUNCATE command on the "EventLog" table.
Currently we use external process to perform this task, however I would like to simplify this so it can be included with the daily backup routine.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
You can change it to TRUNCATE TABLE, but if you want to keep history for a certain amount of time, this will delete records older than five days.