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
>> >> > >
>> >> > >
>> >> > >
>> >>
>> >>
>> >>
>>