How to detect a dead database

I have a database of SqlServer call myData, and it's physicial is
c:\myData.mdf.
Some one stop the SQLServer Service, then delete c:\myData.mdf, then
start the SQLService, and then the database myData is dead.
How can I detect if myData is in this state?Ad
The sysdatabases table has a column status. Read the BOL about it
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OqBggcyyGHA.4408@.TK2MSFTNGP05.phx.gbl...
>I have a database of SqlServer call myData, and it's physicial is
>c:\myData.mdf.
> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
> start the SQLService, and then the database myData is dead.
> How can I detect if myData is in this state?
>|||If the physical file containing the database has been deleted, then the
database is truly gone.
Do you have a backup?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OqBggcyyGHA.4408@.TK2MSFTNGP05.phx.gbl...
>I have a database of SqlServer call myData, and it's physicial is
>c:\myData.mdf.
> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
> start the SQLService, and then the database myData is dead.
> How can I detect if myData is in this state?
>|||> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
> start the SQLService, and then the database myData is dead.
Did you hire a terrorist?
Just like the case of DBF in Foxpro, if you delete the table.dbf, there
is no way to recover it. You may wanna try Easy Data Recovery Pro to
undelete the file. Did you check the recycle bin?
Man-wai Chang
Softmedia Technology Co., Ltd.
Tel: (852)3583 2780|||I did not wnat to recover the database.
I want to confirm if the database has no physical file before delete it.
How can I confirm the database has no physical file?
"Man-wai Chang" <info@.softmedia.hk>
'?:OIcIYg0yGHA.4844@.TK2MSFTNGP04.phx.gbl...
> Did you hire a terrorist?
> Just like the case of DBF in Foxpro, if you delete the table.dbf, there is
> no way to recover it. You may wanna try Easy Data Recovery Pro to undelete
> the file. Did you check the recycle bin?
>
> --
> Man-wai Chang
> Softmedia Technology Co., Ltd.
> Tel: (852)3583 2780|||Hi,
As a first step ensure that no one have rights to SQL Server box apart from
authorised people. If you have backup you could
restore the database from Backup.
Thanks
Hari
SQL Server MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OqBggcyyGHA.4408@.TK2MSFTNGP05.phx.gbl...
>I have a database of SqlServer call myData, and it's physicial is
>c:\myData.mdf.
> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
> start the SQLService, and then the database myData is dead.
> How can I detect if myData is in this state?
>|||Hi,
Execute the command from Master database:-
DROP DATABASE <DBNAME>
This command will drop the database and close all physical MDF and LDF
Files.
Thanks
hari
SQL Server MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:ObiV0Q2yGHA.4116@.TK2MSFTNGP02.phx.gbl...
>I did not wnat to recover the database.
> I want to confirm if the database has no physical file before delete it.
> How can I confirm the database has no physical file?
>
> "Man-wai Chang" <info@.softmedia.hk>
> '?:OIcIYg0yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>|||Thanks,
But how can I dertiminate if a database lost it's physicial file?
"Hari Prasad" <hari_prasad_k@.hotmail.com> glsD:ey1nz22yGHA.4204@.TK2MSFTNGP04.phx.g
bl...
> Hi,
> Execute the command from Master database:-
> DROP DATABASE <DBNAME>
> This command will drop the database and close all physical MDF and LDF
> Files.
> Thanks
> hari
> SQL Server MVP
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:ObiV0Q2yGHA.4116@.TK2MSFTNGP02.phx.gbl...
>|||ad wrote:
> Thanks,
> But how can I dertiminate if a database lost it's physicial file?
>
If you're on SQL 2000, query the sysdatabases table to get the data file
name, then use xp_cmdshell or the undocumented xp_fileexists sproc to
see if the file exists.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
Database will move to suspect status.
Thanks
Hari
SQL Server MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:%23uh5rS6yGHA.996@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> But how can I dertiminate if a database lost it's physicial file?
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com>
> glsD:ey1nz22yGHA.4204@.TK2MSFTNGP04.phx.gbl...
>

How to detect a dead database

I have a database of SqlServer call myData, and it's physicial is
c:\myData.mdf.
Some one stop the SQLServer Service, then delete c:\myData.mdf, then
start the SQLService, and then the database myData is dead.
How can I detect if myData is in this state?Ad
The sysdatabases table has a column status. Read the BOL about it
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OqBggcyyGHA.4408@.TK2MSFTNGP05.phx.gbl...
>I have a database of SqlServer call myData, and it's physicial is
>c:\myData.mdf.
> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
> start the SQLService, and then the database myData is dead.
> How can I detect if myData is in this state?
>|||If the physical file containing the database has been deleted, then the
database is truly gone.
Do you have a backup?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OqBggcyyGHA.4408@.TK2MSFTNGP05.phx.gbl...
>I have a database of SqlServer call myData, and it's physicial is
>c:\myData.mdf.
> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
> start the SQLService, and then the database myData is dead.
> How can I detect if myData is in this state?
>|||> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
> start the SQLService, and then the database myData is dead.
Did you hire a terrorist? :)
Just like the case of DBF in Foxpro, if you delete the table.dbf, there
is no way to recover it. You may wanna try Easy Data Recovery Pro to
undelete the file. Did you check the recycle bin?
Man-wai Chang
Softmedia Technology Co., Ltd.
Tel: (852)3583 2780|||I did not wnat to recover the database.
I want to confirm if the database has no physical file before delete it.
How can I confirm the database has no physical file?
"Man-wai Chang" <info@.softmedia.hk>
'?:OIcIYg0yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
>> start the SQLService, and then the database myData is dead.
> Did you hire a terrorist? :)
> Just like the case of DBF in Foxpro, if you delete the table.dbf, there is
> no way to recover it. You may wanna try Easy Data Recovery Pro to undelete
> the file. Did you check the recycle bin?
>
> --
> Man-wai Chang
> Softmedia Technology Co., Ltd.
> Tel: (852)3583 2780|||Hi,
As a first step ensure that no one have rights to SQL Server box apart from
authorised people. If you have backup you could
restore the database from Backup.
Thanks
Hari
SQL Server MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OqBggcyyGHA.4408@.TK2MSFTNGP05.phx.gbl...
>I have a database of SqlServer call myData, and it's physicial is
>c:\myData.mdf.
> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
> start the SQLService, and then the database myData is dead.
> How can I detect if myData is in this state?
>|||Hi,
Execute the command from Master database:-
DROP DATABASE <DBNAME>
This command will drop the database and close all physical MDF and LDF
Files.
Thanks
hari
SQL Server MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:ObiV0Q2yGHA.4116@.TK2MSFTNGP02.phx.gbl...
>I did not wnat to recover the database.
> I want to confirm if the database has no physical file before delete it.
> How can I confirm the database has no physical file?
>
> "Man-wai Chang" <info@.softmedia.hk>
> '?:OIcIYg0yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
>> start the SQLService, and then the database myData is dead.
>> Did you hire a terrorist? :)
>> Just like the case of DBF in Foxpro, if you delete the table.dbf, there
>> is no way to recover it. You may wanna try Easy Data Recovery Pro to
>> undelete the file. Did you check the recycle bin?
>>
>> --
>> Man-wai Chang
>> Softmedia Technology Co., Ltd.
>> Tel: (852)3583 2780
>|||Thanks,
But how can I dertiminate if a database lost it's physicial file?
"Hari Prasad" <hari_prasad_k@.hotmail.com> ¼¶¼g©ó¶l¥ó·s»D:ey1nz22yGHA.4204@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Execute the command from Master database:-
> DROP DATABASE <DBNAME>
> This command will drop the database and close all physical MDF and LDF
> Files.
> Thanks
> hari
> SQL Server MVP
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:ObiV0Q2yGHA.4116@.TK2MSFTNGP02.phx.gbl...
>>I did not wnat to recover the database.
>> I want to confirm if the database has no physical file before delete it.
>> How can I confirm the database has no physical file?
>>
>> "Man-wai Chang" <info@.softmedia.hk>
>> '?:OIcIYg0yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
>> start the SQLService, and then the database myData is dead.
>> Did you hire a terrorist? :)
>> Just like the case of DBF in Foxpro, if you delete the table.dbf, there
>> is no way to recover it. You may wanna try Easy Data Recovery Pro to
>> undelete the file. Did you check the recycle bin?
>>
>> --
>> Man-wai Chang
>> Softmedia Technology Co., Ltd.
>> Tel: (852)3583 2780
>>
>|||ad wrote:
> Thanks,
> But how can I dertiminate if a database lost it's physicial file?
>
If you're on SQL 2000, query the sysdatabases table to get the data file
name, then use xp_cmdshell or the undocumented xp_fileexists sproc to
see if the file exists.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
Database will move to suspect status.
Thanks
Hari
SQL Server MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:%23uh5rS6yGHA.996@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> But how can I dertiminate if a database lost it's physicial file?
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com>
> ¼¶¼g©ó¶l¥ó·s»D:ey1nz22yGHA.4204@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Execute the command from Master database:-
>> DROP DATABASE <DBNAME>
>> This command will drop the database and close all physical MDF and LDF
>> Files.
>> Thanks
>> hari
>> SQL Server MVP
>>
>> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
>> news:ObiV0Q2yGHA.4116@.TK2MSFTNGP02.phx.gbl...
>>I did not wnat to recover the database.
>> I want to confirm if the database has no physical file before delete it.
>> How can I confirm the database has no physical file?
>>
>> "Man-wai Chang" <info@.softmedia.hk>
>> '?:OIcIYg0yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>> Some one stop the SQLServer Service, then delete c:\myData.mdf, then
>> start the SQLService, and then the database myData is dead.
>> Did you hire a terrorist? :)
>> Just like the case of DBF in Foxpro, if you delete the table.dbf, there
>> is no way to recover it. You may wanna try Easy Data Recovery Pro to
>> undelete the file. Did you check the recycle bin?
>>
>> --
>> Man-wai Chang
>> Softmedia Technology Co., Ltd.
>> Tel: (852)3583 2780
>>
>>
>

How to design "product kits"

Hi,

I've run into a bit of a sticky design issue. We have products in
three categories which I will call 'A', 'B' and 'C'. We have "kits"
which contain three products, one from each category.

Below is some sample SQL to set things up, but I need to ensure that
each kit gets three products -- one from each category. Obviously,
this basic SQL doesn't allow that. Any suggestions? Do I need a
different schema design, or is there something else I should be
looking at?

Cheers,
Curtis

CREATE TABLE category (
id int identity primary key,
name varchar(30)
);

CREATE TABLE products (
id int identity primary key,
name varchar(30),
category_id int references category(id)
);

CREATE TABLE kits (
id int identity primary key,
name varchar(30)
);

CREATE TABLE kit_products (
kit_id int references kits(id),
product_id int references products(id)
);>> We have products in three categories which I will call 'A', 'B' and
'C'. <<

... and you declared them as INTEGER.

>> We have "kits" which contain three products, one from each
category. <<

So, do you have only three categories??

>> Do I need a different schema design, ... <<

Oh yeah! You do not have any keys (IDENTITY is never a key by
definition) and "id" is to vague to be a data element name (read
ISO-11179 rules). Category is singular, while the other table names
are plural; ergo, category must have one and only one row? All the
important data is NULL-able.

I am going to assume that you have so many categories that they
require a separate table; if not, put them in a CHECK() clause.

CREATE TABLE Categories
(category_id INTEGER PRIMARY KEY,
category_name VARCHAR(30) NOT NULL);

CREATE TABLE Products
(product_name VARCHAR(30) NOT NULL,
product_id INTEGER NOT NULL UNIQUE,
category_id INTEGER NOT NULL
REFERENCES Categories(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (product_id, category_id));

CREATE TABLE ProductKits
(kit_id INTEGER NOT NULL
kit_name VARCHAR(30) NOT NULL,
product_id_1 INTEGER NOT NULL,
category_id_1 INTEGER NOT NULL
FOREIGN KEY (product_id_1, category_id_1)
REFERENCES Products (product_id, category_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
product_id_2 INTEGER NOT NULL,
category_id_2 INTEGER NOT NULL
FOREIGN KEY (product_id_2, category_id_2)
REFERENCES Products (product_id, category_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
product_id_3 INTEGER NOT NULL,
category_id_3 INTEGER NOT NULL
FOREIGN KEY (product_id_3, category_id_3)
REFERENCES Products (product_id, category_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
CHECK (category_id_1 = 1
AND category_id_2 = 2
AND category_id_3 = 3));

The sneaky trick is to put both (product_id, category_id) in the
primary key of Products, so both can be referenced. The product_id is
still unique (another assumption, since your original schema allowed a
product to be named NULL or repeated under a thousand different
IDENTITY numbers, making data integrity impossible). I also assume
that the categories for the kits is (1, 2, 3) instead of ('a', 'b',
'c').

If there are onlyn three categories, then use this and no separate
Categories table:

CREATE TABLE Products
(product_name VARCHAR(30) NOT NULL,
product_id INTEGER NOT NULL UNIQUE,
category_id INTEGER NOT NULL
CHECK(category_id IN (3,2,1))
PRIMARY KEY (product_id, category_id));

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
>

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]
>

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
> >>
> >>
> >>
> >
> >
>