SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 do variable tables use up space on c drive
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rjhe22
Constraint Violating Yak Guru

254 Posts

Posted - 03/11/2013 :  10:52:45  Show Profile  Reply with Quote
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
15688 Posts

Posted - 03/11/2013 :  11:09:24  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

254 Posts

Posted - 03/11/2013 :  11:11:38  Show Profile  Reply with Quote
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

USA
15688 Posts

Posted - 03/11/2013 :  11:27:47  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

254 Posts

Posted - 03/11/2013 :  11:29:26  Show Profile  Reply with Quote
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

254 Posts

Posted - 03/11/2013 :  11:31:28  Show Profile  Reply with Quote
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

USA
15688 Posts

Posted - 03/11/2013 :  11:40:19  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

254 Posts

Posted - 03/11/2013 :  11:56:04  Show Profile  Reply with Quote
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

USA
15688 Posts

Posted - 03/11/2013 :  12:05:05  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000