Database Unit Testing: space

Showing posts with label space. Show all posts

Thursday, March 29, 2012

Database Stored on NaS

Can anyone tell me if it is possible to save SQL 2000 Database files on a
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
Thanks
Possible? yes.
Advisable? definitely NOT.
Supported? No.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>
|||Yes, you can store database files on a NaS device, but this will often be a
substantial tradeoff in terms of performance (while you didn't really give
us any details about your specific NaS architecture, typically this is used
for low $-per-GB storage, and not for high performance).
http://www.aspfaq.com/
(Reverse address to reply.)
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:#AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>
|||check kb below
http://support.microsoft.com/default...b;en-us;304261
You might want to look at iSCSI as an alternative
http://support.microsoft.com/default...b;en-us;833770
Andy.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>
|||You will pay a disk I/O performance hit not just because NAS is IP connected
but because Windows will not be able to issue Scatter Gather I/O requests
against it.
SQL Server uses these APIs to enhance its file maintenance and usage.
Sincerely,
Anthony Thomas

"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
Can anyone tell me if it is possible to save SQL 2000 Database files on a
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
Thanks
|||Thanks for everyone's input. The performance is not really an issue. We
have several customers that we support and for every customer we have a copy
of their SQL Data. We do periodically need to run some transactions through
the customers database but that doesn't really happen very often. All of
the data is currently sitting on our SQL box and I need to shift it
somewhere else. I thought the NAS would be the easiest option but I am now
just tempted to buy another SQL box just for the supported DB's.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>

Database Stored on NaS

Can anyone tell me if it is possible to save SQL 2000 Database files on a
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
ThanksPossible? yes.
Advisable? definitely NOT.
Supported? No.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>|||Yes, you can store database files on a NaS device, but this will often be a
substantial tradeoff in terms of performance (while you didn't really give
us any details about your specific NaS architecture, typically this is used
for low $-per-GB storage, and not for high performance).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:#AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>|||check kb below
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
You might want to look at iSCSI as an alternative
http://support.microsoft.com/default.aspx?scid=kb;en-us;833770
Andy.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>|||You will pay a disk I/O performance hit not just because NAS is IP connected
but because Windows will not be able to issue Scatter Gather I/O requests
against it.
SQL Server uses these APIs to enhance its file maintenance and usage.
Sincerely,
Anthony Thomas
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
Can anyone tell me if it is possible to save SQL 2000 Database files on a
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
Thanks|||Thanks for everyone's input. The performance is not really an issue. We
have several customers that we support and for every customer we have a copy
of their SQL Data. We do periodically need to run some transactions through
the customers database but that doesn't really happen very often. All of
the data is currently sitting on our SQL box and I need to shift it
somewhere else. I thought the NAS would be the easiest option but I am now
just tempted to buy another SQL box just for the supported DB's.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>

Database Space Used

I'm trying to report the amount of space allocated and used for each
database. I use sysfiles to report the total space allocated to a database,
but can't find information regarding how much of that space is has been used.
I want to store that information in a table each week/month to chart growth.
Is there a system table that stores how much space of each datafile/database
is being used?
Thanks. Any help would be appreciated.
Ron
You can get that information from the stored procedure sp_spaceused. That
just sums the space used and reserved for the tables and indexes in the
database from sysindexes. You can study the code of sp_spaceused (it's in
the master database), but what you want is basically:
SELECT SUM(reserved)
FROM sysindexes
WHERE indid in (0, 1, 255)
Jacco Schalkwijk
SQL Server MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:99CE9BA6-3B46-434B-B769-17FBD8BE4C9C@.microsoft.com...
> I'm trying to report the amount of space allocated and used for each
> database. I use sysfiles to report the total space allocated to a
> database,
> but can't find information regarding how much of that space is has been
> used.
> I want to store that information in a table each week/month to chart
> growth.
> Is there a system table that stores how much space of each
> datafile/database
> is being used?
> Thanks. Any help would be appreciated.
> Ron
>
|||There is an undocumented DBCC command 'DBCC SHOWFILESTATS' that returns
information on allocations per file. You can write a simple wrapper that
aggregates per filegroup or database (or both).
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:99CE9BA6-3B46-434B-B769-17FBD8BE4C9C@.microsoft.com...
> I'm trying to report the amount of space allocated and used for each
> database. I use sysfiles to report the total space allocated to a
database,
> but can't find information regarding how much of that space is has been
used.
> I want to store that information in a table each week/month to chart
growth.
> Is there a system table that stores how much space of each
datafile/database
> is being used?
> Thanks. Any help would be appreciated.
> Ron
>

Database Space Used

I'm trying to report the amount of space allocated and used for each
database. I use sysfiles to report the total space allocated to a database,
but can't find information regarding how much of that space is has been used.
I want to store that information in a table each week/month to chart growth.
Is there a system table that stores how much space of each datafile/database
is being used?
Thanks. Any help would be appreciated.
RonYou can get that information from the stored procedure sp_spaceused. That
just sums the space used and reserved for the tables and indexes in the
database from sysindexes. You can study the code of sp_spaceused (it's in
the master database), but what you want is basically:
SELECT SUM(reserved)
FROM sysindexes
WHERE indid in (0, 1, 255)
--
Jacco Schalkwijk
SQL Server MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:99CE9BA6-3B46-434B-B769-17FBD8BE4C9C@.microsoft.com...
> I'm trying to report the amount of space allocated and used for each
> database. I use sysfiles to report the total space allocated to a
> database,
> but can't find information regarding how much of that space is has been
> used.
> I want to store that information in a table each week/month to chart
> growth.
> Is there a system table that stores how much space of each
> datafile/database
> is being used?
> Thanks. Any help would be appreciated.
> Ron
>|||There is an undocumented DBCC command 'DBCC SHOWFILESTATS' that returns
information on allocations per file. You can write a simple wrapper that
aggregates per filegroup or database (or both).
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:99CE9BA6-3B46-434B-B769-17FBD8BE4C9C@.microsoft.com...
> I'm trying to report the amount of space allocated and used for each
> database. I use sysfiles to report the total space allocated to a
database,
> but can't find information regarding how much of that space is has been
used.
> I want to store that information in a table each week/month to chart
growth.
> Is there a system table that stores how much space of each
datafile/database
> is being used?
> Thanks. Any help would be appreciated.
> Ron
>

Database space monitoring.

Hi
can any one please help me or guide to some good article to create a script
that has to look into the data file space and log file space for each
database on sql server and if the database id running out of space then it
should automatically increase the log file and data file size.
Thanks in Advance.
Ritesh KumarRitesh
> can any one please help me or guide to some good article to create a
> script that has to look into the data file space and log file space for
> each database on sql server and if the database id running out of space
> then it should automatically increase the log file and data file size.
If the database is running out of space that is too late. You may want to
create/modify a size of database to be increased that prevents from
auto-grow feature
Actually an idea is to compare sysfiles system table data for time to time.
I'm sure you will find on internet many examples.
"Ritesh Kumar" <mailrembersu@.gmail.com> wrote in message
news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hi
> can any one please help me or guide to some good article to create a
> script that has to look into the data file space and log file space for
> each database on sql server and if the database id running out of space
> then it should automatically increase the log file and data file size.
> Thanks in Advance.
> Ritesh Kumar
>|||On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Ritesh
> > can any one please help me or guide to some good article to create a
> > script that has to look into the data file space and log file space for
> > each database on sql server and if the database id running out of space
> > then it should automatically increase the log file and data file size.
> If the database is running out of space that is too late. You may want to
> create/modify a size of database to be increased that prevents from
> auto-grow feature
> Actually an idea is to compare sysfiles system table data for time to time.
> I'm sure you will find on internet many examples.
> "Ritesh Kumar" <mailrembe...@.gmail.com> wrote in message
> news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
>
> > Hi
> > can any one please help me or guide to some good article to create a
> > script that has to look into the data file space and log file space for
> > each database on sql server and if the database id running out of space
> > then it should automatically increase the log file and data file size.
> > Thanks in Advance.
> > Ritesh Kumar- Hide quoted text -
> - Show quoted text -
This stored procedure (2005) will give you the free space on all
drives in your server:
exec sys.xp_fixeddrives
This stored procedure will give you the database size and other useful
info.
exec sp_spaceused
Maybe this will help?
Kristina|||Kristina
We should run this sp with the below parameter,because of results that we
get from sp are not always accurate
sp_spaceused @.updateusage = 'TRUE'
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1175083368.213998.50830@.b75g2000hsg.googlegroups.com...
> On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Ritesh
>> > can any one please help me or guide to some good article to create a
>> > script that has to look into the data file space and log file space
>> > for
>> > each database on sql server and if the database id running out of
>> > space
>> > then it should automatically increase the log file and data file size.
>> If the database is running out of space that is too late. You may want to
>> create/modify a size of database to be increased that prevents from
>> auto-grow feature
>> Actually an idea is to compare sysfiles system table data for time to
>> time.
>> I'm sure you will find on internet many examples.
>> "Ritesh Kumar" <mailrembe...@.gmail.com> wrote in message
>> news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
>>
>> > Hi
>> > can any one please help me or guide to some good article to create a
>> > script that has to look into the data file space and log file space
>> > for
>> > each database on sql server and if the database id running out of
>> > space
>> > then it should automatically increase the log file and data file size.
>> > Thanks in Advance.
>> > Ritesh Kumar- Hide quoted text -
>> - Show quoted text -
> This stored procedure (2005) will give you the free space on all
> drives in your server:
> exec sys.xp_fixeddrives
> This stored procedure will give you the database size and other useful
> info.
> exec sp_spaceused
> Maybe this will help?
> Kristina
>|||On Mar 28, 8:09 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Kristina
> We should run this sp with the below parameter,because of results that we
> get from sp are not always accurate
> sp_spaceused @.updateusage = 'TRUE'
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1175083368.213998.50830@.b75g2000hsg.googlegroups.com...
>
> > On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> >> Ritesh
> >> > can any one please help me or guide to some good article to create a
> >> > script that has to look into the data file space and log file space
> >> > for
> >> > each database on sql server and if the database id running out of
> >> > space
> >> > then it should automatically increase the log file and data file size.
> >> If the database is running out of space that is too late. You may want to
> >> create/modify a size of database to be increased that prevents from
> >> auto-grow feature
> >> Actually an idea is to compare sysfiles system table data for time to
> >> time.
> >> I'm sure you will find on internet many examples.
> >> "Ritesh Kumar" <mailrembe...@.gmail.com> wrote in message
> >>news:Oo$dgfNcHHA.4616@.TK2MSFTNGP03.phx.gbl...
> >> > Hi
> >> > can any one please help me or guide to some good article to create a
> >> > script that has to look into the data file space and log file space
> >> > for
> >> > each database on sql server and if the database id running out of
> >> > space
> >> > then it should automatically increase the log file and data file size.
> >> > Thanks in Advance.
> >> > Ritesh Kumar- Hide quoted text -
> >> - Show quoted text -
> > This stored procedure (2005) will give you the free space on all
> > drives in your server:
> > exec sys.xp_fixeddrives
> > This stored procedure will give you the database size and other useful
> > info.
> > exec sp_spaceused
> > Maybe this will help?
> > Kristina- Hide quoted text -
> - Show quoted text -
good point! :)sql

Database space monitoring.

Database Space Available Is Zero Even AFter Running Backup Log With No Truncate

I refreshed the SQL server name after rnning a backup log with no
truncate ....and still found out that space available size is still 0!
I also shrunk the Trans Log and still the same rusults.
Would very much appreciate your help
Thank you
*** Sent via Developersdex http://www.codecomments.com ***Hi,
What is the Recovery model of your database?
If it is FULL OR BULK_LOGGED then
If you need the transaction log backup execute below to clear the
Transaction log:-
backup log <dbname> to disk='d:\backup\dbname.trn
Incase if you do not require the transaction log backup go for;
backup log <dbname> with truncate_only
The above commands will clear the log , bit to reduce the physical LDF file
size you will have to execute the below command:-
DBCC SHRINKFILE (db1_log1_logical_name,truncat_eonly)
After doing these execute the below command to get the log size
dbcc sqlperf(logspace)
Thanks
Hari
SQL Server MVP
"mike" <-nospam@.yahoo.com> wrote in message
news:%23F7ieaVkFHA.2852@.TK2MSFTNGP14.phx.gbl...
> I refreshed the SQL server name after rnning a backup log with no
> truncate ....and still found out that space available size is still 0!
> I also shrunk the Trans Log and still the same rusults.
> Would very much appreciate your help
> Thank you
>
>
> *** Sent via Developersdex http://www.codecomments.com ***|||did you hear mike <-nospam@.yahoo.com> say in news:#F7ieaVkFHA.2852
@.TK2MSFTNGP14.phx.gbl:

> AFter Running Backup Log With No Truncate
sorry, but wouldn't this be the problem? if you wish to remove the
committed transactions from the log you should remove the no_truncate
option.
http://msdn.microsoft.com/library/d...rl=/library/en-
us/tsqlref/ts_ba-bz_35ww.asp
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||thank you. It worked, perfectly-

Mike
*** Sent via Developersdex http://www.codecomments.com ***

Database Space Available Is Zero Even AFter Running Backup Log With No Truncate

I refreshed the SQL server name after rnning a backup log with no
truncate ....and still found out that space available size is still 0!
I also shrunk the Trans Log and still the same rusults.
Would very much appreciate your help
Thank you
*** Sent via Developersdex http://www.codecomments.com ***
Hi,
What is the Recovery model of your database?
If it is FULL OR BULK_LOGGED then
If you need the transaction log backup execute below to clear the
Transaction log:-
backup log <dbname> to disk='d:\backup\dbname.trn
Incase if you do not require the transaction log backup go for;
backup log <dbname> with truncate_only
The above commands will clear the log , bit to reduce the physical LDF file
size you will have to execute the below command:-
DBCC SHRINKFILE (db1_log1_logical_name,truncatXeonly)
After doing these execute the below command to get the log size
dbcc sqlperf(logspace)
Thanks
Hari
SQL Server MVP
"mike" <-nospam@.yahoo.com> wrote in message
news:%23F7ieaVkFHA.2852@.TK2MSFTNGP14.phx.gbl...
> I refreshed the SQL server name after rnning a backup log with no
> truncate ....and still found out that space available size is still 0!
> I also shrunk the Trans Log and still the same rusults.
> Would very much appreciate your help
> Thank you
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||did you hear mike <-nospam@.yahoo.com> say in news:#F7ieaVkFHA.2852
@.TK2MSFTNGP14.phx.gbl:

> AFter Running Backup Log With No Truncate
sorry, but wouldn't this be the problem? if you wish to remove the
committed transactions from the log you should remove the no_truncate
option.
http://msdn.microsoft.com/library/de...l=/library/en-
us/tsqlref/ts_ba-bz_35ww.asp
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||thank you. It worked, perfectly-

Mike
*** Sent via Developersdex http://www.codecomments.com ***

Database Space Available Is Zero Even AFter Running Backup Log With No Truncate

I refreshed the SQL server name after rnning a backup log with no
truncate ....and still found out that space available size is still 0!
I also shrunk the Trans Log and still the same rusults.
Would very much appreciate your help
Thank you
*** Sent via Developersdex http://www.developersdex.com ***Hi,
What is the Recovery model of your database?
If it is FULL OR BULK_LOGGED then
If you need the transaction log backup execute below to clear the
Transaction log:-
backup log <dbname> to disk='d:\backup\dbname.trn
Incase if you do not require the transaction log backup go for;
backup log <dbname> with truncate_only
The above commands will clear the log , bit to reduce the physical LDF file
size you will have to execute the below command:-
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
After doing these execute the below command to get the log size
dbcc sqlperf(logspace)
Thanks
Hari
SQL Server MVP
"mike" <-nospam@.yahoo.com> wrote in message
news:%23F7ieaVkFHA.2852@.TK2MSFTNGP14.phx.gbl...
> I refreshed the SQL server name after rnning a backup log with no
> truncate ....and still found out that space available size is still 0!
> I also shrunk the Trans Log and still the same rusults.
> Would very much appreciate your help
> Thank you
>
>
> *** Sent via Developersdex http://www.developersdex.com ***|||did you hear mike <-nospam@.yahoo.com> say in news:#F7ieaVkFHA.2852
@.TK2MSFTNGP14.phx.gbl:
> AFter Running Backup Log With No Truncate
sorry, but wouldn't this be the problem? if you wish to remove the
committed transactions from the log you should remove the no_truncate
option.
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/tsqlref/ts_ba-bz_35ww.asp
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogssql

Tuesday, March 27, 2012

Database Space available is 0MB

The space available for a database is 0MB, what are the solutions to solve
this?
Thanks!
Enable autogrowth size up the database to a bigger with the statement
ALTER DATABASE which syntax can be found in the bOL.
HTH, Jens Suessmeyer.
|||Susanna wrote:
> The space available for a database is 0MB, what are the solutions to
> solve this?
What space are you talking about? Space inside the DB? Diskspace?
robert
|||yes space inside the DB, not diskspace
"Robert Klemme" wrote:

> Susanna wrote:
> What space are you talking about? Space inside the DB? Diskspace?
> robert
>
|||Try running DBCC UPDATEUSAGE.
Andrew J. Kelly SQL MVP
"Susanna" <Susanna@.discussions.microsoft.com> wrote in message
news:86D9A357-F5FE-4218-AE04-184B024ABA18@.microsoft.com...
> The space available for a database is 0MB, what are the solutions to solve
> this?
> Thanks!

Database space available error

i am getting incorrect value while trying to retrieve space available ina database using sql dmo..

i am using sql server express 2005

Hard to help you without knowing the code you are using. Please post it here in order to help you. What are you getting back, what are you expecting ? Why are you expecting this value and not the actual presented by DMO ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

databaseobject->get_spaceavailable(&space);

when i check this value with original value .ie. value known by opening database management is diferent

database space

DataBase Space

Hello, All!
I'm creating a script to collect DataBase Space used and DataBase space Total
and after that I will store this information in a table in my SQL Server.
I've tryed using sp_spaceused but I don't know how to use the result of this
StoreProcedure.
If anyone know how to get this information using either sp_spaceused results
or t-sql script example, it would be great for me.
Thanks
Juliano Horta
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200602/1
> I'm creating a script to collect DataBase Space used and DataBase space
> Total
> and after that I will store this information in a table in my SQL Server.
> I've tryed using sp_spaceused but I don't know how to use the result of
> this
> StoreProcedure.
> If anyone know how to get this information using either sp_spaceused
> results
> or t-sql script example, it would be great for me.
Why don't you look at the source code for sp_spaceused and adapt it for your
own needs?
|||Use the undocumented command DBCC ShowFileStats. It supports the WITH
TABLERESULTS option so you can dump the data into a table and work with it.
This is what Enterprise Mangler uses to populate the file used graphs on the
Taskpad pane.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Juliano H via droptable.com" <u13014@.uwe> wrote in message
news:5b3d9946b1a22@.uwe...
> Hello, All!
> I'm creating a script to collect DataBase Space used and DataBase space
> Total
> and after that I will store this information in a table in my SQL Server.
> I've tryed using sp_spaceused but I don't know how to use the result of
> this
> StoreProcedure.
> If anyone know how to get this information using either sp_spaceused
> results
> or t-sql script example, it would be great for me.
> Thanks
> Juliano Horta
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200602/1
|||Hi,
Try this to see whats in sp_spaceused...
sp_helptext 'sp_spaceused'
Thanks,
Sree
"Geoff N. Hiten" wrote:

> Use the undocumented command DBCC ShowFileStats. It supports the WITH
> TABLERESULTS option so you can dump the data into a table and work with it.
> This is what Enterprise Mangler uses to populate the file used graphs on the
> Taskpad pane.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Juliano H via droptable.com" <u13014@.uwe> wrote in message
> news:5b3d9946b1a22@.uwe...
>
>

database space

Hi,
I checked my log and found "Could not adjust the space allocation for file
mydb_Data". The following is the space used (I got after I shrinked tempdb).
Both database has autogrow at 10% and unrestricted max file.
I have questions:
1, how can I check what is the size the database was assigned when it was
created? In my case mydb was created from backup file.
2,how the number is determined for unallocated space? the tempdb has a lot
but mydb only has a little.
3, what is the reserved?
4, Once the tempdb growed the only way to reduce it is restarting the server?
5, the space for mydb will cause problems? Thanks
database_name database_size unallocated space
-- -- --
mydb 2499.12 MB 613.30 MB
reserved data index_size unused
-- -- -- --
2014664 KB 1213072 KB 797696 KB 3896 KB
database_name database_size unallocated space
-- -- --
tempdb 3981.44 MB 3979.82 MB
reserved data index_size unused
-- -- -- --
1144 KB 440 KB 464 KB 240 KBLook at the disk drive that your file resides, it may not have enough space
for the file to grow. From the output of sp_spaceused, your mydb_Data has a
lot of free space now (613.30Mb).
1. From top of my head, there is no way you can be sure about the database
creation size.
2. 'unallocate space' means that the space is not reserved for a particular
object/index. Any object/index could use the space.
3. 'reserved' means that the space is held for a particular object/index;
other objects/indexes could not use the space even if it is unused now.
4. You can run DBCC SHRINKDATABASE/SHRINKFILE to reduce the tempdb size.
Be aware: don't blindly shrink tempdb, it could have impact on your query
performance.
5. It depends. mydb has a lot of free space now (613.30Mb), it is hard for
others to judge if those free space are enough for you. It depends on the
operations you will run in the database.
--
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:2088EAA3-6B5C-449A-ABC2-18081F8DE841@.microsoft.com...
> Hi,
> I checked my log and found "Could not adjust the space allocation for file
> mydb_Data". The following is the space used (I got after I shrinked
tempdb).
> Both database has autogrow at 10% and unrestricted max file.
> I have questions:
> 1, how can I check what is the size the database was assigned when it was
> created? In my case mydb was created from backup file.
> 2,how the number is determined for unallocated space? the tempdb has a lot
> but mydb only has a little.
> 3, what is the reserved?
> 4, Once the tempdb growed the only way to reduce it is restarting the
server?
> 5, the space for mydb will cause problems? Thanks
> database_name database_size unallocated space
> -- -- --
> mydb 2499.12 MB 613.30 MB
> reserved data index_size unused
> -- -- -- --
> 2014664 KB 1213072 KB 797696 KB 3896 KB
>
> database_name database_size unallocated space
> -- -- --
> tempdb 3981.44 MB 3979.82 MB
> reserved data index_size unused
> -- -- -- --
-
> 1144 KB 440 KB 464 KB 240 KB|||Thanks for you reply.
I can see data + index_size + unused = reserved.
what's relationship between reserved, database_size, and unallocated space?
The number is after I shinked the tempdb. I wondering the tempdb
database_size is twice big as my operational database database_size but the
reserved is only a liitle.
"Stephen Yuan Jiang [MSFT]" wrote:
> Look at the disk drive that your file resides, it may not have enough space
> for the file to grow. From the output of sp_spaceused, your mydb_Data has a
> lot of free space now (613.30Mb).
>
> 1. From top of my head, there is no way you can be sure about the database
> creation size.
> 2. 'unallocate space' means that the space is not reserved for a particular
> object/index. Any object/index could use the space.
> 3. 'reserved' means that the space is held for a particular object/index;
> other objects/indexes could not use the space even if it is unused now.
> 4. You can run DBCC SHRINKDATABASE/SHRINKFILE to reduce the tempdb size.
> Be aware: don't blindly shrink tempdb, it could have impact on your query
> performance.
> 5. It depends. mydb has a lot of free space now (613.30Mb), it is hard for
> others to judge if those free space are enough for you. It depends on the
> operations you will run in the database.
> --
> Stephen Jiang
> Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:2088EAA3-6B5C-449A-ABC2-18081F8DE841@.microsoft.com...
> > Hi,
> > I checked my log and found "Could not adjust the space allocation for file
> > mydb_Data". The following is the space used (I got after I shrinked
> tempdb).
> > Both database has autogrow at 10% and unrestricted max file.
> > I have questions:
> > 1, how can I check what is the size the database was assigned when it was
> > created? In my case mydb was created from backup file.
> > 2,how the number is determined for unallocated space? the tempdb has a lot
> > but mydb only has a little.
> > 3, what is the reserved?
> > 4, Once the tempdb growed the only way to reduce it is restarting the
> server?
> > 5, the space for mydb will cause problems? Thanks
> >
> > database_name database_size unallocated space
> > -- -- --
> > mydb 2499.12 MB 613.30 MB
> >
> > reserved data index_size unused
> >
> > -- -- -- --
> > 2014664 KB 1213072 KB 797696 KB 3896 KB
> >
> >
> > database_name database_size unallocated space
> > -- -- --
> > tempdb 3981.44 MB 3979.82 MB
> > reserved data index_size unused
> > -- -- -- --
> -
> > 1144 KB 440 KB 464 KB 240 KB
>
>|||database_size is roughly equal to 'unallocated space + reserved + log file
size'. (Note: 1. log file size is not shown in the output; 2. In SQL 2000,
sometimes 'reserved' size is not very accurate, so that is why I put
'roughly' in the equation.)
If 'reserved' is little, it means the database used very little space in
real data/index. Because page locking in work table is unreliable, shrink
skips work table and work file during tempdb shrink. That is why you still
see a lot of unused space in tempdb after shrink. For example, if a file
has 100 Mb and only one page is used for a work table, if this page is at
the end of file, shrink will skip this page and be unable to truncate the
file to a smaller size - the result will translate to a lot of 'unallocate
space' and very little 'reserved' space.
--
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:3350176D-F124-4B3B-89FE-0F249FB3340B@.microsoft.com...
> Thanks for you reply.
> I can see data + index_size + unused = reserved.
> what's relationship between reserved, database_size, and unallocated
space?
> The number is after I shinked the tempdb. I wondering the tempdb
> database_size is twice big as my operational database database_size but
the
> reserved is only a liitle.
> "Stephen Yuan Jiang [MSFT]" wrote:
> > Look at the disk drive that your file resides, it may not have enough
space
> > for the file to grow. From the output of sp_spaceused, your mydb_Data
has a
> > lot of free space now (613.30Mb).
> >
> >
> > 1. From top of my head, there is no way you can be sure about the
database
> > creation size.
> >
> > 2. 'unallocate space' means that the space is not reserved for a
particular
> > object/index. Any object/index could use the space.
> >
> > 3. 'reserved' means that the space is held for a particular
object/index;
> > other objects/indexes could not use the space even if it is unused now.
> >
> > 4. You can run DBCC SHRINKDATABASE/SHRINKFILE to reduce the tempdb
size.
> > Be aware: don't blindly shrink tempdb, it could have impact on your
query
> > performance.
> >
> > 5. It depends. mydb has a lot of free space now (613.30Mb), it is hard
for
> > others to judge if those free space are enough for you. It depends on
the
> > operations you will run in the database.
> >
> > --
> > Stephen Jiang
> > Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "Jen" <Jen@.discussions.microsoft.com> wrote in message
> > news:2088EAA3-6B5C-449A-ABC2-18081F8DE841@.microsoft.com...
> > > Hi,
> > > I checked my log and found "Could not adjust the space allocation for
file
> > > mydb_Data". The following is the space used (I got after I shrinked
> > tempdb).
> > > Both database has autogrow at 10% and unrestricted max file.
> > > I have questions:
> > > 1, how can I check what is the size the database was assigned when it
was
> > > created? In my case mydb was created from backup file.
> > > 2,how the number is determined for unallocated space? the tempdb has a
lot
> > > but mydb only has a little.
> > > 3, what is the reserved?
> > > 4, Once the tempdb growed the only way to reduce it is restarting the
> > server?
> > > 5, the space for mydb will cause problems? Thanks
> > >
> > > database_name database_size unallocated space
> > > -- -- --
> > > mydb 2499.12 MB 613.30 MB
> > >
> > > reserved data index_size unused
> > >
> > > -- -- -- --
> > > 2014664 KB 1213072 KB 797696 KB 3896 KB
> > >
> > >
> > > database_name database_size unallocated space
> > > -- -- --
> > > tempdb 3981.44 MB 3979.82 MB
> > > reserved data index_size unused
> >
> -- -- -- --
> > -
> > > 1144 KB 440 KB 464 KB 240 KB
> >
> >
> >|||Thank you very much. I got a better picture now. I didn't find any
explaination about the space in BOL. Do you know any articles?
I read it somewhere that tempdb size is about 25% of user db, mine is too
big.
And I manged to shink it data file to 200mb using shinkfile.
Also I read that often shink database will fragment db and file system.
does it apply to tempdb too? Run Disk Defregamenter will cure it?
"Stephen Yuan Jiang [MSFT]" wrote:
> database_size is roughly equal to 'unallocated space + reserved + log file
> size'. (Note: 1. log file size is not shown in the output; 2. In SQL 2000,
> sometimes 'reserved' size is not very accurate, so that is why I put
> 'roughly' in the equation.)
> If 'reserved' is little, it means the database used very little space in
> real data/index. Because page locking in work table is unreliable, shrink
> skips work table and work file during tempdb shrink. That is why you still
> see a lot of unused space in tempdb after shrink. For example, if a file
> has 100 Mb and only one page is used for a work table, if this page is at
> the end of file, shrink will skip this page and be unable to truncate the
> file to a smaller size - the result will translate to a lot of 'unallocate
> space' and very little 'reserved' space.
> --
> Stephen Jiang
> Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:3350176D-F124-4B3B-89FE-0F249FB3340B@.microsoft.com...
> > Thanks for you reply.
> > I can see data + index_size + unused = reserved.
> > what's relationship between reserved, database_size, and unallocated
> space?
> >
> > The number is after I shinked the tempdb. I wondering the tempdb
> > database_size is twice big as my operational database database_size but
> the
> > reserved is only a liitle.
> >
> > "Stephen Yuan Jiang [MSFT]" wrote:
> >
> > > Look at the disk drive that your file resides, it may not have enough
> space
> > > for the file to grow. From the output of sp_spaceused, your mydb_Data
> has a
> > > lot of free space now (613.30Mb).
> > >
> > >
> > > 1. From top of my head, there is no way you can be sure about the
> database
> > > creation size.
> > >
> > > 2. 'unallocate space' means that the space is not reserved for a
> particular
> > > object/index. Any object/index could use the space.
> > >
> > > 3. 'reserved' means that the space is held for a particular
> object/index;
> > > other objects/indexes could not use the space even if it is unused now.
> > >
> > > 4. You can run DBCC SHRINKDATABASE/SHRINKFILE to reduce the tempdb
> size.
> > > Be aware: don't blindly shrink tempdb, it could have impact on your
> query
> > > performance.
> > >
> > > 5. It depends. mydb has a lot of free space now (613.30Mb), it is hard
> for
> > > others to judge if those free space are enough for you. It depends on
> the
> > > operations you will run in the database.
> > >
> > > --
> > > Stephen Jiang
> > > Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "Jen" <Jen@.discussions.microsoft.com> wrote in message
> > > news:2088EAA3-6B5C-449A-ABC2-18081F8DE841@.microsoft.com...
> > > > Hi,
> > > > I checked my log and found "Could not adjust the space allocation for
> file
> > > > mydb_Data". The following is the space used (I got after I shrinked
> > > tempdb).
> > > > Both database has autogrow at 10% and unrestricted max file.
> > > > I have questions:
> > > > 1, how can I check what is the size the database was assigned when it
> was
> > > > created? In my case mydb was created from backup file.
> > > > 2,how the number is determined for unallocated space? the tempdb has a
> lot
> > > > but mydb only has a little.
> > > > 3, what is the reserved?
> > > > 4, Once the tempdb growed the only way to reduce it is restarting the
> > > server?
> > > > 5, the space for mydb will cause problems? Thanks
> > > >
> > > > database_name database_size unallocated space
> > > > -- -- --
> > > > mydb 2499.12 MB 613.30 MB
> > > >
> > > > reserved data index_size unused
> > > >
> > > > -- -- -- --
> > > > 2014664 KB 1213072 KB 797696 KB 3896 KB
> > > >
> > > >
> > > > database_name database_size unallocated space
> > > > -- -- --
> > > > tempdb 3981.44 MB 3979.82 MB
> > > > reserved data index_size unused
> > >
> > -- -- -- --
> > > -
> > > > 1144 KB 440 KB 464 KB 240 KB
> > >
> > >
> > >
>
>|||Is there a way to trace back when the db grow/shrinkincluding user db and
tempdb? I would like to know when my tempdb starts to grow and caused by what
kind of activities since last server restart is September and something made
it burst. Thanks
"Stephen Yuan Jiang [MSFT]" wrote:
> database_size is roughly equal to 'unallocated space + reserved + log file
> size'. (Note: 1. log file size is not shown in the output; 2. In SQL 2000,
> sometimes 'reserved' size is not very accurate, so that is why I put
> 'roughly' in the equation.)
> If 'reserved' is little, it means the database used very little space in
> real data/index. Because page locking in work table is unreliable, shrink
> skips work table and work file during tempdb shrink. That is why you still
> see a lot of unused space in tempdb after shrink. For example, if a file
> has 100 Mb and only one page is used for a work table, if this page is at
> the end of file, shrink will skip this page and be unable to truncate the
> file to a smaller size - the result will translate to a lot of 'unallocate
> space' and very little 'reserved' space.
> --
> Stephen Jiang
> Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:3350176D-F124-4B3B-89FE-0F249FB3340B@.microsoft.com...
> > Thanks for you reply.
> > I can see data + index_size + unused = reserved.
> > what's relationship between reserved, database_size, and unallocated
> space?
> >
> > The number is after I shinked the tempdb. I wondering the tempdb
> > database_size is twice big as my operational database database_size but
> the
> > reserved is only a liitle.
> >
> > "Stephen Yuan Jiang [MSFT]" wrote:
> >
> > > Look at the disk drive that your file resides, it may not have enough
> space
> > > for the file to grow. From the output of sp_spaceused, your mydb_Data
> has a
> > > lot of free space now (613.30Mb).
> > >
> > >
> > > 1. From top of my head, there is no way you can be sure about the
> database
> > > creation size.
> > >
> > > 2. 'unallocate space' means that the space is not reserved for a
> particular
> > > object/index. Any object/index could use the space.
> > >
> > > 3. 'reserved' means that the space is held for a particular
> object/index;
> > > other objects/indexes could not use the space even if it is unused now.
> > >
> > > 4. You can run DBCC SHRINKDATABASE/SHRINKFILE to reduce the tempdb
> size.
> > > Be aware: don't blindly shrink tempdb, it could have impact on your
> query
> > > performance.
> > >
> > > 5. It depends. mydb has a lot of free space now (613.30Mb), it is hard
> for
> > > others to judge if those free space are enough for you. It depends on
> the
> > > operations you will run in the database.
> > >
> > > --
> > > Stephen Jiang
> > > Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "Jen" <Jen@.discussions.microsoft.com> wrote in message
> > > news:2088EAA3-6B5C-449A-ABC2-18081F8DE841@.microsoft.com...
> > > > Hi,
> > > > I checked my log and found "Could not adjust the space allocation for
> file
> > > > mydb_Data". The following is the space used (I got after I shrinked
> > > tempdb).
> > > > Both database has autogrow at 10% and unrestricted max file.
> > > > I have questions:
> > > > 1, how can I check what is the size the database was assigned when it
> was
> > > > created? In my case mydb was created from backup file.
> > > > 2,how the number is determined for unallocated space? the tempdb has a
> lot
> > > > but mydb only has a little.
> > > > 3, what is the reserved?
> > > > 4, Once the tempdb growed the only way to reduce it is restarting the
> > > server?
> > > > 5, the space for mydb will cause problems? Thanks
> > > >
> > > > database_name database_size unallocated space
> > > > -- -- --
> > > > mydb 2499.12 MB 613.30 MB
> > > >
> > > > reserved data index_size unused
> > > >
> > > > -- -- -- --
> > > > 2014664 KB 1213072 KB 797696 KB 3896 KB
> > > >
> > > >
> > > > database_name database_size unallocated space
> > > > -- -- --
> > > > tempdb 3981.44 MB 3979.82 MB
> > > > reserved data index_size unused
> > >
> > -- -- -- --
> > > -
> > > > 1144 KB 440 KB 464 KB 240 KB
> > >
> > >
> > >
>
>|||No you would have to have had a trace going that had the grow events in it.
--
Andrew J. Kelly SQL MVP
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:AD2FCF4F-0686-47FA-9ACD-22B37B4B7B58@.microsoft.com...
> Is there a way to trace back when the db grow/shrinkincluding user db and
> tempdb? I would like to know when my tempdb starts to grow and caused by
> what
> kind of activities since last server restart is September and something
> made
> it burst. Thanks
> "Stephen Yuan Jiang [MSFT]" wrote:
>> database_size is roughly equal to 'unallocated space + reserved + log
>> file
>> size'. (Note: 1. log file size is not shown in the output; 2. In SQL
>> 2000,
>> sometimes 'reserved' size is not very accurate, so that is why I put
>> 'roughly' in the equation.)
>> If 'reserved' is little, it means the database used very little space in
>> real data/index. Because page locking in work table is unreliable,
>> shrink
>> skips work table and work file during tempdb shrink. That is why you
>> still
>> see a lot of unused space in tempdb after shrink. For example, if a file
>> has 100 Mb and only one page is used for a work table, if this page is at
>> the end of file, shrink will skip this page and be unable to truncate the
>> file to a smaller size - the result will translate to a lot of
>> 'unallocate
>> space' and very little 'reserved' space.
>> --
>> Stephen Jiang
>> Microsoft SQL Server Storage Engine
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Jen" <Jen@.discussions.microsoft.com> wrote in message
>> news:3350176D-F124-4B3B-89FE-0F249FB3340B@.microsoft.com...
>> > Thanks for you reply.
>> > I can see data + index_size + unused = reserved.
>> > what's relationship between reserved, database_size, and unallocated
>> space?
>> >
>> > The number is after I shinked the tempdb. I wondering the tempdb
>> > database_size is twice big as my operational database database_size but
>> the
>> > reserved is only a liitle.
>> >
>> > "Stephen Yuan Jiang [MSFT]" wrote:
>> >
>> > > Look at the disk drive that your file resides, it may not have enough
>> space
>> > > for the file to grow. From the output of sp_spaceused, your
>> > > mydb_Data
>> has a
>> > > lot of free space now (613.30Mb).
>> > >
>> > >
>> > > 1. From top of my head, there is no way you can be sure about the
>> database
>> > > creation size.
>> > >
>> > > 2. 'unallocate space' means that the space is not reserved for a
>> particular
>> > > object/index. Any object/index could use the space.
>> > >
>> > > 3. 'reserved' means that the space is held for a particular
>> object/index;
>> > > other objects/indexes could not use the space even if it is unused
>> > > now.
>> > >
>> > > 4. You can run DBCC SHRINKDATABASE/SHRINKFILE to reduce the tempdb
>> size.
>> > > Be aware: don't blindly shrink tempdb, it could have impact on your
>> query
>> > > performance.
>> > >
>> > > 5. It depends. mydb has a lot of free space now (613.30Mb), it is
>> > > hard
>> for
>> > > others to judge if those free space are enough for you. It depends
>> > > on
>> the
>> > > operations you will run in the database.
>> > >
>> > > --
>> > > Stephen Jiang
>> > > Microsoft SQL Server Storage Engine
>> > >
>> > > This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> > >
>> > > "Jen" <Jen@.discussions.microsoft.com> wrote in message
>> > > news:2088EAA3-6B5C-449A-ABC2-18081F8DE841@.microsoft.com...
>> > > > Hi,
>> > > > I checked my log and found "Could not adjust the space allocation
>> > > > for
>> file
>> > > > mydb_Data". The following is the space used (I got after I shrinked
>> > > tempdb).
>> > > > Both database has autogrow at 10% and unrestricted max file.
>> > > > I have questions:
>> > > > 1, how can I check what is the size the database was assigned when
>> > > > it
>> was
>> > > > created? In my case mydb was created from backup file.
>> > > > 2,how the number is determined for unallocated space? the tempdb
>> > > > has a
>> lot
>> > > > but mydb only has a little.
>> > > > 3, what is the reserved?
>> > > > 4, Once the tempdb growed the only way to reduce it is restarting
>> > > > the
>> > > server?
>> > > > 5, the space for mydb will cause problems? Thanks
>> > > >
>> > > > database_name database_size unallocated space
>> > > > -- -- --
>> > > > mydb 2499.12 MB 613.30 MB
>> > > >
>> > > > reserved data index_size unused
>> > > >
>> > > > -- -- -- --
>> > > > 2014664 KB 1213072 KB 797696 KB 3896 KB
>> > > >
>> > > >
>> > > > database_name database_size unallocated space
>> > > > -- -- --
>> > > > tempdb 3981.44 MB 3979.82 MB
>> > > > reserved data index_size unused
>> > >
>> > -- -- -- --
>> > > -
>> > > > 1144 KB 440 KB 464 KB 240 KB
>> > >
>> > >
>> > >
>>|||Hi,
I don't think I really get what you mean.
"Andrew J. Kelly" wrote:
> No you would have to have had a trace going that had the grow events in it.
> --
> Andrew J. Kelly SQL MVP
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:AD2FCF4F-0686-47FA-9ACD-22B37B4B7B58@.microsoft.com...
> > Is there a way to trace back when the db grow/shrinkincluding user db and
> > tempdb? I would like to know when my tempdb starts to grow and caused by
> > what
> > kind of activities since last server restart is September and something
> > made
> > it burst. Thanks
> >
> > "Stephen Yuan Jiang [MSFT]" wrote:
> >
> >> database_size is roughly equal to 'unallocated space + reserved + log
> >> file
> >> size'. (Note: 1. log file size is not shown in the output; 2. In SQL
> >> 2000,
> >> sometimes 'reserved' size is not very accurate, so that is why I put
> >> 'roughly' in the equation.)
> >>
> >> If 'reserved' is little, it means the database used very little space in
> >> real data/index. Because page locking in work table is unreliable,
> >> shrink
> >> skips work table and work file during tempdb shrink. That is why you
> >> still
> >> see a lot of unused space in tempdb after shrink. For example, if a file
> >> has 100 Mb and only one page is used for a work table, if this page is at
> >> the end of file, shrink will skip this page and be unable to truncate the
> >> file to a smaller size - the result will translate to a lot of
> >> 'unallocate
> >> space' and very little 'reserved' space.
> >>
> >> --
> >> Stephen Jiang
> >> Microsoft SQL Server Storage Engine
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >>
> >> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> >> news:3350176D-F124-4B3B-89FE-0F249FB3340B@.microsoft.com...
> >> > Thanks for you reply.
> >> > I can see data + index_size + unused = reserved.
> >> > what's relationship between reserved, database_size, and unallocated
> >> space?
> >> >
> >> > The number is after I shinked the tempdb. I wondering the tempdb
> >> > database_size is twice big as my operational database database_size but
> >> the
> >> > reserved is only a liitle.
> >> >
> >> > "Stephen Yuan Jiang [MSFT]" wrote:
> >> >
> >> > > Look at the disk drive that your file resides, it may not have enough
> >> space
> >> > > for the file to grow. From the output of sp_spaceused, your
> >> > > mydb_Data
> >> has a
> >> > > lot of free space now (613.30Mb).
> >> > >
> >> > >
> >> > > 1. From top of my head, there is no way you can be sure about the
> >> database
> >> > > creation size.
> >> > >
> >> > > 2. 'unallocate space' means that the space is not reserved for a
> >> particular
> >> > > object/index. Any object/index could use the space.
> >> > >
> >> > > 3. 'reserved' means that the space is held for a particular
> >> object/index;
> >> > > other objects/indexes could not use the space even if it is unused
> >> > > now.
> >> > >
> >> > > 4. You can run DBCC SHRINKDATABASE/SHRINKFILE to reduce the tempdb
> >> size.
> >> > > Be aware: don't blindly shrink tempdb, it could have impact on your
> >> query
> >> > > performance.
> >> > >
> >> > > 5. It depends. mydb has a lot of free space now (613.30Mb), it is
> >> > > hard
> >> for
> >> > > others to judge if those free space are enough for you. It depends
> >> > > on
> >> the
> >> > > operations you will run in the database.
> >> > >
> >> > > --
> >> > > Stephen Jiang
> >> > > Microsoft SQL Server Storage Engine
> >> > >
> >> > > This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> > >
> >> > > "Jen" <Jen@.discussions.microsoft.com> wrote in message
> >> > > news:2088EAA3-6B5C-449A-ABC2-18081F8DE841@.microsoft.com...
> >> > > > Hi,
> >> > > > I checked my log and found "Could not adjust the space allocation
> >> > > > for
> >> file
> >> > > > mydb_Data". The following is the space used (I got after I shrinked
> >> > > tempdb).
> >> > > > Both database has autogrow at 10% and unrestricted max file.
> >> > > > I have questions:
> >> > > > 1, how can I check what is the size the database was assigned when
> >> > > > it
> >> was
> >> > > > created? In my case mydb was created from backup file.
> >> > > > 2,how the number is determined for unallocated space? the tempdb
> >> > > > has a
> >> lot
> >> > > > but mydb only has a little.
> >> > > > 3, what is the reserved?
> >> > > > 4, Once the tempdb growed the only way to reduce it is restarting
> >> > > > the
> >> > > server?
> >> > > > 5, the space for mydb will cause problems? Thanks
> >> > > >
> >> > > > database_name database_size unallocated space
> >> > > > -- -- --
> >> > > > mydb 2499.12 MB 613.30 MB
> >> > > >
> >> > > > reserved data index_size unused
> >> > > >
> >> > > > -- -- -- --
> >> > > > 2014664 KB 1213072 KB 797696 KB 3896 KB
> >> > > >
> >> > > >
> >> > > > database_name database_size unallocated space
> >> > > > -- -- --
> >> > > > tempdb 3981.44 MB 3979.82 MB
> >> > > > reserved data index_size unused
> >> > >
> >> > -- -- -- --
> >> > > -
> >> > > > 1144 KB 440 KB 464 KB 240 KB
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>|||The only wat to see when a file grows is to run a trace (Profiler or
sp_trace_create) that includes the proper "Grow" events. Lookup
sp_trace_setevent in BOL and check out events 92 & 93. That trace must have
already been running when the autogrow event took place so it can log the
event. Otherwise there is no practical way to determine this.
--
Andrew J. Kelly SQL MVP
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:FD953403-ECDC-40B9-B27C-1D52B89A97D4@.microsoft.com...
> Hi,
> I don't think I really get what you mean.
> "Andrew J. Kelly" wrote:
>> No you would have to have had a trace going that had the grow events in
>> it.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jen" <Jen@.discussions.microsoft.com> wrote in message
>> news:AD2FCF4F-0686-47FA-9ACD-22B37B4B7B58@.microsoft.com...
>> > Is there a way to trace back when the db grow/shrinkincluding user db
>> > and
>> > tempdb? I would like to know when my tempdb starts to grow and caused
>> > by
>> > what
>> > kind of activities since last server restart is September and something
>> > made
>> > it burst. Thanks
>> >
>> > "Stephen Yuan Jiang [MSFT]" wrote:
>> >
>> >> database_size is roughly equal to 'unallocated space + reserved + log
>> >> file
>> >> size'. (Note: 1. log file size is not shown in the output; 2. In SQL
>> >> 2000,
>> >> sometimes 'reserved' size is not very accurate, so that is why I put
>> >> 'roughly' in the equation.)
>> >>
>> >> If 'reserved' is little, it means the database used very little space
>> >> in
>> >> real data/index. Because page locking in work table is unreliable,
>> >> shrink
>> >> skips work table and work file during tempdb shrink. That is why you
>> >> still
>> >> see a lot of unused space in tempdb after shrink. For example, if a
>> >> file
>> >> has 100 Mb and only one page is used for a work table, if this page is
>> >> at
>> >> the end of file, shrink will skip this page and be unable to truncate
>> >> the
>> >> file to a smaller size - the result will translate to a lot of
>> >> 'unallocate
>> >> space' and very little 'reserved' space.
>> >>
>> >> --
>> >> Stephen Jiang
>> >> Microsoft SQL Server Storage Engine
>> >>
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >>
>> >> "Jen" <Jen@.discussions.microsoft.com> wrote in message
>> >> news:3350176D-F124-4B3B-89FE-0F249FB3340B@.microsoft.com...
>> >> > Thanks for you reply.
>> >> > I can see data + index_size + unused = reserved.
>> >> > what's relationship between reserved, database_size, and unallocated
>> >> space?
>> >> >
>> >> > The number is after I shinked the tempdb. I wondering the tempdb
>> >> > database_size is twice big as my operational database database_size
>> >> > but
>> >> the
>> >> > reserved is only a liitle.
>> >> >
>> >> > "Stephen Yuan Jiang [MSFT]" wrote:
>> >> >
>> >> > > Look at the disk drive that your file resides, it may not have
>> >> > > enough
>> >> space
>> >> > > for the file to grow. From the output of sp_spaceused, your
>> >> > > mydb_Data
>> >> has a
>> >> > > lot of free space now (613.30Mb).
>> >> > >
>> >> > >
>> >> > > 1. From top of my head, there is no way you can be sure about the
>> >> database
>> >> > > creation size.
>> >> > >
>> >> > > 2. 'unallocate space' means that the space is not reserved for a
>> >> particular
>> >> > > object/index. Any object/index could use the space.
>> >> > >
>> >> > > 3. 'reserved' means that the space is held for a particular
>> >> object/index;
>> >> > > other objects/indexes could not use the space even if it is unused
>> >> > > now.
>> >> > >
>> >> > > 4. You can run DBCC SHRINKDATABASE/SHRINKFILE to reduce the
>> >> > > tempdb
>> >> size.
>> >> > > Be aware: don't blindly shrink tempdb, it could have impact on
>> >> > > your
>> >> query
>> >> > > performance.
>> >> > >
>> >> > > 5. It depends. mydb has a lot of free space now (613.30Mb), it is
>> >> > > hard
>> >> for
>> >> > > others to judge if those free space are enough for you. It
>> >> > > depends
>> >> > > on
>> >> the
>> >> > > operations you will run in the database.
>> >> > >
>> >> > > --
>> >> > > Stephen Jiang
>> >> > > Microsoft SQL Server Storage Engine
>> >> > >
>> >> > > This posting is provided "AS IS" with no warranties, and confers
>> >> > > no
>> >> rights.
>> >> > >
>> >> > > "Jen" <Jen@.discussions.microsoft.com> wrote in message
>> >> > > news:2088EAA3-6B5C-449A-ABC2-18081F8DE841@.microsoft.com...
>> >> > > > Hi,
>> >> > > > I checked my log and found "Could not adjust the space
>> >> > > > allocation
>> >> > > > for
>> >> file
>> >> > > > mydb_Data". The following is the space used (I got after I
>> >> > > > shrinked
>> >> > > tempdb).
>> >> > > > Both database has autogrow at 10% and unrestricted max file.
>> >> > > > I have questions:
>> >> > > > 1, how can I check what is the size the database was assigned
>> >> > > > when
>> >> > > > it
>> >> was
>> >> > > > created? In my case mydb was created from backup file.
>> >> > > > 2,how the number is determined for unallocated space? the tempdb
>> >> > > > has a
>> >> lot
>> >> > > > but mydb only has a little.
>> >> > > > 3, what is the reserved?
>> >> > > > 4, Once the tempdb growed the only way to reduce it is
>> >> > > > restarting
>> >> > > > the
>> >> > > server?
>> >> > > > 5, the space for mydb will cause problems? Thanks
>> >> > > >
>> >> > > > database_name database_size unallocated space
>> >> > > > -- -- --
>> >> > > > mydb 2499.12 MB 613.30 MB
>> >> > > >
>> >> > > > reserved data index_size
>> >> > > > unused
>> >> > > >
>> >> > > > -- -- -- --
>> >> > > > 2014664 KB 1213072 KB 797696 KB 3896 KB
>> >> > > >
>> >> > > >
>> >> > > > database_name database_size unallocated space
>> >> > >
>> >> > > > -- -- --
>> >> > > > tempdb 3981.44 MB 3979.82 MB
>> >> > > > reserved data index_size unused
>> >> > >
>> >> > -- -- -- --
>> >> > > -
>> >> > > > 1144 KB 440 KB 464 KB 240 KB
>> >> > >
>> >> > >
>> >> > >
>> >>
>> >>
>> >>
>>

Thursday, March 29, 2012

Database Stored on NaS

Database Stored on NaS

Database Space Used

Database Space Used

Database space monitoring.

Database space monitoring.

Database space monitoring.

Database Space Available Is Zero Even AFter Running Backup Log With No Truncate

Database Space Available Is Zero Even AFter Running Backup Log With No Truncate

Database Space Available Is Zero Even AFter Running Backup Log With No Truncate

Tuesday, March 27, 2012

Database Space available is 0MB

Database space available error

database space

DataBase Space

database space

database space

Database Unit Testing

Blog Archive

About Me