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,
BingCheck 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/features/mssql/article.php/10894_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...
> > 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
>
>