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.
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
|||Year Space Unused
2001 - 190432 KB
2002 - 2592 KB
2003 - 2800 KB
2004 - 1160 KB
2005 - 984 KB
what I do now ?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ecmhPgdSFHA.2384@.tk2msftngp13.phx.gbl...
> Joh wrote:
> 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
>
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
|||Here is the overall result.. need to do any thing ?
reserved data index_size
unused
-- -- -- --
7653328 KB 3488344 KB 3879640 KB 285344 KB
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#9oZw3eSFHA.4028@.tk2msftngp13.phx.gbl...
> Joh wrote:
> 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
>
You only have 285MB of free space. There is no way to recover the 5.5GB
of used space because if is being used by objects.
As you can see you have 7.6GB reserves and 3.4GB of data and 3.8GB
indexes.
David Gugick
Imceda Software
www.imceda.com
