Link to home
Start Free TrialLog in
Avatar of ihost
ihostFlag for United States of America

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.
ASKER CERTIFIED SOLUTION
Avatar of Surendra Nath
Surendra Nath
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You can also do something like this:
exec sp_MSforeachdb 'IF DB_ID(''?'') > 4 and ''?'' <> ''distribution''
begin
	IF EXISTS (SELECT 1 FROM [?].sys.tables WHERE name = ''EventLog'' ) 
	delete	from	?..EventLog
	where	EventDate < DateAdd(day, -5, GetDate())
end'

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.