Cumulative Update – 3 for SQL Server 2014 RTM Is Now Available !


The 3rdcumulative update release for SQL Server 2014 RTM is now available for download at the Microsoft Support site. Cumulative Update 3 contains all the hotfixes released since the initial release of SQL Server 2014 RTM.

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

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

KB Article For CU3 of SQL Server 2014 RTM

Previous Cumulative Update KB Articles:

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

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

How to kill a negative SPID (like SPID -2 or -3) in SQL Server?


Today, I face issue during one database restore. I am not able to restore because “Database is in use, Not able to take exclusive lock”. I have killed all the user sessions & stop the application but still we are facing this issue.

Further going into details, I found that orphan transaction with SPID -2 is running over the database due to which restore not able to take exclusive lock on database. Rarely this scenario will arise when most likely you see this negative SPID (most likely SPID -2) is blocking other transaction causing issues. If you try to kill it using normal KILL command, it will fail with below error:

Msg 6101, Level 16, State 1, Line 1

Process ID <SPID Number> is not a valid process ID. Choose a number between 1 and 2048

This is because of an orphaned distributed transaction ID. You need to find UnitOfWork (UOW) number to kill these kind of session ID. UOW is 32 digit number like “SE4A3415-17F5-44G3-8819-Z12555ABC7E9”.

Step 1: Find the UOW Number

select req_transactionUOW from master..syslockinfo where req_spid = <SPID Number>

Step 2: Copy the UOW number from Step one

KILL ‘<UOW Number>’

This will kill the negative SPID to resolve the issue.

Notes :

1. If you find multiple UOW numbers for single SPID, kill them one by one.

2. In case, these steps failed to resolve your issue or you did not find any UOW then restart SQL Service and MS DTC Service.

3. If you are facing these issues very frequently, then engage your application team for code analyze.

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

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

SQL Server 2005 || Database backup failed because Full Text catalog files are OFFLINE


Error : Database backup is getting fail with below reason.

The backup of full-text catalog ‘FullTextCatalog’ is not permitted because it is not online. Check errorlog file for the reason that full-text catalog becomes offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.

Cause : This behavior occurs because a full-text catalog in the database is not online. To perform a full backup, SQL Server 2005 requires all the database files and full-text catalogs in the database to be online.

Possible Reason : The database is attached from somewhere. However, you specify the incorrect location for the full-text catalog folder during the attachment.

Solution 1 (If FTS files are available but SQL server showing offline):

1. Locate the folder that contains the files for the problematic full-text catalog.

2. Run the ALTER DATABASE statement. Specify in the statement the correct location for the full-text catalog.

3. Rebuild the problematic full-text catalog in the database.

4. Perform a full backup of the database in SQL Server 2005 again.

Solution 2 (If FTS files are not available):

1. Detach database & attach again with all MDF, NDF, LDF & FTS files.

Web Reference :

1) http://support.microsoft.com/kb/923355

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

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

Renaming or Changing SQL Server Cluster Instance


Renaming or Changing SQL Server cluster instance process is bit different from renaming a stand-alone instance.

Refer : Rename or Change SQL Server Standalone Default Instance http://mssqlfun.com/2014/06/30/rename-or-change-sql-server-standalone-default-instance/)

Refer : Rename or Change SQL Server Standalone Named Instance (http://mssqlfun.com/2014/07/29/rename-or-change-sql-server-standalone-named-instance/)

The name of the virtual server is always the same as the name of the SQL Network Name (the SQL Virtual Server Network Name). You can change the name of the virtual server, you cannot change the instance name like in the case of standalone named instance. You can change a virtual SQL server named SQLServerinstance1 to some other name, such as SQLServerNewinstance1, but the instance part of the name, instance1, will remain unchanged.

Rename a virtual server :-

1. Using Cluster Administrator, change the SQL Network Name or SQL Server DNS Name to the new name.

2. Take the network name resource offline. This takes the SQL Server resource and other dependent resources offline as well.

3. Bring the SQL Server resource back online.

Verify the Renaming Operation :-

1) Using @@ServerName

2) Using sys.servers

Minimize network propagation delay :-

ipconfig /flushdns

ipconfig /registerdns

nbtstat –RR

You are done ! You should ask your application teams to reconnect with new Name or if they are using IP address then no changes required.

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

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

Rename or Change SQL Server Standalone Named Instance


Today, We will go through with the steps of renaming or changing SQL Server Standalone Named Instance.

Renaming or Changing SQL Server Standalone Named Instance is almost same as SQL Server Standalone Default Instance. You can change a SQL server named SQLServerinstance1 to some other name, such as SQLServerNewinstance1, but the instance portion of the name, instance1, will remain unchanged.

Refer : Rename or Change SQL Server Standalone Default Instance ()

Step 1 : Check Current Instance & host name

Current Host Name : admin-0783e4076

Current SQL Instance Name : admin-0783e4076SQL2K5_2

Select @@ServerName ServerName, Host_name() HostName

Step 2 : Rename Host name & reboot the server

Step 3 : Try to connect with SQL server ADMIN-0783E4076SQL2K5_2 & you will face below error because no instance of server name [ADMIN-0783E4076SQL2K5_2] exists as server name got changed

Cannot connect to ADMIN-0783E4076SQL2K5_2.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

Step 4 : Try to connect with “.SQL2K5_2” And Re Check Instance & host name and Drop old server name from server list of SQL

Exec sp_dropserver [ADMIN-0783E4076SQL2K5_2]

Step 5 : Add new server name as default server in server list of SQL

Exec sp_addserver [Win2K3_1SQL2K5_2],local

You can check default & other server ddetails from sys.servers.

SELECT * FROM SYS.SERVERS

Step 6 : Restart SQL Services

Step 7 : Try to connect with [Win2K3_1SQL2K5_2] And Re Check Instance & host name

You are done !

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

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

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


The 1st cumulative update release for SQL Server 2012 Service Pack 2 is now available for download at the Microsoft Support site. Cumulative Update 1 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 CU1 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 CU1 of SQL Server 2012 SP2

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

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

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

SQL Server 2012 with SP2 Slipstream ISO images fixed


Microsoft fixed SQL Server 2012 with SP2 Slipstream ISO images do not install SP2 issue. The new ISOs have been posted on their respective release channels (MSDN…) and are now available.

Refer : http://support.microsoft.com/kb/2984316

Source : http://blogs.msdn.com/b/sqlreleaseservices/archive/2014/07/18/sql-server-2012-with-sp2-slipstream-iso-images-fixed.aspx

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

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

categories

Follow

Get every new post delivered to your Inbox.

Join 210 other followers