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.

New Build 4 Now Available for Version 2.26 of ElevateDB from Elevate Software

ElevateDB

Elevate Software is  pleased to announce that build 4 for version 2.26 of ElevateDB is now available. The software release provides the following:

  1. Altering a Table in a Database with Dependent Views Can Result in a Table Checksum Mismatch
  2. COMPARE DATABASE Generating Incorrect Statement Terminators for ALTER Statements

If you own the subscription model or are looking for a database manager that works with Delphi this is the package to go with.

ElevateDB is licensed per-developer, and includes royalty-free distribution. License management isn’t required at all, and the money that you used to pay to the database engine vendor is now your profit to keep.

What does ElevateDB do ?

You don’t need to use a feature-crippled “free” product in order to avoid licensing fees anymore. With Elevate Software, you are backed by a company that has over a decade of experience in highly-distributed and pre-packaged database application software.

ElevateDB is designed to be included in a pre-packaged database application and can be installed very quickly and easily. The ElevateDB Server is a single EXE (~2MB), only requires a single INI text file for configuration, and can be run as an application or Windows service. Source code to the ElevateDB Server is included with every purchase, so it can be customized and re-branded, if necessary. The ElevateDB client-side code can be compiled directly into Delphi, C++Builder, and Lazarus applications, and consists of a single assembly (~2MB) for .Net applications. ElevateDB client code can also transparently switch between local, single-user and client/server, multi-user usage.

ElevateDB automatically creates all necessary files when they are needed. For example, you can distribute a single database catalog file with your application, and ElevateDB will automatically create the the table files as they are opened by sessions. In addition, ElevateDB provides reverse-engineering facilities that allow you to easily create creation and upgrade SQL scripts that can be run during installation to transparently create any necessary system or database objects.

Once installed, ElevateDB is not fragile and cannot be made inoperable by another application’s installation process. Many database engines are extremely sensitive to configuration changes and require constant attention by an administrator.

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.

Windows Developer Day Creators Update is coming up

Microsoft

On February 8, 2017 you are invited to see what’s new for developers in the Windows 10 Creators Update, what it means for your apps and games, and more about Microsoft’s latest developer products. Whether you’re building for the web or UWP, the latest consumer app or line-of-business tool, there’s something in it for you.

To register please Click here

The Agenda for the session is listed below

Keynote   09:00 – 10:00 AM

Kevin Gallo takes us on a tour of what’s new in Windows 10 Creators Update. He’ll apply a developer’s lens as he shows off new Windows capabilities and tooling and will share some news along the way.

Improving user engagement with Windows and Cortana Skills   10:15 – 10:45 AM

Engaging with your users has always been a challenge, especially now that each user wants their app experience to flow seamlessly between their many devices. In this session, we’ll explore how you can meet this need regardless of platform and form factor—whether the devices use small screens, big screens, or no screens at all. We’ll also discuss improvements with the Windows Desktop Bridge.

Building personal and productive apps with Composition and XAML updates   10:50 – 11:20 AM

First impressions are important, so one of our goals with the Windows 10 Creators Update is to make it easier to deliver beautiful, engaging Windows app experiences. In this session, we’ll discuss how the Creators Update will help simplify building apps that feel more natural (using Ink and Dial) and more beautiful (using Composition advances). We’ll not only walk through the big new improvements, but also highlight some of the smaller tweaks that can be just as important, like menus, dialogs, scrollbars and keyboards.

Using the Windows Store to enhance your app   11:25 – 11:55 AM

The Windows Store and Windows Dev Center are constantly improving to meet the evolving needs of developers and the market. In this session, we’ll explore new Windows Store capabilities that make it easier for customers to find and acquire your apps and games, and Dev Center improvements that optimize your app submissions and management workflows.

Developer tools and updates 12:00 – 12:30 PM

Windows is built for developers, and the Windows 10 Creators Update is no exception. In this session, we’ll focus on how Visual Studio 2017 and the Windows 10 Creators Update make you more productive. We’ll discuss updates to the Windows Device Portal, improvements to Windows Developer Mode, Bash and Console, and many other cool advances.

Speaker panel / Q&A  12:35 – 1:05 PM

After a few hours of telling you what’s new, Kevin Gallo and the Windows engineering team will take the stage and answer your questions. The team loves hearing straight from our developers, and talking straight with our developers. Join us and submit your questions live.

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

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.

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.

sys.dm_exec_procedure_stats

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.

SELECT TOP 10
       fld.object_id,
      fld.database_id,
      OBJECT_NAME(object_id, database_id) ‘SP name’,  
     fld.cached_time,
     fld.last_execution_time, 
     fld.total_elapsed_time, 
     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.

sys.dm_exec_query_stats

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.

SELECT TOP 10 
       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.

 

 

 

 

 

 

 

 

halt_and_catch_fire_intertitle

Season 3 of Halt and Catch Fire

The season finale of Halt and catch fire was shown last week for a solid 2 hours.  The entire show has gone from the personal computer start to about the 1990s with the advent of Windows 3.

The series has had it’s moments with characters that tend to be flaky but came up with inventive ideas that brought social up with ease on social interactions (Mutiny), personal computers that were built to take on the business world (Giant) and finally what IPOs can do to a person.

Flawed Characters that know how to party!

The characters have grown but in reality some of them would have been in prison for the shady dealings such as “Joe”, a person who destroyed the personal computer that was dealing a blow to IBM in season one, gave up on a boyfriend, destroyed a company in season two as well as a marriage and finally taking software anti-virus code and giving it to a person “Ryan” who was mentally unstable.

“Cameron” is a flake, whose ideas of C++ object oriented is crap but she can make a company using a Commodore Vic-20 and wrapping up season 3 by having a one night stand with “Joe”.    Somehow she creates Mutiny, loves chaos and doesn’t get a descent haircut after 3 years.

“Gordon” is a engineering genius but has a disease that may kill him and most likely will occur in the final season.  He also decides to go back with “Joe” to take over the internet.

Finally “Donna” who worked extremely hard to get the IPO and make millions but is totally dissatisfied with what her life has turned into, wishing to get all 4 characters into one room to rekindle the past.

Season four is the series finale

It has been a pretty good series but the time to close it out comes next year with 10 episodes.   The producers have taken 30 years of the rising personal computer along with the internet.  They have put it into 30 or more episodes making viewers see a distorted past.  The recommendation is to read the biography of Steve Jobs or Bill Gates.  That will give you a real prospective of what transpired during those years.

 

 

Idera and Embarcadero separate target businesses

 

Idera and Embarcadero have officially separated their businesses for software development Rad Studio that contains Delphi / C++ Builder and the Idera database tools.  This is a fantastic step in going forward and bringing both web sites with a fresh look on the future.

Embarcadero will continue to enhance the Rad Studio product that comprises of Delphi / C++ Builder plus the various tool kits that would enhance the product line. Seattle is the latest version that provides the software developer to build applications for Windows, databases, mobile devices and much more.

embarcadero-idera-separate

The Ultimate Application Development Platform for Windows 10, Mac, Mobile and IoT.

  • Rapid Development
  • Rich UI/UX
  • Measure User Activity
  • Build for Enterprise
  • Internet of Things
  • Embed Data Everywhere

Idera-embarcadero

Idera will be selling the various database tools on their own web site along with ER Studio making the company and product line stronger then before.  Listed below are the features for each product in the Powerstudio.

DBArtisan

Perform Multi-platform Database Administration

  • Manage multiple platforms (Oracle, SQL Server, DB2, Sybase) from one UI
  • Proactively manage space, data and performance with built-in analytics
  • Easily find and fix performance issues using intelligent diagnostics
  • Move schema and table data across different platforms with ease
  • Protect data security with view, grant and revoke of permissions

DB Change Manager

Automate and Manage Complex Database Schema Changes

  • Quickly roll out and reconcile database changes
  • Reveal, track, and report on database changes
  • Comply with database audit and reporting requirements
  • Protect data privacy within the database environment
  • Track changes from multiple major database platforms

DB Optimizer

Automate SQL Tuning and Profiling

  • Streamline tuning of SQL code on major DBMSs from one interface
  • Tune SQL like a pro with automated performance optimization suggestions
  • Tackle complex SQL queries with visual SQL tuning diagrams
  • Pinpoint problem SQL with database profiling of wait-time analysis
  • Load test alternative SQL queries in simulated production environment

Rapid SQL

Develop SQL Code More Efficiently

  • Create high-performing SQL code on major DBMSs from one interface
  • Easily build complex SQL statements with visual query builder
  • Quickly construct, analyze and execute SQL code with code analyst
  • Simplify debugging SQL code, functions, and stored procedures
  • Collaborate effectively across development teams with version control

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.

Percentage