Disk space eats up rapidly when I execute a insert statement

Question

Disk space eats up rapidly when I execute a insert statement

pink.thereisnoif

SSC Rookie

Points: 37
More actions
November 4, 2009 at 5:36 am

#213379

Hi All,
We have a database named 'Track' running on a 64 bit Sql Server 2005 enterprise edition.
Currently the Track DB occupies about 100G space, with 77G data, 16G index, mainly on Primary filegroup.
The primary filegroup has one data file, with autogrowth enabled at 100M with no limit size. The log file is also autogrowth.
When I execute a 'select * into ' query, the disk space is been eaten up rapidly, mainly the mdf file.
It dosen't complete after 30 minutes, so I have to stop the query.
Now the mdf file is 160G, increased about 60G.
The result of 'sp_spaceused' shows:
database_name | database_size | unallocated_space
Track | 163649.94MB | 70518.09MB
reserved | data | index_size | unused
94258216KB | 77720304KB | 16200240KB | 337672KB
Now I have to shrink the the database data file to free the occupied space.
What's the problem?

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

pink.thereisnoif (11/4/2009)
Hi All,
We have a database named 'Track' running on a 64 bit Sql Server 2005 enterprise edition.
Currently the Track DB occupies about 100G space, with 77G data, 16G index, mainly on Primary filegroup.
The primary filegroup has one data file, with autogrowth enabled at 100M with no limit size. The log file is also autogrowth.
When I execute a 'select * into ' query, the disk space is been eaten up rapidly, mainly the mdf file.
It dosen't complete after 30 minutes, so I have to stop the query.
Now the mdf file is 160G, increased about 60G.
The result of 'sp_spaceused' shows:
database_name | database_size | unallocated_space
Track | 163649.94MB | 70518.09MB
reserved | data | index_size | unused
94258216KB | 77720304KB | 16200240KB | 337672KB
Now I have to shrink the the database data file to free the occupied space.
What's the problem?

the problem is your 'select * into ' query,

that literally says to make a copy of your huge table and put the copy into another table. if that table is 100Gig, well...it'll take a while to grab space and copy that 100 gig table.

most likely you do not need to make a copy of the data; you could use a CTE or just a tighter WHERE statement in your process.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pink.thereisnoif SSC Rookie Points: 37 More actions · Answer 2

Thanks Lowell,

Yes, the problem comes from the 'select into ' query.

The query was executed by a careless dev, which produced an almost infinite amount of result rows...

The query is as below:

select b.id, a.amount into result

from log a, member b

where a.logtime < '2009-11-01'

and table 'log' has 2 million rows, 'member' has more the 30 million rows.

Imagine how many records will the query produce....

SteveBell SSCrazy Points: 2008 More actions · Answer 3

and table 'log' has 2 million rows, 'member' has more the 30 million rows.
Imagine how many records will the query produce....

I'm imagining about 60,000,000,000,000 😉

sejal p gudhka SSCrazy Points: 2102 More actions · Answer 4

My questions is why do you require select into another table in same database ?

Geoff A SSChampion Points: 11511 More actions · Answer 5

sejal p gudhka (10/8/2010)
My questions is why do you require select into another table in same database ?

lol. a question that every DBA has asked a DEV at least once 😛

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

sejal p gudhka (10/8/2010)
My questions is why do you require select into another table in same database ?

I've done it on rare occassions to produce flattened results during overnight rebuilds for reporting systems. It's not a common practice, though, and would usually only be done during maintenance/data refreshes.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA