March 26, 2014 at 11:05 pm
Comments posted to this topic are about the item Defragmentation and update statistics for all online databases
Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature
March 27, 2014 at 4:42 am
I am sure that this script does wonderful things, i ran it on a test server and on completion i looked at a blank screen. I'am still wandering if something has happened.
Hans Loykens
Middelburg, The Netherlands
March 27, 2014 at 1:33 pm
Another script that: a) doesn't filter out LOB_DATA or ROW_OVERFLOW_DATA allocation units b) runs over all indexes
Do yourself a favor and download Ola Hallengren's database maintenance solution and just use that: http://ola.hallengren.com/
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
April 15, 2014 at 5:43 am
Hi This Script will not give any output but will rebuild or reorganize indexes.
This Script is only for production environment and it will create a job, You have to run the job. For other environments Please run below listed codes(a,b).
It will update the statistics of these indexes. 🙂
a.
/* Query developed by Vimal Lohani For reorganizing and rebuilding indexes */
/* Query work for all Online Databases in one go */
/* Work for index name available other wise will say INDEX NAME NOT SET OR NULL */
/* Work for user define indexes */
/* Still work if any error occored with indexes */
USE MASTER
GO
SET NOCOUNT ON
If exists (select * from tempdb.sys.all_objects where name like ''#Databases'')
begindrop table #Databases end
SELECT ROW_NUMBER() OVER(ORDER BY name) Seq, name Banco
INTO #Databases
FROM sys.databases
WHERE name NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') --Not including system databases
and name NOT LIKE ''ReportServer%''
AND compatibility_level > 80 --For Database sql 2005 or higher
AND state=0 --fOR All online databases
ORDER BY Banco;
DECLARE
@Loop INT = 1,
@QT INT = (SELECT COUNT(1) FROM #Databases), --max no of databases
@Banco VARCHAR(50);
WHILE @Loop <= @QT
BEGIN
SET @Banco = (SELECT Banco FROM #Databases WHERE Seq = @Loop);
EXEC(
''USE '' + @Banco + ''; '' +
''PRINT ''''For Database : '''' + db_name();
Declare @dbid int;
set @dbid=DB_ID();
SELECT
ROW_NUMBER() OVER(ORDER BY p.object_id, p.index_id) Seq,
t.name Tabela, h.name Esquema,
i.name Indice, p.avg_fragmentation_in_percent Frag
INTO #Consulta
FROM
sys.dm_db_index_physical_stats(@dbid,null,null,null,null) p
join sys.indexes i on (p.object_id = i.object_id and p.index_id = i.index_id)
join sys.tables t on (p.object_id = t.object_id)
join sys.schemas h on (t.schema_id = h.schema_id)
join sys.objects o on (o.object_id=p.object_id)
where p.avg_fragmentation_in_percent > 5.0
and p.index_id >= 0 --all indexes including heap
and p.page_count >= 10
AND o.type=''''U''''
ORDER BY Esquema, Tabela;
DECLARE
@Loop INT = 1,
@Total INT = (SELECT COUNT(1) FROM #Consulta),
@Comando VARCHAR(500),
@Updato VARCHAR(500)
WHILE @Loop <= @Total
BEGIN
SELECT @Comando = ''''ALTER INDEX '''' + Indice +
'''' ON '''' + Esquema + ''''.'''' + Tabela +
( CASE WHEN Frag > 45.0 THEN '''' REBUILD'''' ELSE '''' REORGANIZE'''' END)
FROM #Consulta
WHERE Seq = @Loop;
EXEC(@Comando);
PRINT ''''Executed: '''' + ISNULL(@Comando,''''INDEX NAME NOT SET OR NULL'''');
SELECT @Updato=''''UPDATE STATISTICS ''''+ Esquema + ''''.'''' +Tabela + '''' ''''+Indice FROM #Consulta
WHERE Seq = @Loop;
EXEC(@Updato);
PRINT ''''UPDATE STATISTICS: ''''+ ISNULL(@Updato,''''INDEX NAME NOT SET OR NULL'''');
SET @Loop = @Loop + 1;
END;
PRINT DB_NAME() + '''' Total No of indexes defragmented : '''' + CONVERT(VARCHAR(5),@Total);
PRINT ''''------------------------------------------------------------------------------'''';
DROP TABLE #Consulta;'');
SET @Loop = @Loop + 1;
END;
DROP TABLE #Databases;
b.
DECLARE @SQL VARCHAR(1000)
DECLARE @DB sysname
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT [name]
FROM master..sysdatabases
WHERE [name] NOT IN (''model'', ''tempdb'')
ORDER BY [name]
OPEN curDB
FETCH NEXT FROM curDB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = ''USE ['' + @DB +'']'' + CHAR(13) + ''EXEC sp_updatestats'' + CHAR(13)
PRINT @SQL
exec(@sql)
Select @SQL= ''USE ['' + @DB +'']'' + CHAR(13) + ''alter database ['' + @DB +'']'' +'' set multi_user''+ CHAR(13)
PRINT @SQL
exec(@SQL)
print ''------------------------------------------------''
FETCH NEXT FROM curDB INTO @DB
END
CLOSE curDB
DEALLOCATE curDB
CLOSE curDB
DEALLOCATE curDB
Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature
May 25, 2016 at 6:24 am
This is an old post but must be good enough to repost. I'll pass this on to my DBA.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply