I'm doing my best an deleting rows and rebuilding indexes but the size of the database is growing really fast and I can't see the effect of my operations.
Is there a cause for this and is there a way to reduce the size of the database other than row deletion and index rebuild?
Thank you very much
Please run the following special store procedure and let us know which database file is getting bigger.
sp_helpfile
If the log file is getting bigger, please run below statement to recover space log.
DBCC SHRINKFILE (log, 0)
If the data file, and not the log file, is increasing in size use below query to know which tables are consuming the most space, and start to investigate from there.
select
o.name,
max(s.row_count) AS 'Rows',
sum(s.reserved_page_count) * 8.0 / (1024 * 1024) as 'GB',
(8 * 1024 * sum(s.reserved_page_count)) / (max(s.row_count)) as 'Bytes/Row'
from sys.dm_db_partition_stats s, sys.objects o
where o.object_id = s.object_id
group by o.name
having max(s.row_count) > 0
order by GB desc
Below query gives you the size of every index also.
select
o.Name,
i.Name,
max(s.row_count) AS 'Rows',
sum(s.reserved_page_count) * 8.0 / (1024 * 1024) as 'GB',
(8 * 1024* sum(s.reserved_page_count)) / max(s.row_count) as 'Bytes/Row'
from
sys.dm_db_partition_stats s,
sys.indexes i,
sys.objects o
where
s.object_id = i.object_id
and s.index_id = i.index_id
and s.index_id >0
and i.object_id = o.object_id
group by i.Name, o.Name
having SUM(s.row_count) > 0
order by GB desc