SQL Server: EXCEPTION_ACCESS_VIOLATION when Updating Statistics

ISSUE:
Hello everyone, I got an incident when running update statistic on SCCM database.
the UpdateStat was generating lots of dump file with the error bellow:

***Stack Dump being sent to I:\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\SQLDump0048.txt
SqlDumpExceptionHandler: Process 55 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
* *******************************************************************************
*
* BEGIN STACK DUMP:
*   02/29/16 14:38:56 spid 55
*
*
*   Exception Address = 000007FEF15DB985 Module(sqllang+000000000068B985)
*   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION
*   Access Violation occurred reading address 0000000000000000
* Input Buffer 50 bytes –
*             sp_updatestats

CAUSE:
The issue was caused because database is configured to automatically update statistics and one statistic got corrupted.

RESOLUTION:
– Run sp_updatestats again and check in which table the error is ocurring.

Updating [dbo].[PullDPResponse]

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

– Now we know statistics on [dbo].[PullDPResponse] is corrupted. Lets check stats individually to identify which one is corrupted. That table has two:

DBCC SHOW_STATISTICS(‘PullDPResponse’,’PullDPResponse_PK’);   <– It works
DBCC SHOW_STATISTICS(‘PullDPResponse’,’_WA_Sys_08000002_59A78896′)   <– Not working

– Lets drop corrupted stats:

DROP STATISTICS PullDPResponse._WA_Sys_08000002_59A78896;

– Now we are good need to update all stats to make sure everything is fine.

sp_updatestats

List all triggers on database

--List all triggers on database
SELECT  
       TAB.name as Table_Name 
     , TRIG.name as Trigger_Name
     , TRIG.is_disabled  --or objectproperty(object_id('TriggerName'), 'ExecIsTriggerDisabled')
FROM [sys].[triggers] as TRIG 
inner join sys.tables as TAB 
on TRIG.parent_id = TAB.object_id 

SQL Server: Datafiles and sizes

Selecting datafiles and their sizes:

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'AdventureWorks'

SQL Server SSIS: Package migration from version 3 to version 2 failed with error 0xC001700A

I got a error during SSIS package execution:

Executed as user: NGM\sqlprod. …ersion 9.00.3042.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  3:13:51 PM  Error: 2013-12-09 15:13:51.85     Code: 0xC001700A     Source:       Description: The version number in the package is not valid. The version number cannot be greater than current version number.  End Error  Error: 2013-12-09 15:13:51.85     Code: 0xC0016020     Source:       Description: Package migration from version 3 to version 2 failed with error 0xC001700A “The version number in the package is not valid. The version number cannot be greater than current version number.”.  End Error  Error: 2013-12-09 15:13:51.85     Code: 0xC0010018     Source:       Description: Error loading value “<DTS:Property xmlns:DTS=”www.microsoft.com/SqlServer/Dts” DTS:Name=”PackageFormatVersion”>3</DTS:Property>” from node “DTS:Property”.  End Error  Could not load package “N:\DBA\WC_Exp_Mod_NC_CDX\WC_Exp_Mod_NC_CDX_1.dtsx”…  The package could not be lo…  The step failed.

The failed agent jobs were those, which had steps to execute SSIS packages and they were failing with the error message “Description: The version number in the package is not valid. The version number cannot be greater than current version number.”

On investigating the root cause, what we found out was the below:

1. The SSIS subsystem during the SQL Agent job run was trying to execute DTexec.exe from the folder “c:\Program Files\Microsoft SQL Server\90\DTS\BINN”
2. What happens on boxes running SQL version 2005 and Integration service 2008 is that, whenever a SQL 2005 patch is applied, the value of default key at the location “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\SetUp\DTSPath” in the registry gets reset to “c:\Program Files\Microsoft SQL Server\90\DTS\”

Fix:

1. Manually modify the value to reflect the correct value “c:\Program Files\Microsoft SQL Server\100\DTS\”.
2. If your SQL instance is on a cluster, you need to update the registry on all the cluster nodes.
3. Restart SQL integration service and SQL Agent Service. No need to restart the node or SQL Instance service.
4. SQL instance picks up the newly modified value and the same can be verified by querying the table msdb..syssubsystems. Look for the SSIS subsystem record and the value in the field “agent_exec”

Note: This means, unless SQL instance is also upgraded to version 2008, whenever I apply a CU patch to the SQL 2005 instance, I also need to remember to manually fix the registry key on all the nodes.

 

SQL Server Restart pending

To skip “Restart pending” validation when installing SQL Server components or Service Pack , do the next steps:

1. Start regedit.exe
2. Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager
3. Locate PendingFileRenameOperations
4. Remove any data associated with the value (you may want to export the value for later review)
5. Reboot and rerun installation, check should pass.

Query SQL Server Database Email configuration

You can query SQL Server to get Database Mail configuration:

SELECT  SS.account_id, 
        SS.servertype, 
        SS.servername, 
        SS.port, 
        SS.last_mod_datetime, 
        SS.last_mod_user,
        SA.name, 
        SA.email_address
FROM    msdb.dbo.sysmail_server SS
        INNER JOIN msdb.dbo.sysmail_account SA 
        ON SS.account_id=SA.account_id

 

This query is not compatible with SQL Server 2000.

Failed to set registry settings for server network libraries. The action is SetShilohRoot. The error is 2

ERROR 1: The setup has encountered an unexpected error while Completing Commit. The error is: The cluster resource cannot be moved to another group because other resources are dependent on it.

ERROR 2: Failed to set registry settings for server network libraries. The action is SetShilohRoot. The error is 2 (The system cannot find the file specified.)

 

Regular installation failed many times.

Workaround to manually uninstall:

– Unclustering the instance in each server (http://technet.microsoft.com/en-us/library/ms180973%28v=sql.90%29.aspx)

– Uninstall instances as a single instance in each server

– make sure you move the instance resources to the server before start setup wizard

– Go to Control Panel -> Select SQL Server -> Click Remove -> follow the setup wizard

SQL Server 2005 Pending reboot Requirement

When changing or uninstalling a SQL Server 2005 installation on Windows 2003, probably you will see this error message several times requesting to reboot the server:

Pending reboot Requirement

I you can reboot the server, go ahead… if not follow these steps bellow:

– Backup this registry key

– Clear all contents of this registry key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations

 

Clearing this registry key will allow you to bypass the reboot pre check.

SQL Server: Job History

 

select  j.name as 'JobName',
        msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
        ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) 
        as 'RunDurationMinutes'
From    msdb.dbo.sysjobs j 
        INNER JOIN msdb.dbo.sysjobhistory h 
        ON j.job_id = h.job_id 
where   j.enabled = 1   --Only Enabled Jobs
        --and j.name = 'TestJob' --Uncomment to search for a single job
        /*
        and msdb.dbo.agent_datetime(run_date, run_time) 
        BETWEEN '12/08/2012' and '12/10/2012'  --Uncomment for date range queries
        */
order   by JobName, RunDateTime desc

 

SQL Server : Close all connections

USE master
GO
ALTER DATABASE [MyDB]
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [MyDB]
SET ONLINE