September 2, 2015 at 1:58 am
Hi everyone.
I'm hoping that someone will have a suggestion to a problem that keeps cropping up.
SQL 2008 R2 SP2.
I regularly run checkdb against all the databases on all the instances I support. Every now and again I have a problem running the checkdb against a 450gig (and growing) database supporting SharePoint. The problem is lack of disk space in TempDB. At the scheduled time, no other processes are running. The TempDB database is 25gig data and 2gig log. I cannot extend the TempDB disk space (this has solved the problem when it has happened in the past) due to lack of physical disk space.
I'm sure I read something in the past that gave a formula for predicting how much TempDB disk space would be required for a checkdb and that 25gig is more than adequate.
The error I'm getting is...
storage: 141096612790272' 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. [SQLSTATE 42000] (Error 1105) Error converting data type nvarchar to int. [SQLSTATE 42000] (Error 8114) CRM_Sharepoint_fNDDocs [SQLSTATE 01000] (Error 0). The step failed.
Any ideas much appreciated.
Thanks, Rob
September 2, 2015 at 3:19 am
Try increasing the size of TempDB, you're just running out of space. Put extra files on another drive if necessary, or get a larger drive for it.
Without seeing the formula you mention, I can't comment, but CheckDB's EstimateOnly has bugs and returns a value far too low. CheckDB can use a huge amount of tempDB space.
Alternately, try breaking CheckDB up into CheckAlloc, CheckCatalog and CheckTable on all tables, probably there's one really large table that's requiring all the space to be checked.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply