In most of the development environment or staging server will not be monitored by daily. So the database auto growth can occupy countlessly. Here, we can analyze the memory utilization about the table and we can remove if the database has cache, temp or logs table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
declare @t table ( name nvarchar(100), [rows] int, [reserved] nvarchar(100), [data] nvarchar(100), [index_size] nvarchar(100), [unused] nvarchar(100) ) declare @name nvarchar(100) declare tt cursor for Select name from sys.tables open tt fetch next from tt into @name while @@FETCH_STATUS = 0 begin insert into @t exec sp_spaceused @name fetch next from tt into @name end close tt deallocate tt select name as table_name, [rows] as rows_count, data + [index] as total_size, data as data_size, [index] as index_size from (select name, [rows], cast (LEFT(data, LEN(data)-3) as int) data, cast (LEFT(index_size, LEN(index_size)-3) as int) [index] from @t ) x order by 3 desc, 1 |
Cheers..!
Leave a Reply