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
 Problem with "Could not allocate space"

Author  Topic 

Gery
Starting Member

16 Posts
Posted - 2011-05-17 : 12:40:56
Dear SQL users,

I just tried to import one table that is 3 GB in size. After 40 minutes, I received the following message:

[code]
Msg 1105, Level 17, State 2, Line 2
Could not allocate space for object 'dbo.bathymetry' in database 'database' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
[\code]

I have 160 GB free space in C where SQL server 2008 is installed, and 122 GB free space in D where this new table is stored. Also I'm running the SQL server in Windows 7 64-bits, 8 GB RAM, Intel i5 3.2 GHz.

What could be the solution to circumvent this situation? The image for the database configuration is here: http://i54.tinypic.com/2zolob5.png

Any hint will be very grateful,

Best regards,

Gery

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2011-05-17 : 12:52:33
Manually grow out your data file to accomodate this import. It's likely it couldn't grow fast enough.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Gery
Starting Member

16 Posts
Posted - 2011-05-18 : 08:50:37
Thank you Tara for your answer. I did it but I'm still getting the same error. Just to know if I did it right, I have to go to my_database/properties/files and in Autogrowth press the three points (...) and in File Growth (in Megabytes) put something like 10.000 Mb (so 10 GB), is it right? I also let the "Enable Autogrowth" and the "Unrestricted File Growth" in on.

For correlative numbers as ID, should I choose "int" or "nvarchar(100)" as a data type? I think it is "int", however, that generates an error in this specific table that has aprox. 40 million rows and 8 columns.

Any idea will be very grateful,

Best regards,

Gery
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2011-05-18 : 09:42:44
HOW..are you doing your "Import"



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page

Gery
Starting Member

16 Posts
Posted - 2011-05-18 : 09:47:15
this is my code:


--create table using the database already created
use database
go

--create table to insert data
create table dbo.bathymetry
(
x_dd nvarchar(100),
y_dd nvarchar(100),
x_utm nvarchar(100),
y_utm nvarchar(100),
z nvarchar(100),
campaign_id nvarchar(25),
survey_id nvarchar(25),
bathymetry_id int primary key
)
go

--bulk insert the data from the csv file
DECLARE @row_terminator CHAR;
SET @row_terminator = CHAR(10); -- or char(10)

DECLARE @stmt NVARCHAR(2000);;
SET @stmt = '
	bulk
	insert bathymetry
		from ''d:\database\tables\bathymetry.csv''
		with
			(
			fieldterminator = '';'',
			firstrow = 2,
			ROWTERMINATOR='''+@row_terminator+'''
			)'
exec sp_executesql @stmt;

--check the data in the table
select *
from bathymetry
go


and this is the error message:


quote:

Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1000001, column 8 (bathymetry_id).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1000002, column 8 (bathymetry_id).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1000003, column 8 (bathymetry_id).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1000004, column 8 (bathymetry_id).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1000005, column 8 (bathymetry_id).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1000006, column 8 (bathymetry_id).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1000007, column 8 (bathymetry_id).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1000008, column 8 (bathymetry_id).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1000009, column 8 (bathymetry_id).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1000010, column 8 (bathymetry_id).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1000011, column 8 (bathymetry_id).
Msg 4865, Level 16, State 1, Line 2
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2011-05-18 : 09:54:01
Create a separate filegroup for this table and set the size with code

And why the dynamic SQL?

and are you sure the data file is semi colon delimited and matches the table?

bcp database.dbo.bathymetry in d:\database\tables\bathymetry.csv -T -Sservername -c -t";"

try that

do you truncate the table first??



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page

Gery
Starting Member

16 Posts
Posted - 2011-05-18 : 10:11:05
Thanks Brett for your help, I tried the code:


bcp database.dbo.bathymetry in d:\database\tables\bathymetry.csv -T -Sservername -c -t";"


but it appears:
quote:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.


how can I create a separate file group for this table and set the size with code? I'm new about this stuff, so why dynamic SQL? I really don't know.

Yes, it has the semi-colon as separator. I generated this table in linux, is ASCII text (40 million rows and 8 columns). In fact I created a similar table in linux, with just 170 thousand rows and 13 columns and I indeed uploaded it into my database with the same code I showed here. I'm having problems with these ones, but with CSV generated in excel (ASCI text with CLRF terminators) I had no problems.

how can I truncate the table? no idea, I'd be very grateful if you could help me.

Thanks,
Go to Top of Page

Gery
Starting Member

16 Posts
Posted - 2011-05-18 : 10:58:00
After surfing and googleing a bit, I understand that my code is composed of dynamic code, perhaps T-SQL, not sure, but what I know is that this code worked before with another table similar to the one I'm trying to load. If it is right or not? the thing is that it worked before and it should work now and in the future, in that way work right?

what is the objective of truncate the table? I don't want to get rid of the data and let the table, I just want to load the CSV created in linux (3GB size) into my database.

Please any support is appreciated,

Thanks in advance,
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2011-05-18 : 12:13:56
well TRUNCATE would minimally log the deletion of all the rows

If you want to retain the data, and Add another 3 GB, how many rows do you expect to end up with?

Are there any Unique Indexes or Primary Keys on the table now?

Your code has USE [database]

Is the name of the database really [database]???

That's a reserved word...I'm surprised you didn't have issues if that's true

Can we try this

CREATE TABLE myTable99(Col1 varchar(max))
GO

DECLARE @cmd varchar(max)

SET @cmd = 'bcp database.dbo.myTable99 in d:\database\tables\bathymetry.csv -T -S<servername> -c'

exec master..xp_cmdshell @cmd

Make SURE you change <servername> to be your server with the instance if you have one

EDIT: ALSO Make sure your database name is really [database]

let me know if that works or if you get an error





Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page

Gery
Starting Member

16 Posts
Posted - 2011-05-18 : 12:43:56
Thanks Brett again and thanks also for your patience!

sorry man, I've been changing these names a lot to test what is going on and "database" is not the name, you're completely right, the name is "databasecsv". About the rows I expect to have in my database, I want to load and retain the whole file, this is around 40 million rows and 8 columns. This is the first 11 rows of the file:


x_dd;y_dd;x_utm;y_utm;z;campaign_id;survey_id;bathymetry_id
-79.8283607;-9.2788864;628687.9588735;8974102.5077363;-1031.39;g3peru;mbb;1
-79.8281673;-9.2787388;628709.2577794;8974118.7594240;-1027.02;g3peru;mbb;2
-79.8279898;-9.2786031;628728.8057394;8974133.7009500;-1024.15;g3peru;mbb;3
-79.8278378;-9.278487;628745.5453749;8974146.4843216;-1024.22;g3peru;mbb;4
-79.8276931;-9.2783765;628761.4810690;8974158.6510796;-1024.87;g3peru;mbb;5
-79.8275401;-9.2782597;628778.3308327;8974171.5114840;-1024.02;g3peru;mbb;6
-79.8273932;-9.2781476;628794.5088034;8974183.8543631;-1023.61;g3peru;mbb;7
-79.8272367;-9.2780281;628811.7440526;8974197.0120555;-1021.42;g3peru;mbb;8
-79.8270918;-9.2779173;628827.7018691;8974209.2118931;-1020.47;g3peru;mbb;9
-79.8269517;-9.2778104;628843.1309883;8974220.9822000;-1019.89;g3peru;mbb;10


Yes, I created a column with correlative numbers in order to use it as primary key, it's the last column of the CSV file. Can it be that last column the problem?

I tried your code and I got:


Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.


According to this I'm not the administrator, but in fact I'm!, what should I do instead of going out of the room and scream!!??

Thanks again,
Go to Top of Page

Gery
Starting Member

16 Posts
Posted - 2011-05-18 : 12:47:41
I'll post again the code I'm using for this, hope this helps:


--create table using the database already created
use databasecsv
go

--create table to insert data
create table dbo.bathymetry
(
x_dd nvarchar(100),
y_dd nvarchar(100),
x_utm nvarchar(100),
y_utm nvarchar(100),
z nvarchar(100),
campaign_id nvarchar(25),
survey_id nvarchar(25),
bathymetry_id int primary key
)
go

--bulk insert the data from the csv file
DECLARE @row_terminator CHAR;
SET @row_terminator = CHAR(10); -- or char(10)

DECLARE @stmt NVARCHAR(2000);;
SET @stmt = '
	bulk
	insert bathymetry
		from ''d:\databasecsv\tables\bathymetry.csv''
		with
			(
			fieldterminator = '';'',
			firstrow = 2,
			ROWTERMINATOR='''+@row_terminator+'''
			)'
exec sp_executesql @stmt;

--check the data in the table
select *
from bathymetry
go
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2011-05-18 : 12:53:32
well, it just means that the surface has not turned xp_cmdshell on...

No biggie..just try the bulk insert into that table..without dynamic SQL


	BULK INSERT INTO myTable99
	FROM 'd:\databasecsv\tables\bathymetry.csv'
	WITH(FIRSTROW = 2)


And I see why you have dynamic sql...it was cut and pasted from Books Online

quote:


DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT AdventureWorks2008R2.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>''
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)



Which is usually not a bad thing



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site