Devart Blog

dbForge Team

SQL Server 2016 Stretch Database

Posted by on July 12th, 2016

SQL Server 2016 Stretch Database

On June 1, SQL Server 2016 was released. It brought a large number of novelties into normal development, including the long-announced Stretch Database technology that allows migrating data from SQL Server dynamically to Azure.

From the marketing point of view, Stretch Database has been well promoted. Reasonable arguments were stating that with the growth of historical data the complexity and cost of its maintenance grows as well. As a rational solution to the problem, the automatic migration of obsolescent archival data to the cloud was introduced. Frankly speaking, I enjoyed the idea.

As from SQL Server 2016 RC0, I began testing the Stretch Database technology on two projects I’m helping to develop. The first one is TMetric, a free time tracker with OLTP load, and the second one is an internal project with DW load.

Find invalid objects in your databases

Posted by on January 4th, 2016

DBA has a number of duties that are primarily targeted at supporting of database performance capabilities and data consistency. The administrator can use the CHECKDB command to easily verify the data consistency; however, in case they need to find an invalid object in a database schema, some difficulties may occur.

ORACLE for instance, allows you to get a list of invalid objects:

SELECT owner, object_type, object_name
FROM all_objects
WHERE status = 'INVALID'

SQL Server doesn’t allow to do that directly. In most cases, you need to execute a script to see that an object is invalid. This is very inconvenient…
So let’s create a script that will search invalid objects: (more…)

How to reduce MSDB size from 42Gb to 200Mb

Posted by on December 22nd, 2015

Recently I’ve got a spare minute to see why an old test server was running too slow… I had nothing to do with this, but I was very anxious to find out what was wrong with the server.

First thing, I opened Resource Monitor and looked at the overall load. The sqlserv.exe process took up 100% of CPU and generated a large disk queue exceeding 300… whereas the number greater than 1 is considered problematic.

When analyzing disk activity, I observed continuous IO operations in msdb:


I looked at the size of msdb:

SELECT name, size = size * 8. / 1024, space_used = FILEPROPERTY(name, 'SpaceUsed') * 8. / 1024
FROM sys.database_files

and switch to the “facepalm” mode:

name         size           space_used
------------ -------------- ---------------
MSDBData     42626.000000   42410.374395
MSDBLog      459.125000     6.859375

The data file takes up 42 GB… After a small break, I began to investigate the reason for such «unhealthy» size of msdb, and how to overcome the problems with server performance.

What is the fastest way to calculate the record COUNT?

Posted by on December 15th, 2015

I have always liked simple questions with lots of pitfalls. Here is the one: how do you count the total number of records in a table? At first sight, it’s a snap, but if you dig a little deeper, you can reveal lots of peculiar nuances.

So, let’s start from a simple thing. Do the following queries differ in terms of the end result?

SELECT COUNT(*) FROM Sales.SalesOrderDetail
SELECT COUNT_BIG(*) FROM Sales.SalesOrderDetail


SQL Server 2016 CTP3.1 – What’s New for Developer?

Posted by on December 9th, 2015

Not that long ago, I remember Microsoft’s CEO Satya Nadella has been announcing a new version of SQL Server 2016. And all of a sudden fresh Community Technology Preview versions started appearing one by one (currently the latest version is CTP3.1). Having tested the new version, I would like to share my impressions.

I will now review the new T-SQL syntax features of SQL Server 2016: JSON, GZIP, DROP IF EXISTS, TRUNCATE TABLE by partitions… (more…)

SQL Server Typical Maintenance Plans: Automated Database Backups

Posted by on December 4th, 2015

There are a lot of posts appealing to one simple truth – need perform backups on a regular basis. People will always be divided into two categories: those who do backup, and those who don’t. The first category, which ignores this advice, can often be found on relevant forums with much the same questions:

– My discs failed/someone has deleted my db… How can I restore my data?
– Do you have the latest backup?
– No.

In order to avoid this unpleasant situation, minimum effort is required. First thing you should do is select a disk array for storing backup copies. Since storing backups together with database files is not our choice, the second step will be to create a maintenance plan for the database backup. (more…)

SQL Server Typical Maintenance Plans: Automatic Statistics Update

Posted by on December 1st, 2015

Some time ago, we reviewed the automation of index defrag. Now, it is time to look at statistics.

First of all, what do we need statistics for?

During execution of any query, query optimizer is trying to build an optimal execution plan (within the limits of available information). The plan constitutes the operations sequence, by means of which you can get the result described in the query.

While selecting one or another operation, the query optimizer considers statistics, that describes value distribution for columns within a table or index, as one of the most significant input data resources.


Instant File Initialization – Killer Feature for SQL Server

Posted by on November 24th, 2015

When SQL Server reserves new space on a disk, it initializes the space with zeros. This behavior can be switched off thereby reducing the execution time of some operations and the load on the disk subsystem.

Disk space reservation without initialization is called Instant File Initialization. This feature is not widely known, although its use has become possible starting with SQL Server 2005. (more…)

Capturing SQL Server Trace Data

Posted by on March 16th, 2015

One of the best ways to gather information about SQL Server workloads is to capture trace information. In this article, we will look at how to use SQL Trace and Server Events Profiler for tracing SQL Server activity. You can use this information for auditing, monitoring and troubleshooting SQL Server performance. This article is intended for DBAs who need to collect information about workloads running on SQL Server for analytic purposes. (more…)

Adding Timestamp to a Filename

Posted by on January 23rd, 2015

Summary: This article describes how to add time and date to a filename using the command line.

Sometimes it is crucial to append time and date to the name of a file. For example, we would like to have separate log files for each execution of data synchronization script. To prevent file overriding, we want to specify date and time in the name of each log file.