Problem:
Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
Databases are set up in reverse. Data is stored in .ldf, and log files are
stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Kno
w
how to do this)
Can I shrink the logs even though they're in .mdf named files?
Is it ok to leave them alone in the reversed naming convention state?
Can I detach the databases, create a new .mdf, restore the database(.ldf)
from backup to the new .mdf file? If so, do I need to restore the log file
also, or can I start a new one from scratch?
Thanks for the help.Name is totally irrelevant to SQL Server, as well as extension. If you feel
it is OK, you can keep
the names. One option is to backup, detach (for safety - keep the files some
where else), and with
restore use the MOVE option to specify new file names. Or possibly only deta
ch, rename files and
attach specifying new correct file names (do this from QA not EM - QA gives
you full control over
the parameters used to sp_attach_db procedure).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick A" <Rick A@.discussions.microsoft.com> wrote in message
news:326AE016-3AAE-47B0-B216-E8EA16549B6A@.microsoft.com...
> Problem:
> Contractor migrated Great Plains databases to new server (Win2000, SQL2000
).
> Databases are set up in reverse. Data is stored in .ldf, and log files ar
e
> stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (K
now
> how to do this)
> Can I shrink the logs even though they're in .mdf named files?
> Is it ok to leave them alone in the reversed naming convention state?
> Can I detach the databases, create a new .mdf, restore the database(.ldf)
> from backup to the new .mdf file? If so, do I need to restore the log fil
e
> also, or can I start a new one from scratch?
> Thanks for the help.
>
>
>
>sql
Showing posts with label file. Show all posts
Showing posts with label file. Show all posts
Thursday, March 29, 2012
Database stored in .ldf file
Problem:
Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
Databases are set up in reverse. Data is stored in .ldf, and log files are
stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
how to do this)
Can I shrink the logs even though they're in .mdf named files?
Is it ok to leave them alone in the reversed naming convention state?
Can I detach the databases, create a new .mdf, restore the database(.ldf)
from backup to the new .mdf file? If so, do I need to restore the log file
also, or can I start a new one from scratch?
Thanks for the help.
Name is totally irrelevant to SQL Server, as well as extension. If you feel it is OK, you can keep
the names. One option is to backup, detach (for safety - keep the files somewhere else), and with
restore use the MOVE option to specify new file names. Or possibly only detach, rename files and
attach specifying new correct file names (do this from QA not EM - QA gives you full control over
the parameters used to sp_attach_db procedure).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick A" <Rick A@.discussions.microsoft.com> wrote in message
news:326AE016-3AAE-47B0-B216-E8EA16549B6A@.microsoft.com...
> Problem:
> Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
> Databases are set up in reverse. Data is stored in .ldf, and log files are
> stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
> how to do this)
> Can I shrink the logs even though they're in .mdf named files?
> Is it ok to leave them alone in the reversed naming convention state?
> Can I detach the databases, create a new .mdf, restore the database(.ldf)
> from backup to the new .mdf file? If so, do I need to restore the log file
> also, or can I start a new one from scratch?
> Thanks for the help.
>
>
>
>
Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
Databases are set up in reverse. Data is stored in .ldf, and log files are
stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
how to do this)
Can I shrink the logs even though they're in .mdf named files?
Is it ok to leave them alone in the reversed naming convention state?
Can I detach the databases, create a new .mdf, restore the database(.ldf)
from backup to the new .mdf file? If so, do I need to restore the log file
also, or can I start a new one from scratch?
Thanks for the help.
Name is totally irrelevant to SQL Server, as well as extension. If you feel it is OK, you can keep
the names. One option is to backup, detach (for safety - keep the files somewhere else), and with
restore use the MOVE option to specify new file names. Or possibly only detach, rename files and
attach specifying new correct file names (do this from QA not EM - QA gives you full control over
the parameters used to sp_attach_db procedure).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick A" <Rick A@.discussions.microsoft.com> wrote in message
news:326AE016-3AAE-47B0-B216-E8EA16549B6A@.microsoft.com...
> Problem:
> Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
> Databases are set up in reverse. Data is stored in .ldf, and log files are
> stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
> how to do this)
> Can I shrink the logs even though they're in .mdf named files?
> Is it ok to leave them alone in the reversed naming convention state?
> Can I detach the databases, create a new .mdf, restore the database(.ldf)
> from backup to the new .mdf file? If so, do I need to restore the log file
> also, or can I start a new one from scratch?
> Thanks for the help.
>
>
>
>
Database stored in .ldf file
Problem:
Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
Databases are set up in reverse. Data is stored in .ldf, and log files are
stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
how to do this)
Can I shrink the logs even though they're in .mdf named files?
Is it ok to leave them alone in the reversed naming convention state?
Can I detach the databases, create a new .mdf, restore the database(.ldf)
from backup to the new .mdf file? If so, do I need to restore the log file
also, or can I start a new one from scratch?
Thanks for the help.Name is totally irrelevant to SQL Server, as well as extension. If you feel it is OK, you can keep
the names. One option is to backup, detach (for safety - keep the files somewhere else), and with
restore use the MOVE option to specify new file names. Or possibly only detach, rename files and
attach specifying new correct file names (do this from QA not EM - QA gives you full control over
the parameters used to sp_attach_db procedure).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick A" <Rick A@.discussions.microsoft.com> wrote in message
news:326AE016-3AAE-47B0-B216-E8EA16549B6A@.microsoft.com...
> Problem:
> Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
> Databases are set up in reverse. Data is stored in .ldf, and log files are
> stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
> how to do this)
> Can I shrink the logs even though they're in .mdf named files?
> Is it ok to leave them alone in the reversed naming convention state?
> Can I detach the databases, create a new .mdf, restore the database(.ldf)
> from backup to the new .mdf file? If so, do I need to restore the log file
> also, or can I start a new one from scratch?
> Thanks for the help.
>
>
>
>
Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
Databases are set up in reverse. Data is stored in .ldf, and log files are
stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
how to do this)
Can I shrink the logs even though they're in .mdf named files?
Is it ok to leave them alone in the reversed naming convention state?
Can I detach the databases, create a new .mdf, restore the database(.ldf)
from backup to the new .mdf file? If so, do I need to restore the log file
also, or can I start a new one from scratch?
Thanks for the help.Name is totally irrelevant to SQL Server, as well as extension. If you feel it is OK, you can keep
the names. One option is to backup, detach (for safety - keep the files somewhere else), and with
restore use the MOVE option to specify new file names. Or possibly only detach, rename files and
attach specifying new correct file names (do this from QA not EM - QA gives you full control over
the parameters used to sp_attach_db procedure).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick A" <Rick A@.discussions.microsoft.com> wrote in message
news:326AE016-3AAE-47B0-B216-E8EA16549B6A@.microsoft.com...
> Problem:
> Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
> Databases are set up in reverse. Data is stored in .ldf, and log files are
> stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
> how to do this)
> Can I shrink the logs even though they're in .mdf named files?
> Is it ok to leave them alone in the reversed naming convention state?
> Can I detach the databases, create a new .mdf, restore the database(.ldf)
> from backup to the new .mdf file? If so, do I need to restore the log file
> also, or can I start a new one from scratch?
> Thanks for the help.
>
>
>
>
Database startup
in sql server (re)start where does it find the location of Master.mdf file .
In Oracle, there is control file where the location of *.dbf file is stored and Instance find the location of dbf file from that location
In sql server where it finds the location of Databases as master, msdb etc..
The location is set duing Server installation.
That information is stored in the registry. It can also be supplied as a command line start up parameter [ -dMasterFilePath ].
The registry location 'should' be:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters
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
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.
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
>
> 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 tim
e.
> 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...
>
>
>
>
>
> - 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:
> 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 w
e
> 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...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
good point!
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
>
> 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 tim
e.
> 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...
>
>
>
>
>
> - 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:
> 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 w
e
> 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...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
good point!
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 Kumar
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" <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
>
> 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...
>
>
>
> - 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.googlegro ups.com...
> On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> 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.googlegro ups.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -
good point!
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
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" <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
>
> 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...
>
>
>
> - 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.googlegro ups.com...
> On Mar 28, 4:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> 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.googlegro ups.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -
good point!
Tuesday, March 27, 2012
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
>> >> > >
>> >> > >
>> >> > >
>> >>
>> >>
>> >>
>>
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
>> >> > >
>> >> > >
>> >> > >
>> >>
>> >>
>> >>
>>
Database Snapshot (SQL Server 2005)
Hi,
Sorry for posting my question in this group. Isn't Micsrosoft going to
create new forums for SQL2K5?
BOL states that the snapshot file(sparse file) is small when it is created,
and gradually grows. But I tried on my databases (even big ones) and its
size is the same as original data files. For example on AdventureWorks, the
sparse file I created took 223mb which is even bigger than the db itself!
Any help would be greatly appreciated.
Leila
Right-click the file in explorer, properties, check "size on disk".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to create new forums for
> SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is created, and gradually grows.
> But I tried on my databases (even big ones) and its size is the same as original data files. For
> example on AdventureWorks, the sparse file I created took 223mb which is even bigger than the db
> itself!
> Any help would be greatly appreciated.
> Leila
>
|||That is the way a sparse file works. It appears as large as it can be but
in reality it is only a few bytes to begin with and will grow as it gets
populated. Right click on the file in Explorer and choose properties. You
will see both sizes.
Andrew J. Kelly SQL MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
> Any help would be greatly appreciated.
> Leila
>
|||"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
I hope not. It's just SQL Server.
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
>
Sparse files have a logical size and a smaller physical size. You are just
seeing the logical size of the file.
http://msdn.microsoft.com/en-us/library/ms175823.aspx
Look at the available space on your drive before and after creating the
snapshot. You will find that although the file is reported as being 223mb,
the available space on your drive has hardly diminished at all.
David
|||And just to add, within SQL you can use fn_virtualfilestats to get the
actual size on disk of a snapshot e.g.
select db_name(DbId) as [Database],
sum(cast(((BytesOnDisk/1024.0)/1024.0) as numeric(25,2))) as [SizeOnDisk_MB]
from fn_virtualfilestats(-1,-1)
group by db_name(DbId)
You should see your snapshot database is a lot smaller than the database
it's based on (initially at least!)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
> Any help would be greatly appreciated.
> Leila
>
Sorry for posting my question in this group. Isn't Micsrosoft going to
create new forums for SQL2K5?
BOL states that the snapshot file(sparse file) is small when it is created,
and gradually grows. But I tried on my databases (even big ones) and its
size is the same as original data files. For example on AdventureWorks, the
sparse file I created took 223mb which is even bigger than the db itself!
Any help would be greatly appreciated.
Leila
Right-click the file in explorer, properties, check "size on disk".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to create new forums for
> SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is created, and gradually grows.
> But I tried on my databases (even big ones) and its size is the same as original data files. For
> example on AdventureWorks, the sparse file I created took 223mb which is even bigger than the db
> itself!
> Any help would be greatly appreciated.
> Leila
>
|||That is the way a sparse file works. It appears as large as it can be but
in reality it is only a few bytes to begin with and will grow as it gets
populated. Right click on the file in Explorer and choose properties. You
will see both sizes.
Andrew J. Kelly SQL MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
> Any help would be greatly appreciated.
> Leila
>
|||"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
I hope not. It's just SQL Server.
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
>
Sparse files have a logical size and a smaller physical size. You are just
seeing the logical size of the file.
http://msdn.microsoft.com/en-us/library/ms175823.aspx
Look at the available space on your drive before and after creating the
snapshot. You will find that although the file is reported as being 223mb,
the available space on your drive has hardly diminished at all.
David
|||And just to add, within SQL you can use fn_virtualfilestats to get the
actual size on disk of a snapshot e.g.
select db_name(DbId) as [Database],
sum(cast(((BytesOnDisk/1024.0)/1024.0) as numeric(25,2))) as [SizeOnDisk_MB]
from fn_virtualfilestats(-1,-1)
group by db_name(DbId)
You should see your snapshot database is a lot smaller than the database
it's based on (initially at least!)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
> Any help would be greatly appreciated.
> Leila
>
Database Snapshot (SQL Server 2005)
Hi,
Sorry for posting my question in this group. Isn't Micsrosoft going to
create new forums for SQL2K5?
BOL states that the snapshot file(sparse file) is small when it is created,
and gradually grows. But I tried on my databases (even big ones) and its
size is the same as original data files. For example on AdventureWorks, the
sparse file I created took 223mb which is even bigger than the db itself!
Any help would be greatly appreciated.
Leila
Right-click the file in explorer, properties, check "size on disk".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to create new forums for
> SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is created, and gradually grows.
> But I tried on my databases (even big ones) and its size is the same as original data files. For
> example on AdventureWorks, the sparse file I created took 223mb which is even bigger than the db
> itself!
> Any help would be greatly appreciated.
> Leila
>
|||That is the way a sparse file works. It appears as large as it can be but
in reality it is only a few bytes to begin with and will grow as it gets
populated. Right click on the file in Explorer and choose properties. You
will see both sizes.
Andrew J. Kelly SQL MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
> Any help would be greatly appreciated.
> Leila
>
|||And just to add, within SQL you can use fn_virtualfilestats to get the
actual size on disk of a snapshot e.g.
select db_name(DbId) as [Database],
sum(cast(((BytesOnDisk/1024.0)/1024.0) as numeric(25,2))) as [SizeOnDisk_MB]
from fn_virtualfilestats(-1,-1)
group by db_name(DbId)
You should see your snapshot database is a lot smaller than the database
it's based on (initially at least!)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
> Any help would be greatly appreciated.
> Leila
>
Sorry for posting my question in this group. Isn't Micsrosoft going to
create new forums for SQL2K5?
BOL states that the snapshot file(sparse file) is small when it is created,
and gradually grows. But I tried on my databases (even big ones) and its
size is the same as original data files. For example on AdventureWorks, the
sparse file I created took 223mb which is even bigger than the db itself!
Any help would be greatly appreciated.
Leila
Right-click the file in explorer, properties, check "size on disk".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to create new forums for
> SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is created, and gradually grows.
> But I tried on my databases (even big ones) and its size is the same as original data files. For
> example on AdventureWorks, the sparse file I created took 223mb which is even bigger than the db
> itself!
> Any help would be greatly appreciated.
> Leila
>
|||That is the way a sparse file works. It appears as large as it can be but
in reality it is only a few bytes to begin with and will grow as it gets
populated. Right click on the file in Explorer and choose properties. You
will see both sizes.
Andrew J. Kelly SQL MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
> Any help would be greatly appreciated.
> Leila
>
|||And just to add, within SQL you can use fn_virtualfilestats to get the
actual size on disk of a snapshot e.g.
select db_name(DbId) as [Database],
sum(cast(((BytesOnDisk/1024.0)/1024.0) as numeric(25,2))) as [SizeOnDisk_MB]
from fn_virtualfilestats(-1,-1)
group by db_name(DbId)
You should see your snapshot database is a lot smaller than the database
it's based on (initially at least!)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
> Any help would be greatly appreciated.
> Leila
>
Database sizing question
Hi,
I have 1.3 GB ASII file for each month to be tranferred to a table. I want to get 18 months( 18 files) so 18*1.3 GB will be my total file size. Now I am transferring one file at a time to the table using INSERT. I have an index on one column only ( NON CLUSTERED NON UNIQUE). My question is what should be my data file size and transaction log file size for this operation. I will only be doing SELECT and INSERT in this table. Any other SQL server parameters that I need to take care of then please let me know.
ThanksHowdy
Without knowing the table datatypes used in the table columns, its difficult to know. It can be calculated but its time consuming and probably more hassle than you want.
Try this ( its easier and faster ) :
(1) Create the table you are importing data to.
(2) Switch the database into Bulk Insert or Simple recovery mode during the data load ( this will stop the transaction log becoming huge and is good practice anyway during a large data load into a database ).
(3) Once the data is loaded, re-create the index. This will ensure the database is "clean" and the tran logs is as small as possible. During normal database operation ( inserts & updates ), the tran log will grow of course, if the recovery mode is FULL or BULK LOGGED.
(4) Change the database back into FULL recovery mode ( assuming you want to be able to recover the database to apoint in time ) and start using it.
Cheers,
SG.
I have 1.3 GB ASII file for each month to be tranferred to a table. I want to get 18 months( 18 files) so 18*1.3 GB will be my total file size. Now I am transferring one file at a time to the table using INSERT. I have an index on one column only ( NON CLUSTERED NON UNIQUE). My question is what should be my data file size and transaction log file size for this operation. I will only be doing SELECT and INSERT in this table. Any other SQL server parameters that I need to take care of then please let me know.
ThanksHowdy
Without knowing the table datatypes used in the table columns, its difficult to know. It can be calculated but its time consuming and probably more hassle than you want.
Try this ( its easier and faster ) :
(1) Create the table you are importing data to.
(2) Switch the database into Bulk Insert or Simple recovery mode during the data load ( this will stop the transaction log becoming huge and is good practice anyway during a large data load into a database ).
(3) Once the data is loaded, re-create the index. This will ensure the database is "clean" and the tran logs is as small as possible. During normal database operation ( inserts & updates ), the tran log will grow of course, if the recovery mode is FULL or BULK LOGGED.
(4) Change the database back into FULL recovery mode ( assuming you want to be able to recover the database to apoint in time ) and start using it.
Cheers,
SG.
Sunday, March 25, 2012
Database Size Limitation of SQL Server 2000 Personal Edition ?
I would like to know is there any limitation to the size of data file of SQL
Server 2000 Personal Edition. Just like the MSDE, the data file size cannot
be greater than 2GB (From memory) ?No there is no limitation.
But there is limitation on RAM usage
Server 2000 Personal Edition. Just like the MSDE, the data file size cannot
be greater than 2GB (From memory) ?No there is no limitation.
But there is limitation on RAM usage
Database Size Limitation of SQL Server 2000 Personal Edition ?
I would like to know is there any limitation to the size of data file of SQL
Server 2000 Personal Edition. Just like the MSDE, the data file size cannot
be greater than 2GB (From memory) ?
No there is no limitation.
But there is limitation on RAM usage
Server 2000 Personal Edition. Just like the MSDE, the data file size cannot
be greater than 2GB (From memory) ?
No there is no limitation.
But there is limitation on RAM usage
Database Size Limitation of SQL Server 2000 Personal Edition ?
I would like to know is there any limitation to the size of data file of SQL
Server 2000 Personal Edition. Just like the MSDE, the data file size cannot
be greater than 2GB (From memory) ?No there is no limitation.
But there is limitation on RAM usage
Server 2000 Personal Edition. Just like the MSDE, the data file size cannot
be greater than 2GB (From memory) ?No there is no limitation.
But there is limitation on RAM usage
Database Size Blowout - I mean like HUGE!
Has anybody come across a problem where their database
file becomes enormous for no particular reason?
I have checked all user and system tables, and they are
correct. It seems that in 3 days the database has blown
out from 400MB to 18500MB.
Any ideas/suggestions would be greatly appreciated.
Thank youIs it the database or log? run:
dbcc sqlperf('logspace')
and see if it is your log file, if it is, back it up and shrink the file or
see if you have any open transactions "dbcc opentran"
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> Has anybody come across a problem where their database
> file becomes enormous for no particular reason?
> I have checked all user and system tables, and they are
> correct. It seems that in 3 days the database has blown
> out from 400MB to 18500MB.
> Any ideas/suggestions would be greatly appreciated.
> Thank you|||somebody might have pumped in huge data and db might be in full recovery
mode .. check log size, truncate log ,shrink db u shall gain yur db size
again.
i agree with ray
run dbcc sqlperf('logspace')
u'll know abt the log size and do the above mentioned steps.
backup file will be huge, if u have enough space back it up first to be on a
safer side.
Regards,
Mayur
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> Has anybody come across a problem where their database
> file becomes enormous for no particular reason?
> I have checked all user and system tables, and they are
> correct. It seems that in 3 days the database has blown
> out from 400MB to 18500MB.
> Any ideas/suggestions would be greatly appreciated.
> Thank you|||I've already checked that. The log is currently using
50MB, whilst the PRIMARY database file is using 18329MB.
I've checked all the tables, and the row counts are what
they should be, back when the db was about 400MB.
Bizarre
>--Original Message--
>Is it the database or log? run:
>dbcc sqlperf('logspace')
>and see if it is your log file, if it is, back it up and
shrink the file or
>see if you have any open transactions "dbcc opentran"
>HTH
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in
message
>news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
>> Has anybody come across a problem where their database
>> file becomes enormous for no particular reason?
>> I have checked all user and system tables, and they are
>> correct. It seems that in 3 days the database has
blown
>> out from 400MB to 18500MB.
>> Any ideas/suggestions would be greatly appreciated.
>> Thank you
>
>.
>|||Indexes?
http://vyaskn.tripod.com/code/sp_show_huge_tables.txt
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:f0be01c3f0f4$c4533620$a501280a@.phx.gbl...
> I've already checked that. The log is currently using
> 50MB, whilst the PRIMARY database file is using 18329MB.
> I've checked all the tables, and the row counts are what
> they should be, back when the db was about 400MB.
> Bizarre
>
> >--Original Message--
> >Is it the database or log? run:
> >
> >dbcc sqlperf('logspace')
> >
> >and see if it is your log file, if it is, back it up and
> shrink the file or
> >see if you have any open transactions "dbcc opentran"
> >
> >HTH
> >
> >--
> >Ray Higdon MCSE, MCDBA, CCNA
> >--
> >"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in
> message
> >news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> >> Has anybody come across a problem where their database
> >> file becomes enormous for no particular reason?
> >>
> >> I have checked all user and system tables, and they are
> >> correct. It seems that in 3 days the database has
> blown
> >> out from 400MB to 18500MB.
> >>
> >> Any ideas/suggestions would be greatly appreciated.
> >>
> >> Thank you
> >
> >
> >.
> >
file becomes enormous for no particular reason?
I have checked all user and system tables, and they are
correct. It seems that in 3 days the database has blown
out from 400MB to 18500MB.
Any ideas/suggestions would be greatly appreciated.
Thank youIs it the database or log? run:
dbcc sqlperf('logspace')
and see if it is your log file, if it is, back it up and shrink the file or
see if you have any open transactions "dbcc opentran"
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> Has anybody come across a problem where their database
> file becomes enormous for no particular reason?
> I have checked all user and system tables, and they are
> correct. It seems that in 3 days the database has blown
> out from 400MB to 18500MB.
> Any ideas/suggestions would be greatly appreciated.
> Thank you|||somebody might have pumped in huge data and db might be in full recovery
mode .. check log size, truncate log ,shrink db u shall gain yur db size
again.
i agree with ray
run dbcc sqlperf('logspace')
u'll know abt the log size and do the above mentioned steps.
backup file will be huge, if u have enough space back it up first to be on a
safer side.
Regards,
Mayur
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> Has anybody come across a problem where their database
> file becomes enormous for no particular reason?
> I have checked all user and system tables, and they are
> correct. It seems that in 3 days the database has blown
> out from 400MB to 18500MB.
> Any ideas/suggestions would be greatly appreciated.
> Thank you|||I've already checked that. The log is currently using
50MB, whilst the PRIMARY database file is using 18329MB.
I've checked all the tables, and the row counts are what
they should be, back when the db was about 400MB.
Bizarre
>--Original Message--
>Is it the database or log? run:
>dbcc sqlperf('logspace')
>and see if it is your log file, if it is, back it up and
shrink the file or
>see if you have any open transactions "dbcc opentran"
>HTH
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in
message
>news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
>> Has anybody come across a problem where their database
>> file becomes enormous for no particular reason?
>> I have checked all user and system tables, and they are
>> correct. It seems that in 3 days the database has
blown
>> out from 400MB to 18500MB.
>> Any ideas/suggestions would be greatly appreciated.
>> Thank you
>
>.
>|||Indexes?
http://vyaskn.tripod.com/code/sp_show_huge_tables.txt
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:f0be01c3f0f4$c4533620$a501280a@.phx.gbl...
> I've already checked that. The log is currently using
> 50MB, whilst the PRIMARY database file is using 18329MB.
> I've checked all the tables, and the row counts are what
> they should be, back when the db was about 400MB.
> Bizarre
>
> >--Original Message--
> >Is it the database or log? run:
> >
> >dbcc sqlperf('logspace')
> >
> >and see if it is your log file, if it is, back it up and
> shrink the file or
> >see if you have any open transactions "dbcc opentran"
> >
> >HTH
> >
> >--
> >Ray Higdon MCSE, MCDBA, CCNA
> >--
> >"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in
> message
> >news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> >> Has anybody come across a problem where their database
> >> file becomes enormous for no particular reason?
> >>
> >> I have checked all user and system tables, and they are
> >> correct. It seems that in 3 days the database has
> blown
> >> out from 400MB to 18500MB.
> >>
> >> Any ideas/suggestions would be greatly appreciated.
> >>
> >> Thank you
> >
> >
> >.
> >
Thursday, March 22, 2012
Database size
I would like to ask if 4 GB (maximum for SQL Server 2005 Express edition ) of database size means size of data file + log file size
Thanks
It is datafile size only.
http://technet.microsoft.com/en-us/library/ms345154.aspx - check Engine Specifications
HTH!
Database Size
In the table called system files, we have a field called size = Size of the file (in 8-KB pages). And if i look at the physical drive on the explorer this Size is different from the one in the database on system tables. Could someone tell me how to calculate them.
Secondly, i can see that the dumpfile is stored in sysdevices table, but where can i get the size of this dumpfile (.bak) because is always 0 after i dumped the file and again is not 0 on the physical drive of the explorerAbout your 2nd question:
The lines located on the system table only provide the physical location of a logical dump files. The size is 0 in any case.sql
Secondly, i can see that the dumpfile is stored in sysdevices table, but where can i get the size of this dumpfile (.bak) because is always 0 after i dumped the file and again is not 0 on the physical drive of the explorerAbout your 2nd question:
The lines located on the system table only provide the physical location of a logical dump files. The size is 0 in any case.sql
database size
Is there any shortest way to database size (data & log)
I refer to Article ID: 256650 (reduce log file size)
Quit a lot of step
I want to reduce physical occupied HDD sizerefer BOL,
dbcc shrinkdatabase and dbcc shrinkfile|||Chiwaki
Before that I suggest you to assess the required value for Transaction log file, if you are trying to shrin the Tlog with DBCC SHRINKFILE and if the batch jobs such as bulk load and database maintenance will try to increase the size to process the statements.
So it is better to assess the size in advance to avoid such Open/Close operation.
Hope this helps.
I refer to Article ID: 256650 (reduce log file size)
Quit a lot of step
I want to reduce physical occupied HDD sizerefer BOL,
dbcc shrinkdatabase and dbcc shrinkfile|||Chiwaki
Before that I suggest you to assess the required value for Transaction log file, if you are trying to shrin the Tlog with DBCC SHRINKFILE and if the batch jobs such as bulk load and database maintenance will try to increase the size to process the statements.
So it is better to assess the size in advance to avoid such Open/Close operation.
Hope this helps.
Database Size
Hello there
Is there a way to get the data file size and log file size with code?sp_helpdb dbname
HTH. Ryan
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23CMY$leEGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hello there
> Is there a way to get the data file size and log file size with code?
>|||sp_helpdb <db>
"Roy Goldhammer" wrote:
> Hello there
> Is there a way to get the data file size and log file size with code?
>
>|||Roy
select * from sysfiles
or
select * from master..sysaltfiles where dbid =yourdbidhere
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23CMY$leEGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hello there
> Is there a way to get the data file size and log file size with code?
>
Is there a way to get the data file size and log file size with code?sp_helpdb dbname
HTH. Ryan
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23CMY$leEGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hello there
> Is there a way to get the data file size and log file size with code?
>|||sp_helpdb <db>
"Roy Goldhammer" wrote:
> Hello there
> Is there a way to get the data file size and log file size with code?
>
>|||Roy
select * from sysfiles
or
select * from master..sysaltfiles where dbid =yourdbidhere
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23CMY$leEGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hello there
> Is there a way to get the data file size and log file size with code?
>
database size
Dear all
I have a SQL abc database. Recently, I right click database properity,the
space avaliable is zero, but i see the database file size and transcation lo
g
has some free space. Why the space avaliable is zero in that case and have
any impact about this database?
thanksVerify that your database has the 'Automatic grow file' property checked in
both your data and transaction log files.
If it is not enabled, you have some choices, like expanding the size of your
files manually, or enabling automatic grow file. Perhaps you want to go with
the default of 10 percent.
Ben Nevarez, MCDBA, OCP
"123" <123@.discussions.microsoft.com> wrote in message
news:518D80E7-98FF-4729-9BB4-A6DA6ACFAD90@.microsoft.com...
> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation
> log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>|||Check the fragmentation of your tables. If you haven't already it may be
best to set up a database maintenance plan to reorganise your data and index
pages. The frequency you do this depends on the workload of your server.
"123" wrote:
> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation
log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>
I have a SQL abc database. Recently, I right click database properity,the
space avaliable is zero, but i see the database file size and transcation lo
g
has some free space. Why the space avaliable is zero in that case and have
any impact about this database?
thanksVerify that your database has the 'Automatic grow file' property checked in
both your data and transaction log files.
If it is not enabled, you have some choices, like expanding the size of your
files manually, or enabling automatic grow file. Perhaps you want to go with
the default of 10 percent.
Ben Nevarez, MCDBA, OCP
"123" <123@.discussions.microsoft.com> wrote in message
news:518D80E7-98FF-4729-9BB4-A6DA6ACFAD90@.microsoft.com...
> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation
> log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>|||Check the fragmentation of your tables. If you haven't already it may be
best to set up a database maintenance plan to reorganise your data and index
pages. The frequency you do this depends on the workload of your server.
"123" wrote:
> Dear all
> I have a SQL abc database. Recently, I right click database properity,the
> space avaliable is zero, but i see the database file size and transcation
log
> has some free space. Why the space avaliable is zero in that case and have
> any impact about this database?
> thanks
>
Subscribe to:
Posts (Atom)