Upgrade from SQL 2005 to SQL 2012 fails with error “Service 'SQLSERVERAGENT' start request failed.”

I was working on a 2005 to 2012 upgrade issue and came across an interesting issue which I thought of sharing with you guys.  Whilst running an upgrade from 2005 SP4 to 2012 RTM (and/or slipstreamed copy) it failed half way through, giving the error:   “There was an error attempting to remove the configuration…

4

SQL Server Backup Simulator

We at SQL Server support team, continue to invest our time in writing tools and utilities which can aid you in troubleshooting SQL Server issues. SQL Server Backup Simulator is one such tool which will help you while troubleshooting issues in taking backup of SQL Server using 3rd party utilities like IBM Tivoli, Symantec BackupExec,…


Troubleshooting tempdb growth due to Version Store usage

Troubleshooting tempdb growth due to Version Store usage   Recently I worked on an issue where the tempdb database version store cleanup was not occurring. I created a small repro to demonstrate the behavior. My tempdb has been showing the version store usage below for the past 3 days: sys.dm_db_file_space_used   runtime                         version_store_kb     2010-10-08 10:28:52.740…


Unable to open excel files using a cscript in SQL Server Jobs

·         Disclaimer : Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment. ·         The following article discusses the complications that…


How to restore database backup (Full and Transaction logs) after regenerating Database Encryption Key (KEY) with a new certificate having no impact on Log sequence number (LSN) chain.

It’s a very common scenario where customers have their database encrypted and hold multiple certificates on their SQL Server environment. In this blog I would be sharing some good information on steps to restore full backup that was taken at the time when the database was not encrypted and applying the Transaction logs backups which was…


sp_help_log_shipping_monitor may return the following error after role reversal in SQL 2000/2005 : Violation of PRIMARY KEY constraint <constraint name>. Cannot insert duplicate key in object 'dbo.#log_shipping_monitor'

Problem Description =============== ·         After role reversal in a log shipping setup, the stored procedure sp_help_log_shipping_monitor may return the following error OR we may find the error in the log shipping report in SQL Server Management Studio. Msg 2627, Level 14, State 1, Procedure sp_help_log_shipping_monitor, Line 148 Violation of PRIMARY KEY constraint ‘PK__#log_shipping_mo__15502E78’. Cannot insert…


DB Maintenance: Rebuild Index Task always fails on OperationsManagerDW [SCOM database]

Problem Description ======================================= We encountered a situation where we were trying to execute a maintenance plan to rebuild the index of OperationsManagerDW database. However, the maintenance plan fails to complete.   In the Maintenance Plan log, the following paragraph was found: Rebuild Index Task (<instance_name>) Rebuild index on Local server connection Databases that have a…


INF: A missing exception handle can cause blocking and eventual application hang.

BEHAVIOR/SYMPTOMS: We recently had a customer come to us seeking help on a performance issue that he was experiencing. Understood, that there’s a web-application written in ASP.Net and was using SQL Server at the backend. During the peak usage hours, the application users were experiencing query below timeout errors and sometimes the application even seemed…