SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

Featured Script

The Voice of the DBA

Production Scripts

One of the most useful parts of SQL Server is the SQL Agent scheduler. Over the years I've used this subsystem to automate work and ease the administrative burden of running a SQL Server instance. At times I've even used my SQL Agent to fire off business reports and alerts to nontechnical people that might need to take some action based on the data in our system.

One common task that I see SQL Agent used for is to backup a database using some method (Ola's scripts, SQL Backup, etc.) and then delete the oldest backup. This is a common way of ensuring that you keep xx amount of backups around for your business. Often if the first step (the backup) fails, then the second step (delete backup files) doesn't run. There are times where the execution choices between the steps aren't configured or get changed, and you run into the situation that Paul Randall describes in issue #170 of the SQLskills newsletter. The backup step starts to fail, but the deletes keep running until you don't have any more backups stored.

How does this happen? It's surprisingly easy because most people don't really treat their SQL Agent scripts and code like other code. This often isn't tested well, and rarely tested across time and with unusual conditions. After all, who wants to muck with the time on a production server to be sure that your Agent job works across days? Who wants to force a backup to fail to see how the job handles an issue? Who wants to double check their code when BACKUP is fairly simple syntax and a few quick tests of the delete code works with text files renamed with .bak extensions?

Do you treat your SQL Agent jobs like the production code that they contain? You should. In fact, moving to a more reliable, repeatable, DevOps style environment means that any code in an Agent job needs to be version controlled, it needs to be tested, and it should be a part of some (hopefully, automated) deployment process that ensures that changes to the code are recorded and you are confident of which version of code is on your system.

SQL Agent is a powerful tool, but it's also one that should be treated like a production system. Downtime and simple errors from careless scripting shouldn't be tolerated. We should, and can, do better.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.7MB) podcast or subscribe to the feed at iTunes and Libsyn. feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

SQL in the City

Sign up for more free training from Redgate

Redgate has committed to hosting a free virtual event in every quarter of 2018, and will be kicking this off on February 28 with a livestream themed around data privacy and protection. The agenda has now been released, so you can see who will be presenting, what they will be presenting, and how you can tune in to watch. Find out more about the sessions and register your place

SQL Monitor

Don’t just fix SQL Server problems, prevent them from happening

SQL Monitor helps teams looking after SQL Server be more proactive. Not only does SQL Monitor alert you to current issues, it gives you the information you need to stop them happening in the future. Download SQL Monitor now and get a 14 day free trial

Featured Contents


A Simple Introduction to Dynamic SQL

Paulo de Jesus from SQLServerCentral.com

Dynamic SQL is essentially normal SQL written in such a way that you end up with a “customised” SQL script at run-time. More »


How to Implement Dynamic Data Masking in Azure SQL Database and SQL Server

Additional Articles from Database Journal

Anoop Kumar shows you the SQL Commands (T-SQL) to secure PII/PHI data using the SQL Server Dynamic Data Masking (DDM) feature and explores DDM's built in functions to mask all critical data elements. DDM restricts unauthorized users from accessing critical data with no changes in the application. More »


Baselining a ReadyRoll project from an existing database

When creating a new ReadyRoll project, the schema of your production environment can also be imported as a baseline. Further work can be built on top of the baseline in a sandbox development environment and, when you’re happy with the changes, the project can be deployed to production. More »


From the SQLServerCentral Blogs - Ten Ways To Help Your BI Consultant Be Successful

meaganl from SQLServerCentral Blogs

I’ve been working in the field of business intelligence for over ten years, as a consultant for over five years.... More »


From the SQLServerCentral Blogs - Server Audit Mystery 2: Filtering action_id gets Error Msg 25713

Solomon Rutzky from SQLServerCentral Blogs

This post is, for the most part, a continuation of Server Audit Mystery 1: Filtering class_type gets Error Msg 25713.... More »

Question of the Day

Today's Question (by Steve Jones):

I added this data to my table:

INSERT dbo.Customer
    CustomerName ,
    statusid ,
('Acme', 1, COMPRESS('The number one source for all those road runner capturing schemes')) 

How do I get the description back in its original text?

Think you know the answer? Click here, and find out if you are right.

We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: COMPRESS().

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I've got this table and data:

    intcol INT
    , charcol CHAR(10)
	, varcharcol VARCHAR(100)
	, datecol datetime2
INSERT TheCounts 
 VALUES (1, 'West', 'Denver Broncos', '2018-01-01')
 , (2, 'West', 'Oakland Raiders', '2018-01-01')
 , (3, 'West', 'Los Angeles Chargers', '2018-01-01')
 , (4, 'West', 'Kansas City Chiefs', '2018-01-01')
 , (5, 'South', 'Jacksonville Jaguars', NULL)
 , (5, 'South', NULL, '2018-01-02')
 , (6, NULL, 'Indianapolis Colts', NULL)
 , (7, NULL, 'Houston Texans', NULL)
 , (NULL, 'North', 'Pittsburgh Steelers', NULL)
 , (NULL, 'North', 'Baltimore Ravens', '2018-01-03')
 , (NULL, 'North', 'Cincinnati Bengals', NULL)
 , (NULL, 'North', 'Cleveland Browns', NULL)
 , (13, 'East', 'Cleveland Browns', '2018-01-04')
 , (13, 'East', 'Cleveland Browns', '2018-01-04')
 , (14, 'East', 'Cleveland Browns', '2018-01-04')
 , (14, NULL, 'Cleveland Browns', NULL)

What do I get when running this code?

  COUNT(ALL tc.varcharcol) 
FROM dbo.TheCounts AS tc

Answer: 15


The answer here is 15. The ALL will be applied to all values, with the NULL eliminated.

Ref: COUNT() - click here

The COUNT Functionin T-SQL - http://www.sqlservercentral.com/articles/T-SQL/142568/

» Discuss this question and answer on the forums

Featured Script

Recent database restores

Evgeny Garaev from SQLServerCentral.com

Before I do database refreshes on different environments I often need to know the history of previous restores. That scripts helps me in this case. I usually limit the history by certain date like this "restore_date > '20180125 08:00'" Please change it with the date and time you are interested in also you can use filter based on the database name.

That script has been tested on SQL Server versions: 2005/2008/2008R2/2012/2014/2016.

More »

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 : SQL Server 2017 - Development

sql server radius intersect - I'm fairly experienced when using SQL but new to using the geometry features. I suspect the problem I have is...

Is learning Python/R an advantage for SQL Server Developers? Need an expert advice. - Hello Guys, Is it time for SQL Server Developers to start learning R/Python to excel in their career? Expert advice needed.

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Performance improvement following a disable indexes, shrink, enable indexes - but why? - I'm stumped and need the assistance of the wider community to understand what is going on. The database in question has...

SQL Server 2014 : Development - SQL Server 2014

GROUP BY TO appear IN one row THEN Calculate total and AVG - Hi I currently when i run my query i get the following output. **Current** |year|Weeks|Won|Lost| |---|---|---|---| |2017|48|4|NULL| |2017|49|NULL|2| |2017|49|7|NULL| |2017|50|NULL|8| |2017|50|19|NULL As you can see,the WON and LOST result...

MS Access Compare two columns - MS Access Script I hope you don’t mind me asking for script in an MS Access database, but I don’t...

SQL Server 2008 : T-SQL (SS2K8)

How do I avoid multiplied results when adding a subquery? - I've created a query that includes a subquery.  When I run the subquery alone, it gives me the desired results. ...

SQLServerCentral.com : SQLServerCentral.com Website Issues

Why can't I paste SQL code into a post??? - I've just spent 20 minutes preparing a new post in the SQL 2008 forum. It took me that long to...

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com