2015-08-18

CheckDB error because snapshot file already exists

A ordinary Maintenance Plan with a ordinary Check Database Integrity task failed with this error in the Maintenance Plan log:
A database snapshot cannot be created because it failed to start.
Cannot create file 'D:\\Data\\MyDatabase_Data.mdf_MSSQL_DBCC75' because it already exists. Change the file path or the file name, and retry the operation.

The error text indicates that the file exists which is true.
Also the text indicates that the file path or name can be changed. This is not true in this case as one can't define the physical database snapshot when running a Check Database Integrity task. This is why it can't be defined when running DBCC CHECKDB statement.
In the SQL Server Errorlog the failure is also logged:
2015-08-16 03:11:19.77 spid42      Error: 5170, Severity: 16, State: 1.
2015-08-16 03:11:19.77 spid42      Cannot create file 'D:\Data\MyDatabase_Data.mdf_MSSQL_DBCC75' because it already exists. Change the file path or the file name, and retry the operation.

As the information is similar the failure is confirmed on file details.

In this case the server had a hard shutdown (ups!!!) which caused the CheckDB failure.
To confirm if the database snapshot on the file is still active this statement:
SELECT *
FROM [master].[sys].[databases]
WHERE
  [databases].[name] = 'MyDatabase' AND
  [databases].[source_database_id] IS NOT NULL;

The result is empty which is confirmed by the message:
(0 row(s) affected)

The snapshot file can be inspected on the metadata through PowerShell using the CmdLet Get-Item:
Get-Item -LiteralPath 'E:\Data\MyDatabase_Data.mdf_MSSQL_DBCC75'

As I found no other reference on this error I preserved the database snapshot file by moving it to another location.

Testing the solution is quite simple as I only have to run a CheckDB on the database:
DBCC CHECKDB ('MyDatabase');
As planned the run is good:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'MyDatabase'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

2015-08-14

Attach database

