Author: John

So just a quick one today. I was recently working on a SQL Server, running through some Database Mail setup and testing (see Microsoft Docs) with one of our applications.  I needed a way to see what e-mails were being sent out as well as what wasn’t.  The below queries will give you the info I was after, the first one shows any items that have run through DB Mail and their details for the last day (you can customise the WHERE statement to your needs.  You will want to run them against MSDB, do this by selecting it or issuing a USE MSDB statement.

SELECT p.name, i.send_request_date, i.sent_date, i.recipients, i.subject, i.body
FROM sysmail_mailitems AS i inner join sysmail_profile as p on p.profile_id = i.profile_id
WHERE sent_date > DATEADD(DAY, -1,GETDATE())

Bare in mind that I’m using Aliases to shorten the query a little (see this article).  Now this next one simply shows failed items as well as error responses if any from a mail server.

SELECT i.subject, i.recipients, i.copy_recipients, i.blind_copy_recipients, i.last_mod_date, l.description
FROM sysmail_failedi AS i LEFT OUTER JOIN sysmail_event_log AS l ON i.mailitem_id = l.mailitem_id
WHERE (i.last_mod_date > DATEADD(DAY, - 1, GETDATE()))

Hope that helps.

Working with one of my education customers I recently had to perform some maintenance on their SQL Database server as they were running low on disk space AND had a free unused virtual disk where we could throw their tempdb onto (it was meant to go here but they didn’t place it there during installation). So I had the task of moving it over.

First step is to get an as-is of where tempdb currently is and how many fragments there are; Open up SQL Management Studio and run the following query.

-- Lists all current tempdb files and their paths
SELECT name, physical_name AS CurrentLocation 
FROM sys.master_files 
WHERE database_id = DB_ID (N'tempdb');
GO

Now that we have a listing (similar to our screenshot above/left), we then need to build up a query to move the database files from one drive to another. It should look something similar to the below query, changing paths and adding/removing tempdb fragments as needed (simply add more alter database statements for each file node).

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\templog.ldf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp2, FILENAME = 'F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp3, FILENAME = 'F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf');
GO

In the above example we have specified F drive.  The other thing to note is that if you have SQL Server running through a service account, that will need NTFS modify permissions to the destination.  Once that is executed under the Master database context the files will then be re-created on the next SQL Server service restart.