Re: Maybe the Records Are There After All

From: Geoff N. Hiten (SRDBA_at_Careerbuilder.com)
Date: 10/29/04


Date: Fri, 29 Oct 2004 10:51:08 -0400

Run DBCC UPDATEUSAGE to correct the row count display in Enterprise Mangler.

-- 
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"John Bishop" <ugradfrnd@aol.com> wrote in message
news:%231Z4HYcvEHA.2016@TK2MSFTNGP15.phx.gbl...
> 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!!
> > >
> > >
> >
> >
>
>