Finding top 5 max size tables and deleting records
-
I am an Oracle DBA. I need to fix a issue in a Sqlserver database and I am a newbee to it. Large number of rows are inserted into some tables in the database and database size keep increasing, say 500 MB in a hour. I didn't have database/application design or table details.
I need to find:
1. Top 10 tables based on their size.
2. Delete the records from these tables based on date.
3. To schedule delete process to execute daily.
Please provide commands/steps for it.
Thanks,
Ashok.
ASHOK
ASHOK -
I can offer a starting place. Take a look at sp_spaceused to determine the amount of space - typically you would open a cursor and run this proc once per table. For your situation you'd probably insert the results into a table, then do a select as a second step to determine which tables need to have records removed. From there it's easy to delete records based on a date in the table and comparing it to the current date (using GetDate function).
If you're planning to do this on a scheduled basis, why not set up a delete statement per table to remove records based on appropriate date range for that table, put all statements in a single job that runs nightly? Good practice to remove old data anyway.
Andy
-
I have an SP which I run on dtabases to keep track of table growth which you can find at http://www.nigelrivett.com.
You will notice that
select max(rows) from sysindexes
should be
select rows from sysindexes where indid in (0,1)
This is another one which give the space used (I didn't add it to above because I've lost access to that site).
if exists (select * from sysobjects where id = object_id(N'[dbo].[spReportTableSpace]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spReportTableSpace]
GO
create procedure spReportTableSpace
@dbnamevarchar(128)
as
/*
exec spReportTableSpace 'master'
*/
declare@cmdnvarchar(1000)
set nocount on
create table #Report (s varchar(1000), id int identity (1,1))
create table #a (name varchar(128), size int, id int identity)
select @cmd = 'select name , 0 from ' + @dbname + '..sysobjects where type = ''U'''
insert #a
exec (@cmd)
declare @name varchar(128)
create table #b (name varchar(128), rows varchar(128), reserved varchar(128) , data varchar(128), index_size varchar(128), unused varchar(128))
declare @id int
select @id = 0
while @id < (select max(id) from #a)
begin
select @id = min(id) from #a where id > @id
select @name = name from #a where id = @id
delete #b
select @cmd = 'exec ' + @dbname + '..sp_spaceused ' + @name
insert#b
exec (@cmd)
update #a
set size = convert(int,left(reserved,datalength(reserved) - 3))
from #b
where #a.id = @id
end
insert#Report
select @dbname + ' total used space by tables = ' + convert(varchar(10),sum(size)) + ' kb'
from #a
insert#Report select ''
insert #report (s)
select right(space(15) + convert(varchar(10),size) + ' kb', 15) + ' ' + name from #a
order by size desc
drop table #a
drop table #b
selects
from#Report
order by id
go
Cursors never.
DTS - only when needed and never to control. -
Hi Andy and Nigelrivett,
Thanks for your suggestions. I will try them.
I saw the procedure sp_spaceused. Actually I am looking for system tables similar to Oracle Data Dictionary where I can get like, select segment_name,segment_type, bytes/1024/1024 from dba_segments where bytes/1024/1024 > 100;
and delete records from it.
Ashok.
ASHOK
ASHOK -
Another alternative. In sysindexes, the # of rows is stored. Accurate for clustered indexes. You can guessimate (or calculate if there are no varchar columns) the size of the table from this.
Steve Jones
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply