How much allocated space is actually used?

Hi,
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
Bing
Check out sp_spaceused in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
Hi,
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
Bing
|||Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:

> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>
|||Well, it wouldn't hurt. What really matters is if you're intending to add
more data. It's better to add the space before you need it, since that will
avoid an autogrow event - update activity stalls while the server goes and
allocates the space. If you expand a data file manually before you hit the
autogrow, then you avoid blocking your updates.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A385F721-96CD-40F6-B97C-27E3E42962D0@.microsoft.com...
Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:

> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this
> database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>
|||I prefer to either write my own procedures or use the undocumented DBCC SHOWFILESTATS commands
instead of sp_spaceused. One problem with sp_spaceused is that it doesn't separate data from log.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
|||Thanks so much for the information. I've found the codes provided on
http://www.databasejournal.com/featu...0894_3414111_2
very helpful. It put data space and log space usage together.
Bing
"Tibor Karaszi" wrote:

> I prefer to either write my own procedures or use the undocumented DBCC SHOWFILESTATS commands
> instead of sp_spaceused. One problem with sp_spaceused is that it doesn't separate data from log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
>
>