routine to backup ssas databases fails with: The XML for Analysis request timed out before it was completed

by marcello miorelli   Last Updated June 07, 2017 16:06 PM

I have developed my own stored procedure, that through a LINKED SERVER, backups the SSAS databases according to a table value parameter.

the code for the stored procedure is below.

the problem:

it works well for not so big databases (up to 4 GB lets say) but when the database is bigger (12 GB) it is giving me the error message below: (I run it from a job scheduled to run every night). I am wondering what could be missing.

error:

Executed as user: mycompany\SQLAgent
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">                  
<Object>                <DatabaseID>MYDATABASE BI LIVE</DatabaseID> </Object>               
<File>E:\SQLBackups\mydatabase BI LIVE_2014-11-04_235858.abf</File>
<AllowOverwrite>true</AllowOverwrite> </Backup>
[SQLSTATE 01000] (Message 0)  OLE DB provider "MSOLAP" for linked server "MYSSASSERVER" returned message "XML for Analysis parser: The XML for Analysis request timed out before it was completed.". [SQLSTATE 01000] 
(Message 7412)  -- Exception was caught --05 Nov 2014 00:08:58:940  
The error number:7215  Severity: 17  State: 1  Procedure: sp_backupSSAS  
Line number: 126  Error Message: Could not execute statement on remote server 'myssasserver'.   
-- End of Procedure 05 Nov 2014 00:08:58:940 [SQLSTATE 01000] (Message 0).  The step succeeded.

THE PROCEDURE SP_BACKUPSSAS:

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_backupSSAS]    Script Date: 05/11/2014 10:19:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--DROP PROCEDURE sp_backupSSAS

ALTER PROCEDURE [dbo].[sp_backupSSAS]
   @DBS  SSAS_DBs_to_BAckup READONLY


/*
=======================================================================
Script  :   PROCEDURE sp_backupSSAS
Author  :   Marcelo Miorelli
Date    :   03-NOV-2014
Desc    :   
            Backup the SSAS databases that are on table @DBS
            needs the LINKED server to SSAS - in this case [SSASSERVER]
            On the first version I left that static.

Credits:  the credits for the Original idea  go to Theo Ekelmans
          http://www.sqlservercentral.com/scripts/automatic/97696/



Usage   :           

DECLARE @DBS AS SSAS_DBs_to_BAckup 
--INSERT INTO @DBS (DBName, LocationName) VALUES ('MYCOMPANY BI LIVE','H:\SQLBackups\SSASSERVER\')
INSERT INTO @DBS (DBName, LocationName) VALUES ('Pyramid Demo 2013','\\SQLREPLON1\SQLBackups\SSASSERVER\')

EXEC sp_backupSSAS @DBS


=======================================================================
History
Date        Action      User            Desc
-----------------------------------------------------------------------
<Add Date>  Created     <add your name> <add description of change>

=======================================================================
*/

--======================================
-- describe primary blocks of processing
--======================================

------------------------------------------------
-- describe action of logical groups of commands
------------------------------------------------

-- describe individual actions within a command set


AS 


SET NOCOUNT ON
SET DATEFORMAT DMY
SET DEADLOCK_PRIORITY NORMAL;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


DECLARE @name VARCHAR(50) -- Cube name  
DECLARE @path VARCHAR(256) -- Backup path
DECLARE @fileName VARCHAR(256) -- Backup filename 
DECLARE @DT VARCHAR(20) -- Used for optional file name timestamp
Declare @XMLA nvarchar(4000) -- The SSAS command in XML format


-- Change timestamp to this format: _YYYY-MM-DD_HHMMSS
Set @DT = '_' + Replace(Replace(Convert(nvarchar, getdate(), 120), ':', ''), ' ', '_');

DECLARE @RADHE TABLE ( i int not null identity(1,1) PRIMARY KEY CLUSTERED,
                       CATALOG_NAME  SYSNAME NOT NULL)

DECLARE @I INT
DECLARE @Z INT

DECLARE  @log NVARCHAR(MAX)
        ,@vCrlf CHAR(2);

SELECT  @log = ''
       ,@vCrlf = CHAR(13)+CHAR(10);

INSERT INTO @RADHE (CATALOG_NAME) 
SELECT CATALOG_NAME 
FROM OPENQUERY([SSASSERVER], 'SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS') as a


SELECT @Z = @@ROWCOUNT
SELECT @I = 1


WHILE @I <= @Z BEGIN

  SELECT @NAME = CATALOG_NAME 
    FROM @RADHE WHERE I = @I

  --====================================================================
  -- check whether the database is contained in @DBS 
  -- get the path from @DBS where you want to backup the database
  -- if yes then backup the database, if not then skip
  --====================================================================

  SELECT @PATH = NULL

  SELECT @path = LocationName FROM @DBS WHERE DBName = @NAME 

    IF (@PATH IS NOT NULL) BEGIN 

             ---Create the XMLA string (add a DT stamp to the filename)
             Set @XMLA = N'
             <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
                <Object>
                <DatabaseID>' + @name + '</DatabaseID>
                </Object>
                <File>' + @path + @name + @DT + '.abf</File>
                <AllowOverwrite>true</AllowOverwrite>
             </Backup>
             ';


            BEGIN TRY

                print CAST (@XMLA AS NTEXT)

                -- Execute the string across the linked server (SSAS)
                Exec (@XMLA) At [SSASSERVER]

            END TRY
            BEGIN CATCH

                    SELECT
                            ERROR_NUMBER() AS ErrorNumber,
                            ERROR_SEVERITY() AS ErrorSeverity,
                            ERROR_STATE() AS ErrorState,
                            ERROR_PROCEDURE() AS ErrorProcedure,
                            ERROR_LINE() AS ErrorLine,
                            ERROR_MESSAGE() AS ErrorMessage


                            SELECT @log = @log + '-- Exception was caught --' +  CONVERT(VARCHAR(24), GETDATE(), 113)  + @vCrlf  +
                                                 'The error number:' + coalesce(cast ( ERROR_NUMBER()  as varchar(max)), 'No Info') + @vCrlf  

                            SELECT @log = @log + 'Severity: '        + coalesce(cast ( ERROR_SEVERITY()  as varchar(max)), 'No Info') + @vCrlf  +
                                                 'State: '           + coalesce(cast ( ERROR_STATE() as varchar(max)), 'No Info')  + @vCrlf  

                            SELECT @log = @log + 'Procedure: '       + coalesce(cast ( coalesce(ERROR_PROCEDURE(),'No Info')  as varchar(max)), 'No Info') + @vCrlf  +
                                                 'Line number: '     + coalesce(cast ( ERROR_LINE() as varchar(max)), 'No Info')  + @vCrlf  

                            SELECT @log = @log + 'Error Message: '   + cast ( coalesce(ERROR_MESSAGE(),'No Info')  as varchar(max)) + @vCrlf  

                            SELECT @log = @log + ' -- End of Procedure ' + CONVERT(VARCHAR(24), GETDATE(), 113) + @vCrlf  

                            PRINT CAST(@LOG AS NTEXT)

                            WHILE @@TRANCOUNT > 0 
                                    ROLLBACK

            END CATCH




    END --IF

    SELECT @I += 1

END   

the parameter definition:

USE [master]
GO

CREATE TYPE [dbo].[SSAS_DBs_to_BAckup] AS TABLE(
    [DBName] [sysname] NOT NULL,
    [LocationName] [varchar](1008) NULL
)
GO

example of how to call the stored procedure:

DECLARE @DBS AS SSAS_DBs_to_BAckup 
--INSERT INTO @DBS (DBName, LocationName) VALUES ('MYCOMPANY BI LIVE','H:\SQLBackups\SSASSERVER\')
INSERT INTO @DBS (DBName, LocationName) VALUES ('Pyramid Demo 2013','\\SQLREPLON1\SQLBackups\SSASSERVER\')

EXEC sp_backupSSAS @DBS

When I right click the SSAS server in SSMS and look at properties this is what I see:

enter image description here



Answers 1


Besides what @James raised on the comments regarding the SSAS settings of commit timeout and force commit timeout, It was the change that I did on SQL Server that make it work at the end.

EXEC sys.sp_configure N'remote query timeout (s)', N'6000'
GO
RECONFIGURE WITH OVERRIDE
GO

this is the same value as you can see on the picture below: you get there by sql server server, right click properties,connections.

enter image description here

And also on the Analysis Server Properties, the "Commit Timeout" and "Force Commit Timeout" as you can see on the picture below. enter image description here

Now I have backed up the SSAS database and restored it in a development server, and it proved fine.

marcello miorelli
marcello miorelli
November 12, 2014 14:45 PM

Related Questions


Where does linked server query gets executed?

Updated August 17, 2017 15:06 PM