How to defrag an SQL 7 AND 2000 server?

Can someone tell me how to defrag. an MSSQL 7 AND MSSQL 2000 server. We
have both ver. 7 and 2000 (running on two different systems). I am not a
DBA but I really need to get this done tonight (2/24/2006) while we take our
systems down. I will also want to defrag the Windows 2000 and Windows 2003
NTSF file system volumes. Thanks for any and all input.
I will need the commands and where to run the commands (i.e. command prompt,
inside Enterprise Manager or inside Query Analyzer). I don't know anything
about SQL so go easy on me.
ClaytonHere is a quick but effective way to reindex all the tables in a specific db
which will defrag the tables and indexes. If you want to defrag the OS
files you would simply use one of the tools designed for that such as
DiskKeeper, Norton etc. You should make sure you have a good backup before
you start and turn off sql server before you do the OS defrag for best
results.
Andrew J. Kelly SQL MVP
"Clayton Sutton" <none@.none.com> wrote in message
news:OAnWhaVOGHA.3576@.TK2MSFTNGP15.phx.gbl...
> Can someone tell me how to defrag. an MSSQL 7 AND MSSQL 2000 server. We
> have both ver. 7 and 2000 (running on two different systems). I am not a
> DBA but I really need to get this done tonight (2/24/2006) while we take
> our systems down. I will also want to defrag the Windows 2000 and Windows
> 2003 NTSF file system volumes. Thanks for any and all input.
> I will need the commands and where to run the commands (i.e. command
> prompt, inside Enterprise Manager or inside Query Analyzer). I don't know
> anything about SQL so go easy on me.
>
> Clayton
>|||Hey Andrew,
Thanks for the reply. See inline comments.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uAxOe2WOGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Here is a quick but effective way to reindex all the tables in a specific
> db which will defrag the tables and indexes.
What? How? Did you forget to give me the info?

> If you want to defrag the OS files you would simply use one of the tools
> designed for that such as DiskKeeper, Norton etc.
I will just be using Windows built-in defrag utility

> You should make sure you have a good backup before you start and turn off
> sql server before you do the OS defrag for best results.
I will stop all SQL services first.
But what are the commands to defrag ALL of the DBs on the server? Are you
saying that if a defrag the NTFS volume that that will defrag ALL the DBs
too? Are there not SQL commands that I need to run to defrag ALL of the
DBs?
Clayton

> "Clayton Sutton" <none@.none.com> wrote in message
> news:OAnWhaVOGHA.3576@.TK2MSFTNGP15.phx.gbl...
>|||Sorry I forgot to do the paste, see below for the code:

> But what are the commands to defrag ALL of the DBs on the server? Are you
> saying that if a defrag the NTFS volume that that will defrag ALL the DBs
> too? Are there not SQL commands that I need to run to defrag ALL of the
> DBs?
Defragging at the OS level does nothing towards defragging the tables and
indexes. You would have to run the above script on each database.
SET NOCOUNT ON
DECLARE @.TableName VARCHAR(100)
DECLARE curTables CURSOR STATIC LOCAL
FOR
SELECT Table_Name
FROM Information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'
OPEN curTables
FETCH NEXT FROM curTables INTO @.TableName
SET @.TableName = RTRIM(@.TableName)
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT 'Reindexing ' + @.TableName
DBCC DBREINDEX (@.TableName)
FETCH NEXT FROM curTables INTO @.TableName
END
CLOSE curTables
DEALLOCATE curTables
Andrew J. Kelly SQL MVP
"Clayton Sutton" <none@.none.com> wrote in message
news:u9FgfOYOGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hey Andrew,
> Thanks for the reply. See inline comments.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uAxOe2WOGHA.1312@.TK2MSFTNGP09.phx.gbl...
> What? How? Did you forget to give me the info?
>
> I will just be using Windows built-in defrag utility
>
> I will stop all SQL services first.
> But what are the commands to defrag ALL of the DBs on the server? Are you
> saying that if a defrag the NTFS volume that that will defrag ALL the DBs
> too? Are there not SQL commands that I need to run to defrag ALL of the
> DBs?
>
> Clayton
>
>|||Hey Andrew,
Getting ready to go into work to get started (9:30pm CST). Just one more
question. How do I run the script? In a DOS Command Prompt? In a "Text"
file and call it "SomeFile.vbs"? Do I copy ad paste it into an MSSQL GUI
interface somewhere? Thanks for your help.
Clayton
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OBxAvZZOGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Sorry I forgot to do the paste, see below for the code:
>
> Defragging at the OS level does nothing towards defragging the tables and
> indexes. You would have to run the above script on each database.
> --
> SET NOCOUNT ON
> DECLARE @.TableName VARCHAR(100)
>
> DECLARE curTables CURSOR STATIC LOCAL
> FOR
> SELECT Table_Name
> FROM Information_Schema.Tables
> WHERE Table_Type = 'BASE TABLE'
> OPEN curTables
> FETCH NEXT FROM curTables INTO @.TableName
> SET @.TableName = RTRIM(@.TableName)
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT 'Reindexing ' + @.TableName
> DBCC DBREINDEX (@.TableName)
> FETCH NEXT FROM curTables INTO @.TableName
> END
> CLOSE curTables
> DEALLOCATE curTables
>
> --
> Andrew J. Kelly SQL MVP
>
> "Clayton Sutton" <none@.none.com> wrote in message
> news:u9FgfOYOGHA.1032@.TK2MSFTNGP11.phx.gbl...
>|||The easiest way is to use Query Analyzer and paste the code into the window
and hit the F5 key. Query Analyzer should be found under SQL Servers folder
of the programs menu.
Andrew J. Kelly SQL MVP
"Clayton Sutton" <none@.none.com> wrote in message
news:evR7iwbOGHA.2888@.tk2msftngp13.phx.gbl...
> Hey Andrew,
> Getting ready to go into work to get started (9:30pm CST). Just one more
> question. How do I run the script? In a DOS Command Prompt? In a "Text"
> file and call it "SomeFile.vbs"? Do I copy ad paste it into an MSSQL GUI
> interface somewhere? Thanks for your help.
>
> Clayton
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OBxAvZZOGHA.1288@.TK2MSFTNGP09.phx.gbl...
>|||Cool, thank you VERY much Andrew!
Clayton
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uGXTyDhOGHA.3284@.TK2MSFTNGP14.phx.gbl...
> The easiest way is to use Query Analyzer and paste the code into the
> window and hit the F5 key. Query Analyzer should be found under SQL
> Servers folder of the programs menu.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Clayton Sutton" <none@.none.com> wrote in message
> news:evR7iwbOGHA.2888@.tk2msftngp13.phx.gbl...
>