SQL Server SP emulating Access' Compact & Repair DB

[jasonsalas]

Hi everyone,

Is there a system-level stored procedure in SQL Server that does the same thing as the "Compact and Repair Database" feature in Acess? I've moved some data into a SQL DB, and done some data typing modifications, so if I can compress it any, I'd like to do it.

Thanks!

 

[NoCoolHandle]

First Look up info in BOL for the Maintanence plan wizard.. Not recomended for Large busy db's... Too general

For the Repair... DBCC CHECKDB

for the Compact/Defrag


The first (blue) one will work only on SQL200 the second (green) should work on 7 and 2K

[blue]
Create Proc Sp_DefragDB

as
set nocount on
declare @tid int,@objectName varchar(300),@indexName varchar(300),@cmd Varchar(900)

CREATE TABLE ##fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

insert into ##fraglist
exec ('DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES')



create table ##Frags
(pk int identity (1,1) , objectname varchar(300),indexname varchar(300))

insert into ##Frags

select ObjectName,indexName
from ##fraglist
where
((extentswitches > extents)
or
(countpages / extents < 5 and countpages <>1)) and left(objectname,3) <> 'sys'

select objectname,objectid,indexname,indexid, countpages,extents,extentswitches
from ##fraglist
where
((extentswitches > extents)
or
(countpages / extents < 5 and countpages <>1)) and left(objectname,3) <> 'sys'


Drop Table ##Fraglist

set @tid = 0
while not @tid is null
begin
select @tid = min(pk) from ##frags where pk > @tid
select @objectname = objectname from ##frags where pk = @tid
select @indexname = indexname from ##frags where pk = @tid
set @cmd = 'dbcc dbreindex([' + rtrim(@objectname) + '],[' + rtrim(@indexname) + '])'
if right(@cmd,2) = ',[])'
begin
set @cmd = replace(@cmd,',[])',')')
end
print @cmd
exec (@cmd)
end
select * from ##frags
drop table ##frags
[/blue]

this should work in SQL 7 and above (I will check this tomorrow on a SQL 7 db,

[green]
Create Proc SP_DEFRAGDB
@LogicalFragmentation tinyint = 20,
@ExtentFragmentation tinyint = 20

-- The Logical And Extent Fragmentation Values are specified as wholenumbers, but
-- represent the percentage of fragmentation returned from dbcc showcontig.
-- If you wish to specify a custom percentage, pass the percentage in as a whole number
-- E.G. Sp_DefragDataBase 15,25 will rebuild indexes if Logical (page) fragmentation has hit 15%
-- And Extent Fragmentation is over 25% (Also if the index or table is smaller than 9 pages it
-- doesn't do anything as the table or index is too small to have an impact.

as

set nocount on

create table #fraglist
(ObjectName varchar(60),
IndexName varchar(60) default'',
eFrag varchar(255) null,
lFrag varchar(255) null,
numPages int,
oid int,
iid int)

declare @id int,@iid int,@numpages int,@cmd varchar(8000),@name
varchar(300),
@iname varchar(30),@lFrag varchar(300),@eFrag varchar(300)
select @id = 0,@iid=255
create table #tabsindxs (tabname varchar(30),id int ,indName varchar(30),
iid int)
while @id is not null
begin
select @id = min(id) from sysobjects where id >@id and xtype='u'
select @name = name from sysobjects where id = @id
if @id is null break
set @iid = 255
while @iid is not null
begin
select @iid = max(indid) from sysindexes where indid <@iid and indid <> 0 and id = @id
if @iid is null
begin
set @iid = 255
break
end
insert into #tabsindxs
select so.name as tablename, si.id,si.name as indexname,indid
from sysindexes si join sysobjects so on si.id = so.id
where si.id = @id and indid = @iid and si.name not like '_wa_%'

select @numPages = dpages,@iname = name
from sysindexes where indid =@iid and id = @id

if @iname like '_wa_%'
begin
continue
end

Set @cmd = 'osql /Q&quot;DBCC SHOWCONTIG(' + convert(varchar(30),@id) + ',' + convert(varchar(5),@iid) + ')&quot; /E /d' + db_name() + ' /o C:\showcontig.out'
execute master..xp_cmdshell @cmd ,no_output
create table #lscan(lscan varchar(400))
Set @cmd = 'findstr /C:&quot;Logical Scan&quot; C:\showcontig.out'
insert into #lscan exec master..xp_cmdshell @cmd
select @lfrag = lscan from #lscan where not lscan is null
drop table #lscan
create table #escan (escan varchar(400))
Set @cmd = 'findstr /C:&quot;Extent Scan Fragmentation&quot; C:\showcontig.out'
insert into #escan exec master..xp_cmdshell @cmd
select @efrag = escan from #escan where not escan is null
drop table #escan

insert #fraglist
values(@name,@iname,cast(left(ltrim(rtrim(right(@efrag,6))),len(ltrim(rtrim(right(@efrag,6))))-1) as
decimal),cast(left(ltrim(rtrim(right(@lfrag,6))),len(ltrim(rtrim(right(@lfrag,6))))-1) as decimal),@numPages,@id,@iid)

end
end
-- Build Short List of Tables that need to be reindexed and use &quot;CREATE INDEX With DROP EXISTING&quot; to rebuild where fragmented
Create Table #DefragList (C1 int identity,TName varchar(300),IName
varchar(300),oid int,iid int)
insert into #DefragList
select ObjectName,Indexname,oid,iid from #fraglist where (efrag >@ExtentFragmentation or lfrag >@LogicalFragmentation) and numPages >8 order by 1,2
Declare @fragno int,@index_Type Varchar(300)
set @fragno = 0
while @fragno is not null
begin
select @fragno = min(c1) from #Defraglist where c1 > @fragno
if @fragno is not null
begin
select @name =tname , @iname = iname from #defraglist where c1 = @fragno
Print 'Reindexing....' + @name + ', ' + @iname
-- Commented out due to problems in rebuilding all non clustered indexes if you rebuild clustered index
Create Table #indexes
(index_name varchar(100),index_description varchar(100),index_keys varchar(100))
insert into #indexes exec sp_helpindex @name
select @index_type = index_description from #indexes Where Index_Name = @iname
if left(@index_type,1) = 'c'
begin
Select @cmd = 'Create Clustered Index ' + @iname + ' on ' + @name + '(' + index_keys + ')' from #indexes Where Index_Name = @iname
end
else
begin
Select @cmd = 'Create NonClustered Index ' + @iname + ' on ' + @name + '(' + index_keys + ')' from #indexes Where Index_Name = @iname
end
drop table #indexes
End
end
select * from #fraglist
Select * from #defraglist
drop table #fraglist
drop table #tabsindxs
drop table #Defraglist

[/green]

 

[jasonsalas]

thanks...if you get around to testing it, let me know if it works.