| Author |
 Topic  |
|
|
rjhe22
Posting Yak Master
151 Posts |
 Posted - 03/11/2013 : 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
USA
15559 Posts |
Posted - 03/11/2013 : 11:09:24
|
| If you mean a table variable, yes, it could spool to the disk files used by TempDB. |
 |
|
|
rjhe22
Posting Yak Master
151 Posts |
Posted - 03/11/2013 : 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 |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 03/11/2013 : 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.
|
|
|
|
rjhe22
Posting Yak Master
151 Posts |
Posted - 03/11/2013 : 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 |
|
|
|
rjhe22
Posting Yak Master
151 Posts |
Posted - 03/11/2013 : 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 |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 03/11/2013 : 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. |
|
|
|
rjhe22
Posting Yak Master
151 Posts |
Posted - 03/11/2013 : 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 |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 03/11/2013 : 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? |
|
|
| |
Topic |
|
do variable tables use up space on c drive
