MDF Files currupted: delete

Showing posts with label delete. Show all posts

Monday, March 19, 2012

Mystery Table Delete!

I'm hoping someone out there can help me solve a mystery!
29 tables in our live database were deleted the other day
and we've no real idea how this could have happened.
We have found an entry in Application Event Viewer that
happened at about the same time as the tables vanished,
but we don't really understand what it means:
EventID: 17055
Categorey: (6)
Description: 3041: BACKUP failed to complete the command
dump tran [Claims] with no_log
I understand that this is saying that the command failed,
but where did the command come from? The user on the
event was creating new tables and replacing stored
procedures in the database at the time, however, [Claims]
is the name of the whole database!
We have recovered from the disaster now but really do need
to understand what has happened here, how it happened, and
how we can ensure it never happens again in the future.
Thanks in advance for any help.
Regards
PaulaCheck your web server logs, if there is web application connected. Check
your security logs...
"Pegasus" <anonymous@.discussions.microsoft.com> wrote in message
news:212c301c45a9e$73c9e3c0$a301280a@.phx.gbl...
> I'm hoping someone out there can help me solve a mystery!
> 29 tables in our live database were deleted the other day
> and we've no real idea how this could have happened.
> We have found an entry in Application Event Viewer that
> happened at about the same time as the tables vanished,
> but we don't really understand what it means:
> EventID: 17055
> Categorey: (6)
> Description: 3041: BACKUP failed to complete the command
> dump tran [Claims] with no_log
> I understand that this is saying that the command failed,
> but where did the command come from? The user on the
> event was creating new tables and replacing stored
> procedures in the database at the time, however, [Claims]
> is the name of the whole database!
> We have recovered from the disaster now but really do need
> to understand what has happened here, how it happened, and
> how we can ensure it never happens again in the future.
> Thanks in advance for any help.
> Regards
> Paula|||There is no web application connected (to my knowledge)
and nothing is showing in the security logs.
Using SQL Server 2000.
Has been suggested that using Enterprise Manager and the
DTS Import/Export Wizard to move objects could have caused
the tables to be dropped? Any value in this theory?
>--Original Message--
>Check your web server logs, if there is web application
connected. Check
>your security logs...
>"Pegasus" <anonymous@.discussions.microsoft.com> wrote in
message
>news:212c301c45a9e$73c9e3c0$a301280a@.phx.gbl...
>> I'm hoping someone out there can help me solve a
mystery!
>> 29 tables in our live database were deleted the other
day
>> and we've no real idea how this could have happened.
>> We have found an entry in Application Event Viewer that
>> happened at about the same time as the tables vanished,
>> but we don't really understand what it means:
>> EventID: 17055
>> Categorey: (6)
>> Description: 3041: BACKUP failed to complete the command
>> dump tran [Claims] with no_log
>> I understand that this is saying that the command
failed,
>> but where did the command come from? The user on the
>> event was creating new tables and replacing stored
>> procedures in the database at the time, however,
[Claims]
>> is the name of the whole database!
>> We have recovered from the disaster now but really do
need
>> to understand what has happened here, how it happened,
and
>> how we can ensure it never happens again in the future.
>> Thanks in advance for any help.
>> Regards
>> Paula
>
>.
>|||Probably the best way to see might be to get the tool from Lumigent (
www.lumigent.com)...
Give them a call and see if the tool will help in your situation.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Pegasus" <anonymous@.discussions.microsoft.com> wrote in message
news:212c301c45a9e$73c9e3c0$a301280a@.phx.gbl...
> I'm hoping someone out there can help me solve a mystery!
> 29 tables in our live database were deleted the other day
> and we've no real idea how this could have happened.
> We have found an entry in Application Event Viewer that
> happened at about the same time as the tables vanished,
> but we don't really understand what it means:
> EventID: 17055
> Categorey: (6)
> Description: 3041: BACKUP failed to complete the command
> dump tran [Claims] with no_log
> I understand that this is saying that the command failed,
> but where did the command come from? The user on the
> event was creating new tables and replacing stored
> procedures in the database at the time, however, [Claims]
> is the name of the whole database!
> We have recovered from the disaster now but really do need
> to understand what has happened here, how it happened, and
> how we can ensure it never happens again in the future.
> Thanks in advance for any help.
> Regards
> Paula|||Try a log tool like LogExplorer from www.lumigent.com or ApexSQL Log from
www.apexsql.com to trawl trough your transaction logs and see what happened.
--
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:216db01c45aac$4cc30320$a401280a@.phx.gbl...
> There is no web application connected (to my knowledge)
> and nothing is showing in the security logs.
> Using SQL Server 2000.
> Has been suggested that using Enterprise Manager and the
> DTS Import/Export Wizard to move objects could have caused
> the tables to be dropped? Any value in this theory?
>
> >--Original Message--
> >Check your web server logs, if there is web application
> connected. Check
> >your security logs...
> >
> >"Pegasus" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:212c301c45a9e$73c9e3c0$a301280a@.phx.gbl...
> >> I'm hoping someone out there can help me solve a
> mystery!
> >>
> >> 29 tables in our live database were deleted the other
> day
> >> and we've no real idea how this could have happened.
> >>
> >> We have found an entry in Application Event Viewer that
> >> happened at about the same time as the tables vanished,
> >> but we don't really understand what it means:
> >> EventID: 17055
> >> Categorey: (6)
> >> Description: 3041: BACKUP failed to complete the command
> >> dump tran [Claims] with no_log
> >>
> >> I understand that this is saying that the command
> failed,
> >> but where did the command come from? The user on the
> >> event was creating new tables and replacing stored
> >> procedures in the database at the time, however,
> [Claims]
> >> is the name of the whole database!
> >>
> >> We have recovered from the disaster now but really do
> need
> >> to understand what has happened here, how it happened,
> and
> >> how we can ensure it never happens again in the future.
> >>
> >> Thanks in advance for any help.
> >>
> >> Regards
> >>
> >> Paula
> >
> >
> >.
> >|||Hi,
There is only Copy objects option in DTS and this will just copy the objects
to destination.
What recovery model you are using for this database, If it is FULL or
Bulk_logged and if you have trasnaction logs you can use the Log explorer
tool to analyze the situation which caused the object drop.
www.lumigent.com
If you do not have the transaction log backup, it is very difficult to find
the cause for the object drop.
--
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:216db01c45aac$4cc30320$a401280a@.phx.gbl...
> There is no web application connected (to my knowledge)
> and nothing is showing in the security logs.
> Using SQL Server 2000.
> Has been suggested that using Enterprise Manager and the
> DTS Import/Export Wizard to move objects could have caused
> the tables to be dropped? Any value in this theory?
>
> >--Original Message--
> >Check your web server logs, if there is web application
> connected. Check
> >your security logs...
> >
> >"Pegasus" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:212c301c45a9e$73c9e3c0$a301280a@.phx.gbl...
> >> I'm hoping someone out there can help me solve a
> mystery!
> >>
> >> 29 tables in our live database were deleted the other
> day
> >> and we've no real idea how this could have happened.
> >>
> >> We have found an entry in Application Event Viewer that
> >> happened at about the same time as the tables vanished,
> >> but we don't really understand what it means:
> >> EventID: 17055
> >> Categorey: (6)
> >> Description: 3041: BACKUP failed to complete the command
> >> dump tran [Claims] with no_log
> >>
> >> I understand that this is saying that the command
> failed,
> >> but where did the command come from? The user on the
> >> event was creating new tables and replacing stored
> >> procedures in the database at the time, however,
> [Claims]
> >> is the name of the whole database!
> >>
> >> We have recovered from the disaster now but really do
> need
> >> to understand what has happened here, how it happened,
> and
> >> how we can ensure it never happens again in the future.
> >>
> >> Thanks in advance for any help.
> >>
> >> Regards
> >>
> >> Paula
> >
> >
> >.
> >|||Thanks for your input. I have already got information
about LogExplorer from www.lumigent.com and we're looking
into purchasing this product for use in the future.
Unfortunately the log file no longer exists (don't ask!!!)
so this software would be of no use on this occasion. Is
there any other way forward on this?
Could the DTS Import/Export Wizard have caused this much
destruction by copying over 2 new tables and 3 existing
stored procedures from a development copy of the
database? The user insists that the objects were not
transferred to the live database using this Wizard. Even
if "dependant objects" was selected, could there be a
scenario where SQL would have dropped all tables
referenced in the SP's and all tables related to those
tables? If this is possible, can we disable the use of
DTS Import/Export wizard in Enterprise Manager for ALL
users?
>--Original Message--
>I'm hoping someone out there can help me solve a mystery!
>29 tables in our live database were deleted the other day
>and we've no real idea how this could have happened.
>We have found an entry in Application Event Viewer that
>happened at about the same time as the tables vanished,
>but we don't really understand what it means:
>EventID: 17055
>Categorey: (6)
>Description: 3041: BACKUP failed to complete the command
>dump tran [Claims] with no_log
>I understand that this is saying that the command failed,
>but where did the command come from? The user on the
>event was creating new tables and replacing stored
>procedures in the database at the time, however, [Claims]
>is the name of the whole database!
>We have recovered from the disaster now but really do
need
>to understand what has happened here, how it happened,
and
>how we can ensure it never happens again in the future.
>Thanks in advance for any help.
>Regards
>Paula
>.
>|||Thanks for your input.
No, we don't have the transaction log or a backup of it
(don't ask!!!) Is there another way to see what's
happened? There were 29 tables dropped in all so this
had very serious consequences and now we're trying to
ensure this doesn't happen again, but, without knowing
what DID happen we can't safeguard against it happening
again.
>--Original Message--
>Hi,
>There is only Copy objects option in DTS and this will
just copy the objects
>to destination.
>What recovery model you are using for this database, If
it is FULL or
>Bulk_logged and if you have trasnaction logs you can use
the Log explorer
>tool to analyze the situation which caused the object
drop.
>www.lumigent.com
>If you do not have the transaction log backup, it is very
difficult to find
>the cause for the object drop.
>--
>Thanks
>Hari
>MCDBA
><anonymous@.discussions.microsoft.com> wrote in message
>news:216db01c45aac$4cc30320$a401280a@.phx.gbl...
>> There is no web application connected (to my knowledge)
>> and nothing is showing in the security logs.
>> Using SQL Server 2000.
>> Has been suggested that using Enterprise Manager and the
>> DTS Import/Export Wizard to move objects could have
caused
>> the tables to be dropped? Any value in this theory?
>>
>> >--Original Message--
>> >Check your web server logs, if there is web application
>> connected. Check
>> >your security logs...
>> >
>> >"Pegasus" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >news:212c301c45a9e$73c9e3c0$a301280a@.phx.gbl...
>> >> I'm hoping someone out there can help me solve a
>> mystery!
>> >>
>> >> 29 tables in our live database were deleted the other
>> day
>> >> and we've no real idea how this could have happened.
>> >>
>> >> We have found an entry in Application Event Viewer
that
>> >> happened at about the same time as the tables
vanished,
>> >> but we don't really understand what it means:
>> >> EventID: 17055
>> >> Categorey: (6)
>> >> Description: 3041: BACKUP failed to complete the
command
>> >> dump tran [Claims] with no_log
>> >>
>> >> I understand that this is saying that the command
>> failed,
>> >> but where did the command come from? The user on
the
>> >> event was creating new tables and replacing stored
>> >> procedures in the database at the time, however,
>> [Claims]
>> >> is the name of the whole database!
>> >>
>> >> We have recovered from the disaster now but really do
>> need
>> >> to understand what has happened here, how it
happened,
>> and
>> >> how we can ensure it never happens again in the
future.
>> >>
>> >> Thanks in advance for any help.
>> >>
>> >> Regards
>> >>
>> >> Paula
>> >
>> >
>> >.
>> >
>
>.
>

Mystery Table Delete!

Wednesday, March 7, 2012

my store procedure that call back itself doesn't seems to work.

I have written a store procedure that will delete the record and any children record of it. That store procedure is suppose to call back itself to for every children record, but it only seems to delete the first record (the parent record). Below is my store procedure, pls help.

CREATE PROCEDURE [deleteCMSPage]
(@.PROJ_CMS_PAGE_ID [int])

/* do not delete page id 1 */
if @.PROJ_CMS_PAGE_ID <> 1
begin
-- For storing the list of child activities.
DECLARE @.page_list_cursor CURSOR
DECLARE @.CHILD_ID INTEGER
/* get the children of this page, for every child, call deleteCMSPage */
SET @.page_list_cursor = CURSOR FOR
SELECT PROJ_CMS_PAGE_ID
FROM TBL_PROJ_CMS_PAGE
WHERE PARENT_ID = @.PROJ_CMS_PAGE_ID
ORDER BY PAGE_ORDER

-- Populate the cursor.
OPEN @.page_list_cursor

WHILE (@.@.FETCH_STATUS = 0)
BEGIN
-- Fetch the details for next activity.
FETCH NEXT FROM @.page_list_cursor
INTO @.CHILD_ID
-- Calls itself, i.e
-- as parent to get all child nodes associated with it.
EXECUTE deleteCMSPage @.CHILD_ID
END
-- Close the cursor.
CLOSE @.page_list_cursor
-- Deallocate the cursor.
DEALLOCATE @.page_list_cursor
DELETE TBL_PROJ_CMS_PAGE
WHERE PROJ_CMS_PAGE_ID = @.PROJ_CMS_PAGE_ID
end
GO

The global variable @.@.FETCH_STATUS is set only when you perform a fetch. So the WHILE loop condition is wrong. You should code it like:

while (1=1)

begin

fetch...

if @.@.fetch_status < 0 break

...

end

|||

I have change the code, and right now it only delete the first branch, eg. root, first child, child of first child ..etc, I am not sure why.

CREATE PROCEDURE [deleteCMSPage]
(@.PROJ_CMS_PAGE_ID [int])

-- Populate the cursor.
OPEN @.page_list_cursor

while (1=1)
BEGIN

-- Fetch the details for next activity.
FETCH NEXT FROM @.page_list_cursor
INTO @.CHILD_ID
-- Calls itself, i.e
-- as parent to get all child nodes associated with it.
EXECUTE deleteCMSPage @.CHILD_ID
if @.@.fetch_status < 0 break
END
-- Close the cursor.
CLOSE @.page_list_cursor
-- Deallocate the cursor.
DEALLOCATE @.page_list_cursor
DELETE TBL_PROJ_CMS_PAGE
WHERE PROJ_CMS_PAGE_ID = @.PROJ_CMS_PAGE_ID
end
GO

|||

You have the CHECK for FETCH in the wrong place not like what I showed. Here you are checking FETCH_STATUS of some fetch operation that happened within the delete SP not the one before the call to the SP. So you should change below:

-- Fetch the details for next activity.
FETCH NEXT FROM @.page_list_cursor
INTO @.CHILD_ID

-- Calls itself, i.e
-- as parent to get all child nodes associated with it.
EXECUTE deleteCMSPage @.CHILD_ID
if @.@.fetch_status < 0 break

to:

-- Fetch the details for next activity.
FETCH NEXT FROM @.page_list_cursor
INTO @.CHILD_ID

if @.@.fetch_status < 0 break

-- Calls itself, i.e
-- as parent to get all child nodes associated with it.
EXECUTE deleteCMSPage @.CHILD_ID

|||thanks for you help, it's working now. :)

Monday, March 19, 2012

Mystery Table Delete!

Mystery Table Delete!

Mystery Table Delete!

Wednesday, March 7, 2012

my store procedure that call back itself doesn't seems to work.

MDF Files currupted

Blog Archive

About Me