Sql Server

General
Q1: When is it recommended to use SQL Server over Sybase or Oracle?

Answer:

There is a strong infrastructure in support of the Sybase and Oracle products on the Sun/HP Unix platforms within the GCIB organization. Our Windows support environment though maturing is not as robust today. This in conjunction with available statistics on performance, reliability and scalability make the Sybase and Oracle products our recommended RDBMS for large scale, mission critical applications. We find SQL Server more suited today for non-mission critical applications with specific interoperability and/or cost concerns. As our Windows infrastructure evolves this recommendation will be revisited.

General
Q2: Is SQL Server site licensed or do I need to purchase a license?

Answer:

SQL Server is not site licensed so you will need to purchase a license.

General
Q3: What are the licensing requirements for SQL Server products?

Answer:

The following document from Microsoft explains the various licensing configurations. Licensing questions can be directed to Walter Delacruz in Purchasing, the GDMG, the MS SQL Server Knowledge Center or the GDES group.

General
Q4: How do I obtain a copy of the software once an order has been approved?

Answer:

The software is available in house once an order has been submitted for the license. A copy can be obtained from the GDES group.

General
Q5: When will Microsoft terminate support for SQL Server 6.5?

Answer:

RDBMS vendors will not usually maintain more than the three most recent versions of their product. The most recent version to date is SQL 2000 followed by 7.0 and 6.5. The next version of the software (code named Yukon) is not expected until 2002. Though support for 6.5 is available, Microsoft will no longer provide service packs for 6.5. It is highly recommended that you upgrade from 6.5 to the 7.0 or 2000 as there are huge performance and stability gains that can be achieved.  

General
Q6: Is SQL Server 2000 certified for use at Citigroup?

Answer:

No. SQL Server 2000 is still under going an in house certification process by the GDES group. Certification is expected by 5/15/2001.

General
Q7: What is the recommended SQL Server version?

Answer:

New deployments should utilize version 7.0. When SQL Server 2000 has been internally certified the recommendation will be updated.

General
Q8: Is there any benefit to upgrading to SQL Server 2000 from a lower version?

Answer:

SQL Server 2000 will provide improvements in performance, reliability and scalability.

General
Q9: Can I upgrade directly from SQL Server 6.5 to SQL Server 2000?

Answer:

Yes; you can upgrade from SQL Server 6.5 to SQL Server 2000. The process is similar to an upgrade from SQL Server 6.5 to 7.0.

General
Q10: How do I get technical support for SQL Server problems?

Answer:

Problems should be reported to your database support organization. If the DBA does not know how to report a problem to Microsoft they can post problems to the MS SQL Server Development Center forum, contact the GDMG or the GDES group.  

General
Q11: Does SQL Server 2000 run on Windows NT?

Answer:

Yes, SQL Server 2000 runs on both Windows NT and Windows 2000.

General
Q12: How do you determine the sort order of a SQL Server?

Answer:

You can execute the sp_helpsort stored procedure from isql or Query Analyzer to determine the sort order of SQL Server

General
Q13: When might I want to use a surrogate key?

Answer:

A surrogate key is a key that has no meaning other then uniquely identifying a row in the table -it contains no meaningful business information.

Reasons you might use one :

1. There is no "good" primary key.
2. The obvious primary keys are awkward to use.
3. The obvious primary key is used in foreign key relationships and will require updating

General
Q14: How can I run some SQL code every time SQL Server starts?

Answer:

With SQL 6.x use the sp_markstartup supplied stored-procedure. This will run any SP you write yourself as soon as SQL Server finishes initializing.

With SQL 7.0 the same result is achieved using sp_procoption to mark the SP for auto-start. Another way, assuming that you use SQL Agent, is to define a SQL Agent job with the 'Start automatically when the SQLServerAgent starts' option. However, this will run the job every time SQL Agent is re-started (if it ever is).

General
Q15: Why do my device sizes appear as negative values in SQL EM?

Answer:

You may also find that because of this error it will not let you make any changes. This is caused by a known bug in Enterprise Manager when there is greater than 2Gb of free space on a disk. It was fixed in 6.5 SP3 and above. If you are running SQL EM from a client, then you will need to apply SP3 to that as well.

General
Q16: : What SQL Service Pack am I running?

Answer:

Do a "select @@version" and check the build number against the following table:

8.00.194 SQL Server 2000
8.00.384 SQL Server 2000 SP1
7.00.699 SQL Server 7.0 SP1
7.00.842 SQL Server 7.0 SP2
7.00.961 SQL Server 7.0 SP3
6.50.213 SQL Server 6.5 with Service Pack 1
6.50.240 SQL Server 6.5 with Service Pack 2
6.50.258 SQL Server 6.5 with Service Pack 3
6.50.281 SQL Server 6.5 with Service Pack 4
6.50.415 SQL Server 6.5 with Service Pack 5
6.50.416 SQL Server 6.5 with Service Pack 5a


General
Q17: : How can I see table size information in 2000?

Answer:

exec sp_msforeachtable @command1="print '?'", @command2="sp_spaceused '?'"

General
Q18: : How does the SQL Mail Service start and stop and how can I see when it has started and stopped?

Answer: The SQL Mail Service can be configured to start when the SQL Server is started in Enterprise Manager in the "SQL Mail Properties" window under "Support Services". It is configured to stop whenever the SQL Server is shutdown. The SQL Server errorlog contains entries whenever the SQL Mail Service is started or stopped.

2001-11-12 09:37:07.89 spid1 Launched startup procedure 'xp_startmail'
2001-11-12 09:37:08.15 spid10 Using 'sqlmap70.dll' version '1998.11.13' to execute extended stored procedure 'xp_startmail'.
2001-11-12 09:37:08.15 ods Starting SQL Mail session...
2001-11-13 10:20:37.82 kernel SQL Server terminating because of system shutdown.
2001-11-12 09:37:09.98 ods SQL Mail session started.
2001-11-13 10:20:39.15 kernel SQL Server is terminating due to 'stop' request from Service Control Manager.
2001-11-13 10:20:39.17 ods Stopped SQL Mail session.

General
Q19: Can I talk to another user logged into the server (useful if some is blocking your process, if you need to shutdown a server,…)?

Answer:

Using Enterprise Manager click on the Management and Current Activities tabs. Then click on Process Info, allowing you to see all the processes currently on the server. Here you will be able to see any blocking, what processes are currently running, which database a user is in, etc. To talk to a specific user, right click the process id and click Send Message. You will be able to send that particular user a message that will appear on their terminal.

General
Q20: We have a 4-way SQL Server 2000 license. SQL Server Analysis Services comes free with a SQL Server license. We would like to keep Analysis Services on a different hardware/machine which is a 4-way/2-way server. Can we do it without buying extra licenses, or do we need to buy more licenses?

Answer:

No, you will have to purchase new SQL Server licenses for this scenario. Microsoft does not allow you to "split" its licenses and use it on more than one server/processor. The Analysis Service is an integral part of SQL Server software, and can not be used on a separate server/processor. You need to purchase additional licenses for each server/processor.

General
Q21: How do I increase security for DTS packages?

Answer:

Did you know that anyone with a valid login to your SQL Server can see and execute all DTS packages on that server via Enterprise Manager? They can also add as many new DTS packages as they want. This is because access to the stored procedures that perform these activities defaults to the public role.

To block this kind of activity, remove the execute permissions from the following stored procedures found in the msdb database:

sp_add_dtspackage
When permissions are removed from this stored procedure standard users can no longer add DTS packages.

sp_enum_dtspackages
When permissions are removed from this stored procedure standard users can no longer see existing DTS packages.

sp_get_dtspackage
When permissions are removed from this stored procedure standard users can no longer open nor execute existing DTS packages.

After doing this you can add more security by resaving each DTS Package and assigning a owner password and/or a user password.

General
Q22: What restrictions exist on the naming of a SQL Server?

Answer:

In SQL Server 7.0 there can be no dashes (-) in the name of the SQL server, while in SQL Server 2000, there cannot be any underscores ( _ ) in the server name.

Support Infrastructure
Q1: Is there a SAN environment available in house for SQL Server/NT applications?

Answer:

The Wintel organization is currently testing the use of the EMC SAN environment for use with SQL Server. The target completion date is 5/15/2001.

Support Infrastructure
Q2: How can I monitor the health of my SQL Server?

Answer:

NetIQ is the company standard for monitoring NT environments. There is an initial start up cost and a yearly maintenance fee associated with this software. Further information can be obtained from the Wintel organization.

Support Infrastructure
Q3: Is Netbackup available for MS SQL Servers?

Answer:

There is an obsi module that is required to backup a SQL Server to the tape silos using SQL Backtrack and Netbackup. This module is in house as of 3/29/2001 and is currently being tested. Today you can backup your SQL Server to disk and let the OS backup process save a copy of the dump file to tape. This must be timed carefully to prevent conflict.

Support Infrastructure
Q4: What is the difference between remote servers and linked servers?

Answer:

The biggest difference is that a remote server only allows you to execute a stored procedure that exists on another SQL Server, whereas a linked server allows you to run distributed queries and transactions, between any of your linked servers.

Because remote servers are only provided for backward compatibility and linked servers provide more functionality than remote servers, you should always use linked servers.
[7.0,2000]

Development
Q2: What is the limit on the number of tables in a query in SQL Server?

Answer:

With SQL 6.5 and earlier the limit is 16. With SQL 7.0 the limit is 256.

Development
Q3: How do I pass a stored procedure result to a table?

Answer:

insert into <tablename> exec <storedprocedure>
Logic and table structure must be equal to the result columns of the stored procedure execution.

Development
Q4: How can I restrict access to my SQL Server so that it only allows certain programs to connect? (I don't want my clients using Access to hack data directly).

Answer:

SQL Server 6.5 and below have no built-in tools/facilities to do this, nor does it allow a stored-procedure to be run on client connection that could be used to do this. Of course if all your data is protected correctly with SQL/Integrated security and all auditing/business rules are held with constraints/triggers then you won't need to worry as it doesn't matter what tool people use to connect. However, in a real-world application, these pre-requisites are unlikely to be there.

SQL Server 7.0 and 2000 has the idea of application ROLES, as well as user roles.

If you are still on 6.5 or below then you can try one of the below - but they are all kludges: -

1. Write your own ODS Gateway and point the clients at that instead of the SQL Server - the ODS Gateway will then do the checking. However, there is nothing stopping clients figuring out the correct SQL client-config entries to point straight at the SQL Server. There are examples of ODS code in the SQL Programmers Toolkit - available for free download from the MS website.

2. Write a constantly running/scheduled stored-procedure that checks the relevant column in sysprocesses (program_name), and then issues a KILL command for any processes that should not be running. This way allows people to connect and possibly make changes before they are spotted and killed.

3. Change servers to use standard security. When the apps connect they request a trusted connection and get dumped into a new database - the only one their trusted account can connect to. This database contains a table holding a lookup between the users NT account and an alternative login name, password (encrypted) and perhaps server and database name to use. The only runnable object is a stored proc that returns the user's rows from this table. The front-end calls the sp, decrypts the password and re-connects to the server. One downside to this sort of approach is that the users "real" name and password could potentially be cracked with an ODBC trace (several versions of the ODBC sdk tools allowed passwords to be displayed) or network sniffer.

Development
Q5: How can I dynamically access database objects within T-SQL?

Answer:

SQL Server 7.0 and up provides a facility for executing queries with dynamic object references. The following code, though useless, is an example of how to dynamically pass table names.

Declare @TableName varchar(30), @SQLString varchar(1000)
Set @TableName = '0'

While 1 = 1
Begin
Set rowcount 1
Select @TableName = name from sysobjects
Where type = 'U'
And name >@TableName
Order by name

If @@rowcount = 0
Break

Set @SQLString='Select * from ' + @TableName
set rowcount 0
Execute(@SQLString)
End

Development
Q6: How can I BCP out columns from multiple tables into one file?

Answer:

Versions 7.0 and higher provide options to allow for the export of the result sets of views and stored procedures.
Bcp "exec <dbname>..<stored procedure>" queryout <filename> -c -Sserver_name -U<username> -P<password>

Development
Q7: How does SQL Server decide whether or not to use indexes?

Answer:

SQL Server is a cost-based optimizer, not a rule-based system. Being cost-based, SQL Server can therefore be syntax-independent and literally cost each execution strategy based on the projected number and size of the results set. If you want to force table scans or index strategies, you can only guarantee their use by using "index hints." This is generally not recommended, although at times it may become necessary.

It is difficult to generalize and specify a basic set of rules under which the query processor will "always pick a table scan or index seek." In general, the use of an index access strategy is favored over table scans unless the choice is very clear, for example, in the case when all rows are wanted. Table scans acquire shared locks, and can thereby greatly reduce concurrency (that is, multi-user access). That is why table scans are avoided whenever possible.

Scan decisions are based on anticipated execution costs, so there is no "size" limit below which indexes are ignored. However, if the entire table fits on a single page, there are very few cases, if any, in which indexes will be of value.

Development
Q8: Are DB-Library applications still supported in SQL Server 2000?

Answer:

Yes. However, DB-Library has not been enhanced for SQL Server 2000. DB-Library ships with the same features that the Microsoft SQL Server 7.0 DB-Library contained. This means that a DB-Library application can only connect to a default instance of SQL Server 2000; it cannot connect to a named instance. In addition, it won't understand any of the new features available in SQL Server 7.0 and later.

Development
Q9: What Is DTS and how can I use it?

Answer:

Data Transformation Services (DTS) is a set of tools you can use to import, export, and transform heterogeneous data between one or more data sources, such as Microsoft SQL Server, Microsoft Excel, or Microsoft Access. Connectivity is provided through OLE DB, an open-standard for data access. ODBC (Open Database Connectivity) data sources are supported through the OLE DB Provider for ODBC.

You create a DTS solution as one or more packages. Each package may contain an organized set of tasks that define work to be performed, transformations on data and objects, workflow constraints that define task execution, and connections to data sources and destinations. DTS packages also provide services, such as logging package execution details, controlling transactions, and handling global variables.

These tools are available for creating and executing DTS packages:

· The Import/Export Wizard is for building relatively simple DTS packages, and supports data migration and simple transformations.
· The DTS Designer graphically implements the DTS object model, allowing you to create DTS packages with a wide range of functionality.
· DTSRun is a command-prompt utility used to execute existing DTS packages.
· DTSRunUI is a graphical interface to DTSRun, which also allows the passing of global variables and the generation of command lines.
· SQLAgent is not a DTS application; however, it is used by DTS to schedule package execution.

Using the DTS object model, you also can create and run packages programmatically, build custom tasks, and build custom transformation.

Development
Q10: Should I be using cursors?

Answer:

If possible, avoid cursors in SQL Server. They generally use a lot of SQL Server resources and reduce the performance and scalability of an application. If row-by-row processing must be done: try perform the task at the client level, use tempdb tables, use derived table, use a correlated sub-query, or use the CASE statement.

Development
Q11: I have a column in a SQL Server table that allows NULL values. I want this column to be unique when it has a value other than NULL. What's the best way to program this behavior? If I put a UNIQUE constraint on the column, I can include only one record that has a NULL value. I'm using a trigger to enforce this restriction, but can you recommend a simpler way to ensure that all non-NULL values are unique?

Answer:

SQL Server has no built-in mechanism to prohibit duplicates except NULLs, so you need to implement a custom CHECK constraint to enforce this restriction. For example, the following code snippet enforces the kind of integrity you're looking for.
USE tempdb
CREATE table t1 (c1 int NULL, c2 char(5) NULL)
CREATE trigger mytrigger on t1 for insert, update as
BEGIN
IF (select max(cnt) from (select count(i.c1)
as cnt from t1, inserted i where t1.c1=i.c1 group
by i.c1) x) > 1
ROLLBACK TRAN
END
In SQL Server 2000, you can also use INSTEAD OF triggers to carry out this enforcement. For more information about INSTEAD OF triggers, see the articles below. To access these articles, go to SQL Server Magazine, enter the InstantDoc number in the InstantDoc box, and then click Go. The articles are:
· Tricks with INSTEAD OF Triggers; InstantDoc number 15828
· INSTEAD OF Triggers on Views; InstantDoc number 15791
· INSTEAD OF Triggers; InstantDoc number 15524

Development
Q12: How do I know what type of index to create?

Answer:

Before creating clustered indexes, understand how your data will be accessed.

Consider using a clustered index for:

Clustered indexes are not a good choice for:

Before creating nonclustered indexes, understand how your data will be accessed.

Consider using nonclustered indexes for:

Development
Q13: How can I optimize the query plans for my stored procedures accepting parameters, thus achieving optimal performance?

Answer:

The problem is the query-plans, the pre-compilation of stored procedures, that SQL Server does for you. As you know, one of the biggest reasons to use stored procedures instead of ad-hoc queries is the performance gained by using them. The problem that arises with the above tip is that SQL Server will only generate a query-plan for the path taken through your stored procedure when you first call it, not all possible paths.

Let me illustrate this with an example. Consider the following procedure (pre-compilation doesn't really have a huge effect on the queries used here, but these are just for illustration purposes):

CREATE PROCEDURE dbo.spTest (@query bit) AS
IF @query = 0
SELECT * FROM authors
ELSE
SELECT * FROM publishers
GO

Suppose I make my first call to this procedure with the @query parameter set to 0. The query-plan that SQL Server will generate will be optimized for the first query ("SELECT * FROM authors"), because the path followed on the first call will result in that query being executed.
Now, if I next call the stored procedure with @query set to 1, the query plan that SQL Server has in memory will not be of any use in executing the second query, since the query-plan is optimized for the authors table, not the publishers table. Result: SQL Server will have to compile a new query plan, the one needed for the second query. If I next call the procedure with @query set to 0 again, the whole path will have to be followed from the start again, since only one query-plan will be kept in memory for each stored procedure. This will result in sub-optimal performance.

As it happens I have a solution, one that I've used a lot with success. It involves the creation of what I like to call a 'delegator'. Consider again spTest. I propose to rewrite it like this:

CREATE PROCEDURE dbo.spTestDelegator (@query bit) AS
IF @query = 0
EXEC spTestFromAuthors
ELSE
EXEC spTestFromPublishers
GO

CREATE PROCEDURE dbo.spTestFromAuthors AS
SELECT * FROM authors
GO

CREATE PROCEDURE dbo.spTestFromPublishers AS
SELECT * FROM publishers
GO

The result of this restructuring will be that there will always be an optimized query-plan for spTestFromAuthors and spTestFromPublishers, since they only hold one query. The only one getting re-compiled over and over again is the delegator, but since this stored procedure doesn't actually hold any queries, that won't have a noticeable effect on execution time. Of course re-compiling a plan for a simple 'SELECT *' from a single table will not give you a noticeable delay either (in fact, the overhead of an extra stored procedure call may be bigger then the re-compilation of "SELECT * FROM AnyTable"), but as soon as the queries get bigger, this method certainly pays off.

The only downside to this method is that now you have to manage three stored procedures instead of one. This is not that much of a problem though as the different stored procedures can be considered one single 'system', so it would be logical to keep all of them together in the same script, which would be just as easy to edit as a single stored procedure would be. As far as security is concerned, this method shouldn't give you any extra headaches either, as the delegator is the only stored procedure directly called by the client, this is the only one you need to manage permissions on. The rest will only be called by the delegator, which will always work as long as those stored procedures are owned by the same user as the delegator.

Development
Q14: My DTS package is stored inside SQL Server. Now I cannot open it. How can this be resolved?

Answer:

The DTS packages are stored in the sysdtspackages system table in the msdb database. Check this package presents on the server:

USE msdb
select name from sysdtspackages

The text of the DTS package is stored in the PackageData image field of the sysdtspackages system table in the msdb database. To save DTS package in file, you can use textcopy.exe utility from the C:\MSSQL7\BINN (path by default). This is the example to save DTSName package from the sysdtspackages table into DTSText.dts file:

textcopy /S ServerName /U sa /P /D msdb /T sysdtspackages /C packagedata /W "where name='DTSName'" /F DTSText.dts /O

Now you can open the DTSName package from the DTSText.dts file and run it.

Development
Q15: Is using the TOP N clause faster than using SET ROWCOUNT N to return a specific number of rows from a query?

Answer:

With proper indexes, the TOP N clause and SET ROWCOUNT N statement are equally fast, but with unsorted input from a heap, TOP N is faster. With unsorted input, the TOP N operator uses a small internal sorted temporary table in which it replaces only the last row. If the input is nearly sorted, the TOP N engine must delete or insert the last row only a few times. Nearly sorted means you're dealing with a heap with ordered inserts for the initial population and without many updates, deletes, forwarding pointers, and so on afterward.
A nearly sorted heap is more efficient to sort than sorting a huge table. In a test that used TOP N to sort a table with the same number of rows but with unordered inserts, TOP N was not as efficient anymore. Usually, the I/O time is the same both with an index and without; however, without an index SQL Server must do a complete table scan. Processor time and elapsed time show the efficiency of the nearly sorted heap. The I/O time is the same because SQL Server must read all the rows either way.

Development
Q16: Is there a Windows equivalent to the Unix command grep?

Answer:

Yes. Use the Windows NT FINDSTR.EXE utility to search for text patterns in the error logs. (FINDSTR.EXE is NT's equivalent of GREP.) This utility can help automate the process of scanning the log for errors. The following example shows how to scan the error log for the keyword error :

C:\mssql\log>findstr /i /n /c:"error :" errorlog

Following is some sample output:

C:\mssql\log>findstr /i /n /c:"error :" errorlog
34:95/09/16 11:24:20.42 ods Error : 17903, Severity: 18, State: 1
36:95/09/16 11:24:20.43 ods Error : 17951, Severity: 18, State: 1
53:95/09/16 11:24:35.53 ods Error : 17903, Severity: 18, State: 1
55:95/09/16 11:24:35.54 ods Error : 17951, Severity: 18, State: 1

Development
Q17: How do I return a set of records as a comma delimited string?

Answer:

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +
CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList


--Results--
---------
1, 2, 4


The COALESCE function performs the magic here. When @EmployeeList is NULL (the first row processed), it returns an empty string. On subsequent rows, it concatenates the @EmployeeList value with a comma and the current @Emp_UniqueID value.

Replication
Q1: How can I monitor latency during transactional replication?

Answer:

You will need to know the amount of time it takes the Log Reader to move transactions from the Publication database transaction log to the Distribution database as well as the time required for the Distributor Agent to move transactions from the Distribution database to the Subscriber database. The total of these two measurements is the latency for the replication of a transaction from the publication database to the subscriber database. The counters for these two processes are the SQL Server Replication LogReader: Delivery Latency counter and the SQL Server Replication Dist.: Delivery Latency counter. A significant increase in either of these counters is an indicator that a problem may exist.
[6.5, 7.0, 2000]

DB Admin
Q1: How do I remove the tempdb database from master/default devices?

Answer:

Do the following for SQL 6.5 and below:
1.Configure tempdb to be in RAM for 2 MB. Use SQL EM or sp_configure.
2.Stop and restart SQL Server.
3.Add a new device for tempdb. Do not call it temp_db - any other name should be ok
4.Make that new device a 'default' device; make sure no other device is marked as default, especially master. You can check/change default status either using SQL EM, or the sp_diskdefault stored-procedure. Both are fully described in the books-online.
5.Configure tempdb to NOT be in RAM (set value to 0)
6.Stop and restart SQL Server
7.Re-mark whichever device(s) you want to be default.

For SQL 7.0 :-
1.alter database tempdb alter file filename = ....
2.Stop and restart Sql Server.

DB Admin
Q2: How do I use startup trace flags in a SQL Server clustered environment?

Answer:

To start SQL Server in a clustered environment and use the various startup parameters, perform the following steps:

Use the Microsoft Cluster Server (MSCS) Cluster Administrator to take the virtual server offline.

This step involves editing the registry.

WARNING: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

For information about how to edit the registry, view the "Changing Keys and Values" Help topic in Registry Editor (Regedit.exe) or the "Add and Delete Information in the Registry" and "Edit Registry Data" Help topics in Regedt32.exe. Note that you should back up the registry before you edit it. If you are running Windows NT or Windows 2000, you should also update your Emergency Repair Disk (ERD).

Run REGEDT32 and navigate to the following key:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer$<SVS>
Change or add a startup parameter to the Parameters\SQLArg<n> key.

Place the virtual server back online. This causes the registry keys to be read, and SQL Server will be started using the new parameters.

DB Admin
Q3: How do I shut down SQL Server from the command line without the Cluster Service interpreting this shutdown as a failure?

Answer:

The proper way to shut down SQL Server from the command line is to use the Cluster.exe application that comes with Microsoft Windows NT Server Enterprise Edition. This application is installed as part of the normal Windows NT Cluster Setup, which can also be run on a Windows NT Workstation or member server computer to install just the Cluster Administrator and the other administrative applications. The basic syntax for this command is:
cluster [cluster name] RESOURCE [resource name] /option

In the command syntax above, the /option switch controls this functionality. The specific options to be used are /online and /offline. These two options are equivalent to the commands net start mssqlserver (the method to start SQL Server from the command line) and net stop mssqlserver (the method to shut down SQL Server from the command line) for a non-virtualized server, respectively. This procedure can be performed on the Generic Service, the SQL Server Agent 7.0, and the SQL Server 7.0 resources.

The following are some examples of how to use this command syntax:

To take the SQL Server 7.0 resource offline if the Cluster Name is "SQLCluster" and the resource is named "VirtualSQL" (where 'VirtualSQL' is the name of the SQL Server 7.0 resource, not the virtual network name resource):
cluster "SQLCluster" resource "VirtualSQL" /offline

To bring the SQL Server 7.0 resource back online:
cluster "SQLCluster" resource "VirtualSQL" /online

DB Admin
Q4: Can I install SQL Server 7.0 on a server that already has SQL Server 6.x installed?

Answer:

Yes, but you cannot run both SQL Server 6.x and SQL Server 7.0 at the same time. SQL Server Setup adds a version switch, which you can use to switch between SQL Server 6.x and SQL Server 7.0.

CAUTION: If you are installing SQL Server 7.0 alongside SQL Server version 6.x on the same computer, do not install SQL Server 7.0 in the same directory as SQL Server 6.x.

DB Admin
Q5: Can multiple instances of SQL Server 7.0 be run on the same machine?

Answer:

No. You need to upgrade to SQL Server 2000 to run multiple instances at the same time on one computer.

DB Admin
Q6: When running both SQL Server 7.0 and SQL Server 2000 on the same computer, is there any way to get the two instances to share the same user databases?

Answer:

No. The same database files cannot be used for both SQL Server 7.0 and SQL Server 2000. SQL Server 7.0 databases can be upgraded to SQL Server 2000 databases, but after the database files are upgraded, they cannot be used by the SQL Server 7.0 instance. Note that this database upgrade is done automatically when a SQL Server 7.0 database is attached or restored to a SQL Server 2000 server. In addition, there is no way to downgrade the database files from SQL Server 2000 files to SQL Server 7.0 files.

DB Admin
Q7: How can I get the hardware spec of a SQL Server remotely?

Answer:

The xp_msver extended procedure will return most of the information you need.

DB Admin
Q8: What are SQL Server hot-fixes and where can I get them?

Answer:

Hot-fixes are builds of SQL Server just like service-packs. However, they are not fully packaged or given the same level of testing as a service pack. They should only be applied to resolve a particular problem. Hot fixes can be obtained from Microsoft Technical Support. If you are not sure how to contact Microsoft Technical Support, post a question to the SQL Server Knowledge Center forum or contact the GDMG organization.

DB Admin
Q9: How can I clear SQL Server's procedure cache?

Answer:

With SQL 6.5 and below you can't. You must stop/start SQL Server.
For SQL 7.0 DBCC DROPCLEANBUFFERS .

DB Admin
Q10: I run SQL Server 7.0 on Windows NT 4.0. The database size is 50GB. I plan to set up transactional replication to another server in the same location. Approximately how long should I expect the initial replication to take? I've installed the Distributor and Publisher on the same server. For a database of 50GB, how much space do I need to claim for the Distributor?

Answer:

You should expect the initial replication to take about the same amount of time that you spend using the bulk copy program (bcp) to bcp out the data that you want to replicate plus the time the data takes to bulk-copy into the Subscriber. Microsoft recommends using native format for SQL Server-to-SQL Server replication. You can also tweak SQL Server's snapshot and distribution tasks to generate and apply the initial data faster by experimenting with the MaxBcpThreads parameter. The mileage varies depending on the number of CPUs and the disk subsystem you assign to the task. The speed of your network greatly affects how long the replication takes. For the Distributor, you need to allocate space approximately equal to the size of the data you're replicating plus the space that SQL Server needs to accumulate incremental changes in the distribution database. If you replicate continuously, the accumulation should be insignificant.

DB Admin
Q11: While running the SQL 2000 graphical query analyzer, a disproportionately large percentage of the batch execution time is dedicated to bookmark lookup.

Answer:

The Bookmark Lookup logical and physical operator uses a bookmark (row ID or
clustering key) to look up the corresponding row in the table or clustered
index. The Argument column contains the bookmark label used to look up the
row in the table or clustered index. The Argument column also contains the
name of the table or clustered index in which the row is looked up. If the
WITH PREFETCH clause appears in the Argument column, then the query
processor has determined that it is optimal to use asynchronous prefetching
(read-ahead) when looking up bookmarks in the table or clustered index.

You would need to review the code that is executing to determine whether
this operator is being explicitly called. This can be set using either
cursors or OLEDB calls. You can find information on this in books on line.

The optimizer may also choose to use bookmark lookups based on the queries
submitted. The presence of this operator alone does not indicate a problem.
If you find that your execution time is unacceptable, you would need to
analyze the statements (using the query analyzer) as well as the overall
execution environment to determine what could be done to enhance
performance.  

DB Admin
Q12: How can I have a unique index that allows multiple NULL values?

Answer:

First create the table with nullable columns. And then create a view based on the table that only contains not null values. Finally, build the unique index against the view.

For example,

create table TABLE (x int null, y varchar(20))
go

create view vTABLE
as select x from TABLE where x is not null
go

create unique clustered index idx on vTABLE(x)
go


DB Admin
Q13: Why does my transaction-log fill up when I use fast-bcp, select into or the transfer tool?. I thought this didn't log anything.

Answer:

Fast BCP and select into do not log record updates. However, they DO log extent allocations. They need to do this so that if the process is terminated unexpectedly (maybe the power goes out), SQL can recover the space. Therefore, with large bcp's/select into's - when a lot of extents need allocating - the log can still fill. In which case, it needs to be made larger.

DB Admin
Q14: Can I change the name of a SQL Server and if so how?

Answer:

SQL Server versions 6.5 and 7 takes the name of the NT Machine that it runs on. If you change the name of the NT Machine then you do the following:

For 6.5 - sp_dropserver <oldname>
go
sp_addserver <newname>, local
go

For 7.0 - With SQL 7.0 you will need to re-run setup to reset the server name. Setup will detect the name conflict, resolve it, and then finish.  

DB Admin
Q15: What registry entries does SQL Server use?

Answer:

SQL Server uses the following registry keys. (If you delete all these then the SQL setup routine shouldn't spot the old version)

All versions:
· HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer
· HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer

6.0 and above:
· HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSDTC
· HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLExecutive

7.0 and above:
· HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLServerAgent
· HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server 7
· HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServ65

DB Admin
Q16: I have a SQL Server process that shows as being in SPINLOOP. What does this mean?

Answer:

The process concerned is accessing an area of memory that SQL Server needs to protect against multiple access - typically on SMP servers. To this end it locks that area of memory (via a spin lock) so that any other access to it gets paused. In concept it is very similar to row/page locking in a database, but is used for internal memory areas within a program. There is plenty of documentation in the Win32 SDK/MSDN about spin locks and their usage.

If the SPINLOOP process does not give up control then it is very likely that SQL Server will become unresponsive. It is unlikely that you will be able to KILL a process in this state, and you will probably have to kill SQL Server itself (use KILL command from the NT Resource Kit).

DB Admin
Q17: What happens when SQL Server detects a torn page? How can I recover from torn pages?

Answer:

When a torn page is detected, a severe I/O error is raised. This error will close the connection. The database is only marked suspect if the torn page is detected during recovery. Restoring the database from a backup and rolling the transaction log forward should correct the problem with no data loss.

DB Admin
Q18: Why is my reported log space never 0 (zero), even after truncating the log?

Answer:

In SQL Server 7.0, the log truncation granularity is the virtual log file; in SQL Server 6.x it is a page. Consider an example in which a log configuration consists of four virtual log files. In this example, even if there are no outstanding or unreplicated transactions that prevent truncation of the log after backup, at least 25 percent of the log will always be "in use" because there is always a small portion of the log in use, thereby causing at least one virtual log file to be marked as busy.

Efficiency is one of the primary motivating factors for implementing this schema in SQL Server 7.0. In SQL Server 6.x, truncating the log (even to throw it away) requires scanning through the page chain and deallocating pages. Now truncating the log is as simple as changing the status on a virtual log file from a "used but doesn't contain active log" state to "usable".

It is still possible to have a process back up the log when it reaches some level of being full. However, SQL Server 7.0 differs from SQL Server 6.x in that the smallest "fullness" level that can be achieved through a transaction log backup and truncation is (1/n * 100) percent, where n is the number of virtual log files in the database configuration.

Because of this new method of managing transaction log activity, it is no longer necessary to run DBCC CHECKTABLE(syslogs) to get an accurate determination of log space used.

DB Admin
Q19: How do I find the port number for a server on MS2000?

Answer:

On the local machine. Click 'start' , 'programs', 'Microsoft SQL Server','Server Network Utility' Then highlight the appropriate instance. In the Enable Protocols highlight TCP/IP. Then click properties. This will show the server port number.  

DB Admin
Q20: How do you run a specific action against all the databases or all tables in the database (excluding system ones)?

Answer:

For each Database:

There are two ways of doing it. First, one can use the existing procedure, starting with SQL Server 7.0 and up, sp_MSforeachdb, which allows you to enter any action as a parameter to the procedure: execute a proc, run SQL statement or DBCCs.

Example 1. Run a system proc against all databases:
EXEC sp_MSForEachDB 'USE ? SELECT DB_Name() EXEC sp_updatestats'

Example 2. Run DBCC against all the databases.
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

For more info on this procedure check out this msdn link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro99/html/SQL99E1.asp

The same task can be done using below more manual method. Here is a random sample of it:

declare
@isql varchar(2000),
@dbname varchar(64)

declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
begin
select @isql = 'if exists(select * from @dbname..sysobjects where type = ''u'' and name like ''QueryTable'')' + char(13)
select @isql = @isql + 'begin' + char(13)
select @isql = @isql + 'print ''@dbname''' + char(13)
select @isql = @isql + 'select * from @dbname..QueryTable' + char(13)
select @isql = @isql + 'end' + char(13)
select @isql = replace(@isql,'@dbname',@dbname)
-- print @isql
exec(@isql)
fetch next from c1 into @dbname
end
close c1
deallocate c1

For each table in the database:

Sometimes, you need to perform the same actions for all tables in the database. You can make cursor for this purpose, but you can also use sp_MSforeachtable stored procedure in this case. For example: You can use this stored procedure to rebuild all indexes in your database.
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

DB Admin
Q21: How can I quickly change the owner of a table?

Answer:

If all you need to do is change the owner of tables, do the following:

sp_MSForEachTable 'sp_changeobjectowner ''?'',''OwnerNameHere'''

Keep in mind you will need to correct any queries, views, or stored procedures that reference the bad owner. If you need to do this for views or stored procedures you can do sp_changeobjectowner 'objecthere', 'ownernamehere' for each item or put it in a cursor that gets these from the sysobjects table to do this automatically.

DB Admin
Q22: How can I help identify the worst performing queries in my SQL Server database application?

Answer:

In SQL Server 7.0 and SQL Server 2000, this is an easy task using the SQL Server Profiler.

In the SQL Server 7.0 Profiler, you can use the "Create Trace Wizard" to choose the "Find the worst performing queries" profile trace. You can specify some number, in milliseconds, to determine which queries you want to view. For example, if you only want to identify those queries that take longer than 10 seconds to perform, then you can enter 10,000 milliseconds as the cutoff time, and only those queries that take longer than 10 seconds will be captured for your analysis.
In SQL Server 2000, which doesn't have a "Create Trace Wizard," you need to create your own template using this configuration:

Select These Event Classes
· SQL:BatchCompleted

Select These Data Columns
· Groups
· Duration
· Columns
· EventClass
· TextData
· CPU
· Application Name
· LoginName
· NTUserName
· SPID

Select These Filters (as needed)
· Application Name
· DatabaseName
· Duration

The output of this Profiler trace is not as easy to read as the one provided with the SQL Server 7.0 Profiler "Create Trace Wizard," but it fulfills the same function of identifying the worst performing queries.

Once you have identified the worst performing queries in your application, then you can start to analyze each one, trying to find ways to boost their performance. Don't waste your time on queries that are seldomly run. Instead, focus your time on those long running queries that run the most often in your application.

DB Admin
Q23: I've noticed that the SQL Server 7.0 code page for a default installation is SQL_Latin1_General_CP1_CI_AS, but the SQL Server 2000 default code page is Latin1_General_CI_AS. This change makes a significant difference when I need to restore SQL Server 7.0 databases into SQL Server 2000. How can I get around this change?

Answer:

SQL Server is moving away from the legacy, self-defined code page support and toward code pages matching the Microsoft® Windows code pages you choose when you set Windows locale IDs. For more information about code pages, see "How Character Data Is Stored" in SQL Server 2000 Books Online.

When you upgrade from SQL Server 7.0 to SQL Server 2000, you keep your existing sort order and code page by default. However, when you perform a clean installation of SQL Server 2000, you get the new sort order and code page. If you attach a database to a SQL Server 2000 instance, SQL Server keeps whatever the collation was on the database when you created it. In other words, you keep the SQL Server 7.0 code page if you attach a SQL Server 7.0 database to SQL Server 2000.

Because SQL Server is moving toward a new default code page, we recommend that, if you're upgrading by doing a clean installation and then attaching a database, you pick a collation at installation time that matches your SQL Server 7.0 collation. You can determine this collation by performing an upgrade, for example an upgrade of the pubs database, from your SQL Server 7.0 installation and then looking at the collation that results when you run a query using the SERVERPROPERTY (Collation) function.

Microsoft made this change to reduce the scope of differing results that the incompatible SQL Server and Windows code pages cause.

DB Admin
Q24: How can I determine what database files are being used the most?

Answer:

SQL Server 2000 offers a system table-valued function that provides statistical information on the I/O activity of specific database files. It is called fn_virtualfilestats. What is interesting about this function is that it is specific to a database file. For example, if you want to see the I/O activity of a particular user database, you can. Keep in mind that all SQL Server databases have at least two physical files (for the database and log), and can have many more, depending on how the database was created. When using this function, you have to not only specify the database, but the file within the database. This is very specific I/O statistics indeed. To run this function, use the syntax found below:

SELECT * FROM :: fn_virtualfilestats(dbid, fileid)
Where:

dbid: The database ID of the database you want to examine.
fileid: The file ID of the physical files that make up your database.

At a minimum, each database has at least two files: the database file (MDF file) and a log file (LDF). A database can have many files, and the file number refers to the number of the physical file that you want to examine.

To identify the dbid and fileid you want to use in the above statement, run the following SELECT statement. It will provide you with a list of all of the database names, database ids, and file ids (for each database) on your server.

SELECT sysdatabases.name AS Database_Name,
sysdatabases.dbid AS Database_ID,
sysaltfiles.fileid AS File_ID
FROM sysaltfiles INNER JOIN sysdatabases
ON sysaltfiles.dbid = sysdatabases.dbid
ORDER BY sysdatabases.name, sysaltfiles.fileid

Once you run this function, a wide variety of information is available, including:
NumberReads: The number of physical reads made against this file since the last time SQL Server was restarted.
NumberWrites: The number of physical writes made against this file since the last time SQL Server was restarted.
BytesRead: The number of bytes read from this file since the last time SQL Server was restarted.
BytesWritten: The number of writes to this file since the last time SQL Server was restarted.
IoStallMS: The total amount of time that users have waited for I/Os to complete for this file (in milliseconds).

The first four statistics can give you a feel for how busy a particular file is. This can come in handy when comparing multiple filegroups in a database and to see how balanced the I/O is to each file. To make the most of filegroups, I/O should be spread out among the various files for best overall performance. The last statistic, IoStallMS, is best used find out if your have a bottleneck in your transaction log, as demonstrated below:

SELECT IoStallMS / (NumberReads+NumberWrites) as IsStall
FROM :: fn_virtualfilestats(dbid, fileid)
Where:

dbid: The database ID of the database you want to examine.
fileid: The file ID of the transaction log of the database being examined.

DB Admin
Q25: What is the difference between DBCC INDEXDEFRAG and DBCC REINDEX?

Answer:

Unlike DBCC DBREINDEX or any general index build, DBCC INDEXDEFRAG is an online operation, so it does not hold long-term locks that can block running queries or updates. Depending on the amount of fragmentation, DBCC INDEXDEFRAG can be considerably faster than running DBCC DBREINDEX due to the fact that a relatively unfragmented index can be defragmented much faster than a new index can be built. Also, another advantage is that with DBCC INDEXDEFRAG the index is always available, unlike DBREINDEX. Note however, that a large amount of fragmentation can cause DBCC INDEXDEFRAG to run considerably longer than DBCC DBREINDEX, which may or may not outweigh the benefit of the command's online capabilities. Also, DBCC INDEXDEFRAG will not help if two indexes are interleaved on the disk because INDEXDEFRAG shuffles the pages in place. To improve the clustering of pages, rebuild the index.

DB Admin
Q26: Can you detach a SQL Server 7.0 database and attach it to a SQL Server 2000 server?

Answer:

Yes. SQL Server 7.0 databases are compatible with SQL Server 2000. However, exceptions do exist. For a list of these exceptions, see the SQL Server 2000 Books Online topic "Database Upgrade from SQL Server 7.0". Note that attaching a SQL Server 7.0 database to SQL Server 2000 automatically upgrades the SQL Server 7.0 database to a SQL Server 2000 database, and the database is then no longer usable by the SQL Server 7.0 installation.

DB Admin
Q27: How do I increase the number of SQL Server Errorlogs?

Answer:

In SQL Server 2000, in the Enterprise Manager, right-click the SQL Server Logs under the Management folder and select Configure. Enter the desired number in the textbox titled "Maximum Number of Error Log Files."

In SQL Server 7.0, you must add an entry in the registry.

- Because this command changes the registry, Microsoft recommends that you back up your changes. You can do this by running the command-line utility Regrebld.exe with the -Backup parameter. For example:
D:\MSSQL7\BINN>regrebld -Backup

- The new registry key that you can set is:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\NumErrorLogs

By default, this key is absent. The value is of the REG_DWORD type. Modify the value to the number of logs that you want to maintain.

DB Admin
Q28: How do I configure the client query time-out for SQL Server?

Answer:

If you are using a utility then this is normally under file/options or tool/options from the menu. Set it to the value you require - 0 means infinite (no) timeout.

For example :

Enterprise Manager time-out is set in TOOLS/OPTIONS/Connection/Query Timeout
Query Analyzer time-out is set in QUERY/Current Connect Options/Query Timeout

DB Admin
Q29: How do I change the default database location for new database files?

Answer:

The following registry key:

HKLM\Software\Microsoft\MSSQLServer\MSSQLServer

contains two values. One for the data files and one for the log files.

Data files: DefaultData
Log files: DefaultLog

Set the key values to the location of the folder.
Of course, using the EM GUI allows you to specify a location for the files

DB Admin
Q30: How can I tell if my server is scheduled for a reboot?

Answer:

From a command prompt, type:

c:\AT \\servername

AT comes with Windows 2000.
You will need rights to the <servername> specified
It will return any schedules defined. 

DB Admin
Q31: How to create a job category in Enterprise Manager

Answer:

To create a job category Expand a server group, and then expand a server.

Expand Management, and then expand SQL Server Agent. Right-click Jobs, point to All Tasks, and then click Manage Job Categories.

In the Job Categories dialog box, click Add. In the Name box, enter a name for the job category.