Figure 1 shows the possible results and their interpretations. These codes are for Intel systems running SQL Server 6.5. Alpha systems have different service packs, and SQL Server 6.0 for Intel, Alpha, and MIPS also had three service packs for each platform.
After what appears to be a normal shutdown of SQL Server 6.5 Service Pack 4 (SP4), I see in the error log on startup that the server still recovers transactions on my database. When the shutdown occurs, no transactions are open and no users are connected. Why is SQL Server rolling transactions forward? Where did they come from?
According to SQL Server Books Online (BOL), SQL Server issues an implicit CHECKPOINT command whenever you stop SQL Server normally with the
SHUTDOWN command or from the service control manager. Following a normal shutdown (a quiesce, which includes a CHECKPOINT), SQL Server usually doesn't authenticate any more logons but lets transactions in process finish and COMMIT. The portion of the transaction (modifications, commit record) that follows the system CHECKPOINT on shutdown will reside on the transaction log until the next
time you start the server. The server always goes through this recovery process
on restart, no matter how it was shut down previously. It will always do a
backward repair, forward repair, roll back, and roll forward, as long as the
transaction log is intact. In other words, what you're observing is normal.
Briefly, the likely cause of SQL Server's always rolling forward one
transaction when SQL Server starts is a database that has the trunc.log on
chkpt option enabled. This option causes the database's transaction log to
be truncated each time the checkpoint checking process issues a CHECKPOINT. And
because SHUTDOWN performs a CHECKPOINT before stopping SQL Server, it truncates
the log at that time. A CHECKPOINT keeps track of all uncommitted transactions,
so the CHECKPOINT record in the log shows the dump process from the log
truncation as an open transaction when the CHECKPOINT was issued.
When you restart SQL Server, it scans the transaction log of each database,
rolls back any uncommitted transactions, and rolls forward transactions that
completed but didn't write to disk during the last CHECKPOINT. When SQL Server
encounters the CHECKPOINT record that shows the dump process as open, SQL Server
rolls forward that transaction to show that it had completed. That roll-forward
action is the source of the message. Microsoft Knowledge Base article Q81340
(http://support.microsoft.com/support/kb/articles/q81/3/40.asp) explains
what happens.
You can create devices on an NTFS partition very quickly, but creating the same device on the same disk using a FAT partition takes forever. Is something wrong with my system?
The difference in speed on the partitions is no cause for worry, but you
can't do anything about it. The difference in speed is a result of the
differences between NTFS and FAT. When SQL Server creates a device, it zeros out
all the space on the device file. Unlike FAT, NTFS doesn't need to physically
rewrite the entire file to ensure that it's clean, so creating a device on an
NTFS partition usually takes seconds, regardless of how big the device is.
However, creating a device on a FAT partition could take from minutes to hours,
depending on the size of the partition and the speed of your disks. We recommend
that you use the NTFS file system, not only because of its speed, but because
NTFS is the file system of choice for BackOffice servers.
What causes a SQL Server 6.5 Service Pack 4 (SP4) system to show configuration options such as symmetric multiprocessing (SMP) concurrency and remote authentication (RA) options in the Server Configuration tab?
Some options within sp_configure are considered advanced, and you can view
them only if you've enabled the show advanced options setting by running
EXEC sp_configure "show advanced options", 1
RECONFIGURE
This setting lets you see all the configuration options--whether you're
viewing them from the sp_configure Transact SQL (T-SQL) interface or the SQL
Enterprise Manager (EM) GUI interface (which you bring up by right-clicking
Server in SQL EM). Be careful about who has access to these settings. They're
the SQL Server equivalent of the Registry.
Can I install SQL Server on a Windows NT Workstation system?
NT Workstation 4.0 and NT Server 4.0 are legally distinct, and so are SQL
Workstation 6.5 (which costs only $499) and SQL Server 6.5. You can physically
install SQL Server on an NT Workstation system, but this installation isn't
legal. When you mix entities, you're constrained by NT Workstation's
limitations--a maximum of two processors, 10 concurrent users, and one
dial-in connection. You can't use an NT Workstation system as a Primary Domain
Controller (PDC) or Backup Domain Controller (BDC). Windows knows whether a
system is running NT Server or NT Workstation from Registry settings.
To install SQL Server on a standalone NT Workstation system, select the
Microsoft Loopback Adapter in place of an NIC. In SQL Server 7.0, however, you
won't be able to install the server edition on an NT Workstation system.
Our development house has an ISDN connection between our site and the client. The problem is, whenever we start SQL Enterprise Manager (EM), it kicks in the router and keeps the connection open. How can I prevent this? Surely we don't need to register and unregister the servers every time! I'm concerned about very large phone bills.
SQL EM wants to check the status of the SQL Server services so it can show
the stoplights properly. In your Registry, under HKEY_CURRENT_USER/Software/Microsoft/MSSQLServer/SQLEW/PollingInterval, you probably have a
key called EnableServerPolling set to a 1. Our sources at Microsoft say they
think that if you change it to 0, you'll turn off that check.
Recently I got an Error 2620 (Severity Level 21) and had to reload my database. What happened?
In the spring, Microsoft posted Knowledge Base article Q165166, "FIX:
Load Tran May Cause 806, 605, 2620, or 6902 with IRL On" (http://support.microsoft.com/support/kb/articles/q165/1/66.asp), describing an unfortunate situation that can occur if SQL Server has Insert Row Locking on and stops while it is loading a transaction log. The database is left in an unknown state, and you'll probably get an Error 2620: The offset of the row number at offset %d does not match the entry in the offset table of the following page when you
restart SQL Server.
Because the process of loading the transaction log (which copies database pages and recovers the database) was interrupted, SQL Server can't guarantee the state of the current database because the reload wasn't completed. The solution: Reload from a known database state. Microsoft fixed the problem in SQL Server 6.5 Service Pack 3.
End of Article
Prev. page
1
[2]
next page -->
