SQL Server Performance Forum – Threads Archive
Rebuild Index
I have been using a modified version of isp_ALTER_INDEX written by Tara Kizer. I don#%92t have Enterprise and are not able to make online defragmentation. I don#%92t know if this is goodpractice I do. ALTER DATABASE ‘<database_name, sysname, your_database_name>’ SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE EXEC isp_ALTER_INDEX @dbName = ‘<database_name, sysname, your_database_name>’,
@statsMode = ‘SAMPLED’,
@defragType = ‘REBUILD’,
@minFragPercent = 1,
@maxFragPercent = 100,
@minRowCount = 1000 ALTER DATABASE ‘ + ”<database_name, sysname, your_database_name>” + ‘ SET MULTI_USER WITH ROLLBACK IMMEDIATE Is bad to put RESTRICTED_USER on databases when making weekly rebuilds?
In order to complete the REBUILD process quickly it is better to keep with RESTRICTED USER to avoid general connections.
quote:
When RESTRICTED_USER is specified, only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles are allowed to connect to the database, but it does not limit their number.
When RESTRICTED_USER is specified, only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles are allowed to connect to the database, but it does not limit their number.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
I don’t think you need use "WITH ROLLBACK IMMEDIATE " with the following statement… ALTER DATABASE ‘ + ”<database_name, sysname, your_database_name>” + ‘ SET MULTI_USER WITH ROLLBACK IMMEDIATE
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Better to use as :The rollback after statement will force currently executing statements to rollback after N seconds. The default is to wait for all currently running transactions to complete and for the sessions to be terminated. Use the rollback immediate clause to rollback transactions immediately. Using RESTRICTED_USER still you can allow Sysadmin users in thsi case and if any exclusive statements are running its better to control this way. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
I got the idea of IMMEDIATE ROLLBACK but why with MULTI_USER when you are changing the db to multi_user mode?
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
]]>