With the release of SQL Server 2017 CU 6 today, you now have the ability to add the replication Distribution database to an Always On Availability Group.
The current setup steps and limitations can be found here.
AKAWN Montoring version 18.04.14 is now available for download
So what’s new?
- Default trace monitoring
- Alert if the Default trace is not running
- SQL Agent job unsuccessful alerting
- SQL Agent jobs which are long running alerting
- SQL Agent job duration tracking
- Addition of the following 2 gauges to the Environment Dashboard:
- Unsuccessful Jobs
- Long Running Jobs
Default trace monitoring
Although the default trace has been marked for removal in a future version of Microsoft SQL Server, it still contains useful information and is enabled by default when you install SQL Server.
The default trace information reported on in this release includes:
- Database add/deletes
- Database detaches
- Login issues
- Add/Remove of Users/Logins
- Server and Database role member changes
- Server start/stops
- Data and Log files Grow/Shrinks
- DBCC Shrinks occurring
- GDR events
A sample email for default trace findings is shown below: (remember you can always add exceptions for expected events, you can change the priority of checks, you can change the schedule of checks, you can disable checks and you can even specify if checks get emailed/paged)
Alert if the Default trace is not running
Also included in this release is the detection of the default trace not running. This event typically occurs when the drive hosting the default trace runs out of disk space.
You will not only be alerted when the trace is detected as stopped, you will also be alerted when the issue has been resolved.
SQL Agent job unsuccessful alerting
This handy check helps you to keep on top of job unsuccessful job outcomes and includes some handy information:
- both the job and step errors
- who, if anyone, got notified and how
- run duration and retries attempted
- job schedule information
This check has also been added to the Environment Dashboard for increased visibility.
SQL Agent jobs which are long running alerting
Another useful check is detecting long running jobs. The criteria for a long running job depends on if there is job history for the job.
- If there is job history and the job had succeeded in the past, you will be notified if the job has taken twice as long as its average run duration
- If there is NO job history or the job has never succeeded, you will be notified if the job is still running after 60 minutes
For increased visibility, long running jobs show on the Environment Dashboard along with step being run and the duration passed.
If you have alerting you will also be notified when a long running job is detected as well as once it has been resolved, which may save you having to log onto the server.
SQL Agent job duration tracking
One last new feature to mention is the job duration tracking. If you are wanting to know if jobs and/or individual steps are taking longer or getting quicker, this should help.
Currently the information is available from the view v_job_duration_tracking in the AKAWN repository database.
We’ll look to incorporate this into AKAWN Studio in the next release for easier reporting, but for now at least you know the information is available to you.
So you’ve been working on scripts in SSMS v17.x and suddenly it freezes and won’t let you interact with it at all.
No fear, when you close (kill) the SSMS process using Task Manager and reopen it you should be prompted to recover your files. The caveat being that you haven’t disabled Save AutoRecover in the Tools – Options menu item.
However, if you’d rather not take the risk of not being prompted and as per the previous screenshot, you should hopefully be able to find your scripts under your Documents\Visual Studio 2015 folder.
We’ve all heard of Location, Location, Location, well in the SQL Server world it is Monitoring, Monitoring, Monitoring.
Without active monitoring of your SQL environment you’re basically an ambulance at the bottom of the cliff and probably should be looking for another profession.
You’ll likely see people posting various scripts to check for issues ad hoc when something has gone wrong, but really this is the at the too late stage and you should be looking for another profession.
If you are a DBA, what you need to be doing is implementing your own, or using a 3rd party, solution to continually monitor and alert you to what you SQL environment is doing or not doing.
When saving data you may have wondered if converting a null value to another character i.e. a space, impacts disk space usage in your database.
Why would you convert a null to a space or dash? Well some people/organizations, rightly or wrongly, prefer this for reporting purposes or some other business requirement.
OK, lets review 3 common storage types, int, char and varchar:
Finally we get a difference of 3 bytes when using a null rather than another character when using varchar.
Conclusion, not all data types allow for space savings when using nulls.
With the newly released SSMS v17.6 there has been a new Drag/Drop section added to Tools -> Options menu item as shown below:
When both are set to True you’ll get the existing functionality
When both are set to False you will now get:
Notice that the Database Version column will still have brackets as it has a space in the name even though Surround object names with brackets when dragged is set to False.
Yes, AKAWN Monitoring can do all this, and best of all, if you have one instance, you can do it for free.
GUI, emails, trends etc it doesn’t get much better than that.
For a limited time, 1 (one) free AKAWN Monitoring license is being given away to each registered user when using the promo code: 1million
The license does not expire and can be used for either Production or Non-Production monitoring.
If 2 or more licenses are purchased, the free license can be deducted by using the same promo code.
Give the product a try and let us know what you think!
AKAWN Monitoring Download and Validation
AKAWN Monitoring Server Setup Using A Domain Account
AKAWN Monitoring Server Setup Using A Local Account
AKAWN Monitoring Studio Setup
Not everyone will be in a position to use AM for monitoring their SQL Server environment, but the ones that can will benefit from the ever-increasing capabilities i.e. currently you’ll enjoy:
- Support for all versions from SQL Server 2005 onward
- Support for all editions of SQL Server
- The ability to monitor SQL Server on Windows, Linux and Azure SQL Databases
- Agent-less monitoring with a minuscule CPU/network overhead
- Schedule checks when you want them to occur and at the frequency you desire
- Easy check exceptions for expected events
- Alert and/or page based on alerts
- Trending of Server and SQL Server performance counters with easy on the eye charts
- Detailed Active query tracking on a schedule of your choosing with included query plans, locks, waits, connectivity info etc
- Full, Transaction and Differential backup capabilities
- Index maintenance and integrity check capabilities
- 100+ checks, including the mandatory missing indexes, duplicate indexes, top CPU/IO queries, security auditing etc
- A refreshing environment dashboard for a simple high-level overview of what is occurring in your SQL environment
- An increasing number of site reports which can be exported to Word/Excel/PDF
- A monitoring license which never expires
All of this for ~ NZ$1.50 a week, it is almost free, try it now, you can’t go wrong Download Page