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. |
|
|
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 |
|
|
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. |
|
|
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 |
|
|
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 dpinner 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 isnot enough space on the disk.but i really dont think there is over 250 rows in any of the tables |
|
|
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 dpinner join generic.Simulation s on dp.ISIN =s.ISINinner join generic.HistoricalPrice hp on dp.isin = hp.isinwhere dp.Date > hp.Date and s.ThirdSimulation is not nulland 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
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 |
|
|
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? |
|
|
|