Need Help:The free space in properties of TempDB shows 95% free space. But the Data files dont have any free space.

Question

Need Help:The free space in properties of TempDB shows 95% free space. But the Data files dont have any free space.

hemanthnightmoon

SSC Veteran

Points: 269
More actions
July 9, 2012 at 3:30 pm

#263494

The free space in properties of TempDB shows 95% free space. But the Data files dont have any free space. When i go to shrink files and select the Data files they show 99.1 % filled up. I split them according to the number of processors..and combining all files the size of MDf is around 30 GB. It is not even set to autogrowth. But due to some process it suddenly gets filled up. And the application keeps throwing the error.
Event ID:3758
Could not allocate space for object XXX: 422284921077760' 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.;
Please help me..

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 1

hemanthnightmoon (7/9/2012)
But due to some process it suddenly gets filled up. And the application keeps throwing the error.

You're correctly identified the problem. "Some process" probably has a "many-to-many" join it it. Another name for such a join is an "Accidental Cross Join".

You need to find out what that process or stored procedure is and redact it so it no longer consumes so much TempDB during its joins.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 2

Jeff has clued you in. Tempdb works on temp objects, and you need to handle the peak. If you run out of space when you run queries, you don't have enough. When those queries end, or cancel, tempdb will not have all that space consumed.

hemanthnightmoon SSC Veteran Points: 269 More actions · Answer 3

Thanks Both,

What i did is

select getdate() AS runtime, SUM (user_object_reserved_page_count)*8 as usr_obj_kb,

SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,

SUM (version_store_reserved_page_count)*8 as version_store_kb,

SUM (unallocated_extent_page_count)*8 as freespace_kb,

SUM (mixed_extent_page_count)*8 as mixedextent_kb

FROM sys.dm_db_file_space_usage

and found out that the Version_store_reserved_page_count filled upto 30GB. So i found out the most expensive query that is filling out this verison store and killed it. It took some time to rollback, after that it cleared my Version_store.

I have the doubt like why this Version_store gets filled up..? I have checked the isolation levels and both snapshot and is_read_committed both are set to 0.? Is there any way to clear this Version_store data..?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

hemanthnightmoon (7/9/2012)
Thanks Both,
What i did is
select getdate() AS runtime, SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM (version_store_reserved_page_count)*8 as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb,
SUM (mixed_extent_page_count)*8 as mixedextent_kb
FROM sys.dm_db_file_space_usage
and found out that the Version_store_reserved_page_count filled upto 30GB. So i found out the most expensive query that is filling out this verison store and killed it. It took some time to rollback, after that it cleared my Version_store.
I have the doubt like why this Version_store gets filled up..? I have checked the isolation levels and both snapshot and is_read_committed both are set to 0.? Is there any way to clear this Version_store data..?

THe version store is also used by triggers to persist the INSERTED and DELETED tables. So a query that modifies a large set of data with a trigger involved could be the issue.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

hemanthnightmoon SSC Veteran Points: 269 More actions · Answer 5

Hi ,

I used some DMVs and got the spid, of the process. Now when i do DBCC input buffer of the process, it gives a huge query.

The stored proc is getting called from Shared point, can you please guide me over on how to identify any triggers that are getting executed. I want to understand which stored procs and which trigeers are getting executed.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

If you know the query, then you have the tables where the data is being modified so you can query sys.triggers to see if any of those tables have triggers on them.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question