Why does restoring a database needs TempDB?

Or the chicken and egg problem when attempting to restore a corrupted model database as it was presented by my MCM colleague Gail Shaw the other day in our MCM discussion group. This is the case he presented us with: Hi all Something I’ve been puzzling over recently. Restore database appears to need or use…

3

FAQ around sys.dm_db_index_usage_stats

For those of you who may not be familiarized with this object, sys.dm_db_index_usage_stats is one of the many Dynamic Management Views built into SQL Server. This one specifically falls into the category index related DMVs, and it returns counts of different types (scans, seeks, and lookups) of index operations, triggered by user or system initiated…

2

Want your SQL Server to simply ignore the AUTO_CLOSE setting, for all open databases for which it has been enabled?

Under normal circumstances, once there is no one referencing any object from a database whose AUTO_CLOSE option is enabled, the database manager would attempt to close it (with the exception of master, tempdb and model system databases). But if you want a given instance of SQL Server to simply skip closing any database whose AUTO_CLOSE…

0

The Read Ahead that doesn’t count as Read Ahead

As you may know from having read the documentation, whitepapers, or personal synthetic experiments, SQL Server’s Storage Engine supports a performance optimization mechanism named read-ahead (RA for short). Its aim is anticipating the data and index pages needed to fulfill a query execution plan and bringing those pages into the buffer pool (cache) before they…

0

What is the expected behavior from an attempt to enable a trace flag which is not defined in the targeted version of the product?

Imagine, for example, that you run DBCC TRACEON(1117, -1) to globally enable trace flag 1117 on an instance of SQL Server 2005, where it had no effect because nowhere in the Database Engine’s code honored such flag. We could then say trace flag 1117 was in a “reserved for future use” state in SQL Server…

2