| Groups | Blog | Home |
sql server new users : How to defrag an SQL 7 AND 2000 server?
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
Here 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 [quoted text, click to view] "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. [quoted text, click to view] "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? [quoted text, click to view] > 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 [quoted text, click to view] > 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 [quoted text, click to view] > > "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 >> > >
Sorry I forgot to do the paste, see below for the code:
[quoted text, click to view] > 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 [quoted text, click to view] "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... >> 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... >>> 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,
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 [quoted text, click to view] "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: > >> 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... >>> 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... >>>> 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 >>>> >>> >>> >> >> > >
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 [quoted text, click to view] "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... >> 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... >>>> 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... >>>>> 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 >>>>> >>>> >>>> >>> >>> >> >> > >
Don't see what you're looking for? Try a search.
|
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |