records per week

I have one database. In that DB the largest table is "xyz" . This runs into millions of records over time. Can we see how much this table grows by records every week.

Thanks in advance.

Regards,
Anil Kumar

Hi Hemanth,

Thanks for looking into this....

My database is growing quite rapidely, one GB per week. To get control over my database, like how much xyz table growing per week and how much will grow in near future. My main concern is to know in which month/week the table is growing much and in which month/week it is normally growing.
Based on that setps we need to take etc....

I am some what unclear with your reply. It means I must create a column getdate() which will take count.

Could you explain with an example.... in script format.

Regards,
Anil Kumar

Hi,

Fine script. I have made some changes for my requirement. I think it will work.

Send your opinion on my script, Is it work or not?

Thanks for sending script.


alter table xyz
add sysdate datetime default getdate()

use xyz
select count(*) from xyz where abs(datediff(day,getdate(),sysdate))<=7

Regards,
Anil Kumar

quote:Originally posted by ghemant

Hi,
for that you can add a column that stores date{derault getdate()} in *xyz* table , and every time record inserted in that *xyz* table a value for that new column is added / inserted then based on that you can easily count it as per yr requirement , week / month /quarter or year wise.

but may we know why you want this ! do you wants some reports for entry or make some assumptions for db growth ratio based on this !


Regards


Hemantgiri S. Goswami
ghemant@gmail.com
"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami

If you are only interested in the week and not the day or time, you should also consider a separate table that contains the year and week # and the count of rows in your table. This will save a considerable amount of space and is technically more normalized than storing a unneccessary datetime in every row.

You can populate the table with an insert trigger, or add the appropriate INSERT/UPDATE command into your maintenance plan.

Keith Payne
Technical Marketing Solutions
www.tms-us.com

Hello there!


Depending on your needs, you can create a separated table, containing only the large table's row count information, updated thru a scheduled job running daily, weekly, monthly, on your maintenance window...

I believe this way you can save some space on your storage device, keep your insert performance the way it is and separate your business tables from your maintenance tables.

You guys may want to check out this script:
SET NOCOUNT ON

DECLARE @dbidINT,
@SQLVARCHAR(8000),
@dbnameVARCHAR(255),
@tbnameVARCHAR(255)

SELECT @dbname = 'MyDatabase'-- Set your database name
SELECT @tbname = 'MyOwner.MyLargeTable'-- Set your table name to perform a row count


CREATE TABLE #TMP_TBINFO (QTDROWS NUMERIC(19,0))


SELECT @SQL = 'DBCC UPDATEUSAGE (' + @dbname + ') WITH COUNT_ROWS, NO_INFOMSGS'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL = 'INSERT INTO #TMP_TBINFO SELECT rows FROM ' + @dbname + '..sysindexes WHERE indid < 2 AND id = OBJECT_ID(''' + @dbname + '.' + @tbname + ''')'
PRINT @SQL
EXEC(@SQL)


INSERT INTO YourHistoryTable-- Set your row count history table name
SELECT CONVERT(VARCHAR(19), GETDATE(), 120) AS REFDATE, QTDROWS FROM #TMP_TBINFO

DROP TABLE #TMP_TBINFO


Regards,

Fernando Toledo

Wow folks,

Great responce. Thanks for all....

Satya, that is the exactly same Question I am going to ask. But u r ahead of the game...

Offcourse it is Question. I want to ask to you but u have asked.....

I do have a practice of taking sp_spaceused in day-today basis. This helped me in terms of record growth and size.

try this in QA:

sp_spaceused "owner.table_name"
or
sp_msforeachtable 'sp_spaceused"?"'

What I do as a practice, take the result and save it in daily, and I can compare it.

Try it
-Johnson

Wow Satya. That is a good link. This will archive data to another database. Offcourse it is useful for lot of databases but I have one database. I want the database data will perminently archived. Is there any other link <img src='/community/emoticons/emotion-1.gif' alt='' />