Author: Ian

Delete Database Mail Messages from the msdb Database in SQL Server (T-SQL)

Posted on by Ian

When you use Database Mail to send emails from SQL Server, email messages and their attachments are stored in the msdb database. You should periodically delete these messages to prevent the database from growing to large.

To delete these messages with T-SQL, use the sysmail_delete_mailitems_sp stored procedure.

You can delete mail messages based on their send request date (i.e. before a certain date) or based on their status. You can also delete all mail messages by using the current date as the request date.

Continue reading →

Create a “Last Modified” Column in SQL Server

Posted on by Ian

Some database tables include a “last modified” column, which stores the date and time that the row was last updated. Each time the row is updated, the date is updated to reflect the date and time of that update.

In SQL Server, you can use a trigger to perform this update.

A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server.

You can use the CREATE TRIGGER statement to create a trigger when using T-SQL. This statement can be used to create a DML, DDL, or logon trigger.

Continue reading →