April 30, 2012 at 8:46 am
Hi, am looking for a few pointers into what is fundamentally wrong with a statement that is giving this error when I try and run it through Management Studio:
[font="Times New Roman"]Msg 1101, Level 17, State 10, Line 1
Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.[/font]
(This also raises a seperate issue of: occasionally the above error is recorded in the SQL error log, but not when I am manually running it to test. What is the reason for only some occurances to be logged??)
Anyway, my main concern is with statements that seem to come from an application. They begin with:
SELECT DISTINCT TOP 1000 <lots of fields>........FROM
TABLE1 AS T1
WITH (NOLOCK)
JOIN
TABLE1 AS T2 WITH (NOLOCK) ON
T1.xx_id = T2.xx_id
LEFT OUTER JOIN
TABLE3 ON ....few items linked to T1/T2
LEFT OUTER JOIN
TABLE4 on .....few items linked to T1/T2
WHERE ....bit of criteria
TABLE1/2 has 35M rows
TABLE3 has 27K rows
TABLE4 has 6M rows
OK, so I think the vendor is creating a bit of a monster here, but
1) Is there any way for me to determine the size of TEMPDB needed based on the table sizes, and how many rows might be getting selected prior to the 'distinct top 1000'?
2) What should my line of approach be re: telling the vendor their code looks poor??
April 30, 2012 at 12:02 pm
rarara (4/30/2012)
(snip)
1) Is there any way for me to determine the size of TEMPDB needed based on the table sizes, and how many rows might be getting selected prior to the 'distinct top 1000'?
2) What should my line of approach be re: telling the vendor their code looks poor??
1) One way to look at it is whether or not an index is helping that query. If there is no index helping the query then you pretty much have to look at all the resulting rows to see which ones belong in the top 1000. If there is an index that the query can use then I'd imagine it could just fetch the top 1000 rows according to those rows placement in the index being used. What does the estimated query plan tell you about this?
2) I think you should just give yourself a pep talk every morning to develop a tolerance for stuff like that. Oftentimes vendors have their own school of thought about how they're going to design their code, and sometimes we have to support stuff that we may have built differently.
April 30, 2012 at 1:41 pm
How big is tempdb and how big is the disk drive (or lun) that it is on? What else is on thid disk drive or lun?
May 1, 2012 at 1:42 am
One clarification, before somebody misread Lynn’s suggestion as ‘buy more space and resolve the issue’. It just means remove unwanted files from tempdb drive and let SQL Server utilize it in full. It also mean, if tempdb is not adequately sized please do it now.
May 1, 2012 at 2:55 am
OK, a rundown of the sizes are:
Overall Data Drive: 255GB with 132GB Free
*Half of the database datafiles are located here, the rest on a mounted volume of 255GB with 93GB free
* The largest overall database is 250GB having a max table size of 58GB as mentioned before.
* Transaction logs are on another volume of 64GB with 11GB free
*TempDB is located on a further seperate volume, which is only 16GB in size with 7GB free. It consists of 4 data files of roughly 2GB each (not exactly identical - tut tut) and a log of 1.2GB
Does TempDB seem small? What is the strategy - expand it to fit all available space? Would that not be potentially more dangerous if for instance the log ever filled and then would have no more space to expand to? Maybe expand into half of the available 7GB so still leaving a bit more? I suppose the fact that these errors are occuring means expansion is a necessity.
Incidentally, a lot of the 'select distinct top 1000' statements mentioned above don't have any order by. Does that mean they would default to an index order if it existed? Do all the rows get imported to tempdb where the top selection then takes place?
May 1, 2012 at 4:18 am
Incidentally, a lot of the 'select distinct top 1000' statements mentioned above don't have any order by. Does that mean they would default to an index order if it existed? Do all the rows get imported to tempdb where the top selection then takes place?
Can you please attach actual execution plan for the query?
May 1, 2012 at 4:21 am
Msg 1101, Level 17, State 10, Line 1
Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Please verify the autogrowth for Tempdb (default filegroup).
May 2, 2012 at 8:35 am
Also, check to see what the growth rate of the TEMPDB .mdf and .ldf files are and if it is set to extend at all. Also, file growth, is it set to unrestricted or restricted at a certain size.
If the extend size is by percent you should change that to MB and have a value that is good for that.
May 2, 2012 at 10:34 am
definitely Tempdb settings adjustment required here.may be multiple files reuired. do check the percentage of used and free of files here for tempdb.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply