How to purge Microsoft SQL email Items

Purge

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.

Creating a Disaster Recovery Plan for your database (SQL)

Idera

 

Idera recently had a fantastic webinar that went over the creation of a disaster recovery plan concerning your databases.  This should be a high critical piece of work usually done by DBAs within a company.  Let’s go over a simple set of plans that depending on the type of company you are dealing with, can be done either large or small.

  1. Backups.  All backups should be done with incremental or full backups of your database.  There should be a protected network folder that contains the incremental in one and the full backups (usually done once a week) in a separate folder.  An incremental is a backup that contains only a snapshot of transactions that are done during a day while a full backup contains the entire database.
  2. Data model.  A data model provides a visual as well as a detailed layout of each database / table that provides a method for someone to read and understand how the data is stored.  It can be noted as a roadmap that contains the relationships between the tables within the database as well as the keys / indexes that work with the tables.
  3. User security profiles.  Each DBA should have a listing of the usernames / passwords in a safe area.  In the event a fellow member leaves but they had the password on a specific database you now possibly have the way in.  There are numerous companies that tend to not have that and during the exit interviews it becomes lost.
  4. Contact listing of people.  The contact list contains the individuals that have to be notified if a database is corrupted and has to be repaired or restored from the backups.  You can set the priority of each person as well.
  5. Checklist. By having a checklist of what to do in the event of a DRP it will keep you focus on getting everything back up and running.  One missed step could push you back thus delaying the recovery.
  6. Backup life cycles.  Each company is different and whether the backups are taken off site for now long and when are they removed / erased.  You have to plan this out and the usually lifecycle is between 3 months to a year.

These are only minor steps but at least they do enforce a DRP that any DBA needs to have in place prior to the disaster happening.

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

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.

Baron Software has released the latest version for Windows the OLE Error Helper which is a simple free application that allows developers to do a quick search on OLE Errors and the definition.  Click here to download the latest version.  Developed using Rad Studio Delphi and SQLite stores the information.  System requirements is any Windows 7 or higher and the versions are either 32 bit or 64 bit.

OLE_Error_Image1

Search by simple words to match what you are looking for.

OLE_Error_Image2

You can view or print a report based on your search criteria or dump out the entire database to read at your leisure.

OLE_Error_Image3

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

Embarcadero has released DB PowerStudio 2016 during the DataU Webinar 2 day conference.   Product installation is simple and quick.  The new features that provide DBAs the ability of checking SQL Server performance by using the add-on Performance IQ.

Only Rapid SQL and DBArtisan 2016 have been released, not sure what the status is on DB Change Manager and DB Optimizer since they were not part of this release.    The packages are getting more robust but the add-on cost is big for smaller firms.

 

  • Key Enhancements for DBArtisan and Rapid SQL 2016
    • Deep performance integrations into DBArtisan & Rapid SQL
    • Significant SQL IDE enhancements to detect poor SQL performance
      • Cardinality problems
      • Data skew issues
      • Substandard SQL syntax
      • Inefficient index usage & index suggestions
      • Full alerting package (+blackouts & non monitoring)
  • Performance IQ (Available as a paid add-on for DBArtisan 2016 & Rapid SQL 2106)
    • Embedded Performance Metrics & Analytics
      • Embedded performance metrics & analytics across DBArtisan to provide deeper database insights
      • Understand the top SQL statements impacting IO, table and index growth history
    • SQL Analytics
      • SQL editor shows you how SQL has performed over time in its various revisions
      • Why performance variances are occurring
      • New indexes are suggested should SQL performance benefit from such additions
    • 24/7 Wait-based Monitoring & Alerting
      • 24/7 wait-based observations to help you proactively manage your database environment
      • Time travel to allow you to rapidly travel back in time to dissect issues as they occurred
      • Baselines to more fully understand deviations from the normal trends
      • User configurable alerting so there are no surprises
    • DB Change Manager and DB Optimizer 2016 continues to be supported and sustain currency

Software developers have always fought to keep up with the skills necessary to make themselves marketable.  A new survey has been created by IDC / Application Developers Alliance that provides the insight on the tools used as well as organizations.   As always a survey is conducted by taking a sample from people of where they are working.  So take it with a grain of salt because certain software tools are used in particular areas of the country.

The survey, conducted by research firm IDC and commissioned by the Application Developers Alliance, drew responses from 850 developers—not a massive cross-section of the world developer population, which easily numbers in the millions, but certainly enough to bring certain trends to light.

Here are some highlights from the study:

  • Some 68 percent of surveyed developers had 5 or more years of experience.
  • Around 57 percent said they were interested in “staying current” about development technology.
  • A full 75 percent used open-source software.
  • Some 83 percent considered themselves “self-reliant,” using online forums and search engines to get job-related help.

The most popular databases and platforms include MySQL (64 percent of respondents), Microsoft SQL Server (49 percent), SQlite (39 percent), Oracle DB (37 percent), and ProgresSQL (23 percent).

 

A lot of developers who read the survey will likely see themselves reflected in the results.

SQLite Essentials

 

I recently had the pleasure of reading Android SQLite Essentials by Vikash Kumar Karn / Sunny Kumar Aditya. The book details about using SQLite with your android and some tips about accessing the database from multiple applets on your android device. The book is rather small about a little over 90 pages which I do not understand how Amazon comes up with 127 pages unless they are counting certain sections twice.  The chapters are simple concepts that anyone can understand and within an hour you can have a pretty good idea on what to expect as well as how to use SQLite.

The chapters are listed below, you can complete the book within an hour and get developing.   The source code is available from their web site and as always the publisher PACKT does a nice job putting the book together.

Chapter 1 – Enter SQLite provides the reader the ability of knowing what SQLite can do for your Android device as well as the simple steps on maintaining and overall usage.
Chapter 2 – Connecting the dots provides the reader with the simple mechanics of allowing the developer to write the necessary routines for the client to maintain their database. Simple Edit / Delete / Update / Insert instructions are discussed.
Chapter 3 – Sharing is caring discusses the important concept of content provider so that other applets on the device can retrieve the centralized data.
Chapter 4 – Thread carefully wraps up the book talking about loaders and something we tend to forget, security.

Overall the cost is low about $20 on Amazon, it is a simple quick read that provides an understanding about SQLite but is not in depth,  if you are looking for that I would suggest Using SQLite by Jay A. Kreibich which does go over the syntax and design using the SQLite database for your applications.  Still I do recommend that you give the book a try.