I'm having a problem when i try to delete a table...I have run
dbcc newalloc
DBCC checkdb
on the database and the newalloc came back without errors...the
checkdb just hangs without returning anything...this is a developement
database with the live version off site, so whatever i need to do to
get things going is ok - but i'd like to just delete the table and
bring a new one down...
the problem is i can't do anything with this table...i can open it,
but i can't delete a row from it...i can't delete the whole table (it
just hangs if i try) and i can't copy a new table over the top or
rename it (again, hang)
Is there any way to just delete this specific table or figure out what
is wrong so i can fix it?
thanks in advance...
jake
Hi,
Might be locking. Can you kill all the users connected to this database and
then set the database to single user
sp_dboption 'dbname','single user',true
After that try executing the below command to delete the contents of the
table
truncate table <table_name>
This will fail if that table got any relations (Foreign key), in that case
use delete statement.
Thanks
Hari
MCDBA
"jake stahh" <junk@.anv.net> wrote in message
news:n2s2i0hogemkd32pp71tsm66mkofiqj0l8@.4ax.com...
> I'm having a problem when i try to delete a table...I have run
> dbcc newalloc
> DBCC checkdb
> on the database and the newalloc came back without errors...the
> checkdb just hangs without returning anything...this is a developement
> database with the live version off site, so whatever i need to do to
> get things going is ok - but i'd like to just delete the table and
> bring a new one down...
> the problem is i can't do anything with this table...i can open it,
> but i can't delete a row from it...i can't delete the whole table (it
> just hangs if i try) and i can't copy a new table over the top or
> rename it (again, hang)
> Is there any way to just delete this specific table or figure out what
> is wrong so i can fix it?
> thanks in advance...
> jake
|||you are a genius! thank you so much - it worked perfectly! the check
is in the mail...
jake stahh
On Tue, 17 Aug 2004 09:28:41 +0530, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:
>Hi,
>Might be locking. Can you kill all the users connected to this database and
>then set the database to single user
>sp_dboption 'dbname','single user',true
>After that try executing the below command to delete the contents of the
>table
>truncate table <table_name>
>This will fail if that table got any relations (Foreign key), in that case
>use delete statement.
>Thanks
>Hari
>MCDBA
>
>"jake stahh" <junk@.anv.net> wrote in message
>news:n2s2i0hogemkd32pp71tsm66mkofiqj0l8@.4ax.com.. .
>
sql
Showing posts with label back. Show all posts
Showing posts with label back. Show all posts
Thursday, March 29, 2012
Deleting Table
I'm having a problem when i try to delete a table...I have run
dbcc newalloc
DBCC checkdb
on the database and the newalloc came back without errors...the
checkdb just hangs without returning anything...this is a developement
database with the live version off site, so whatever i need to do to
get things going is ok - but i'd like to just delete the table and
bring a new one down...
the problem is i can't do anything with this table...i can open it,
but i can't delete a row from it...i can't delete the whole table (it
just hangs if i try) and i can't copy a new table over the top or
rename it (again, hang)
Is there any way to just delete this specific table or figure out what
is wrong so i can fix it?
thanks in advance...
jake
sorry about this one...it was a double post...please ignore
On Tue, 17 Aug 2004 09:25:38 -0600, jake stahh <junk@.anv.net> wrote:
>I'm having a problem when i try to delete a table...I have run
>dbcc newalloc
>DBCC checkdb
>on the database and the newalloc came back without errors...the
>checkdb just hangs without returning anything...this is a developement
>database with the live version off site, so whatever i need to do to
>get things going is ok - but i'd like to just delete the table and
>bring a new one down...
>the problem is i can't do anything with this table...i can open it,
>but i can't delete a row from it...i can't delete the whole table (it
>just hangs if i try) and i can't copy a new table over the top or
>rename it (again, hang)
>Is there any way to just delete this specific table or figure out what
>is wrong so i can fix it?
>thanks in advance...
>jake
dbcc newalloc
DBCC checkdb
on the database and the newalloc came back without errors...the
checkdb just hangs without returning anything...this is a developement
database with the live version off site, so whatever i need to do to
get things going is ok - but i'd like to just delete the table and
bring a new one down...
the problem is i can't do anything with this table...i can open it,
but i can't delete a row from it...i can't delete the whole table (it
just hangs if i try) and i can't copy a new table over the top or
rename it (again, hang)
Is there any way to just delete this specific table or figure out what
is wrong so i can fix it?
thanks in advance...
jake
sorry about this one...it was a double post...please ignore
On Tue, 17 Aug 2004 09:25:38 -0600, jake stahh <junk@.anv.net> wrote:
>I'm having a problem when i try to delete a table...I have run
>dbcc newalloc
>DBCC checkdb
>on the database and the newalloc came back without errors...the
>checkdb just hangs without returning anything...this is a developement
>database with the live version off site, so whatever i need to do to
>get things going is ok - but i'd like to just delete the table and
>bring a new one down...
>the problem is i can't do anything with this table...i can open it,
>but i can't delete a row from it...i can't delete the whole table (it
>just hangs if i try) and i can't copy a new table over the top or
>rename it (again, hang)
>Is there any way to just delete this specific table or figure out what
>is wrong so i can fix it?
>thanks in advance...
>jake
Deleting Table
I'm having a problem when i try to delete a table...I have run
dbcc newalloc
DBCC checkdb
on the database and the newalloc came back without errors...the
checkdb just hangs without returning anything...this is a developement
database with the live version off site, so whatever i need to do to
get things going is ok - but i'd like to just delete the table and
bring a new one down...
the problem is i can't do anything with this table...i can open it,
but i can't delete a row from it...i can't delete the whole table (it
just hangs if i try) and i can't copy a new table over the top or
rename it (again, hang)
Is there any way to just delete this specific table or figure out what
is wrong so i can fix it?
thanks in advance...
jakeHi,
Might be locking. Can you kill all the users connected to this database and
then set the database to single user
sp_dboption 'dbname','single user',true
After that try executing the below command to delete the contents of the
table
truncate table <table_name>
This will fail if that table got any relations (Foreign key), in that case
use delete statement.
Thanks
Hari
MCDBA
"jake stahh" <junk@.anv.net> wrote in message
news:n2s2i0hogemkd32pp71tsm66mkofiqj0l8@.
4ax.com...
> I'm having a problem when i try to delete a table...I have run
> dbcc newalloc
> DBCC checkdb
> on the database and the newalloc came back without errors...the
> checkdb just hangs without returning anything...this is a developement
> database with the live version off site, so whatever i need to do to
> get things going is ok - but i'd like to just delete the table and
> bring a new one down...
> the problem is i can't do anything with this table...i can open it,
> but i can't delete a row from it...i can't delete the whole table (it
> just hangs if i try) and i can't copy a new table over the top or
> rename it (again, hang)
> Is there any way to just delete this specific table or figure out what
> is wrong so i can fix it?
> thanks in advance...
> jake|||you are a genius! thank you so much - it worked perfectly! the check
is in the mail...
jake stahh
On Tue, 17 Aug 2004 09:28:41 +0530, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:
>Hi,
>Might be locking. Can you kill all the users connected to this database and
>then set the database to single user
>sp_dboption 'dbname','single user',true
>After that try executing the below command to delete the contents of the
>table
>truncate table <table_name>
>This will fail if that table got any relations (Foreign key), in that case
>use delete statement.
>Thanks
>Hari
>MCDBA
>
>"jake stahh" <junk@.anv.net> wrote in message
> news:n2s2i0hogemkd32pp71tsm66mkofiqj0l8@.
4ax.com...
>
dbcc newalloc
DBCC checkdb
on the database and the newalloc came back without errors...the
checkdb just hangs without returning anything...this is a developement
database with the live version off site, so whatever i need to do to
get things going is ok - but i'd like to just delete the table and
bring a new one down...
the problem is i can't do anything with this table...i can open it,
but i can't delete a row from it...i can't delete the whole table (it
just hangs if i try) and i can't copy a new table over the top or
rename it (again, hang)
Is there any way to just delete this specific table or figure out what
is wrong so i can fix it?
thanks in advance...
jakeHi,
Might be locking. Can you kill all the users connected to this database and
then set the database to single user
sp_dboption 'dbname','single user',true
After that try executing the below command to delete the contents of the
table
truncate table <table_name>
This will fail if that table got any relations (Foreign key), in that case
use delete statement.
Thanks
Hari
MCDBA
"jake stahh" <junk@.anv.net> wrote in message
news:n2s2i0hogemkd32pp71tsm66mkofiqj0l8@.
4ax.com...
> I'm having a problem when i try to delete a table...I have run
> dbcc newalloc
> DBCC checkdb
> on the database and the newalloc came back without errors...the
> checkdb just hangs without returning anything...this is a developement
> database with the live version off site, so whatever i need to do to
> get things going is ok - but i'd like to just delete the table and
> bring a new one down...
> the problem is i can't do anything with this table...i can open it,
> but i can't delete a row from it...i can't delete the whole table (it
> just hangs if i try) and i can't copy a new table over the top or
> rename it (again, hang)
> Is there any way to just delete this specific table or figure out what
> is wrong so i can fix it?
> thanks in advance...
> jake|||you are a genius! thank you so much - it worked perfectly! the check
is in the mail...
jake stahh
On Tue, 17 Aug 2004 09:28:41 +0530, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:
>Hi,
>Might be locking. Can you kill all the users connected to this database and
>then set the database to single user
>sp_dboption 'dbname','single user',true
>After that try executing the below command to delete the contents of the
>table
>truncate table <table_name>
>This will fail if that table got any relations (Foreign key), in that case
>use delete statement.
>Thanks
>Hari
>MCDBA
>
>"jake stahh" <junk@.anv.net> wrote in message
> news:n2s2i0hogemkd32pp71tsm66mkofiqj0l8@.
4ax.com...
>
Deleting Table
I'm having a problem when i try to delete a table...I have run
dbcc newalloc
DBCC checkdb
on the database and the newalloc came back without errors...the
checkdb just hangs without returning anything...this is a developement
database with the live version off site, so whatever i need to do to
get things going is ok - but i'd like to just delete the table and
bring a new one down...
the problem is i can't do anything with this table...i can open it,
but i can't delete a row from it...i can't delete the whole table (it
just hangs if i try) and i can't copy a new table over the top or
rename it (again, hang)
Is there any way to just delete this specific table or figure out what
is wrong so i can fix it?
thanks in advance...
jakesorry about this one...it was a double post...please ignore
On Tue, 17 Aug 2004 09:25:38 -0600, jake stahh <junk@.anv.net> wrote:
>I'm having a problem when i try to delete a table...I have run
>dbcc newalloc
>DBCC checkdb
>on the database and the newalloc came back without errors...the
>checkdb just hangs without returning anything...this is a developement
>database with the live version off site, so whatever i need to do to
>get things going is ok - but i'd like to just delete the table and
>bring a new one down...
>the problem is i can't do anything with this table...i can open it,
>but i can't delete a row from it...i can't delete the whole table (it
>just hangs if i try) and i can't copy a new table over the top or
>rename it (again, hang)
>Is there any way to just delete this specific table or figure out what
>is wrong so i can fix it?
>thanks in advance...
>jake
dbcc newalloc
DBCC checkdb
on the database and the newalloc came back without errors...the
checkdb just hangs without returning anything...this is a developement
database with the live version off site, so whatever i need to do to
get things going is ok - but i'd like to just delete the table and
bring a new one down...
the problem is i can't do anything with this table...i can open it,
but i can't delete a row from it...i can't delete the whole table (it
just hangs if i try) and i can't copy a new table over the top or
rename it (again, hang)
Is there any way to just delete this specific table or figure out what
is wrong so i can fix it?
thanks in advance...
jakesorry about this one...it was a double post...please ignore
On Tue, 17 Aug 2004 09:25:38 -0600, jake stahh <junk@.anv.net> wrote:
>I'm having a problem when i try to delete a table...I have run
>dbcc newalloc
>DBCC checkdb
>on the database and the newalloc came back without errors...the
>checkdb just hangs without returning anything...this is a developement
>database with the live version off site, so whatever i need to do to
>get things going is ok - but i'd like to just delete the table and
>bring a new one down...
>the problem is i can't do anything with this table...i can open it,
>but i can't delete a row from it...i can't delete the whole table (it
>just hangs if i try) and i can't copy a new table over the top or
>rename it (again, hang)
>Is there any way to just delete this specific table or figure out what
>is wrong so i can fix it?
>thanks in advance...
>jake
Tuesday, March 27, 2012
Deleting or Clearing
Is there a way to delete a database log file without detaching the database?
I don't care about loosing the ability to roll back, etc.
I tried "restricting the growth" setting, but that just makes the database
unusable when it hits the limit. I'd like to be able to restrict a log file
from growing above a specific amount.No, SQL Server need the log file. First you need to consider recovery model.
If you have full, you
need to do regular log backups to empty (not shrink) the log. If you don't w
ant to do regular log
backup (which has a bunch of advantages), then have simple recovery model. O
nce you have sorted that
out, see http://www.karaszi.com/SQLServer/info_dont_shrink.asp for info on h
ow to do the actual
shrink of the log files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott" <sbailey@.mileslumber.com> wrote in message news:uIJc2$ddGHA.3632@.TK2MSFTNGP05.phx.g
bl...
> Is there a way to delete a database log file without detaching the databas
e? I don't care about
> loosing the ability to roll back, etc.
> I tried "restricting the growth" setting, but that just makes the database
unusable when it hits
> the limit. I'd like to be able to restrict a log file from growing above a
specific amount.
>|||The key point here is to use "dbcc shrinkdatabase".
The first solution keeps the backup of data and transaction logs.
The database log file CANNOT be removed, but the size will be reduced.
Depending on your database size, this may be time consuming due to the
backup task.
- Do a full backup on the database.
- Shrink the database using "dbcc shrinkdatabase (dbname)".
Here's another quick trick that I will use when there is not enough space on
the server for doing a full/log backup, while adding a HDD is not possible.
(But, the data in the log database will be removed, and cannot be
recovered!)
-- ** warning ** (copied from BOL)
-- TRUNCATE_ONLY removes the inactive part of the log without making a
backup copy of it and truncates the log.
-- This option frees space. Specifying a backup device is unnecessary
because the log backup is not saved.
-- The changes recorded in the log are not recoverable.
-- For recovery purposes, immediately execute BACKUP DATABASE.
backup log dbname with truncate_only
-- You can then shrink the database.
dbcc shrinkdatabase (dbname)
Martin C K Poon
Senior Analyst Programmer
====================================
"Scott" <sbailey@.mileslumber.com> bl
news:uIJc2$ddGHA.3632@.TK2MSFTNGP05.phx.gbl g...
> Is there a way to delete a database log file without detaching the
database?
> I don't care about loosing the ability to roll back, etc.
> I tried "restricting the growth" setting, but that just makes the database
> unusable when it hits the limit. I'd like to be able to restrict a log
file
> from growing above a specific amount.
>|||so, to your knowledge, this method will reduce a log the maximum amount that
is possible while keeping the database attached?
"Martin C K Poon" < martinpoon__at__graduate__dot__hku__dot_
_hk> wrote in
message news:eUoUCSedGHA.1656@.TK2MSFTNGP02.phx.gbl...
> The key point here is to use "dbcc shrinkdatabase".
> The first solution keeps the backup of data and transaction logs.
> The database log file CANNOT be removed, but the size will be reduced.
> Depending on your database size, this may be time consuming due to the
> backup task.
> - Do a full backup on the database.
> - Shrink the database using "dbcc shrinkdatabase (dbname)".
> Here's another quick trick that I will use when there is not enough space
> on
> the server for doing a full/log backup, while adding a HDD is not
> possible.
> (But, the data in the log database will be removed, and cannot be
> recovered!)
> -- ** warning ** (copied from BOL)
> -- TRUNCATE_ONLY removes the inactive part of the log without making a
> backup copy of it and truncates the log.
> -- This option frees space. Specifying a backup device is unnecessary
> because the log backup is not saved.
> -- The changes recorded in the log are not recoverable.
> -- For recovery purposes, immediately execute BACKUP DATABASE.
> backup log dbname with truncate_only
> -- You can then shrink the database.
> dbcc shrinkdatabase (dbname)
> --
> Martin C K Poon
> Senior Analyst Programmer
> ====================================
> "Scott" <sbailey@.mileslumber.com> bl
> news:uIJc2$ddGHA.3632@.TK2MSFTNGP05.phx.gbl g...
> database?
> file
>|||Both backup (full/log) and "dbcc shrinkdatabase" can be done with the
database online (and without detaching the database).
There is also a database option 'autoshrink' that could be used for
shrinking a database periodically and automcatically by SQL Server. By
default, the 'autoshrink' option is set to OFF in SS2000 (except SS2000
Personal Edition). You will need to implement an appropriate backup
strategy, anyways.
-- To set the autoshrink database option. (When true, the database files are
candidates for automatic periodic shrinking.)
sp_dboption 'dbname', 'autoshrink', 'TRUE/FALSE'
References
- Shrinking the transaction log
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
- Truncating the transaction log
http://msdn.microsoft.com/library/d...r />
_7vaf.asp
Martin C K Poon
Senior Analyst Programmer
====================================
"scott" <sbailey@.mileslumber.com> bl
news:%23E1ZEXgdGHA.1260@.TK2MSFTNGP05.phx.gbl g...
> so, to your knowledge, this method will reduce a log the maximum amount
that
> is possible while keeping the database attached?
>
> "Martin C K Poon" < martinpoon__at__graduate__dot__hku__dot_
_hk> wrote in
> message news:eUoUCSedGHA.1656@.TK2MSFTNGP02.phx.gbl...
space
>sql
I don't care about loosing the ability to roll back, etc.
I tried "restricting the growth" setting, but that just makes the database
unusable when it hits the limit. I'd like to be able to restrict a log file
from growing above a specific amount.No, SQL Server need the log file. First you need to consider recovery model.
If you have full, you
need to do regular log backups to empty (not shrink) the log. If you don't w
ant to do regular log
backup (which has a bunch of advantages), then have simple recovery model. O
nce you have sorted that
out, see http://www.karaszi.com/SQLServer/info_dont_shrink.asp for info on h
ow to do the actual
shrink of the log files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott" <sbailey@.mileslumber.com> wrote in message news:uIJc2$ddGHA.3632@.TK2MSFTNGP05.phx.g
bl...
> Is there a way to delete a database log file without detaching the databas
e? I don't care about
> loosing the ability to roll back, etc.
> I tried "restricting the growth" setting, but that just makes the database
unusable when it hits
> the limit. I'd like to be able to restrict a log file from growing above a
specific amount.
>|||The key point here is to use "dbcc shrinkdatabase".
The first solution keeps the backup of data and transaction logs.
The database log file CANNOT be removed, but the size will be reduced.
Depending on your database size, this may be time consuming due to the
backup task.
- Do a full backup on the database.
- Shrink the database using "dbcc shrinkdatabase (dbname)".
Here's another quick trick that I will use when there is not enough space on
the server for doing a full/log backup, while adding a HDD is not possible.
(But, the data in the log database will be removed, and cannot be
recovered!)
-- ** warning ** (copied from BOL)
-- TRUNCATE_ONLY removes the inactive part of the log without making a
backup copy of it and truncates the log.
-- This option frees space. Specifying a backup device is unnecessary
because the log backup is not saved.
-- The changes recorded in the log are not recoverable.
-- For recovery purposes, immediately execute BACKUP DATABASE.
backup log dbname with truncate_only
-- You can then shrink the database.
dbcc shrinkdatabase (dbname)
Martin C K Poon
Senior Analyst Programmer
====================================
"Scott" <sbailey@.mileslumber.com> bl
news:uIJc2$ddGHA.3632@.TK2MSFTNGP05.phx.gbl g...
> Is there a way to delete a database log file without detaching the
database?
> I don't care about loosing the ability to roll back, etc.
> I tried "restricting the growth" setting, but that just makes the database
> unusable when it hits the limit. I'd like to be able to restrict a log
file
> from growing above a specific amount.
>|||so, to your knowledge, this method will reduce a log the maximum amount that
is possible while keeping the database attached?
"Martin C K Poon" < martinpoon__at__graduate__dot__hku__dot_
_hk> wrote in
message news:eUoUCSedGHA.1656@.TK2MSFTNGP02.phx.gbl...
> The key point here is to use "dbcc shrinkdatabase".
> The first solution keeps the backup of data and transaction logs.
> The database log file CANNOT be removed, but the size will be reduced.
> Depending on your database size, this may be time consuming due to the
> backup task.
> - Do a full backup on the database.
> - Shrink the database using "dbcc shrinkdatabase (dbname)".
> Here's another quick trick that I will use when there is not enough space
> on
> the server for doing a full/log backup, while adding a HDD is not
> possible.
> (But, the data in the log database will be removed, and cannot be
> recovered!)
> -- ** warning ** (copied from BOL)
> -- TRUNCATE_ONLY removes the inactive part of the log without making a
> backup copy of it and truncates the log.
> -- This option frees space. Specifying a backup device is unnecessary
> because the log backup is not saved.
> -- The changes recorded in the log are not recoverable.
> -- For recovery purposes, immediately execute BACKUP DATABASE.
> backup log dbname with truncate_only
> -- You can then shrink the database.
> dbcc shrinkdatabase (dbname)
> --
> Martin C K Poon
> Senior Analyst Programmer
> ====================================
> "Scott" <sbailey@.mileslumber.com> bl
> news:uIJc2$ddGHA.3632@.TK2MSFTNGP05.phx.gbl g...
> database?
> file
>|||Both backup (full/log) and "dbcc shrinkdatabase" can be done with the
database online (and without detaching the database).
There is also a database option 'autoshrink' that could be used for
shrinking a database periodically and automcatically by SQL Server. By
default, the 'autoshrink' option is set to OFF in SS2000 (except SS2000
Personal Edition). You will need to implement an appropriate backup
strategy, anyways.
-- To set the autoshrink database option. (When true, the database files are
candidates for automatic periodic shrinking.)
sp_dboption 'dbname', 'autoshrink', 'TRUE/FALSE'
References
- Shrinking the transaction log
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
- Truncating the transaction log
http://msdn.microsoft.com/library/d...r />
_7vaf.asp
Martin C K Poon
Senior Analyst Programmer
====================================
"scott" <sbailey@.mileslumber.com> bl
news:%23E1ZEXgdGHA.1260@.TK2MSFTNGP05.phx.gbl g...
> so, to your knowledge, this method will reduce a log the maximum amount
that
> is possible while keeping the database attached?
>
> "Martin C K Poon" < martinpoon__at__graduate__dot__hku__dot_
_hk> wrote in
> message news:eUoUCSedGHA.1656@.TK2MSFTNGP02.phx.gbl...
space
>sql
Thursday, March 22, 2012
deleting dependent rows in SQL 2000
I have a contacts table that has around 30 tables related to it (which
have other tables related to it - that often refer back to the same
contacts table). I am trying to delete a single contact and all rows
in any table related to it. I wrote a recursive query to scan the
database and remove the corresponding rows before deleting the contact
row.
I'm not sure this is the best solution. It takes a great deal of time
to run and seems to have problems with certain sections of the
database. Another idea I had was to modify each of the foreign keys
to turn on Cascade Delete whenever possibe, delete the row in the
contacts table then turn Cascade Delete off.
I would simply write the query out by hand but, because this is one of
the core tables, any new tables are usually linked to it and usually
kill the script (forcing yet another rewrite). Anyone have any
suggestions on what the best method would be to do this? Thanks for
your help.
AlexAlex,
Yes, you've got to tear them down in order of hierarchy.
DELETE GREATGRANDCHILDREN
DELETE GRANDCHILDREN
DELETE CHILDREN
DELETE PARENT
No shortcuts.
The good new is, once you get it written, you shouldn't have to write it
again. ;-)
James Hokes
"Alex" <under_payd@.yahoo.com> wrote in message
news:64b52496.0402231433.10fdcf5a@.posting.google.com...
> I have a contacts table that has around 30 tables related to it (which
> have other tables related to it - that often refer back to the same
> contacts table). I am trying to delete a single contact and all rows
> in any table related to it. I wrote a recursive query to scan the
> database and remove the corresponding rows before deleting the contact
> row.
> I'm not sure this is the best solution. It takes a great deal of time
> to run and seems to have problems with certain sections of the
> database. Another idea I had was to modify each of the foreign keys
> to turn on Cascade Delete whenever possibe, delete the row in the
> contacts table then turn Cascade Delete off.
> I would simply write the query out by hand but, because this is one of
> the core tables, any new tables are usually linked to it and usually
> kill the script (forcing yet another rewrite). Anyone have any
> suggestions on what the best method would be to do this? Thanks for
> your help.
> Alex
have other tables related to it - that often refer back to the same
contacts table). I am trying to delete a single contact and all rows
in any table related to it. I wrote a recursive query to scan the
database and remove the corresponding rows before deleting the contact
row.
I'm not sure this is the best solution. It takes a great deal of time
to run and seems to have problems with certain sections of the
database. Another idea I had was to modify each of the foreign keys
to turn on Cascade Delete whenever possibe, delete the row in the
contacts table then turn Cascade Delete off.
I would simply write the query out by hand but, because this is one of
the core tables, any new tables are usually linked to it and usually
kill the script (forcing yet another rewrite). Anyone have any
suggestions on what the best method would be to do this? Thanks for
your help.
AlexAlex,
Yes, you've got to tear them down in order of hierarchy.
DELETE GREATGRANDCHILDREN
DELETE GRANDCHILDREN
DELETE CHILDREN
DELETE PARENT
No shortcuts.
The good new is, once you get it written, you shouldn't have to write it
again. ;-)
James Hokes
"Alex" <under_payd@.yahoo.com> wrote in message
news:64b52496.0402231433.10fdcf5a@.posting.google.com...
> I have a contacts table that has around 30 tables related to it (which
> have other tables related to it - that often refer back to the same
> contacts table). I am trying to delete a single contact and all rows
> in any table related to it. I wrote a recursive query to scan the
> database and remove the corresponding rows before deleting the contact
> row.
> I'm not sure this is the best solution. It takes a great deal of time
> to run and seems to have problems with certain sections of the
> database. Another idea I had was to modify each of the foreign keys
> to turn on Cascade Delete whenever possibe, delete the row in the
> contacts table then turn Cascade Delete off.
> I would simply write the query out by hand but, because this is one of
> the core tables, any new tables are usually linked to it and usually
> kill the script (forcing yet another rewrite). Anyone have any
> suggestions on what the best method would be to do this? Thanks for
> your help.
> Alex
deleting dependent rows in SQL 2000
I have a contacts table that has around 30 tables related to it (which
have other tables related to it - that often refer back to the same
contacts table). I am trying to delete a single contact and all rows
in any table related to it. I wrote a recursive query to scan the
database and remove the corresponding rows before deleting the contact
row.
I'm not sure this is the best solution. It takes a great deal of time
to run and seems to have problems with certain sections of the
database. Another idea I had was to modify each of the foreign keys
to turn on Cascade Delete whenever possibe, delete the row in the
contacts table then turn Cascade Delete off.
I would simply write the query out by hand but, because this is one of
the core tables, any new tables are usually linked to it and usually
kill the script (forcing yet another rewrite). Anyone have any
suggestions on what the best method would be to do this? Thanks for
your help.
AlexAlex,
Yes, you've got to tear them down in order of hierarchy.
DELETE GREATGRANDCHILDREN
DELETE GRANDCHILDREN
DELETE CHILDREN
DELETE PARENT
No shortcuts.
The good new is, once you get it written, you shouldn't have to write it
again. ;-)
James Hokes
"Alex" <under_payd@.yahoo.com> wrote in message
news:64b52496.0402231433.10fdcf5a@.posting.google.com...
> I have a contacts table that has around 30 tables related to it (which
> have other tables related to it - that often refer back to the same
> contacts table). I am trying to delete a single contact and all rows
> in any table related to it. I wrote a recursive query to scan the
> database and remove the corresponding rows before deleting the contact
> row.
> I'm not sure this is the best solution. It takes a great deal of time
> to run and seems to have problems with certain sections of the
> database. Another idea I had was to modify each of the foreign keys
> to turn on Cascade Delete whenever possibe, delete the row in the
> contacts table then turn Cascade Delete off.
> I would simply write the query out by hand but, because this is one of
> the core tables, any new tables are usually linked to it and usually
> kill the script (forcing yet another rewrite). Anyone have any
> suggestions on what the best method would be to do this? Thanks for
> your help.
> Alex
have other tables related to it - that often refer back to the same
contacts table). I am trying to delete a single contact and all rows
in any table related to it. I wrote a recursive query to scan the
database and remove the corresponding rows before deleting the contact
row.
I'm not sure this is the best solution. It takes a great deal of time
to run and seems to have problems with certain sections of the
database. Another idea I had was to modify each of the foreign keys
to turn on Cascade Delete whenever possibe, delete the row in the
contacts table then turn Cascade Delete off.
I would simply write the query out by hand but, because this is one of
the core tables, any new tables are usually linked to it and usually
kill the script (forcing yet another rewrite). Anyone have any
suggestions on what the best method would be to do this? Thanks for
your help.
AlexAlex,
Yes, you've got to tear them down in order of hierarchy.
DELETE GREATGRANDCHILDREN
DELETE GRANDCHILDREN
DELETE CHILDREN
DELETE PARENT
No shortcuts.
The good new is, once you get it written, you shouldn't have to write it
again. ;-)
James Hokes
"Alex" <under_payd@.yahoo.com> wrote in message
news:64b52496.0402231433.10fdcf5a@.posting.google.com...
> I have a contacts table that has around 30 tables related to it (which
> have other tables related to it - that often refer back to the same
> contacts table). I am trying to delete a single contact and all rows
> in any table related to it. I wrote a recursive query to scan the
> database and remove the corresponding rows before deleting the contact
> row.
> I'm not sure this is the best solution. It takes a great deal of time
> to run and seems to have problems with certain sections of the
> database. Another idea I had was to modify each of the foreign keys
> to turn on Cascade Delete whenever possibe, delete the row in the
> contacts table then turn Cascade Delete off.
> I would simply write the query out by hand but, because this is one of
> the core tables, any new tables are usually linked to it and usually
> kill the script (forcing yet another rewrite). Anyone have any
> suggestions on what the best method would be to do this? Thanks for
> your help.
> Alex
Wednesday, March 21, 2012
Deleting database backups and logs
New to SQL server. During my 'experimental' phase I created backups plans
to back up databases. Missed the part where the data would be removed after
XX time/days, can I safely delete the old TRN and BAK up file in the backup
folder located in the MSSQL directory directly from Windows Explorer?
The short answer is yes.
Rick Sawtell
MCT, MCSD, MCDBA
<msnews.microsoft.com> wrote in message
news:uZCO4EclEHA.1712@.TK2MSFTNGP09.phx.gbl...
> New to SQL server. During my 'experimental' phase I created backups plans
> to back up databases. Missed the part where the data would be removed
after
> XX time/days, can I safely delete the old TRN and BAK up file in the
backup
> folder located in the MSSQL directory directly from Windows Explorer?
>
|||You can also modify the Maintenance Plan once it has been created. In SQL
Enterprise Manager, open the "Maintenance" node. Select Maintenance Plans,
then double-click the maintenance plan that you created. In here you can
modify the properties for the maintenence plan (including the property to
delete old backups). However the answer to your question is - yes you can
delete the files manually through Explorer. These are backup files and SQL
does not hold a lock on these files once it has completed writing them out.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
to back up databases. Missed the part where the data would be removed after
XX time/days, can I safely delete the old TRN and BAK up file in the backup
folder located in the MSSQL directory directly from Windows Explorer?
The short answer is yes.
Rick Sawtell
MCT, MCSD, MCDBA
<msnews.microsoft.com> wrote in message
news:uZCO4EclEHA.1712@.TK2MSFTNGP09.phx.gbl...
> New to SQL server. During my 'experimental' phase I created backups plans
> to back up databases. Missed the part where the data would be removed
after
> XX time/days, can I safely delete the old TRN and BAK up file in the
backup
> folder located in the MSSQL directory directly from Windows Explorer?
>
|||You can also modify the Maintenance Plan once it has been created. In SQL
Enterprise Manager, open the "Maintenance" node. Select Maintenance Plans,
then double-click the maintenance plan that you created. In here you can
modify the properties for the maintenence plan (including the property to
delete old backups). However the answer to your question is - yes you can
delete the files manually through Explorer. These are backup files and SQL
does not hold a lock on these files once it has completed writing them out.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Deleting database backups and logs
New to SQL server. During my 'experimental' phase I created backups plans
to back up databases. Missed the part where the data would be removed after
XX time/days, can I safely delete the old TRN and BAK up file in the backup
folder located in the MSSQL directory directly from Windows Explorer?The short answer is yes.
Rick Sawtell
MCT, MCSD, MCDBA
<msnews.microsoft.com> wrote in message
news:uZCO4EclEHA.1712@.TK2MSFTNGP09.phx.gbl...
> New to SQL server. During my 'experimental' phase I created backups plans
> to back up databases. Missed the part where the data would be removed
after
> XX time/days, can I safely delete the old TRN and BAK up file in the
backup
> folder located in the MSSQL directory directly from Windows Explorer?
>|||You can also modify the Maintenance Plan once it has been created. In SQL
Enterprise Manager, open the "Maintenance" node. Select Maintenance Plans,
then double-click the maintenance plan that you created. In here you can
modify the properties for the maintenence plan (including the property to
delete old backups). However the answer to your question is - yes you can
delete the files manually through Explorer. These are backup files and SQL
does not hold a lock on these files once it has completed writing them out.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
to back up databases. Missed the part where the data would be removed after
XX time/days, can I safely delete the old TRN and BAK up file in the backup
folder located in the MSSQL directory directly from Windows Explorer?The short answer is yes.
Rick Sawtell
MCT, MCSD, MCDBA
<msnews.microsoft.com> wrote in message
news:uZCO4EclEHA.1712@.TK2MSFTNGP09.phx.gbl...
> New to SQL server. During my 'experimental' phase I created backups plans
> to back up databases. Missed the part where the data would be removed
after
> XX time/days, can I safely delete the old TRN and BAK up file in the
backup
> folder located in the MSSQL directory directly from Windows Explorer?
>|||You can also modify the Maintenance Plan once it has been created. In SQL
Enterprise Manager, open the "Maintenance" node. Select Maintenance Plans,
then double-click the maintenance plan that you created. In here you can
modify the properties for the maintenence plan (including the property to
delete old backups). However the answer to your question is - yes you can
delete the files manually through Explorer. These are backup files and SQL
does not hold a lock on these files once it has completed writing them out.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Sunday, March 11, 2012
deleting a backup device
I have created two backup devices for a database: 1 for the data and 1 for the logs. I back up transaction logs to the device over the course of a day and then do a differential backup for the data nightly.
Once a week, I do a full backup of the database. After the full backup is done, I want to delete the log file device since it's no longer needed. I've done it before, but haven't in a while and can't remember the syntax in the job step setup. Can someone please refresh me?
I know it's a CmdExec job, but the syntax of the step is eluding my memory.
Thanks for any help.just bumping for assistance.|||re-bumping... any assistance would be greatly appreciated.|||why delete the device? don't you want to leave it there to use it again. just set the backup log init to overwrite the previous log file.
Friday, March 9, 2012
Deleted Builtin\Administrator group now can’t login
As the title says I stupidly deleted the Builtin\Administrator group now can’t login. Is there anyway get back in?
I did not setup the server up so I’m unsure what the SA password is. As a last resort could I rebuild the Master database and then over write if backup?
As yourself say i think it is the solution (use same install disk).
I didn't try a way :
-restoring master backup of your situation to other server
-stop de service, copy mdf and ldf of master of that server,
-stop de service of problem server
-replace files of master and startup the service
|||Would this work?
Well I won't make this mistake again
Thanks,
Alan
|||What version of SQL Server are you using? For SQL Server 2005, a machine admin can connect by starting the server in single-user mode.
For SQL Server 2000, you could also enable mixed-mode authentication and connect as sa, if you know its password.
Thanks
Laurentiu
|||I am adding a link to the blog article describing how to recover from this situation on SQL Server 2005 (it describes the mechanism that Laurentiu mentioned):
http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
|||
Thanks Raul. This worked great
Wednesday, March 7, 2012
Deleted .LDF File how can the database be brought back online
Ok this morning one of the IT guys deleted the LDF file
from the server for a particular database. The file was
45 gig, he stopped the server then deleted the file,
without detaching the database.
We tried running sp_attach_db @.dbname='', @.physname=''
filling in the blanks but that refused to work.
We still have the 65 gig MDF file but we get error 9004
when we try to attach using enterprise manager.
We saw a knowledge base article saying to install service
pack 3a will this solve the problem, don't necessarily
want to mess about with these things unless absolutely
necessary
I will be very grateful is someone tells me how to sort
this and doesn't say i'm up the creek without a paddle
thanks in advanceYou can try sp_attach_single_file_db instead of sp_attach_db. That is still
not guaranteed to work in this scenario though, it is only _guaranteed_ to
work if you explicitly detach the database, but you might be lucky.
Otherwise you will have to restore from the back up.
--
Jacco Schalkwijk
SQL Server MVP
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance|||You also need to seriously suggest firing that IT guy...What he's done
really pissed off DBAs!
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance|||Common error. Look at the suggestion by Jasper Smith here
(http://tinyurl.com/5g2ol). Note that rebuilding the log this way is not
supported by Microsoft, and you should make a copy of the mdf file just in
case it does not work for you. Good luck.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance|||> You also need to seriously suggest firing that IT guy...What he's done
> really pissed off DBAs!
Or fire the IT manager who allow for non-trained personnel have this kind or privileges on a database
server... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:eiYHCg2jEHA.4068@.TK2MSFTNGP10.phx.gbl...
> You also need to seriously suggest firing that IT guy...What he's done
> really pissed off DBAs!
>
> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
> news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> > Ok this morning one of the IT guys deleted the LDF file
> > from the server for a particular database. The file was
> > 45 gig, he stopped the server then deleted the file,
> > without detaching the database.
> >
> > We tried running sp_attach_db @.dbname='', @.physname=''
> >
> > filling in the blanks but that refused to work.
> >
> > We still have the 65 gig MDF file but we get error 9004
> > when we try to attach using enterprise manager.
> >
> > We saw a knowledge base article saying to install service
> > pack 3a will this solve the problem, don't necessarily
> > want to mess about with these things unless absolutely
> > necessary
> >
> > I will be very grateful is someone tells me how to sort
> > this and doesn't say i'm up the creek without a paddle
> >
> > thanks in advance
>|||I wouldn't sack anyone for pissing of DBAs. Putting 65 GB of business data
at risk is a different matter though... not to mention the downtime and the
extra work involved.
--
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWLfBT4jEHA.1404@.TK2MSFTNGP09.phx.gbl...
>> You also need to seriously suggest firing that IT guy...What he's done
>> really pissed off DBAs!
> Or fire the IT manager who allow for non-trained personnel have this kind
> or privileges on a database
> server... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Richard Ding" <rding@.acadian-asset.com> wrote in message
> news:eiYHCg2jEHA.4068@.TK2MSFTNGP10.phx.gbl...
>> You also need to seriously suggest firing that IT guy...What he's done
>> really pissed off DBAs!
>>
>> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
>> news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
>> > Ok this morning one of the IT guys deleted the LDF file
>> > from the server for a particular database. The file was
>> > 45 gig, he stopped the server then deleted the file,
>> > without detaching the database.
>> >
>> > We tried running sp_attach_db @.dbname='', @.physname=''
>> >
>> > filling in the blanks but that refused to work.
>> >
>> > We still have the 65 gig MDF file but we get error 9004
>> > when we try to attach using enterprise manager.
>> >
>> > We saw a knowledge base article saying to install service
>> > pack 3a will this solve the problem, don't necessarily
>> > want to mess about with these things unless absolutely
>> > necessary
>> >
>> > I will be very grateful is someone tells me how to sort
>> > this and doesn't say i'm up the creek without a paddle
>> >
>> > thanks in advance
>>
>|||Well they managed to get it up and running with only a 2 week loss of data,
so that was good but they didnt get any sleep doing it, got it running the
next morning, not sure how they did it, but they did, luckily
think the backups are been re-thinked, lol
A day researching was different i s'pose
thanks for the suggestions
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:ekAqO$2jEHA.596@.TK2MSFTNGP11.phx.gbl...
> Common error. Look at the suggestion by Jasper Smith here
> (http://tinyurl.com/5g2ol). Note that rebuilding the log this way is not
> supported by Microsoft, and you should make a copy of the mdf file just in
> case it does not work for you. Good luck.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
>
> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
> news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> > Ok this morning one of the IT guys deleted the LDF file
> > from the server for a particular database. The file was
> > 45 gig, he stopped the server then deleted the file,
> > without detaching the database.
> >
> > We tried running sp_attach_db @.dbname='', @.physname=''
> >
> > filling in the blanks but that refused to work.
> >
> > We still have the 65 gig MDF file but we get error 9004
> > when we try to attach using enterprise manager.
> >
> > We saw a knowledge base article saying to install service
> > pack 3a will this solve the problem, don't necessarily
> > want to mess about with these things unless absolutely
> > necessary
> >
> > I will be very grateful is someone tells me how to sort
> > this and doesn't say i'm up the creek without a paddle
> >
> > thanks in advance
>
from the server for a particular database. The file was
45 gig, he stopped the server then deleted the file,
without detaching the database.
We tried running sp_attach_db @.dbname='', @.physname=''
filling in the blanks but that refused to work.
We still have the 65 gig MDF file but we get error 9004
when we try to attach using enterprise manager.
We saw a knowledge base article saying to install service
pack 3a will this solve the problem, don't necessarily
want to mess about with these things unless absolutely
necessary
I will be very grateful is someone tells me how to sort
this and doesn't say i'm up the creek without a paddle
thanks in advanceYou can try sp_attach_single_file_db instead of sp_attach_db. That is still
not guaranteed to work in this scenario though, it is only _guaranteed_ to
work if you explicitly detach the database, but you might be lucky.
Otherwise you will have to restore from the back up.
--
Jacco Schalkwijk
SQL Server MVP
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance|||You also need to seriously suggest firing that IT guy...What he's done
really pissed off DBAs!
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance|||Common error. Look at the suggestion by Jasper Smith here
(http://tinyurl.com/5g2ol). Note that rebuilding the log this way is not
supported by Microsoft, and you should make a copy of the mdf file just in
case it does not work for you. Good luck.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance|||> You also need to seriously suggest firing that IT guy...What he's done
> really pissed off DBAs!
Or fire the IT manager who allow for non-trained personnel have this kind or privileges on a database
server... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:eiYHCg2jEHA.4068@.TK2MSFTNGP10.phx.gbl...
> You also need to seriously suggest firing that IT guy...What he's done
> really pissed off DBAs!
>
> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
> news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> > Ok this morning one of the IT guys deleted the LDF file
> > from the server for a particular database. The file was
> > 45 gig, he stopped the server then deleted the file,
> > without detaching the database.
> >
> > We tried running sp_attach_db @.dbname='', @.physname=''
> >
> > filling in the blanks but that refused to work.
> >
> > We still have the 65 gig MDF file but we get error 9004
> > when we try to attach using enterprise manager.
> >
> > We saw a knowledge base article saying to install service
> > pack 3a will this solve the problem, don't necessarily
> > want to mess about with these things unless absolutely
> > necessary
> >
> > I will be very grateful is someone tells me how to sort
> > this and doesn't say i'm up the creek without a paddle
> >
> > thanks in advance
>|||I wouldn't sack anyone for pissing of DBAs. Putting 65 GB of business data
at risk is a different matter though... not to mention the downtime and the
extra work involved.
--
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWLfBT4jEHA.1404@.TK2MSFTNGP09.phx.gbl...
>> You also need to seriously suggest firing that IT guy...What he's done
>> really pissed off DBAs!
> Or fire the IT manager who allow for non-trained personnel have this kind
> or privileges on a database
> server... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Richard Ding" <rding@.acadian-asset.com> wrote in message
> news:eiYHCg2jEHA.4068@.TK2MSFTNGP10.phx.gbl...
>> You also need to seriously suggest firing that IT guy...What he's done
>> really pissed off DBAs!
>>
>> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
>> news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
>> > Ok this morning one of the IT guys deleted the LDF file
>> > from the server for a particular database. The file was
>> > 45 gig, he stopped the server then deleted the file,
>> > without detaching the database.
>> >
>> > We tried running sp_attach_db @.dbname='', @.physname=''
>> >
>> > filling in the blanks but that refused to work.
>> >
>> > We still have the 65 gig MDF file but we get error 9004
>> > when we try to attach using enterprise manager.
>> >
>> > We saw a knowledge base article saying to install service
>> > pack 3a will this solve the problem, don't necessarily
>> > want to mess about with these things unless absolutely
>> > necessary
>> >
>> > I will be very grateful is someone tells me how to sort
>> > this and doesn't say i'm up the creek without a paddle
>> >
>> > thanks in advance
>>
>|||Well they managed to get it up and running with only a 2 week loss of data,
so that was good but they didnt get any sleep doing it, got it running the
next morning, not sure how they did it, but they did, luckily
think the backups are been re-thinked, lol
A day researching was different i s'pose
thanks for the suggestions
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:ekAqO$2jEHA.596@.TK2MSFTNGP11.phx.gbl...
> Common error. Look at the suggestion by Jasper Smith here
> (http://tinyurl.com/5g2ol). Note that rebuilding the log this way is not
> supported by Microsoft, and you should make a copy of the mdf file just in
> case it does not work for you. Good luck.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
>
> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
> news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> > Ok this morning one of the IT guys deleted the LDF file
> > from the server for a particular database. The file was
> > 45 gig, he stopped the server then deleted the file,
> > without detaching the database.
> >
> > We tried running sp_attach_db @.dbname='', @.physname=''
> >
> > filling in the blanks but that refused to work.
> >
> > We still have the 65 gig MDF file but we get error 9004
> > when we try to attach using enterprise manager.
> >
> > We saw a knowledge base article saying to install service
> > pack 3a will this solve the problem, don't necessarily
> > want to mess about with these things unless absolutely
> > necessary
> >
> > I will be very grateful is someone tells me how to sort
> > this and doesn't say i'm up the creek without a paddle
> >
> > thanks in advance
>
Deleted .LDF File how can the database be brought back online
Ok this morning one of the IT guys deleted the LDF file
from the server for a particular database. The file was
45 gig, he stopped the server then deleted the file,
without detaching the database.
We tried running sp_attach_db @.dbname='', @.physname=''
filling in the blanks but that refused to work.
We still have the 65 gig MDF file but we get error 9004
when we try to attach using enterprise manager.
We saw a knowledge base article saying to install service
pack 3a will this solve the problem, don't necessarily
want to mess about with these things unless absolutely
necessary
I will be very grateful is someone tells me how to sort
this and doesn't say i'm up the creek without a paddle
thanks in advanceYou can try sp_attach_single_file_db instead of sp_attach_db. That is still
not guaranteed to work in this scenario though, it is only _guaranteed_ to
work if you explicitly detach the database, but you might be lucky.
Otherwise you will have to restore from the back up.
Jacco Schalkwijk
SQL Server MVP
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance|||You also need to seriously suggest firing that IT guy...What he's done
really pissed off DBAs!
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance|||Common error. Look at the suggestion by Jasper Smith here
(http://tinyurl.com/5g2ol). Note that rebuilding the log this way is not
supported by Microsoft, and you should make a copy of the mdf file just in
case it does not work for you. Good luck.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance|||> You also need to seriously suggest firing that IT guy...What he's done
> really pissed off DBAs!
Or fire the IT manager who allow for non-trained personnel have this kind or
privileges on a database
server... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:eiYHCg2jEHA.4068@.TK2MSFTNGP10
.phx.gbl...
> You also need to seriously suggest firing that IT guy...What he's done
> really pissed off DBAs!
>
> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
> news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
>|||I wouldn't sack anyone for pissing of DBAs. Putting 65 GB of business data
at risk is a different matter though... not to mention the downtime and the
extra work involved.
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWLfBT4jEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Or fire the IT manager who allow for non-trained personnel have this kind
> or privileges on a database
> server... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Richard Ding" <rding@.acadian-asset.com> wrote in message
> news:eiYHCg2jEHA.4068@.TK2MSFTNGP10.phx.gbl...
>|||Well they managed to get it up and running with only a 2 week loss of data,
so that was good but they didnt get any sleep doing it, got it running the
next morning, not sure how they did it, but they did, luckily
think the backups are been re-thinked, lol
A day researching was different i s'pose
thanks for the suggestions
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:ekAqO$2jEHA.596@.TK2MSFTNGP11.phx.gbl...
> Common error. Look at the suggestion by Jasper Smith here
> (http://tinyurl.com/5g2ol). Note that rebuilding the log this way is not
> supported by Microsoft, and you should make a copy of the mdf file just in
> case it does not work for you. Good luck.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
>
> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
> news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
>
from the server for a particular database. The file was
45 gig, he stopped the server then deleted the file,
without detaching the database.
We tried running sp_attach_db @.dbname='', @.physname=''
filling in the blanks but that refused to work.
We still have the 65 gig MDF file but we get error 9004
when we try to attach using enterprise manager.
We saw a knowledge base article saying to install service
pack 3a will this solve the problem, don't necessarily
want to mess about with these things unless absolutely
necessary
I will be very grateful is someone tells me how to sort
this and doesn't say i'm up the creek without a paddle
thanks in advanceYou can try sp_attach_single_file_db instead of sp_attach_db. That is still
not guaranteed to work in this scenario though, it is only _guaranteed_ to
work if you explicitly detach the database, but you might be lucky.
Otherwise you will have to restore from the back up.
Jacco Schalkwijk
SQL Server MVP
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance|||You also need to seriously suggest firing that IT guy...What he's done
really pissed off DBAs!
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance|||Common error. Look at the suggestion by Jasper Smith here
(http://tinyurl.com/5g2ol). Note that rebuilding the log this way is not
supported by Microsoft, and you should make a copy of the mdf file just in
case it does not work for you. Good luck.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance|||> You also need to seriously suggest firing that IT guy...What he's done
> really pissed off DBAs!
Or fire the IT manager who allow for non-trained personnel have this kind or
privileges on a database
server... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:eiYHCg2jEHA.4068@.TK2MSFTNGP10
.phx.gbl...
> You also need to seriously suggest firing that IT guy...What he's done
> really pissed off DBAs!
>
> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
> news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
>|||I wouldn't sack anyone for pissing of DBAs. Putting 65 GB of business data
at risk is a different matter though... not to mention the downtime and the
extra work involved.
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWLfBT4jEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Or fire the IT manager who allow for non-trained personnel have this kind
> or privileges on a database
> server... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Richard Ding" <rding@.acadian-asset.com> wrote in message
> news:eiYHCg2jEHA.4068@.TK2MSFTNGP10.phx.gbl...
>|||Well they managed to get it up and running with only a 2 week loss of data,
so that was good but they didnt get any sleep doing it, got it running the
next morning, not sure how they did it, but they did, luckily
think the backups are been re-thinked, lol
A day researching was different i s'pose
thanks for the suggestions
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:ekAqO$2jEHA.596@.TK2MSFTNGP11.phx.gbl...
> Common error. Look at the suggestion by Jasper Smith here
> (http://tinyurl.com/5g2ol). Note that rebuilding the log this way is not
> supported by Microsoft, and you should make a copy of the mdf file just in
> case it does not work for you. Good luck.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
>
> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
> news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
>
Deleted .LDF File how can the database be brought back online
Ok this morning one of the IT guys deleted the LDF file
from the server for a particular database. The file was
45 gig, he stopped the server then deleted the file,
without detaching the database.
We tried running sp_attach_db @.dbname='', @.physname=''
filling in the blanks but that refused to work.
We still have the 65 gig MDF file but we get error 9004
when we try to attach using enterprise manager.
We saw a knowledge base article saying to install service
pack 3a will this solve the problem, don't necessarily
want to mess about with these things unless absolutely
necessary
I will be very grateful is someone tells me how to sort
this and doesn't say i'm up the creek without a paddle
thanks in advance
You can try sp_attach_single_file_db instead of sp_attach_db. That is still
not guaranteed to work in this scenario though, it is only _guaranteed_ to
work if you explicitly detach the database, but you might be lucky.
Otherwise you will have to restore from the back up.
Jacco Schalkwijk
SQL Server MVP
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance
|||You also need to seriously suggest firing that IT guy...What he's done
really pissed off DBAs!
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance
|||Common error. Look at the suggestion by Jasper Smith here
(http://tinyurl.com/5g2ol). Note that rebuilding the log this way is not
supported by Microsoft, and you should make a copy of the mdf file just in
case it does not work for you. Good luck.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance
|||> You also need to seriously suggest firing that IT guy...What he's done
> really pissed off DBAs!
Or fire the IT manager who allow for non-trained personnel have this kind or privileges on a database
server... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:eiYHCg2jEHA.4068@.TK2MSFTNGP10.phx.gbl...
> You also need to seriously suggest firing that IT guy...What he's done
> really pissed off DBAs!
>
> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
> news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
>
|||I wouldn't sack anyone for pissing of DBAs. Putting 65 GB of business data
at risk is a different matter though... not to mention the downtime and the
extra work involved.
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWLfBT4jEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Or fire the IT manager who allow for non-trained personnel have this kind
> or privileges on a database
> server... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Richard Ding" <rding@.acadian-asset.com> wrote in message
> news:eiYHCg2jEHA.4068@.TK2MSFTNGP10.phx.gbl...
>
|||Well they managed to get it up and running with only a 2 week loss of data,
so that was good but they didnt get any sleep doing it, got it running the
next morning, not sure how they did it, but they did, luckily
think the backups are been re-thinked, lol
A day researching was different i s'pose
thanks for the suggestions
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:ekAqO$2jEHA.596@.TK2MSFTNGP11.phx.gbl...
> Common error. Look at the suggestion by Jasper Smith here
> (http://tinyurl.com/5g2ol). Note that rebuilding the log this way is not
> supported by Microsoft, and you should make a copy of the mdf file just in
> case it does not work for you. Good luck.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
>
> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
> news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
>
from the server for a particular database. The file was
45 gig, he stopped the server then deleted the file,
without detaching the database.
We tried running sp_attach_db @.dbname='', @.physname=''
filling in the blanks but that refused to work.
We still have the 65 gig MDF file but we get error 9004
when we try to attach using enterprise manager.
We saw a knowledge base article saying to install service
pack 3a will this solve the problem, don't necessarily
want to mess about with these things unless absolutely
necessary
I will be very grateful is someone tells me how to sort
this and doesn't say i'm up the creek without a paddle
thanks in advance
You can try sp_attach_single_file_db instead of sp_attach_db. That is still
not guaranteed to work in this scenario though, it is only _guaranteed_ to
work if you explicitly detach the database, but you might be lucky.
Otherwise you will have to restore from the back up.
Jacco Schalkwijk
SQL Server MVP
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance
|||You also need to seriously suggest firing that IT guy...What he's done
really pissed off DBAs!
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance
|||Common error. Look at the suggestion by Jasper Smith here
(http://tinyurl.com/5g2ol). Note that rebuilding the log this way is not
supported by Microsoft, and you should make a copy of the mdf file just in
case it does not work for you. Good luck.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
> Ok this morning one of the IT guys deleted the LDF file
> from the server for a particular database. The file was
> 45 gig, he stopped the server then deleted the file,
> without detaching the database.
> We tried running sp_attach_db @.dbname='', @.physname=''
> filling in the blanks but that refused to work.
> We still have the 65 gig MDF file but we get error 9004
> when we try to attach using enterprise manager.
> We saw a knowledge base article saying to install service
> pack 3a will this solve the problem, don't necessarily
> want to mess about with these things unless absolutely
> necessary
> I will be very grateful is someone tells me how to sort
> this and doesn't say i'm up the creek without a paddle
> thanks in advance
|||> You also need to seriously suggest firing that IT guy...What he's done
> really pissed off DBAs!
Or fire the IT manager who allow for non-trained personnel have this kind or privileges on a database
server... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:eiYHCg2jEHA.4068@.TK2MSFTNGP10.phx.gbl...
> You also need to seriously suggest firing that IT guy...What he's done
> really pissed off DBAs!
>
> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
> news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
>
|||I wouldn't sack anyone for pissing of DBAs. Putting 65 GB of business data
at risk is a different matter though... not to mention the downtime and the
extra work involved.
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWLfBT4jEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Or fire the IT manager who allow for non-trained personnel have this kind
> or privileges on a database
> server... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Richard Ding" <rding@.acadian-asset.com> wrote in message
> news:eiYHCg2jEHA.4068@.TK2MSFTNGP10.phx.gbl...
>
|||Well they managed to get it up and running with only a 2 week loss of data,
so that was good but they didnt get any sleep doing it, got it running the
next morning, not sure how they did it, but they did, luckily
think the backups are been re-thinked, lol
A day researching was different i s'pose
thanks for the suggestions
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:ekAqO$2jEHA.596@.TK2MSFTNGP11.phx.gbl...
> Common error. Look at the suggestion by Jasper Smith here
> (http://tinyurl.com/5g2ol). Note that rebuilding the log this way is not
> supported by Microsoft, and you should make a copy of the mdf file just in
> case it does not work for you. Good luck.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
>
> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
> news:08b501c48f52$f1c64690$a401280a@.phx.gbl...
>
Delete's Value
Has anyone ever had an issue when they try to commit changes back to the database based on a value they enter in a application...the value that u would use (i.e Select * From Customers Where CustomerID = 'value') to get the rest of the value's pertaining to that value you enter gets deleted from the database....its really wierd but i have that problemYou mean that rows have been deleted by selecting them ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||I mean the one value i enter in my form gets deleted all the information from that row stay's except for the search value that i use...|||
"My form" is not really descriptive enough. Is this a tool issue? Or a SQL issue with code you have written to support a form?
If it is SQL, can you post some of the table structures? If it is the former, I would probably suggest you use profiler to get a view of the queries being sent and try to match that to what you expect to happen.
|||How would i use the SQL profiler to see whats being sent...I have not used it before but ill check the net too...thanksSunday, February 19, 2012
Delete records permission
Hello,
I have a couple of tables in a SQL Server 2005 database back end and I want
to delete some records through a query from a MS
Access 2003 front end. I get an error in MS Access saying "Could not delete
from specified tables". There are multiple users of the
back end and each one logs in to SQL Server using Windows Authentication. Ea
ch user has the db_datareader and db_datawriter
permissions checked for the database role membership. SELECT queries work an
d I can see the contents of the tables but the DELETE
query cannot run from MS Access. I have a feeling I need to set up more perm
issions. Does anyone know how to get this working?
Cheers,
Max.Max,
Does the table you are trying to delete from have a primary key? If not, you
may need to add one.
-- Bill
"Max" <maxy_100@.yahoo.com> wrote in message
news:OyWc3GOMHHA.140@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a couple of tables in a SQL Server 2005 database back end and I
> want to delete some records through a query from a MS
> Access 2003 front end. I get an error in MS Access saying "Could not
> delete from specified tables". There are multiple users of the
> back end and each one logs in to SQL Server using Windows Authentication.
> Each user has the db_datareader and db_datawriter
> permissions checked for the database role membership. SELECT queries work
> and I can see the contents of the tables but the DELETE
> query cannot run from MS Access. I have a feeling I need to set up more
> permissions. Does anyone know how to get this working?
> Cheers,
> Max.
>
I have a couple of tables in a SQL Server 2005 database back end and I want
to delete some records through a query from a MS
Access 2003 front end. I get an error in MS Access saying "Could not delete
from specified tables". There are multiple users of the
back end and each one logs in to SQL Server using Windows Authentication. Ea
ch user has the db_datareader and db_datawriter
permissions checked for the database role membership. SELECT queries work an
d I can see the contents of the tables but the DELETE
query cannot run from MS Access. I have a feeling I need to set up more perm
issions. Does anyone know how to get this working?
Cheers,
Max.Max,
Does the table you are trying to delete from have a primary key? If not, you
may need to add one.
-- Bill
"Max" <maxy_100@.yahoo.com> wrote in message
news:OyWc3GOMHHA.140@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a couple of tables in a SQL Server 2005 database back end and I
> want to delete some records through a query from a MS
> Access 2003 front end. I get an error in MS Access saying "Could not
> delete from specified tables". There are multiple users of the
> back end and each one logs in to SQL Server using Windows Authentication.
> Each user has the db_datareader and db_datawriter
> permissions checked for the database role membership. SELECT queries work
> and I can see the contents of the tables but the DELETE
> query cannot run from MS Access. I have a feeling I need to set up more
> permissions. Does anyone know how to get this working?
> Cheers,
> Max.
>
Delete records permission
Hello,
I have a couple of tables in a SQL Server 2005 database back end and I want to delete some records through a query from a MS
Access 2003 front end. I get an error in MS Access saying "Could not delete from specified tables". There are multiple users of the
back end and each one logs in to SQL Server using Windows Authentication. Each user has the db_datareader and db_datawriter
permissions checked for the database role membership. SELECT queries work and I can see the contents of the tables but the DELETE
query cannot run from MS Access. I have a feeling I need to set up more permissions. Does anyone know how to get this working?
Cheers,
Max.Max,
Does the table you are trying to delete from have a primary key? If not, you
may need to add one.
-- Bill
"Max" <maxy_100@.yahoo.com> wrote in message
news:OyWc3GOMHHA.140@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a couple of tables in a SQL Server 2005 database back end and I
> want to delete some records through a query from a MS
> Access 2003 front end. I get an error in MS Access saying "Could not
> delete from specified tables". There are multiple users of the
> back end and each one logs in to SQL Server using Windows Authentication.
> Each user has the db_datareader and db_datawriter
> permissions checked for the database role membership. SELECT queries work
> and I can see the contents of the tables but the DELETE
> query cannot run from MS Access. I have a feeling I need to set up more
> permissions. Does anyone know how to get this working?
> Cheers,
> Max.
>
I have a couple of tables in a SQL Server 2005 database back end and I want to delete some records through a query from a MS
Access 2003 front end. I get an error in MS Access saying "Could not delete from specified tables". There are multiple users of the
back end and each one logs in to SQL Server using Windows Authentication. Each user has the db_datareader and db_datawriter
permissions checked for the database role membership. SELECT queries work and I can see the contents of the tables but the DELETE
query cannot run from MS Access. I have a feeling I need to set up more permissions. Does anyone know how to get this working?
Cheers,
Max.Max,
Does the table you are trying to delete from have a primary key? If not, you
may need to add one.
-- Bill
"Max" <maxy_100@.yahoo.com> wrote in message
news:OyWc3GOMHHA.140@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a couple of tables in a SQL Server 2005 database back end and I
> want to delete some records through a query from a MS
> Access 2003 front end. I get an error in MS Access saying "Could not
> delete from specified tables". There are multiple users of the
> back end and each one logs in to SQL Server using Windows Authentication.
> Each user has the db_datareader and db_datawriter
> permissions checked for the database role membership. SELECT queries work
> and I can see the contents of the tables but the DELETE
> query cannot run from MS Access. I have a feeling I need to set up more
> permissions. Does anyone know how to get this working?
> Cheers,
> Max.
>
Subscribe to:
Posts (Atom)