The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. (Microsoft SQL Server, Error: 1204)

This error has made another day of my typical DBA life. We have a restore job which takes backup of production DB and restores it to test server. When restore to test server was happening, the test server was rebooted by windows team for finishing some critical windows patch deployment. Server came up fine after reboot, I have verified and noticed the DB was also ‘Online’, which was in mid of restore prior to reboot. But I started receiving complaints from users that applications are not accessible.

when I tried to open the database through SSMS I received below error:

The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. (Microsoft SQL Server, Error: 1204)

Below are the options I tried to fix the issue, I’m listing out everything I tried so that anyone of the options below might help someone in fixing their issue:

I verified applications relying on the DB and they are throwing error related to permission issue. So I tried granting permission to the users for which its giving permission issue but the grant permission commands never completed and has thrown same above error.

I ran sp_who2 to find out what are the active SPID’s currently in the server and found no user SPID’s for this DB, but there was a SPID 20 which was a Background process and it was running the command ‘DB STARTUP’ on the same DB which was having issue, the CPUTime and I/O is quite high for this SPID. So this confirmed that the DB STARTUP command got stuck due to the reboot and not released.

As it was a BackGround SPID we cannot KILL it... DB was showing ONLINE through SSMS and even sys.databases table also shows DB is ONLINE, but data was not accessible… I tried running the restore command manually with RESTART option but it didn’t helped. 

Note: 'RESTART' option in restore command can be used when we have a failure while restore is happening and if we want to start the restore from same point where restore failed.

Eg: Restore database dbname from disk = ‘C:\temp\test.bak’ with RESTART;

Okie I tried brining the DB to SINGLE USER mode with below command but it has thrown error ‘Database is in a state where you cannot put it in SINGLE USER mode’

ALTER DATABASE dbname

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE;

Tried taking the DB OFFLINE but still that didn’t worked:

ALTER DATABASE dbname  SET OFFLINE WITH ROLLBACK IMMEDIATE;

Tried dropping the DB, nope that didn’t worked too… for the above trails that is for dropping and taking DB offline, it was throwing error ‘Cannot perform this operation as database is in use’. When verified the same SPID 20 which was running the command ‘DB STARTUP’ was blocking these commands.

Now when I ran sp_who2 again it started throwing below error for query as well:

Msg 1204, Level 19, State 4, Procedure sp_who2, Line 89

The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

When I refreshed the GUI, I got a pop-up window showing same error, now SQL Agent also not visible in SSMS. So I decided to restart the SQL services, I took all SQL related services down and started them back, I noticed now the DB was in ‘In Recovery’ state so I was happy it started recovering, when checked in SQL logs its showing the recovery status of the DB 20%,60%,90% and 100% completed. After 100% there was an entry in log stating DB is online. Hurray!!! DB is back…. Nope not so easy when I tried accessing the DB same above errors L

Okie I tried restore command with ‘recovery’ option, nope this didn’t worked tooo…. it has thrown same error ‘Database is in a state where you cannot put it in SINGLE USER mode’

Eg: restore database dbname with recovery

Note: This recovery option will bring the DB online if the DB was left in ‘In Restoring’ state.

Now I started searching in google and almost all the articles(at least what I found) says they received this error while running a SP or bulk query or during any heavy transaction, but none of those were my case… NOW WHAT!!!

As a last option in order to get rid of the DB, I took SQL services down and renamed its corresponding mdf/ldf files and started the services. In normal case DB will go to SUSPECT state when the underlying mdf/ldf files are not available but NO in this case it didn’t happened, even without mdf/ldf files also the DB status was showing as ONLINE through GUI and in sys.databases table as well(J it’s a miracle)…. Anyways now at least the stuck SPID 20 no longer exists and now it allowed me to drop the DB and after dropping it I deleted the mdf/ldf files of it and started the restore job again. Once restore job completed successfully the DB was really ONLINE and was accessible!!!

The resolution I used is the last option, as mine was a test server and I have a restore job which can bring back the DB, so I did that and I DO NOT RECOMMEND this to anyone. 

If anyone faced same kind of issue and have fixed it without dropping the DB please share the details.

Thanks!!

Powershell script using excel COM object is running forever through Windows task scheduler.

From past few days I was being haunted by an issue of ‘Windows Task Scheduler’.

Issue: I have a PowerShell script that gets certain SQL server related data from different servers and stores that data in an excel file and sends that excel file to group of people. This script works fine without any issue when I run either through ‘Windows Powershell’ or ‘Command Prompt’. I tried to schedule same script through ‘Windows Task Scheduler’ of Windows Server 2008 R2 server but it never completes successfully.

Either I run the windows task manually or if it starts as per schedule the task goes to ‘Running’ status and hangs in that status forever and never completes. The only thing noticed is whenever this task hangs I can see single or multiple ‘Excel.exe *32’ process in task manager, I tried killing these process but that just lead to failure of the task but script never got executed.

I have been referring to many of the articles but have not found exact reason for this behavior.

Below are the 2 solutions I found so far for this issue: (if anyone else have better explanation and solution for this issue please feel free to share)

1.    You have to create a folder (or two on a 64bit-windows):

(32Bit, always)

C:\Windows\System32\config\systemprofile\Desktop

(64Bit)

C:\Windows\SysWOW64\config\systemprofile\Desktop

