How to get SQL Server Agent job history

The lower part of the SQL Agent jobs history window contains detailed execution history for the selected job

SQL Server Agent is a Windows service that is widely used by database administrators and developers to automate the different types of critical and complex business and administrative tasks, to be executed based on a regular predefined schedule. It provides us with the ability to schedule an operating system CMD command, PowerShell script, SQL Server Analysis Services command or query, SQL Server Integration Services package or simply a T-SQL script.

March 22, 2019

SQL blocking chains in SQL Server

View the full T-SQL for the query that is part of the blocking chain

SQL blocking means that one or multiple SQL queries are waiting for resources that are locked by another SQL Query. SQL blocking can affect data throughput and SQL Server responsiveness significantly. SQL Server response time is a measure that shows how long the end user that has executed a query must wait until he gets a response. So, in case that a query is doing nothing while waiting on another query to release the lock on the target resource, the response time increases thus making the SQL Server less responsive.

March 4, 2019

Remote database synchronization using a database installer package

Create new or open existing remote database synchronization projects

This article will cover remote database synchronization using a database comparison and synchronization tool that will create a standalone database installer to execute a script, to complete the a synchronization and update between two remote database

March 4, 2019

SQL auditing tools overview

SQL Auditing tools overview - SQL Server Audit

Many SQL auditing tools and solutions are available to help DBAs achieve change-auditing and compliance goals. To achieve high performance auditing fit for a specific environment, it is important to consider all of these different SQL auditing tools which use different approaches, techniques and mechanisms to audit various SQL Server operations and events in order to choose and implement a most suitable one. In this article, we are going to look and compare 5 different SQL auditing tools which leverage different SQL Server mechanisms for auditing, including embedded auditing, transaction logs, database triggers, SQL traces and more. These are:

March 1, 2019

SQL Server Agent job alert configuration

SQL Agent job properties

As a proactive database administrator, one of your main tasks is monitoring different activities happening in your SQL Server instances continuously.

February 28, 2019

How to get a backup SQL database history

Seeing successful SQL Server database backups in last 7 days

This article will review how to get information on your backup SQL database history, including the metadata in MSDB that can be queried, as well as value-added tools and features to group, sort, report, and export this critical information

February 26, 2019

How to automatically compare and synchronize a shared SQL script folder with SQL Server database data

This article will provide an overview of automating the comparison and synchronization the data between a SQL script folder and a live SQL Server database

Challenge

In the article on how to automatically compare and synchronize SQL Server database objects with a shared script folder, a “poor man’s source control repository” was explained. It was shown how to set up a SQL script folder on a shared network location, in order to “mimic” the work on a SQL source control repository, where all developers will commit their changes, without any cost concerns.

February 7, 2019

SQL Index Fragmentation and Maintenance

One of the most critical variables for database performance is SQL Index Fragmentation. We may face blocking, deadlocks, IO issues, Disk spill issues if the fragmentation level is not appropriately managed. It can also lead to improper execution plan as well.

February 6, 2019

How to deploy object changes directly from a SQL source control label

Challenge

One of the challenges when working with SQL source control systems is to have a sort of a “safe mechanism” that will allow developers to revert any changes made on the destination database. That kind of “mechanism” are source control labels, which are basically snapshots of the SQL source control repository captured at specific time where each object is saved as individual SQL script.

February 4, 2019

SQL Server Agent job configuration basics

This article will cover SQL Server Agent capabilities and features as well as tools to extend and enhance these core capabilities.

SQL Server provides us with the ability to schedule the different types of administrative tasks, in order to execute it automatically with customizable configurations. These tasks can be simple queries, database maintenance tasks, such as database backup, integrity check, re-indexing or statistics update, command prompt application, Microsoft ActiveX script, Analysis Services command or complex SQL Server Integration Service Packages. The Windows service that is responsible for scheduling and executing these tasks is called the SQL Server Agent Service.

January 17, 2019

SQL Server data and structure recovery tool use cases

When a disaster hits, recovering lost data and structure is usually paramount. With ApexSQL Recover, the information inside the database (MDF) and transaction log (LDF) files is leveraged in order to create recovery scripts or to perform recovery tasks directly into a database and negate the data/structure loss. Once the recovery task is initiated, inadvertent or malicious changes can be identified, including the who made them and when, and then rolled back to the original state before they were ‘damaged’. Additionally, in many situations users need to extract information from huge backup files which is not always an easy task, and this is where ApexSQL Recover comes into play and simply extracts the data or structure from the backup without requiring or using a tremendous amount of resources used by the restore job which would otherwise be required.

November 2, 2018

SQL Server transaction log reader use cases

One of the most common tasks for ApexSQL Log is to be used as part of a post-incident investigation, usually involving inadvertently or maliciously changed or damaged data to determine who made the changes, what exactly was changed, when and how. With ApexSQL Log, users can dig into the transaction log files and see the exact values before and after the change has occurred as well as review the complete history of changes on the affected rows.

August 24, 2018

SQL Server database Forensic auditing including row history and before-and-after auditing

Prevention is one of the most powerful approaches against any inadvertent or malicious events. But it is not always possible to prepare for all different calamities which may take place in SQL Server environments. Inadvertent changes, SQL injection attacks, malicious manipulations are some of the most frequent disasters SQL Server DBAs and engineers encounter. While the recovery process for the specific events which has negatively impacted the database is paramount, the next critical task in all situations is to find out who or what made the changes, what was changed and when. Gathering this information is a key for preventing other similar events which can affect SQL Server databases and endanger the data, as well as the business itself.

August 14, 2018

Load testing: Recording production transactions and replaying on a test server

Load testing not always the same as stress testing. Stress testing seeks to maximize the load on a server to see when and where it breaks. In theory, you can escalate the load until you isolate those failure points, even to a point where the stress exerted on the server may far exceed anything that would be experienced on production

August 13, 2018

Hands free, no-coding SQL Server database replication of a reporting database

ApexSQL Log is an amazing tool for reading the transaction log and reading the transaction log can form the basis of a lot of great features, functionality. In this case, we’ll look at how you can use ApexSQL Log for transactional replication to keep a reporting database up to date with your production system.

August 7, 2018

What is SQL Server database continuous integration (CI)? – a high level conceptual overview and walk thru of the steps in a SQL database CI pipeline

If you’ve never automated builds in SQL Server before and but are still CI-curious, then this quick high-level overview, should introduce to you the basics and maybe even lead to you wading into the water a little deeper

First, continuous integration means that you will rapidly iterate changes, so that each time a change is formally made, usually by a source control commit, then the target system, a database in our case is automatically destroyed and recreated with the change. Furthermore, the entire test environment is recreated with a combination of static and test data, any automated tests and/or reviews are conducted, and the build is fully documented.

July 26, 2018