Compression, Log Files, and More

Can I use a compressed drive or NTFS compression with SQL Server data files?

Storing data files on a compressed drive or file can prevent database recovery. Never store data on drives or in directories with compression enabled.

What's the difference between a fillfactor of 0 and a fillfactor of 100?

If you specify a fillfactor of 0 or 100, SQL Server 7.0 completely fills the leaf-level pages. If you specify a fillfactor of 0 in SQL Server 6.5, SQL Server leaves a default space for 1 or 2 index entries on each leaf-level page. To completely fill the leaf-level pages in SQL Server 6.5, you need to specify a fillfactor of 100.

I see SPID 6 on my sp_lock output in SQL Server 7.0. What is it?

SPID 6 is the background task manager thread. If you use row locks to delete rows, SQL Server marks the rows as deleted, then SPID 6 physically deletes the rows and reorganizes the page appropriately. SPID 6 is also responsible for shrinking a database.

Can I mount SQL Server 7.0 databases on a Zip or Jaz drive?

SQL Server Enterprise Manager doesn't see Zip or Jaz drives as local disks, so you won't see them in the list of available drives. However, you can use a Transact-SQL statement to create a database on a Jaz drive. For example, my system's E: drive is a Jaz drive, so I can enter

Create Database testdb on PRIMARY(Name = testdb_data, FILENAME =    'e:testdb_data.mdf', SIZE = 5MB)LOG ON (Name = testdb_log, FILENAME =    'e:testdb_log.ldf', SIZE = 2MB)

When does SQL Server write a log record physically to disk?

SQL Server writes a log record to disk when a transaction terminates (commit or rollback), when SQL Server issues a prepare (distributed transactions), or when the log buffer fills. SQL Server writes to disk a data or index page that has changes on it, if the changes have been recorded on one or more log records that weren't previously written to disk. Examples of such changes are a checkpoint and a low-memory condition.

What's the difference between DBCC DBREINDEX and CREATE INDEX WITH DROP_EXISTING?

First, a general rule: Use CREATE INDEX WITH DROP_EXISTING instead of the Database Consistency Checker (DBCC) command DBCC DBREINDEX. Microsoft doesn't guarantee that DBCC commands will exist in future releases of SQL Server.

The difference in your question is that the DROP EXISTING command assumes the index is valid, so it can rebuild quickly using less space. The DBREINDEX command drops the existing index, then kicks off a total rebuild of the index. Microsoft included this command in SQL Server 7.0 to rebuild a corrupted index. DBCC DBREINDEX rebuilds the nonclustered indexes because the uniquifier (for nonunique clustered indexes) may change. DROP_EXISTING avoids the uniquifier if possible to prevent nonclustered index rebuilds. Use DBCC DBREINDEX only if you can't use CREATE INDEX WITH DROP_EXISTING.

I created a table that contains a varchar(8000) field. When I insert data that has more than 255 characters, the table looks fine. However, when I view the table through Query Analyzer, I see only 255 characters. How can I view this data?

You can't see the data if you use ISQL/w from SQL Server 6.5 or earlier because ISQL/w is a DB-Library application restricted to 255 bytes. The Query Analyzer tool in SQL Server 7.0 is an ODBC app that can view up to 8000 bytes. To use it, select Current Connection Options in the Query menu. On the Advanced tab, increase the maximum characters per column from the default of 256.

I don't understand this error message I received: The license for the installation of MS SQL Server on your source and destinations connections does not permit the use of DTS to transform data. Refer to your license for more information.

The SQL Server 7.0 Desktop edition can talk only to SQL Server servers (Standard or Enterprise Edition) that run in per-seat mode. Microsoft documented this condition in the license agreement. The software enforces the restriction. The restriction applies to Data Transformation Services (DTS), replication functions, and distributed queries.

How can I find out when two-digit year conversions take effect in SQL Server?

Look at Microsoft article "INF: Trace Flag 8816 to Help Year 2000 Conversion" (http://support.microsoft.com/ support/kb/articles/q198/4/16.asp?FR=0), which reads in part: "Trace flag 8816 may help application designers and Year 2000 consultants find Year 2000 bugs in older applications. This trace flag logs every two-digit year conversion to a four-digit year." For example,

DBCC TRACEON(8816)goSET DATEFORMAT mdygoCREATE TABLE BirthDay(Name nvarchar(50),   BirthDate datetime)goINSERT BirthDay(Name, BirthDate)VALUES('Smith, Joe', '7/30/77')

This example will print the following line in the errorlog: 1998-12-22 18:36:51.23 spid7 2-digit year 77 converted to 1977.