Maximum size of table?

SQL20 Ent SP3 WIN2K Adv SP3
I'm hoping for some guidance here.
I've a single table with 184,000,000 rows, I think this is probably
a good candidate for some kind of partitioning.
My question is, how many rows would constitute a decent sized
table, before partitioning?
I know it's a very subjective question and depends on row size
database size and others, but any opinions would be most welcome.It's more on what you do with the data and how you access it. Explain a
little of how you access this table and we can give a better answer.
--
Andrew J. Kelly
SQL Server MVP
"Stressed" <k@.c.co.uk> wrote in message
news:uIKG5g7SDHA.2248@.TK2MSFTNGP11.phx.gbl...
> SQL20 Ent SP3 WIN2K Adv SP3
> I'm hoping for some guidance here.
> I've a single table with 184,000,000 rows, I think this is probably
> a good candidate for some kind of partitioning.
> My question is, how many rows would constitute a decent sized
> table, before partitioning?
> I know it's a very subjective question and depends on row size
> database size and others, but any opinions would be most welcome.
>|||Sorry,
In addition to the previous, Data Junction is used to load the data, query
analyzer is used by our analysts to query the data.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ehcR3u7SDHA.2148@.TK2MSFTNGP11.phx.gbl...
> It's more on what you do with the data and how you access it. Explain a
> little of how you access this table and we can give a better answer.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Stressed" <k@.c.co.uk> wrote in message
> news:uIKG5g7SDHA.2248@.TK2MSFTNGP11.phx.gbl...
> > SQL20 Ent SP3 WIN2K Adv SP3
> >
> > I'm hoping for some guidance here.
> >
> > I've a single table with 184,000,000 rows, I think this is probably
> > a good candidate for some kind of partitioning.
> >
> > My question is, how many rows would constitute a decent sized
> > table, before partitioning?
> >
> > I know it's a very subjective question and depends on row size
> > database size and others, but any opinions would be most welcome.
> >
> >
>|||When you do these updates right after you import the data does it involve
any of the previous rows or just the new ones? How about the subsets, are
they created only from the new data? Sounds like you work mainly with
blocks of data, maybe by date. If that's true then you may consider
partitioning the data by date (weeks, Month, quarter etc) so it's easier to
work with only the relevant data. If you do keep it in a single table then
make sure you have a clustered index on the column(s) that will allow you to
differentiate the current data. Otherwise you may be scanning the entire
table over and over for these updates and queries.
--
Andrew J. Kelly
SQL Server MVP
"Stressed" <k@.c.co.uk> wrote in message
news:uaxCE27SDHA.1556@.TK2MSFTNGP10.phx.gbl...
> Thanks for taking the time to reply.
> Table is used in a warehousing type environment. Initially a bulk load,
> then incremental loads, approx once a month. Once the load has taken
> place a series of updates are performed, following that, the table is
> used to create subsets of data in another database, for shipping to our
> client sites for review.
> In short, much loading, some updating, much querying.
> I hope this gives a reasonable insight.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ehcR3u7SDHA.2148@.TK2MSFTNGP11.phx.gbl...
> > It's more on what you do with the data and how you access it. Explain a
> > little of how you access this table and we can give a better answer.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Stressed" <k@.c.co.uk> wrote in message
> > news:uIKG5g7SDHA.2248@.TK2MSFTNGP11.phx.gbl...
> > > SQL20 Ent SP3 WIN2K Adv SP3
> > >
> > > I'm hoping for some guidance here.
> > >
> > > I've a single table with 184,000,000 rows, I think this is probably
> > > a good candidate for some kind of partitioning.
> > >
> > > My question is, how many rows would constitute a decent sized
> > > table, before partitioning?
> > >
> > > I know it's a very subjective question and depends on row size
> > > database size and others, but any opinions would be most welcome.
> > >
> > >
> >
> >
>

Maximum row size for SQLExpress

I was just wondering if SQLExpress will have the same maximum row size
funcationality as SQL Server 2005. (being able to have a single row go
accross multiple pages and allow the entire row to exceed 8060 bytes)?
Thanks,
John Scott.
Hi
8060 applies. If you use the new datatypes NVARCHAR(max) and VARCHAR(max),
those data types get sliced up the SQL Server itself across multiple pages.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John Scott" <johnscott@.despammed.com> wrote in message
news:08F62DEB-4D56-4E0F-8560-403B46F513B0@.microsoft.com...
>I was just wondering if SQLExpress will have the same maximum row size
> funcationality as SQL Server 2005. (being able to have a single row go
> accross multiple pages and allow the entire row to exceed 8060 bytes)?
>
> Thanks,
> John Scott.
|||Yes, Express has the extended row size functionality introduced in SQL
Server 2005.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"John Scott" <johnscott@.despammed.com> wrote in message
news:08F62DEB-4D56-4E0F-8560-403B46F513B0@.microsoft.com...
>I was just wondering if SQLExpress will have the same maximum row size
> funcationality as SQL Server 2005. (being able to have a single row go
> accross multiple pages and allow the entire row to exceed 8060 bytes)?
>
> Thanks,
> John Scott.

Maximum number of databases

Hello everyone -
Running SQL Server 2000
What is the maximum number of databases that can be had in a single MS SQL installation??
thanks
tonyFrom the article titled "Maximum Capacity Specifications" in BOL: 32,767|||If you get close to that you are probably doing something wrong...

Looking at your next thread.... ;)|||I see you are following my thought processes here...

company is approaching 500 databases in a single sql server

not sure on the direction to take or how soon before the ship takes on too much water

thanks for the replies!!

take care
tony

maximum number of database users in sql2k

Hi All,
I was trying to find some documentation from microsoft which specifies the
maximum number of users that can be created in a single sql2k database.
We seem to be hitting the limit at 16379.
The error raised is:
<<
Server: Msg 15065, Level 16, State 1, Procedure sp_grantdbaccess, Line 160
All user IDs have been assigned.
>>
The following section of code from sp_grantdbaccess suggests that 16379 is
the limit:
/////////////////////////////////////////////
-- OBTAIN NEW UID (RESERVE 1-4) --
if user_name(5) IS NULL
select @.uid = 5
else
select @.uid = min(uid)+1 from sysusers
where uid >= 5 and uid < (16384 - 1) -- stay in users range
and user_name(uid+1) is null -- uid not in use
if @.uid is null
begin
raiserror(15065,-1,-1)
return (1)
end
/////////////////////////////////////////////
Can anyone confirm this?
Is there any Microsoft Documentation which states that 16379 is the maximum
number of users in sql2k.
(Could not find it in the sql books online)
Thanks
Vikram
--
Vikram Vamshi
Eclipsys CorporationThe maximum number of connection is 32767.
Run sp_configure 'user connection' on your server if it is
set differently on your server.
Hope to help.
>--Original Message--
>Hi All,
>I was trying to find some documentation from microsoft
which specifies the
>maximum number of users that can be created in a single
sql2k database.
>We seem to be hitting the limit at 16379.
>The error raised is:
><<
>Server: Msg 15065, Level 16, State 1, Procedure
sp_grantdbaccess, Line 160
>All user IDs have been assigned.
>The following section of code from sp_grantdbaccess
suggests that 16379 is
>the limit:
>/////////////////////////////////////////////
> -- OBTAIN NEW UID (RESERVE 1-4) --
> if user_name(5) IS NULL
> select @.uid = 5
> else
> select @.uid = min(uid)+1 from sysusers
> where uid >= 5 and uid < (16384 - 1) --
stay in users range
> and user_name(uid+1) is null --
uid not in use
> if @.uid is null
> begin
> raiserror(15065,-1,-1)
> return (1)
> end
>/////////////////////////////////////////////
>Can anyone confirm this?
>Is there any Microsoft Documentation which states that
16379 is the maximum
>number of users in sql2k.
>(Could not find it in the sql books online)
>Thanks
>Vikram
>--
>Vikram Vamshi
>Eclipsys Corporation
>
>.
>|||obaid,
I did get the infomration regarding the user 'connections' from books
online.
Here I am trying to get the information regarding the number of 'database
users' that can be created in a database.
Vikram

maximum databases no.

hi,
I would like to hear from you guys how many databases (maximum number)
did you reach on a single server.
We are trying to work with a few hundreds of databases at the same
time, and we get weird errors or problems, even with low load on these
databases.
I would like to hear from your experience and maybe get leads to
material on the subject.
I'm aware of the official limits (http://technet.microsoft.com/en-us/
library/ms143432(SQL.90).aspx) but in reality the numbers are much
lower.
I don't have any problems with over 500 databases on two different
production systems.
Of course, I took painstaking efforts to make sure the systems were designed
to really treat the databases as independently as possible.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
<yaniv.harpaz@.gmail.com> wrote in message
news:1181232580.249349.26550@.q69g2000hsb.googlegro ups.com...
> hi,
> I would like to hear from you guys how many databases (maximum number)
> did you reach on a single server.
> We are trying to work with a few hundreds of databases at the same
> time, and we get weird errors or problems, even with low load on these
> databases.
> I would like to hear from your experience and maybe get leads to
> material on the subject.
> I'm aware of the official limits (http://technet.microsoft.com/en-us/
> library/ms143432(SQL.90).aspx) but in reality the numbers are much
> lower.
>
|||I have a client with 6400+ databases on a single relatively low-end cluster
(2 dual core AMD 64 bit CPUs, 8GB ram, VERY low-end drive system). These
same databases are log-shipped (custom-code, not built in) to an even
wimpier stand alone server for reporting purposes. Issues include:
1) Most automated tools break down. These include automated
monitoring/backup apps as well as tools like DBArtisan, which simply barf at
the extreme amount of system information they try to chew through.
2) Log-shipping/mirroring/replication become essentially non-useable too in
my experience.
3) Automation of things like rollouts, maintanence, etc are a must to keep
your sanity. I had a developer build what I call a "database shotgun" that
uses between 10 and 20 threads to concurrently run scripts against multiple
databases at a time. Stuff that takes ms_foreachdb tens of minutes to
complete now complete in a few minutes.
4) msdb will get huge. plan for this by expanding the data and log file
sizes ahead of time to avoid fragmentation. Set up jobs to flush old
history records for job runs/backups as appropriate.
Not sure about the 'wierd errors or problems' you are receiving. Any
examples?
TheSQLGuru
President
Indicium Resources, Inc.
<yaniv.harpaz@.gmail.com> wrote in message
news:1181232580.249349.26550@.q69g2000hsb.googlegro ups.com...
> hi,
> I would like to hear from you guys how many databases (maximum number)
> did you reach on a single server.
> We are trying to work with a few hundreds of databases at the same
> time, and we get weird errors or problems, even with low load on these
> databases.
> I would like to hear from your experience and maybe get leads to
> material on the subject.
> I'm aware of the official limits (http://technet.microsoft.com/en-us/
> library/ms143432(SQL.90).aspx) but in reality the numbers are much
> lower.
>
|||with 64-bit 8GB memory I got connection timeouts after going over 200
connections
I got very efficient queries starting to take 10 minutes instead of 2
seconds (without having high load on the DB server)
|||yaniv.harpaz@.gmail.com wrote:
> with 64-bit 8GB memory I got connection timeouts after going over 200
> connections
> I got very efficient queries starting to take 10 minutes instead of 2
> seconds (without having high load on the DB server)
I have to second yaniv's experience with sql2005 - with time it becomes
much more brittle than Sql2000.

maximum databases no.

hi,
I would like to hear from you guys how many databases (maximum number)
did you reach on a single server.
We are trying to work with a few hundreds of databases at the same
time, and we get weird errors or problems, even with low load on these
databases.
I would like to hear from your experience and maybe get leads to
material on the subject.
I'm aware of the official limits (http://technet.microsoft.com/en-us/
library/ms143432(SQL.90).aspx) but in reality the numbers are much
lower.I don't have any problems with over 500 databases on two different
production systems.
Of course, I took painstaking efforts to make sure the systems were designed
to really treat the databases as independently as possible.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
<yaniv.harpaz@.gmail.com> wrote in message
news:1181232580.249349.26550@.q69g2000hsb.googlegroups.com...
> hi,
> I would like to hear from you guys how many databases (maximum number)
> did you reach on a single server.
> We are trying to work with a few hundreds of databases at the same
> time, and we get weird errors or problems, even with low load on these
> databases.
> I would like to hear from your experience and maybe get leads to
> material on the subject.
> I'm aware of the official limits (http://technet.microsoft.com/en-us/
> library/ms143432(SQL.90).aspx) but in reality the numbers are much
> lower.
>|||I have a client with 6400+ databases on a single relatively low-end cluster
(2 dual core AMD 64 bit CPUs, 8GB ram, VERY low-end drive system). These
same databases are log-shipped (custom-code, not built in) to an even
wimpier stand alone server for reporting purposes. Issues include:
1) Most automated tools break down. These include automated
monitoring/backup apps as well as tools like DBArtisan, which simply barf at
the extreme amount of system information they try to chew through.
2) Log-shipping/mirroring/replication become essentially non-useable too in
my experience.
3) Automation of things like rollouts, maintanence, etc are a must to keep
your sanity. I had a developer build what I call a "database shotgun" that
uses between 10 and 20 threads to concurrently run scripts against multiple
databases at a time. Stuff that takes ms_foreachdb tens of minutes to
complete now complete in a few minutes.
4) msdb will get huge. plan for this by expanding the data and log file
sizes ahead of time to avoid fragmentation. Set up jobs to flush old
history records for job runs/backups as appropriate.
Not sure about the 'wierd errors or problems' you are receiving. Any
examples?
TheSQLGuru
President
Indicium Resources, Inc.
<yaniv.harpaz@.gmail.com> wrote in message
news:1181232580.249349.26550@.q69g2000hsb.googlegroups.com...
> hi,
> I would like to hear from you guys how many databases (maximum number)
> did you reach on a single server.
> We are trying to work with a few hundreds of databases at the same
> time, and we get weird errors or problems, even with low load on these
> databases.
> I would like to hear from your experience and maybe get leads to
> material on the subject.
> I'm aware of the official limits (http://technet.microsoft.com/en-us/
> library/ms143432(SQL.90).aspx) but in reality the numbers are much
> lower.
>|||with 64-bit 8GB memory I got connection timeouts after going over 200
connections
I got very efficient queries starting to take 10 minutes instead of 2
seconds (without having high load on the DB server)|||yaniv.harpaz@.gmail.com wrote:
> with 64-bit 8GB memory I got connection timeouts after going over 200
> connections
> I got very efficient queries starting to take 10 minutes instead of 2
> seconds (without having high load on the DB server)
I have to second yaniv's experience with sql2005 - with time it becomes
much more brittle than Sql2000.

maximum databases no.

hi,
I would like to hear from you guys how many databases (maximum number)
did you reach on a single server.
We are trying to work with a few hundreds of databases at the same
time, and we get weird errors or problems, even with low load on these
databases.
I would like to hear from your experience and maybe get leads to
material on the subject.
I'm aware of the official limits (http://technet.microsoft.com/en-us/
library/ms143432(SQL.90).aspx) but in reality the numbers are much
lower.I don't have any problems with over 500 databases on two different
production systems.
Of course, I took painstaking efforts to make sure the systems were designed
to really treat the databases as independently as possible.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
<yaniv.harpaz@.gmail.com> wrote in message
news:1181232580.249349.26550@.q69g2000hsb.googlegroups.com...
> hi,
> I would like to hear from you guys how many databases (maximum number)
> did you reach on a single server.
> We are trying to work with a few hundreds of databases at the same
> time, and we get weird errors or problems, even with low load on these
> databases.
> I would like to hear from your experience and maybe get leads to
> material on the subject.
> I'm aware of the official limits (http://technet.microsoft.com/en-us/
> library/ms143432(SQL.90).aspx) but in reality the numbers are much
> lower.
>|||I have a client with 6400+ databases on a single relatively low-end cluster
(2 dual core AMD 64 bit CPUs, 8GB ram, VERY low-end drive system). These
same databases are log-shipped (custom-code, not built in) to an even
wimpier stand alone server for reporting purposes. Issues include:
1) Most automated tools break down. These include automated
monitoring/backup apps as well as tools like DBArtisan, which simply barf at
the extreme amount of system information they try to chew through.
2) Log-shipping/mirroring/replication become essentially non-useable too in
my experience.
3) Automation of things like rollouts, maintanence, etc are a must to keep
your sanity. I had a developer build what I call a "database shotgun" that
uses between 10 and 20 threads to concurrently run scripts against multiple
databases at a time. Stuff that takes ms_foreachdb tens of minutes to
complete now complete in a few minutes.
4) msdb will get huge. plan for this by expanding the data and log file
sizes ahead of time to avoid fragmentation. Set up jobs to flush old
history records for job runs/backups as appropriate.
Not sure about the 'wierd errors or problems' you are receiving. Any
examples?
--
TheSQLGuru
President
Indicium Resources, Inc.
<yaniv.harpaz@.gmail.com> wrote in message
news:1181232580.249349.26550@.q69g2000hsb.googlegroups.com...
> hi,
> I would like to hear from you guys how many databases (maximum number)
> did you reach on a single server.
> We are trying to work with a few hundreds of databases at the same
> time, and we get weird errors or problems, even with low load on these
> databases.
> I would like to hear from your experience and maybe get leads to
> material on the subject.
> I'm aware of the official limits (http://technet.microsoft.com/en-us/
> library/ms143432(SQL.90).aspx) but in reality the numbers are much
> lower.
>|||with 64-bit 8GB memory I got connection timeouts after going over 200
connections
I got very efficient queries starting to take 10 minutes instead of 2
seconds (without having high load on the DB server)|||yaniv.harpaz@.gmail.com wrote:
> with 64-bit 8GB memory I got connection timeouts after going over 200
> connections
> I got very efficient queries starting to take 10 minutes instead of 2
> seconds (without having high load on the DB server)
I have to second yaniv's experience with sql2005 - with time it becomes
much more brittle than Sql2000.