[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to REORG SQL 2000 Database

Posted on 2006-05-03
15
Medium Priority
?
2,410 Views
Last Modified: 2012-08-13
Hi,

How can I reorg a SQL 2000 database and what is the prerequisites for this to happen?.

ie: database user mode,online or offline?

Please help I am unable to find any information pertinent to my qeury.
0
Comment
Question by:johanvz1
  • 8
  • 7
15 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16594674
johanvz1,

>How can I reorg a SQL 2000 database and what is the prerequisites for this to happen?.
What exactly you are looking for


> database user mode
1. Offline
              When Offline, no one can access the database and is not currently used by the sql server.So you can copy the mdf and ldf files in this state.

2.read only
                When ReadOnly, users can only read data in the database, not modify it. The database cannot be in use when a new value for the read only option is specified. The master database is the exception, and only the system administrator can use master while the read only option is being set.

3. Online
              The database has to be online for any operations on the database. You wont be able to copy the mdf and the ldf files in this  state ...
0
 
LVL 4

Author Comment

by:johanvz1
ID: 16594691
I would like to know must the database be in single user mode to do reorg?. Must it be offline or can it be live an in an online state. And most importantly how do u do a SQL reorg?. Is a reorg the same as the following: DBCC REINDEX I am a beginner in SQL so I have no idea
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16594927
DBCC REINDEX = reorganisation

Periodically (weekly or monthly) perform a database reorganization on all the indexes on all the tables in your database. This will rebuild the indexes so that the data is no longer fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server's performance.

If you do a reorganization on a table with a clustered index, any non-clustered indexes on that same table will automatically be rebuilt.

Database reorganizations can be done scheduling SQLMAINT.EXE to run using the SQL Server Agent, or if by running your own custom script via the SQL Server Agent (see below).

Unfortunately, the DBCC DBREINDEX command will not automatically rebuild all of the indexes on all the tables in a database, it can only work on one table at a time. But if you run the following script, you can index all the tables in a database with ease.

When DBCC DBREINDEX is used to rebuild indexes, keep in mind that as the indexes on a table are being rebuilt, that the table becomes unavailable for use by your users. For example, when a non-clustered index is rebuilt, a shared table lock is put on the table, preventing all but SELECT operations to be performed on it. When a clustered index is rebuilt, an exclusive table lock is put on the table, preventing any table access by your users. Because of this, you should only run this command when users don't need access to the tables being reorganized.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Author Comment

by:johanvz1
ID: 16594988
what is the script that you mentioned?. And I have been fiddling with with sqlmaint.exe but cant get it to run must it be in a specific directory?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16595019
--Script to automatically reindex all tables in a database

USE DatabaseName --Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT "Reindexing " + @TableName
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16595051

It will be located under
C:\Program Files\Microsoft SQL Server\MSSQL\Binn


You can start the maintenance wizard from  Enterprise manager -> tools ->Database Maintenance Planner
0
 
LVL 4

Author Comment

by:johanvz1
ID: 16595124
And if I go via database maintenance planner can I drop this script in there somewhere?. And how long might it take on a 80gb database?. And must the server be in multi user mode or single user mode?.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16595397
johanvz1,
> And if I go via database maintenance planner can I drop this script in  there somewhere?

You can create a job to run the above script

> And how long might it take on a 80gb database?

This will depends on the no of indexes and data, I hope it will finish in less than 20 mts

> And must the server be in multi user mode or single user mode?
This is answered in my previous post, it will always better if you can run the server in single user mode.. Otherwise run this only when the least no of users are connected
0
 
LVL 4

Author Comment

by:johanvz1
ID: 16603252
But is this not just re-indexing or is re-indexing and reorging a database the same thing?.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16603262


DBCC REINDEX = reorganisation
0
 
LVL 4

Author Comment

by:johanvz1
ID: 16603278
I have a question will this suffice to put the database in single user mode:


EXEC sp_dboption @db, 'single user', 'true'

Or must I rather use this?.:


USE epwi
GO
DECLARE @db NVARCHAR(128)
SET @db = 'epwi'  --variable makes it easier to change db to work on

DECLARE @debug TINYINT
DECLARE @exec TINYINT
SET @debug = 1  --1 = print gen'd commands before running them; 0 = don't
SET @exec = 0   --1 = exec gen'd commands; 0 = don't
DECLARE @sql VARCHAR(4000)
DECLARE @error INT
--*
--* Kill all non-system tasks (other than this one) using the db.
--*
SET @sql = ''
SELECT @sql = @sql + ' KILL ' + CAST(spid AS VARCHAR(4)) + ' '
FROM master..sysprocesses
WHERE DB_NAME(dbid) = @db AND spid > 7 AND spid <> @@SPID --don't try to KILL myself!
IF @debug > 0
    PRINT 'Sql = ' + @sql
IF @exec > 0
    EXEC (@sql)
SELECT @error = @@ERROR
--*
--* Put db in single-user mode
--*
IF @debug > 0
    PRINT 'Single user command reached'
IF @exec > 0
    EXEC sp_dboption @db, 'single user', 'true'
0
 
LVL 4

Author Comment

by:johanvz1
ID: 16603284


The latter script I got of an msn article somewhere stating that it's a good way to kill all process and make sure the database is in single user mode before you actually start with the reorg. What is your take on this?. Thanks a lot for all your help thus far you are extremely helpful
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16603388
I think the problem with KILLing the process is that, it will termininate the process immediately. Sometimes you may kill some critical process. So better you run this query

DBCC OPENTRAN

This will return the open transactions,better you wait for sometime to commit them.  Then you can use either the first one or the second one.
0
 
LVL 4

Author Comment

by:johanvz1
ID: 16603427
Ok so after running DBCC OPENTRAN do you still think its necessary to use the seconc to kille the processes or would the process commit timeously after running the opentran query?.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 16603805
DBCC OPENTRAN -> will only list you the open transactions currently in the system. There will be so many other processes running on the System, that's why i asked you to use the second method. Hope You got the point
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 post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 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