SQL Server General DBA

SQL Server Agent job syspolicy_purge_history is failing in the cluster environment.

When you install or upgrade SQL Server 2008 or R2 you will  see a new SQL Server Agent job named syspolicy_purge_history. In fact this is the only job you will see after installing a brand new SQL Server 2008 instance. This job is relevant to Policy Based Management and by default it has a daily […]

SQL Agent job getting suspended.

When you run some SQL Server agent jobs they will be suspended and you will not be permitted to re-run them. This is due the issues with the SQL Server Agent subs system information which  can be obtained from themsdb.dbo.syssubsystems table. To do this execute: SELECT * FROM msdb.dbo.syssubsystems The output for this is shown […]

How to change server name when replication is enabled.

How to change server name when replication is enabled. The following steps will change the server name when replication is enabled: 1.       Backup all the replication stored procedures. 2.       Script the replication objects 3.       Disable distributors and publications 4.       Change the server name (This will require rebooting the server) 5.       Log into SQL Server preferably […]

Queries which include DMFs return a syntax error

When I execute queries which include DMFs the queries return a syntax error. For example: SELECT — TOP 20 qs.sql_handle, qs.execution_count, qs.total_worker_time AS Total_CPU, total_CPU_inSeconds = –Converted from microseconds qs.total_worker_time/1000000, average_CPU_inSeconds = –Converted from microseconds (qs.total_worker_time/1000000) / qs.execution_count, qs.total_elapsed_time, total_elapsed_time_inSeconds = –Converted from microseconds qs.total_elapsed_time/1000000 –, st.text –, qp.query_plan from sys.dm_exec_query_stats as qs CROSS APPLY […]

Could not find stored procedure ‘dbo.sp_MSins_dboTest’

Transaction Replication is failing due to the above error and this error can be shown in the Replication monitor. If you run the following query in your publication database, you will see the following results. SELECT name,          del_cmd,          ins_cmd,          upd_cmd          FROM dbo.sysarticles This means that the above […]

Setting Up Data And Log Files For SQL Server

When a database is created in SQL Server it creates two files by default: one is the data file with an extension of .mdf that stores the actual data, and the other is the log file with an extension of .ldf that stores the transaction log information used to recover data in disaster recovery scenarios. […]

Will Check Constraints Improve Database Performance?

The short answer is yes, but only in some instances.  Let’s say we have a table like following.  CREATE TABLE CheckConst (       ID Integer NOT NULL PRIMARY KEY,     Status CHAR(1)) We will insert few records just for demo purposes. INSERT INTO CheckConst (ID,Status) VALUES (1,’Y’) INSERT INTO CheckConst (ID,Status) VALUES (2,’N’) INSERT INTO […]

The Excel Connection Manager is not supported in the 64-bit version of SSIS

When you attempt to execute an SSIS package which has a Excel Destination/Source in SQL Server 64 bit , you will get following error. [Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available. As […]

Is there a difference between fill factor 0 and 100

When creating indexes in SQL Server, if you do not specify an index fill factor, the fill factor will be 0 (effectively the same as 100%). You can specify an index’s fill factor percentage in a number of different ways. Some of these methods include: The Create Index statement, DBCC DBReindex and Rebuild. ALTER INDEX […]

Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created.

ERROR: “Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.” This error happens because “Saving Changes is not […]
Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |