| Author |
Topic |
|
DBA007
Posting Yak Master
145 Posts |
 Posted - 2010-02-11 : 10:39:19
|
| Team,My Maintenanceplan has failed stating the error as followsExecuting the query "DBCC CHECKDB WITH NO_INFOMSGS" failed with the following error: "Could not allocate space for object 'dbo.SORT temporary run storage: 140737729003520' 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 transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databasesCheck terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.I have even restarted the services of sqlserver but of new use. |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-02-11 : 11:57:56
|
| A few questions:1) Are you out of space on the drive where TempDB is located?2) Can you move TempDB to another drive?3) What value is in the sys.databases.log_reuse_wait_desc column when you select from it?=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain) |
 |
|
|
DBA007
Posting Yak Master
145 Posts |
Posted - 2010-02-11 : 12:04:06
|
| The drive whare the databases reside contains 3Gb of free space and when i run sys.databases.log_reuse_wait_desc I found all zeros for all the coumns. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-11 : 12:42:59
|
| TEMPDB set with a fixed limit?Does TEMPDB return to its "normal" (i.e. minimum) size when SQL Server is restarted? If not it probably needs rebuilding |
|
|
|
DBA007
Posting Yak Master
145 Posts |
Posted - 2010-02-11 : 13:42:28
|
| yes tempdb has retun to normal.but the maintenanceplan had failed even after that |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-11 : 13:46:56
|
| Is "TEMPDB set with a fixed limit?" ?? |
|
|
|
DBA007
Posting Yak Master
145 Posts |
Posted - 2010-02-11 : 14:15:04
|
| no,it has set to grow with 10% |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-11 : 15:13:28
|
| Run DBCC CHECKDB WITH ESTIMATEONLY to see what the estimated tempdb size is? |
|
|
|
DBA007
Posting Yak Master
145 Posts |
Posted - 2010-02-11 : 15:25:41
|
| it is showing as 39 |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-02-11 : 19:58:58
|
| What exactly is you Maintenance Plan Doing? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-12 : 03:07:07
|
| "it is showing as 39"Well that's KB ... so it shouldn't need hardly any space at all, so I expect it is whatever you are using for the maintenance that is causing the problem.I've asked about what tools you use for maintenance before, but never seem to have had a straight answerYou appear to be using some 3rd party software for your backup (and possibly your maintenance).You frequently post errors on here that you are gettingIf this is from a small number of servers you look after (rather than from a small number that have errors, but you look after thousands of servers) then whatever you are using for maintenance should be chucked out and replaced with something more robust. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-02-12 : 03:54:36
|
quote:
Originally posted by DBA007
it is showing as 39
Did you try CheckDB with EstimateOnly on ALL databases on that server, or just one? By default a maintenance plan checks ALL databases on the server--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-12 : 04:39:40
|
| Good point! I assumed that DBA007 would run it on the one that had failed ... |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-02-12 : 09:32:28
|
| Maybe, but he may not know which DB it failed on. The maint plan for CheckDB by default does all the DBs and there's nothing in the error message in the original post to indicate which DB it was doing at the time. Ok, the CheckDB progress will be in the error log, but not everyone reads the error log.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-12 : 10:01:46
|
| Good point. I had assumed (dangerous that!) that "dbo.SORT" was only going to be in one DB |
|
|
|
DBA007
Posting Yak Master
145 Posts |
Posted - 2010-02-12 : 13:59:20
|
| I get different tempdbvalues for CHECKALLOC and CHECKTABLES for different databases when i run CheckDB with EstimateOnly |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-12 : 14:15:00
|
| Yeah, you would, but the largest value is the only one you have to worry about. What's that? |
|
|
|
DBA007
Posting Yak Master
145 Posts |
Posted - 2010-02-12 : 14:59:52
|
| The following are some of the databases which contains highest valuesDB1Estimated TEMPDB space needed for CHECKALLOC(KB)6941Estimated TEMPDB space needed for CHECKTABLES(KB)17623119--------DB2Estimated TEMPDB space needed for CHECKALLOC(KB)7242Estimated TEMPDB space needed for CHECKTABLES(KB)17618498--------DB3Estimated TEMPDB space needed for CHECKALLOC(KB)5007Estimated TEMPDB space needed for CHECKTABLES(KB)27405251-------DB4Estimated TEMPDB space needed for CHECKALLOC(KB)7609Estimated TEMPDB space needed for CHECKTABLES(KB)17635088--------DB5Estimated TEMPDB space needed for CHECKALLOC(KB)3982Estimated TEMPDB space needed for CHECKTABLES(KB)24948063--------DB6Estimated TEMPDB space needed for CHECKALLOC(KB)3975Estimated TEMPDB space needed for CHECKTABLES(KB)24551389 |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-02-12 : 16:17:12
|
| I think I see your problem clearly. DB1, DB2 and DB4 need 17GB of space in TempDBDB3, DB5 and DB6 all need over 20GB of space in TempDBYou have 3GB free on that disk. Hence you're running the drive out of spaceEither increase the size of that drive, move Temp to a drive with more space or add a data file that's on a drive with more space available.--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-02-12 : 16:23:20
|
| I'm going to take a guess that you have lots of indexed views in these databases. That's mostly what CheckDB uses TempDB for while it's running, to check that what an indexed view contains is what it should contain.--Gail ShawSQL Server MVP |
|
|
|
|
TempDB Error