Home » SQL Server

Category Archives: SQL Server

SQL Server 20082008 R22012 || Remove Node from failover cluster

Steps to remove node from failover cluster for SQL Server 2008 onwards :-

Note :-

a) Same process will be used to remove node from failover cluster

b) Same process will be used when you left with only one node, this will clear cluster group as well

c) Old process of uninstallation from Add and Remove program in Control Panel will not work

1) Open SQL Server installation Center > Go to Maintenance Option from Left > Click on “Remove Node from a SQL Server failover cluster” from right

2) Click on Run to start uninstallation process

3) Click “OK”. In case of any failure, you need to fix that before uninstallation

4) Click Next

5) Select Instance Name & click Next

6) Click Remove to start uninstallation

7) Uninstallation in progress

8) Uninstallation successfully completed.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Originally posted 2014-12-02 13:20:36. Republished by Blog Post Promoter

MSDB log file grown large – SLEEP_MSDBSTARTUP waittype

Issue: MSDB database log file is getting full very rapidly and became very huge. Currently, T-Log file is of 5GB whereas Data file is only 1GB for MSDB on server.

Findings: We found system owned open transaction in MSDB which is causing issue.

Transaction information for database ‘msdb’.

Oldest active transaction:

SPID (server process ID): 19s

UID (user ID) : -1

Name : CTraceEvDataQueue

LSN : (12968:15110:1)

Start time : May 7 2016 12:33:14:507PM

SID : 0x0

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

When we try to find SPID 19 details from sys.sysprocesses, we found that session is causing SLEEP_MSDBSTARTUP waittype.

SLEEP_MSDBSTARTUP waittype Occurs when SQL Trace waits for the msdb database to complete startup. This is very rare watitype which we come across in our day to day production support.

Resolution: As we all know, we cannot kill SYSTEM session IDs, We need to restart SQL Services to get rid out of this problematic session.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Cumulative Update – 8 for SQL Server 2008 R2 Service Pack 2 Is Now Available !

The 8th cumulative update release for SQL Server 2008 R2 Service Pack 2 is now available for download at the Microsoft Support site. Cumulative Update 8 contains all the hotfixes released since the initial release of SQL Server 2008 R2 SP2.

Those who are facing severe issues with their environment, they can plan to test CU8 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP3 final release to deploy on your production environment, to have consolidate build.

KB Article For CU5 of SQL Server 2008 R2 SP2

· CU#8 KB Article: http://support.microsoft.com/kb/2871401

Previous Cumulative Update KB Articles:

· CU#7 KB Article: http://support.microsoft.com/kb/2844090

· CU#6 KB Article: http://support.microsoft.com/kb/2830140

· CU#5 KB Article: http://support.microsoft.com/kb/2797460

· CU#4 KB Article: http://support.microsoft.com/kb/2777358

· CU#3 KB Article: http://support.microsoft.com/kb/2754552

· CU#2 KB Article: http://support.microsoft.com/kb/2740411

· CU#1 KB Article: http://support.microsoft.com/kb/2720425

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Originally posted 2013-09-03 19:34:21. Republished by Blog Post Promoter

MDW Data Collector – Collection Set jobs not working

ERROR :

Error Message 1 :

Executed as user: Domain\UserName. SSIS error. Component name: DFT – Upload collection snapshot, Code: -1071636372, Subcomponent: RFS – Read Current Upload Data [1], Description: String too long. The adapter read a string that was 50331648 bytes long, and expected a string no longer than 129 bytes, at offset 12901. This could indicate a damaged input file. The file shows a string length that is too large for the buffer column. . SSIS error. Component name: DFT – Upload collection snapshot, Code: -1073450952, Subcomponent: SSIS.Pipeline, Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "RFS – Read Current Upload Data" (1) returned error code 0x80004005. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. .The master package exited with error, previous error messages should explain the cause. Process Exit Code 5.

Error Message 2 :

Executed as user: : Domain\UserName. SSIS error. Component name: DFT – Collect Query 0, Code: -1071636383, Subcomponent: Raw File Destination [57], Description: The file had bad version and flags information. The file is damaged or not a SSIS-produced raw data file. . SSIS error. Component name: DFT – Collect Query 0, Code: -1071636383, Subcomponent: Raw File Destination [48], Description: The file had bad version and flags information. The file is damaged or not a SSIS-produced raw data file. .SSIS error. Component name: DFT – Collect Query 0, Code: -1073450982, Subcomponent: SSIS.Pipeline, Description: component "Raw File Destination" (57) failed the pre-execute phase and returned error code 0xC0202061. . SSIS error. Component name: DFT – Collect Query 0, Code: -1073450982, Subcomponent: SSIS.Pipeline, Description: component "Raw File Destination" (48) failed the pre-execute phase and returned error code 0xC0202061. .The master package exited with error, previous error messages should explain the cause. Process Exit Code 5. The step failed.

REASON : Cache files is having issue. When Collection jobs trying to upload cache files, job is getting failed.

SOLUTION :

1. Start SQL Server Management Studio, and connect to the instance of SQL Server where the error occurs.

2. Expand the Management folder, right-click Data Collection, and select Properties.

3. Cache directory displayedis the location of the Data Collector cache files. Go to step 5.

4. If a directory is not displayed for Cache directory, the default cache directory is the local temporary directory of the account that executes the collection set. This account may be the SQL Server Agent service account. For example, on Windows Server 2008, if the collection set was executed by an account that is named "SQLSERVICEACCOUNT," this account’s temporary directory is located in a path that resembles the following: C:\Users\ SQLSERVICEACCOUNT\AppData\Local\Temp.

5. Find all files that have a *.CACHE file name extension, and move the files to a different directory.

6. Now wait for next run of collection set jobs.

Reference Article : https://support.microsoft.com/en-us/kb/2019126

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Implementation of Data Compression – SQL Server

Data Compression

Data Compression is feature of Microsoft SQL Server to reduce the size of table on the basis of Duplicates, Null & Zeroes. It’s a process of reducing size of database & its objects by increasing CPU cycle and reducing I/O effort.

Types of Database Compression

· Row Compression

· Page Compression

You can download Data Compression SQL Server white Paper – https://mssqlfun.files.wordpress.com/2015/10/data-compression-sql-server-white-paper1.pdf

Implementation of Data Compression

Row Compression Page Compression
T-SQL Command

ALTER TABLE <TABLE_NAME> WITH (DATA_COMPRESSION=ROW) ALTER TABLE <TABLE_NAME> WITH (DATA_COMPRESSION=PAGE)
SSMS
Step 1 – Open Data Compression wizard. Right Click on Table > Storage > Manage Compression

Step 2 – Select Compression Type & you can click on calculate to check the impact

You can use below system store procedure to estimate the compression results
EXEC sp_estimate_data_compression_savings ‘Schema’,’Table_Name’,Null,Null,’Type_of_Compression’
Step 3 – Select Option if you want to run it immediately or later by generating script

Step 4 – Final Configuration window before you give final go ahead

Step 5 – Compression implemented successfully

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

MDW Data Collector – Collection Set jobs not working

ERROR :

Error Message 1 :

Executed as user: Domain\UserName. SSIS error. Component name: DFT – Upload collection snapshot, Code: -1071636372, Subcomponent: RFS – Read Current Upload Data [1], Description: String too long. The adapter read a string that was 50331648 bytes long, and expected a string no longer than 129 bytes, at offset 12901. This could indicate a damaged input file. The file shows a string length that is too large for the buffer column. . SSIS error. Component name: DFT – Upload collection snapshot, Code: -1073450952, Subcomponent: SSIS.Pipeline, Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "RFS – Read Current Upload Data" (1) returned error code 0x80004005. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. .The master package exited with error, previous error messages should explain the cause. Process Exit Code 5.

Error Message 2 :

Executed as user: : Domain\UserName. SSIS error. Component name: DFT – Collect Query 0, Code: -1071636383, Subcomponent: Raw File Destination [57], Description: The file had bad version and flags information. The file is damaged or not a SSIS-produced raw data file. . SSIS error. Component name: DFT – Collect Query 0, Code: -1071636383, Subcomponent: Raw File Destination [48], Description: The file had bad version and flags information. The file is damaged or not a SSIS-produced raw data file. .SSIS error. Component name: DFT – Collect Query 0, Code: -1073450982, Subcomponent: SSIS.Pipeline, Description: component "Raw File Destination" (57) failed the pre-execute phase and returned error code 0xC0202061. . SSIS error. Component name: DFT – Collect Query 0, Code: -1073450982, Subcomponent: SSIS.Pipeline, Description: component "Raw File Destination" (48) failed the pre-execute phase and returned error code 0xC0202061. .The master package exited with error, previous error messages should explain the cause. Process Exit Code 5. The step failed.

REASON : Cache files is having issue. When Collection jobs trying to upload cache files, job is getting failed.

SOLUTION :

1. Start SQL Server Management Studio, and connect to the instance of SQL Server where the error occurs.

2. Expand the Management folder, right-click Data Collection, and select Properties.

3. Cache directory displayedis the location of the Data Collector cache files. Go to step 5.

4. If a directory is not displayed for Cache directory, the default cache directory is the local temporary directory of the account that executes the collection set. This account may be the SQL Server Agent service account. For example, on Windows Server 2008, if the collection set was executed by an account that is named "SQLSERVICEACCOUNT," this account’s temporary directory is located in a path that resembles the following: C:\Users\ SQLSERVICEACCOUNT\AppData\Local\Temp.

5. Find all files that have a *.CACHE file name extension, and move the files to a different directory.

6. Now wait for next run of collection set jobs.

Reference Article : https://support.microsoft.com/en-us/kb/2019126

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

500+ Facebook Like

Thanks every one for loving me, joining me & providing you extreme support.

I am very happy to share that we reach to 500+ milestone.

Please keep supporting us & liking us.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Strange Issue of waittype – PREEMPTIVE_OS_GETPROCADDRESS

ISSUE : Today, My Friend is facing issue with SQL Server error logs on one of my production server. SQL Server is not able to read SQL server error log files. We have checked that SQL Server Error log files are accessible on file system.

1) When we are running SP_readerrorlog, XP_readerrorlogs or XP_enumerrorlogs, query keeps running without showing results.

2) When We try to browse error logs from SQL Server GUI, the wizard is also hanging.

FINDINGS :

1) We found all sessions running & stuck on waittype “PREEMPTIVE_OS_GETPROCADDRESS”

PREEMPTIVE_OS_GETPROCADDRESS is a new wait type that was introduced in SQL Server 2008 to track time taken by GetProcAddress to load the entrypoint in the DLL when Extended Stored Procedure calls are made by the SQL Server instance.

2) We check what is actually SP_readerrorlog, XP_readerrorlogs or XP_enumerrorlogs trying to execute. As we all know, all this 3 store procedures are extended store procedure. Moreover, running same xpstart.dll

3) Other extended store procedures are working fine.

RESOLUTION :We have rebooted the server so that call to xpstar.dll will work properly.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Last Cumulative Update – 16 for SQL Server 2012 Service Pack 1 Is Now Available !

The 16th cumulative update release for SQL Server 2012 Service Pack 1 is now available for download at the Microsoft Support site. Cumulative Update 16 contains all the hotfixes released since the initial release of SQL Server 2012 SP1.

Those who are facing severe issues with their environment, they can plan to test CU16 in test environment & then move to Production after satisfactory results.

Although, I prefer to have SP2 with latest CU in your environment.

NOTE: This is the final Cumulative Update for SQL Server 2012 Service Pack 1. SQL Server 2012 SP1 exits service pack support on July 14, 2015.

KB Article For CU16 of SQL Server 2012 SP1

Previous Cumulative Update KB Articles:

· CU#12 KB Article: http://support.microsoft.com/kb/2991533

· CU#11 KB Article: http://support.microsoft.com/kb/2975396

· CU#10 KB Article: http://support.microsoft.com/kb/2954099

· CU#9 KB Article: http://support.microsoft.com/kb/2931078

· CU#8 KB Article: http://support.microsoft.com/kb/2917531

· CU#7 KB Article: http://support.microsoft.com/kb/2894115

· CU#6 KB Article: http://support.microsoft.com/kb/2874879

· CU#5 KB Article: http://support.microsoft.com/kb/2861107

· CU#4 KB Article: http://support.microsoft.com/kb/2833645

· CU#3 KB Article: http://support.microsoft.com/kb/2812412

· CU#2 KB Article: http://support.microsoft.com/kb/2790947

· CU#1 KB Article: http://support.microsoft.com/kb/2765331

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Cumulative Update – 6 for SQL Server 2012 Service Pack 2 Is Now Available !

The 6th cumulative update release for SQL Server 2012 Service Pack 2 is now available for download at the Microsoft Support site. Cumulative Update 6 contains all the hotfixes released since the initial release of SQL Server 2012 SP2.

Those who are facing severe issues with their environment, they can plan to test CU6 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP3 final release to deploy on your production environment, to have consolidate build.

KB Article For CU6 of SQL Server 2012 SP2

Previous Cumulative Update KB Articles:

Download Link of SQL Server 2012 SP2 :

SQL Server 2012 SP2 : http://www.microsoft.com/en-us/download/details.aspx?id=43340

SQL Server 2012 SP2 EXPRESS : http://www.microsoft.com/en-us/download/details.aspx?id=43351

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

%d bloggers like this: