SQL Server General DBA

In SQL Server 2005, when I’m trying to open the Database Maintenance Wizard, I get an error message telling me that “Agent XPs” are not running. How can I resolve this problem, and what are “Agent XPs?”

If the SQL Server 2005 Surface Area Configuration tool is used to start the SQL Server Agent service, it also turns on the “Agent XPs” advanced option. This allows the Database Maintenance Wizard to run with no issues. This is a new SQL Server option found in SQL Server 2005. But if you don’t start the […]

What is the purpose of the Surface Area Configuration Tool in SQL Server 2005?

In recent times, Microsoft’s Trustworthy Computing initiative has strongly influenced how security is implemented out of the box for new products, such as SQL Server 2005. One of the main things Microsoft has done is to turn off, by default, potential areas of their applications that could be susceptible to security risks. In SQL Server […]

How can I enable xp-cmdshell on a SQL Server 2005 instance?

(Note: Please replace the - [hyphen] with an _ [underscore] wherever xp-cmdshell is mentioned in this FAQ.) As part of Microsoft’s desire to increase the out-of-the-box security of SQL Server 2005, it has turned off the xp-cmdshell extended stored procedure by default. If you try to use xp-cmdshell without manually enabling it, you will get an error message […]

Is it possible to set the database name in advance when deploying client database with an installer option?

Question: We have a requirement from our clients to deploy the database and set the database name in advance when installating the SQL Express or MSDE with the auto-install option. Answer: It is possible to create the database dynamically using SP_EXECUTESQL to build the SQL statements during runtime. You can then use EXECUTE to run […]

How do I get the taskpad view in SQL Server Management Studio as it was in SQL Server 2000 version?

Question: How do I get the taskpad view in SQL Server Management Studio as it was in SQL Server 2000 version in order to see database data & log files usage? Answer: SQL Server 2000 Enterprise Manager has a feature to display database data & transaction log file usage in a taskpad view when a […]

DTS package error “Cannot find specified package in the storage location specified”.

When a user attempts to execute a DTS package that has ‘spaces’ this will fail with the error “Cannot find specified package in the storage location specified”. The text of the error looks like  a problem with the path, which is partially correct. Also, the DBA must check the privileges for SQLAgent login context if […]

Unable to install Service Pack 1 for SQL Server 2005 version.

SQL Server 2005 has many key new features including a production-ready version of database mirroring from Service Pack1 installation, in which the primary production server is mirrored at all times by a standby server. “This allows for automated, seamless failover between primary and standby server, if the primary server needs to come down”.  SP1 also includes […]

How to get resource usage of an executing query in SQL Server without using SYSMON/PERFMON utility?

Perfmon/Sysmon utility is a handy utility for Database Administrators to invoke for data to be collected on system resources usage. The logging function of this tool is useful for logging details that can be used for further analysis from the trace. The SQL Server Develoment team has made a major contribution to combining this facility with […]

Can I downgrade my SQL Server databases to an earlier version?

No, at least not using sp_detach_db, sp_attached_db; or backup and restore. But you can recreate the database schema in a database on a different version of the database and then use DTS or SSIS to move the data.

Why do I get an unordered resultset, although my table has a clustered index?

Although the presence of a clustered index often seems to produce an ordered resultset, there is no guarantee that this will work in all cases. If you execute a SELECT statement without an explicit ORDER BY, SQL Server will try to return the information in the fastest possible way. This might be or might be […]
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 |