Large Table Maintenance with BLOB data

I am quickly running out of room on my sqlserver2000
database. I have a 300gb or so table on a 500gb server
and am considering how to keep the table within my space
constraints.
My requirements/issues are:
1. win2k server running sqlserver2000
2. each row contains the IMAGE data type
3. The database resides on a SCSI RAID disk drive array.
4. I don't have enough room on anything to back up the
database or make a copy.
5. Uptime is of the essence as this db is used heavily
during business hours.
Can someone provide recommendations on what would be the
best way to keep this database manageable?
I want to drop any rows older than a certain date to get
rid of old images first.
Thanks,
TobinYou got big troubles.
Your first problem is to make management aware of the serious availability
problems you are facing. If this is a business critical system, they MUST
free up some resources ($$$) to handle backups and maintenance tasks OR deal
with the outage times. Document the backup and recovery strategy, warts and
all, and present it to your boss. Same for maintenance issues. Start
reporting database space ona weekly basis with projections of when it gets
90% full and is effectively used up. Be sure and note that certain
maintenance tasks may no longer be possible (DBCC DBREINDEX) given the tight
space constraints. You don't have to fix all the problems now, but you do
have to make your company's management aware of them so you don't get
blasted when this system blows up. Be sure and have a plan ready to fix the
shortcomings in case management gets scared and demands something be done
NOW.
As for the short term problem, I take older data and DTS it out to another
database. I use views to link it back to the original database. Once the
data is out, you can backup the new DB once and then lock it read-only.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Tobin" <tobin@.computac.com> wrote in message
news:f76001c3f181$3f324480$a601280a@.phx.gbl...
> I am quickly running out of room on my sqlserver2000
> database. I have a 300gb or so table on a 500gb server
> and am considering how to keep the table within my space
> constraints.
> My requirements/issues are:
> 1. win2k server running sqlserver2000
> 2. each row contains the IMAGE data type
> 3. The database resides on a SCSI RAID disk drive array.
> 4. I don't have enough room on anything to back up the
> database or make a copy.
> 5. Uptime is of the essence as this db is used heavily
> during business hours.
> Can someone provide recommendations on what would be the
> best way to keep this database manageable?
> I want to drop any rows older than a certain date to get
> rid of old images first.
> Thanks,
> Tobin|||Thanks for the advice. A question on something you said:
"Once the data is out, you can backup the new DB once and
then lock it read-only."
At the current time I can't backup the database. I am a
beginner when it comes to sqlserver. I understand that if
I just drop the old data the pages the data is stored on
might not be contiguous and I'll need to reindex the db.
Is my only solution to dts the data out or delete the rows
and then reindex to keep the database a reasonable size?
>--Original Message--
>You got big troubles.
>Your first problem is to make management aware of the
serious availability
>problems you are facing. If this is a business critical
system, they MUST
>free up some resources ($$$) to handle backups and
maintenance tasks OR deal
>with the outage times. Document the backup and recovery
strategy, warts and
>all, and present it to your boss. Same for maintenance
issues. Start
>reporting database space ona weekly basis with
projections of when it gets
>90% full and is effectively used up. Be sure and note
that certain
>maintenance tasks may no longer be possible (DBCC
DBREINDEX) given the tight
>space constraints. You don't have to fix all the
problems now, but you do
>have to make your company's management aware of them so
you don't get
>blasted when this system blows up. Be sure and have a
plan ready to fix the
>shortcomings in case management gets scared and demands
something be done
>NOW.
>As for the short term problem, I take older data and DTS
it out to another
>database. I use views to link it back to the original
database. Once the
>data is out, you can backup the new DB once and then lock
it read-only.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Tobin" <tobin@.computac.com> wrote in message
>news:f76001c3f181$3f324480$a601280a@.phx.gbl...
>> I am quickly running out of room on my sqlserver2000
>> database. I have a 300gb or so table on a 500gb server
>> and am considering how to keep the table within my space
>> constraints.
>> My requirements/issues are:
>> 1. win2k server running sqlserver2000
>> 2. each row contains the IMAGE data type
>> 3. The database resides on a SCSI RAID disk drive
array.
>> 4. I don't have enough room on anything to back up the
>> database or make a copy.
>> 5. Uptime is of the essence as this db is used heavily
>> during business hours.
>> Can someone provide recommendations on what would be the
>> best way to keep this database manageable?
>> I want to drop any rows older than a certain date to get
>> rid of old images first.
>> Thanks,
>> Tobin
>
>.
>|||Reindexing and space are only part of your problem. Not having a backup is
a huge issue. Not having space for basic maintenance is just as bad.
Seriously, get your management involved and aware of the problems. If you
don't know much about SQL, get some books and some training. If they won't
listen, get your resume updated. You will need it when the system blows up.
When you DTS the data, where will you put it. My recommendation is to DTS
to another database and link the information back using a view. After it is
all done, you can set the DTS target database read-only. Use multiple
target databases (perhaps one per Month or Quarter) and you may get this
down to a more managable size.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:fa0a01c3f1a4$2ebf37c0$a501280a@.phx.gbl...
> Thanks for the advice. A question on something you said:
> "Once the data is out, you can backup the new DB once and
> then lock it read-only."
> At the current time I can't backup the database. I am a
> beginner when it comes to sqlserver. I understand that if
> I just drop the old data the pages the data is stored on
> might not be contiguous and I'll need to reindex the db.
> Is my only solution to dts the data out or delete the rows
> and then reindex to keep the database a reasonable size?
> >--Original Message--
> >You got big troubles.
> >
> >Your first problem is to make management aware of the
> serious availability
> >problems you are facing. If this is a business critical
> system, they MUST
> >free up some resources ($$$) to handle backups and
> maintenance tasks OR deal
> >with the outage times. Document the backup and recovery
> strategy, warts and
> >all, and present it to your boss. Same for maintenance
> issues. Start
> >reporting database space ona weekly basis with
> projections of when it gets
> >90% full and is effectively used up. Be sure and note
> that certain
> >maintenance tasks may no longer be possible (DBCC
> DBREINDEX) given the tight
> >space constraints. You don't have to fix all the
> problems now, but you do
> >have to make your company's management aware of them so
> you don't get
> >blasted when this system blows up. Be sure and have a
> plan ready to fix the
> >shortcomings in case management gets scared and demands
> something be done
> >NOW.
> >
> >As for the short term problem, I take older data and DTS
> it out to another
> >database. I use views to link it back to the original
> database. Once the
> >data is out, you can backup the new DB once and then lock
> it read-only.
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >I support the Professional Association for SQL Server
> >www.sqlpass.org
> >
> >"Tobin" <tobin@.computac.com> wrote in message
> >news:f76001c3f181$3f324480$a601280a@.phx.gbl...
> >> I am quickly running out of room on my sqlserver2000
> >> database. I have a 300gb or so table on a 500gb server
> >> and am considering how to keep the table within my space
> >> constraints.
> >>
> >> My requirements/issues are:
> >>
> >> 1. win2k server running sqlserver2000
> >> 2. each row contains the IMAGE data type
> >> 3. The database resides on a SCSI RAID disk drive
> array.
> >> 4. I don't have enough room on anything to back up the
> >> database or make a copy.
> >> 5. Uptime is of the essence as this db is used heavily
> >> during business hours.
> >>
> >> Can someone provide recommendations on what would be the
> >> best way to keep this database manageable?
> >>
> >> I want to drop any rows older than a certain date to get
> >> rid of old images first.
> >>
> >> Thanks,
> >>
> >> Tobin
> >
> >
> >.
> >