Error Fix: Msg 605 Attempt to fetch logical page (3:1307866) in database 6 failed. It belongs to allocation unit 72057594113359872 not to 72057594113490944

Msg 605, Level 21, State 3, Line 1
Attempt to fetch logical page (3:1307866) in database 6failed. It belongs to allocation unit 72057594113359872 not to72057594113490944.
Or
Msg 21, Level 21, State 1, Line 1
Warning: Fatal error 605 occurred at OCT  31 2011 6:08AM. Note the error and time, and contact your system administrator.
This error comes in red color is disguise, if you login withany user which is not sysadmin type user, you will see this kind of error incorruption of page(s), if you login with SA, Administrator or and otherSysAdmin login, you will not see error comes in red color but the descriptiveerror given in the title
There was a festival holidays in our Indian office from 26thto 28th OCT 2011 (Wed/Friday) so obviously I was out of town andback after 5 days on 31st OCT 2011. As soon as I come to the officeand checked few of my email, I suddenly get complain that few pages in oursoftware are throwing an error so I picked up the query which was there inthose pages along with the same parameter they were providing and found theerror given above.
As soon as I read this error, I came to know that there is apage corruption in database.  It may bedue to heavy snow fall in NJ in October, they have got power failure there fromfew days and tried to keep the server up and running via power generator. Assoon as Power Generator’s fuel get finished, everything gets down and afterrefuel, they starts everything again. I came to know this as soon as I comeback from holiday. I think this is the Indian New Year gift to me.
I tried to gather some more information about the data fileand page number given in error message with DBCC PAGE command.
dbcc traceon(3604)
dbcc page(‘MyDatabaseName’,3,1307866,3)

dbcc traceoff(3604)

You can use option 0,1,2,3 as the last parameter (bold 3) ofDBCC PAGE command.
So now I have two options.
1.)    Restorethat page from full Database backup
2.)    TryDBCC commands and if needed, allow data loss
I HIGHLY RECOMMEND to go for 1st option but ifyou are out of luck and don’t have last full backup, you must have to go forsecond way.
I have restored page from my last full database backup withbelow given TSQL.
RESTORE DATABASE MyDatabaseName
PAGE = ‘3:1307866’
FROM DISK = ‘D:\MyDatabaseName.bak’
WITH NORECOVERY
Now, let us talk about second option if you don’t have fullbackup of your database. Use DBCC CHECKDB command.
–checkingdatabase’s integrity and won’t show so many informational message,
–it will onlyshows error messages and warnings.
DBCC CHECKDB(‘MyDatabaseName’) WITH NO_INFOMSGS
Once you execute above command, it will recommend you repairlevel. it may recommend REPAIR_REBUILD if you really lucky but if you will seerepair level REPAIR_ALLOW_DATA_LOSS, you have to be ready to lose some of yourdata.
You may use either of the below given command based on theRepair Level you have been suggested.
1.)
ALTER DATABASE MyDatabaseName SETSINGLE_USER
GO
DBCC CHECKDB(‘MyDatabaseName’, REPAIR_REBUILD)
GO
ALTER database MyDatabaseName SETMULTI_USER
GO
2.)
ALTER DATABASE MyDatabaseName SETSINGLE_USER
GO
DBCC CHECKDB(‘MyDatabaseName’, REPAIR_ALLOW_DATA_LOSS)
GO
ALTER database MyDatabaseName SETMULTI_USER
GO
Moral of the story is, always have FULL recovery model foryour database and schedule full/ transaction/ differential backup policy. Thisis a MUST DO for any DBAs. You never know, when will you need it!!!!
Reference: Ritesh Shah
 
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examplesand explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Serverrelated question at my “ASK Profile
Advertisements

Playing with OPTION KEEPFIXED PLAN hint in SQL Server

My previous article was on Compilation& Recompilation of Stored Procedure. After reading that article, two ofmy reader sent me an email about more clarification on some terminology and thatis the base of writing this article.
Actually SQL Server Query Optimizer is really one of thesmart tools which used to find or generate best execution plan for query or storedprocedure and make the task easy for DBA. Basically it uses Cost BasedOptimizer (CBO) to do this task, even sometime feels to have specific ExecutionPlan for query so Microsoft have provided many different hints at differentlevel.
Query Optimizer (QO) used to save execution plan when itcompiles Stored Procedure first time and use the same execution plan when thesame stored procedure is being used again so that you can get rid of overheadof generating execution plan again and again. 
When index statistics changes heavily, QO used to recompilethe statement to get best plan for your stored procedure but if you are surethat the existing Plan would works fine than one should go for OPTION KEEPFIXEDPLAN for that statement so that you can save your resources and boost upperformance.
I will demonstrate this with few queries run in SQL ServerManagement Studio (SSMS) and capture the results in Profiler. 
I will select following Events in Profiler while creatingnew Trace for my server.
Stored Procedure
  • SP:Completed
  • SP:Recompile
  • SP:smtpCompleted
  • SP:smtpStarting

 

Now execute following TSQL in your SSMS.
–create onedatabase which you can delete after running this example
create database SQLHub
GO
USE SQLHub
GO
–if orderstable is already there. you can delete it than create new one with name”Orders”
IF OBJECT_ID(‘orders’, ‘U’) IS NOT NULL BEGIN
      DROP TABLE orders
END
GO
–creating table
CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
GO
–creatingnonclustered index
CREATE NONCLUSTERED INDEXIDX_ORD ON Orders(amount)
GO
–inserting only1 record in our table
INSERT INTO orders VALUES(GETDATE(),1001,111)
GO
–creating SPwhich will SELECT all records from Orders table where Amount is 1001
CREATE PROC spGet_ord
AS
SELECT * FROM orders WHERE Amount=1001
GO
–execute SP
exec spGet_ord
GO
If you will see in profiler than you would get followingevent captured



Now, make change in Index statistics by inserting so manyrecords in table so that we can test the effect in profiler after executing SPagain.
–inserting50000 fack rows into table
INSERT INTO orders (OrderDate, Amount, Refno)
SELECT TOP 50000
      DATEADD(minute, ABS(a.object_id % 50000 ), CAST(‘2010-02-01’ AS DATETIME)),
      ABS(a.object_id % 10),
      CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
–execute SPagain
exec spGet_ord
GO

Now, if you see the profiler, you will see “SP:Recompile”event as your statistics are updated so it need recompilation of statement. If youagain few more thousands record in same table and execute the same SP again,you will see recompilation again.
Now, after droping table and SP, we will make one smallchange in Stored Procedure and will use “KEEPFIXED PLAN” hint so that we canavoid recompilation.
drop proc spGet_ord
GO
drop table orders
go
–creating table
CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
GO
CREATE NONCLUSTERED INDEXIDX_ORD ON Orders(amount)
GO
INSERT INTO orders VALUES(GETDATE(),1001,111)
GO
CREATE PROC spGet_ord
AS
SELECT * FROM orders WHERE Amount=1001
–adding belowstatement other than that, same code as previous SP
OPTION (KEEPFIXED PLAN);
GO
exec spGet_ord
GO
After recreating table along with fresh records, we aregoing to see what happened while executing SP in above statement.

Since our table now has only 1 record so it is time tochange statistics with bulk insert as follows again.
–inserting100000 fack rows into table (BTW, thank to Jacob Sebastian, copy in INSERTscript from one of his demo to generate big table)
INSERT INTO orders (OrderDate, Amount, Refno)
SELECT TOP 50000
      DATEADD(minute, ABS(a.object_id % 50000 ), CAST(‘2010-02-01’ AS DATETIME)),
      ABS(a.object_id % 10),
      CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
exec spGet_ord
GO
–if you wish,you can uncomment below code and delete SQLHub database
–use master
–go
–drop database sqlhub
Here is the screen capture of Profiler which doesn’t showRecompilation.

 

You can even add few more thousands of rows and execute SPagain, you won’t get recompilation. 
Reference: Ritesh Shah
 
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile



Playing with OPTION KEEPFIXED PLAN hint in SQL Server

My previous article was on Compilation& Recompilation of Stored Procedure. After reading that article, two ofmy reader sent me an email about more clarification on some terminology and thatis the base of writing this article.
Actually SQL Server Query Optimizer is really one of thesmart tools which used to find or generate best execution plan for query or storedprocedure and make the task easy for DBA. Basically it uses Cost BasedOptimizer (CBO) to do this task, even sometime feels to have specific ExecutionPlan for query so Microsoft have provided many different hints at differentlevel.
Query Optimizer (QO) used to save execution plan when itcompiles Stored Procedure first time and use the same execution plan when thesame stored procedure is being used again so that you can get rid of overheadof generating execution plan again and again.
When index statistics changes heavily, QO used to recompilethe statement to get best plan for your stored procedure but if you are surethat the existing Plan would works fine than one should go for OPTION KEEPFIXEDPLAN for that statement so that you can save your resources and boost upperformance.
I will demonstrate this with few queries run in SQL ServerManagement Studio (SSMS) and capture the results in Profiler.
I will select following Events in Profiler while creatingnew Trace for my server.
Stored Procedure
  • SP:Completed
  • SP:Recompile
  • SP:smtpCompleted
  • SP:smtpStarting

 

Now execute following TSQL in your SSMS.
–create onedatabase which you can delete after running this example
create database SQLHub
GO
USE SQLHub
GO
–if orderstable is already there. you can delete it than create new one with name”Orders”
IF OBJECT_ID(‘orders’, ‘U’) IS NOT NULL BEGIN
      DROP TABLE orders
END
GO
–creating table
CREATE TABLE orders(OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
GO
–creatingnonclustered index
CREATE NONCLUSTERED INDEXIDX_ORD ON Orders(amount)
GO
–inserting only1 record in our table
INSERT INTO orders VALUES(GETDATE(),1001,111)
GO
–creating SPwhich will SELECT all records from Orders table where Amount is 1001
CREATE PROC spGet_ord
AS
SELECT * FROM orders WHERE Amount=1001
GO
–execute SP
exec spGet_ord
GO
If you will see in profiler than you would get followingevent captured
Now, make change in Index statistics by inserting so manyrecords in table so that we can test the effect in profiler after executing SPagain.
–inserting50000 fack rows into table
INSERT INTO orders(OrderDate, Amount, Refno)
SELECT TOP 50000
      DATEADD(minute, ABS(a.object_id % 50000 ), CAST(‘2010-02-01’ AS DATETIME)),
      ABS(a.object_id % 10),
      CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
–execute SPagain
exec spGet_ord
GO
Now, if you see the profiler, you will see “SP:Recompile”event as your statistics are updated so it need recompilation of statement. If youagain few more thousands record in same table and execute the same SP again,you will see recompilation again.
Now, after droping table and SP, we will make one smallchange in Stored Procedure and will use “KEEPFIXED PLAN” hint so that we canavoid recompilation.
drop proc spGet_ord
GO
drop table orders
go
–creating table
CREATE TABLE orders(OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
GO
CREATE NONCLUSTERED INDEXIDX_ORD ON Orders(amount)
GO
INSERT INTO orders VALUES(GETDATE(),1001,111)
GO
CREATE PROC spGet_ord
AS
SELECT * FROM orders WHERE Amount=1001
–adding belowstatement other than that, same code as previous SP
OPTION (KEEPFIXED PLAN);
GO
exec spGet_ord
GO
After recreating table along with fresh records, we aregoing to see what happened while executing SP in above statement.
Since our table now has only 1 record so it is time tochange statistics with bulk insert as follows again.
–inserting100000 fack rows into table (BTW, thank to Jacob Sebastian, copy in INSERTscript from one of his demo to generate big table)
INSERT INTO orders(OrderDate, Amount, Refno)
SELECT TOP 50000
      DATEADD(minute, ABS(a.object_id % 50000 ), CAST(‘2010-02-01’ AS DATETIME)),
      ABS(a.object_id % 10),
      CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
exec spGet_ord
GO
–if you wish,you can uncomment below code and delete SQLHub database
–use master
–go
–drop database sqlhub
Here is the screen capture of Profiler which doesn’t showRecompilation.

 

You can even add few more thousands of rows and execute SPagain, you won’t get recompilation.
Reference: Ritesh Shah
 
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile