If I don't have a field for DateEntered in my tables, is
there any System Tables that I can find out how many
records the database is taking per day?
Thank you sooooo much!!!!
AnnieYou could create an ON INSERT trigger to log each time someone INSERTs into your table
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||This is a multi-part message in MIME format.
--=_NextPart_000_0261_01C3600F.F913DBB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Nope. What you can do is run SELECT COUNT (*) on the table(s) in =question at the same time every day and store the result in a table or =spreadsheet, then calculate the difference from previous day.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Annie" <piyomeme@.yahoo.com> wrote in message =news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
Hi everyone,
If I don't have a field for DateEntered in my tables, is there any System Tables that I can find out how many records the database is taking per day?
Thank you sooooo much!!!!
Annie
--=_NextPart_000_0261_01C3600F.F913DBB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Nope. What you can do is run =SELECT COUNT (*) on the table(s) in question at the same time every day and store the =result in a table or spreadsheet, then calculate the difference from previous day.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Annie"
--=_NextPart_000_0261_01C3600F.F913DBB0--|||If you are just wanting inserts, then you could build a job to do a
sp_spaceused <table_name> into another table. Just have the job run in what
ever interval you want. You could also export sysindexes as well.
If you are wanting DELETE, UPDATE and INSERT, I have no real good answer. I
keep track of daily DUI, however I have replication in place and that makes
it very easy.
"Annie" <piyomeme@.yahoo.com> wrote in message
news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
> Hi everyone,
> If I don't have a field for DateEntered in my tables, is
> there any System Tables that I can find out how many
> records the database is taking per day?
> Thank you sooooo much!!!!
> Annie|||This is a multi-part message in MIME format.
--=_NextPart_000_0094_01C36009.FA1A36E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
To build on his statement and my other one. SELECT COUNT(*) would work, =but in a large environment might not be optimal. "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:elWpGGDYDHA.1784@.TK2MSFTNGP09.phx.gbl...
Nope. What you can do is run SELECT COUNT (*) on the table(s) in =question at the same time every day and store the result in a table or =spreadsheet, then calculate the difference from previous day.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Annie" <piyomeme@.yahoo.com> wrote in message =news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
Hi everyone,
If I don't have a field for DateEntered in my tables, is there any System Tables that I can find out how many records the database is taking per day?
Thank you sooooo much!!!!
Annie
--=_NextPart_000_0094_01C36009.FA1A36E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
To build on his statement and my other =one. SELECT COUNT(*) would work, but in a large environment might not be optimal.
"Tom Moreau"
Nope. What you can do is run =SELECT COUNT (*) on the table(s) in question at the same time every day and store =the result in a table or spreadsheet, then calculate the difference from =previous day.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Annie"
--=_NextPart_000_0094_01C36009.FA1A36E0--|||Agreed
With the information given we know nothing of "Hits" per day. I would personally go with Tom's idea and take a SELECT COUNT(*) from each table put it in a spreadsheet and calculate the differences.
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||This is a multi-part message in MIME format.
--=_NextPart_000_0293_01C36013.7CC0EF90
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
It depends on how accurate they want the numbers. I'd go for adding a =last updated column on the target tables and then adding a nonclustered =index to support the query,
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message =news:OcXClPDYDHA.1900@.TK2MSFTNGP10.phx.gbl...
To build on his statement and my other one. SELECT COUNT(*) would work, =but in a large environment might not be optimal. "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:elWpGGDYDHA.1784@.TK2MSFTNGP09.phx.gbl...
Nope. What you can do is run SELECT COUNT (*) on the table(s) in =question at the same time every day and store the result in a table or =spreadsheet, then calculate the difference from previous day.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Annie" <piyomeme@.yahoo.com> wrote in message =news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
Hi everyone,
If I don't have a field for DateEntered in my tables, is there any System Tables that I can find out how many records the database is taking per day?
Thank you sooooo much!!!!
Annie --=_NextPart_000_0293_01C36013.7CC0EF90
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
It depends on how accurate they want =the numbers. I'd go for adding a last updated column on the target =tables and then adding a nonclustered index to support the query,
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kevin Brooks"
To build on his statement and my other =one. SELECT COUNT(*) would work, but in a large environment might not be optimal.
"Tom Moreau"
Nope. What you can do is run =SELECT COUNT (*) on the table(s) in question at the same time every day and store =the result in a table or spreadsheet, then calculate the difference from =previous day.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Annie"
--=_NextPart_000_0293_01C36013.7CC0EF90--|||Thank You for helping~
According to Books-On-Line, The value in the timestamp
column is
updated every time a row containing a timestamp column is
inserted or
updated. And the timestamp field is in Binary code.
I found a timestamp field in my DB, is there any way I can
decode the
timestamp's binary code to readable text in order to
identify the record
updated time?
Thank you~~
Annie
>--Original Message--
>Agreed
>With the information given we know nothing of "Hits" per
day. I would personally go with Tom's idea and take a
SELECT COUNT(*) from each table put it in a spreadsheet
and calculate the differences.
>
>
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>.
>|||Thank You for helping~
According to Books-On-Line, The value in the timestamp
column is
updated every time a row containing a timestamp column is
inserted or
updated. And the timestamp field is in Binary code.
I found a timestamp field in my DB, is there any way I can
decode the
timestamp's binary code to readable text in order to
identify the record
updated time?
Thank you~~
Annie
>--Original Message--
>If you are just wanting inserts, then you could build a
job to do a
>sp_spaceused <table_name> into another table. Just have
the job run in what
>ever interval you want. You could also export sysindexes
as well.
>If you are wanting DELETE, UPDATE and INSERT, I have no
real good answer. I
>keep track of daily DUI, however I have replication in
place and that makes
>it very easy.
>
>"Annie" <piyomeme@.yahoo.com> wrote in message
>news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
>> Hi everyone,
>> If I don't have a field for DateEntered in my tables,
is
>> there any System Tables that I can find out how many
>> records the database is taking per day?
>> Thank you sooooo much!!!!
>> Annie
>
>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_00C7_01C3600E.297F40C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
This would work too, but you are still increasing size and decreasing =response. I understand we do not know the size and other specs, however =it all about least impact on the server and applications. Heck I have =some crappy in house and 3rd party apps that will not let me change =schema without blowing up. As far as accuracy, that is what AVG is =for:)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OrVD8UDYDHA.2236@.TK2MSFTNGP10.phx.gbl...
It depends on how accurate they want the numbers. I'd go for adding a =last updated column on the target tables and then adding a nonclustered =index to support the query,
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message =news:OcXClPDYDHA.1900@.TK2MSFTNGP10.phx.gbl...
To build on his statement and my other one. SELECT COUNT(*) would =work, but in a large environment might not be optimal. "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:elWpGGDYDHA.1784@.TK2MSFTNGP09.phx.gbl...
Nope. What you can do is run SELECT COUNT (*) on the table(s) in =question at the same time every day and store the result in a table or =spreadsheet, then calculate the difference from previous day.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Annie" <piyomeme@.yahoo.com> wrote in message =news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
Hi everyone,
If I don't have a field for DateEntered in my tables, is there any System Tables that I can find out how many records the database is taking per day?
Thank you sooooo much!!!!
Annie
--=_NextPart_000_00C7_01C3600E.297F40C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
This would work too, but you are still =increasing size and decreasing response. I understand we do not know the =size and other specs, however it all about least impact on the server and applications. Heck I have some crappy in house and 3rd party apps =that will not let me change schema without blowing up. As far as =accuracy, that is what AVG is for:)
"Tom Moreau"
It depends on how accurate they want =the numbers. I'd go for adding a last updated column on the target =tables and then adding a nonclustered index to support the =query,
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kevin Brooks"
To build on his statement and my =other one. SELECT COUNT(*) would work, but in a large environment might not be optimal.
"Tom Moreau"
Nope. What you can do is run =SELECT COUNT (*) on the table(s) in question at the same time every day and =store the result in a table or spreadsheet, then calculate the difference =from previous day.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Annie"
--=_NextPart_000_00C7_01C3600E.297F40C0--|||This is a multi-part message in MIME format.
--=_NextPart_000_02D0_01C36017.9A333F70
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I guess a third possibility is taking the most recent backup and =restoring it onto another box and running the stats there ...
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message =news:OLyRUgDYDHA.2448@.TK2MSFTNGP09.phx.gbl...
This would work too, but you are still increasing size and decreasing =response. I understand we do not know the size and other specs, however =it all about least impact on the server and applications. Heck I have =some crappy in house and 3rd party apps that will not let me change =schema without blowing up. As far as accuracy, that is what AVG is =for:)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OrVD8UDYDHA.2236@.TK2MSFTNGP10.phx.gbl...
It depends on how accurate they want the numbers. I'd go for adding a =last updated column on the target tables and then adding a nonclustered =index to support the query,
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message =news:OcXClPDYDHA.1900@.TK2MSFTNGP10.phx.gbl...
To build on his statement and my other one. SELECT COUNT(*) would =work, but in a large environment might not be optimal. "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:elWpGGDYDHA.1784@.TK2MSFTNGP09.phx.gbl...
Nope. What you can do is run SELECT COUNT (*) on the table(s) in =question at the same time every day and store the result in a table or =spreadsheet, then calculate the difference from previous day.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Annie" <piyomeme@.yahoo.com> wrote in message =news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
Hi everyone,
If I don't have a field for DateEntered in my tables, is there any System Tables that I can find out how many records the database is taking per day?
Thank you sooooo much!!!!
Annie --=_NextPart_000_02D0_01C36017.9A333F70
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I guess a third possibility is taking =the most recent backup and restoring it onto another box and running the stats =there ...
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kevin Brooks"
This would work too, but you are still =increasing size and decreasing response. I understand we do not know the =size and other specs, however it all about least impact on the server and applications. Heck I have some crappy in house and 3rd party apps =that will not let me change schema without blowing up. As far as =accuracy, that is what AVG is for:)
"Tom Moreau"
It depends on how accurate they want =the numbers. I'd go for adding a last updated column on the target =tables and then adding a nonclustered index to support the =query,
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kevin Brooks"
To build on his statement and my =other one. SELECT COUNT(*) would work, but in a large environment might not be optimal.
"Tom Moreau"
Nope. What you can do is run =SELECT COUNT (*) on the table(s) in question at the same time every day and =store the result in a table or spreadsheet, then calculate the difference =from previous day.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Annie"
--=_NextPart_000_02D0_01C36017.9A333F70--|||This is a multi-part message in MIME format.
--=_NextPart_000_00ED_01C36015.3E3B4FC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Yup, that would work too. I would just dump sysindexes or use =sp_spaceused for the calculations though. I update stats on a regular =basis and the numbers will avg nicely. COUNT(*) will block potentially =unless you use (NOLOCK), in which case you are doing a dirty read which =throws off accuracy.
My only reason for raising all these responses, is that we need to =always design and think high end, highly volitile, high availablity and =big databases. COUNT(*), changing table schema and adding a trigger are =good ideas. They would probably be a great solution in several =environments, just not all.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:O$0OukDYDHA.1916@.TK2MSFTNGP12.phx.gbl...
I guess a third possibility is taking the most recent backup and =restoring it onto another box and running the stats there ...
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message =news:OLyRUgDYDHA.2448@.TK2MSFTNGP09.phx.gbl...
This would work too, but you are still increasing size and decreasing =response. I understand we do not know the size and other specs, however =it all about least impact on the server and applications. Heck I have =some crappy in house and 3rd party apps that will not let me change =schema without blowing up. As far as accuracy, that is what AVG is =for:)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OrVD8UDYDHA.2236@.TK2MSFTNGP10.phx.gbl...
It depends on how accurate they want the numbers. I'd go for adding =a last updated column on the target tables and then adding a =nonclustered index to support the query,
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message =news:OcXClPDYDHA.1900@.TK2MSFTNGP10.phx.gbl...
To build on his statement and my other one. SELECT COUNT(*) would =work, but in a large environment might not be optimal. "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:elWpGGDYDHA.1784@.TK2MSFTNGP09.phx.gbl...
Nope. What you can do is run SELECT COUNT (*) on the table(s) in =question at the same time every day and store the result in a table or =spreadsheet, then calculate the difference from previous day.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Annie" <piyomeme@.yahoo.com> wrote in message =news:021b01c36030$d4b1baa0$a301280a@.phx.gbl...
Hi everyone,
If I don't have a field for DateEntered in my tables, is there any System Tables that I can find out how many records the database is taking per day?
Thank you sooooo much!!!!
Annie
--=_NextPart_000_00ED_01C36015.3E3B4FC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Yup, that would work too. I would =just dump sysindexes or use sp_spaceused for the calculations though. I =update stats on a regular basis and the numbers will avg nicely. COUNT(*) will =block potentially unless you use (NOLOCK), in which case you are doing a =dirty read which throws off accuracy.
My only reason for raising all these =responses, is that we need to always design and think high end, highly volitile, high availablity and big databases. COUNT(*), changing =table schema and adding a trigger are good ideas. They would probably be a great =solution in several environments, just not all.
"Tom Moreau"
I guess a third possibility is =taking the most recent backup and restoring it onto another box and running the stats =there ...
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kevin Brooks"
This would work too, but you are =still increasing size and decreasing response. I understand we do not know =the size and other specs, however it all about least impact on the server and applications. Heck I have some crappy in house and 3rd party =apps that will not let me change schema without blowing up. As far as =accuracy, that is what AVG is for:)
"Tom Moreau"
It depends on how accurate they =want the numbers. I'd go for adding a last updated column on the target =tables and then adding a nonclustered index to support the =query,
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kevin Brooks"
To build on his statement and my =other one. SELECT COUNT(*) would work, but in a large environment =might not be optimal.
"Tom Moreau"
Nope. What you can do is =run SELECT COUNT (*) on the table(s) in question at the same time every day =and store the result in a table or spreadsheet, then calculate the =difference from previous day.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, =SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Annie"
--=_NextPart_000_00ED_01C36015.3E3B4FC0--
No comments:
Post a Comment