Mike,
>1. I've already changed the Autogrowth of the following files
> Data File (Autogrowth = 10% Unrestricted Growth) (Initial size = 35138 MB)
> Log File (Autogrowth = 10% Restricted Growth) (Initial size = 10668 MB)
>
> Have I set it up correctly?
That should be fine, we like to have fixed MB size increments, but percentage works too.
>3. How heavily inserted is your database? How heavily updated is it?
>
> We have approximately 2000 to 3000 transactions per day. That would be about 6000 to 10000 records being inserted on our tables.
> Updates do not happen very often. We only do updates for data correction, transaction adjustments and the like.
So what is that in terms of rough percentage of the tables involved? ie how many transactions are in the database already?
>4. Is it possible or normal for the index file size to exceed the data file size, considering that only 50 percent of the fields have index?
Quite often a table will have more space consumed by index than data. The question becomes if you really need all of those indexes to support the queries being run against the database. Do you have a lot of really complex indexes, ones composed of several fields? A lot of times those can be removed and replaced with much simpler single field indexes.
dbcc showcontig( 'TheTable' ) WITH tableresults, ALL_INDEXES
Run the above in query analyzer and copy/paste the result over to excel, add two columns: ActualCount / BestCount, and ( AcutalCount - BestCount ) * 8192. These two numbers give you a better look at how many times too big the number of index pages are and how many bytes of waste you have. The worst ones give you candidates for reindexing.
This is some code adapted from some article that I've used to do global space analysis on a database:
dbcc updateusage(0) with no_infomsgs
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @unused dec(15,0)
create table #spt_space
(
[Database Name] varchar(64),
[Table Name] varchar(64),
rows int null,
reserved dec(15,0) null,
data dec(15,0) null,
indexp dec(15,0) null,
unused dec(15,0) null
)
set nocount on
select @dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)
select @bytesperpage = low
from master.dbo.spt_values
where number = 1 and type = 'E'
select @unused = sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
insert into #spt_space values
(
db_name(),
'',
null,
@dbsize,
null,
null,
@dbsize - @unused
)
create table #temp (
uid int identity(1,1),
id int,
[Table Name] varchar(64),
pages1 int null,
pages2 int null,
reserved int null,
indexexp int null,
rows int null,
unused int null
)
insert into #temp ( id, [Table Name] )
select id, name
from sysobjects
where xType = 'U'
update #temp
set pages1 = ( select sum(dpages)
from sysindexes
where indid < 2 and #temp.id = sysindexes.id )
update #temp
set pages2 = ( select isnull(sum(used), 0)
from sysindexes
where indid = 255 and #temp.id = sysindexes.id )
update #temp
set reserved = ( select sum(reserved)
from sysindexes
where indid in (0, 1, 255) and #temp.id = sysindexes.id )
update #temp
set indexexp = ( select sum(used)
from sysindexes
where indid in (0, 1, 255) and #temp.id = sysindexes.id ) - ( pages1 + pages2 )
update #temp
set unused = reserved - indexexp - ( pages1 + pages2 )
update #temp
set rows = ( select rows
from sysindexes
where indid < 2 and #temp.id = sysindexes.id )
insert into #spt_space
select
db_name(),
[Table Name],
rows,
reserved,
pages1 + pages2,
indexexp,
unused
from #temp
select 'Database Name', 'Table Name',
'rows',
'Allocated space',
'Data space',
'Index space',
'Unused space',
'Percent Data',
'Percent Index',
'Percent Unused',
'Percent of Database'
select [Database Name], [Table Name],
rows,
[Allocated space] = reserved * @bytesperpage,
[Data space] = data * @bytesperpage,
[Index space] = indexp * @bytesperpage,
[Unused space] = unused * @bytesperpage,
[Percent Data] = case
when reserved > 0 then cast( data / reserved * 100.0 as dec(5,1))
else 0.0 end,
[Percent Index] = case
when reserved > 0 then cast( indexp / reserved * 100.0 as dec(5,1))
else 0.0 end,
[Percent Unused] = case
when reserved > 0 then cast( unused / reserved * 100.0 as dec(5,1))
else 0.0 end,
[Percent of Database] = cast( reserved / @dbsize * 100.0 as dec(5,1))
from #spt_space
order by [Table Name]
drop table #spt_space
drop table #temp
Again these two result sets can be copied/pasted into Excel for further slicing and dicing (I suppose that could be contracted to Roncoing *bg*)