|
Home > Archive > MS SQL Server > March 2006 > How much allocated space is actually used?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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
| |
| Tom Moreau 2006-03-14, 11:23 am |
| 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@microso
ft.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@microso
ft.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
>
>
| |
| Tom Moreau 2006-03-14, 1:23 pm |
| 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@microso
ft.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@microso
ft.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
>
>
| |
| Tibor Karaszi 2006-03-15, 3:23 am |
| 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@microso
ft.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
| |
|
|
|
|
|