Category Archives: SQL Server Errors

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.
Advertisements

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 defragmentationjob 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 INDEXIX_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 INDEXIX_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 FROMDatabaseList INTO @DBName
             END
            CLOSE DatabaseList
            DEALLOCATE DatabaseList
—–Update allow_page_locks option for those indexes where we need 
 SET@IndexCount=(SELECTMAX(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=@IndexCount1
      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.