SQL Server General DBA

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 [...]

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 [...]

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 [...]

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 [...]

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 [...]

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. [...]

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 [...]

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 [...]

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 [...]

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 [...]