SQL Server General DBA

For optimum security, is it recommended to use a single domain account the SQL Server instances in a network?

Question Our team supports 150+ SQL Server 2000/2005 instances company-wide. In order to make it easier to manage the services accounts, we use the same domain account for all the SQL Services. This domain account has been granted both local machine and SA privileges.Recently, somebody raised a question about the wisdom of using the same domain account […]

What is the best way to test the upgrade process from SQL Server 7.0 or 2000 to SQL Server 2005, before I perform the actual upgrade?

As you indicate in your question, it is very important that you test the upgrade process before you upgrade your production server. Here’s what I recommend. Restore a backup of each of your production databases onto another (test) server, including the system databases running the same version of SQL Server as your production box. In […]

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

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

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

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