SQL Server Performance

DBCC Checkdb Sparse File running out of system resources

Discussion in 'SQL Server 2005 General DBA Questions' started by cnikirk, Sep 25, 2011.

  1. cnikirk Member

    Hi,

    I have a large database, 2.7 TB. I have been running DBCC Checkdb on this database for years without issue. Suddenly the last few weeks, my Checkdb fails with this error.

    M:\db_name.MDF:MSSQL_DBCC7: Operating system error 1450(Insufficient system resources exist to complete the requested service.) encountered.

    I am running SQL Server 2005 64 bit SP 4.

    I realize this is a sparse file problem, but the mystery is that I have over 700 gig of free space on the volume where the db is stored. My tempdb volume has 500 gig free as well. The server has 128 gig of memory. By using the dbcc checkdb with estimateonly I see that my check should only use 50 gig of space.

    I am lost as to why I'm running out of system resources and I can't figure out any way to fix this. It is almost like SQL Server or windows doesn't see the free space I have.

    Any ideas? Thanks.
  2. Luis Martin Moderator

  3. cnikirk Member

  4. satya Moderator

    At what time you are running the CHECKDB?
    How many filegroups are associated to your database?

    As you referred the database size is enormous and it will take long time to complete. You can take advantage of DBCC CHECKTABLE for the tables that will have frequent addition and deletion of data, unless you come up with the list of tables that are busy in the system you need to change the way of checking the INTEGRITY of the database.
  5. cnikirk Member

    I run it in the early Sunday AM hours as I always have and it takes about 4 hours to complete, I'm on a Raid 10 array with 28 fiber drive spindles, so I have a lot of IO capacity. There is only one filegroup. The DB size hasn't grown much lately, so it doesn't make sense to me that the checkdb would start failing. I am concerned I have a larger problem or that the server is going to start having other problems as well. Like I said, there is no apparent reason this should be failing.

    Thanks Satya.
  6. satya Moderator

    Being it is running on one FILE the IO is really heavy which is causing another issue to complete in time, being the CHECKDB is an resource intensive operation you should go about running DBCC CHECKCATALOG or CHECKTABLE for active tables than running for whole database.

Share This Page