Archive for category Bugs

Databases in SIMPLE recovery mode may not really be set to SIMPLE

In SQL 2012 if your model database is set to SIMPLE recovery and you create a new user database you expect the recovery model of the user database to be automatically set to SIMPLE. In fact, sp_helpdb and the sys.databases reports that the recovery model is SIMPLE. However, if you backup the database and then backup the transaction log you’ll be surprised that the last action succeeds. If you do this in earlier SQL versions the backup log reports an error stating that such an operation is not permitted for databases with SIMPLE recovery model.

Typically for development servers you’d set model to SIMPLE. Databases which have inherited this recovery model will show ever increasing transaction log files. If you manually CHECKPOINT such databases you should observe that the log space used never decreases.

One workaround is to manually toggle the recovery model. That is, use the ALTER DATABASE to set it to FULL and then run it again but this time set it to SIMPLE.

The following Connect item has been raised for this issue:

https://connect.microsoft.com/SQLServer/feedback/details/765315/when-model-db-is-set-to-simple-recovery-newly-created-databases-are-not-truely-in-simple-recovery

Leave a comment

Incorrect query worker times for SQL Server 2005

The worker time columns in sys.dm_exec_query_stats will report incorrect values for queries that use parallelism. By comparison SQL traces will report correct values in the CPU column. Microsoft do not provide a fix for this for SQL 2005. See this Connect post:

http://connect.microsoft.com/SQLServer/feedback/details/487838/cpu-time-sys-dm-exec-query-stats-vs-set-statistics-time-on

The work time column values are correct for non-parallel queries.

Leave a comment