This was given by user named ‘Jens Kalski’ in this link and I tried the same thing and my script started working fine. Thanks again Jens Kalsi.

2.    To check the option of Windows task ‘Run only when user is logged on’. Even with this also task will complete successfully but as it says, the task will run only if the user is logged on.

Hope this saves someone’s time!!

Thanks!!

How to downgrade SQL Server 2008 R2 from Enterprise Edition to Developer Edition?

We had few Non-Production servers which were running on Enterprise editions of SQL Server 2008/SQL Server 2008 R2. For obvious reasons we had to downgrade these editions to developer editions of respective versions. Unlike adding new features to existing instance or upgrading existing instance from 2008 to R2 or upgrading from Standard to Enterprise edition there is no direct way(atleast I have not found) to perform a downgrade. Yes that’s true there is no direct way of doing it.

Though I have learnt 2 ways of doing edition downgrade: one is the mostly followed and lengthy processes and second one is less-time and less-known method.

First Method: You can try this for your first time:

This is a complete removal of existing instance and re-installing the instance of required edition. Most of the blogs\articles\forums suggest\prefer this.

How to do it?

First of all, as a first responsibility of every DBA what you do? Yes, take backup of all databases(User DB’s and System DB’s).

1.    Make copy of current mdf\ldf files of all system databases to other location.

2.    Make note of current version of SQL server instance like Service Packs, Hot Fixes, CU# installed.

3.    Script out all the server level permissions of logins.

4.    Script out all database level permissions of users.

5.    Make note of all SQL users credentials like username and passwords.

6.    Make note of Network configuration details like TCP\IP, VIA so on and port number of the SQL instance. You can get it from SQL Configuration manager.

7.    Account details on which SQL Services are running.

8.    In addition you can make a note of mdf\ldf file locations of all databases(system and user). This is required if your databases are spread across different drives.

9.    Also make note of enterprise features that are being used. (Anyway you can’t use them anymore J)

Now go to control panel and un-install the instance let’s say I want to downgrade my ‘Tester’ instance from Enterprise to Developer. So un-install Tester instance completely from the machine using Add\Remove programs of Control panel. Once un-installation completes reboot the server, once server is back verify and make sure instance got removed successfully.

Now place the CD of Developer edition and start installing. Now give the same instance name and if you have DB’s spread across different drives you can specify those file locations while installation, use the details you took in Point 8. Also configure the account for the services during installation itself with the details you have in Point 7. Also add yourself as admin while installation. Then proceed clicking Next and also mainly make sure you are installing only the features which were being used\installed earlier. Once installation completes, install all the required Service packs, CU# and hot fixes required to bring the edition to same version number as earlier(we took in Point 2) .

After bringing the newly installed Tester instance to same version number as point 2, stop the SQL services and replace the mdf\ldf files of master and msdb databases of the new instance with the mdf\ldf files of old instance(we took copy in Point 1). Once after replacing the master and msdb mdf\ldf files, start the sql services.

Now verify the instance and you should be able to see all the jobs and logins back. Now you have to restore all the user databases to the new instance in fact you can attach them back if you have taken mdf\ldf file copies of user databases already. Configure all the logins\users and their permissions(Step 3 and 4). Then configure configuration manager same as previous instance(Step 6 and 7).

Now re-start the SQL services and verify if everything is working as expected. Also confirm now you have same instance running on Developer edition with same version number as earlier.

Note:

                                                                                                                                                                                          

·         You need to replace mdf\ldf files of ‘model’ DB as well if your model DB is customized.

·         After re-installation VERSION number should be same as previous.

Second Method:

Why I mentioned this method as less-known because I have not found any blog mentioning they tried this method. Only place I found is in the comments of this link.

The method says making the registry changes will downgrade the edition without need to re-installation. I have to downgrade anyways in non-production environment so thought of trying this and IT WORKED!

Even though it worked for me, every time I try this I make sure I have the backups of all DB’s and above mentioned backups from Step 1 to 9.

Once you have backups, go to registry.

1.    Type regedit.msc

2.    Got to SQL installation section in registry by default it will be here:
HKLM(HKEY_LOCAL_MACHINE) -> SOFTWARE -> Microsoft -> Microsoft SQL Server ->

3.    Here you have to select the instance you want to downgrade if you have multiple instances.

Expand required instance folder.

Eg: In my case it is ‘Tester’ instance so

MSSQL10_50.Tester -> Setup

4.    Click on ‘Setup’

5.    Here You need to change one by one all below entries.

AGTGroup

DigitalProductID

Edition

EditionType

ProductCode

ProductID

SQLGroup

FDHostGroup

Checksum

Take these values from any already installed Developer edition instance and replace here. Make sure from where you are taking these values have the same features installed and same version and all.

6.    Now click on ‘SQL_Engine_Core_Inst’ and change the entries here as well.

·         ProductCode

7.    Now re-start the SQL Services and verify everything is in place and working as normal.

That’s it you are done.

Note:

·         Second method requires registry changes so please take backup of registry before making any changes.

·         Most important do not try this in your sensitive servers unless you tried it once in your less critical environments and it worked.

·         Make sure you have backup of everything.

     DO NOT MAKE ANY CHANGES TO THE REGISTRY WITHOUT BEING SURE WHAT YOU ARE DOING!!

Thanks!!