March 16, 2009 at 7:16 am
This past weekend, we implemented a new maintenance plan on our SQL 2005 servers that replaces the default maintenance plan objects of 'Rebuild Index' and 'Reorganize Index' with the code listed below. We have found a few issues that are related to databases not being in SQL 2005 compatibility, but on the database servers that are used by our MOSS installations, I am getting the following error message on all 4 servers related to the SharePoint_AdminContent_GUID database:
" failed with the following error: "Could not locate entry in sysdatabases for database 'SharePoint_AdminContent_bdcb8853'. No entry found with that name. Make sure that the name is entered correctly."
Has anyone seen this before?
sp_msforeachdb 'USE ?;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command varchar(8000);
IF EXISTS (SELECT name FROM ?.sys.objects WHERE name = ''work_to_do'') DROP TABLE ?.dbo.work_to_do;
SELECT object_id AS objectid,index_id AS indexid,partition_number AS partitionnum,avg_fragmentation_in_percent AS frag INTO ?.dbo.work_to_do FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'') WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
DECLARE partitions CURSOR FOR SELECT * FROM ?.dbo.work_to_do;
OPEN partitions;
FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
SELECT @objectname = o.name, @schemaname = s.name FROM ?.sys.objects AS o JOIN ?.sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid;
SELECT @indexname = name FROM ?.sys.indexes WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*) FROM ?.sys.partitions WHERE object_id = @objectid AND index_id = @indexid;
IF @frag < 30.0
BEGIN;
SELECT @command = ''ALTER INDEX '' + @indexname + '' ON ?.'' + @schemaname + ''.'' + @objectname + '' REORGANIZE'';
IF @partitioncount > 1
SELECT @command = @command + '' PARTITION='' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
END;
IF @frag >= 30.0
BEGIN;
SELECT @command = ''ALTER INDEX '' + @indexname +'' ON ?.'' + @schemaname + ''.'' + @objectname + '' REBUILD'';
IF @partitioncount > 1
SELECT @command = @command + '' PARTITION='' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
PRINT ''Executed '' + @command;
END;
FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
END;
CLOSE partitions;
DEALLOCATE partitions;
IF EXISTS (SELECT name FROM ?.sys.objects WHERE name = ''work_to_do'') DROP TABLE ?.dbo.work_to_do;'
March 16, 2009 at 3:52 pm
Error is clear you dont have the database "Sharepoint_admin...." databse present under sys.databases catalog view. MAke sure the database exists before querying it through the command.
March 17, 2009 at 7:18 am
I already queried the sysdatabases table and found that this database is in there and it is listed with the full name; that was actually the first thing I checked.
The next thing that I was looking at was the db name in the error was truncated to 32 characters, so I'm wondering if within that script there is something that is truncating the dbname from 60 characters down to 32 characters.
Any thoughts are much appreciated!
Thanks!
Brian
March 17, 2009 at 10:07 am
It is not a 32 character limit:
Executed ALTER INDEX PK_ProductProductPhoto_ProductID_ProductPhotoID ON a1234567890_1234567890_1234567890_1234567890_AdventureWorks.Production.ProductProductPhoto REBUILD
Executed ALTER INDEX AK_StoreContact_rowguid ON a1234567890_1234567890_1234567890_1234567890_AdventureWorks.Sales.StoreContact REBUILD
Executed ALTER INDEX IX_StoreContact_ContactID ON a1234567890_1234567890_1234567890_1234567890_AdventureWorks.Sales.StoreContact REBUILD
Executed ALTER INDEX IX_StoreContact_ContactTypeID ON a1234567890_1234567890_1234567890_1234567890_AdventureWorks.Sales.StoreContact REBUILD
Executed ALTER INDEX PK_ProductReview_ProductReviewID ON a1234567890_1234567890_1234567890_1234567890_AdventureWorks.Production.ProductReview REBUILD
Executed ALTER INDEX IX_ProductReview_ProductID_Name ON a1234567890_1234567890_1234567890_1234567890_AdventureWorks.Production.ProductReview REBUILD
Executed ALTER INDEX PK_BillOfMaterials_BillOfMaterialsID ON a1234567890_1234567890_1234567890_1234567890_AdventureWorks.Production.BillOfMaterials REBUILD
Executed ALTER INDEX IX_BillOfMaterials_UnitMeasureCode ON a1234567890_1234567890_1234567890_1234567890_AdventureWorks.Production.BillOfMaterials REBUILD
Executed ALTER INDEX PK_Vendor_VendorID ON a1234567890_1234567890_1234567890_1234567890_AdventureWorks.Purchasing.Vendor REBUILD
Executed ALTER INDEX IX_PurchaseOrderHeader_VendorID ON a1234567890_1234567890_1234567890_1234567890_AdventureWorks.Purchasing.PurchaseOrderHeader REBUILD
Executed ALTER INDEX PK_CountryRegion_CountryRegionCode ON a1234567890_1234567890_1234567890_1234567890_AdventureWorks.Person.CountryRegion REBUILD
Executed ALTER INDEX AK_Employee_LoginID ON a1234567890_1234567890_1234567890_1234567890_AdventureWorks.HumanResources.Employee REBUILD
Executed ALTER INDEX AK_Employee_NationalIDNumber ON a1234567890_1234567890_1234567890_1234567890_AdventureWorks.HumanResources.Employee REBUILD
Executed ALTER INDEX PK_SalesPersonQuotaHistory_SalesPersonID_QuotaDate ON a1234567890_1234567890_1234567890_1234567890_AdventureWorks.Sales.SalesPersonQuotaHistory REBUILD
Executed ALTER INDEX AK_Product_ProductNumber ON a1234567890_1234567890_1234567890_1234567890_AdventureWorks.Production.Product REBUILD
Executed ALTER INDEX AK_Product_Name ON a1234567890_1234567890_1234567890_1234567890_AdventureWorks.Production.Product REBUILD
Executed ALTER INDEX AK_Product_rowguid ON a1234567890_1234567890_1234567890_1234567890_AdventureWorks.Production.Product REBUILD
I just renamed Adventureworks ...
Still looking - I have produced a number of other errors but not yours with the script provided - sorry -
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
March 17, 2009 at 10:13 am
Thanks for the help! I'm stumped at this point especially since it's happening on all 4 of our SQL Servers.
Also, I did check to make sure that the db compatability level is SQL 2005 as that is another issue that will cause that script to fail.
July 8, 2011 at 2:34 pm
It is the dash in the SharePoint DB name. the script below replicates the error, but darned if I know what the solution might be
select len('MichaelJSextonJDSQLgodMarriedToNoreleeBushSextonWorksAsaContractorForCritigenFatherofConorLivesInHighlandsRancCO')
go
create database MichaelJSextonJDSQLgodMarriedToNoreleeBushSextonWorksAsaContractorForCritigenFatherofConor
go
create table #DBName (dbName varchar(130))
exec sp_MSforeachDB 'Use ?; insert into #DBName( dbName) values( DB_NAME(DB_ID(''?'')) )'
select * from #DBName
truncate table #DBName
drop database MichaelJSextonJDSQLgodMarriedToNoreleeBushSextonWorksAsaContractorForCritigenFatherofConor
create database [Michael-J-SextonJDSQLgodMarriedToNoreleeBushSextonWorksAsa-ContractorForCritigenFatherofConor]
exec sp_MSforeachDB 'Use ?; insert into #DBName( dbName) values( DB_NAME(DB_ID(''?'')) )'
select * from #DBName
drop table #DBName
drop database [Michael-J-SextonJDSQLgodMarriedToNoreleeBushSextonWorksAsa-ContractorForCritigenFatherofConor]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply