| A community of more than 1,600,000 database professionals and growing |
| | | 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.comJoin 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. The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. |
|
|
|
| | ADVERTISEMENT | | | | 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 |
| | | | 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 |
|
|
|
| | | | | 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 » |
| | 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 » |
| | 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 » |
| | 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 » |
| | 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 » |
|
|
| | | Today's Question (by Steve Jones): I added this data to my table: INSERT dbo.Customer ( CustomerName , statusid , CompressedDescription ) VALUES ('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: CREATE TABLE TheCounts ( intcol INT , charcol CHAR(10) , varcharcol VARCHAR(100) , datecol datetime2 ) GO 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? SELECT COUNT(ALL tc.varcharcol) FROM dbo.TheCounts AS tc
Answer: 15 Explanation: 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 |
|
|
| | | | 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 |
| | |
| This email has been sent to [email protected]. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the [email protected]. | 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: [email protected] |
|
|
