how to find rowsize in table

Question

One of my DBs have grown closer to permitted size.

Inorder to find out the table containing the max data, i used the following query:

exec sp_MSforeachtable @command1="print '?' exec sp_spaceused '?'"

It returned the culprit table comprising the max data.

As a next step, i want to cleanup the rows based on the size. For this, i would like to order the rows based on size.

How to achieve this using a query? Are there any tools to do this?

Answer 1

An easier approach for all table sizes is to use the stored procedure at this site. You could alter the select statement of that stored procedure to:

SELECT * 
FROM #TempTable
Order by dataSize desc

to have it ordered by size.

How do you want to cleanup? Cleanup the biggest row of a specific table? Not sure I understand the question.

EDIT (response to comment)

Assuming your eventlog has the same layout as mine (DNN eventlog):

SELECT     LEN(CONVERT(nvarchar(MAX), LogProperties)) AS length
FROM         EventLog
ORDER BY length DESC

Answer 2

Maybe something like this will work

delete table where id in 
(
    select top 100 id
    from table
    order by datalength(event_text) + length(varchar_column) desc
)

(since you are dealing with an event table its probably a text column you are looking at ordering on so the datalength sql command is key here)

Answer 3

This will give you a list of rows by size, just set @table and @idcol accordingly (as written it'll run against the Northwind sample)

declare @table varchar(20)
declare @idcol varchar(10)
declare @sql varchar(1000)

set @table = 'Employees'
set @idcol = 'EmployeeId'
set @sql = 'select ' + @idcol +' , (0'

select @sql = @sql + ' + isnull(datalength(' + name + '), 1)' 
 from syscolumns where id = object_id(@table)
set @sql = @sql + ') as rowsize from ' + @table + ' order by rowsize desc'

exec (@sql)