I have MSSQL 2005 Express installed locally. I've developed a database and
would like to copy it to my web host to be accessed using some ASP code.
Everything appears to be OK, except for one issue that I can't figure out.
HOW do I take my local database and upload it to my host?
I do have the MS SQL Server 2005 Express Manager installed and can connect
to both my local SQL 2005 server and my hosts SQL 2003 server.
How are databases normally developed for web applications?Hi,
"SQL 2003 server." There is no SQL 2003 Server. If your hoster doesn=B4t
offer you to restore backups you ade on your test system. You can
either use scripts changing your database which can be applied on the
server, or you can transfer objects to your hosters db. I am alqys
doing a whole *backup* (with transfering the database objects to my
local machine) doing changes and reapply them on the *productional*
server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||You can backup local database and restore on the host database.
If you developed database file using Visual Studio, you can attach database
on the host.
And can modifiy connection string.
"Noozer"?? ??? ??:
> I have MSSQL 2005 Express installed locally. I've developed a database and
> would like to copy it to my web host to be accessed using some ASP code.
> Everything appears to be OK, except for one issue that I can't figure out.
> HOW do I take my local database and upload it to my host?
> I do have the MS SQL Server 2005 Express Manager installed and can connect
> to both my local SQL 2005 server and my hosts SQL 2003 server.
> How are databases normally developed for web applications?
>
>
Showing posts with label mssql. Show all posts
Showing posts with label mssql. Show all posts
Friday, March 30, 2012
How to develop database locally and post to web host?
I have MSSQL 2005 Express installed locally. I've developed a database and
would like to copy it to my web host to be accessed using some ASP code.
Everything appears to be OK, except for one issue that I can't figure out.
HOW do I take my local database and upload it to my host?
I do have the MS SQL Server 2005 Express Manager installed and can connect
to both my local SQL 2005 server and my hosts SQL 2003 server.
How are databases normally developed for web applications?
Hi,
"SQL 2003 server." There is no SQL 2003 Server. If your hoster doesn=B4t
offer you to restore backups you ade on your test system. You can
either use scripts changing your database which can be applied on the
server, or you can transfer objects to your hosters db. I am alqys
doing a whole *backup* (with transfering the database objects to my
local machine) doing changes and reapply them on the *productional*
server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||You can backup local database and restore on the host database.
If you developed database file using Visual Studio, you can attach database
on the host.
And can modifiy connection string.
"Noozer"?? ??? ??:
> I have MSSQL 2005 Express installed locally. I've developed a database and
> would like to copy it to my web host to be accessed using some ASP code.
> Everything appears to be OK, except for one issue that I can't figure out.
> HOW do I take my local database and upload it to my host?
> I do have the MS SQL Server 2005 Express Manager installed and can connect
> to both my local SQL 2005 server and my hosts SQL 2003 server.
> How are databases normally developed for web applications?
>
>
would like to copy it to my web host to be accessed using some ASP code.
Everything appears to be OK, except for one issue that I can't figure out.
HOW do I take my local database and upload it to my host?
I do have the MS SQL Server 2005 Express Manager installed and can connect
to both my local SQL 2005 server and my hosts SQL 2003 server.
How are databases normally developed for web applications?
Hi,
"SQL 2003 server." There is no SQL 2003 Server. If your hoster doesn=B4t
offer you to restore backups you ade on your test system. You can
either use scripts changing your database which can be applied on the
server, or you can transfer objects to your hosters db. I am alqys
doing a whole *backup* (with transfering the database objects to my
local machine) doing changes and reapply them on the *productional*
server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||You can backup local database and restore on the host database.
If you developed database file using Visual Studio, you can attach database
on the host.
And can modifiy connection string.
"Noozer"?? ??? ??:
> I have MSSQL 2005 Express installed locally. I've developed a database and
> would like to copy it to my web host to be accessed using some ASP code.
> Everything appears to be OK, except for one issue that I can't figure out.
> HOW do I take my local database and upload it to my host?
> I do have the MS SQL Server 2005 Express Manager installed and can connect
> to both my local SQL 2005 server and my hosts SQL 2003 server.
> How are databases normally developed for web applications?
>
>
Wednesday, March 7, 2012
How to delete row 169441?
MSSQL database table DATE field has corruption. The error message says it is at row 169441. I cannot finish an export, browse to the record or delete it. I got the bad record below by exporting to a text file and this was the last record before it stopped.
This is the bad record. 2nd field is DATE
"0000000004015 ",15955-04-30 103:21:55.207000000,"
This is a good record immediately preceding the bad one.
"0000000004015 ",2006-01-22 00:00:00,"Can you select that row with this where clause?
where date > '1/1/2050'|||No. where date > '1/1/2050' returns 'invalid date data'|||If there is no "key" on this table you could try:
Backup your database
Set RowCount 169440 -- Process only first nnnn rows.
Select * Into #TMP_Table from YourTable
Set RowCount 169441 -- Process only first nnnn rows.
Delete From YourTable -- Should delete all rows copied plus the bad one.
Set RowCount 0 -- Process all rows
Insert Into @.TMP_Table Select * From YourTable -- Copy the rest
This is a big "??". The "Select"s and "Delete"s may not process all rows in the same order, but they probably will. If nothing else works it is worth a try. Just make sure that you can restore from your backup!
OR
You could try some of the undocumented DBCC commands to force the data.
Good luck.|||This is the bad record. 2nd field is DATE
"0000000004015 ",15955-04-30 103:21:55.207000000,"
This is a good record immediately preceding the bad one.
"0000000004015 ",2006-01-22 00:00:00,"
You have commas at the beginning and end of your date? You have three double-quotes delimiting two values? You have trailing spaces after your first field? Is your date a string?
We can't diagnose the problem like this.
Please take the time to post correct data with a complete and careful description if you want correct answers.
This is the bad record. 2nd field is DATE
"0000000004015 ",15955-04-30 103:21:55.207000000,"
This is a good record immediately preceding the bad one.
"0000000004015 ",2006-01-22 00:00:00,"Can you select that row with this where clause?
where date > '1/1/2050'|||No. where date > '1/1/2050' returns 'invalid date data'|||If there is no "key" on this table you could try:
Backup your database
Set RowCount 169440 -- Process only first nnnn rows.
Select * Into #TMP_Table from YourTable
Set RowCount 169441 -- Process only first nnnn rows.
Delete From YourTable -- Should delete all rows copied plus the bad one.
Set RowCount 0 -- Process all rows
Insert Into @.TMP_Table Select * From YourTable -- Copy the rest
This is a big "??". The "Select"s and "Delete"s may not process all rows in the same order, but they probably will. If nothing else works it is worth a try. Just make sure that you can restore from your backup!
OR
You could try some of the undocumented DBCC commands to force the data.
Good luck.|||This is the bad record. 2nd field is DATE
"0000000004015 ",15955-04-30 103:21:55.207000000,"
This is a good record immediately preceding the bad one.
"0000000004015 ",2006-01-22 00:00:00,"
You have commas at the beginning and end of your date? You have three double-quotes delimiting two values? You have trailing spaces after your first field? Is your date a string?
We can't diagnose the problem like this.
Please take the time to post correct data with a complete and careful description if you want correct answers.
How to delete large number of record without matter with transaction log?
Hello,
It happens to me that I have control over a MSSQL 2000 server that was
looked after by another staff today. And than there is a call from the
server's owner company that the server's harddisk is running out of space.
As background information, the SQL server's running on a 5GB HDD
partition with the Win2k system dir on it. And after some inspection, I
found the "msdb" database occupies about 1.13GB and it's transaction log
occupies 2MB.
So I opened Enterprise Manager and looked into the tables one by one.
And found the 'sysdtssteplog' table has almost 7 million rows. So I run
"delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
Analyzer"(I've explicitly called it to run the SQL statement) tell me it
cannot finish the task because the disk is running out of space and no room
for transaction log. And when I see the file size, the transaction log file
of msdb has grown to over 200MB. Oops.
At least, I managed to found an unoccupied machine to install a temp SQL
server, copy the database file to, trancate, and put back to the origional
server. And the story is over. But what should I do if I experienced that
next time? There's management change in both my company and that company so
replacement of harddisk may not be feasible in a short time. And I'd like to
know if there's anything wrong in my procedure of handling the issue. This
is my first time to handle a SQL server, and I have tough time on this. I
used to be a programmer only.
Looking forward for any advice. Thanks a lot.
Regards,
Lau Lei Cheong
Vyas's example shows how to divide a "big" transaction into a small ones
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
/*
DELETION here and Don't forget WHERE condition
*/
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT --Or doing BACKUP LOG File
END
END
SET ROWCOUNT 0
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> It happens to me that I have control over a MSSQL 2000 server that was
> looked after by another staff today. And than there is a call from the
> server's owner company that the server's harddisk is running out of space.
> As background information, the SQL server's running on a 5GB HDD
> partition with the Win2k system dir on it. And after some inspection, I
> found the "msdb" database occupies about 1.13GB and it's transaction log
> occupies 2MB.
> So I opened Enterprise Manager and looked into the tables one by one.
> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
> cannot finish the task because the disk is running out of space and no
room
> for transaction log. And when I see the file size, the transaction log
file
> of msdb has grown to over 200MB. Oops.
> At least, I managed to found an unoccupied machine to install a temp
SQL
> server, copy the database file to, trancate, and put back to the origional
> server. And the story is over. But what should I do if I experienced that
> next time? There's management change in both my company and that company
so
> replacement of harddisk may not be feasible in a short time. And I'd like
to
> know if there's anything wrong in my procedure of handling the issue. This
> is my first time to handle a SQL server, and I have tough time on this. I
> used to be a programmer only.
> Looking forward for any advice. Thanks a lot.
> Regards,
> Lau Lei Cheong
>
>
|||Hello Uri,
Thanks for your response.
One further question, why does the code use "WHILE 1 = 1" instead of
anything like "WHILE (TRUE)"? Is there any reason behind?
Regards,
Lau Lei Cheong
"Uri Dimant" <urid@.iscar.co.il> glsD:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx .gbl...
> Vyas's example shows how to divide a "big" transaction into a small ones
> SET ROWCOUNT 1000
> WHILE 1 = 1
> BEGIN
> /*
> DELETION here and Don't forget WHERE condition
> */
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT --Or doing BACKUP LOG File
> END
> END
> SET ROWCOUNT 0
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> room
> file
> SQL
> so
> to
>
|||1=1 is TRUE condition but if @.@.rowcount=0 we exit from the loop.
You can build your own logic to fetch the rows
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:e8UVyiQjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hello Uri,
> Thanks for your response.
> One further question, why does the code use "WHILE 1 = 1" instead of
> anything like "WHILE (TRUE)"? Is there any reason behind?
> Regards,
> Lau Lei Cheong
> "Uri Dimant" <urid@.iscar.co.il>
glsD:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx .gbl...[vbcol=seagreen]
log[vbcol=seagreen]
one.[vbcol=seagreen]
"Query[vbcol=seagreen]
it[vbcol=seagreen]
temp[vbcol=seagreen]
that[vbcol=seagreen]
company[vbcol=seagreen]
like[vbcol=seagreen]
I
>
It happens to me that I have control over a MSSQL 2000 server that was
looked after by another staff today. And than there is a call from the
server's owner company that the server's harddisk is running out of space.
As background information, the SQL server's running on a 5GB HDD
partition with the Win2k system dir on it. And after some inspection, I
found the "msdb" database occupies about 1.13GB and it's transaction log
occupies 2MB.
So I opened Enterprise Manager and looked into the tables one by one.
And found the 'sysdtssteplog' table has almost 7 million rows. So I run
"delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
Analyzer"(I've explicitly called it to run the SQL statement) tell me it
cannot finish the task because the disk is running out of space and no room
for transaction log. And when I see the file size, the transaction log file
of msdb has grown to over 200MB. Oops.
At least, I managed to found an unoccupied machine to install a temp SQL
server, copy the database file to, trancate, and put back to the origional
server. And the story is over. But what should I do if I experienced that
next time? There's management change in both my company and that company so
replacement of harddisk may not be feasible in a short time. And I'd like to
know if there's anything wrong in my procedure of handling the issue. This
is my first time to handle a SQL server, and I have tough time on this. I
used to be a programmer only.
Looking forward for any advice. Thanks a lot.
Regards,
Lau Lei Cheong
Vyas's example shows how to divide a "big" transaction into a small ones
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
/*
DELETION here and Don't forget WHERE condition
*/
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT --Or doing BACKUP LOG File
END
END
SET ROWCOUNT 0
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> It happens to me that I have control over a MSSQL 2000 server that was
> looked after by another staff today. And than there is a call from the
> server's owner company that the server's harddisk is running out of space.
> As background information, the SQL server's running on a 5GB HDD
> partition with the Win2k system dir on it. And after some inspection, I
> found the "msdb" database occupies about 1.13GB and it's transaction log
> occupies 2MB.
> So I opened Enterprise Manager and looked into the tables one by one.
> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
> cannot finish the task because the disk is running out of space and no
room
> for transaction log. And when I see the file size, the transaction log
file
> of msdb has grown to over 200MB. Oops.
> At least, I managed to found an unoccupied machine to install a temp
SQL
> server, copy the database file to, trancate, and put back to the origional
> server. And the story is over. But what should I do if I experienced that
> next time? There's management change in both my company and that company
so
> replacement of harddisk may not be feasible in a short time. And I'd like
to
> know if there's anything wrong in my procedure of handling the issue. This
> is my first time to handle a SQL server, and I have tough time on this. I
> used to be a programmer only.
> Looking forward for any advice. Thanks a lot.
> Regards,
> Lau Lei Cheong
>
>
|||Hello Uri,
Thanks for your response.
One further question, why does the code use "WHILE 1 = 1" instead of
anything like "WHILE (TRUE)"? Is there any reason behind?
Regards,
Lau Lei Cheong
"Uri Dimant" <urid@.iscar.co.il> glsD:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx .gbl...
> Vyas's example shows how to divide a "big" transaction into a small ones
> SET ROWCOUNT 1000
> WHILE 1 = 1
> BEGIN
> /*
> DELETION here and Don't forget WHERE condition
> */
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT --Or doing BACKUP LOG File
> END
> END
> SET ROWCOUNT 0
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> room
> file
> SQL
> so
> to
>
|||1=1 is TRUE condition but if @.@.rowcount=0 we exit from the loop.
You can build your own logic to fetch the rows
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:e8UVyiQjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hello Uri,
> Thanks for your response.
> One further question, why does the code use "WHILE 1 = 1" instead of
> anything like "WHILE (TRUE)"? Is there any reason behind?
> Regards,
> Lau Lei Cheong
> "Uri Dimant" <urid@.iscar.co.il>
glsD:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx .gbl...[vbcol=seagreen]
log[vbcol=seagreen]
one.[vbcol=seagreen]
"Query[vbcol=seagreen]
it[vbcol=seagreen]
temp[vbcol=seagreen]
that[vbcol=seagreen]
company[vbcol=seagreen]
like[vbcol=seagreen]
I
>
How to delete large number of record without matter with transaction log?
Hello,
It happens to me that I have control over a MSSQL 2000 server that was
looked after by another staff today. And than there is a call from the
server's owner company that the server's harddisk is running out of space.
As background information, the SQL server's running on a 5GB HDD
partition with the Win2k system dir on it. And after some inspection, I
found the "msdb" database occupies about 1.13GB and it's transaction log
occupies 2MB.
So I opened Enterprise Manager and looked into the tables one by one.
And found the 'sysdtssteplog' table has almost 7 million rows. So I run
"delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
Analyzer"(I've explicitly called it to run the SQL statement) tell me it
cannot finish the task because the disk is running out of space and no room
for transaction log. And when I see the file size, the transaction log file
of msdb has grown to over 200MB. Oops.
At least, I managed to found an unoccupied machine to install a temp SQL
server, copy the database file to, trancate, and put back to the origional
server. And the story is over. But what should I do if I experienced that
next time? There's management change in both my company and that company so
replacement of harddisk may not be feasible in a short time. And I'd like to
know if there's anything wrong in my procedure of handling the issue. This
is my first time to handle a SQL server, and I have tough time on this. I
used to be a programmer only.
Looking forward for any advice. Thanks a lot.
Regards,
Lau Lei CheongVyas's example shows how to divide a "big" transaction into a small ones
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
/*
DELETION here and Don't forget WHERE condition
*/
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT --Or doing BACKUP LOG File
END
END
SET ROWCOUNT 0
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> It happens to me that I have control over a MSSQL 2000 server that was
> looked after by another staff today. And than there is a call from the
> server's owner company that the server's harddisk is running out of space.
> As background information, the SQL server's running on a 5GB HDD
> partition with the Win2k system dir on it. And after some inspection, I
> found the "msdb" database occupies about 1.13GB and it's transaction log
> occupies 2MB.
> So I opened Enterprise Manager and looked into the tables one by one.
> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
> cannot finish the task because the disk is running out of space and no
room
> for transaction log. And when I see the file size, the transaction log
file
> of msdb has grown to over 200MB. Oops.
> At least, I managed to found an unoccupied machine to install a temp
SQL
> server, copy the database file to, trancate, and put back to the origional
> server. And the story is over. But what should I do if I experienced that
> next time? There's management change in both my company and that company
so
> replacement of harddisk may not be feasible in a short time. And I'd like
to
> know if there's anything wrong in my procedure of handling the issue. This
> is my first time to handle a SQL server, and I have tough time on this. I
> used to be a programmer only.
> Looking forward for any advice. Thanks a lot.
> Regards,
> Lau Lei Cheong
>
>|||Hello Uri,
Thanks for your response.
One further question, why does the code use "WHILE 1 = 1" instead of
anything like "WHILE (TRUE)"? Is there any reason behind?
Regards,
Lau Lei Cheong
"Uri Dimant" <urid@.iscar.co.il> glsD:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...[vb
col=seagreen]
> Vyas's example shows how to divide a "big" transaction into a small ones
> SET ROWCOUNT 1000
> WHILE 1 = 1
> BEGIN
> /*
> DELETION here and Don't forget WHERE condition
> */
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT --Or doing BACKUP LOG File
> END
> END
> SET ROWCOUNT 0
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> room
> file
> SQL
> so
> to
>[/vbcol]|||1=1 is TRUE condition but if @.@.rowcount=0 we exit from the loop.
You can build your own logic to fetch the rows
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:e8UVyiQjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hello Uri,
> Thanks for your response.
> One further question, why does the code use "WHILE 1 = 1" instead of
> anything like "WHILE (TRUE)"? Is there any reason behind?
> Regards,
> Lau Lei Cheong
> "Uri Dimant" <urid@.iscar.co.il>
glsD:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...
log[vbcol=seagreen]
one.[vbcol=seagreen]
"Query[vbcol=seagreen]
it[vbcol=seagreen]
temp[vbcol=seagreen]
that[vbcol=seagreen]
company[vbcol=seagreen]
like[vbcol=seagreen]
I[vbcol=seagreen]
>
It happens to me that I have control over a MSSQL 2000 server that was
looked after by another staff today. And than there is a call from the
server's owner company that the server's harddisk is running out of space.
As background information, the SQL server's running on a 5GB HDD
partition with the Win2k system dir on it. And after some inspection, I
found the "msdb" database occupies about 1.13GB and it's transaction log
occupies 2MB.
So I opened Enterprise Manager and looked into the tables one by one.
And found the 'sysdtssteplog' table has almost 7 million rows. So I run
"delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
Analyzer"(I've explicitly called it to run the SQL statement) tell me it
cannot finish the task because the disk is running out of space and no room
for transaction log. And when I see the file size, the transaction log file
of msdb has grown to over 200MB. Oops.
At least, I managed to found an unoccupied machine to install a temp SQL
server, copy the database file to, trancate, and put back to the origional
server. And the story is over. But what should I do if I experienced that
next time? There's management change in both my company and that company so
replacement of harddisk may not be feasible in a short time. And I'd like to
know if there's anything wrong in my procedure of handling the issue. This
is my first time to handle a SQL server, and I have tough time on this. I
used to be a programmer only.
Looking forward for any advice. Thanks a lot.
Regards,
Lau Lei CheongVyas's example shows how to divide a "big" transaction into a small ones
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
/*
DELETION here and Don't forget WHERE condition
*/
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT --Or doing BACKUP LOG File
END
END
SET ROWCOUNT 0
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> It happens to me that I have control over a MSSQL 2000 server that was
> looked after by another staff today. And than there is a call from the
> server's owner company that the server's harddisk is running out of space.
> As background information, the SQL server's running on a 5GB HDD
> partition with the Win2k system dir on it. And after some inspection, I
> found the "msdb" database occupies about 1.13GB and it's transaction log
> occupies 2MB.
> So I opened Enterprise Manager and looked into the tables one by one.
> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
> cannot finish the task because the disk is running out of space and no
room
> for transaction log. And when I see the file size, the transaction log
file
> of msdb has grown to over 200MB. Oops.
> At least, I managed to found an unoccupied machine to install a temp
SQL
> server, copy the database file to, trancate, and put back to the origional
> server. And the story is over. But what should I do if I experienced that
> next time? There's management change in both my company and that company
so
> replacement of harddisk may not be feasible in a short time. And I'd like
to
> know if there's anything wrong in my procedure of handling the issue. This
> is my first time to handle a SQL server, and I have tough time on this. I
> used to be a programmer only.
> Looking forward for any advice. Thanks a lot.
> Regards,
> Lau Lei Cheong
>
>|||Hello Uri,
Thanks for your response.
One further question, why does the code use "WHILE 1 = 1" instead of
anything like "WHILE (TRUE)"? Is there any reason behind?
Regards,
Lau Lei Cheong
"Uri Dimant" <urid@.iscar.co.il> glsD:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...[vb
col=seagreen]
> Vyas's example shows how to divide a "big" transaction into a small ones
> SET ROWCOUNT 1000
> WHILE 1 = 1
> BEGIN
> /*
> DELETION here and Don't forget WHERE condition
> */
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT --Or doing BACKUP LOG File
> END
> END
> SET ROWCOUNT 0
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> room
> file
> SQL
> so
> to
>[/vbcol]|||1=1 is TRUE condition but if @.@.rowcount=0 we exit from the loop.
You can build your own logic to fetch the rows
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:e8UVyiQjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hello Uri,
> Thanks for your response.
> One further question, why does the code use "WHILE 1 = 1" instead of
> anything like "WHILE (TRUE)"? Is there any reason behind?
> Regards,
> Lau Lei Cheong
> "Uri Dimant" <urid@.iscar.co.il>
glsD:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...
log[vbcol=seagreen]
one.[vbcol=seagreen]
"Query[vbcol=seagreen]
it[vbcol=seagreen]
temp[vbcol=seagreen]
that[vbcol=seagreen]
company[vbcol=seagreen]
like[vbcol=seagreen]
I[vbcol=seagreen]
>
How to delete large number of record without matter with transaction log?
Hello,
It happens to me that I have control over a MSSQL 2000 server that was
looked after by another staff today. And than there is a call from the
server's owner company that the server's harddisk is running out of space.
As background information, the SQL server's running on a 5GB HDD
partition with the Win2k system dir on it. And after some inspection, I
found the "msdb" database occupies about 1.13GB and it's transaction log
occupies 2MB.
So I opened Enterprise Manager and looked into the tables one by one.
And found the 'sysdtssteplog' table has almost 7 million rows. So I run
"delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
Analyzer"(I've explicitly called it to run the SQL statement) tell me it
cannot finish the task because the disk is running out of space and no room
for transaction log. And when I see the file size, the transaction log file
of msdb has grown to over 200MB. Oops.
At least, I managed to found an unoccupied machine to install a temp SQL
server, copy the database file to, trancate, and put back to the origional
server. And the story is over. But what should I do if I experienced that
next time? There's management change in both my company and that company so
replacement of harddisk may not be feasible in a short time. And I'd like to
know if there's anything wrong in my procedure of handling the issue. This
is my first time to handle a SQL server, and I have tough time on this. I
used to be a programmer only.
Looking forward for any advice. Thanks a lot.
Regards,
Lau Lei CheongVyas's example shows how to divide a "big" transaction into a small ones
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
/*
DELETION here and Don't forget WHERE condition
*/
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT --Or doing BACKUP LOG File
END
END
SET ROWCOUNT 0
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> It happens to me that I have control over a MSSQL 2000 server that was
> looked after by another staff today. And than there is a call from the
> server's owner company that the server's harddisk is running out of space.
> As background information, the SQL server's running on a 5GB HDD
> partition with the Win2k system dir on it. And after some inspection, I
> found the "msdb" database occupies about 1.13GB and it's transaction log
> occupies 2MB.
> So I opened Enterprise Manager and looked into the tables one by one.
> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
> cannot finish the task because the disk is running out of space and no
room
> for transaction log. And when I see the file size, the transaction log
file
> of msdb has grown to over 200MB. Oops.
> At least, I managed to found an unoccupied machine to install a temp
SQL
> server, copy the database file to, trancate, and put back to the origional
> server. And the story is over. But what should I do if I experienced that
> next time? There's management change in both my company and that company
so
> replacement of harddisk may not be feasible in a short time. And I'd like
to
> know if there's anything wrong in my procedure of handling the issue. This
> is my first time to handle a SQL server, and I have tough time on this. I
> used to be a programmer only.
> Looking forward for any advice. Thanks a lot.
> Regards,
> Lau Lei Cheong
>
>|||Hello Uri,
Thanks for your response.
One further question, why does the code use "WHILE 1 = 1" instead of
anything like "WHILE (TRUE)"? Is there any reason behind?
Regards,
Lau Lei Cheong
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...
> Vyas's example shows how to divide a "big" transaction into a small ones
> SET ROWCOUNT 1000
> WHILE 1 = 1
> BEGIN
> /*
> DELETION here and Don't forget WHERE condition
> */
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT --Or doing BACKUP LOG File
> END
> END
> SET ROWCOUNT 0
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> It happens to me that I have control over a MSSQL 2000 server that
>> was
>> looked after by another staff today. And than there is a call from the
>> server's owner company that the server's harddisk is running out of
>> space.
>> As background information, the SQL server's running on a 5GB HDD
>> partition with the Win2k system dir on it. And after some inspection, I
>> found the "msdb" database occupies about 1.13GB and it's transaction log
>> occupies 2MB.
>> So I opened Enterprise Manager and looked into the tables one by one.
>> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
>> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
>> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
>> cannot finish the task because the disk is running out of space and no
> room
>> for transaction log. And when I see the file size, the transaction log
> file
>> of msdb has grown to over 200MB. Oops.
>> At least, I managed to found an unoccupied machine to install a temp
> SQL
>> server, copy the database file to, trancate, and put back to the
>> origional
>> server. And the story is over. But what should I do if I experienced that
>> next time? There's management change in both my company and that company
> so
>> replacement of harddisk may not be feasible in a short time. And I'd like
> to
>> know if there's anything wrong in my procedure of handling the issue.
>> This
>> is my first time to handle a SQL server, and I have tough time on this. I
>> used to be a programmer only.
>> Looking forward for any advice. Thanks a lot.
>> Regards,
>> Lau Lei Cheong
>>
>|||1=1 is TRUE condition but if @.@.rowcount=0 we exit from the loop.
You can build your own logic to fetch the rows
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:e8UVyiQjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hello Uri,
> Thanks for your response.
> One further question, why does the code use "WHILE 1 = 1" instead of
> anything like "WHILE (TRUE)"? Is there any reason behind?
> Regards,
> Lau Lei Cheong
> "Uri Dimant" <urid@.iscar.co.il>
¼¶¼g©ó¶l¥ó·s»D:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...
> > Vyas's example shows how to divide a "big" transaction into a small ones
> >
> > SET ROWCOUNT 1000
> > WHILE 1 = 1
> > BEGIN
> > /*
> > DELETION here and Don't forget WHERE condition
> > */
> >
> > IF @.@.ROWCOUNT = 0
> > BEGIN
> > BREAK
> > END
> > ELSE
> > BEGIN
> > CHECKPOINT --Or doing BACKUP LOG File
> > END
> > END
> >
> > SET ROWCOUNT 0
> > "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> > news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> >> Hello,
> >>
> >> It happens to me that I have control over a MSSQL 2000 server that
> >> was
> >> looked after by another staff today. And than there is a call from the
> >> server's owner company that the server's harddisk is running out of
> >> space.
> >>
> >> As background information, the SQL server's running on a 5GB HDD
> >> partition with the Win2k system dir on it. And after some inspection, I
> >> found the "msdb" database occupies about 1.13GB and it's transaction
log
> >> occupies 2MB.
> >>
> >> So I opened Enterprise Manager and looked into the tables one by
one.
> >> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> >> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the
"Query
> >> Analyzer"(I've explicitly called it to run the SQL statement) tell me
it
> >> cannot finish the task because the disk is running out of space and no
> > room
> >> for transaction log. And when I see the file size, the transaction log
> > file
> >> of msdb has grown to over 200MB. Oops.
> >>
> >> At least, I managed to found an unoccupied machine to install a
temp
> > SQL
> >> server, copy the database file to, trancate, and put back to the
> >> origional
> >> server. And the story is over. But what should I do if I experienced
that
> >> next time? There's management change in both my company and that
company
> > so
> >> replacement of harddisk may not be feasible in a short time. And I'd
like
> > to
> >> know if there's anything wrong in my procedure of handling the issue.
> >> This
> >> is my first time to handle a SQL server, and I have tough time on this.
I
> >> used to be a programmer only.
> >>
> >> Looking forward for any advice. Thanks a lot.
> >>
> >> Regards,
> >> Lau Lei Cheong
> >>
> >>
> >>
> >
> >
>
It happens to me that I have control over a MSSQL 2000 server that was
looked after by another staff today. And than there is a call from the
server's owner company that the server's harddisk is running out of space.
As background information, the SQL server's running on a 5GB HDD
partition with the Win2k system dir on it. And after some inspection, I
found the "msdb" database occupies about 1.13GB and it's transaction log
occupies 2MB.
So I opened Enterprise Manager and looked into the tables one by one.
And found the 'sysdtssteplog' table has almost 7 million rows. So I run
"delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
Analyzer"(I've explicitly called it to run the SQL statement) tell me it
cannot finish the task because the disk is running out of space and no room
for transaction log. And when I see the file size, the transaction log file
of msdb has grown to over 200MB. Oops.
At least, I managed to found an unoccupied machine to install a temp SQL
server, copy the database file to, trancate, and put back to the origional
server. And the story is over. But what should I do if I experienced that
next time? There's management change in both my company and that company so
replacement of harddisk may not be feasible in a short time. And I'd like to
know if there's anything wrong in my procedure of handling the issue. This
is my first time to handle a SQL server, and I have tough time on this. I
used to be a programmer only.
Looking forward for any advice. Thanks a lot.
Regards,
Lau Lei CheongVyas's example shows how to divide a "big" transaction into a small ones
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
/*
DELETION here and Don't forget WHERE condition
*/
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT --Or doing BACKUP LOG File
END
END
SET ROWCOUNT 0
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> It happens to me that I have control over a MSSQL 2000 server that was
> looked after by another staff today. And than there is a call from the
> server's owner company that the server's harddisk is running out of space.
> As background information, the SQL server's running on a 5GB HDD
> partition with the Win2k system dir on it. And after some inspection, I
> found the "msdb" database occupies about 1.13GB and it's transaction log
> occupies 2MB.
> So I opened Enterprise Manager and looked into the tables one by one.
> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
> cannot finish the task because the disk is running out of space and no
room
> for transaction log. And when I see the file size, the transaction log
file
> of msdb has grown to over 200MB. Oops.
> At least, I managed to found an unoccupied machine to install a temp
SQL
> server, copy the database file to, trancate, and put back to the origional
> server. And the story is over. But what should I do if I experienced that
> next time? There's management change in both my company and that company
so
> replacement of harddisk may not be feasible in a short time. And I'd like
to
> know if there's anything wrong in my procedure of handling the issue. This
> is my first time to handle a SQL server, and I have tough time on this. I
> used to be a programmer only.
> Looking forward for any advice. Thanks a lot.
> Regards,
> Lau Lei Cheong
>
>|||Hello Uri,
Thanks for your response.
One further question, why does the code use "WHILE 1 = 1" instead of
anything like "WHILE (TRUE)"? Is there any reason behind?
Regards,
Lau Lei Cheong
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...
> Vyas's example shows how to divide a "big" transaction into a small ones
> SET ROWCOUNT 1000
> WHILE 1 = 1
> BEGIN
> /*
> DELETION here and Don't forget WHERE condition
> */
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT --Or doing BACKUP LOG File
> END
> END
> SET ROWCOUNT 0
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> It happens to me that I have control over a MSSQL 2000 server that
>> was
>> looked after by another staff today. And than there is a call from the
>> server's owner company that the server's harddisk is running out of
>> space.
>> As background information, the SQL server's running on a 5GB HDD
>> partition with the Win2k system dir on it. And after some inspection, I
>> found the "msdb" database occupies about 1.13GB and it's transaction log
>> occupies 2MB.
>> So I opened Enterprise Manager and looked into the tables one by one.
>> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
>> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
>> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
>> cannot finish the task because the disk is running out of space and no
> room
>> for transaction log. And when I see the file size, the transaction log
> file
>> of msdb has grown to over 200MB. Oops.
>> At least, I managed to found an unoccupied machine to install a temp
> SQL
>> server, copy the database file to, trancate, and put back to the
>> origional
>> server. And the story is over. But what should I do if I experienced that
>> next time? There's management change in both my company and that company
> so
>> replacement of harddisk may not be feasible in a short time. And I'd like
> to
>> know if there's anything wrong in my procedure of handling the issue.
>> This
>> is my first time to handle a SQL server, and I have tough time on this. I
>> used to be a programmer only.
>> Looking forward for any advice. Thanks a lot.
>> Regards,
>> Lau Lei Cheong
>>
>|||1=1 is TRUE condition but if @.@.rowcount=0 we exit from the loop.
You can build your own logic to fetch the rows
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:e8UVyiQjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hello Uri,
> Thanks for your response.
> One further question, why does the code use "WHILE 1 = 1" instead of
> anything like "WHILE (TRUE)"? Is there any reason behind?
> Regards,
> Lau Lei Cheong
> "Uri Dimant" <urid@.iscar.co.il>
¼¶¼g©ó¶l¥ó·s»D:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...
> > Vyas's example shows how to divide a "big" transaction into a small ones
> >
> > SET ROWCOUNT 1000
> > WHILE 1 = 1
> > BEGIN
> > /*
> > DELETION here and Don't forget WHERE condition
> > */
> >
> > IF @.@.ROWCOUNT = 0
> > BEGIN
> > BREAK
> > END
> > ELSE
> > BEGIN
> > CHECKPOINT --Or doing BACKUP LOG File
> > END
> > END
> >
> > SET ROWCOUNT 0
> > "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> > news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> >> Hello,
> >>
> >> It happens to me that I have control over a MSSQL 2000 server that
> >> was
> >> looked after by another staff today. And than there is a call from the
> >> server's owner company that the server's harddisk is running out of
> >> space.
> >>
> >> As background information, the SQL server's running on a 5GB HDD
> >> partition with the Win2k system dir on it. And after some inspection, I
> >> found the "msdb" database occupies about 1.13GB and it's transaction
log
> >> occupies 2MB.
> >>
> >> So I opened Enterprise Manager and looked into the tables one by
one.
> >> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> >> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the
"Query
> >> Analyzer"(I've explicitly called it to run the SQL statement) tell me
it
> >> cannot finish the task because the disk is running out of space and no
> > room
> >> for transaction log. And when I see the file size, the transaction log
> > file
> >> of msdb has grown to over 200MB. Oops.
> >>
> >> At least, I managed to found an unoccupied machine to install a
temp
> > SQL
> >> server, copy the database file to, trancate, and put back to the
> >> origional
> >> server. And the story is over. But what should I do if I experienced
that
> >> next time? There's management change in both my company and that
company
> > so
> >> replacement of harddisk may not be feasible in a short time. And I'd
like
> > to
> >> know if there's anything wrong in my procedure of handling the issue.
> >> This
> >> is my first time to handle a SQL server, and I have tough time on this.
I
> >> used to be a programmer only.
> >>
> >> Looking forward for any advice. Thanks a lot.
> >>
> >> Regards,
> >> Lau Lei Cheong
> >>
> >>
> >>
> >
> >
>
Sunday, February 19, 2012
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.
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
"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...
>
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
"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...
>
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.
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
"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...
>
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
"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...
>
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...
>> 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:
> 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
>>
>|||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
>>
>>
>|||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...
>> 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
>>
>>
>>
>|||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...
>> 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
>>
>>
>>
>>
>
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...
>> 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:
> 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
>>
>|||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
>>
>>
>|||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...
>> 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
>>
>>
>>
>|||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...
>> 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
>>
>>
>>
>>
>
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...
>
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...
>
Subscribe to:
Posts (Atom)