SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How many rows can a table have?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raindear
Yak Posting Veteran

64 Posts

Posted - 06/12/2013 :  05:14:10  Show Profile  Reply with Quote
Hi

Something 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 are

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

India
52325 Posts

Posted - 06/12/2013 :  05:23:48  Show Profile  Reply with Quote
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 overkill

Why 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

raindear
Yak Posting Veteran

64 Posts

Posted - 06/12/2013 :  06:05:17  Show Profile  Reply with Quote
Thats the thing. We have tried looking but we cannot find where the issues is coming from
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/12/2013 :  06:08:08  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

raindear
Yak Posting Veteran

64 Posts

Posted - 06/12/2013 :  06:15:53  Show Profile  Reply with Quote
I'm not really that proficient on SQL is this an easy process?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/12/2013 :  06:19:28  Show Profile  Reply with Quote
yep..your DBA should be able to help you out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 06/12/2013 :  06:21:26  Show Profile  Reply with Quote
Refer these links for SQL Profiler Trace
http://beyondrelational.com/modules/12/tutorials/631/tutorials/15252/getting-started-with-sql-server-profiler-part-2-profiler-templates-template-types-and-creating-custo.aspx
http://dba.stackexchange.com/questions/519/sql-profiler-tutorials-for-newbie

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/12/2013 :  06:23:45  Show Profile  Reply with Quote
quote:
Originally posted by bandi

Refer these links for SQL Profiler Trace
http://beyondrelational.com/modules/12/tutorials/631/tutorials/15252/getting-started-with-sql-server-profiler-part-2-profiler-templates-template-types-and-creating-custo.aspx
http://dba.stackexchange.com/questions/519/sql-profiler-tutorials-for-newbie

--
Chandu


very little chance OP will have the access to do it in production which is why i suggested to sought DBAs help for this.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 06/12/2013 :  10:03:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/12/2013 :  10:13:08  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/12/2013 :  12:39:57  Show Profile  Reply with Quote
quote:
Originally posted by raindear

I am fairly new to SQL but my questions are

1.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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000