Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Rapid Growth of Database---Newbie

Status
Not open for further replies.

btacy

IS-IT--Management
Nov 2, 2007
32
GB
Hi,
I'm new to SQL Server and i'd appreciate as much help as i can get. the database i inherited doubled in size (backup files) within a period of two months, this has caused some concern, as there hasn't been any major influx of data other than regular work inputs. Any idea what the problem could be or how to go about diagnosing the problem.

it's SQL server 2005 sitting on Windows 2003 server.

thanks in advance
 
Check out how many unused space available on the database, then issue DBCC SHRINKFILE or DBCC SHRINKDATABASE to see how much space you can reclaim.

Then try to rebuild index to remove fragmentation. Also check out the size of table/index to see which object is really occuplying space on the database.

 
since you're on 2005, here's something I found which lists table sizes and index sizes, it's been VERY helpful. p.s. I pulled it off some site but can't remember which one...

Code:
with TableSize (
	schema_name, table_name, index_name, used, reserved, ind_rows, tbl_rows
) AS (
	select
		s.Name,
		o.Name,
		coalesce(i.Name, 'HEAP'),
		p.used_page_count * 8,
		p.reserved_page_count * 8,
		p.row_count,
		case 
			when i.index_id in (0, 1) then p.row_count
			else 0
		end
	from
		sys.dm_db_partition_stats p inner join
		sys.objects as o on o.object_id = p.object_id inner join
		sys.schemas as s on s.schema_id = o.schema_id left join
		sys.indexes as i on i.object_id = p.object_id 
			and i.index_id = p.index_id
	where 
		o.type_desc = 'USER_TABLE'
		and o.is_ms_shipped = 0
)
	select
		t.schema_name,
		t.table_name,
		t.index_name,
		sum(t.used) as UsedInKb,
		sum(t.reserved) as ReservedInKb,
		case grouping(t.index_name)
			when 0 then sum(t.ind_rows)
			else sum(t.tbl_rows)
		end as rows
	from TableSize as t
	group by
		t.schema_name,
		t.table_name,
		t.index_name
	with rollup
	order by 
		rows desc

--------------------
Procrastinate Now!
 
thanks guys,
i executed the script Crowley16, and i found that there are two tables (table name = null, index name=null)one of them is the the dbo schema and the other has a null schema value. these two table are extremely large and have a large amount of space as reserved page count as well.

is there a another way to deal with this or do i just go ahead and issue the 'dbcc shrink database' command?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top