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…


Sometimes Replication Monitor shows number of Undistributed Transactions as a very high number, but replication itself works properly.

An overview of the inner working of a replication monitor   We might see large number of Pending transactions on one of the Publisher in P2P replication. But when we check the Replication Monitor for second server (Subscriber) it might show a small value (which would be the actual value). You might see that the…

1

RESTORE DATABASE TASK IN SQL 2000 FROM ENTERPRISE MANAGER MAY HANG AT TIMES

PROBLEM DESCRIPTION ================   While performing Restore Database task in SQL 2000 from Enterprise Manager it may hang or appears hung at times. However you may not find any specific errors in the SQL server error log, application log and system event log.     CAUSE ====== The reason why “restore database” takes a long…


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…


Database Mail failing with “The service queue "ExternalMailQueue" is currently disabled.” message

Some of you may experience problems while using Database Mail on SQL Server 2005 or SQL Server 2008 which will not be able to send emails to the database users. The SQL Server error logs will log following errors:   The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following:  ‘The service queue “ExternalMailQueue”…