Large Addition to Database

#1

I just made a major addition to the data in our database (SQL 7 with an
Access 2000 front end). The users had been working with a set of 66,000 Word
documents, having to open each to view when needed. I converted these docs
to RTF format, and brought the RTF codes into the database itself. Each
former Word doc is now stored in a single Text field in the record; and
there is an additional Text field which is the text of the document in plain
text format. (The average document is about 1,000 characters, and the
average document with RTF codes is about 1,000-1,500 characters.)

As a result, my database went from being about 600 MB in size to a little
over a gig. Still not huge; but significant increase from it's original
size.

Since there's a big jump in the size, I was wondering if there's anything I
need to do maintenance-wise to help the database use the new data.

Thanks,

Neil

#2

"Neil" <nospam (AT) nospam (DOT) net> wrote

Quote:

I just made a major addition to the data in our database (SQL 7 with an
Access 2000 front end). The users had been working with a set of 66,000
Word documents, having to open each to view when needed. I converted these
docs to RTF format, and brought the RTF codes into the database itself.
Each former Word doc is now stored in a single Text field in the record;
and there is an additional Text field which is the text of the document in
plain text format. (The average document is about 1,000 characters, and the
average document with RTF codes is about 1,000-1,500 characters.)

As a result, my database went from being about 600 MB in size to a little
over a gig. Still not huge; but significant increase from it's original
size.

Since there's a big jump in the size, I was wondering if there's anything
I need to do maintenance-wise to help the database use the new data.

That's still very small as far as SQL databases go.

Not sure how access documents, but a decent index on that (some sort of ID,
unique name or something) will make things work well most likely.

(BTW, you may want to look into upgrading the DB to use something like SQL
Express edition. More modern, better supported and may be a bit faster,
especially on a Windows 2003 box.)

Quote:

Thanks,

Neil

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

#3

Quote:

Since there's a big jump in the size, I was wondering if there's anything
I need to do maintenance-wise to help the database use the new data.

That's still very small as far as SQL databases go.

Right. I just meant that it was almost double the original size. Wasn't sure
if there was any kind of maintenance that should be done.

Quote:

Not sure how access documents, but a decent index on that (some sort of
ID, unique name or something) will make things work well most likely.

Yes, each record has a unique ID for the record. These two fields are just
two of many fields in the record.

Quote:

(BTW, you may want to look into upgrading the DB to use something like SQL
Express edition. More modern, better supported and may be a bit faster,
especially on a Windows 2003 box.)

SQL Express? I thought SQL Express was the new version of SQL Desktop/MSDE.
Maybe I'm wrong.

We're using the full version of SQL 7, and are upgrading to SQL 2005.

Thanks,

Neil

Quote:

Thanks,

Neil

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com
http://www.greenms.com/sqlserver.html

#4

"Neil" <nospam (AT) nospam (DOT) net> wrote

Quote:

Since there's a big jump in the size, I was wondering if there's
anything
I need to do maintenance-wise to help the database use the new data.

That's still very small as far as SQL databases go.

Right. I just meant that it was almost double the original size. Wasn't
sure if there was any kind of maintenance that should be done.

Nothing more than the usual. Backups, do a DBCC CheckDB on a regular basis.
Perhaps update stats from time to time, monitor your indices.

Quote:

Not sure how access documents, but a decent index on that (some sort of
ID, unique name or something) will make things work well most likely.

Yes, each record has a unique ID for the record. These two fields are just
two of many fields in the record.

(BTW, you may want to look into upgrading the DB to use something like
SQL Express edition. More modern, better supported and may be a bit
faster, especially on a Windows 2003 box.)

SQL Express? I thought SQL Express was the new version of SQL
Desktop/MSDE. Maybe I'm wrong.

It is. For many smaller applications, it's a great choice. For a 1 gig
database, I'd certainly consider it.

Quote:

We're using the full version of SQL 7, and are upgrading to SQL 2005.

Didn't want to suggest that if it wasn't in the budget.

Quote:

Thanks,

Neil

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

#5

Quote:

(BTW, you may want to look into upgrading the DB to use something like
SQL Express edition. More modern, better supported and may be a bit
faster, especially on a Windows 2003 box.)

SQL Express? I thought SQL Express was the new version of SQL
Desktop/MSDE. Maybe I'm wrong.

It is. For many smaller applications, it's a great choice. For a 1 gig
database, I'd certainly consider it.

Isn't there a limitation on the number of users that can access the
database? I thought that it was something like 10 users or something.

#6

"Neil" <nospam (AT) nospam (DOT) net> wrote

Quote:

(BTW, you may want to look into upgrading the DB to use something like
SQL Express edition. More modern, better supported and may be a bit
faster, especially on a Windows 2003 box.)

