SQL Server Performance Forum – Threads Archive

database grew immensly

One of our db which has been consistent over past few years and suddenly has increased by double its size in a year. I need to know: which parameters have cased this.
how to proceed further on anlayzing what has caused such and increase and to present the same. Any help is greatly appretiated. Regards,
Samata

There is no such information or reports stored in SQL server, you have to perform such audit by taking into consideration on number of tables, their usage, scheduled jobs such as db reindex etc.http://www.sql-server-performance.com/sql_server_performance_audit.asp is best thing to start. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

I guess u need to check which table or index takes more space.U can use sp_spaceused(tablename) to get space used by this object in DB.U can use this in Sp_MSforeachtable for getting all values for all tables.
Then u can analyze which table consume more space.

What recovery model do you have and what is the backup plan? Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Run this in query analyzer with the database in question selected: select
so.id as [OBJECT_ID],
CONVERT(CHAR(30),so.name) as [OBJECT_NAME],
coalesce(j_rows.rows,0) as [ROWCOUNT],
coalesce(j_ru.sum_reserved,0) * cast(m.low as dec) / 1024 as [RESERVED (KB)],
d.data * cast(m.low as dec) / 1024 /1024 as [DATA (MB)],
(coalesce(j_ru.sum_used,0) – d.data) * cast(m.low as dec) / 1024/ 1024 as [INDEX (MB)],
(coalesce(j_ru.sum_reserved,0) – coalesce(j_ru.sum_used,0)) * cast(m.low as dec) / 1024 as [UNUSED (KB)]
from
sysobjects so — rows
left join sysindexes j_rows on j_rows.indid < 2 and j_rows.id = so.id
left join
( select id, sum(reserved) as sum_reserved, sum(used) as sum_used from sysindexes where indid in (0, 1, 255) group by id ) j_ru on j_ru.id = so.id
left join
( select j_dpages.id, coalesce(j_dpages._sum,0) + coalesce(j_used._sum,0) as data from ( select id, sum(dpages) as _sum from sysindexes where indid < 2 group by id) j_dpages left join
( select id, sum(used) as _sum from sysindexes where indid = 255 group by id ) j_used on j_used.id = j_dpages.id
) d on d.id = so.id
inner join master.dbo.spt_values m on m.number = 1 and m.type = ‘E’
where OBJECTPROPERTY(so.id, N’IsUserTable’) = 1
order by [DATA (MB)] DESC, [ROWCOUNT] ASC It will tell you which tables are largest. This should be the start of your investigation. I run this query on all my databases about once a month to help clean up clutter. Live to Throw
Throw to Live

Check the procedure and run it to get more info from the db…. http://vyaskn.tripod.com/code/sp_show_huge_tables.txt
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Hi, Here are some points * Is your database recovery model changed to Full?
* Bulk copy/insert activity increased?
* What value you have set for database growth?
* Is no of insert transaction increased?
* what is a size of T-Log file in your database? Regards
Hemantgiri S. Goswami
MS SQL Server MVP
————————-
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami http://hemantgirisgoswami.blogspot.com

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] strange how come originator never came back to reply even though lot of things to check…. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> @<a target="_blank" href=http://www.askasqlguru.com/>http://www.askasqlguru.com/</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

]]>