Hello,
My database is 1gig big and it has so far one table with 1 melion records.
Once I tried to create the primary key in this table but it was rejected by
MS SQL since it was not unique. That's fine with me but I noticed that my
database doubled in size to just over 2 gigs. Then I created clastered index
and the size of the file was around 3 gigs. When I deleted newly created
index the size of the file stayed the same almost reaching the limites that
I set up before. I would never expect that the my database would grow so
fast since the same data in the Access database is just 450 MB.
Why the data grows when the key or index fails to be created?
How to control the size of the Database?
Any help is greatly appreciated,
Les
> Why the data grows when the key or index fails to be created?
It is growing because sql is creating index to the point of failure. Of
course creation takes some disk space. But after failure it cleans the data
in failure but by default it does not shrink the database file. So the file
stays as big as it was on failure.
> How to control the size of the Database?
the best way to control it is to set a maximm size, but the you should set
an alert to monitor the size and notify you database size is near full.
Best way to see how much space your data is taking is to set a TaskPad view
in Enterprise Manager.
(Select Database, go to menu View -> TaskPad).
Danijel
"Tom" <tom@.killspam.com> wrote in message
news:UjSFd.48178$TN6.1797082@.news20.bellglobal.com ...
> Hello,
> My database is 1gig big and it has so far one table with 1 melion records.
> Once I tried to create the primary key in this table but it was rejected
> by MS SQL since it was not unique. That's fine with me but I noticed that
> my database doubled in size to just over 2 gigs. Then I created clastered
> index and the size of the file was around 3 gigs. When I deleted newly
> created index the size of the file stayed the same almost reaching the
> limites that I set up before. I would never expect that the my database
> would grow so fast since the same data in the Access database is just 450
> MB.
> Why the data grows when the key or index fails to be created?
> How to control the size of the Database?
> Any help is greatly appreciated,
> Les
>
Showing posts with label size. Show all posts
Showing posts with label size. Show all posts
Friday, March 30, 2012
Sunday, February 19, 2012
How to choose the hardware for SQL Server wit large data
Hi,
I have a large data base and data keeps adding daily. I have the current
number of rows and row size. How do i calculate the hardware power required
for such data? is there any method to calculate that? using which i may go
for server with 10 processcer with 1tb hard drive and some 5gb ram.
Below is my data detail.
Table Current Rows Daily addition Chars per Row
table 1 6,900,000 10000 30
table 2 4,000,000 5000 85
table 3 4,000,000 5000 35
table 4 2,000,000 2500 30
table 5 20,000 2500 65
table 6 4,000,000 5000 105
Please help me.
Regards,
Kiran7,000,000 is not a big table in database ...
I had one back in 2002 with more than 150,000,000 rows (in an automatic
partitionned view - ok) (Hardware : 2 processors, < 40GBytes).
By the way, you need to evaluate more points:
* peek number of simultaneous connections / peek value of 'count per minute'
* mean size of row data stored and returned
...
Sl.
"N Kiran Kumar" <N Kiran Kumar@.discussions.microsoft.com> a écrit dans le
message de news: 46BB099A-37CB-4D25-9728-1110CB9804CD@.microsoft.com...
> Hi,
> I have a large data base and data keeps adding daily. I have the
> current
> number of rows and row size. How do i calculate the hardware power
> required
> for such data? is there any method to calculate that? using which i may go
> for server with 10 processcer with 1tb hard drive and some 5gb ram.
> Below is my data detail.
> Table Current Rows Daily addition Chars per Row
> table 1 6,900,000 10000 30
> table 2 4,000,000 5000 85
> table 3 4,000,000 5000 35
> table 4 2,000,000 2500 30
> table 5 20,000 2500 65
> table 6 4,000,000 5000 105
> Please help me.
> Regards,
> Kiran
>
>|||Hi
Along with other posts!!
You should also talk to your hardware supplier! The hardware does depend on
what you are doing with your database and usually that tends to change over
time with new releases/requirements etc... Looking at the current hardware
and performance counters you may get some idea, whatever you come up with may
have to be compromised by budget.
As far as disc space goes you will probably need to intellegently place your
data, therefore look for more spindles (or the ability to add more) rather
than fewer discs with higher capacity.
This site and these articles may help
http://www.sql-server-performance.com/articles/per/hardware_planning_p1.aspx
John
"N Kiran Kumar" wrote:
> Hi,
> I have a large data base and data keeps adding daily. I have the current
> number of rows and row size. How do i calculate the hardware power required
> for such data? is there any method to calculate that? using which i may go
> for server with 10 processcer with 1tb hard drive and some 5gb ram.
> Below is my data detail.
> Table Current Rows Daily addition Chars per Row
> table 1 6,900,000 10000 30
> table 2 4,000,000 5000 85
> table 3 4,000,000 5000 35
> table 4 2,000,000 2500 30
> table 5 20,000 2500 65
> table 6 4,000,000 5000 105
> Please help me.
> Regards,
> Kiran
>
>
I have a large data base and data keeps adding daily. I have the current
number of rows and row size. How do i calculate the hardware power required
for such data? is there any method to calculate that? using which i may go
for server with 10 processcer with 1tb hard drive and some 5gb ram.
Below is my data detail.
Table Current Rows Daily addition Chars per Row
table 1 6,900,000 10000 30
table 2 4,000,000 5000 85
table 3 4,000,000 5000 35
table 4 2,000,000 2500 30
table 5 20,000 2500 65
table 6 4,000,000 5000 105
Please help me.
Regards,
Kiran7,000,000 is not a big table in database ...
I had one back in 2002 with more than 150,000,000 rows (in an automatic
partitionned view - ok) (Hardware : 2 processors, < 40GBytes).
By the way, you need to evaluate more points:
* peek number of simultaneous connections / peek value of 'count per minute'
* mean size of row data stored and returned
...
Sl.
"N Kiran Kumar" <N Kiran Kumar@.discussions.microsoft.com> a écrit dans le
message de news: 46BB099A-37CB-4D25-9728-1110CB9804CD@.microsoft.com...
> Hi,
> I have a large data base and data keeps adding daily. I have the
> current
> number of rows and row size. How do i calculate the hardware power
> required
> for such data? is there any method to calculate that? using which i may go
> for server with 10 processcer with 1tb hard drive and some 5gb ram.
> Below is my data detail.
> Table Current Rows Daily addition Chars per Row
> table 1 6,900,000 10000 30
> table 2 4,000,000 5000 85
> table 3 4,000,000 5000 35
> table 4 2,000,000 2500 30
> table 5 20,000 2500 65
> table 6 4,000,000 5000 105
> Please help me.
> Regards,
> Kiran
>
>|||Hi
Along with other posts!!
You should also talk to your hardware supplier! The hardware does depend on
what you are doing with your database and usually that tends to change over
time with new releases/requirements etc... Looking at the current hardware
and performance counters you may get some idea, whatever you come up with may
have to be compromised by budget.
As far as disc space goes you will probably need to intellegently place your
data, therefore look for more spindles (or the ability to add more) rather
than fewer discs with higher capacity.
This site and these articles may help
http://www.sql-server-performance.com/articles/per/hardware_planning_p1.aspx
John
"N Kiran Kumar" wrote:
> Hi,
> I have a large data base and data keeps adding daily. I have the current
> number of rows and row size. How do i calculate the hardware power required
> for such data? is there any method to calculate that? using which i may go
> for server with 10 processcer with 1tb hard drive and some 5gb ram.
> Below is my data detail.
> Table Current Rows Daily addition Chars per Row
> table 1 6,900,000 10000 30
> table 2 4,000,000 5000 85
> table 3 4,000,000 5000 35
> table 4 2,000,000 2500 30
> table 5 20,000 2500 65
> table 6 4,000,000 5000 105
> Please help me.
> Regards,
> Kiran
>
>
How to choose the hardware for SQL Server wit large data
Hi,
I have a large data base and data keeps adding daily. I have the current
number of rows and row size. How do i calculate the hardware power required
for such data? is there any method to calculate that? using which i may go
for server with 10 processcer with 1tb hard drive and some 5gb ram.
Below is my data detail.
Table Current Rows Daily addition Chars per Row
table 1 6,900,000 10000 30
table 2 4,000,000 5000 85
table 3 4,000,0005000 35
table 4 2,000,000 2500 30
table 5 20,000 2500 65
table 6 4,000,000 5000 105
Please help me.
Regards,
Kiran
Hi
Along with other posts!!
You should also talk to your hardware supplier! The hardware does depend on
what you are doing with your database and usually that tends to change over
time with new releases/requirements etc... Looking at the current hardware
and performance counters you may get some idea, whatever you come up with may
have to be compromised by budget.
As far as disc space goes you will probably need to intellegently place your
data, therefore look for more spindles (or the ability to add more) rather
than fewer discs with higher capacity.
This site and these articles may help
http://www.sql-server-performance.com/articles/per/hardware_planning_p1.aspx
John
"N Kiran Kumar" wrote:
> Hi,
> I have a large data base and data keeps adding daily. I have the current
> number of rows and row size. How do i calculate the hardware power required
> for such data? is there any method to calculate that? using which i may go
> for server with 10 processcer with 1tb hard drive and some 5gb ram.
> Below is my data detail.
> Table Current Rows Daily addition Chars per Row
> table 1 6,900,000 10000 30
> table 2 4,000,000 5000 85
> table 3 4,000,0005000 35
> table 4 2,000,000 2500 30
> table 5 20,000 2500 65
> table 6 4,000,000 5000 105
> Please help me.
> Regards,
> Kiran
>
>
I have a large data base and data keeps adding daily. I have the current
number of rows and row size. How do i calculate the hardware power required
for such data? is there any method to calculate that? using which i may go
for server with 10 processcer with 1tb hard drive and some 5gb ram.
Below is my data detail.
Table Current Rows Daily addition Chars per Row
table 1 6,900,000 10000 30
table 2 4,000,000 5000 85
table 3 4,000,0005000 35
table 4 2,000,000 2500 30
table 5 20,000 2500 65
table 6 4,000,000 5000 105
Please help me.
Regards,
Kiran
Hi
Along with other posts!!
You should also talk to your hardware supplier! The hardware does depend on
what you are doing with your database and usually that tends to change over
time with new releases/requirements etc... Looking at the current hardware
and performance counters you may get some idea, whatever you come up with may
have to be compromised by budget.
As far as disc space goes you will probably need to intellegently place your
data, therefore look for more spindles (or the ability to add more) rather
than fewer discs with higher capacity.
This site and these articles may help
http://www.sql-server-performance.com/articles/per/hardware_planning_p1.aspx
John
"N Kiran Kumar" wrote:
> Hi,
> I have a large data base and data keeps adding daily. I have the current
> number of rows and row size. How do i calculate the hardware power required
> for such data? is there any method to calculate that? using which i may go
> for server with 10 processcer with 1tb hard drive and some 5gb ram.
> Below is my data detail.
> Table Current Rows Daily addition Chars per Row
> table 1 6,900,000 10000 30
> table 2 4,000,000 5000 85
> table 3 4,000,0005000 35
> table 4 2,000,000 2500 30
> table 5 20,000 2500 65
> table 6 4,000,000 5000 105
> Please help me.
> Regards,
> Kiran
>
>
Subscribe to:
Posts (Atom)