Scenario:
Active/Active Cluster:
3 Instances: Default and Named Instance 1 are sql 2000 and Named Instance 2 is on SQL 2005
Requirement:
Need to Un Install SQL 2k [Default and Named Instance 1]
Final Look:
You should have only named instance 2 [SQL 2k5]
Problems:
1) It looks like sql server 2000 and sql server 2005 have a common binn files on both the nodes i.e. on D:\mssql\
Backup Taken:
1) Copied Binn folder to the new location
2) All user database backups on SQL 2k5 named instance and also configure the T-log backups for all the database for point in time recovery.
3) MSDB database backup on SQL 2k5 named instance
4) Login Script [optional - take the script of Jobs/Linked Servers]
5) Dropped the SQL Server 2000 cluster resources from the cluadmin as this would stop the alerts [but still the sql server2000 instances remains]
Used the following KB article to uninstall SQL server instance manually
http://support.microsoft.com/kb/290991
We have referenced below kb article to remove the SQL Server from the server:
Plan 1:
1. It looks like sql server 2000 and sql server 2005 have a common binnfiles on both the nodes ie. on D:\mssql\
2. Incase if we follow the below article would there be any issuesfor exitsing 2005 instace ?
http://support.microsoft.com/kb/290991
Other work around would be to drop the SQL Server 2000 cluster resources from the cluadmin this would stop the alerts but still the sql server2000 instances remains.
Plan 2:
We can take a total backup of d:\mssql folder on both thedrives and take down time on all the 3 instances rename the existsing mssql-oldfold
Plan 2 worked for me :), before proceeding with this please be ensure that you have taken the full backup of your databases.
I am related to Software Industry working as SQL Database Administrator, so I have created this blog to collect useful information about my area of work. It may contains references to stuff published somewhere else on internet [with full regards to the publisher, of course] and also some of my own. I will also post some out of the box stuff too, if I find it interesting.
Wednesday 3 September 2008
SSIS packages is going in suspended mode in SQL
Error In Log: 2008-08-22 00:30:00 - ! [LOG] Step 1 of job 'WWE_Live_EventsImporter' (0x02221292890280428E3B425FD9657674) cannot be run because the SSIS subsystem failed to load. The job has been suspended\
When you change servers and restore msdb/Attached old MSDB database, you may find your SSIS Packages/Maintenance plans failing and putting the following messages into various logs:
* The SSIS subsystem failed to load* Subsystem could not be loaded* The job has been suspended* The specified module could not be found
The problem is most likely that the location of your SQL Server installation directory differs from the entries in msdb table syssubsystems. As SQL Server keeps a table of pointers to the SSIS DLLs in msdb, we need to flush this table and repopulate it with the new locations (KB article 914171)
SELECT * FROM msdb.dbo.syssubsystems
Result:
SubSystem_DLL Path : D:\MSSQL.1\MSSQL\binn\SQLDTSSS90.DLL
Check the Actual path for this DLL file, if it is differ then below is the method to fix this:
-- Delete the existing rows.DELETE FROM msdb.dbo.syssubsystems
-- Fill the table with new rows pointing to the proper location of the DLLs.
EXEC msdb.dbo.sp_verify_subsystems 1
Restarted SQL Server Agent for it to pick up the new table entries and unsuspend the SSIS jobs.KB Article for Reference: http://support.microsoft.com/kb/914171
When you change servers and restore msdb/Attached old MSDB database, you may find your SSIS Packages/Maintenance plans failing and putting the following messages into various logs:
* The SSIS subsystem failed to load* Subsystem could not be loaded* The job has been suspended* The specified module could not be found
The problem is most likely that the location of your SQL Server installation directory differs from the entries in msdb table syssubsystems. As SQL Server keeps a table of pointers to the SSIS DLLs in msdb, we need to flush this table and repopulate it with the new locations (KB article 914171)
SELECT * FROM msdb.dbo.syssubsystems
Result:
SubSystem_DLL Path : D:\MSSQL.1\MSSQL\binn\SQLDTSSS90.DLL
Check the Actual path for this DLL file, if it is differ then below is the method to fix this:
-- Delete the existing rows.DELETE FROM msdb.dbo.syssubsystems
-- Fill the table with new rows pointing to the proper location of the DLLs.
EXEC msdb.dbo.sp_verify_subsystems 1
Restarted SQL Server Agent for it to pick up the new table entries and unsuspend the SSIS jobs.KB Article for Reference: http://support.microsoft.com/kb/914171
Databases File Size [Trend Analysis] : SQL 2005 Script
Here is the script that is useful for to capture Databases File Sizes required for Trend Analysis:
/****
CREATE DATABASE TREND
USE TREND
IF OBJECT_ID('DatabaseFiles') IS NULL
BEGIN
SELECT TOP 0 file_id, name, physical_name, size*1.0/128 as [size in mbs], max_size, max_size*1.0/128 as [max_size in mbs],
growth, growth*1.0/128 as [growth in mbs], is_percent_growth INTO DatabaseFiles
FROM sys.database_files
ALTER TABLE DatabaseFiles
ADD CreationDate DATETIME DEFAULT(GETDATE())
END
EXECUTE sp_msforeachdb 'INSERT INTO DatabaseFiles SELECT file_id, name, physical_name, size*1.0/128 as [size in mbs], max_size, max_size*1.0/128 as [max_size in mbs],
growth, growth*1.0/128 as [growth in mbs], is_percent_growth, GETDATE() from [?].sys.database_files'
SELECT * FROM DATABASEFILES
*****/
You can create a scheduled job and include execute script to run once in a week and once you collected 3-6 months data, then you can export the necessary fields to Excel and using pivot table wizard you can do trend analysis.
/****
CREATE DATABASE TREND
USE TREND
IF OBJECT_ID('DatabaseFiles') IS NULL
BEGIN
SELECT TOP 0 file_id, name, physical_name, size*1.0/128 as [size in mbs], max_size, max_size*1.0/128 as [max_size in mbs],
growth, growth*1.0/128 as [growth in mbs], is_percent_growth INTO DatabaseFiles
FROM sys.database_files
ALTER TABLE DatabaseFiles
ADD CreationDate DATETIME DEFAULT(GETDATE())
END
EXECUTE sp_msforeachdb 'INSERT INTO DatabaseFiles SELECT file_id, name, physical_name, size*1.0/128 as [size in mbs], max_size, max_size*1.0/128 as [max_size in mbs],
growth, growth*1.0/128 as [growth in mbs], is_percent_growth, GETDATE() from [?].sys.database_files'
SELECT * FROM DATABASEFILES
*****/
You can create a scheduled job and include execute script to run once in a week and once you collected 3-6 months data, then you can export the necessary fields to Excel and using pivot table wizard you can do trend analysis.
Knowledge Base Article- SQL Server Assertion Errors
Error Description: SQL Server Assertion: File: , line=1070 Failed Assertion = 'db_mem'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
These assertion errors are related to Replication, The fix for this issue was first released in Cumulative Update 9.
Most of the SQL Assertion Errors are now fixed in Cumulative Update 9, whenever you find assertion Errors in the sql server first check if the server is configured for replication and then check the version of sql server hot fix level .
Following KB Article has the fix for Assertion errors. Not always the cu9 has the fix for the assertion we need to analyze / compare the assertions with fixes in cu9 most of the assertions related to replication are now fixed in sql server 2008 (katmai )
References :
http://support.microsoft.com/kb/956376/
http://support.microsoft.com/kb/953752/LN/
CAUSE
This problems occurs because of an interruption that occurs when the Merge Agent or the Log Reader Agent tries to allocate memory in the sqlservr!repl_add_mem function.
SYMPTOMS
Consider the following scenario. In Microsoft SQL Server 2005, you configure a transactional publication. When you synchronize data, you find that the following error message is logged in Replication Monitor:
Date Time SPID Error: 14151, Severity: 18, State: 1.
Date Time SPID Replication-Replication Transaction-Log Reader Subsystem: agent AgentName failed.
The process could not execute 'sp_repldone/sp_replcounters' on PublisherName .
If you configure a merge publication, you find that the following error message is logged in Replication
Monitor when you synchronize data:
The merge process could not perform retention-based meta data cleanup in database DatabaseName . (Source: Merge Replication Provider, Error number: -2147199467) Get help: http://help/-2147199467 General network error. Check your network documentation.
Additionally, the following error message is logged in the SQL Server 2005 Errorlog file:
Date Time SPID * BEGIN STACK DUMP:
Date Time SPID * Date Time SPID
Date Time SPID *
Date Time SPID * Location: repltimer.cpp:1070
Date Time SPID * Expression: db_mem
Date Time SPID * SPID: SPID
Date Time SPID * Process ID: ProcessID
Date Time SPID * Error: 17066, Severity: 16, State: 1.
Date Time SPID * SQL Server Assertion: File:, line=1070 Failed Assertion = 'db_mem'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
Date Time SPID Error: 3624, Severity: 20, State: 1.
Date Time SPID A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
These assertion errors are related to Replication, The fix for this issue was first released in Cumulative Update 9.
Most of the SQL Assertion Errors are now fixed in Cumulative Update 9, whenever you find assertion Errors in the sql server first check if the server is configured for replication and then check the version of sql server hot fix level .
Following KB Article has the fix for Assertion errors. Not always the cu9 has the fix for the assertion we need to analyze / compare the assertions with fixes in cu9 most of the assertions related to replication are now fixed in sql server 2008 (katmai )
References :
http://support.microsoft.com/kb/956376/
http://support.microsoft.com/kb/953752/LN/
CAUSE
This problems occurs because of an interruption that occurs when the Merge Agent or the Log Reader Agent tries to allocate memory in the sqlservr!repl_add_mem function.
SYMPTOMS
Consider the following scenario. In Microsoft SQL Server 2005, you configure a transactional publication. When you synchronize data, you find that the following error message is logged in Replication Monitor:
Date Time SPID Error: 14151, Severity: 18, State: 1.
Date Time SPID Replication-Replication Transaction-Log Reader Subsystem: agent AgentName failed.
The process could not execute 'sp_repldone/sp_replcounters' on PublisherName .
If you configure a merge publication, you find that the following error message is logged in Replication
Monitor when you synchronize data:
The merge process could not perform retention-based meta data cleanup in database DatabaseName . (Source: Merge Replication Provider, Error number: -2147199467) Get help: http://help/-2147199467 General network error. Check your network documentation.
Additionally, the following error message is logged in the SQL Server 2005 Errorlog file:
Date Time SPID * BEGIN STACK DUMP:
Date Time SPID * Date Time SPID
Date Time SPID *
Date Time SPID * Location: repltimer.cpp:1070
Date Time SPID * Expression: db_mem
Date Time SPID * SPID: SPID
Date Time SPID * Process ID: ProcessID
Date Time SPID * Error: 17066, Severity: 16, State: 1.
Date Time SPID * SQL Server Assertion: File:
Date Time SPID Error: 3624, Severity: 20, State: 1.
Date Time SPID A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
How to determine the version of Microsoft SQL Server 2005 Integration Services (SSIS) that is installed on a computer.
You can determine the SSIS version by using one of the following methods.
Notes
•
SSIS does not support multiple instances. You can only install one instance of SSIS on a computer.
•
Method 1 is the easiest method to determine the version of SSIS.
•
If you cannot connect to the SSIS server, use Method 2, Method 3, or Method 4.
Method 1: Use Object Explorer
1.
Open SQL Server Management Studio.
2.
In the Connect to Server dialog box, click Integration Services in the Server type list.
3.
In the Server name box, specify the SSIS server name, and then click Connect.
4.
In Object Explorer, view the version number in the name of the root node.
The name of the root node appears in the following syntax:
Server_Name (Integration Services Version_No – Domain_Name\User_Name)
Notes
•
Server_Name represents the SSIS server name. Usually, the SSIS server name is the network name of the computer that is running SSIS.
•
Version_No respents the SSIS service pack level or the SSIS hotfix level.
•
User_Name represents the user name that you use to connect to SSIS.
•
Domain_Name represents the name of the domain that the user belongs to. If the user does not belong to a domain, Domain_Name represents the name of the computer that is running SSIS.
For example, assume the SSIS server name is MyServerName. The domain name is MyDomain. The user name is MyUser. The following table lists the service pack level, the version number, and the corresponding name of the root node:
Service pack level Version number Name of the root node
----------------------------------------------------------------------------------
The release version of SSIS 9.0.1399 MyServerName (Integration Services
9.0.1399 - MyDomain\MyUser)
SSIS with Service Pack 1 (SP1) 9.0.2047 MyServerName (Integration Services
9.0.2047 - MyDomain\MyUser)
SSIS with Service Pack 2 (SP2) 9.0.3042 MyServerName (Integration Services
9.0.3042 - MyDomain\MyUser)
Method 2: Use the MsDtsSrvr.exe file
Note The MsDtsSrvr.exe file is the SSIS service executable file.
1.
Open the following folder:
%programfiles%\Microsoft SQL Server\90\DTS\Binn\
2.
Right-click the MsDtsSrvr.exe file, and then click Properties.
3.
In the MsDtsSrvr.exe Properties dialog box, click the Version tab.
4.
On the Version tab, view the text after the File version label. The text is the SSIS version number.
The version number represents the SSIS service pack level or the SSIS hotfix level.The following table lists the SSIS service pack level and the corresponding version number:
Service pack level Text after the "File version" label
-------------------------------------------------------------------------------
The release version of SSIS 9.0.1399.0
SSIS with SP1 9.0.2047.0
SSIS with SP2 9.0.3042.0
If the server is running a 64-bit version of SSIS, the MsDtsSrvr.exe file is a 64-bit file. No 32-bit version of the MsDtsSrvr.exe file exists on the server. However, other SSIS executable files and SSIS DLLs have a corresponding 32-bit version file and a 64-bit version file. For example, on a server that is running a 64-bit version of SSIS, the DTExec.exe file has a 32-bit version file and a 64-bit version file.
Method 3: Use the registry
1.
Click Start, click Run, type Regedit, and then click OK.
2.
In Registry Editor, locate the following registry subkey:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\DTS\Setup
Note If you are running a 32-bit version of SSIS on a 64-bit version of the Microsoft Windows operating system, locate the following registry subkey:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\90\DTS\Setup
3.
In the Data column, view the value of the Version registry entry and the value of the Patchlevel registry entry.The value of the Version registry entry represents the SSIS service pack level. The value of the Patchlevel registry entry represents the SSIS hotfix level.
The following table lists the SSIS service pack level and the corresponding value of the Version registry entry.
Service pack level Value of the Version registry entry
-------------------------------------------------------------------------
The release version of SSIS 9.00.1399.06
SSIS with SP1 9.1.2047.00
SSIS with SP2 9.2.3042.00
Method 4: Use the Application log
1.
Click Start, click Run, type Eventvwr, and then click OK.
2.
In the Event Viewer window, click Application.
3.
Locate the event that meets the following requirements:
•
The value in the Event column is 257.
•
The value in the Source column is SQLISService.
•
The event is the most recent event of a series of similar events. To determine the most recent event, view the value in the Data column and the value in the Time column.
4.
Double-click the event that you located in step 3.
5.
In the Event Properties dialog box, view the Server version Version_Num text under Description.Version_Num represents the SSIS service pack level or the SSIS hotfix level.
The following table lists the SSIS service pack level and the corresponding Version_Num value.
Service pack level Version_Num in the "Server version Version_Num"
----------------------------------------------------------------------------------
The release version of SSIS 9.00.1399.00
SSIS with SP1 9.00.2047.00
SSIS with SP2 9.00.3042.00
Notes
•
SSIS does not support multiple instances. You can only install one instance of SSIS on a computer.
•
Method 1 is the easiest method to determine the version of SSIS.
•
If you cannot connect to the SSIS server, use Method 2, Method 3, or Method 4.
Method 1: Use Object Explorer
1.
Open SQL Server Management Studio.
2.
In the Connect to Server dialog box, click Integration Services in the Server type list.
3.
In the Server name box, specify the SSIS server name, and then click Connect.
4.
In Object Explorer, view the version number in the name of the root node.
The name of the root node appears in the following syntax:
Server_Name (Integration Services Version_No – Domain_Name\User_Name)
Notes
•
Server_Name represents the SSIS server name. Usually, the SSIS server name is the network name of the computer that is running SSIS.
•
Version_No respents the SSIS service pack level or the SSIS hotfix level.
•
User_Name represents the user name that you use to connect to SSIS.
•
Domain_Name represents the name of the domain that the user belongs to. If the user does not belong to a domain, Domain_Name represents the name of the computer that is running SSIS.
For example, assume the SSIS server name is MyServerName. The domain name is MyDomain. The user name is MyUser. The following table lists the service pack level, the version number, and the corresponding name of the root node:
Service pack level Version number Name of the root node
----------------------------------------------------------------------------------
The release version of SSIS 9.0.1399 MyServerName (Integration Services
9.0.1399 - MyDomain\MyUser)
SSIS with Service Pack 1 (SP1) 9.0.2047 MyServerName (Integration Services
9.0.2047 - MyDomain\MyUser)
SSIS with Service Pack 2 (SP2) 9.0.3042 MyServerName (Integration Services
9.0.3042 - MyDomain\MyUser)
Method 2: Use the MsDtsSrvr.exe file
Note The MsDtsSrvr.exe file is the SSIS service executable file.
1.
Open the following folder:
%programfiles%\Microsoft SQL Server\90\DTS\Binn\
2.
Right-click the MsDtsSrvr.exe file, and then click Properties.
3.
In the MsDtsSrvr.exe Properties dialog box, click the Version tab.
4.
On the Version tab, view the text after the File version label. The text is the SSIS version number.
The version number represents the SSIS service pack level or the SSIS hotfix level.The following table lists the SSIS service pack level and the corresponding version number:
Service pack level Text after the "File version" label
-------------------------------------------------------------------------------
The release version of SSIS 9.0.1399.0
SSIS with SP1 9.0.2047.0
SSIS with SP2 9.0.3042.0
If the server is running a 64-bit version of SSIS, the MsDtsSrvr.exe file is a 64-bit file. No 32-bit version of the MsDtsSrvr.exe file exists on the server. However, other SSIS executable files and SSIS DLLs have a corresponding 32-bit version file and a 64-bit version file. For example, on a server that is running a 64-bit version of SSIS, the DTExec.exe file has a 32-bit version file and a 64-bit version file.
Method 3: Use the registry
1.
Click Start, click Run, type Regedit, and then click OK.
2.
In Registry Editor, locate the following registry subkey:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\DTS\Setup
Note If you are running a 32-bit version of SSIS on a 64-bit version of the Microsoft Windows operating system, locate the following registry subkey:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\90\DTS\Setup
3.
In the Data column, view the value of the Version registry entry and the value of the Patchlevel registry entry.The value of the Version registry entry represents the SSIS service pack level. The value of the Patchlevel registry entry represents the SSIS hotfix level.
The following table lists the SSIS service pack level and the corresponding value of the Version registry entry.
Service pack level Value of the Version registry entry
-------------------------------------------------------------------------
The release version of SSIS 9.00.1399.06
SSIS with SP1 9.1.2047.00
SSIS with SP2 9.2.3042.00
Method 4: Use the Application log
1.
Click Start, click Run, type Eventvwr, and then click OK.
2.
In the Event Viewer window, click Application.
3.
Locate the event that meets the following requirements:
•
The value in the Event column is 257.
•
The value in the Source column is SQLISService.
•
The event is the most recent event of a series of similar events. To determine the most recent event, view the value in the Data column and the value in the Time column.
4.
Double-click the event that you located in step 3.
5.
In the Event Properties dialog box, view the Server version Version_Num text under Description.Version_Num represents the SSIS service pack level or the SSIS hotfix level.
The following table lists the SSIS service pack level and the corresponding Version_Num value.
Service pack level Version_Num in the "Server version Version_Num"
----------------------------------------------------------------------------------
The release version of SSIS 9.00.1399.00
SSIS with SP1 9.00.2047.00
SSIS with SP2 9.00.3042.00
Subscribe to:
Posts (Atom)