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

What’s the meaning of different States for error 9002?

I just happened to find that when the log manager raises error 9002 “The transaction log for database ‘%s’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases” the State can vary and is loaded with meaning. The value reflects whatever was the holdup reason…

1

How much is crash recovery parallelized? In which order are databases recovered?

Following is the answer I provided to the interesting question asked by my colleague Pat Martin about the insides of crash recovery in SQL Server.   The iterator used to scan the catalog for the databases that need startup and recovery returns them sorted by database id. So, if you want a database to start…

4

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

Inaccurate values for “Currently allocated space” and “Available free space” in the Shrink File dialog for TEMPDB only

Last week I went to a customer who showed me the following weird information.   He opened SSMS (SQL Server Management Studio) 2008 R2 and connected to one particular instance of SQL Server 2008 R2 in which he observed this behavior we wasn’t able to reproduce with any other.   From the Object Explorer window,…

2