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

SQL Server changes behavior of the file autolocation invoked during the planning phase of a full restore with REPLACE clause

A colleague reported the following concern from one of the customers he works with: We just ran into a small issue with our 2012 upgrades.  In older versions of SQL, a RESTORE DATABASE command using the “WITH REPLACE” option worked as long as the logical file names matched even if the file paths were different. …

3

How the density and distribution of data in the leading key column of an index affects the degree of parallelism chosen for index operations targeting that index?

There’s a topic in SQL Server’s documentation whose title is Configuring Parallel Index Operations, which mentions that the Database Engine can reduce the degree of parallelism if the leading key column of a non-partitioned index has a limited number of distinct values or the frequency of each distinct value varies significantly. What does that exactly…

3

DBCC DBRECOVER finally removed from SQL Server

For those of you who relied on that command, typically used in data recovery learning labs, just wanted to warn you, if you haven’t noticed already, that it has been removed from the product starting with SQL Server 2012 RTM. The supported way to do what that undocumented DBCC command did would be using ALTER…

3

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

VDI Backups and Backup Compression Default

This week I was in a customer measuring the effects it would have, in one particular environment where they were using SQL Server 2008 R2 Enterprise, if they would compress their backups. To start with, we ran a few tests using BACKUP DATABASE & BACKUP LOG T-SQL statements and controlled various figures along each test…

0

Backup Compression and Checksum

Notice that when I wrote this repro and captured the screenshots available below, I didn’t think they will be rendered in this blog. Unfortunately, you will have to click on each image to be able to read the descriptions of every relevant chunk of data highlighted in the raw data stream. A while ago Paul…

10

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