SQL Server DBA - Msg 2501, Level 16, State 45, Line 1 Cannot find a table or object with the name “TableName”. Check the system catalog.

Msg 2501, Level 16, State 45, Line 1
Cannot find a table or object with the name “TableName”. Check the system catalog.

You need to Defragment All the Indexes in the Database.

Simple Way To De fragment All Indexes In A Database That Is Fragmented Above A Declared Threshold.

/* Originally created by Microsoft */
-- Specify your Database Name
USE AdventureWorks;
GO
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename VARCHAR(128);
DECLARE @execstr VARCHAR(255);
DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @frag decimal;
DECLARE @maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT CAST(TABLE_SCHEMA AS VARCHAR(100))
+'.'+CAST(TABLE_NAME AS VARCHAR(100))
AS Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName CHAR(255),
ObjectId INT,
IndexName CHAR(255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity decimal,
BestCount INT,
ActualCount INT,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(VARCHAR(15),@frag)) + '%';
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')';
EXEC (@execstr);
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GO

After running this Your Table will be in the Accessing mode

SQL DBA - Msg 8914, Level 16, State 1, Line 1 Incorrect PFS free space information

Errors :-  

When u Run DBCC CHECKDB you will get a Error like this :-

 

Msg 8914, Level 16, State 1, Line 1

Incorrect PFS free space information for page (1:278) in object ID 1047674780, index ID 0, partition ID 72057594052018176, alloc unit ID 72057594065125376 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.

There are 701 rows in 359 pages for object "XXXxxxXXXxxxXXX".

CHECKDB found 0 allocation errors and 6 consistency errors in table 'XXXxxxXXXxxxXXX' (object ID 1047674780).

Solution For this Error :-

             1. Check which Table is corrupted using.

  DBCC Checktable ('Your_Table_Name’).

2.Run this Query to Change DB into Single User Mode.

  ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE

3.Run this Query to Recover the Table/DB. 

 DBCC CheckTable ('corrupted_table', REPAIR_ALLOW_DATA_LOSS)

4.Once this Execution Completed Sql returns “Errors are Corrected” .

5.Run this Query to Change the DB into MultiUser Mode.

 ALTER DATABASE corrupted_db SET MULTI_USER

Here are the technical details of the problem :

The issue was that when minimal logging for LOBs was used (under the SIMPLE recovery model, during BULK INSERT/BCP/Large insert with TABLOCK), extents are being pre-allocated and the pages were being marked 100% full in the PFS page when the pages were allocated. The idea was that all pages eventually will be filled up with LOB data, and by marking them full during allocation we avoid an extra update to the PFS pages when the data is actually put on the page.

Suppose now that 64 pages are pre-allocated, and only 40 or so pages are used and have rows on them. When the transaction commits, the Storage Engine is supposed to deallocate the 24 extra pages that were pre-allocated, and marked 100% full, even though they don’t have any rows on them yet. There was a bug where in a certain case the deallocation wouldn't happen, so you end up with empty pages that have a PFS state of 100%, but don’t have any rows on them.

In general, even though this is a bug in the code, functionality wise, there is nothing wrong with the database, besides a number of additional pages that are empty and allocated to the LOB tree, so as long as the message tells you that the page is supposed to be empty, but is actually marked 100%, nothing can really go wrong with that page. Unfortunately, DBCC will keep reporting these errors.

Although I haven't tried this, my guess is that you can get rid of the empty LOB pages using ALTER INDEX ... REORGANIZE WITH (LOB_COMPACTION = ON). So, if you see some of these errors with no other errors, you may have hit this bug and have nothing really to worry about.

 

SQL DBA : Msg 7303, Level 16, State 1, Line 1 oracle linked server error

1- install the Oracle OLEDB,
2- create the linked server
3- run the openquery at the server (it worked!!)
4- run the openquery at a client Management Studio (it failed!!)
In our case it failed and returned the generic error:
Msg 7302, Level 16, State 1, Line 1 Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE".Msg

5- so we had to do this:
USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
GO
6- run the openquery at a client Management Studio (IT WORKS!!)