My blog has moved…

I was tired a bit of all these restrictions of free version of wordpress.com blog. So I has moved it to a self-hosted wordpress.org blog.

My new blog is http://andreyzavadskiy.com

Advertisements

Offline Help for SQL Server 2014

To use Help in SQL Server 2014 in offline mode you should perform the following steps:

1) install Documentation Components – at least you need the Help Viewer to see the content of help library

Documentation components

2) download the installer from Microsoft site Product Documentation for Microsoft SQL Server 2014 for firewall and proxy restricted environments

3) run the installer SQLServer2014Documentation_August2014_EN.exe

4) unzip files to a folder (I prefer to manually create a folder and unzip files there)

5) launch Help Settings

This can be made from Windows Start menu (Microsoft SQL Server 2014 -> Documentation & Community -> Manage Help Settings) or from SQL Server Management Studio (Help -> Manage Help Settings)

6) click Install content from disk in the Help Library Manager window

Install content from disk

7) locate the folder to which you have unzipped the files at step 4 and select the file HelpContentSetup.msha, then press Open

8) press Next in the Help library Manager

9) click the links in Actions column to select the desired sections, then press Update

Help sections

10) after you’ll see a message “Finished updating” press Finish button, then press Exit

11) now you can delete the folder with unzipped files

Tip: to check that you are really using the local help start Manage Help Settings again, click the first link Choose online or local help, and ensure the setting is correct.

Local help setting

View Instance and Database Versions

You can get the following characteristics about your SQL Server:

  • product version (for example, 12.x for SQL Server 2014)
  • product level (RTM, Service Pack or CTP)
  • edition (standard, enterprise, developer, etc.)
SELECT 
  SERVERPROPERTY('ProductVersion') AS [Product Version],
  SERVERPROPERTY ('ProductLevel') AS [Product Level] ,
  SERVERPROPERTY ('Edition') AS [Edition];
Product Version      Product Level        Edition
-------------------- -------------------- ------------------------------
12.0.2495.0          RTM                  Developer Edition (64-bit)

(1 row(s) affected)

The full list of server properties can be seen in MSDN article SERVERPROPERTY (Transact-SQL)

For the database you can get the current internal version number and compatibility level. With some extra effort you can get starting database version number, i.e. the version at which the database was created originally.

SELECT DATABASEPROPERTYEX('test', 'Version') as [Internal Version Number];

SELECT COMPATIBILITY_LEVEL 
FROM sys.databases
WHERE NAME = 'test';
Internal Version Number
------------------------------
782

(1 row(s) affected)

COMPATIBILITY_LEVEL
-------------------
120

(1 row(s) affected)

To see the starting internal databse number you shoud use DBCC DBINFO command.

DBCC TRACEON(3604);

DBCC DBINFO('test');
DBINFO STRUCTURE:

DBINFO @0x000000000E29D8E0

dbi_version = 782                   dbi_createVersion = 661             dbi_SEVersion = 0
dbi_dvSplitPoint = 0:0:0 (0x00000000:00000000:0000)                      
dbi_dbbackupLSN = 0:0:0 (0x00000000:00000000:0000) 
...

Look at the first line starting with dbi_. The dbi_version is the current internal database version number (782 is SQL Server 2014), the dbi_createVersion is that of the moment of database creation (661 is 2008 R2). The list of these numbers can be found at this post.

Implicit Database Upgrade and Internal Database Version Numbers

When you run a database from previous version on a SQL Server instance with a higher version, the database is automatically upgraded to the version of the instance. It happens on:

  1. attaching a database from older version;
  2. restroring a database from older version;
  3. and, of course, when you make an in-place upgrade of SQL Server.

For example, when I attach a database from SQL Server 2008 R2 in SQL Server 2014, I’ll get these messages telling about database upgrade.

Converting database 'test' from version 661 to the current version 782.
Database 'test' running the upgrade step from version 661 to version 668.
Database 'test' running the upgrade step from version 668 to version 669.
...
Database 'test' running the upgrade step from version 781 to version 782.

The version numbers you see in Messages window is internal database version numbers. The list of these numbers can be found at this post.

Be aware this is a one-direction upgrade. There’s no possibility to downgrade the database nor open it in SQL Server with original version or any intermediate version prior to upgraded one. This means that if your database was created in SQL Server 200, then you opened it in SQL Server 2014, you never can open in SQL Server version from 2005 till 2012. The error message is self-explanatory.

Msg 1813, Level 16, State 2, Line 1
Could not open new database 'test'. CREATE DATABASE is aborted.
Msg 948, Level 20, State 1, Line 1
The database 'test' cannot be opened because it is version 782. This server supports version 663 and earlier. A downgrade path is not supported.

Another issue is that you can NOT prevent SQL Server from upgrading the database. If you make the primary database file read-only in order to get a read-only database and try to open it in a higher version, you’ll get an error 3415.

Msg 3415, Level 16, State 2, Line 3
Database 'test' cannot be upgraded because it is read-only, has read-only files or the user does not have permissions to modify some of the files. Make the database or files writeable, and rerun recovery.

Windows 8.1 Touchpad Driver for ASUS Notebooks

I’m working on Asus K53sd notebook. After upgrade to Windows 8.1 (mainly to use Hyper-V) I’ve faced some problems with touchpad – I can’t disable it in gracious manner except disabling it totally at driver settings level. I’ve tested all drivers that are listed on product support page. Beside that I could not disable touchpad when I plugg in a mouse.

It took me some time to find the right link. It’s a page devoted to touchpad. And it really works!

I don’t know why they did not include it in product page. Maybe it’s a question for some scientists 😉

Link to Asus touchpad driver http://support.asus.com/Download.aspx?SLanguage=en&m=Touchpad&os=30

Hope the driver for Windows 10 will be there…

Ghost Transaction Log File

I’ve encountered a very intriguing case with transaction log files in SQL Server 2014. Maybe it is present in earlier version.

I open a database properties, click Files and then I see two transaction log files. But when I look at Windows folder I see only one. Is it funny?

Ghost log file

Now I’ll tell you how it was happened.

I’ve created a database with a fixed size transaction log file, then add a second log file. I’ve inserted some stuff data, made some actions for the second log files would be truncated. After that I’ve removed the second log file.

As a result physical log file was really deleted, but it’s still present in SQL Server. When you look at sys.database_files you can notice that state for the second log file is different from the first.

Ghost log file state

Script to repeat this case Ghost transaction log file (change .doc extension to .sql)

Missing Performance Monitor Counters for SQL Server 2014

I’ve encountered a strange situation when I could not find SQLServer:Databases group of counters in Windows Performance Monitor.

Missing PerfMon counteres

It was happened on my test system with SQL Server 2014. I checked it on production SQL Server 2008 R2 – everything is OK.

The most important thing to solve this problem was found in Windows Application Log. It was error 8310.

Error 8310

Simple search in Google with the error message leads me to the description of that particular problem. It’s on SQL Server 2014 installation and is corrected by installing Cumulative Update 2. I’ve installed CU7 and now it works.

You can check all CUs in KB2936603 SQL Server 2014 build versions

The latest Service Pack can be checked in KB2958069 How to obtain the latest service pack for SQL Server 2014