Home » Articles posted by rohitmssqlfun

Author Archives: rohitmssqlfun

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


The 12th cumulative update release for SQL Server 2012 Service Pack 1 is now available for download at the Microsoft Support site. Cumulative Update 12 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 CU12 in test environment & then move to Production after satisfactory results.

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

KB Article For CU12 of SQL Server 2012 SP1

Previous Cumulative Update KB Articles:

· 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 – 2 for SQL Server 2012 Service Pack 2 Is Now Available !


The 2ndcumulative update release for SQL Server 2012 Service Pack 2 is now available for download at the Microsoft Support site. Cumulative Update 2 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 CU2 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 CU2 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

Creation & Deletion of Database Snapshot by SQL Agent Job


Some days back, I received one query on my facebook page that user want to create Database Snapshot frequently & want to delete the oldest with same frequency. Quite interesting scenario. I did not go in deep why he has such requirement but I am clear that It can be done very easily.

So let’s do it. I have setup 2 scripts one for creation of snapshot & another is for deletion of snapshot. We can schedule a SQL agent jobs with 2 steps using these scripts.

Step 1 :-

DECLARE @CREATE_SS VARCHAR(MAX)

DECLARE @DT VARCHAR(100)

SET @DT = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),’-‘,’_’),’ ‘,’_’),':’,’_’)

SET @CREATE_SS =

‘CREATE DATABASE TEST_’+@DT+’ ON

( NAME = TEST, FILENAME =

”C:\TEST_’+@DT+’.SS” )

AS SNAPSHOT OF TEST’

EXEC (@CREATE_SS)

Step 2 :-

IF(SELECT COUNT(*) FROM SYS.DATABASES WHERE SOURCE_DATABASE_ID = DB_ID(‘TEST’))>1

BEGIN

DECLARE @DROP_SS VARCHAR(MAX)

DECLARE @SS_NAME VARCHAR(100)

SELECT TOP 1 @SS_NAME = NAME FROM SYS.DATABASES WHERE SOURCE_DATABASE_ID = DB_ID(‘TEST’)

ORDER BY CREATE_DATE ASC

SET @DROP_SS = ‘DROP DATABASE ‘+ @SS_NAME

EXEC (@DROP_SS)

END

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

How to Start SQL Server in Single User Mode ?


Sometime, We caught in a situation where we need to run SQL server in single user mode. Issues like Master DB restore, Sysadmin locked out etc.

Steps to start SQL Server in Single User Mode :-

1) Stop SQL Server Services

2) Add [-m] startup parameter in SQL Services from configuration manager

Parameter [-m] Starts SQL Server instance in single-user mode. SQL Server in single-user mode can connect with single user only and did not start CHECKPOINT process. Starting SQL Server in single-user mode enables any member of the computer’s local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role.

From SQL Server 2005 to SQL Server 2008 R2

From SQL Server 2012 onwards

3) Start SQL Services & SQL server will come online in single user mode. You can connect using SQLCMD & continue with desired operation

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

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

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/)

Follow

Get every new post delivered to your Inbox.

Join 217 other followers