MS SQL Server - sp_spaceused vs Enterprise Manager

Author

sp_spaceused vs Enterprise Manager

Kamal Hassan

2006-01-13, 11:23 am

One of the production database show following info when sp_spaceused run
against it

reserved data index_size unused

------------------ ------------------ ------------------
------------------
29268456 KB 4466920 KB 1086184 KB 23715352 KB

But when view using Enterprise Manager it shows following info:

Data:
Used: 28583 MB, Available: 1070 MB (PRIMARY Filegroup)
Used: 1 MB, Available 499 MB (Secondary File Group, table yet to eb
moved)
Log: 27 MB, Available: 4700 MB (grown due to heavy transactional activity)

The database backup size is approx. 4.0 GB

As you can see there is inconsistncy between sp_spaceused reporting the
UNUSED space (23.0 GB) vs EM (1.0 GB). I have RAN dbcc updateusage but that
did not correct the problem.

Any other idea/suggestion please?

Thanks.

Ryan

2006-01-13, 11:23 am

EM relies on cached data so it's often wrong, to get a true reading use EXEC
sp_spaceused withthe @updateusage

--
HTH. Ryan
"Kamal Hassan" < KamalHassan@discussi
ons.microsoft.com> wrote in message
news:12747AF7-610A-4782-A052- 4E1DFAE934B8@microso
ft.com...
> One of the production database show following info when sp_spaceused run
> against it
>
> reserved data index_size unused
>
> ------------------ ------------------ ------------------
> ------------------
> 29268456 KB 4466920 KB 1086184 KB 23715352 KB
>
>
> But when view using Enterprise Manager it shows following info:
>
> Data:
> Used: 28583 MB, Available: 1070 MB (PRIMARY Filegroup)
> Used: 1 MB, Available 499 MB (Secondary File Group, table yet to eb
> moved)
> Log: 27 MB, Available: 4700 MB (grown due to heavy transactional
> activity)
>
> The database backup size is approx. 4.0 GB
>
> As you can see there is inconsistncy between sp_spaceused reporting the
> UNUSED space (23.0 GB) vs EM (1.0 GB). I have RAN dbcc updateusage but
> that
> did not correct the problem.
>
> Any other idea/suggestion please?
>
> Thanks.
>
>
>
>
>

ronc

2006-01-13, 11:23 am

Enterprise manager is still going to report that the database as using that
space even though the space is currently being unused. Best thing to do
would give that unused space back to the OS. I would use shrinkfile to shink
the data file. If its a production database shrink it a few GIG at a time
until you get it where you feel comfortable with the size. After ecach dbcc
shirnkfile is executed run the dbcc updateusage (0) on the database. You
will then start to see in enterprise manager the size changing. Good luck.

"Kamal Hassan" wrote:

> One of the production database show following info when sp_spaceused run
> against it
>
> reserved data index_size unused
>
> ------------------ ------------------ ------------------
> ------------------
> 29268456 KB 4466920 KB 1086184 KB 23715352 KB
>
>
> But when view using Enterprise Manager it shows following info:
>
> Data:
> Used: 28583 MB, Available: 1070 MB (PRIMARY Filegroup)
> Used: 1 MB, Available 499 MB (Secondary File Group, table yet to eb
> moved)
> Log: 27 MB, Available: 4700 MB (grown due to heavy transactional activity)
>
> The database backup size is approx. 4.0 GB
>
> As you can see there is inconsistncy between sp_spaceused reporting the
> UNUSED space (23.0 GB) vs EM (1.0 GB). I have RAN dbcc updateusage but that
> did not correct the problem.
>
> Any other idea/suggestion please?
>
> Thanks.
>
>
>
>
>

Kamal Hassan

2006-01-13, 11:23 am

Thanks for the answers. I will follow up as per suggestion from RONC.

With reference to response from Ryan, I have also RAN sp_spaceused
@updateusage='true' against the database (I should have stated earlier).

Let me know if you have any new/other ideas.

Thanks very much for quick responses/suggestions.

"Ryan" wrote:

> EM relies on cached data so it's often wrong, to get a true reading use EXEC
> sp_spaceused withthe @updateusage
>
> --
> HTH. Ryan
> "Kamal Hassan" < KamalHassan@discussi
ons.microsoft.com> wrote in message
> news:12747AF7-610A-4782-A052- 4E1DFAE934B8@microso
ft.com...
>
>
>

Kamal Hassan

2006-02-06, 8:23 pm

Hi All,

By the way, I am still interested to learn/know what CAUSES this situation
where the SPACECE ALLOCATED is much larger than the USED SPACE.

I have ran sp_spaceused @updateusage=-'true'
dbcc updateusage(0) but still does not seems to report the CORRECT stats.

I have RAN multiple DBCC SHRINKFILE (Data File, size) to bring it down to
smaller size but it does not seem work at once . why?

I would like to run DBCC once with appropriate data space (lets say 4000MB)
and get it done. But it does NOT seems to bring to 4000MB?

Any idea/help would be appreciated.

"Kamal Hassan" wrote:
[color=darkred]
> Thanks for the answers. I will follow up as per suggestion from RONC.
>
> With reference to response from Ryan, I have also RAN sp_spaceused
> @updateusage='true' against the database (I should have stated earlier).
>
> Let me know if you have any new/other ideas.
>
> Thanks very much for quick responses/suggestions.
>
> "Ryan" wrote:
>

Sponsored Links