Warning when rebuilding index in another database

Question

Warning when rebuilding index in another database

loach

Right there with Babe

Points: 796
More actions
November 20, 2007 at 9:44 am

#119624

I just wrote a procedure to assist in selectively reorganizing or rebuilding indexes based on fragmentation levels. I had one I used in 2000, but it required that the procedure exist in each database it was run against. In re-writing it for 2005, I decided to create one that only needs to exist in master and can be 'pointed' to the database it is to work on. All was fine until I ran the procedure against a database that is replicated. For each 'ALTER INDEX ... REBUILD ...' I receive the following warning:
[font="Courier New"]"Replication is skipping schema version logging because the systranschemas table is not present in database '1'. This is an informational message only. No user action is required."[/font]
So, I get the warning if I execute the following:
[font="Courier New"]USE MASTER
GO
ALTER INDEX PK_tblname ON DatabaseName.dbo.tblname REBUILD WITH (ONLINE = ON);
GO[/font]
But I do not get the warning if I execute the following:
[font="Courier New"]USE DatabaseName
GO
ALTER INDEX PK_tblname ON dbo.tblname REBUILD WITH (ONLINE = ON);
GO[/font]
Clearly, since database '1' is master, and master is not replicated, systranschemas will not exist there.
Does anyone know exactly what this warning means and if it is something I should be concerned about?
Thanks in advance for any assistance you can provide!
-k

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

loach Right there with Babe Points: 796 More actions · Answer 1

bump...

Does anyone know if I should be concerned here? I'm assuming not since the message does say 'no user action required' but it still makes me nervous.

Thanks in advance

-k

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 2

I'm going to make an educated guess. Although fair warning it is just an educated guess. I belive what is happening is that when you do an alter index on a replicated database replication is looking for the table "systranschemas" in the local database (where you are starting from) and so of course it doesn't exist .. because master is not replicated. That and probably "skipping schema version logging" just means that replication is not going to log the fact that you performed the command and is not going to send it down the line to your replicated databases.

If you are worried about it you could change your code (based on what you are doing I'm assuming its dynamic SQL?) to use this version.

USE DatabaseName

GO

ALTER INDEX PK_tblname ON dbo.tblname REBUILD WITH (ONLINE = ON);

GO

Basically do this:

DECLARE @Sql nvarchar(max)

SET @sql = 'USE ' + @DatabaseName + '; ' +

'ALTER INDEX ' + ......

EXEC sp_executesql @sql

Instead of this

USE Master

GO

DECLARE @Sql nvarchar(max)

SET @sql = 'ALTER INDEX ' + ......

EXEC sp_executesql @sql

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 3

I agree with Kenneth that your procedure looks for systransschema in the local datbase.

I ran into similar issues then writing such a procedure, but instead of prefixing all the objects with the databasename I decided to mark the procedure as a system object. That way you can call the procedure from any database and it will always execute under the context of the database where you call it from.

Just use create your procedure in master and then run:

EXEC sp_MS_marksystemobject '[dbo].[sp_RebuildIndexes_2K5]'

Markus

P.S. in your case you might have to edit the procedure and remove the database prefixes first.

[font="Verdana"]Markus Bohse[/font]

loach Right there with Babe Points: 796 More actions · Answer 4

Some good advice here. Thanks to all. Either approach should work (inserting 'USE...' statements in the executed batches or marking the proc as a system and executing form the target database). I'll give them a shot!

-k