Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL REBUILD -- how to rebuild all sql databases?

Posted on 2011-05-08
19
Medium Priority
?
719 Views
Last Modified: 2012-05-11
I found a script to rebuild all databases and tables however the problem that I am having is that some of my databases and tables have a dash in them.  IE my_Table-test

Can you help to fix?  See error below:

Error:
Database= my_Table-test -- BEGIN
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 16916, Level 16, State 1, Line 27
A cursor with the name 'TableCursor' does not exist.
Msg 16916, Level 16, State 1, Line 29
A cursor with the name 'TableCursor' does not exist.
Msg 16916, Level 16, State 1, Line 43
A cursor with the name 'TableCursor' does not exist.
Msg 16916, Level 16, State 1, Line 44
A cursor with the name 'TableCursor' does not exist.
DECLARE @Database VARCHAR(255)   
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @print NVARCHAR(500)  
DECLARE @fillfactor INT 

SET @fillfactor = 90 

DECLARE DatabaseCursor CURSOR FOR  
SELECT name FROM master.dbo.sysdatabases   
WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')   
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   
       -- SQL 2000 command  
       --DBCC DBREINDEX(@Table,' ',@fillfactor)   
         
       -- SQL 2005 command  
       SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + 
               CONVERT(VARCHAR(3),@fillfactor) + ')'  
	   EXEC (@cmd)  

       FETCH NEXT FROM TableCursor INTO @Table   
   END   
   
   CLOSE TableCursor   
   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  
   print N'Database= ' + @Database + N' -- BEGIN'
END  
CLOSE DatabaseCursor   
DEALLOCATE DatabaseCursor

Open in new window

0
Comment
Question by:jdraggi
  • 6
  • 6
  • 2
  • +3
19 Comments
 
LVL 15

Expert Comment

by:Daniel_PL
ID: 35716276
Edit 10'th to 12'th line of your code to:
 
SELECT QUOTENAME(name) FROM master.dbo.sysdatabases   
WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')
ORDER BY 1

Open in new window

0
 
LVL 3

Author Comment

by:jdraggi
ID: 35716289
ok, that seems to have resolved the databse name issue but I still have the table issue where some of the tables have a dash "-" .

Database= my_Table-test -- BEGIN
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 35716407
You have to wrap the tablename and db name in square brackets []. A dash is just as bad as a space in SQL.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 3

Author Comment

by:jdraggi
ID: 35716411
How would you suggest that I do that?
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 35716420
I don't have line numbers on my  phone but here is where I see the change being needed:
ble_schema + ''.'' +
              table_name as tableName
Should be
ble_schema + ''.['' +
              table_name + "]" as tableName
0
 
LVL 81

Expert Comment

by:arnold
ID: 35716444
See if the rebuild/reindex referenced in http://msdn.microsoft.com/en-us/library/ms188917.aspx is a better fit for you.
0
 
LVL 3

Author Comment

by:jdraggi
ID: 35716487
aarontomosky, I tried to update the code but it didn't work.

arnold, I'm open whatever the most simple way would be to rebuild ALL databases and ALL tables for each database and set the fill factor to 90 while doing the rebuild.  All of our databases are using SQL 2005 and higher.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 732 total points
ID: 35717211
>>I found a script to rebuild all databases and tables however ... <<
And here is the big question, why are you rebuilding all the tables in all the databases.  Have you considered that this may be totally unecessary and that in many shops it is not even possible:  The window is too small to to them all.

Instead consider doing a rebuild or reorganize as needed, based on standard threshold levels.

Here is a script that does precisely that for you:
SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization - Documentation
http://ola.hallengren.com/Documentation.html
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35717692
DECLARE @Database VARCHAR(255)  
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @print NVARCHAR(500)  
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR  
SELECT name FROM master.dbo.sysdatabases  
WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')  
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  
      print N'Database= ' + @Database + N' Table= ' + @Table +  N' -- BEGIN'
       -- SQL 2000 command  
       --DBCC DBREINDEX(@Table,' ',@fillfactor)  
         
       -- SQL 2005 command  
       SET @cmd = 'ALTER INDEX ALL ON ' + SUBSTRING( 'DISHVIEW.dbo.Plates',0, LEN('DISHVIEW.dbo.Plates')-CharIndex('.',REVERSE('DISHVIEW.dbo.Plates'))+1) + RIGHT('DISHVIEW.dbo.Plates',CharIndex('.',REVERSE('DISHVIEW.dbo.Plates'))) + ' REBUILD WITH (FILLFACTOR = ' +
               CONVERT(VARCHAR(3),@fillfactor) + ')'  
       print 'Command ' + @cmd
           EXEC (@cmd)  
            
       FETCH NEXT FROM TableCursor INTO @Table  
   END  
   
   CLOSE TableCursor  
   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  
   print N'Database= ' + @Database + N' -- BEGIN'
END  
CLOSE DatabaseCursor  
DEALLOCATE DatabaseCursor
0
 
LVL 15

Expert Comment

by:Daniel_PL
ID: 35718047
Change lines 21 - 23 to:
 
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + 
              QUOTENAME(table_name) as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES 
              WHERE table_type = ''BASE TABLE'''

Open in new window

0
 
LVL 15

Assisted Solution

by:Daniel_PL
Daniel_PL earned 996 total points
ID: 35718059
In addition I also suggest (as acperkins does) to check Ola Hallengren maintenance scripts which I personally use:

Ola Hallengren: SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization
0
 
LVL 15

Expert Comment

by:Daniel_PL
ID: 35783446
Is your issue fixed?
0
 
LVL 3

Author Comment

by:jdraggi
ID: 35816188
Sorry for the delay, I have been busy with other issues.

REF: acperkins -- "And here is the big question, why are you rebuilding all the tables in all the databases" -- Yes, that was the goal because I was under the impression that this was the best way to completely optimize the database.  Is this not correct?

acperkins & Daniel_PL, do you believe that the ola.hallengren.com scripts will work better than the one that I found?  I'm simply looking for something that will properly rebuild & organize all of our databases on a set schedule.

Recently we ran into a problem where some queries where taking a few minutes vs near zero-seconds like they should have on the databases with a lot of data entry going on.
0
 
LVL 15

Assisted Solution

by:Daniel_PL
Daniel_PL earned 996 total points
ID: 35816272
I think yes, because using Ola's scripts you don't need to rebuild all indexes. You can reorganize them depending on fragmentation (in short by default 5-30% reorganize, >30% rebuild). They are easy to use and set up.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 732 total points
ID: 35819529
>>Yes, that was the goal because I was under the impression that this was the best way to completely optimize the database.  Is this not correct?<<
You should only rebuild/reorganize those tables that are fragmented.  The rest are a waste of time.

>>Recently we ran into a problem where some queries where taking a few minutes vs near zero-seconds like they should have on the databases with a lot of data entry going on. <<
You may find this has nothing to do with index fragmentation, but rather query optimization.
0
 
LVL 3

Author Comment

by:jdraggi
ID: 35916161
I finally had some time to work with these scripts ola.hallengren.com, they're very nice.

Couple questions:

How do I setup the script to automatically run each night?

Do you suggest that I update the statistics each time the scripts run using their execute command?

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLow = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

0
 
LVL 81

Assisted Solution

by:arnold
arnold earned 272 total points
ID: 35916353
You add this either as a subplan in a maintenance plan (T-SQL) which I tend to do as that is a way to setup jobs that are part/affecting the same set of resources/applications.

An alternative is to create the job manually.
Note when doing manually make sure not to use a login that could be disabled i.e. a person's login should not be used. use an account that will always be active.

http://msdn.microsoft.com/en-us/library/ms187910.aspx

reorganize/rebuild take the longest which would mean they will run on a monthly basis and the fragmentation level will control whether an index is reorganized or rebuilt. Update statistics can be run more frequently i.e. on a weekly basis.
0
 
LVL 15

Assisted Solution

by:Daniel_PL
Daniel_PL earned 996 total points
ID: 35916880
>How do I setup the script to automatically run each night?
Create job which will execute that sp for you.

>Do you suggest that I update the statistics each time the scripts run using their execute command?
Yes you can run them both in this procedure. Normally updating stats after index rebuild is not optimal because index rebuild also creates stats WITH FULLSCAN option.
I personally update stats in different job.
0
 
LVL 3

Author Closing Comment

by:jdraggi
ID: 35927604
Thanks for all of your help!  Works great!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question