1. First install Outlook 2000 on the server.
2. Login as the user that you SQL service starts as (SQLService in our case).
3. Run Outlook making sure you choose the Corporate or Workgroup option.
4. Then Select the Pop3 account type.
5. Setup the pop3 account with some valid credentials (in our case we’re only sending so in doesn’t matter if you use your own just make sure you tick the “leave messages on server” check box)
6. Ok the account setup and you should see your unread emails flood in, test the sending with a test email to someone you like.
7. Exit outlook and fire up enterprise manager and bring up your SQL Servers properties.
8. On the “Server Settings” tab there in an “SQL Mail” section, hit the “Change” button and select the MAPI profile you just created in Outlook ( there should only be the one in there).
9. Now open up the properties of your “SQL Server Agent” and check that the “Mail session” “Mail Profile” is set to the same MAPI profile you just set in the SQL Server properties and hit the test button to make sure it can send.

After the Agent service restarts itself you’re right to setup email notifications for Alerts and Agent Jobs.

Here’s a handy little query I came up with to list your scheduled jobs, what time they’re going to run next and their average duration on your MSSQL box. I’ve been chasing performance on my sever for months, tweaking things here and there, I found that windows was having massive page fault spikes every 5 minutes. Further investigation indicated that multiple (up to 10) scheduled tasks within SQL Server were fireing at the same time.

I used the query below to randomise the schedules (adding a random number of seconds to the start time) spreading the load more evenly over time.

A small gain but a nice one to finally nut out.

SELECT
    j.name, 

    endTime = CONVERT
    (
        DATETIME,
        RTRIM(next_run_date)
    )
    +
    (
        next_run_time * 9
        + next_run_time % 10000 * 6
        + next_run_time % 100 * 10
    ) / 216e4,

   (
        select avg(run_duration)
        from sysjobhistory jh
        where j.job_id = jh.job_id and run_duration is not null
   )
FROM
    msdb..sysjobschedules j
WHERE
	next_run_date <> 0
	and (name <> 'Schedule_1' and name <> 'Schedule 1')

order by
CONVERT
    (
        DATETIME,
        RTRIM(next_run_date)
    )
    +
    (
        next_run_time * 9
        + next_run_time % 10000 * 6
        + next_run_time % 100 * 10
    ) / 216e4

This is one that has come in hand a few times, if you’ve accidentaly filled up you data drive on your SQL server and don’t have the space left to clear out the log file and reclaim the space just run the following command then shrink the DB.

BACKUP LOG "MyDatabaseName" WITH TRUNCATE_ONLY