:::: MENU ::::

Posts Categorized / dbDigger

  • Dec 19 / 2013
  • 0
DBA Interview questions, dbDigger, Disaster Recovery, High Availability

High Availability VS Disaster Recovery

Most of the times both these terms are used interchangeably however we should have a clear distinction of these in mind. Particularly while dealing with the disaster recovery (DR) and high availability (HA) scenarios.

One word difference
Major difference lies in one word i.e. data. HA does not involve data recovery aspects while DR do as name indicates.

High availability (HA)

  • It is the system’s ability to remain accessible in the event of a system component failure
  • It involves multiple levels of fault tolerance and/or load balancing capabilities into a system
  • It does not include the redundancy or data recovery abilities
  • Service downtime does not exist or is in seconds

Disaster Recovery (DR)

  • It is the process by which a system is restored to a previous acceptable state, after a natural or man-made disaster
  • DR involves recovery of data and services within some down time
  • Downtime depends upon the option used along with the HA involved
  • For DR, measurable characteristics, such as Recovery Time Objective (RTO) and Recovery Point Objective (RPO) drive our DR plan
  • Dec 06 / 2013
  • 0
Backup and Restore, dbDigger, SQL Server Agent scheduled Jobs, SQL Server Error messages, Xp_CmdShell

Executed as user: NT AUTHORITYSYSTEM. xp_create_subdir() returned error 5, ‘Access is denied.’

Recently i got an job failure email alert. This job is involved in backup of few databases and as a part of process it was required to create directories for each database as well. When i explored the job history to get the failure reason following message was found in log

Executed as user: NT AUTHORITYSYSTEM. xp_create_subdir() returned error 5, ‘Access is denied.’ [SQLSTATE 42000] (Error 22048). The step failed.

sa is owner of this job and there is no apparent role of NT AUTHORITYSYSTEM as mentioned in the message. However there is a by design flow that involved NT AUTHORITYSYSTEM.
Point to note is that SQL Server service was configured to run under local system account.


When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. In this case xp_cmdshell was being invoked by sa i.e. sysadmin so it is being executed in context of local system account. Local account was NT AUTHORITYSYSTEM that has no access to create the directories on backup location.


To solve the issue i changed the SQL Server service login to a valid domain account that has access to create folders and files on network location for backup.

  • Dec 03 / 2013
  • 0
Constraints and Keys, Data Modeling and Database Design, dbDigger, DDL, SQL Server Error messages

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

Today i was working to create foreign keys on few tables. Some of ADD CONSTRAINT statements got failed by generating the following error message.

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “%”. The conflict occurred in database “%”, table “%”, column ‘%’.

If we look closely at the descriptive message it points to the same foreign key constraint that we are just trying to create. Reason is that while creating the constraint SQL Server is trying to validate the existing data based on new constraint. There are some records with no reference in their base primary key table. Such records are cause of error here.


we have couple of options. If you want to make sure that no such orphaned records be there in subject table you may just detect and delete them. Then ADD CONSTRAINT statement will work without any error. However deleting the data will hardly be a suitable option. Alternate is to use the ADD CONSTRAINT statement with NOCHECK option. It will make sure that existing data is not validated for constraint at time of creation.


Here is some informative text on these options from BOL.

Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.
If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.


The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.

  • Oct 11 / 2013
  • 0
Backup and Restore, Consultancy, dbDigger, T-SQL Tips and Tricks

Create directories through T-SQL

In a recent task i was connected to SQL Server through SSMS and had no OS access. To configure the automatic backups i was required to create couple of directories on the disk. So without having OS access i had to create directories by using T-SQL. Following is the command that i used to create ‘D:DBBackupsDBDigger’

EXEC master.dbo.xp_create_subdir 'D:DBBackups_DBDIgger'
  • Oct 04 / 2013
  • 0
DBCC Commands, dbDigger, Monitoring and Analysis, Transaction log files

Get total size of log file along with used space percentage for each SQL Server database

Always there exists a single log file for each SQL Server database. Log file keeps growing when in full or bulk logged recovery model. DBAs implement strategies to manage the log file size. There are two aspects of log file size.

  • One is log file size that it captures on disk and is shown at OS level.
  • Second aspect relates to filled part of over all log file.

Suppose there is a log file with size 20 GB on disk then it is quite possible that it is filled with only 1 GB log and rest of 19 GB is empty. SQL Server will keep populating the remaining part unless it is full and then will claim more size according to file growth settings.
It is a basic requirement to have an idea of log file and its used space. Following is a very simple way to get this information. You may also insert the information into a table on daily or weekly basis and may process alerts for these.


Get total size of log file along with used space percentage for each SQL Server database

As a result we have Database name, total log file size as well as the filled log space percentage. Last column status is always zero.

Consult us to explore the Databases. Contact us