How to purge Microsoft SQL email Items

How to purge Microsoft SQL email Items


There are times that housecleaning older mail in your database needs to be done.  A simple scheduled task can be done every month to purge what is no longer necessary.  SQL Server stores all mails and attachments in msdb database. To avoid unnecessary growth of msdb database you should remove these mail history unless it is required for auditing or other purposes.

As always make sure you have your backups done prior to running any sort of removal in the event you did something incorrectly.  By purging you are reducing the amount of wasted space in the msdb database that could be used for other things activities.

Simply running the following statement using T-SQL you can check the amount of database mails that have been processed from the catalog msdb.dbo.sysmail_allitems.   There are 3 additional  views which shows mails in their different statuses:  sysmail_faileditemssysmail_unsentitems and sysmail_sentitems.


SELECT  COUNT(*)  FROM  msdb.dbo.sysmail_allitems

You should get back the count of how many mail items are stored in the msdb.

How to delete mail

To delete mail items you can use system stored procedure sysmail_delete_mailitems_sp, it has below syntax:

sysmail_delete_mailitems_sp [@sent_before] [@sent_status]

You can delete mail using either of the parameters, @sent_before deletes all mail that were sent before specified date, and @sent_status deletes all mails with specified status.

For example, to delete all mails which are sent and are older than current month we can use:

EXEC  msdb.dbo.sysmail_delete_mailitems_sp

@sent_before = ‘2017-05-10 00:00:00’,

@sent_status = ‘sent’

You can modify the @sent_before to do the following:

DECLARE @PurgeDate datetime = dateadd(dd,-30,getdate());  

@PurgeDate will be the date from 30 days or older which is a good idea to remove that or you can change it to
-10 for anything older than 10 days.

So the statement would be

EXEC  msdb.dbo.sysmail_delete_mailitems_sp @sent_before=@PurgeDate;

You may choose to only delete mail items that have been successfully sent.  If that is the case then you can run the following code to delete your database mail based on the sent­_status of “sent”.

If you are not periodically deleting your mail then you might want to consider cleaning up old database mail that no longer has value.  Determine what retention period you should use for your database mail.  With your established retention period then just set up a SQL Agent job to purge your database mail based on your retention period.

Ebooks and video at HUGE discounts at Packt Publishing

Packt Pub

Ebooks and video at HUGE discounts at Packt Publishing

Packt Publishing publishes technical manuals for all software developers are having a huge $5.00 sale for ebooks and videos.

Developer skills platform that supports software professionals in learning the skills they need to stay relevant in their field. From web development to data science, Packt delivers practical content on what’s important in the tech world, helping developers solve today’s – and tomorrow’s challenges.

With every eBook and every video $5 there’s never been a better time to learn something new or dive deeper into what’s important to you.

Some of the current sale items are:

  • Mastering Swift 2
  • Getting Started with Lazarus IDE
  • Mastering Ubuntu Server
  • Ubuntu Server Cookbook
  • SQL Server 2016 Reporting Services Cookbook

The library is huge and software developers will find something well worth purchasing and can expect high quality from PackT.

Eurekalog debugs your deployed application.

Eurekalog debugs your deployed application.



Eurekalog has consistently proven that they assist developers in debugging deployed applications.  No kidding, Eurekalog will send back a detailed report to the developer informing them exactly where the bug occurred and what exactly happened.  Baron Software has implemented in all of their applications the Eurekalog method and recommend to all Delphi developers to implement it as well.

Eurekalog Features

Features that should convince you to purchase the Eurekalog applcation:

  • Detailed bug report about each exception, leak or hang;
  • Bug report includes call stack with unit names, class names, routine names, and line numbers;
  • Extensive run-time and environment information is logged into bug report;
  • RAW dump and disassembly information;
  • Easy integration, no need to write code;
  • No additional files needed (no DLLs, no .map files, no .tds files);
  • Packing and encryption of all information;
  • No performance loss (unless exception occurs);
  • Full unicode support;
  • Win32 and Win64 support;
  • VCL, CLX, FMX (FireMonkey) support;
  • Supports any application kind: GUI, CGI, WinCGI, ISAPI, IntraWeb, COM, Multi-Thread, etc.;
  • Full support for .exe packers and protectors;
  • Support for modern cutting-edge features (nested exceptiond, Wait Chain Traversal, etc.);
  • Easy and powerfull customization;
  • Many helper tools;
  • Sending bug report to developers (e-mail, HTTP, FTP, bug trackers);
  • SSL/TLS support for all send methods;

New Partnership with Gnostice

The Eurekalog subscription model is recommended to allow the Delphi developer to keep up with the latest updates .  Eurekalog is announcing a new partnership with Gnostice, the makers of XtremeDocumentStudio, a document-processing and PDF library compatible with Delphi and other languages. Eurekalog will begin offering a 25% discount coupon on XtremeDocumentStudio to any registered EurekaLog customer.

Gnostice XtremeDocumentStudio Delphi [25% Off]
Gnostice XtremeDocumentStudio Delphi is a suite of multi-format document processing components for VCL and FMX. You can View, Print, Convert PDF, DOCX, DOC, RTF, BMP, JPEG, PNG, WMF, EMF and images, OCR images to searchable PDF, and much more.

Call Eurekalog today and get the Gnostice tool as well.  This is a  win-win for all Delphi developers.

Baron Software Supports IDERA DB PowerStudio

Idera Powerstudio

Baron Software Supports IDERA’s DB PowerStudio

Baron Software is a longtime user of IDERA’s DB PowerStudio which assists us in dealing with our clients in maintaining thousands of mission-critical multi-platform databases that are used for data warehouses along with highly robust data driven applications,” said Richard Baroniunas, software developer and DBA at Baron Software. “It is an utmost priority to use the absolutely best monitoring and troubleshooting software for our clients. All of Baron Software’s clients deserve the best software tools on the market and that is the reason IDERA’s DB PowerStudio suite is always used. The newest release of DB PowerStudio will create the client satisfaction that is our main objective.

Read the Businesswire article

Read the full article at BusinessWire.  Idera DB Powerstudio for various SQL servers give you the utmost ability to Develop SQL Code More Efficiently, Automate SQL Tuning and Profiling, Automate and Manage Complex Database Schema Changes and Perform Multi-platform Database Administration.

What does DB Powerstudio do for me ?

DB PowerStudio combines four innovative solutions to help build and maintain mission-critical database applications, streamline the database change management process, and quickly pinpoint and fix performance bottlenecks.

Get the following packages:

  • DBArtisan
  • DB Change Manager
  • DB Optimizer
  • Rapid SQL

DB PowerStudio is available in two editions: The DBA edition includes all four of the products listed above. The Developer edition includes Rapid SQL, DB Optimizer and DB Change Manager.

Supported Database Platforms:

  • Oracle 9i, 10g*, 11*, 12c*
  • SAP Sybase ASE 12.x – 15.x
  • IBM DB2 LUW 9-10.x and 11.x*
  • Microsoft SQL Server 2008, 2012*, 2014*, 2016*
  • InterBase 2007, 2009, XE, XE3, and XE7
  • Firebird 1.5, 2.0

Get the full support package that gives you the overall protection for any database you provide support for.

Baron Software has used the package for some time, we learn new ways of using the package and all of this works off of our notebooks when allowed to connect to the server.  Idera purchased the product from Embarcadero and is fully supportive for the future.

Baron Software

Saving SQL Column Headers Results

Baron Software

Saving SQL Column Headers Results

Sometimes you can run into problems that tend to take up time and could have been solved in a minute.  One of these strange problems is when you attempt to save a Microsoft SQL query result grid to a CSV file.

Using Microsoft SQL Server Management Studio (SSMS)

Now most of DBA or Developers tend to use the Microsoft SQL Server Management Studio (SSMS) to do the necessary general SQL work.  As you expected when you execute a query the results grid appears displaying the results.  I as countless others tend to copy the information and save it in an excel spreadsheet to do further analysis.  But there are times you may need to save the information into a CSV file for the purpose of processing the information later or with a different application.

If you use the option to save out to a CSV file you will notice that the column headers are missing and you are pretty much baffled.

A simple click to solve the issue

It is not you but apparently Microsoft decided not to default a simple setting that saves the column headers in your CSV file.  What you need to simply do is click on Tools – Options which will open the Options Dialog box.

In this Dialog box you will click on Query Results – Results to Grid.  You will see the “Include column headers when copying or saving the results”, click to enable it.  You should see the similar as the image below.  Click OK to close the dialog and will have to restart the Server Management Studio application for the settings to take hold.

MS SQL Server

This is something we all take for granted and by diving into the vast amount of options you can solve it.  So go ahead and take a look to see if you have it set or need to.

SQL Stored Procedures to get folder and files

 Baron Software

SQL Stored Procedures to get folder and files

There may be a time when you will need to get the file name contents from a folder for the purpose of using the information in your stored procedure.  The one undocumented stored procedure on Microsoft SQL Server 2016 is master.sys.xp_dirtree that you can use.

In your stored procedure, we will go ahead and create a temporary table that will store the files with their correct extension.  The first thing we want to do is check whether the temporary table was left over and remove it.  Usually when you close out a session temporary tables will be destroyed but it is fine to check and do house cleaning in  your stored procedure.

IF OBJECT_ID(‘tempdb..#FilesListing’) IS NOT NULL
DROP TABLE #FilesListing;

The next step is to create the temporary table again call it #FilesListing or whatever you wish to call it.  This table will have an identity field which will be seeded with 1 and incremented by 1.  The file name will be stored in FullFileName, you will see in a short period of time depth and isfile are used to store the information provided by the undocumented extended stored procedure; master.sys.xp_dirtree.

CREATE TABLE #FilesListing (
id int IDENTITY(1,1),
FullFileName nvarchar(512),
depth int,
isfile bit);

Preparing master.sys.xp_dirtree

master.sys.xp_dirtree has three parameters:

  1. directory – This is the directory you pass when you call the stored procedure; like our example of C:\TEMP.
  2. depth  – This tells the stored procedure how many subfolder levels to display.  The default of 0 will display all subfolders.
  3. file – This will either display files as well as each folder.  The default of 0 will not display any files.

INSERT #FilesListing (FullFileName, depth, isfile )
EXEC master.sys.xp_dirtree ‘C:\TEMP’, 1, 1;

You can play with changing the depth and file settings from 0 to 1 to see the different results you can gather.  Finally by placing a select statement from the temporary table #FilesListing will provide the results.

Select * from #FilesListing;

Wrap  up

Depth is 1 and file is 1 will show only the directory names in the results


Depth is 0 and file is 1 will show the directory and the file names in the results.


As you can see you can do a lot, one idea are nightly files deposited in a folder for the SQL Agent to kick off a stored procedure which needs to process them.  The performance is quick but you need to be extremely careful where you are pulling files from since listing the entries into the table may take a few minutes.

Getting performance statistics for cached stored procedures

Baron Software

Getting performance statistics for cached stored procedures

It is always extremely important to maintain your SQL server to the utmost efficiency.    DBAs review the database structure and whether certain pieces such as stored procedures, triggers, etc. can be tuned even further.  One of the areas that will be discussed are the stored procedure which can be used during any time as well as day.

 Dynamic Management Views and Function (DMVs and DMFs) to identify resources used by stored procedures

DMVs and DMFs were introduced in SQL Server 2005 and are able to obtain figures only for those stored procedures that have execution plans in the procedure cache.  The SQL Server engine starts gathering information about the performance of a stored procedure the first time that  the plan is placed in the procedure cache.  As long as the stored procedure’s execution plan stays in the cache, SQL Server keeps updating the performance figures every time the stored procedure is executed. If the execution plan for a stored procedure is dropped from the procedure cache then the metrics for the stored procedure are also removed.  Therefore you will only be able to obtain metrics for those stored procedures that have cached plans, and those metrics will contain a summarization of the amount of resources used since the stored procedure was compiled, and its’ execution plan was placed in the procedure cache.  One thing to keep in mind is whether you are using the SP_RECOMPILE procedure for any or all stored procedures, that will remove the statistical information.


This DMV was introduced with SQL Server 2008.   By using this DMV, you can returns metrics for stored procedures, but it does not directly identify the stored procedure by name.  Instead, the DMV only identifies the object_id, and a database_id for each stored procedure:  Therefore to identify the actual stored procedure name, that the performance figures belong to, you should either join the output of this DMV with one of the system views within the appropriate database, or use a few metadata functions.

      OBJECT_NAME(object_id, database_id) ‘SP name’,  
     fld.total_elapsed_time/fld.execution_count AS [avg_elapsed_time], 
     fld.last_elapsed_time, fld.execution_count 
  FROM sys.dm_exec_procedure_stats AS fld
  where database_id <> 32767
  ORDER BY [total_worker_time] DESC;

This SQL example demonstrates the information pertaining to the stored procedures.  This allows you to review what if possible is performing poorly or long.

  • cached_time: when the procedure was cached or last started.
  • last_execution_time: when the procedure was last executed.
  • execution_count: this tells you the amount of times the procedure was executed

I would suggest to not to display the resource database which always has a ID of 32767 and you can view that in the where clause.

The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata. (from BOL).  In SQL Server the maximum databases per instances that can be created are 32,767. This last number has been reserved by Resource Database itself.


This DMV can be used to obtain statement level metrics.  These metrics are only available if the statement comes from a cached plan.  By using this DMV and summarizing the statement metrics up to the plan level, you are able to identify metrics for stored procedures.

       DB_NAME(dbid) AS DatabaseName,
      OBJECT_SCHEMA_NAME(objectid,dbid) AS [SCHEMA_NAME],  
      OBJECT_NAME(objectid,dbid)AS [Process_Name],
      MAX(qs.creation_time) AS 'cache_time',
      MAX(last_execution_time) AS 'last_execution_time',
      MAX(usecounts) AS [execution_count],
      SUM(total_worker_time) / SUM(usecounts) AS AVG_CPU,
      SUM(total_elapsed_time) / SUM(usecounts) AS AVG_ELAPSED,
      SUM(total_logical_reads) / SUM(usecounts) AS AVG_LOGICAL_READS,
      SUM(total_logical_writes) / SUM(usecounts) AS AVG_LOGICAL_WRITES,
      SUM(total_physical_reads) / SUM(usecounts)AS AVG_PHYSICAL_READS      
  FROM sys.dm_exec_query_stats qs  
   join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle 
   CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) 
  WHERE objtype = 'Proc' AND text NOT LIKE '%CREATE FUNC%' and dbid <> 32767
  GROUP BY execution_count, cp.plan_handle, DBID, objectid 
  ORDER BY execution_count DESC

Final Wrap up

As demonstrated these queries will provide you with a simple method to view the stored procedures performances.  You can tune the stored procedures that need it by watching the various reporting fields displayed.    You have the ability of storing this information or exporting it to Excel for maintaining a log over a time period.









Expedite SQL with Idera Rapid SQL 2016

Idera rapid sql 2016

Expedite SQL Development with Rapid SQL 2016

• Write code across different database platforms – Produce high-performing SQL code across DBMS platforms (Oracle, SQL Server, DB2, Sybase and PostgreSQL) from one user interface.
• Develop SQL code quickly and efficiently – The SQL IDE is a clean, slick development environment, ideal for creating and modifying SQL code geared to the needs of application developers. Real-time quick fixes flag and correct inefficient SQL automatically, saving you time.
• Simplify debugging SQL code – Simplify the task of hunting down and eliminating coding errors. SQL Debugger lets you debug SQL Server, Oracle, Sybase or DB2 stored procedures as well as Oracle functions, improving productivity.

Click to view the data sheet

Develop High Performing Code Across DBMSs

Create high-performing SQL code on all major DBMSs (Oracle, SQL Server, DB2 and Sybase) from a single common interface. Reduce training requirements and streamline collaboration among teams across the organization.

Easily Build Complex Statements

Save time and start developing SQL code immediately with Visual Query Building tools. Rapid SQL gives you the ability to construct complex SQL statements with point-and-click ease using the Visual Query Builder.

Quickly Construct and Execute SQL Code

The SQL IDE is a development environment, ideal for creating and modifying SQL code geared to the needs of application developers. Real-time quick fixes flag and correct inefficient SQL au­tomatically. The IDE is also equipped with SQL code assist, real-time SQL syntax validation, and project level SQL file cataloging and search features.

Simplify Debugging

SQL Debugger simplifies the task of finding coding errors and lets you debug SQL Server, Oracle, Sybase or DB2 stored procedures as well as Oracle functions. The SQL Debugger features basic execution, line-by-line execution, breakpoint support, and other common debugging features.

Effectively Collaborate

Collaborate with your team with version control, reverse-engineering, and source code repository capabilities. Rapid SQL incorporates version control functions and build management facilities to help you manage and build projects. It provides seamless integration with most commercial version control packages and support for all operations including get, check-out, check-in, history, and differences.


After using Rapid SQL, productivity time has improved while design / development time has been reduced. The option to use various databases and allows a developer to quickly get the database designed, tested and released much faster.

Microsoft SQL Server 2016 is coming to Linux

Microsoft SQL Server 2016 is coming to Linux will provide the ability for users to have the features and additional security.

Microsoft SQL Server 2016Click the image for more information about SQL Server

Microsoft will be releasing a version of their SQL Server 2016 for Linux which is a major step in the world domination plan.  With about 35 percent of the web servers in the world using Linux as their operating system this will open new revenue streams for Microsoft.

As you can see from the grid below the percentages of SQL Servers within companies.  The area where MySQL rules is on the web server which puts both at about 35 percent each on market share.  If Microsoft begins the move towards Linux the development tools will be released shortly.  What this means for a small company using a web server, they now have an option to use the Linux OS but with a robust SQL Server that also contains high end security and has better performance with tasks while MySQL is a low end database manager.

One of the most important things to secure a database is to use a mature data abstraction layer that exists as close to the data as possible.  MySQL is disqualified because it runs best when you minimize your abstraction layers to all but the most simple tasks.  SQL Server abstraction layers are just fine to write.  It is by far more mature than MySQL, but remember that MySQL is cheaper for web hosting firms and always include it with their packages.

If Microsoft does increase their market share within a web hosting firm that will provide additional revenue as well as allowing mobile devices designed with Visual Studio to gain access almost anywhere.  The design and development of database software will be better off due to the fact that a complete database can be migrated from a Windows Platform to a Linux Platform, now that would be pretty awesome.


Embarcadero and Baron Software Powerstudio case study which monitors SQL Performance

Embarcadero and Baron Software has issued a case study that demonstrates the benefits of using Microsoft SQL Powerstudio product line.  Powerstudio is a robust package that monitors SQL performance and can provide assistance for clients to update or create tables, stored procedures, etc. for various SQL servers some being Oracle, Microsoft SQL and many more.  The detailed report can be view by clicking here.


Embarcadero Logo
Case Study