Joh wrote:
> Select * into MedicalHistory_2001 from MedicalHistory where
> year(Creationdate) = 2001
>
> Select * into MedicalHistory_2002 from MedicalHistory where
> year(Creationdate) = 2002
>
> Select * into MedicalHistory_2003 from MedicalHistory where
> year(Creationdate) = 2003
>
> Before using the above three commands my 'Health_Data' DataFile size
> is 3GB but now its 8.5GB, can I shrink the datafile ?
>
> I have used the following command but doesn't effect.
>
> backup log Health with truncate_only
> dbcc shrinkfile('Health_Log',EmptyFile)
> dbcc shrinkfile('Health_Data')
>
> Any guidance?
>
> Thanks in advance.

"David Gugick" <davidg-nospam@imceda.com> wrote in message
news:ecmhPgdSFHA.2384@tk2msftngp13.phx.gbl...
> Joh wrote:
> > Select * into MedicalHistory_2001 from MedicalHistory where
> > year(Creationdate) = 2001
> >
> > Select * into MedicalHistory_2002 from MedicalHistory where
> > year(Creationdate) = 2002
> >
> > Select * into MedicalHistory_2003 from MedicalHistory where
> > year(Creationdate) = 2003
> >
> > Before using the above three commands my 'Health_Data' DataFile size
> > is 3GB but now its 8.5GB, can I shrink the datafile ?
> >
> > I have used the following command but doesn't effect.
> >
> > backup log Health with truncate_only
> > dbcc shrinkfile('Health_Log',EmptyFile)
> > dbcc shrinkfile('Health_Data')
> >
> > Any guidance?
> >
> > Thanks in advance.
>
> Did you drop those tables? How large is each table? Run the following
> and report back:
>
> exec sp_spaceused -- what's the "unused" portion?
> exec sp_spaceused MedicalHistory_2001 -- what's the "unused" portion?
> exec sp_spaceused MedicalHistory_2002 -- what's the "unused" portion?
> exec sp_spaceused MedicalHistory_2003 -- what's the "unused" portion?
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Joh wrote:
> Year Space Unused
> 2001 - 190432 KB
> 2002 - 2592 KB
> 2003 - 2800 KB
> 2004 - 1160 KB
> 2005 - 984 KB
>

"David Gugick" <davidg-nospam@imceda.com> wrote in message
news:#9oZw3eSFHA.4028@tk2msftngp13.phx.gbl...
> Joh wrote:
> > Year Space Unused
> > 2001 - 190432 KB
> > 2002 - 2592 KB
> > 2003 - 2800 KB
> > 2004 - 1160 KB
> > 2005 - 984 KB
> >
>
> You only have about 200MB of unused space in those tables. You didn't
> run the first query which gives you the stats for the entire database.
> I'm assuming, though, that the results will not be much different.
>
> If you want to recover the space, it sounds like you'll need to identify
> and move or delete objects from the data file. I'm just assuming those
> tables are large and responsible for the increase in data file size.
> Look at the allocated portion to see how much space each table occupies
> on disk.
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

John wrote:
> Here is the overall result.. need to do any thing ?
>
>
> reserved data index_size
> unused
> ------------------ ------------------ ------------------
> ------------------ 7653328 KB 3488344 KB 3879640 KB
> 285344 KB
>