Large Addition to Database

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

Re: Large Addition to Database

"Neil" wrote in message
news:z_SSi.59295$Um6.46164@newssvr12.news.prodigy.net...
>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.)

>
> Thanks,
>
> Neil
>



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

Re: Large Addition to Database

>> 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.

>
> 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.

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

Thanks,

Neil



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

Re: Large Addition to Database

"Neil" wrote in message
news:bTXSi.7102$Pv2.3736@newssvr23.news.prodigy.net...
>
>>> 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.

>
>>
>> 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.

>
> 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.

>
> Thanks,
>
> Neil

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

Re: Large Addition to Database

>>> (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.

Re: Large Addition to Database

"Neil" wrote in message
news:G0hTi.54$nN3.12@newssvr17.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

Re: Large Addition to Database

Greg D. Moore (Strider) (mooregr_deleteth1s@greenms.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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Large Addition to Database

"Erland Sommarskog" wrote in message
news:Xns99D2F206743A5Yazorman@127.0.0.1...
> Greg D. Moore (Strider) (mooregr_deleteth1s@greenms.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.



>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

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

Re: Large Addition to Database

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

"Greg D. Moore (Strider)" wrote in message
news:13hrn2sbia0lt74@corp.supernews.com...
> "Neil" wrote in message
> news:G0hTi.54$nN3.12@newssvr17.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
>
>

Re: Large Addition to Database

Thanks for adding that info.


"Erland Sommarskog" wrote in message
news:Xns99D2F206743A5Yazorman@127.0.0.1...
> Greg D. Moore (Strider) (mooregr_deleteth1s@greenms.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@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx