January 13, 2009 at 3:14 am
I have some code (included at bottom) which basically does the following:
for all dbs in SQLSvr installation
if database != system/demo db
loop through all tables for this db
reindex table
next table
next for
The aim is to reindex each db on an installation every weekend when they are quiet. At present we have some hardcoded scripts which name each database to be reindexed but this seems better as it doesn't rely on maintaining lists of dbs.
However on a couple of occasions the script has bombed out somewhere in the inner loop. Whilst it's more stable now I'd like to be able to handle this if possible by
a) resuming the loop on the next db
b) if possible setting the db back to FULL recovery rather than BULK LOGGED
The first is more important as whilst FULL recdovery can be toggled on Monday morning, reindexing ant dbs that got missed isn't so easy.
Thanks
Full code:
declare @dbname varchar(255)
declare @tblname varchar(255)
declare @qry varchar(255)
use master
select @dbname = ' '
-- loop through all dbs
while @dbname is not null
begin
select @dbname = min(name)
from sys.databases
where name > @dbname
-- if it's not a system db (or AdventureWorks which causes errors)...
if @dbname not in ('AdventureWorksDW', 'master', 'model', 'msdb', 'tempdb')
begin
-- set bulk logging on
select @qry = 'alter database ' + @dbname + ' set recovery bulk_logged'
execute (@qry)
-- get all the table names in current db
select @qry = 'declare TblCursor cursor for select table_schema + ''.'' + table_name from ' + @dbname + '.information_schema.tables where table_type = ''BASE TABLE'''
execute (@qry)
-- reindex all tables for db
print '@@@@@@@@@@@@@@@@@@@@@@
print ' starting reindex of database ' + @dbname
print '@@@@@@@@@@@@@@@@@@@@@@
open TblCursor
-- reindex each table in this db
fetch next from TblCursor into @tblname
while @@fetch_status = 0
begin
select @tblname = @dbname + '.' + @tblname
print 'reindexing table ' + @tblname
dbcc dbreindex(@tblname)
print ' '
fetch next from TblCursor into @tblname
end
close TblCursor
deallocate TblCursor
-- finished all tables for db
print '@@@@@@@@@@@@@@@@@@@@@@
print ' finished reindex of database ' + @dbname
print '@@@@@@@@@@@@@@@@@@@@@@
print ' '
print ' '
---- -- set focus to current db and sort out stats
---- select @qry = 'use ' + @dbname
---- execute (@qry)
------ exec sp_createstats
------ exec sp_updatestats
-- revert to full recovery mode
select @qry = 'alter database ' + @dbname + ' set recovery full'
execute (@qry)
end
end
go
January 13, 2009 at 3:46 am
The best way to handle this is to create a maintainance plan with reindex/reorganize index task in it.
Look in Books Online for Maintainance Plans...
--Ramesh
January 13, 2009 at 5:32 am
Ideally I would like to implement the code as it fits in better with our infrastructure - specifically we can have one script which is version controlled and implemented automatically to all SQL Svr hosts. Our scheduling is not done in SQL Svr as it isn't the only dbms we use. Using Maintenance plans means creating one for each host (admittedly not a long task) and keeping them mastered on the db host.
I am however following your advice and investigating Maintenance plans. Do you know if it's possible to get more detailed information in the log than this (which was generated by selecting the extended logging option):
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3282
Report was generated on "giraffe".
Maintenance Plan: test_MaintenancePlan_mso
Duration: 00:04:26
Status: Succeeded.
Details:
Rebuild Index Task (giraffe)
Rebuild index on Local server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Object: Tables and views
Original amount of free space
Task start: 2009-01-13T12:05:30.
Task end: 2009-01-13T12:09:11.
Success
or whether it produces more information when it fails?
thanks
January 13, 2009 at 6:58 am
I'm not a fan of maintenance plans, not enough control.
Use the TRY/CATCH mechanism to trap the errors. As long the error doesn't cause a disconnect, you should be able to report it, log it, whatever, and then resume the loop.
This article [/url]has an introduction.
Also, you might want to search around in the scripts posted here. There are several good solutions already built that do what you're trying to do, selectively, based on index fragmentation. I posted a simple example[/url] (it's written for 2008, but I think it will work in 2005), but there are better ones.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 13, 2009 at 7:08 am
Hi
This is the script use - it reindexes everthing though - whether it needs it or not:
USE Master
DECLARE @dbName varchar(255)
DECLARE @SQL varchar(500)
DECLARE dbCursor CURSOR FOR
select [name] From master.sys.databases
WHERE owner_sid <> 0x01
And is_read_only = 0 and state_desc = 'ONLINE'
Order by [name]
OPEN dbCursor
FETCH NEXT FROM dbCursor INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'Use [' + @dbName + '] EXEC sp_MSforeachTable ''ALTER INDEX ALL ON ?REBUILD'' '
--Print @sql
EXEC (@sql)
FETCH NEXT FROM dbCursor INTO @dbName
END
CLOSE dbCursor
DEALLOCATE dbCursor
You could add the AND NAME NOT IN('adventureworks','etc') in the select for the cursor
Good Luck
Seth
January 13, 2009 at 7:27 am
m.s.owen (1/13/2009)
Ideally I would like to implement the code as it fits in better with our infrastructure - specifically we can have one script which is version controlled and implemented automatically to all SQL Svr hosts. Our scheduling is not done in SQL Svr as it isn't the only dbms we use. Using Maintenance plans means creating one for each host (admittedly not a long task) and keeping them mastered on the db host.
Yes, you have a better point to keep away from maintenance plans.
I am however following your advice and investigating Maintenance plans. Do you know if it's possible to get more detailed information in the log than this (which was generated by selecting the extended logging option):
This is all what the maintenance plan can provide.
or whether it produces more information when it fails?
The same error details that you get when you execute an erroneous t-sql statement.
So, in your case, I think its better off using the script instead of plans. And as Grant said, you can use TRY/CATCH construct to trap the errors and log it in table or wherever you want it be.
--Ramesh
January 13, 2009 at 9:12 am
Lookup DBCC SHOWCONTIG in Books Online. Example "E" is what you're looking for if you don't mind using cursors. I personally won't use a cursor (including the monster cursors in sp_MSForEachDB and sp_MSForEachTable), but it's perfectly acceptable to do such a thing for this type of code... it's not actually RBAR... the code is controlling a process.
--Jeff Moden
January 14, 2009 at 2:09 am
Was off yesterday afternoon but come back to some useful replies. I'm glad it's not just us doing things using code, I was starting to feel a little antiquated.
Thanks all for the suggestions, I'll work through them today (once I've cleared the pile of work that turned up in my absence) and post back if I have any further questions.
Thanks
January 14, 2009 at 2:32 am
To paraphrase what one of my younger developers recently said... "Dude! It's the code that seperates the men from the boys." 🙂
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply