Database Size - Allocated v. Used - Strange Problem

Ok, I am seeing something weird.
I have a production database that according to EM and DBCC SHOWFILESTATS
has 140GB of space allocated and is currently using 138GB. However,
according to the OS, the data file is ~ 157GB. I also perform log
shipping with this DB and the size of the data file on the standby copy is
correct @. 140GB. This tells me that the number OS seen size on the
production server is definitely incorrect. How can this be? What could
cause this sort of discrepancy?
The only thing that makes even a bit of sense.... the last mod date on
the data file is last week around the time I had a stupid vendor kick off
a select * into from a table with 35 million records. It is possible that
this caused an auto grow. The transaction never committed as I caught it
and rolled it back. Could the growth on the OS side have "stuck". The
numbers jive somewhat with what the auto growth rate is set at.
Regardless I am not ready to give up that much additional space right now,
especially not knowing if the OS/SQL are chatting correctly, therefore
making what the OS sees actually available to SQL.
Any input would be greatly appreciated.
Thanks!
Paul
Well I think I fixed it.
I grew the data file by 1MB through EM, and the file size value in the OS
seemed to "reset" itself.
Strange.
Paul
"Paul Timmerman" wrote:

> Ok, I am seeing something weird.
> I have a production database that according to EM and DBCC SHOWFILESTATS
> has 140GB of space allocated and is currently using 138GB. However,
> according to the OS, the data file is ~ 157GB. I also perform log
> shipping with this DB and the size of the data file on the standby copy is
> correct @. 140GB. This tells me that the number OS seen size on the
> production server is definitely incorrect. How can this be? What could
> cause this sort of discrepancy?
> The only thing that makes even a bit of sense.... the last mod date on
> the data file is last week around the time I had a stupid vendor kick off
> a select * into from a table with 35 million records. It is possible that
> this caused an auto grow. The transaction never committed as I caught it
> and rolled it back. Could the growth on the OS side have "stuck". The
> numbers jive somewhat with what the auto growth rate is set at.
> Regardless I am not ready to give up that much additional space right now,
> especially not knowing if the OS/SQL are chatting correctly, therefore
> making what the OS sees actually available to SQL.
> Any input would be greatly appreciated.
> Thanks!
> Paul
>
|||Try DBCC UPDATEUSAGE next time and see if that helps.
Andrew J. Kelly SQL MVP
"Paul Timmerman" <PaulTimmerman@.discussions.microsoft.com> wrote in message
news:75C0B5A4-A4EF-48F0-B4C2-BB4F22C9F0E1@.microsoft.com...[vbcol=seagreen]
> Well I think I fixed it.
> I grew the data file by 1MB through EM, and the file size value in the OS
> seemed to "reset" itself.
> Strange.
> Paul
> "Paul Timmerman" wrote: