the Size of DB became very big , any idea to cut it to smaller

Question

the Size of DB became very big , any idea to cut it to smaller

wagdy_maher

SSC Rookie

Points: 37
More actions
March 20, 2015 at 9:19 pm

#315859

I need help if any idea to keep data of last 6 month only and the oldest keep it as backup or any
any idea please ?
Thanks,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

wagdy_maher (3/20/2015)
I need help if any idea to keep data of last 6 month only and the oldest keep it as backup or any
any idea please ?
Thanks,

Quick thought, there is no simple answer apart from "it depends", there are quite few things to consider such as Activity, Maintenance Windows,HA, Physical Size, Data Sizes, Hardware Capacity etc.

😎

The simplest approach would be on the line of full-backup and then delete the unwanted data.

wagdy_maher SSC Rookie Points: 37 More actions · Answer 2

its DB for online system ,I can take it down for one hour in the week end

it's normal single server no HA till now

Data size 300 GB

storage capcity : over san storage , no problem about storage

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 3

wagdy_maher (3/21/2015)
its DB for online system ,I can take it down for one hour in the week end
it's normal single server no HA till now
Data size 300 GB
storage capcity : over san storage , no problem about storage

Is that for a single table? If so, how many months are in that 300GB now?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

wagdy_maher SSC Rookie Points: 37 More actions · Answer 4

storage capcity : over san storage , no problem about storage

Is that for a single table? If so, how many months are in that 300GB now?

It's single database

around 14 months

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 5

wagdy_maher (3/22/2015)
storage capcity : over san storage , no problem about storage
Is that for a single table? If so, how many months are in that 300GB now?
It's single database
around 14 months

Doesn't look like a complex problem, simply copy the database but only transfer the rows you want to keep to the copy and then switch.

😎

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 6

Jeff Moden (3/21/2015)
wagdy_maher (3/21/2015)
its DB for online system ,I can take it down for one hour in the week end
it's normal single server no HA till now
Data size 300 GB
storage capcity : over san storage , no problem about storage
Is that for a single table? If so, how many months are in that 300GB now?

Understood but I'm trying to get to the root of several problems that I'm fairly sure that are starting for you. What size are the largest 3 tables (including any blobs and indexes) and what is their purpose? Are they audit tables, tables like an InvoiceDetail table might be, or ???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)