check database integrity. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

check database integrity.

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??
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/
Check the tempdb location by running…
exec sp_helpdb tempdb
and make sure where tempdb mdf file resides has enough space to grow…. http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
it has 165gb of free space.
Check the space on primary file group. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/
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.
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.
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.
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 />
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.
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.
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.
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
]]>