I had to get some data from a old database, where the server is recycled. But at that time I was present enough to get a copy of the data files of the database (mdf & ndf).
The quick solution is to attach the database on my workstation where I have a SQL Server Developer Edition running. This statement should do the trick:
CREATE DATABASE [Repository_old] ON PRIMARY
  (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\DATA\Repository.mdf'),
  (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\DATA\Repository_2.ndf')
FOR ATTACH_REBUILD_LOG


But when I tried to attach the database I got a error on file access.
Msg 5120, Level 16, State 101, Line nn
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\DATA\Repository.mdf". Operating system error 5: "5(Access denied.)".

A quick look on the other SQL Server files in the same folder indicated that the service account should be owner of the files. The error message is on the first file in the statement, but this goes on all files for the database.
This can be done in Windows Explorer through the properties of the file (right-click > properties). On the Security tab you click the Advanced button. Then you get another window named Advanced Security Settings for ... In this windows Owner tab you click the Edit button. Now you might have to log on as you are required to be local administrator.
Click the button Other users or groups to enter the service account. This is not straight forward if you are using a virtual service account. First you have to pick the computer as location. Then you can limit the object type to Builtin service accounts.
Finally entering the name of the service account is a little tricky - in my case I entered "nt service\mssql$mssql2014". Notice the space after "nt"!
I am working on the named instance MSSQL2014 which gives the name of the account after the backslash.
Before clicking the OK button I do recommend that you verify the account name by clicking Check names. If the name is changed to capitals, the "nt service\" is removed and the name is underlined it is OK.

The service account is (default) a Virtual Service Account. This is a local edition of a Managed Service Account. To get the name of the service account you can look in the Server Facet of the instance with Management Studio.

But still I got error on file access.
Msg 5120, Level 16, State 101, Line nn
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\DATA\Repository.mdf". Operating system error 5: "5(Access denied.)".

I usually work on installation where DBAs are isolated from other admins. In this case the database files are moved/copied around using one account and the attach statement is executed with another account. When I then added the user of SQL Server Management Studio (SSMS) who executes the statement to the files with Full Control permissions everything went smooth.
I first tried to limit the rights for the SSMS user the Read permissions, but that didn't work as I got this error
Msg 1813, Level 16, State 2, Line nn
Could not open new database 'Repository'. CREATE DATABASE is aborted.


In this case I did not have the transaction log file (ldf), but I really didn't need it. This is why the statement is with FOR ATTACH_REBUILD_LOG. There are some issues you have to consider like a clean shutdown of the database. Please take a look at the documentation for CREATE DATABASE.
The original transaction log file could not be found by the instance, as expected. This generates a failure message like
File activation failure. The physical file name "H:\MSSQL\Data\Repository_log.ldf" may be incorrect.
But right after the new transaction log file is created automatic
New log file 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\DATA\Repository_old_log.ldf' was created.

2015-01-09

Leap Second and Day

Leap Second

A positive leap second 2015-06-30 has been announced by International Earth Rotation & Reference Systems Service:
hpiers.obspm.fr/iers/bul/bulc/bulletinc.dat
SQL Server gets the time from Windows, who knows how to handle a leap second (KB909614).
The leap second is „smeared“ so that the timestamps are not affected.
This gives that the leap second can not be registered.

In T-SQL it will look like this
(statement)
DECLARE @before_leap_second DATETIME = '2015-06-30 23:59:59'
SELECT @before_leap_second AS [normal_time];

(result)
normal_time
-----------------------
2015-06-30 23:59:59.000


(statement)
DECLARE @leap_second DATETIME = '2015-06-30 23:59:60';
SELECT @leap_second AS [leap_second];

(result)
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

leap_second
-----------------------
NULL


Leap Day

A positive leap day is more precise defined. This gives that it can be defined in a data type, and calculations can be done correct. The Leap Day is nicely handled by T-SQL like this
(statement)
DECLARE @leap_day DATETIME = '2012-02-29';
SELECT @leap_day AS [leap_second];

(result)
leap_day
-----------------------
2012-02-29 00:00:00.000


Also a more tricky Leap Day is handled correct by T-SQL like this
(statement)
DECLARE @leap_day DATETIME = '2000-02-29';
SELECT @leap_day AS [leap_day];

(result)
leap_day
-----------------------
2000-02-29 00:00:00.000


An attempt to use a wrong Leap Day will give an error like above
(statement)
DECLARE @not_leap_day DATETIME = '2013-02-29'
SELECT @not_leap_day AS [not_leap_day];

(result)
Msg 242, Level 16, State 3, Line 11
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

not_leap_day
-----------------------
NULL


History

2015-01-09 Blog entry about Leap Second.
2015-02-10 Blog entry added section about Leap Year.

2014-10-09

DBCC CHECKDB with PowerShell

Microsoft Transact-SQL (T-SQL) is really great for many things inside the SQL Server Database Engine, but automated maintenance with file handling, logging and other nice things is more of a challenge. This is why I and other fellow DBAs use PowerShell to automate tasks.

A standard DBA task is to check the databases, also their integrity with the console command DBCC CHECKDB.
The output like below is (somewhat) nicely formatted for human reading, but I would like to catch it inside the automation so that I can process and log the output.

Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
DBCC results for 'sys.sysrscols'.
There are 1970 rows in 24 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 346 rows in 4 pages for object "sys.sysrowsets".
...
DBCC results for 'Person.vStateProvinceCountryRegion'.
There are 181 rows in 2 pages for object "Person.vStateProvinceCountryRegion".
DBCC results for 'sys.plan_persist_query'.
There are 0 rows in 0 pages for object "sys.plan_persist_query".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'AdventureWorks2014'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(example on DBCC CHECKDB output from the database [AdventureWorks2014])

The PowerShell code is
$DatabaseName = 'AdventureWorks2014'
$Sql = "DBCC CHECKDB ([$DatabaseName]);"
$CheckDbMessages = @()
$CheckDbWatch = [System.Diagnostics.Stopwatch]::StartNew()
SQLCMD.EXE -E -Q $Sql |
ForEach-Object {
  $CheckDbMessages += $_
  "  {0:s}Z  $_" -f ([System.DateTime]::UtcNow)
}
$CheckDbWatch.Stop()

"{0:s}Z  Database [$DatabaseName] integrity check done in $($CheckDbWatch.Elapsed.ToString()) [hh:mm:ss.ddd]." -f ([System.DateTime]::UtcNow)
if ($CheckDbMessages[($CheckDbMessages.Count) - 2] -clike 'CHECKDB found 0 allocation errors and 0 consistency errors in database *') {
  "{0:s}Z  Database [$DatabaseName] integrity check is OK." -f ([System.DateTime]::UtcNow)
}
else {
  "{0:s}Z  Error in integrity check of the database [$DatabaseName]:`n  $($CheckDbMessages[($CheckDbMessages.Count) - 2])" -f ([System.DateTime]::UtcNow)
  throw "Integrity check of the database [$DatabaseName] failed."
}

(example on PowerShell script to execute DBCC CHECKDB)

I have put it inside a (advanced) function, but that is not shown here as is is the DBCC CHECKDB command execution that is the subject. I can only recommend that you take a serious look on PowerShell advanced functions. The best introduction that I still go back to is the book „Learn PowerShell Toolmaking in a Month of Lunches“ by Don Jones and Jeffery Hicks.
The variable $DatabaseName is a String object containing the name of the database to check.The script above does not check the prerequisites to the DBCC CHECKDB command.

  2014-10-09T17:55:04Z  DBCC results for 'AdventureWorks2014'.
  2014-10-09T17:55:04Z  Service Broker Msg 9675, State 1: Message Types analyzed: 14.
  2014-10-09T17:55:04Z  Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
  2014-10-09T17:55:04Z  Service Broker Msg 9667, State 1: Services analyzed: 3.
  2014-10-09T17:55:04Z  Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
  2014-10-09T17:55:04Z  Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
  2014-10-09T17:55:04Z  Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
  2014-10-09T17:55:04Z  Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
  2014-10-09T17:55:04Z  Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
  2014-10-09T17:55:04Z  DBCC results for 'sys.sysrscols'.
  2014-10-09T17:55:04Z  There are 1970 rows in 24 pages for object "sys.sysrscols".
  2014-10-09T17:55:04Z  DBCC results for 'sys.sysrowsets'.
  2014-10-09T17:55:04Z  There are 346 rows in 4 pages for object "sys.sysrowsets".
...
  2014-10-09T17:55:04Z  DBCC results for 'Person.vStateProvinceCountryRegion'.
  2014-10-09T17:55:04Z  There are 181 rows in 2 pages for object "Person.vStateProvinceCountryRegion".
  2014-10-09T17:55:04Z  DBCC results for 'sys.plan_persist_query'.
  2014-10-09T17:55:04Z  There are 0 rows in 0 pages for object "sys.plan_persist_query".
  2014-10-09T17:55:04Z  CHECKDB found 0 allocation errors and 0 consistency errors in database 'AdventureWorks2014'.
  2014-10-09T17:55:04Z  DBCC execution completed. If DBCC printed error messages, contact your system administrator.
2014-10-09T17:55:04Z  Database [AdventureWorks2014] integrity check done in 00:00:04.1890229 [hh:mm:ss.ddd].
2014-10-09T17:55:04Z  Database [AdventureWorks2014] integrity check is OK.

(example on DBCC CHECKDB output from script - AdventureWorks)

Using the good-old SQLCMD.EXE gives the output to the default output stream, where the output then can be processed in the automation by PowerShell.

The SQL Server PowerShell module SQLPS CmdLet Invoke-SqlCmd can also execute the command DBCC CHECKDB, and by setting the -Verbose flag the output is shown - but it is in the Verbose stream and can not be accessed direct in the automation for further processing.
Streaming the Verbose to a file with a redirection operator is possible, but introduces extra complexity...

With ADO.NET the challenge is the same as with the CmdLet Invoke-SqlCmd, and I guess that it is because the CmdLet is constructed in .NET using ADO.NET.

Actually it is a general challenge to catch T-SQL output shown as messages in SQL Server Management Studio. This is because the output is sent async as events.
It is possible to build a strict .NET solution by using SqlInfoMessageEventHandler but I find the solution with SQLCMD.EXE more simple, and that I do like.

The option TABLERESULTS to the command DBCC CHECKDB is not documented by Microsoft, but widely recognized. I do not use the TABLERESULTS option in this solution as the results are delivered during execution but af one answer when the execution is finish. This is the behavior in all cases, also with ADO.NET ExecuteReader.
I have some larger databases, where a check takes several hours and I would like to have a log of the execution. Is everything running? Any errors corrected up til now? and so on...
This is also why I add a timestamp to each message line from DBCC CHECKDB.

2014-10-08

SQL Agent check error count step

I had to create a workaround on a database backup job for a server withe 50+ important databases.
To be sure that an attempt is made to take a backup on each database, have some history easy to access and also a option to reconfigure the job for en extra backup on one or more databases I set up one job step for a backup of each database.
This job structure could be illustrated like this:

  1. job step 1: Backup database alpha.
  2. job step 2: Backup database beta.
  3. job step 3: Backup database gamma.
  4. etcetera...
Each job step continues to the next no matter if the job step fails or not. This is to ensure that an attempt to backup each database is made. This structure will give a false status if the last jobs step does not fail.

To ensure a true job status I have added a final control job step, that check the status of the previous job steps.
The status of the previous job steps I get from the table msdb.dbo.sysjobhistory where I filter on the job start time by inserting SQL Agent Tokens in the T-SQL statement:
DECLARE @error_count INT = (
SELECT COUNT(*)
FROM [msdb].[dbo].[sysjobhistory]
WHERE job_id = $(ESCAPE_NONE(JOBID))
  AND [run_date] = $(ESCAPE_NONE(STRTDT))
  AND [run_time] >= $(ESCAPE_NONE(STRTTM))
  AND [run_status] <> 1);

IF (@error_count > 0)
BEGIN
  DECLARE @error_msg NVARCHAR(2047) = N''Error in Database Full Backup job. Check log file for details. (Error Count = '' + CAST(@error_count AS NVARCHAR(3)) + N'' job steps).'';
  RAISERROR(@error_msg, 19, 0) WITH LOG;
END
ELSE
BEGIN
  RAISERROR(''Database Full Backup job conpleted with no errors.'', 0, 0) WITH NOWAIT;
END

This control jobs step generates a error if a previous job step in the same job has failed. The plan (hope) is that the general monitoring platform like Microsoft System Center will catch the error and raise an incident to the Service Desk.