What is Target Server Memory (KB)?

What is Target Server Memory (KB)?   To  super simplify  in conventional memory model  SQL Server calculates something like target1 and target2 pages using below formula                   Target1 = Current committed pages of SQL Server + ( Available Physical Memory – min (Total Physical Memory Pages / 20, Available Physical Memory Pages / 2))…


Transaction log for the database is growing and system SPID is holding open transaction

Transaction log for the database is growing and system SPID is holding open transaction Select log_reuse_wait_desc From Sys.databases where name=’%’ will return ‘ACTIVE_TRANSACTION’ DBCC Opentran says there is a Opentran held by system thread (Similar to SPID (server process ID): 7s and 6s in below example) Oldest active transaction: SPID (server process ID): 7s UID…


SQL Server performance degraded in 32-Bit SQL Server after adding additional RAM.

  Do you know that adding additional RAM can affect the performance of SQL Server Sometimes?   SQL Server performance degraded in 32-Bit SQL Server after I added additional RAM or Do you see SQL Server memory errors after adding RAM ?   Following SQL Server Memory errors are logged in SQL error log after…


Copy database wizard or replication setup might fail due to broken dependency

Copy database wizard would fail with below error while creating views or user defined functions if the dependency lists of objects is  broken. Error: failed with the following error: "Invalid object name ‘dbo. .". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly….


SQL Server Agent is taking long time to start

  SQL Server Agent might take long time to start because of slow communications with Certificate Authorities.   If you enable verbose logging for SQL Server agent (-v) and look at the SQL Server agent log you will notice that ‘ANALYSISQUERY’ subsystem has taken long time to long 2012-02-15 15:42:42 – ? [124] Subsystem ‘QueueReader’…


SQL-Server resource fails to come online IS Alive check fails

SQL-Server resource fails to come online with Error: [sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = 35; message = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is…


Linked server connection fails with “An error occurred during decryption”

  Problem Description Linked server connection fails with below error { Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc) —————————— ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) —————————— An error occurred during decryption. (Microsoft SQL Server, Error: 15466) Msg 15593, Level 16, State 1, Line 1 An error occurred…


Service pack ,Hotfix and CU installation for SQL Server 2005 might fail with “Unable to install Windows Installer MSI file“

Installation of SQL Server 2005 Service pack ,Hotfix and CU  would fail fails with “Unable to install Windows Installer MSI file“ and message box “ A recently applied update failed to install” You will find below errors in setup logs. Summary.txt: Product Installation Status Product                   : Setup Support Files Product Version (Previous): Product Version (Final)  …


How to move the LOB data from one file group to other?

  We do not have a direct way to move the LOB data from one file group to other. Using ALTER TABLE and/or CREATE INDEX to support moving LOB data is unavailable till current version of  SQL (SQL Server 2008).   Only way to move the LOB data is to 1. create new table in…


Runtime error: ActiveX component can’t create object: ‘SQLDMO.SQLServer’

  When you use SQLDMO ??? Install SQL Server2005 Backward compatibility http://download.microsoft.com/download/3/1/6/316FADB2-E703-4351-8E9C-E0B36D9D697E/SQLServer2005_BC.msi Also remember if you have installed 32-Bit SQL Server on 64-Bit you have to use Cscript.exe from SysWOW64

3