SQL Express? I thought SQL Express was the new version of SQL
Desktop/MSDE. Maybe I'm wrong.

It is. For many smaller applications, it's a great choice. For a 1 gig
database, I'd certainly consider it.

Isn't there a limitation on the number of users that can access the
database? I thought that it was something like 10 users or something.

No, and there never has been. That's a misunderstanding of the old query
governer built into MSDE (whcih permitted only 5 or 6 (I've seen both
numbers)) of queries to run at the same time before things were slowed down
on purpose).

SQL Server Express 2005 does not have that limit. Rather it is limited in
DB size (2GB), CPU (1) and memory (I believe 1GB). But within those
parameters it operates at full-speed and works very well.

Quote:

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

#7

Greg D. Moore (Strider) (mooregr_deleteth1s (AT) greenms (DOT) com) writes:

Quote:

No, and there never has been. That's a misunderstanding of the old
query governer built into MSDE (whcih permitted only 5 or 6 (I've seen
both numbers)) of queries to run at the same time before things were
slowed down on purpose).

SQL Server Express 2005 does not have that limit. Rather it is limited in
DB size (2GB), CPU (1) and memory (I believe 1GB). But within those
parameters it operates at full-speed and works very well.

But keep in mind that SQL Express does not come with SQL Agent, which can
be a killer for Neil if he wants to schedule backsup and that.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#8

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Quote:

Greg D. Moore (Strider) (mooregr_deleteth1s (AT) greenms (DOT) com) writes:
No, and there never has been. That's a misunderstanding of the old
query governer built into MSDE (whcih permitted only 5 or 6 (I've seen
both numbers)) of queries to run at the same time before things were
slowed down on purpose).

SQL Server Express 2005 does not have that limit. Rather it is limited
in
DB size (2GB), CPU (1) and memory (I believe 1GB). But within those
parameters it operates at full-speed and works very well.

But keep in mind that SQL Express does not come with SQL Agent, which can
be a killer for Neil if he wants to schedule backsup and that.

Good point.

Quote:

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

#9

Thanks for the info. That's good to know.

"Greg D. Moore (Strider)" <mooregr_deleteth1s (AT) greenms (DOT) com> wrote

Quote:

"Neil" <nospam (AT) nospam (DOT) net> wrote in message
news:G0hTi.54$nN3.12 (AT) newssvr17 (DOT) news.prodigy.net...
(BTW, you may want to look into upgrading the DB to use something like
SQL Express edition. More modern, better supported and may be a bit
faster, especially on a Windows 2003 box.)

SQL Express? I thought SQL Express was the new version of SQL
Desktop/MSDE. Maybe I'm wrong.

It is. For many smaller applications, it's a great choice. For a 1 gig
database, I'd certainly consider it.

Isn't there a limitation on the number of users that can access the
database? I thought that it was something like 10 users or something.

No, and there never has been. That's a misunderstanding of the old query
governer built into MSDE (whcih permitted only 5 or 6 (I've seen both
numbers)) of queries to run at the same time before things were slowed
down on purpose).

SQL Server Express 2005 does not have that limit. Rather it is limited in
DB size (2GB), CPU (1) and memory (I believe 1GB). But within those
parameters it operates at full-speed and works very well.

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com
http://www.greenms.com/sqlserver.html

#10

Thanks for adding that info.

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Quote:

Greg D. Moore (Strider) (mooregr_deleteth1s (AT) greenms (DOT) com) writes:
No, and there never has been. That's a misunderstanding of the old
query governer built into MSDE (whcih permitted only 5 or 6 (I've seen
both numbers)) of queries to run at the same time before things were
slowed down on purpose).

SQL Server Express 2005 does not have that limit. Rather it is limited
in
DB size (2GB), CPU (1) and memory (I believe 1GB). But within those
parameters it operates at full-speed and works very well.

But keep in mind that SQL Express does not come with SQL Agent, which can
be a killer for Neil if he wants to schedule backsup and that.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Large Addition to Database

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Large Addition to Database - 10-21-2007 , 08:25 PM

Re: Large Addition to Database - 10-21-2007 , 10:30 PM

Re: Large Addition to Database - 10-22-2007 , 01:58 AM

Re: Large Addition to Database - 10-22-2007 , 06:16 AM

Re: Large Addition to Database - 10-23-2007 , 12:57 AM

Re: Large Addition to Database - 10-23-2007 , 06:36 AM

Re: Large Addition to Database - 10-23-2007 , 04:41 PM

Re: Large Addition to Database - 10-24-2007 , 06:33 AM

Re: Large Addition to Database - 10-24-2007 , 11:45 PM

Re: Large Addition to Database - 10-24-2007 , 11:49 PM