July 10, 2012 at 12:31 pm
I have this message earlier today. My Temp DB has 8 files with all of them in restricted growth.
Can I shrink the data files or something please advice how to trouble shoot the same.
Msg 1105, Level 17, State 2, Line 2
Could not allocate space for object '<temporary system object: 422568035418112>' 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.
July 10, 2012 at 12:41 pm
It sounds like the data file grew to meet the max restriction on growth for that file. Are you out of disk space too?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 10, 2012 at 1:15 pm
We have all 4 system databases on a mount point in which only 43 GB is used and 956 GB is free. Please advice what to do here to avoid this error. Is there a way to shrink the data files(MDF file). How should I trouble shoot it.
July 10, 2012 at 1:22 pm
Two things you can do. One, increase the max size that the tempdb files can grow. This could be just a temporary fix. Two, find out what process or processes are causing tempdb to grow so much and look at reengineering them to reduce their need on tempdb. Sounds like there is some performance tuning that could be done.
July 10, 2012 at 1:31 pm
Lynn Pettis (7/10/2012)
Two things you can do. One, increase the max size that the tempdb files can grow. This could be just a temporary fix. Two, find out what process or processes are causing tempdb to grow so much and look at reengineering them to reduce their need on tempdb. Sounds like there is some performance tuning that could be done.
That is the same direction I was heading.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 10, 2012 at 1:40 pm
As of now we have increased the primary file allocation from 5 GB to 6GB. have to follow up with the queries that users fire on this sql instance to see why these temp DB is getting filled up so much.
Thanks a ton for your swift response.
July 10, 2012 at 1:42 pm
You are welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply