dbo.CommandLog table will be in whatever database you created it. By default it will be in
master (here is a portion of the MaintenanceSolution.sql script):
USE [master] -- Specify the database in which the objects will be created.
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[CommandLog]') AND type in (N'U'))
CREATE TABLE [dbo].[CommandLog](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [sysname] NULL,
If you want to log on another server, you need to be prepared for things like flaky or dead connections, and the potential latency of writing a lot of information over a linked server.
If you really want to do this, then create the
dbo.CommandLog table on that other instance, then in the database where that table exists currently:
-- you could drop, but in case you have history you want to keep:
EXEC sys.sp_rename N'dbo.CommandLog', N'CommandLogBackup', N'OBJECT';
CREATE SYNONYM dbo.CommandLog
dbo.DatabaseBackup there is a check for the table in the same database as the procedure:
IF @LogToTable = 'Y' AND NOT EXISTS
SELECT * FROM sys.objects objects
INNER JOIN sys.schemas schemas
ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] = 'U'
AND schemas.[name] = 'dbo'
AND objects.[name] = 'CommandLog'
SET @ErrorMessage = 'The table CommandLog is missing. Download...'
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
It's only failing because it's explicitly looking for a table - the fact that it is a synonym won't prevent any subsequent logging calls from working (again, as long as everything - including the synonym - live in the same database). You can try just commenting out the
RAISERROR line or just removing the check in its entirety. Just remember to do that any time you deploy an update to the procedure. You might consider asking Ola for a feature to support a synonym or view so that the logging table could be kept elsewhere.