I Thought SQL Server Has No Limits

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

SQL Server 2000 Forums

SQL Server Development (2000)

I Thought SQL Server Has No Limits

Author

Topic

akz
Starting Member

6 Posts

Posted - 2004-09-12 : 12:21:58

I have created a database in SQL Server 2000. We create at least 2 millions records a day. Every thing is fine until row size reaches around 40 million records. Then it cannot accept anymore records or allow me to create indexes to speed up queries. I create a separate db each month to limit db sizes.

What am I doing wrong?

Many thanks
Akz

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-09-12 : 12:53:39

what edition are you using?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-12 : 12:58:10

maybe you're exceding maximum db size of 4 Gb?? take a look at that...

this is also helpfull:
http://www.nigelrivett.net/
under administration

Go with the flow & have fun! Else fight the flow

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-12 : 13:20:56

Could be the edition (personal and msde have a low size limit).
Could be the disk format - do you have ntfs?

How big do the database files get before it fails.
40 million recs in itself won't be a problem nor will 100Gb databases.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

akz
Starting Member

6 Posts

Posted - 2004-09-14 : 15:18:22

I have used both Developer and Enterprise editions. I also use ntfs filesystem.
DB file sizes get up to 6 or 7 GBs.

Many thanks
Akz

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-14 : 15:20:48

What is the error that you are getting?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-14 : 15:30:00

quote:
Originally posted by spirit1

maybe you're exceding maximum db size of 4 Gb??

Huh?

OK, pony up the $$$$

My Money is on the log being blown out....or tempdb

Any takers?

Brett

8-)

akz
Starting Member

6 Posts

Posted - 2004-09-14 : 15:31:31

I get the following error: Server: Msg 3624, Level 20, State 1, Line 1
The error message also complains about a violated assertion to do with max row size or something.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-14 : 15:33:20

There's more to the error, please post it.

And the max row size is 8060. Show us the DDL (CREATE TABLE) of the table that is failing.

Tara

akz
Starting Member

6 Posts

Posted - 2004-09-14 : 15:38:08

Tara, it is a simple table, with 6 columns, all of type varchar (50).
I used Enterprise manager to create the table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-14 : 15:41:53

Please post the exact error. We need the entire thing not just the error code information.

Tara

akz
Starting Member

6 Posts

Posted - 2004-09-14 : 15:44:05

I don't have access to the db right now but I will post it tomorrow. Do you check this forum regularly?

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-14 : 15:44:33

quote:
Originally posted by tduggan

There's more to the error, please post it.

And the max row size is 8060. Show us the DDL (CREATE TABLE) of the table that is failing.

Tara

You vill show us!

Vhere is de error message!

Show us NOW!

I didn't know duggan was Geramn...I thought it was Irish...

lol

Not a bad way to get ...8000

akz...sorry to digress....

1. There are NO miracles

2. There's a lot more going on then you know...

We need the DDL, and the statement you execute that gives you that message

We can then recreate it...NOW!

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-14 : 15:45:52

quote:
Originally posted by akz

Do you check this forum regularly?

Now I'm on the floor.....

akz...do you know how to subscribe the thread? Look at the options around the window...

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-14 : 22:12:19

Do a dbcc checktable on it.
Look at syscolumns - especially the column offsets.
How are you doing the inserts.
are there any triggers, computed columns or anything odd about the table?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-15 : 09:15:43

dumb question, but -- how much free space is available on the harddrive that the database files reside on?

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-15 : 09:17:49

By the way -- I googled and found this on that particular error # in SQL Server:

http://support.microsoft.com/default.aspx?kbid=828337

Not sure if it applies/helps, but thought I'd send it along.

- Jeff

akz
Starting Member

6 Posts

Posted - 2004-09-15 : 09:36:19

I get the following assertion error:

m_sizeRec >0 && m_sizeRec <=MAXDATAROW
Server msg 3624, level 20, state 1, line 1
Spid: 52 process io: 756

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources