Skip to content

Notice

Regression Testing before CUs and Service Packs

Voice of the DBA

Someone asked me on Twitter recently if I ran full regression tests before applying Cumulative Updates (CUs). I decided it wasn’t worth discussing in 140 character chunks, so I decided to jot a few notes down. I’ll also expand this to encompass Service Packs since these are almost CU rollups delivered yearly.

The short answer: it depends.

I hate giving that answer, but it’s honestly the correct one. There isn’t a single way to answer this question without examining the situation and environment in which I’m working.

Do I Have Regression Tests?

You’d be surprised how many apps I’ve worked on, whether third party or developed internally, where we didn’t have a set of comprehensive regression tests. If I was lucky, we had a good set of tests for each release, but more often than not I’ve found developers and testers focusing on specific features and ignoring the overall application.

View original post 696 more words

Query to Return SQL Job and Schedule Info

Tasked with getting an inventory of the all the SQL Jobs on all your database servers?  Need to know which jobs are running when and with what frequency? Are email notifications configured?  Are jobs being logged to event log?

I wrote the following query that returns Job Name, Job Owner, Job Category, Job Description, Is Enabled, Originating Server, Is Scheduled, Email Notification Criteria, Email Address, Log to Event Log, Schedule Name, Schedule Frequency, Schedule Occurrence, Schedule Recurrence, Schedule Frequency and job deletion Criterion.

USE msdb;
GO

SELECT
GetDate() AS DateRun
, [sJOB].[name] AS [JobName]
, [sDBP].[name] AS [JobOwner]
, [sCAT].[name] AS [JobCategory]
, [sJOB].[description] AS [JobDescription]
, CASE [sJOB].[enabled]
WHEN 1 THEN ‘Yes’
WHEN 0 THEN ‘No’
END AS [IsEnabled]
, [sSVR].[name] AS [OriginatingServerName]
, CASE
WHEN [sSCH].[schedule_uid] IS NULL THEN ‘No’
ELSE ‘Yes’
END AS [IsScheduled]

, CASE [sJOB].[notify_level_email]
WHEN 0 THEN ‘NEVER’
WHEN 1 THEN ‘On Success’
WHEN 2 THEN ‘On Failure’
WHEN 3 THEN ‘On Complete’
END AS [Email Notification]
, sOps.email_address
, CASE [sJOB].[notify_level_eventlog]
WHEN 0 THEN ‘Never’
WHEN 1 THEN ‘On Success’
WHEN 2 THEN ‘On Failure’
WHEN 3 THEN ‘Always’
END AS [Log to EventLog]
, [sSCH].[name] AS [JobScheduleName]
, CASE
WHEN [freq_type] = 64 THEN ‘Start automatically when SQL Server Agent starts’
WHEN [freq_type] = 128 THEN ‘Start whenever the CPUs become idle’
WHEN [freq_type] IN (4,8,16,32) THEN ‘Recurring’
WHEN [freq_type] = 1 THEN ‘One Time’
END [ScheduleType]
, CASE [freq_type]
WHEN 1 THEN ‘One Time’
WHEN 4 THEN ‘Daily’
WHEN 8 THEN ‘Weekly’
WHEN 16 THEN ‘Monthly’
WHEN 32 THEN ‘Monthly – Relative to Frequency Interval’
WHEN 64 THEN ‘Start automatically when SQL Server Agent starts’
WHEN 128 THEN ‘Start whenever the CPUs become idle’
END [Occurrence]
, CASE [freq_type]
WHEN 4 THEN ‘Occurs every ‘ + CAST([freq_interval] AS VARCHAR(3)) + ‘ day(s)’
WHEN 8 THEN ‘Occurs every ‘ + CAST([freq_recurrence_factor] AS VARCHAR(3))
+ ‘ week(s) on ‘
+ CASE WHEN [freq_interval] & 1 = 1 THEN ‘Sunday’ ELSE ” END
+ CASE WHEN [freq_interval] & 2 = 2 THEN ‘, Monday’ ELSE ” END
+ CASE WHEN [freq_interval] & 4 = 4 THEN ‘, Tuesday’ ELSE ” END
+ CASE WHEN [freq_interval] & 8 = 8 THEN ‘, Wednesday’ ELSE ” END
+ CASE WHEN [freq_interval] & 16 = 16 THEN ‘, Thursday’ ELSE ” END
+ CASE WHEN [freq_interval] & 32 = 32 THEN ‘, Friday’ ELSE ” END
+ CASE WHEN [freq_interval] & 64 = 64 THEN ‘, Saturday’ ELSE ” END
WHEN 16 THEN ‘Occurs on Day ‘ + CAST([freq_interval] AS VARCHAR(3))
+ ‘ of every ‘
+ CAST([freq_recurrence_factor] AS VARCHAR(3)) + ‘ month(s)’
WHEN 32 THEN ‘Occurs on ‘
+ CASE [freq_relative_interval]
WHEN 1 THEN ‘First’
WHEN 2 THEN ‘Second’
WHEN 4 THEN ‘Third’
WHEN 8 THEN ‘Fourth’
WHEN 16 THEN ‘Last’
END
+ ‘ ‘
+ CASE [freq_interval]
WHEN 1 THEN ‘Sunday’
WHEN 2 THEN ‘Monday’
WHEN 3 THEN ‘Tuesday’
WHEN 4 THEN ‘Wednesday’
WHEN 5 THEN ‘Thursday’
WHEN 6 THEN ‘Friday’
WHEN 7 THEN ‘Saturday’
WHEN 8 THEN ‘Day’
WHEN 9 THEN ‘Weekday’
WHEN 10 THEN ‘Weekend day’
END
+ ‘ of every ‘ + CAST([freq_recurrence_factor] AS VARCHAR(3))
+ ‘ month(s)’
END AS [Recurrence]
, CASE [freq_subday_type]
WHEN 1 THEN ‘Occurs once at ‘
+ STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
WHEN 2 THEN ‘Occurs every ‘
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ‘ Second(s) between ‘
+ STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
+ ‘ & ‘
+ STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
WHEN 4 THEN ‘Occurs every ‘
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ‘ Minute(s) between ‘
+ STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
+ ‘ & ‘
+ STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
WHEN 8 THEN ‘Occurs every ‘
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ‘ Hour(s) between ‘
+ STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
+ ‘ & ‘
+ STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
END [Frequency]
, CASE [sJOB].[delete_level]
WHEN 0 THEN ‘Never’
WHEN 1 THEN ‘On Success’
WHEN 2 THEN ‘On Failure’
WHEN 3 THEN ‘On Completion’
END AS [JobDeletionCriterion]
FROM
[msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
ON [sJOB].[originating_server_id] = [sSVR].[server_id]
LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
ON [sJOB].[category_id] = [sCAT].[category_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
ON [sJOB].[job_id] = [sJSTP].[job_id]
AND [sJOB].[start_step_id] = [sJSTP].[step_id]
LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
ON [sJOB].[owner_sid] = [sDBP].[sid]
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
LEFT JOIN [msdb].[dbo].[sysoperators] [sOps]
ON [sOps].[id] = [sJOB].[notify_email_operator_id]
ORDER BY [JobName]

I ran this against my CMS to create a full inventory and audit of jobs across all our SQL servers.  Modify to add or remove columns to suit your needs.

References:

  1. Create a Job Category – http://msdn.microsoft.com/en-us/library/ms191128.aspx
  2. Custom job categories to organize your SQL Agent jobs – http://www.mssqltips.com/sqlservertip/1484/custom-job-categories-to-organize-your-sql-agent-jobs/
  3. SQL Server Agent Jobs without an Operator – http://www.mssqltips.com/sqlservertip/2390/sql-server-agent-jobs-without-an-operator/

Decryption of data encrypted with Cell Level Encryption is unsuccessful / unreadable

I recently had to create a table that required encryption of string data in one of the fields.  I decided to implement Cell Level Encryption using a symmetric key encrypted by a certificate.  The first row of data encrypted fine and could be decrypted and read successfully. However, when decrypting the subsequent record inserted, I received unreadable garbage back.  To demonstrate the behavior and how to resolve it, I created the following table in Adventureworks.

CREATE TABLE [AdventureWorks2012].[dbo].[SecretStuff](
    [MyID] [int] IDENTITY(1,1) NOT NULL,
    [Stuff] [nvarchar](255) NULL,
    [EncryptedStuff] [varbinary](max) NULL,
CONSTRAINT [PK_AdventureWorks2012.SecretStuff] PRIMARY KEY CLUSTERED
(
    [MyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

I then insert a record (in the Stuff column)

Insert Into [AdventureWorks2012].[dbo].[SecretStuff] (Stuff)
Values (stuffabc123)

I’ve created a  Database Master Key (By Password) and Certificate MCER for the Adventurworks2012 Database.  I then created a Symmetric Key(MySymmetricKeyCert) encrypted by the certificate (MCER)

–Create a Database Master Key (DMK) Encrypted by Password $Str0ngPa$$w0rd
USE Adventurworks2012
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘$Str0ngPa$$w0rd’;
GO

–Create Certificate Subject of Metadata in sys.certificates should reflect use
CREATE Certificate MCER with Subject = ‘Encrypt Data for SecretStuff Test’
GO

–Create Symmetric Key Encrypted by Certificate
CREATE SYMMETRIC KEY MySymmetricKeyCert
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE MCER
GO

For Demo purposes, the string value in the Stuff column will be encrypted it in the Encrypted Stuff column

—Open DatabaseMasterKey
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘$Str0ngPa$$w0rd’

–Open the Symmetric Key
USE AdventureWorks2012;
OPEN SYMMETRIC KEY MySymmetricKeyCert
DECRYPTION BY CERTIFICATE MCER
GO

UPDATE [AdventureWorks2012].[dbo].[SecretStuff]
SET [EncryptedStuff] = ENCRYPTBYKEY(KEY_GUID(‘MySymmetricKeyCert’), Stuff)
GO

Taking a look at the data

SELECT * FROM [AdventureWorks2012].[dbo].[SecretStuff]

image

Now We test decrypting

SELECT MyID, Stuff, CONVERT([nvarchar](255)
            ,DECRYPTBYKEY([EncryptedStuff])) AS [DecryptedStuff]
FROM [AdventureWorks2012].[dbo].[SecretStuff]
GO

image

So far, so good.  Time to insert and encrypt the next record

Use [AdventureWorks2012];
GO
OPEN SYMMETRIC KEY MySymmetricKeyCert
DECRYPTION BY CERTIFICATE MCER;
GO

INSERT INTO [AdventureWorks2012].[dbo].[SecretStuff] ([Stuff],[EncryptedStuff])
VALUES (‘morestuffabc123’, EncryptByKey( Key_GUID(‘MySymmetricKeyCert’), CONVERT(varbinary(max),’morestuffabc123′) ) );   
GO

Looking at the data in the table again

SELECT *,CONVERT([nvarchar](255)
            ,DECRYPTBYKEY([EncryptedStuff])) AS [DecryptedStuff]
FROM [AdventureWorks2012].[dbo].[SecretStuff]
GO

image

Why is it being decrypted into Chinese gibberish? Why is the decryption illegible?  Why did the decryption fail?

What happened is related to how SQL deals with string constants.  To support backwards compatibility, SQL will return a non-Unicode string unless the sting is preceded by the N prefix (http://support.microsoft.com/kb/239530). 

“If you do not prefix a Unicode string constant with N, SQL Server will convert it to the non-Unicode code page of the current database before it uses the string.”

“If your application is Unicode-enabled and sends data to SQL Server 7.0 as Unicode string constants without the N prefix, you may encounter a loss of character data. When SQL Server converts a Unicode string without the N prefix from Unicode to the SQL Server database’s code page, any characters in the Unicode string that do not exist in the SQL Server code page will be lost. “

So what?  The string “morestuffabc123”  doesn’t seem to contain any characters that wouldn’t be recognized in the non-unicode character set.  Turns out, because I did not specify the string as Unicode, the data encrypted was in varchar format.  To be decrypted correctly you must specify varchar in the decryption statement.

SELECT *,CONVERT([varchar](255)
            ,DECRYPTBYKEY([EncryptedStuff])) AS [DecryptedStuff]
FROM [AdventureWorks2012].[dbo].[SecretStuff]
GO

image

Now we can see that the 2nd record can be successfully decrypted but the first lost data.  The update statement used to encrypt the first record, encrypted as an nvarchar.  Converting nvarchar (Unicode) to varchar (Non-Unicode) when decrypting resulted in data being lost.

Best to stick to one string data type/character set.  I drop the second row (and reseed) and re-insert the data specifying Unicode nvarchar.

USE AdventureWorks2012;
GO
DELETE FROM [AdventureWorks2012].[dbo].[SecretStuff]
WHERE MyID = 2;
GO

DECLARE @MaxID INT
SELECT @MaxID = MAX(MyID)
FROM [AdventureWorks2012].[dbo].[SecretStuff]
DBCC CHECKIDENT(‘[AdventureWorks2012].[dbo].[SecretStuff]’, RESEED, @MaxID)
GO

INSERT INTO [AdventureWorks2012].[dbo].[SecretStuff] ([Stuff],[EncryptedStuff])
VALUES (‘morestuffabc123’, EncryptByKey( Key_GUID(‘MySymmetricKeyCert’), CONVERT(varbinary(max),N‘morestuffabc123’) ) );
GO

Performing the decrypt

SELECT *,CONVERT([nvarchar](255)
            ,DECRYPTBYKEY([EncryptedStuff])) AS [DecryptedStuff]
FROM [AdventureWorks2012].[dbo].[SecretStuff]
GO

image

So why the Chinese/Japanese/Korean when decrypting varchar as nvarchar? My guess is maybe because the nchar/nvarchar data types use 2 bytes per character and char/varchar use 1 byte per character.  Non-Unicode data types are only 1 byte and can only represent up to 256 different characters.  The extra byte in the Unicode specification allows encoding using 2 bytes  (65,536 characters) in order to handle larger alphabets like Hanzi,Kanji or Hangul.  When decrypting varchar as nvarchar, SQL added an extra byte and returned Chinese characters.

References:

1) INF: SQL Server Code Pages and AutoAnsiToOem Behavior – http://support.microsoft.com/kb/153449

2) You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server – http://support.microsoft.com/kb/239530

3) Varchar vs char vs nvarchar vs nchar – http://ask.sqlservercentral.com/questions/1277/varchar-vs-char-vs-nvarchar-vs-nchar.html

4) Using Unicode Data – http://msdn.microsoft.com/en-us/library/ms191200(v=SQL.105).aspx

5) DecryptByKey shortens original string to 1 character if converte from vrchar to nvarchar or vice versa –http://connect.microsoft.com/SQLServer/feedback/details/376377/decryptbykey-shortens-original-string-to-1-character-if-converte-from-vrchar-to-nvarchar-or-vice-versa

“If your application is Unicode-enabled and sends data to SQL Server 7.0 as Unicode string constants without the N prefix, you may encounter a loss of character data. When SQL Server converts a Unicode string without the N prefix from Unicode to the SQL Server database’s code page, any characters in the Unicode string that do not exist in the SQL Server code page will be lost. “

6) Encrypt/Decrypt (similar issue) – http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/3374cf2d-7cd3-4cd9-8600-abbae665ab36

7) When must we use NVARCHAR/NCHAR instead of VARCHAR/CHAR in SQL Server? – http://stackoverflow.com/questions/612430/when-must-we-use-nvarchar-nchar-instead-of-varchar-char-in-sql-server

8) Decryption leading to unreadable data – http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/5aa569b3-fb04-42ea-92fd-706d4fff476a/

9) Decrypting symmetric key encrypted data is…http://www.sqlservercentral.com/Forums/Topic818014-1526-1.aspx#bm819821

10) Collation and Code Page Architecture – http://msdn.microsoft.com/en-us/library/ms186356(v=SQL.90).aspx

SQL performance slow on Hyper-V VM (Broadcom Nics and VMQ)

I recently started having serious issues with PageIOLatch_sh and OLEDB waits across almost all my SQL Servers (All SQL VMs).  These waits can be indicative of  problems with the disk subsystem.  DBCC CheckDBs and SQL Backups also became extremely slow.  Ran sp_Blitz across my CMS and all servers returned issues with disk read and write latency.  We recently moved these SQL VMs to a new SAN.  Table and Heap scans were not an issue and Page Life Expectancy was above 10,000. Given all these clues, I was pretty sure this was a SAN issue but I wanted to get all my data together before bringing the issue up with the SAN Admin.  I just finished reviewing my notes (graphs of IO and job performance before and after) and watching Brent Ozar’s How to Prove It’s a SAN Problem Webcast Video, when things came to a head.  Our Boss called us in and I explained my case.  SAN Admin was adamant nothing was wrong on his end.  I mentioned Pinal Dave’s Blog regarding HBA queue depth as a possible culprit to investigate.  Well it turned out the issue was with Hyper-V VMQ and the SAN Broadcom iSCSI NICs.  SAN Admin disabled VMQ and performance returned to normal.  Turns out the old SAN had Intel NICs and VMQ disabled.  I did a little research and found this article that referenced the whole thing, Hyper-V Virtual Machine Very Slow Network – VMQ.  Also Checked Technet regarding VMQ and found this nugget:

“When VMQ is enabled, a dedicated queue is established on the physical network adapter for each virtual network adapter that has requested a queue. As packets arrive for a virtual network adapter, the physical network adapter places them in that network adapter’s queue. When packets are indicated up, all the packet data in the queue is delivered directly to the virtual network adapter. Packets arriving for virtual network adapters that don’t have a dedicated queue, as well as all multicast and broadcast packets, are delivered to the virtual network in the default queue.”

More than likely the solution is not disabling VMQ but upgrading the iSCSI drivers (or switching to Intel), dedicating a queue and/or configuring the appropriate queue depth ala Joe Sack’s post – SQL Server and HBA Queue Depth Mashup and Christian Bolton’s – Tuning your SAN: Too much HBA Queue Depth? blog posts.

References:

1) How to Prove It’s a SAN Problem Webcast Video – http://www.brentozar.com/archive/2011/08/how-prove-its-san-problem-webcast-video/

2) sp_Blitz – http://www.brentozar.com/blitz/

3) SAN Optimization Tips for SQL Server Performance – http://www.brentozar.com/sql/sql-server-san-best-practices/

4) SQL Server and HBA Queue Depth Mashup – http://blogs.msdn.com/b/joesack/archive/2009/01/28/sql-server-and-hba-queue-depth-mashup.aspx

5) Tuning your SAN: Too much HBA Queue Depth? – http://sqlblogcasts.com/blogs/christian/archive/2009/01/12/tuning-your-san-too-much-hba-queue-depth.aspx

6) Using Virtual Machine Queue – http://technet.microsoft.com/en-us/library/gg162704(v=ws.10).aspx

7) Machine Very Slow Network – VMQ. – http://blog.osmicro.org/hyper-v-virtual-machine-very-slow-network-vmq/

8) SQL SERVER – PAGEIOLATCH_DT, PAGEIOLATCH_EX, PAGEIOLATCH_KP, PAGEIOLATCH_SH, PAGEIOLATCH_UP – Wait Type – Day 9 of 28 – http://blog.sqlauthority.com/2011/02/09/sql-server-pageiolatch_dt-pageiolatch_ex-pageiolatch_kp-pageiolatch_sh-pageiolatch_up-wait-type-day-9-of-28/

9) SQL SERVER – IO_COMPLETION – Wait Type – Day 10 of 28 – http://blog.sqlauthority.com/2011/02/10/sql-server-io_completion-wait-type-day-10-of-28/

10) Fixng the OLEDB wait type – http://www.confio.com/logicalread/sql-server-oledb-wait-type/

11)

SQLCMD–Error: Named Pipes Provider: Could not open a connection to SQL Server [2]

Started SQLCMD, open a command window (START –> RUN –> CMD) and type sqlcmd –s <fully qualified server name>\<instance name>

Connecting from Local machine to Local SQL Server instance….

Got the following error…

image

 

Enabled Named Pipes for this instance using SQL Server Configuration Manager and then restarted the SQL Server Service for the instance ‘multi’. No luck, same error.

Checked to see if remote admin connections was enabled

image

Still same error….

Repeated SQLCMD statement using IP address.  Still same error…..

Checked SQLCMD Utility parameters on http://msdn.microsoft.com/en-us/library/ms162773.aspx

Using the –E (use trusted connection) parameter

image

-E is a default, Still no dice…

SQLCMD by default uses Windows authentication,

Edited the SQL Server Service login account from Network Service to Local system resolved the error.

Weirdness, I switched the login account back to network service just to check, and it still works!

References:

1) Use the sqlcmd Utility

2) sqlcmd Ultility

3) Tutorial: sqlcmd Utility

4) Using the sqlcmd Utility

5) Connecting to SQL Server Using SQLCMD Utility

Reseed an Identity Column

You have a table with an identity column.  You decide (poorly) to delete the last row you inserted.  The next row insert jumps to the next identity value and now you no longer have sequential identity values.

Some countries laws require sequential identity values (in Nicaragua its retail transaction tables).  For whatever reason a row was deleted, how do you fix the identity column restoring sequential value insertion?

Use DBCC CHECKIDENT to reseed the identity column.

/* Get current Max Value and reseed table */
DECLARE @MaxID INT
SELECT @MaxID = MAX(ID)
FROM TableID
DBCC CHECKIDENT(‘TableID’, RESEED, @MaxID)
GO

References:

1)  SQL SERVER – Reseed Identity of Table – Table Missing Identity Values – Gap in Identity Column – PInal Dave

2) DBCC CHECKIDENT

Using Ola Hallengren’s SQL Maintenance Scripts, SQLCMD needs to be on one line.

Recently, I switched from the default SQL maintenance plans to using Ola Hallengren’s SQL maintenance stored procedure scripts.

While it is possible to configuring SQL job steps to run T-SQL to execute the stored procedures, it is recommended to run the job steps as Type: Operating system (CmdExec).  This approach allows “error handling  and logging to work as designed” and makes the job(s) more robust since a T-SQL step will stop executing after the first error.

Note:  Care should be taken when entering the job step (Operating system (CmdExec) Command. Initially, I hit enter to improve the readability of the following command.

image

WARNING!  This job step will run successfully but no backup will actually occur!  I realized this when I double checked the backup directory and didn’t find any subdirectories or backup files.

Running SQLCMD is not the same as T-SQL.  To run correctly, the SQLCMD must be entered as one line.

image

Once I fixed this, the subdirectories and backup files were created as expected.

Using Powershell Get-WMIObject win32 service, Get-Service and Get-ItemProperty to help take a server inventory

I find it is very useful to take (and maintain) an inventory of the SQL servers I’m responsible for.  Documenting services, as well as installed applications, helps in troubleshooting (see my non-paged pool post) and can aid in recovering from a disaster situation.

The following Power Shell scripts use Get-WMIObject, Get-Service and Get-ItemProperty .

# clear-Host
Get-WmiObject Win32_Service -ComputerName MyServer | `
Sort-Object Name | `
Select-Object Name, Description, StartMode, State, Status, StartName | `
export-csv c:\serviceslist.csv

Uses Get-WmiObject Win32_Service to remotely query and dump to a local CSV file, the Windows Services Name, Description, Startmode (Auto,Manual, Disabled), State (Running or Stopped), Status (OK or ??? ) and Startname (Logon As).  As a SQL DBA, it would be highly recommended to keep track of which logon account each SQL service runs under.

Unfortunately the name returned above is the Service Name and not the Display Name. This is the name that you would see when running Services.msc.  To include the more readable DisplayName, I had to run the following:

# clear-Host
Get-Service -ComputerName MyServer  | `
Sort-Object Name | `
Select-Object DisplayName | `
export-csv c:\servicesdisplaynamelist.csv

Get-Service does not include Description, StartMode,  Status, StartName objects.  I’m still learning Power shell and couldn’t come up with a script to merge the ouput so I just opened both files and merged them manually in Excel.  As I write this post, I figure, one could script these to incorporate a SQL insert/update statement.  It would probably be a better idea to maintain this info in a database anyway.  Maybe even execute the PS script using SQL agent on a schedule…..For now this is food for a future post.

Lastly, I used the following script to get a list of all the applications that you would normally see using Control Panel –> Programs and Features.

if (!([Diagnostics.Process]::GetCurrentProcess().Path -match ‘\\syswow64\\’))
{
$unistallPath = “\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\”
$unistallWow6432Path = “\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\”
@(
if (Test-Path “HKLM:$unistallWow6432Path” ) { Get-ChildItem “HKLM:$unistallWow6432Path”}
if (Test-Path “HKLM:$unistallPath” ) { Get-ChildItem “HKLM:$unistallPath” }
if (Test-Path “HKCU:$unistallWow6432Path”) { Get-ChildItem “HKCU:$unistallWow6432Path”}
if (Test-Path “HKCU:$unistallPath” ) { Get-ChildItem “HKCU:$unistallPath” }
) |
ForEach-Object { Get-ItemProperty $_.PSPath } |
Where-Object {
$_.DisplayName -and !$_.SystemComponent -and !$_.ReleaseType -and !$_.ParentKeyName -and ($_.UninstallString -or $_.NoRemove)
} |
Sort-Object DisplayName |
Select-Object DisplayName | `
export-csv C:\InstalledApps.csv
}
else
{
“You are running 32-bit Powershell on 64-bit system. Please run 64-bit Powershell instead.” | Write-Host -ForegroundColor Red
}

I had to run this last script locally on each machine and copy the output csv to my local machine.  Again I think this could probably modify this to use an update/insert SQL statement to write the output to a database.  The blog I got this from included a solution to query remote servers.  Someday soon I’ll make the time and combine the two in one script and post it when I’m done

References:

1) Windows PowerShell Filtering and Formatting Data (Don Jones) – http://technet.microsoft.com/en-us/magazine/2007.04.powershell.aspx

2) Getting list of installed software that matches control panel’s ‘add/remove programs’ or ‘programs and features’ list (Daniel Jones) – http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/thread/1fd035f3-a170-4721-a6b5-d4809ca2e39d

see Whooda’s post for running against remote machine

3) Stairway to SQL PowerShell Level Series (Ben Miller) – http://www.sqlservercentral.com/articles/Stairway+Series/90381/

 

DBCC CHECKDB Msg 2570 Data Purity Errors

Recently updated maintenance plans on several SQL servers to use Ola Hallengrens famed maintenance scripts.  Ola’s DatabaseIntegrityCheck stored procedure use the DATA_PURITY option by default.  The DATA_PURITY option will check each value in a column to see if it’s outside the acceptable range for that column.  For example, “a datetime where the minutes past midnight exceed 1440, a Unicode string where the number of bytes is not a multiple of 2, or a float or real with an invalid floating point value.”  

Running Ola’s script provided me the ‘opportunity’ to learn about and fix Data Purity Errors.

The server where the corrupted database resides is a SQL2005 SP2 instance.  It had been configured to perform nightly CHECKDB DBCC’s using the SQL Maintenance Plan Task.  The built-in SQL2005 DBCC CHECKDB Maintenance task doesn’t do a purity check. Additionally, “Purity errors are not checked for by default on a database upgraded from SQL 2000 or lower”.  Sure enough, the database is Compatibility Level (80).

The new CHECKDB (DATA_PURITY option enabled) job,  returned the following error 16 times (once for each error it found).

Msg 2570, Level 16, State 3, Line 1
Page (1:4232), slot 14 in object ID 309576141, index ID 0, partition ID 20288381976576, alloc unit ID 20288381976576 (type "In-row data"). Column "AMOUNT" value is out of range for data type "decimal".  Update column to a legal value.

CHECKDB found 0 allocation errors and 16 consistency errors in database ‘MyDB’.

The article, Help, my database is corrupt. Now what? by Gail Shaw is a great resource and provided the following guidance: “CheckDB will not fix this. It doesn’t know what values to put in the column to replace the invalid ones. The fix for this is fairly easy, but manual. The bad values have to manually updated to something meaningful. The main challenge is finding the bad rows.”

The kb article goes over the steps in detail.http://support.microsoft.com/kb/923247”.

Note: A F-A-N-T-A-S-T-I-C Resource is the “DBCC CHECKDB Use and Abuse” article by sql-server-pro.com.  Don’t even think about using DBCC CHECKDB with any of the REPAIR options till you read this and the BOL.

As usual for any DBCC CHECKDB error, the recommended course of action is to restore from backup.  In this case (and probably most cases) a poorly written application allowed out-of-range values to be written to the database without raising an error (yet).  We need to repair and or save as much data as we can.

“2570 errors cannot be repaired using any of the DBCC repair options. This is because it is impossible for DBCC to determine what value should used to replace the invalid column value. Thus, the column value must be manually updated.
To perform a manual update, you have to find the row that has the problem. There are two ways to accomplish this.

  • Execute a query against the table that contains the invalid values to find the rows that contain the invalid values.
  • Use the information from the 2570 error to identify the rows that have an invalid value.”

In my case, I have values in the column "AMOUNT" where the value is out of range for data type "decimal".  The table column has a property, Decimal (15,2) which allows for 2 digits to the right of the decimal and up to 13 digits to the left.

The error tells me that perhaps I have a value that either exceeds 13 digits or has too many digits to the right of the decimal point.

To see if I have a too large a number I execute the following query:

SELECT TENDER_PAYFILENBR FROM TENDER  —primary key column
WHERE AMOUNT > 9999999999999.99
OR AMOUNT < -9999999999999.99

Which returned BUPKIS

So now I need to check if there are any values with too many digits to the right of the decimal point. I found this great code here (insert link), which creates a function that can then be used to return the count of digits to the right of the decimal.

CREATE FUNCTION dbo.countDigits (@A float)
RETURNS tinyint AS BEGIN declare @R tinyint
IF @A IS NULL RETURN NULL
set @R = 0 while @A – str(@A, 18 + @R, @r) <> 0
begin
SET @R = @R + 1
end
RETURN @R
END
GO

SELECT Amount, dbo.countDigits(Amount)as CNT from TENDER
Order by CNT DESC

Which returned no value greater than 2.

Exhausting option 1 from above, I proceed to option 2 “Use the information from the 2570 error to identify the rows that have an invalid value.”  This involves use of the undocumented DBCC Page command. To use DBCC Page you must enable Trace flag 3604

DBCC TRACEON (3604)

Note: Use of the DBCC Page Trace flag could impact performance of the database server.  Better to restore the database to another server and run the DBCC Page there to identify the rows/values that are out of range.  Then update the row values on the production instance.

The syntax of DBCC PAGE command is as follows:

DBCC PAGE ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

Where:

    • 0 – print just the page header
    • 1 – page header plus per-row hex dumps and a dump of the page slot array (unless its a page that doesn’t have one, like allocation bitmaps)
    • 2 – page header plus whole page hex dump
    • 3 – page header plus detailed per-row interpretation

From the Error MSG 2570 above we get the filenum and pagenum Page (1:4232), Execute

DBCC (MYDB, 1 ,4232, 3)

Which returns page info for several rows (which can vary based on the size (width) of the row as each page can hold 8K of data):

PAGE: (1:859343)

BUFFER:

BUF @0x042C46A8

bpage = 0x153DC000                   bhash = 0x00000000                   bpageno = (1:859343)
bdbid = 12                           breferences = 3                      bUse1 = 51866
bstat = 0x4c00009                    blog = 0x59ca2159                    bnext = 0x00000000

PAGE HEADER:

Page @0x153DC000

m_pageId = (1:859343)                m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 309576141                                   m_indexId (AllocUnitId.idInd) = 0
Metadata: AllocUnitId = 20288381976576                                    Metadata: PartitionId = 20288381976576
Metadata: IndexId = 0                Metadata: ObjectId = 309576141       m_prevPage = (0:0)
m_nextPage = (0:0)                   pminlen = 51                         m_slotCnt = 87
m_freeCnt = 394                      m_freeData = 7624                    m_reservedCnt = 0
m_lsn = (19515:826:2)                m_xactReserved = 0                   m_xdesId = (0:0)
m_ghostRecCnt = 0                    m_tornBits = 2605326                

Allocation Status

GAM (1:511232) = ALLOCATED           SGAM (1:511233) = NOT ALLOCATED     
PFS (1:857328) = 0x44 ALLOCATED 100_PCT_FULL                              DIFF (1:511238) = NOT CHANGED
ML (1:511239) = NOT MIN_LOGGED      

Slot 0 Offset 0x60 Length 108

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x05FCC060

00000000:   30003300 6153cf77 02000000 7e010200 †0.3.aS.w….~…        
00000010:   00007400 000004f5 d8003d9d 00000000 †..t…….=…..        
00000020:   00003c9d 00000200 0000016f 17000000 †..<……..o….        
00000030:   0000001c 0000f8f1 0f0a0054 00540054 †………..T.T.T        
00000040:   00540054 00540054 005d0067 006c0043 †.T.T.T.T.].g.l.C        
00000050:   6865636b 31303730 30323139 32313335 †heck107002192135        
00000060:   31343036 31353430 31333634 ††††††††††140615401364            

Slot 0 Column 0 Offset 0x4 Length 4

TENDER_PAYFILENBR = 2010076001      

Slot 0 Column 1 Offset 0x8 Length 4

TENDER_WSNBR = 2                    

Slot 0 Column 2 Offset 0xc Length 2

TENDER_TRANSNBR = 382               

Slot 0 Column 3 Offset 0xe Length 2

TENDER_TRANSTYPE = 2                

Slot 0 Column 4 Offset 0x10 Length 2

TENDER_TRANSSEQ = 0 ………

…………………………………                

As the out-of-range value is in Amount column I search for ‘Amount =’ and examine each value (not knowing what I’m looking for yet) using Find Next.  Data for each row in the page is located in one of 84 Slots. Low and behold in Slot 14  (see original DBCC error above)…..

Slot 14 Column 0 Offset 0x4 Length 4

TENDER_PAYFILENBR = 2010076001      

Slot 14 Column 1 Offset 0x8 Length 4

TENDER_WSNBR = 2                    

Slot 14 Column 2 Offset 0xc Length 2

TENDER_TRANSNBR = 412               

Slot 14 Column 3 Offset 0xe Length 2

TENDER_TRANSTYPE = 2                

Slot 14 Column 4 Offset 0x10 Length 2

TENDER_TRANSSEQ = 3                 

Slot 14 Column 5 Offset 0x12 Length 4

CUSTNBR = 128                       

Slot 14 Column 6 Offset 0x16 Length 8

TRANSTIME = Mar 18 2010  1:32PM     

Slot 14 Column 7 Offset 0x1e Length 8

PAYFILE_POSTDATE = Mar 17 2010 12:00AM                                   

Slot 14 Column 8 Offset 0x26 Length 4

TENDERTYPE = 1                      

Slot 14 Column 9 Offset 0x3d Length 11

TENDERNAME = Cash Change            

Slot 14 Column 10 Offset 0x2a Length 9

AMOUNT = -0.00                      
CC_NUMBER = [NULL]                  
CC_EXPIRATION_DATE = [NULL]         
CC_AUTH_NBR = [NULL]                
CC_ZIP = [NULL]                     
CC_STREET = [NULL]                  
CC_OFFLINE_IND = [NULL]             
CHECK_BANK_ROUTING_NUMBER = [NULL]  
CHECK_BANK_ACCOUNT_NUMBER = [NULL]  
CHECK_CHECK_NUMBER = [NULL]         
CHECK_WRITE_NAME = [NULL]           
CHECK_WRITE_ADDRESS = [NULL]        
CHECK_WRITE_CITY = [NULL]           
CHECK_WRITE_STATE = [NULL]          
CHECK_WRITE_ZIP = [NULL]            
CHECK_WRITE_COUNTRY = [NULL]        
REFERENCE = [NULL]                  
CHECK_TYPE_DESC = [NULL]

-0.00 certainly doesn’t look like a valid value.  No other value in the DBCC Page result exceed the range allowed by decimal (15,2).  Inspection of the rows using a select statement can’t find any row with a value with Amount= –0.00. 

SELECT * FROM MYDB WHERE AMOUNT = –0.00 gives incorrect syntax error (a clue?)

How do we go about finding the row in question?  We must look for the associated primary key.  In this case the table has a composite primary key made up of 5 rows (TENDER_PAYFILENBR , TENDER_WSNBR, TENDER_TRANSNBR, TENDER_TRANSTYPE,  and TENDER_TRANSSEQ).  Taking the values for each of these keys from the DBCC Page result associated with Amount= –0.00.  I create the following select statement:

SELECT *
  FROM [TENDER]
  WHERE TENDER_PAYFILENBR = 2010076001 and TENDER_TRANSNBR = 412 and TENDER_TRANSSEQ = 3

Which returns one row whose AMOUNT field is 0.00 but whose page data is –0.00

II then go back to the production database and run the following update statement

UPDATE [TENDER]
SET AMOUNT = 0.00
WHERE TENDER_PAYFILENBR = 2010076001 and TENDER_TRANSNBR = 412 and TENDER_TRANSSEQ = 3

I then re-run the Ola’s DBCC CHECKDB (with DATA_PURITY option)

And now I have only 15 errors ! : )

CHECKDB found 0 allocation errors and 15 consistency errors in database ‘MyDB’.

I could follow the same process above to find and fix the rest of the errors in each row,  however I suspect that the other 15 errors are also because a value of –0.00 was incorrectly stored.  So I update of all rows where amount = 0 in the table to 0:

UPDATE [TENDER]
SET AMOUNT = 0.00
WHERE AMOUNT = 0.00

Subsequent DBCC CHECKDB still fails the Data Purity check!

Don’t know why (yet) but I have to go error by error to identify the row and update each Amount individually.

From the next error :

Msg 2570, Level 16, State 3, Line 1
Page (1:709956), slot 83 in object ID 309576141…..

 

I get the filenum and pagenum and run

DBCC PAGE ( MyDB , 1 , 709956 , 3 )

this time I scroll straight to the entries for Slot 83

Where I find Amount = –0.00.  So I run

UPDATE [TENDER]
SET AMOUNT = 0.00
WHERE TENDER_PAYFILENBR = 2009229001 and TENDER_TRANSNBR = 119 and TENDER_TRANSSEQ = 3

I then re-run the Ola’s DBCC CHECKDB (with DATA_PURITY option)

And now I have only 14 errors ! : )

Like the back of the shampoo bottle says: Wash, Rinse and Repeat

If anyone can tell me why updating all rows where amount =0 to 0 again didn’t fix the issue, PLEASE comment!

References:

1) SQL Server Maintenance Solution (Ola Hallengren) – http://ola.hallengren.com/

2) Help My Database is Corrupt, Now What? (Gail Shaw) – http://www.sqlservercentral.com/articles/Corruption/65804/

3) SQL Server Error Msg 2570 – type "In-row data". Column value is out of range for data type – http://mssqlcorruptiontackle.blogspot.com/2010/12/sql-server-error-msg-2570-type-in-row.html

4) Troubleshooting DBCC error 2570 in SQL Server 2005 and later versions – http://support.microsoft.com/kb/923247

5) How to use DBCC PAGE – http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/10/625659.aspx

6) DBCC CHECKDB – http://technet.microsoft.com/en-us/library/ms176064(v=SQL.105).aspx

7) DBCC CHECKDB Use and Abuse – http://www.sql-server-pro.com/dbcc-checkdb.html

SQL Release Services Blog

http://blogs.msdn.com/b/sqlreleaseservices/

Follow

Get every new post delivered to your Inbox.