SQL Server Performance

check database integrity.

Discussion in 'SQL Server 2005 General DBA Questions' started by preethi.talapanuri, Apr 19, 2007.

  1. preethi.talapanuri New Member

    Hi

    there is a MP which checks the datbase integrity. it is failing due to the following error.

    Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
    " failed with the following error: "Could not allocate space for object 'dbo.SORT temporary run storage: 209421225033728' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
    The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    what do i need to do??
  2. ndinakar Member

    Looks like the drive to which your filegrup is allocated is almost full. check that and perhaps add more drives to the filegroup and try the command again.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  3. MohammedU New Member

  4. preethi.talapanuri New Member

    it has 165gb of free space.
  5. ndinakar Member

    Check the space on primary file group.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  6. preethi.talapanuri New Member

    Both of them are on the F drive and it has 165gb of free space. do you think i have to shrink the datafiles.

    we restrict the data files to grow to a certain extent and its full now.

  7. MohammedU New Member

    Make sure tempdb files are not configure to "Restricted file growth"

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  8. satya Moderator

    This happens when the DBCC process is trying to perform and demanding more space on TEMPDB (primary filegroup) and usual process on TEMPDB is trying to increase its size. The clash of process throws the immediate error about primary filegroup is full, the best option is to resize the TEMPDB to 10% more to accomodate the relevant quries.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  9. MohammedU New Member

    This clash can be called as a SQL bug? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />
  10. satya Moderator

    No, I don't think so. It is purely an action from hardware and SQL Server process. How can you say when SQL is demanding more space and hardware is not able to cope up.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  11. MohammedU New Member

    In that case sql server has to wait or timeout or throw the right error instead giving incorrect error.

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  12. satya Moderator

    No timeout is only for SQL processes and not for Operating System, using Sysinternals tools you can see this happens on OS level and I don't feel that is an incorrect error as the space demanding process has upper hand to finish that task.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  13. kinuthiapeters New Member

    Just increase the size of the tempDB to a size higher than the current size.run the querry again.if you get the error again increase the size again to a higher value i.e
    assume it was originally 1024Mb,
    increase to 2048 run the querry if you get the error increase thye size again,
    what is happening is that the temp Db size is too small to accommodate the size of the querry that you are executing.
    regards
    kinuthia

Share This Page