SQL Server Memory Issues - SQL Server Forums

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

SQL Server Memory Issues

Author

Topic

Johnny1985
Starting Member

26 Posts

Posted - 2007-05-24 : 08:10:49

Hello,

I'm really into getting on the edge with softwares .. and so I'm trying to store 1,000,000,000 randomized strings (varchar of 20) into my DB (Local).

I'm using SQL Server 2005.

HD Storage is not a problem, however while reaching about 70,000,000 strings spot, I noticed the sqlservr.exe process was about 1.6 Gig of RAM memory usage (out of 2Gig).

1) Why is that ?
2) How else can I store this massive data if not DB ?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-24 : 08:31:36

best bet is to insert it in batches or with bulk import.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Johnny1985
Starting Member

26 Posts

Posted - 2007-05-24 : 08:37:10

[quote]Originally posted by spirit1

best bet is to insert it in batches or with bulk import.

What do you mean .. ?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-24 : 08:55:23

in batches means that you insert say 10000 rows at once.

bulk insert means to use bulk insert T-SQL command.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Johnny1985
Starting Member

26 Posts

Posted - 2007-05-24 : 08:59:54

I did inserted them row by row, however what's the relationship to sqlservr.exe memory usage ? am I to expect the same ?

As long as I don't have any HD Storage problem, this shouldn't cause trouble.. but it does .. :/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-24 : 09:49:51

Sql will use all available memory by default. If you need memory for other apps on the server, you can set max memory in sql.

Johnny1985
Starting Member

26 Posts

Posted - 2007-05-25 : 04:32:21

I'm trying to insert data from file with the BULK INSERT command, but I get "BULK INSERT SQL construct or statement not supported".
help ..

Where can I Set sqlservr.exe's maximum memory usage ?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-25 : 09:39:01

You can set max memory in ssms server properties or with sp_configure. What's your sql2k5 edition? Does your sql login in bulkadmin server role?

Johnny1985
Starting Member

26 Posts

Posted - 2007-05-25 : 12:16:31

ok.. thanks for the memory part. did that.

and yes, I do have a user under bulkadmin. still getting that error.

have you done anything special before using the INSERT BULK command ?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-25 : 12:24:35

Nothing special to run bulk insert command, does you account have insert permission on target table?

Johnny1985
Starting Member

26 Posts

Posted - 2007-05-25 : 12:33:57

I've granted any permissions I could find ...
still - same error of unknown command.

can you *please* create a new user for your local DB and try activate BULK INSERT ?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-25 : 13:59:24

No problem on my servers. What's your sql2k5 edition and sp level?

Johnny1985
Starting Member

26 Posts

Posted - 2007-05-25 : 14:23:38

2005: v9.00.1399.06, Server Management Studio v9.00.3042.00

don't know what you mean by "sp level" ..

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-25 : 14:29:41

sp = service pack

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp