Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database.

The backup job of SQL Server 2008 databases started failing with below error:

Description: Error encountered during backup. Error: [Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. An exception occurred while executing a Transact-SQL statement or batch.]

This error comes up due to several reasons and majority of the reasons are:

 There might be space issue in the drive\disk where database files reside or database. msdb database itself would be corrupt.

In our case both were the reasons, first when we had backup failure noticed there was space issue in drive, after releasing some space backups went fine. But after few days backups started failing again with same error, but this time there is no space issue and when tried to query backup related tables in msdb database it was throwing error basically few backup related tables in msdb are not accessible.

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

So we ended up restoring the msdb database with most recent available backup and that has fixed the issue. I don’t suggest rebuilding msdb  or any system database unless that’s the only option left, as this may lead to other issues. So be sure before you go for this option.

Thanks!!

How to automate post reboot verification of SQL Server.

Recently we have been facing an issue in one of our test servers. It is having new issue every time server gets rebooted, sometimes few databases go offline or would be stuck in middle of restore or SQL agent won’t come up and etc. Being a test server several people do rigorous testing’s on it and can’t blame anyone if they have modified any option for their testing purposes but at the end of the day everyone needs all the databases of the so called test server to be healthy and available all the times. So I have been searching for a way to automate the verification of stuff we need to in SQL Server every time it gets re-started.

Then I learnt that there is an option in SQL Server to execute stored procedure every time the SQL services get re-started.

By turning on the below option we can make any user defined SP to get executed automatically every time SQL services gets re-started. I have tested this on SQL Server 2005\2008\R2\2012\2014 versions successfully.

sp_procoption @ProcName = 'UserDefinedSP',               @OptionName = 'STARTUP',               @OptionValue = 'ON'

Note: We can turn this option ON for the SP’s that are in master database only. SP cannot contain INPUT or OUTPUT parameters.

I coined below simple SP which will verify status of all the databases after every time SQL services get re-started and sends mail notification.

Same way we can make use of this to verify different things of SQL Server.

Thanks!!

How to install SQL Server 2014

From 1^st April 2014 Microsoft has made SQL Server 2014 Evaluation copy and other editions of it available, you can get information related to SQL 2014 in this link. I have downloaded the evaluation copy of it and installed. Below are the screenshots of step by step installation.

The installation is almost similar to earlier version, major difference is instead of installation wizard closing and opening multiple windows for license, global rules and then installing setup files and then actual install and etc.., now entire installation is done in a single window. Other than that in this version we can see ‘prerequisites for selected features and disk space requirements’ when we select a feature while installing, we had this in SQL Server 2012 as well. Rest all installation steps are same. 

Below is a quick walk through of the installation, as most of the steps are same as previous version I have just added the captures.

First Window after running setup.exe:

As we can see the ‘product key’, license, global rules are all on same window instead of separate.

Installation of SetUp Files:

Selection of required database features:

On right side we can see the prerequisites and space requirements for the features we select. I have selected only DB engine services.

Instance configuration same as earlier versions:

Screen shot showing installation in progress:

Installation completed successfully:

After restarting server, I have opened SSMS and it is the same as SQL Server 2012 version no major changes noticed.

How to change port number for SSAS(Analysis Service) in SQL Server 2008, 2008 R2?

Clustered Instance:

Please note that the clustered instance of Analysis service will always use the port 2383. Even though it will allow to change the port number but still it runs on 2383 port itself. SO we cannot make SSAS to use any user defined port.

Standalone Instance:

Search for the file named ‘msmdsrv.ini’ file in Config folder of installation folder. In this folder there will be a section

<port>0</port>

Here we need to replace ‘0’ ZERO with the port number we need. After changing restart the SQL services and SSAS will start using the port specified.

Thanks!!

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!!