Posted on 12/02/2014 14:57:08
Hi Gunnar,
I use this script on DW8 databases. Don't think there's any difference between the two versions. I'll post without explanation, but ask if you need elaboration.
use [DatabaseName]
DECLARE @DBname varchar(255)
set @DBname = 'DatabaseName'
-- CLEAR STATV2, in-comment if you wish to clear StatV2
/*
truncate table statv2session
truncate table dbo.Statv2SessionBot
truncate table dbo.Statv2Page
truncate table dbo.Statv2Object -- you may have to delete/recreate FK-relation (see below) to use TRUNCATE. Statv2Page is primary, Statv2Object secondary
*/
-- USE BELOW TO FIND CONSTRAINTS ON TABLE StatV2Object IF YOU HAVE PROBLEMS TRUNCATING
/*
Select SysObjects.[Name] As [Constraint Name] ,
Tab.[Name] as [Table Name],
Col.[Name] As [Column Name]
From SysObjects Inner Join
(Select [Name],[ID] From SysObjects) As Tab
On Tab.[ID] = Sysobjects.[Parent_Obj]
Inner Join sysconstraints On sysconstraints.Constid = Sysobjects.[ID]
Inner Join SysColumns Col On Col.[ColID] = sysconstraints.[ColID] And Col.[ID] = Tab.[ID]
where Tab.name = 'Statv2Page'
order by [Tab].[Name]
*/
-- SHRINK DATABASE
DBCC SHRINKDATABASE (@DBname, 10);
GO
-- REBUILD INDEXES TO RESOLVE INDEX FRAGMENTATION
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 90
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES
WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
BEGIN
-- SQL 2005 or higher command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)
END
ELSE
BEGIN
-- SQL 2000 command
DBCC DBREINDEX(@Table,' ',@fillfactor)
END
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
Notice, however, that TRUNCATE will remove ALL content from truncated table. If you want filtering you'll have to DELETE instead. That you may have to do in chunks in order to not bringing the SQL to its knees, but also to avoid making the transaction log explode.
Regards /Snedker
