November 15, 2009 at 3:58 pm
Database, current size 332,294,976KB (316.9GB) residing on 320GB Drive E:, server has one other partion Drive C: which has 4.46GB of free space. Any attempt to perform any maintenance on this database fails. I am looking for any suggestions as to reduce the size of the database without removing any data, adding another partion, etc.
November 15, 2009 at 4:24 pm
Is that data and log? Is the log on the same drive? This might be a case where you can shrink the log down to the maximum size it actually needs to be to support normal workloads.
Any columns defined larger then they need to be (unicode when not needed, bigint rather than int, char rather than varchar...)
USe reorganise rather than rebuild when defragging the indexes, those should work.
Start off with defragging the smaller tables to slowly increase amount of free space, don't bother at all with tables < 1000 pages or so.
check fillfactors on indexes, perhaps some could be increased. Any redundant indexes you could get rid of?
---------------------------------------------------------------------
November 15, 2009 at 9:09 pm
You could run this to look for unused index that might be candidates for deletion. Keep in mind that the info in sys.dm_db_index_usage_stats is only accurate since the last restart of sql, so if you restarted sql last week this will show indexes that have not been used only in the last week, and might still be needed.
-- Combine 2 queries of Unused Indexes
-- 1) stats exist, but never used
select db.name AS DATABASENAME, obj.name as 'Table'
, idx.name as 'Index', idx.rowcnt, idx.used/128 'MB', substring(ix.type_desc,1,20), 'Unused Stats', is_unique as 'unique', is_primary_key as 'primary'
--,ixu.* ,idx.* ,obj.*
from sys.dm_db_index_usage_stats ixu
join sys.databases db on db.database_id = ixu.database_id
join sys.objects obj on ixu.object_id = obj.object_id
join sys.sysindexes idx on ixu.object_id = idx.id and ixu.index_id = idx.indid
join sys.indexes ix on ixu.object_id = ix.object_id and ixu.index_id = ix.index_id
where last_user_seek is null and last_user_scan is null and last_user_lookup is null
and ixu.index_id > 0 and db.database_id = db_id() and obj.type <> 'S' -- and ix.type_desc = 'NONCLUSTERED'
and ix.is_unique = 0 and ix.is_primary_key = 0 -- don't include unique or primary keys
UNION
-- 2) Stats were never created, so index is not used
SELECT DB_NAME() AS DATABASENAME,
--OBJECT_NAME(B.OBJECT_ID) AS 'Table',
obj.name as 'Table',
B.NAME AS 'Index',
idx.rowcnt, idx.used/128 'MB', substring(b.type_desc,1,20), 'No Stats', is_unique as 'unique', is_primary_key as 'primary'
FROM SYS.OBJECTS obj
INNER JOIN SYS.INDEXES B ON obj.OBJECT_ID = B.OBJECT_ID
join SYS.SYSINDEXES idx ON B.OBJECT_ID = idx.ID and B.index_id = idx.indid
WHERE NOT EXISTS (SELECT *
FROM SYS.DM_DB_INDEX_USAGE_STATS C
WHERE B.OBJECT_ID = C.OBJECT_ID
AND B.INDEX_ID = C.INDEX_ID)
AND obj.TYPE <> 'S'
and B.NAME is not NULL
and b.is_unique = 0 and b.is_primary_key = 0 -- don't include unique or primary keys
ORDER BY obj.name , idx.NAME
November 16, 2009 at 8:41 am
Get the size of the database files and check for the unused space!
Set nocount on
Print 'Show Size, Space Used, Unused Space, Type, and Name of all database files'
Select
[FileSizeMB]=
convert(numeric(10,2),sum(round(a.size/128.,2))),
[UsedSpaceMB]=
convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) ,
[UnusedSpaceMB]=
convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) ,
[Type] =
case when a.groupid is null then '' when a.groupid = 0 then 'Log' else 'Data' end,
[DBFileName]= isnull(a.name,'*** Total for all files ***')
from
sysfiles a
group by
groupid,
a.name
with rollup
having
a.groupid is null or
a.name is not null
order by
case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end,
a.groupid,
case when a.name is null then 99 else 0 end,
a.name
Regards,
Pavan.
November 16, 2009 at 10:38 am
Please provide information from sysfiles for this database. Also run dbcc sqlperf(logspace) and provide the data for the log for this database.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply