How do I reduce the size of my databases 4

[Maritime]

Hi,
I once again have a database that seems to be growing out of control. I have run dbcc shrinkdatabase, however the database doesn't seem to be getting any smaller. I have backed up the database and truncated the logs, but the database is still huge.

The database is configured to use space on 3 drives and it's just eating up all the free space that the drives have. On the database options I restricted file growth to a certain amount of space and I have Automatically Grow database in MB=1. The database seems to have ignored this. I don't know what to do to recover the space.

Thanks.

 

[MDXer]

I guess the first method would be to check and make sure your datatype and column widths are all what they need to be to function but not so large they require excess space. Things like using a varchar(50) over a char(50) a smalldatetime over datetime. these items may only account for a few bytes per record but can add up over the life of the database. also make sure that you don't have any wasted indexes, indexes that are never used.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Maritime]

Most of my tables are created by an import from an AS400. So I do not have any control over the table design. I can't figure out how to recover the free space, for I know that the database is not that big.

 

[mrdenny]

Take a look at the taskpad view of the database in EM. From there check the tables tab. That will let you easily see the sizes for each table, and each index. That will tell you exactally which table or index is using up all your space.

 

[Maritime]

Hi mrdenny.

Thanks a lot. This was very helpful. Thanks again.

 

[mrdenny]

no problem.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[Corran007]

I find that it can take a bit if you have alot of tables. i uses this script where i got from somewhere. it tells you the size of the tables and wasted space for top 25

/**************************************************************************************
*
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* graz@sqlteam.com
* v1.1
*
**************************************************************************************/

declare @id int
declare @type character(2)
declare @pages int
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)

create table #spt_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)

set nocount on

-- Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = 'U'

open c_tables

fetch next from c_tables
into @id

while @@fetch_status = 0
begin

/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id

select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
where objid = @id


/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
where objid = @id

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where objid = @id

update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
and objid = @id

fetch next from c_tables
into @id
end


select top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
data_KB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')

from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc

drop table #spt_space
close c_tables
deallocate c_tables

 

[JohnBates]

thanks Corran007. That is a very useful tool !
John

 

[MrMystery]

Thank you VERY much Corran007!! You have saved me from adding more disks to my server - I found a table which was eating LOTS of disk space, and it turned out to be redundant, so I deleted it and have freed up loads of space!
Mike

 

[mrdenny]

Corran, you might want to make that an FAQ. That's a handy little script that people would probably like to be able to find easily.

Denny

--Anything is possible. All it takes is a little research. (Me)