Wednesday, June 20, 2012

SQL Server : Query Result Showing Incomplete Text

Sometime, simple and basic problem can trap experts. This happened last week with one of my senior, who was trying to generate some scripts and saving output scripts to an output text file. On execution of these scripts he found that for some queries text was not complete. He tried to get result in text format but problem was same.  

File output

If you are facing same basic problem of SQL Server Management Studio then no need to worry, as you just need to make some changes in SSMS options.
Go to TOOLS -- > OPTIONS -- > QUERY RESULTS -- > Result to Text -- > Maximum number of characters displayed in each column
Default value is 256, which is too less, update it to your desired length. You can extend it to 8192 characters maximum.

Friday, June 8, 2012

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

Thursday, June 7, 2012

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




Tuesday, June 5, 2012

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)

Friday, June 1, 2012

SQL SERVER Log: This instance of SQL Server has been using a process ID of xxxx since mm/dd/yyyy


If you call yourself SQL Server DBA, then you must be able to interpret SQL Server Log, One of a common information message captured by SQL Server Log is
This instance of SQL Server has been using a process ID of 2308 since 5/3/2012 2:30:52 AM (local) 5/3/2012 6:30:52 AM (UTC). This is an informational message only; no user action is required.

(process id and time would be different every time)

First thing to note about this log entry is that it’s just an information message and no user action is required. Mean NO NEED TO WORRY. This is just an information message that SQL Server instance using a process id (in my case it is 2308), since SQL Server services are started (in my case SQL Server services are started at 5/3/2012 2:30:52 AM) and after a month instance is still running.

SQL Server creates a log entry for this message on each date change. So you can find one entry for each 24 hours.

To verify this process id, open Task Manager and move to “Processes” tab, click on “View” in menu, “Select Columns”. Select PID (Process Identifier). Now it will start showing process identifier for each process. Check process identifier for sqlserver.exe






------------------------------------------------------------------------------------
Read More about SQL Server Log Errors/Messages