Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Backup Failed database...
Microsoft Dynamics GP (Archived)

Backup Failed database of companies in Dynamics GP - SQL Server 2008 SP4

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

I'm having a problem when I'm backing up a DB from the Dynamics GP company, which I show on the screen and detailed the messages, to see if you know the solution to allow me to do the bakcup.

Do the steps mentioned in the following link, but I did not work.

https://community.dynamics.com/gp/b/dynamicsuniversitygp/archive/2013/12/12/attention-sql-backup-failed

===================================

 

Error de Backup para Servidor XXXXX.  (Microsoft.SqlServer.SmoExtended)

 

------------------------------

Para obtener ayuda, haga clic en: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.5500.0+((Katmai_PCU_Main).110922-0336+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

 

------------------------------

Ubicación del programa:

 

   at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)

   at Microsoft.SqlServer.Management.SqlManagerUI.BackupPropOptions.OnRunNow(Object sender)

 

===================================

 

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

 

------------------------------

Ubicación del programa:

 

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)

   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)

   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages)

   at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)

   at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)

 

===================================

 

Failed to flush the commit table to disk in dbid 40 due to error 2601. Check the errorlog for more information. (.Net SqlClient Data Provider)

 

------------------------------

Para obtener ayuda, haga clic en: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.5500&EvtSrc=MSSQLServer&EvtID=3999&LinkId=20476

 

------------------------------

Nombre del servidor: XXXXXXXXX

Número de error: 3999

Gravedad: 17

Estado: 1

Número de línea: 1

 

 

------------------------------

Ubicación del programa:

 

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)

   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

Here is the version of SQL Server that I have.

Microsoft SQL Server 2008 (SP4) - 10.0.6000.29 (X64)   Sep  3 2014 04:11:34   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (VM)

*This post is locked for comments

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Backup Failed database of companies in Dynamics GP - SQL Server 2008 SP4

    For the solution, do the following:

    1. Open SQL Server Management Studio, run the following script on the database with the failure, to detect which tables have change tracking enabled. 
      select S.name as SchemaName,
OBJECT_NAME(T.object_id) as TableName,
T.is_track_columns_updated_on, T.min_valid_version,
T.begin_version, T.cleanup_version
from sys.change_tracking_tables T
inner join sys.tables TT on TT.object_id = T.object_id
inner join sys.schemas S on S.schema_id = TT.schema_id
order by SchemaName, TableName
go
    2. The result, copy it with header and paste it in excel, to facilitate the construction of the script to inactivate the change tracking in the different tables with the following formula: 
      =CONCAT("ALTER TABLE ",B2,
" DISABLE CHANGE_TRACKING;")

NOTE: Run this script on each of the tables found in step one above, substituting the actual table name for GLXXXXX: 

ALTER TABLE GLXXXXX
DISABLE CHANGE_TRACKING;
    3. Execute the script directly in the DB that presents us problem. 
      NOTE: Here all the tables that came out in the list of step 1 with change tracking enabled.

ALTER TABLE AAG00400 DISABLE CHANGE_TRACKING;
ALTER TABLE AAG00401 DISABLE CHANGE_TRACKING;
ALTER TABLE AAG00902 DISABLE CHANGE_TRACKING;
ALTER TABLE AAG00903 DISABLE CHANGE_TRACKING;
ALTER TABLE AAG00904 DISABLE CHANGE_TRACKING;
ALTER TABLE AAG00905 DISABLE CHANGE_TRACKING;
ALTER TABLE AAG10000 DISABLE CHANGE_TRACKING;
ALTER TABLE AAG10001 DISABLE CHANGE_TRACKING;
ALTER TABLE AAG10002 DISABLE CHANGE_TRACKING;
ALTER TABLE AAG10003 DISABLE CHANGE_TRACKING;
ALTER TABLE AAG30000 DISABLE CHANGE_TRACKING;
ALTER TABLE AAG30001 DISABLE CHANGE_TRACKING;
ALTER TABLE AAG30002 DISABLE CHANGE_TRACKING;
ALTER TABLE AAG30003 DISABLE CHANGE_TRACKING;
ALTER TABLE AAG40000 DISABLE CHANGE_TRACKING;
ALTER TABLE AAG40001 DISABLE CHANGE_TRACKING;
ALTER TABLE AAG40002 DISABLE CHANGE_TRACKING;
ALTER TABLE AAG40003 DISABLE CHANGE_TRACKING;
ALTER TABLE GL00100 DISABLE CHANGE_TRACKING;
ALTER TABLE GL00102 DISABLE CHANGE_TRACKING;
ALTER TABLE GL00200 DISABLE CHANGE_TRACKING;
ALTER TABLE GL00201 DISABLE CHANGE_TRACKING;
ALTER TABLE GL10000 DISABLE CHANGE_TRACKING;
ALTER TABLE GL10001 DISABLE CHANGE_TRACKING;
ALTER TABLE GL10100 DISABLE CHANGE_TRACKING;
ALTER TABLE GL10101 DISABLE CHANGE_TRACKING;
ALTER TABLE GL12000 DISABLE CHANGE_TRACKING;
ALTER TABLE GL12001 DISABLE CHANGE_TRACKING;
ALTER TABLE GL20000 DISABLE CHANGE_TRACKING;
ALTER TABLE GL30000 DISABLE CHANGE_TRACKING;
ALTER TABLE GL32000 DISABLE CHANGE_TRACKING;
ALTER TABLE GL40000 DISABLE CHANGE_TRACKING;
ALTER TABLE GL40200 DISABLE CHANGE_TRACKING;
ALTER TABLE MC00200 DISABLE CHANGE_TRACKING;
ALTER TABLE MC40000 DISABLE CHANGE_TRACKING;
ALTER TABLE MC40600 DISABLE CHANGE_TRACKING;
ALTER TABLE SY00300 DISABLE CHANGE_TRACKING;
ALTER TABLE SY40100 DISABLE CHANGE_TRACKING;
ALTER TABLE SY40101 DISABLE CHANGE_TRACKING; 
    4. Once change tracking is turned OFF on all of the tables listed in the script in step one, turn off Change Tracking on the affected database by right-clicking the database, choosing Properties, clicking on Change Tracking on the left side of the properties window, highlighting ‘True’ and changing it to False.
    5. Close SQL Server Management Studio, Restart the different SQL Server services. (Run - services.msc)
    6. Re-open SQL Server Management Studio, Attempt to perform backup in the database that posed problem.
    7. At this point, you should perform the backup without problem, if not, perform step 5 again and restart the server.
    8. Go to the Management Reporter console and activate the change tracking again (This step does it because, my problem was with the Management Reporter 2012 that I presented the error)
    9. Perform the backup again to make sure it is done correctly.
  • Suggested answer
    Mariano Gomez Profile Picture
    Mariano Gomez 26,225 on at
    RE: Backup Failed database of companies in Dynamics GP - SQL Server 2008 SP4

    This happens because you have database Change Tracking enabled/Esto ocurre porque tienes la función de Control de Cambios abilitada para la base de datos en cuestión.

    You have two options/Tienes dos opciones:

    1. Disable Change Tracking / Desabilita la función de Control de Cambios.

    a) First disable change tracking / primero desabilita Control de Cambios

    ALTER DATABASE TWO SET CHANGE_TRACKING = OFF;

    b) Restart the SQL Server service/ Reinicializa el servicio de SQL

    c) Enable change tracking again / Abilita control de cambios nuevamente

    ALTER DATABASE TWO
SET CHANGE_TRACKING = ON  
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

    d) Proceed to backup the database / Procede a hacer la copia de resguardo de la base de datos

    2) Apply SQL Server 2008 R2 latest service pack / Aplica el paque de servicios más reciente para SQL Server 2008 R2.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans