Maybe the Records Are There After All

From: John Bishop (ugradfrnd_at_aol.com)
Date: 10/29/04 

Date: Fri, 29 Oct 2004 10:47:20 -0400

Your explanation makes SENSE and yes, it appears that I was checking
immediately after the change. (Bear in mind that I am still feeling my way
along.)

The indication that the records were DELETED is that Server Enterprise
Manager reports that the table has ZERO Rows. I had made the change in Data
Type in Server Enterprise Manager and then was making notes and documenting
what I had done. I went back to note the record count (which I had
previously found was conveniently reported in the Table Properties in Server
Enterprise Manager and it gave me the sensation that ALL of the data had
been deleted.

Now, I am thinking that it is doing precisely as you describe. I was able
to use the Import and Export Data tool to COPY all of the data from that
table to another table in a different database, which reports that it has
387,825 records (CORRECT). The original table STILL says in the Properties
that it has ZERO records, but that table has several Indices that are
probably being rebuilt.

But, the larger database from last night STILL SAYS that it has ZERO records
nine hours after the records appeared to all be deleted. But it has nine
million records and more than a few indices. And I am running this SQL
Evaulation on an older 866 MHz single processor system with only 512 Mbs of
memory.

Is there some way to monitor the things that SQL is doing in the BACKGROUND
to these tables?

"Jeff Dillon" <jeff@removeemergencyreporting.com> wrote in message
news:ORaVrEcvEHA.2584@TK2MSFTNGP10.phx.gbl...
> Of course you backed up your database, prior to making major changes to
> tables, correct?
>
> Whew..thank goodness.
>
> And no, generally records are not deleted. However, behind the scenes,
it's
> probably renaming the table, creating a new structure, then inserting the
> records, then dropping and renaming the temp table.
>
> Did you check if the data was there immediately after changing the column
> datatype?
>
> Jeff
> "John Bishop" <ugradfrnd@aol.com> wrote in message
> news:eDbrR9bvEHA.3896@TK2MSFTNGP09.phx.gbl...
> > Last night, I posted the message "HELP!: A Disasterous Delete" (Friday,
> > October 29, 2004 1:20 AM). This morning, as I pressed ahead with my
work
> > with my smaller data extract, I have had a recurrence of the problem of
> the
> > mysterious deletion of ALL RECORDS, but now with the smaller data
extract.
> >
> > But I have a better sense now of what may have CAUSED the problem (I
> think).
> > I had just used SQL Server Enterprise Manager to Change the Data Type of
> two
> > columns from char to nvarchar. I was then re-running a query.
> Thereafter,
> > in noticed that ALL ROWS of the table seemed to be missing.
> >
> > Perhaps I am SPOILED by the way MS Access handles changes to a
> datastructure
> > with data records in place.
> >
> > DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN
IS
> > ALTERED??? This seems like a rather radical treatment to the data
within
> a
> > table if this is the case.
> >
> > Now I seem to have lost NOT ONLY the original larger 9 million record
data
> > table, but also the smaller 350,000 record data extract.
> >
> > Maybe I should just throw in the towel!
> >
> > Any suggestions are appreciated!!
> >
> >
>
>

Relevant Pages

  • Re: software needed to input data
    ... ENTERPRISE MANAGER give you a simple one table at a time ... Your programmer might have proprietary rights to the ... >If you are database savvy, you can always get into SQL ...
    (microsoft.public.sqlserver.server)
  • Re: Newbie - Help!
    ... This is my error message: ... CREATE DATABASE permission denied in database 'master'. ... database 'pubs' to file 'D:\Program Files\Microsoft SQL ... I was able to find a trial version of the Enterprise Manager. ...
    (microsoft.public.sqlserver.msde)
  • Enterprise Manager Query functionality
    ... If you right click on a table in a database in Enterprise Manager you have ... Re installing SQL did not resolve this issue. ...
    (microsoft.public.sqlserver.setup)
  • Re: Recreating database on production machine?
    ... If you want to recreate the database with a script, ... SQL Server MVP ... >I know that you can use the graphical tools in Enterprise Manager to create ...
    (microsoft.public.sqlserver.tools)
  • Re: Recreating database on production machine?
    ... If you want to recreate the database with a script, ... SQL Server MVP ... >I know that you can use the graphical tools in Enterprise Manager to create ...
    (microsoft.public.sqlserver.programming)