1

I am using Ola Hallengren's maintenance solution to back up a SQL 2012 Express instance in our environment. I am a brand new DBA, only 10 weeks old so I am not too familiar yet but I am learning fast. I am pretty sure I have everything configured the way we want it. I created the stored procedures in the master database of our express instance and I have scheduled batch files to be executed nightly from within Windows Task Scheduler and all seems to be set up and working minus one detail, the logging to table.

First of all, I am not quite sure how to enable it or what database to find the table in if it is enabled?

Second, I would like to log the results to a table in a different database on a different server\instance. Is this possible? If so, does anyone have any direction they can point me to so I can set this up?

Thanks!

2

The 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'))
BEGIN
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
  FOR LinkedServerName.DatabaseName.dbo.CommandLog;

In 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'
  )
  BEGIN
    SET @ErrorMessage = 'The table CommandLog is missing. Download...'
    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
    SET @Error = @@ERROR
  END

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.

  • I saw that code and expected for the dbo.CommandLog table to be in master but when I expand the object tree for the database there is no Tables object. If I run the select statement it does show that it exists, I just can't see it in master tree view in Object Explorer I guess, is this normal? For the 2nd half of this, thank you for the options, being so new to my DBA role I will admit that your suggestion is something I have no experience with but I will do some digging around to see if this will work for me. Thank you! – TattooedDBA Jun 5 '18 at 15:57
  • That sounds like some issue with Object Explorer. What does SELECT name FROM [master].sys.tables; yield? – Aaron Bertrand Jun 5 '18 at 16:33
  • when I run that select statement it shows the CommandLog table. So I have now confirmed that the logging was happening to the CommandLog table, even though I cannot see the Tables object in the tree view in object explorer. I ran the rename sp and that successfully worked. I created the synonym and that worked. But now there is an error being thrown in the log file that is being generated. Msg 50000, Level 16, State 1, Server MyServer\MyInstance, Procedure DatabaseBackup, Line 333 The table CommandLog is missing. – TattooedDBA Jun 5 '18 at 17:47
  • More Details: Since this is an express instance I am running the backups via a windows scheduled task that is calling a batch file since the express edition does not have the sql agent. When I try to run the backup commands in SSMS that are in the batch file it yields the same error. The backup statement that is running is as follows EXECUTE dbo.DatabaseBackup @Databases = 'SYSTEM_DATABASES', @Directory = N'\\PathToFolderShare', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 72, @CleanupMode = 'AFTER_BACKUP', @LogToTable = 'Y' Any ideas? – TattooedDBA Jun 5 '18 at 17:51
  • Thanks again. Everything is in that master database and only after creating the synonym does it error out. I will see if I can get in touch with Ola for any further support. – TattooedDBA Jun 5 '18 at 20:07
0

If you look at the [dbo].[CommandExecute] stored procedure that Ola's scripts use, it looks like they are expecting the [dbo].[CommandLog] logging table to exist in the same database where the scripts are executing.

The only way I know that you could log these commands to another SQL Server instance would be to create a linked server to that remote instance and add an AFTER insert/update trigger to the [dbo].[CommandLog] table which would insert/update the remote instance version of the table. You'd still need to delete entries periodically from the local instance as they would originally be logged there first.

  • If you're going to maintain two identical tables, I'd recommend Replication over Triggers and possibly log shipping. I'm thinking a view named CommandLog referencing a linked server could plausibly work. – Shaulinator Jun 5 '18 at 15:34
  • I am afraid to ask certain questions as I am so new and I feel like I should know the answer but here it goes: I also thought that the [dbo].[CommandLog] table was supposed to be located in the same database that the scripts are executing. I created the Stored Procedures in the master database and that is where they run from but the master database does not show a Tables object when I expand the tree. Is this normal? As for the 2nd half. I am unfamiliar with how to do that at this point but am going to research and learn to see if this solution can work for us? – TattooedDBA Jun 5 '18 at 15:47
  • The default is to NOT log, but if you include the parm to log to table, that table definitely needs to exist. You can download the table create script from Ola’s site – Scott Hodgin Jun 5 '18 at 15:51
  • @ScottHodgin The crazy thing is that it does exist. When I run this query from Ola's script where it checks to see if the table 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' it shows that the table is there, but there is no Tables object when I expand the tree for master – TattooedDBA Jun 5 '18 at 16:01
  • @brandon - did you refresh the table tree node in SSMS? Right click and refresh? – Scott Hodgin Jun 5 '18 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.