July 13, 2018 at 2:46 am
Hello,
I am having trouble running DBCC CHECKDB (2012) on a 400GB Database, the tempdb (200GB) is running of space!
Error is:Could not allocate space for object ‘dbo.SORT temporary run storage: 140751663071232’ in database ‘tempdb’ because the ‘PRIMARY’ filegroup is full.
On SQL 2005 Server the tempdb is 30GB, 1 Data File, 1 Filegroup, The command completes OK.
On a NEW SQL 2012 Server the tempdb is 200GB (dedicated temp drive) 4 Data Files-1000mb each, Unlimited, autogrowth on @100MB, 1 Filegroup.
I've tried: Defragging the disk, Re-Indexing Fragmented tables, adding more files to the tempdb, Updating to the latest SP4 (as there were some bugs around tempdb consumption) and still all the time the process ranges from 2.5hrs to 4.5hrs and consumes all of the 200GB.
I know the culprit is one specific table which has thousands of rows and pages. Just need to work out solution long-term instead of throwing more disk at the problem as a short-term solution.
Any recommendations?
July 13, 2018 at 3:12 am
Your below statement doesn't make sense:
On a NEW SQL 2012 Server the tempdb is 200GB (dedicated temp drive) 4 Data Files-1000mb each
If your 4 data files were 1000MB each, your tempdb would only be 3.9GB in size. Assume you mean that the initial size of the database is 4 * 1000MB files?
If your tempdb is saying it can't get any bigger, then that suggests you're out of disk space (as the data files have unlimited growth), or your log file(s) have reached the maximum limit. The fact that is if that large, with a 3.9GB initial size, also suggests that some process heavily made use of it at some point since you last started the service.
Do you have a size limit on your log files for tempdb? How often do you restart your service as well, out of interest?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 13, 2018 at 3:27 am
Thom A - Friday, July 13, 2018 3:12 AMYour below statement doesn't make sense:On a NEW SQL 2012 Server the tempdb is 200GB (dedicated temp drive) 4 Data Files-1000mb each
If your 4 data files were 1000MB each, your tempdb would only be 3.9GB in size. Assume you mean that the initial size of the database is 4 * 1000MB files?
If your tempdb is saying it can't get any bigger, then that suggests you're out of disk space (as the data files have unlimited growth), or your log file(s) have reached the maximum limit. The fact that is if that large, with a 3.9GB initial size, also suggests that some process heavily made use of it at some point since you last started the service.
Do you have a size limit on your log files for tempdb? How often do you restart your service as well, out of interest?
Hello Thom A,
First of all many thanks for the swift response!
During this troubleshooting I've had to regularly restart the service to reclaim the tempdb (drive) space back from Zero.
Initial Size is:
10000MB x 4 Data Files
2500MB for Log File
Both Unlimited Growth by 100MB.
No other process should be using this server as it's not in production yet.
To give you an idea the old server(2005 the tempdb was based on data drive and had approx 50/60gb disk and never failed. or ran out of space.
July 13, 2018 at 3:33 am
AP24 - Friday, July 13, 2018 3:27 AM10000MB x 4 Data Files
AP24 - Friday, July 13, 2018 2:46 AM4 Data Files-1000mb each
10,000 or 1,000 Megabtyes? There's a difference of 8.78GB there. What is the total disc size of the drive tempdb is located on? The fact that you state you're restarting the service frequently to release the space sounds like you do have someone really hitting tempdb, frequently.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 13, 2018 at 3:36 am
Thom A - Friday, July 13, 2018 3:33 AMAP24 - Friday, July 13, 2018 3:27 AM10000MB x 4 Data FilesAP24 - Friday, July 13, 2018 2:46 AM4 Data Files-1000mb each10,000 or 1,000 Megabtyes? There's a difference of 8.78GB there. What is the total disc size of the drive tempdb is located on? The fact that you state you're restarting the service frequently to release the space sounds like you do have someone really hitting tempdb, frequently.
10,000 MegaBytes Each.
TempDB Drive is 200GB capacity.
Only restarting to clear as I've tried many different things and been re-running the DBCC CHECKDB command.
July 16, 2018 at 6:34 am
With that machine NOT in production yet, pull it's network cable and work directly from it's console and see if anything changes...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 16, 2018 at 8:26 am
sgmunson - Monday, July 16, 2018 6:34 AMWith that machine NOT in production yet, pull it's network cable and work directly from it's console and see if anything changes...
I have to ask, what's the thinking behind that? What do you think is happening or going to happen?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 16, 2018 at 9:09 am
Jeff Moden - Monday, July 16, 2018 8:26 AMsgmunson - Monday, July 16, 2018 6:34 AMWith that machine NOT in production yet, pull it's network cable and work directly from it's console and see if anything changes...I have to ask, what's the thinking behind that? What do you think is happening or going to happen?
Monitoring software imposed burdens, previously unknown users getting early access to the box simply because they're in a certain AD group and deciding to use it as a playground... etc., etc...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply