SQL Server 2005 upgrade to SQL Server 2014 and compatibility levels

A database currently attached to an instance of SQL Server 2005 could be either backed up (or detached), and later restored (or attached) on top of an instance of SQL Server 2014, and SQL Server 2014 won’t complain as it typically did in the past when you tried to restore (or attach) databases across database…

2

Do the 10GB DB size limit in SQL Server Express SKUs also apply to TempDB?

No, it doesn’t. The CheckSize function from the file manager class, which is called when a file is grown or shrinked skips checking for the limits imposed by the SKU when the file correspond to the master, tempdb, or model system databases. For further information about the “Maximum Relational Database Size” limit imposed by every…

0

Applying DELAYED_DURABILITY = FORCED on TEMPDB

  My PFE colleague Sam Mesel posted the following information a few days ago on an internal distribution group: I’m testing DELAYED_DURABILITY on TempDB Applying the following change on it does not give me any error message, but I see no performance improvements. ALTER DATABASE [tempdb] SET DELAYED_DURABILITY = FORCED Is this the expected behavior…

1

RESTORE VERIFYONLY: How does it check for available space on destination devices?

  “How does RESTORE VERIFYONLY checks for space on destination drives when a drive isn’t specified as part of the statement?” was the question being asked this time. And this was my answer to that one: This is what SQL does: In order to complete the space check, it iterates over all data files, transaction…

0

When doing a database restore, what actions is the engine taking after it gets past the 100%?

This was a question my admired Greg Low posted on the SQL Server MCMs discussion group a few days back, while he was restoring a 2TB database and noticed that the percent_complete column from sys.dm_exec_requests for the session from which the RESTORE statement was running, reached 100% and thirty minutes later, the restore hadn’t finished…

1

Is SQL 2014 Buffer Pool Extension an Enterprise Edition only feature?

No, it’s not. It’s on Standard too. If an attempt is made to enable it on any other edition, you’ll see error 865 written to ERRORLOG “Buffer pool extension is only supported on Standard and Enterprise Editions of SQL Server.” and the same exception is raised aborting the ALTER SERVER CONFIGURATION statement. It’s also worth…

0

Is eXtreme Transaction Processing (ie In-Memory OLTP) Supported?

Someone recently asked under which circumstances SQL Server 2014 would return false when interrogated for the IsXTPSupported server property. I looked into the source code and this is what I encountered there. For the boxed version of SQL14, that server property would return FALSE if: 1) lightweight pooling (fiber mode) is enabled, 2) the instance…

0

Does larger TEMPDB log file affect the performance of startup of SQL Server?

I invested some time today investigating what SQL Server does with the initialization of TEMPDB’s log file, because a question was raised last week during Robert Davis session on TEMPDB at SQL PASS Summit in Seattle, and I wasn’t sure about what the answer would be. The reason why people is afraid the size of…

3

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