Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 do variable tables use up space on c drive

Author  Topic 

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-03-11 : 10:52:45
if im using a variable table in my sql would it take up space on a c drive or what ever drive the sql is stored on

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-03-11 : 11:09:24
If you mean a table variable, yes, it could spool to the disk files used by TempDB.
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-03-11 : 11:11:38
do u reckon it would take up much space is there a way i can get rid of data i ran a select on the temp table and its empty but since i ran the query the space on the sever seems to have gone and there was alot of space on it well over 40g
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-03-11 : 11:27:47
40 GB is a lot of data, it's not recommended to put that much in a table variable or even a temp table. Where are you getting the data from? What are you trying to accomplish (what's the end result)? Posting your query would help.

Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-03-11 : 11:29:26
no i mean the table i was working with isnt that big but as soon as i ran the query 40g of the server drive became full. is it possible it was my query that did that
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-03-11 : 11:31:28
this was my query
[codeDECLARE @FirstTable TABLE
(
Fk_fund int,
Date datetime,
isin nvarchar(12),
Fundname ntext,
ShareCurrency nvarchar(3),
Pricepershare numeric,
exchanegRate numeric,
FirstSimulation nvarchar(20),
SecondSimulation nvarchar(20),
ThirdSimulation nvarchar (20)
)
INSERT INTO @FirstTable (Fk_fund,
Date,isin,Fundname,ShareCurrency, Pricepershare,
exchanegRate,FirstSimulation,SecondSimulation,ThirdSimulation)
SELECT dp.Fk_fund ,
dp.Date ,
dp.isin ,
dp.Fundname,
dp.ShareCurrency ,
dp. Pricepershare ,
dp. exchanegRate ,
s.FirstSimulation ,
s.SecondSimulation ,
s.ThirdSimulation
FROM generic.DailyPrice dp
inner
join generic.Simulation s
on dp.ISIN =s.ISIN
inner
join generic.HistoricalPrice hp
on dp.isin = hp.isin
where dp.Date > hp.Date
and s.ThirdSimulation is not null
and dp.Date=
(SELECT MAX(Date) FROM generic.DailyPrice)][/code]

i got this error when i ran it
(251605 row(s) affected)
An error occurred while executing batch. Error message is: There is
not enough space on the disk.

but i really dont think there is over 250 rows in any of the tables
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-03-11 : 11:40:19
My first question is Fundname, why is that declared as ntext? It seems unlikely you'd need 1 billion characters for the name of a fund. That probably isn't the cause of the space issue but it's not helping.

The query looks correct as far as the joins are concerned. What value do you get from this query:
SELECT COUNT(*)
FROM generic.DailyPrice dp
inner join generic.Simulation s on dp.ISIN =s.ISIN
inner join generic.HistoricalPrice hp on dp.isin = hp.isin
where dp.Date > hp.Date and s.ThirdSimulation is not null
and dp.Date=(SELECT MAX(Date) FROM generic.DailyPrice)
Another recommendation is to not use the C: drive for any SQL Server data or transaction log files, including TempDB. C: should only be used for operating system files and program executables. If you have another disk available you should try to move the SQL Server files to it instead.
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-03-11 : 11:56:04
well its actully on the d drive i was just putting that down as i needed an answer

on the fund name i didnt set up the db so dont no why they have it as ntext will probably change it no u say it shouldnt be ntext
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-03-11 : 12:05:05
Shouldn't be ntext for a few reasons, one is that ntext is a deprecated data type, nvarchar(max) would replace it. Secondly is that you don't need to use the max variation, nvarchar(256) is probably more than long enough to store a name. I don't know anything about funds but most organizations don't typically work with names over 100 characters.

I'm not clear about where you're having the problem. Your original question mentioned the C: drive, now you say the files are on the D: drive. Which is it? Which drive is having the space issue?

Are your tempdb files set with a maximum file size, and are they near that limit?
Go to Top of Page
   

- Advertisement -