Troubleshooting
      Back to home
      1. Legacy
      2. Troubleshooting

      Large SQL Dump Files

      Issue:
      Are large SQL dump files filling up your hard drive?

      Microsoft(R) Server Maintenance Utility (Unicode) Version 12.0.5000
      Report was generated on "EVO-SQL\EVOSUS".
      Maintenance Plan: Evosus Maintenance Plan
      Duration: 00:00:20
      Status: Warning: One or more tasks failed.
      Details:
      Check Database Integrity (EVO-SQL\EVOSUS)
      Check Database integrity on Local server connection
      Databases: EVO_PROD
      Include indexes
      Task start: 2017-08-09T18:35:54.
      Task end: 2017-08-09T18:36:14.
      Failed       -1073548784) Executing the query "DBCC CHECKDB(N'EVO_PROD') WITH NO_INFOMSGS"
      failed with the following error: "The database could not be exclusively locked to perform the operation."
      Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
      Command:USE [EVO_PROD]
      GO
      DBCC CHECKDB(N''EVO_PROD'') WITH NO_INFOMSGS
      GO
       

      Solution:
      This problem occurs if the following conditions are true:

      • At least one other connection is using the database against which you run the DBCC CHECK command.

      • The database contains at least one file group that is marked as read-only.

      a. To continue to perform the checks, the DBCC CHECK command tries to acquire an EX database lock. If other users are connected to this database, this attempt to acquire an EX lock fails. Therefore, you receive an error message.

      b. If a read-only filegroup exists in the database, the internal database snapshot is not created.

      c. Starting with SQL Server 2005, DBCC CHECK commands create and use an internal database snapshot for consistency purposes when the command performs any checks.


      To resolve this problem, follow these steps instead of running the DBCC CHECK command against the database:

      1. Create a database snapshot of the database for which you want to perform the checks. For more information about how to create a database snapshot, see the "Create a Database Snapshot (Transact-SQL)" topic in SQL Server Books Online.

      2. Run the DBCC CHECK command against the database snapshot.

      3. Drop the database snapshot after the DBCC CHECK command is completed.