Ошибка sql 1073548784

Fixes an issue that occurs when you use the AlwaysOn Availability Groups feature in SQL Server 2014.

SQL Server 2014 Developer SQL Server 2014 Enterprise SQL Server 2014 Standard More…Less

Symptoms

Consider the following scenario:

  • You use the AlwaysOn Availability Groups feature in Microsoft SQL Server 2014.

  • You have an availability group whose backup-preference setting is set as «Prefer Secondary» or «Secondary only.»

  • You create a maintenance plan that uses a backup database task to back up a database, and the database belongs to the availability group.

  • You select the «Verify backup integrity» option, and click to clear the «For availability databases, ignore replica priority for backup and backup on primary settings» option for the backup database task.

  • You execute the maintenance plan.

In this scenario, you receive the following error message:

Error Number:
-1073548784
Error Message:
Executing the query «<Query statement>» failed with the following error: «Cannot open backup device ‘<File path of the backup file>’. Operating system error 2(The system cannot find the file specified.). VERIFY DATABASE is terminating abnormally.». Possible failure reasons: Problems with the query, «ResultSet» property not set correctly, parameters not set correctly, or connection not established correctly.

Resolution

The issue was first fixed in the following cumulative updates of SQL Server:

  • Cumulative Update 2 for SQL Server 2014 SP1

  • Cumulative Update 9 for SQL Server 2014

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the «Applies to» section.

Need more help?

Источник
  • Remove From My Forums

  • Question

  • Hello,

    I am running SQL Server 2008 R2 (Microsoft SQL Server Management Studio      10.50.2500.0
    Strumenti client di Microsoft Analysis Services      10.50.2500.0
    Microsoft Data Access Components (MDAC)      6.1.7601.17514
    Microsoft MSXML      3.0 6.0
    Microsoft Internet Explorer      9.0.8112.16421
    Microsoft .NET Framework      2.0.50727.5456
    Sistema operativo      6.1.7601)

    I have two backup plans, one Daily, with Differential backup, and one Weekly with FULL backup. I keep all files for 30 days.

    Since I want to be really really sure of backing up my precious data, I installed Cobian Backup, and created a task to daily zip and transfer the (incremental) files via FTP to some other place in the world.

    Since  running Cobian Backup, the Daily plan has broken. It complains that it cannot find the backup information any more, and returns a -1073548784 error.

    the log files are not very helpful:

    «Possible failure reasons: Problems with the query, «ResultSet» property not set correctly, parameters not set correctly, or connection not established correctly.»

    Why is the zip and ftp shorting the SQL?

    Any help is welcome!

    thanks

Answers

  • Hello,

    the error detail for -1073548784 suggests that SQL Server is unable to find the latest Full backup, on which to run the Differential.

    I tried disabling the Volume Shadow Copy option on Cobian Backup. Now the differential backup on SQL Server works.

    Something will not work if one of the two processes try to access the backup files in the same time frame. I will have to find some workaround for this.

    My conclusion is:

    SQL Server 2008 R2 differential backups are incompatible with Volume Shadow Copy service on Windows Server 2008 R2.

    Somehow, somewhere Volume Shadow Copy (or Cobian Backup’s use of it) breaks the ability of SQL Server to find the latest Full backup.

    • Marked as answer by

      Friday, September 28, 2012 1:20 PM

Источник
  • Remove From My Forums

  • Question

  • Hi Everyone,

    We have a weekly maintenance plan in place that fails with the following error:

    Executing the query «ALTER INDEX [NCI_WI_BId_PId_PMId_SId_NPB] ON [Infr…» failed with the following error: «Transaction (Process ID 94) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.».
    Possible failure reasons: Problems with the query, «ResultSet» property not set correctly, parameters not set correctly, or connection not established correctly.

    This maintenance plan runs okay for one week and it fails the next and so forth and so on. I haven’t been able to find the culprit.. I have created and re-created the maintanence plan, play around with schedules becuase I thoght that other job could
    be blocking this task but nothing works. Do you guys have any advice?

    Running SQL Server 2008 R2

    Thanks in advance,

    J

Answers

  • Add the startup option -T1222 to SQL Server. (You do this from SQL Server
    Configuration Manager.) You need to restart SQL Server for the option to
    take effect.

    SQL Server will now print a trace for all deadlocks to the SQL Server
    errorlog.

    You can also enable the trace flag with the command DBCC TRACEON. But if you
    make it a startup option, you have it on for good.

    When you add the startup option, make sure that you don’t add any space
    there. Run DBCC TRACESTATUS(-1) after the restart to make sure that the flag
    is in effect.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by

      Thursday, July 12, 2012 2:50 AM

    • Marked as answer by
      Iric Wen
      Thursday, July 19, 2012 8:33 AM

Источник
  • Remove From My Forums

  • Question

  • Hi Everyone,

    We have a weekly maintenance plan in place that fails with the following error:

    Executing the query «ALTER INDEX [NCI_WI_BId_PId_PMId_SId_NPB] ON [Infr…» failed with the following error: «Transaction (Process ID 94) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.».
    Possible failure reasons: Problems with the query, «ResultSet» property not set correctly, parameters not set correctly, or connection not established correctly.

    This maintenance plan runs okay for one week and it fails the next and so forth and so on. I haven’t been able to find the culprit.. I have created and re-created the maintanence plan, play around with schedules becuase I thoght that other job could
    be blocking this task but nothing works. Do you guys have any advice?

    Running SQL Server 2008 R2

    Thanks in advance,

    J

Answers

  • Add the startup option -T1222 to SQL Server. (You do this from SQL Server
    Configuration Manager.) You need to restart SQL Server for the option to
    take effect.

    SQL Server will now print a trace for all deadlocks to the SQL Server
    errorlog.

    You can also enable the trace flag with the command DBCC TRACEON. But if you
    make it a startup option, you have it on for good.

    When you add the startup option, make sure that you don’t add any space
    there. Run DBCC TRACESTATUS(-1) after the restart to make sure that the flag
    is in effect.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by

      Thursday, July 12, 2012 2:50 AM

    • Marked as answer by
      Iric Wen
      Thursday, July 19, 2012 8:33 AM

Источник

I ran DBCC CheckDB in our Production SQL Server 2014. It reported back with one consistency error:

Failed:(-1073548784) Executing the query "DBCC CHECKDB(N'MYDB')  WITH NO_INF..." failed with the following error: 

"Table error: Object ID 629577392, index ID 1, partition ID 72057594387039319, alloc unit ID 72057594045596759 (type LOB data). The off-row data node at page (1:6009), slot 24, text ID 15754068079 is not referenced.

CHECKDB found 0 allocation errors and 1 consistency errors in table 'MYTABLE' (object ID 629577281).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'MYDB'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MYDB).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Since we do not have a good backup for that DB, we have to route to the below scenario.

What I did to troubleshoot:

  1. I restored the recent backup file from Prod to UAT and ran DBCC CheckDB again to replicate the error. The same consistency error came back.
  2. Ran DBCC CHECKTABLE (MYTABLE) = same consistency error shows:
Table error: Object ID 629577392, index ID 1, partition ID 72057594387039319, alloc unit ID 72057594045596759 (type LOB data). 

The off-row data node at page (1:6009), slot 24, text ID 15754068079 is not referenced.
DBCC results for 'MYTABLE'.
There are 53635 rows in 2705 pages for object "MYTABLE".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'MYTABLE' (object ID 629577281).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE
  1. Then implemented the following steps below:

— step 1
ALTER DATABASE [MYDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

— step 2
DBCC CHECKDB(N'[MYDB]’, REPAIR_ALLOW_DATA_LOSS);

Msg 8964, Level 16, State 1, Line 3
Table error: Object ID 629577392, index ID 1, partition ID 72057594387039319, alloc unit ID 72057594045596759 (type LOB data). The off-row data node at page (1:6009), slot 24, text ID 15754068079 is not referenced.
        The error has been repaired.
There are 53635 rows in 2705 pages for object "MYTABLE".

— step 3
DBCC CHECKDB (‘[MYDB]’) —(Re-run to check for any additional errors = no errors reported)

— step 4
ALTER DATABASE [MYDB] SET MULTI_USER; —(setting it back to multi user mode = users can access the DB)

My questions/concerns:

  1. How do I know which data has been lost? From my understanding it doesn’t seem like any data was lost because before the repair the table had 53635 rows. After the repair it still has 53635 rows.

  2. Do we need to to schedule downtime/ outage for that exact site when setting the DB to single user mode?

  3. After doing the repair in Production, what are some best practices to keep aware of?

Источник

Понравилась статья? Поделить с друзьями:
  • Ошибка stack overflow minecraft
  • Ошибка steamstartup failed missing interface portal что делать
  • Ошибка sql 1064 you have an error in your sql syntax
  • Ошибка stabilitrak opel insignia
  • Ошибка steam неверная платформа