SQL Server: SET SET, A Strange Compatibility Upgradation Issue

During upgradation of a client database from SQL Server 2000 (Compatibility Level 80) to SQL Server 2005 (Compatibility Level 90), I have found that there are changes which need to apply for compatibility level 90, that are still not documented. Like everyone knows that *= or =* type joins are not acceptable in compatibility level 90. But what about following simple code.

USE [master]

GO

ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 80

GO

USE [AdventureWorks]

GO

DECLARE @MyVar INT

SET SET @MyVar = 5
Executing above code with compatibility  level 80 doesn’t generate any error but when we execute same code with COMPATIBILITY_LEVEL =90, it will generate following error.

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.

SQL Server Errors: ORDER BY items must appear in the select list if SELECT DISTINCT is specified

SQL Server force you to put columns in SELECT DISTINCT list which are part of ORDER BY clause. But what if we don’t want to add that column/s in SELECT list. Lets try it.

--create temporary table to hold records

CREATE TABLE #DistinctSortTest (Val1 INT, Val2 INT)

GO

--insert some records

INSERT INTO #DistinctSortTest

VALUES (1,100),(8,55),(3,33),(1,1),(9,999)

GO

--lets see what we have in table

SELECT * FROM #DistinctSortTest

GO

 
From this table we need only “Val1” column with distinct values BUT sorting output with “Val2”. Lets try simple query.

SELECT DISTINCT Val1

FROM #DistinctSortTest

ORDER BY Val2

Opps. Error

Msg 145, Level 15, State 1, Line 1

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

To resolve this problem, we can use GROUP BY clause with MIN()or MAX() function in ORDER BY clause. 

SELECT  Val1

FROM #DistinctSortTest

GROUP BY Val1

ORDER BY MIN(Val2)

Through GROUP BY (all select columns) we will achive functionality of DISTINCT and MIN()/MAX() functions for sorting. MIN() in Order By clause can be used for ASC sort and MAX() for DESC sort.

--drop temporary table when not required

DROP TABLE #DistinctSortTest

SQL Server : @@VERSION Showing Incorrect Service Pack Information

Recently, due to SQL Server Log message The time stamp counter of CPU on scheduler id 13 is not synchronized with other CPUs. we have decided to resolve it by installing Service Pack 3 for SQL Server 2005. To confirm, that target server has version prior to Service Pack 3, I have used following simplest query.

SELECT @@VERSION

From query result it became sure that said server has Service Pack 2 installed and need to be upgraded to Service Pack3. 
On receiving confirmation mail from Systems department that said instance is upgraded, I went for same above query to verify and surprisingly, it was still showing the same result with Service Pack 2.

For further confirmation I have used a different query (SERVERPROPERTY('PRODUCTLEVEL') and found actual results (i.e. Service Pack 3). 

Basically, it was a simple misconception. Till date i thought @@version shows patch information of SQL Server, but actually it shows operating system patch.

SQL Server Log: DeviceIoControl failed, error 21

This morning in log report of one of our production server I found an unusual  error.

DeviceIoControl failed, error 21

DeviceIOControl is a function which sends code to your disk drivers to perform read or writes functions and error 21 clearly indicates that device which we have specified to perform a job is not ready.

This happened because, on our production server, we had configured a backup job which takes daily full backup to a removable device and unfortunately it was removed by our systems department, which caused this error.

------------------------------------------------------------------------------------

Read More about SQL Server Log Errors/Messages 

• SQL Server has encountered 1 occurrence(s) of cachestore flush
• This instance of SQL Server has been using a process ID of xxxx since mm/dd/yyyy
• SQL Server has encountered occurrence(s) of I/O requests taking longer than 15 seconds to complete
• The time stamp counter of CPU on scheduler id 13 is not synchronized with other CPUs.
• I/O is frozen on database DatabaseName

SQL Server: Object Validation Error by Estimated Plan While Actual Plan Working Fine

It happened dozen of times when I tried to get an Estimated Execution Plan, it return object verification error, while the same Stored Procedure was working perfectly. Then what’s wrong with Estimated Execution Plan, lets create a simple procedure.

CREATE PROCEDURE Proc_TestProcedure

AS
BEGIN
      IF 1 = 2
            SELECT * FROM NoTable -- NoTable doesn't exists
      ELSE
      SELECT 'ESLE'
END
GO

On compilation and execution, above stored procedure will not return any error, because condition is never true, so it never need to exec SELECT * FROM NoTable, and to check that table exists or not. Now just try to get execution plan of

EXEC Proc_TestProcedure

It will return error.

Msg 208, Level 16, State 1, Procedure Proc_TestProcedure, Line 8

Invalid object name 'NoTable'.

Why So.

Because on estimation, query optimizer check each and every statement separately and once it try to estimate cost for “NoTable”, which never exists, it returns above mentioned error.

SQL Server: Restore Failure from Enterprise to Standard Edition

Yes you can restore a backup file which is taken from SQL Server Enterprise Edition to Standard Edition BUT if source database is not using Enterprise Edition Specific Features.  Like partition functions in our case.

This happened to one of my junior, when he was restoring a database (on SQL Server 2008 R2 Standard Edition) from full backup, which was taken from SQL Server 2008 R2 Enterprise edition. 6 GB database backup file took more than half an hour to restore and after showing 100 percent completion, on starting database it returned FAILURE ERROR.

Database 'SQL2008R2_Ent_PartitionFunction' cannot be started in this edition of SQL Server because it contains a partition function 'SSF_PF_Right'. Only Enterprise edition of SQL Server supports partitioning.

Database 'SQL2008R2_Ent_PartitionFunction' cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 905)

Basically restore process is completed successfully and you can see database name in list and its files on required location but in inaccessible mode.

I think Microsoft should improve this process and these prerequisites should be checked first.

SQL Server: Restore Failed, Logical file ‘xxxx’ is not part of database 'xxxx'.

Problem occurs when we try to restore a database from backup set, but using MOVE option and when you provide wrong logical name of file in MOVE section of RESTORE script.
To avoid this problem, first, one must verify original logical names of files by using following  RESTORE FILELISTONLY .

Now if  somehow I use wrong logical file name, it will return error.

Correct the logical names, as per RESTORE FILELISTONLY output and it will work fine.

RESTORE DATABASE [TraceDB2]
FROM  DISK = N'D:\temp.bak' WITH 
 MOVE 'TraceDB' TO 'd:\TraceDB2',
  MOVE 'TraceDB_logw' TO 'd:\TraceDB2_log',
  FILE = 4,  NOUNLOAD,  REPLACE,  STATS = 10

SQL Server: Script to Fix ALLOW_PAGE_LOCKS Option for All indexes on All Databases

Recently we have found that our index defragmentation job is failing on a production server, due to REORGANIZE   failure of one of our index. SQL Server was unable to REORGANIZE this index because mistakenly we have an index with the ALLOW_PAGE_LOCKS options set to OFF

What is Page Lock Option?

According to BOL, If ALLOW_PAGE_LOCKS option is set to ON, it means Page locks are allowed when accessing the index. The Database Engine determines when page locks are used.

If ALLOW_PAGE_LOCKS  option is set to off following query will return an error.
ALTER INDEX IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
ON Person.Address REORGANIZE

The index "IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode" (partition 1) on table "Address" cannot be reorganized because page level locking is disabled.

You can correct it by simply updating ALLOW_PAGE_LOCKS option to ON with the help of following query

ALTER INDEX IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
ON  Person.Address
SET (ALLOW_PAGE_LOCKS = ON);

To make sure to avoid this problem in futur for all datbases on a instance, Amna Asif has suggested following simple but efficient script to detect indexes with ALLOW_PAGE_LOCKS option set to OFF and automatically fix this problem for all of your databases on an instance.

/***********************************

Script By: Amna Asif
Purpose : To fix ALLOW_PAGE_LOCKS option on
                 all indexes of all databases on a particular instance
***********************************/
USE MASTER
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO

DECLARE @DBName NVARCHAR(255)

DECLARE @IndexCount int
DECLARE @UpdateIndexQuery Varchar(500)
--- Table variable to hold intermediate result set
            DECLARE @IndexsInfo TABLE
            ( 
            RowNo int identity(1,1),
            DatabaseName varchar(100),
            TableName varchar(100),
            IndexName varchar(100)
            )
--- Cursor to work on each changeable index of each db on an instance
      DECLARE DatabaseList CURSOR 
                  FOR
                    SELECT Name
                    FROM sys.databases
                    WHERE state_desc = 'ONLINE'
                    AND is_read_only = 0
                    ORDER BY name
      OPEN DatabaseList
             FETCH NEXT FROM DatabaseList INTO @DBName
             WHILE @@FETCH_STATUS = 0
             BEGIN   
               INSERT INTO @IndexsInfo (DatabaseName,TableName,IndexName)
               EXEC( '  SELECT '''+@DBName+''' AS DatabaseName,TABL.NAME AS TableName,indx.NAME AS IndexName     
                              FROM '+@DBName+'.SYS.INDEXES indx
                              LEFT OUTER JOIN '+@DBName+'.SYS.TABLES TABL ON indx.[OBJECT_ID]=TABL.[OBJECT_ID]
                              WHERE ALLOW_PAGE_LOCKS = 0           -- where page lock option is not selected
                              AND indx.name NOT LIKE ''QUEUE%''  ' -- we need only user defined indices
                        )
            FETCH NEXT FROM DatabaseList INTO @DBName
             END
            CLOSE DatabaseList
            DEALLOCATE DatabaseList
-----Update allow_page_locks option for those indexes where we need 
 SET @IndexCount=(SELECT MAX(RowNo) FROM @IndexsInfo )
      WHILE @IndexCount >0  
      BEGIN
          SET @UpdateIndexQuery=( SELECT ' ALTER INDEX '+ IndexsInfo.IndexName +' ON ['+
            IndexsInfo.DatabaseName+'].dbo.['+IndexsInfo.TableName+']
            SET (
                  ALLOW_PAGE_LOCKS = ON
                  ) ; '
            FROM @IndexsInfo AS IndexsInfo
            WHERE IndexsInfo.RowNo=@IndexCount)
     
            EXEC(@UpdateIndexQuery)
        
           SET @IndexCount=@IndexCount-1
      END

SQL Server: Incorrect PFS free space information for page (x:xxxx) in object ID xxxx

One can check integrity issues by using DBCC CHECKDB (“DatabaseName”) and unfortunately if DBCC CHECKDB returns some type of database corruption, then it can be solved by executing DBCC CHECKTABLE. According to BOL “DBCC CHECKTABLE: Checks the integrity of all the pages and structures that make up the table or indexed view.”

Most of the time it works fine for me at least, but last week it returned a different error and failed to fix it.

DBCC results for 'MyTable'.

Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:6294) in object ID 1325247776, index ID 1, partition ID 72057594860535808, alloc unit ID 72057594366853120 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
 

Error message clearly showing that there is no actual corruption for said page. You can call it just information that PFS (Page Free Space) entry has wrong calculation for free space in page. Though PFS showing that page is empty (0%), but in reality page is full (100%). And it is misleading free-space scanner, which finds it full when try to insert data.

You can live happily with this error, but if you have applied a job to execute DBCC CHECKDB then you will keep on receiving job failure notice.
 
How to resolve it:

Three methods, first, you should restore database from latest backup, which is error free. And second method is to create a replica of culprit table, insert data into it, delete existing (culprit table) and start enjoying new error free table.

(Once data copied to new table and verified, don’t forget to check further errors by executing DBCC CHECKDB.

Before trying above two methods first go for third one. i.e. DBCC PAGE

DBCC PAGE ('YourDatabaseNameHere', 1, 6294, 1)

SQL Server has encountered occurrence(s) of I/O requests taking longer than 15 seconds to complete

This morning, while going through my regular SQL Server Logs reports, for one of our production server, I found a different error.

SQL Server has encountered 52 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TempDB.mdf] in database [TempDB] (2). The OS file handle is 0x00000884. The offset of the latest long I/O is: 0x00000457490000

First thing that I searched about this error was that is this a critical message?
And answer I found was YES. Basically when talk about I/O in SQL Server, we always have measurements of mille seconds in our mind and waits of several seconds is considered too odd. SQL Server I/O wait time can be examined by following query:

SELECT  *

FROM    sys.dm_os_wait_stats

WHERE   wait_type LIKE 'PAGEIOLATCH%'

How to check you hard drive performance?

To check, server IO subsystems I trust on Performance Monitor IO Counter PhysicalDisk Object: Avg. Disk Queue Length. Monitor this counter for at least 10 minutes. If the Avg. Disk Queue Length exceeds 2 for next ten minutes for each individual disk drive in an array, then it is sure that you have IO bottleneck.

Who is the culprit, SQL Server or Operating System?

Problem is only your SAN or Local disk IO subsystem. In my case, I found that few other applications were also installed by client on same drive and which were pushing SQL Server to wait for too long to complete its IO requests.

------------------------------------------------------------------------------------

Read More about SQL Server Log Errors/Messages 

• SQL Server has encountered 1 occurrence(s) of cachestore flush
• DeviceIoControl failed, error 21
• This instance of SQL Server has been using a process ID of xxxx since mm/dd/yyyy
• The time stamp counter of CPU on scheduler id 13 is not synchronized with other CPUs.
• I/O is frozen on database DatabaseName