| Author |
Topic |
|
raindear
Yak Posting Veteran
64 Posts |
 Posted - 2013-06-12 : 05:14:10
|
| HiSomething on our website is triggering an anonymous customer row in a customer.dbo table. We run a maintenance to remove these every few days.However they are growing rapidly. Almost 100,000 per day. We are currently up to 2.8 million active rows.I am fairly new to SQL but my questions are1.How many rows can we get up to before the table is full?2.Can the deleted rows be recycled and reused?Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-12 : 05:23:48
|
| 1. depends on your id column limit (ie maximum value range of data type like integer, big int etc)2. yep..that can be done but is a kind of an overkillWhy not try avoiding the issue in the first place? look for triggers, procedures etc and see where the spurios rows are getting inserted and fix it rather than worrying on table overcoming its limit!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
raindear
Yak Posting Veteran
64 Posts |
Posted - 2013-06-12 : 06:05:17
|
| Thats the thing. We have tried looking but we cannot find where the issues is coming from |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-12 : 06:08:08
|
| hmm...why not run a profiler trace to see the code causing this?From this you could understand whether its a trigger or procedure or adhoc query.You should fix it at the source rather than doing clean up and recycle at table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
raindear
Yak Posting Veteran
64 Posts |
Posted - 2013-06-12 : 06:15:53
|
| I'm not really that proficient on SQL is this an easy process? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-12 : 06:19:28
|
| yep..your DBA should be able to help you out------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-06-12 : 10:03:41
|
If they had a DBA, the problem would not occur in the first place, right?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-12 : 10:13:08
|
quote:
Originally posted by SwePeso
If they had a DBA, the problem would not occur in the first place, right?
N 56°04'39.26"E 12°55'05.63"
Hmm..How can that be guaranteed?How will a DBA be aware of all internal logic used by the application and causing the creation of this anonymous row? That might be something related to business logic not implemented in right way.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-06-12 : 12:39:57
|
quote:
Originally posted by raindear
I am fairly new to SQL but my questions are1.How many rows can we get up to before the table is full?2.Can the deleted rows be recycled and reused?
1. Limited by available storage (http://msdn.microsoft.com/en-us/library/ms143432.aspx)2. Yes you can detele the rows and the space may be reclaimed, but the "row" is not reused per se. |
|
|
|
|
How many rows can a table have?
------------------------------------------------------------------------------------------------------SQL Server MVP