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 spac
e
> 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 particula
r
> 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 fo
r
> 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...
> tempdb).
> server?
> -
>
>|||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[vbcol=seagreen]
> reserved is only a liitle.
> "Stephen Yuan Jiang [MSFT]" wrote:
>
space[vbcol=seagreen]
has a[vbcol=seagreen]
database[vbcol=seagreen]
particular[vbcol=seagreen]
object/index;[vbcol=seagreen]
size.[vbcol=seagreen]
query[vbcol=seagreen]
for[vbcol=seagreen]
the[vbcol=seagreen]
rights.[vbcol=seagreen]
file[vbcol=seagreen]
was[vbcol=seagreen]
lot[vbcol=seagreen]
> -- -- -- --|||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 stil
l
> 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...
> space?
> the
> space
> has a
> database
> particular
> object/index;
> size.
> query
> for
> the
> rights.
> file
> was
> lot
>
>|||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 wha
t
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 stil
l
> 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...
> space?
> the
> space
> has a
> database
> particular
> object/index;
> size.
> query
> for
> the
> rights.
> file
> was
> lot
>
>|||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...[vbcol=seagreen]
> 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:
>|||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...
>
>|||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...[vbcol=seagreen]
> Hi,
> I don't think I really get what you mean.
> "Andrew J. Kelly" wrote:
>
Showing posts with label following. Show all posts
Showing posts with label following. Show all posts
Tuesday, March 27, 2012
database space
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 KB
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
|||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...
> tempdb).
> server?
> -
>
>
|||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[vbcol=seagreen]
> reserved is only a liitle.
> "Stephen Yuan Jiang [MSFT]" wrote:
space[vbcol=seagreen]
has a[vbcol=seagreen]
database[vbcol=seagreen]
particular[vbcol=seagreen]
object/index;[vbcol=seagreen]
size.[vbcol=seagreen]
query[vbcol=seagreen]
for[vbcol=seagreen]
the[vbcol=seagreen]
rights.[vbcol=seagreen]
file[vbcol=seagreen]
was[vbcol=seagreen]
lot[vbcol=seagreen]
> -- -- -- --
|||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...
> space?
> the
> space
> has a
> database
> particular
> object/index;
> size.
> query
> for
> the
> rights.
> file
> was
> lot
>
>
|||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...
> space?
> the
> space
> has a
> database
> particular
> object/index;
> size.
> query
> for
> the
> rights.
> file
> was
> lot
>
>
|||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...[vbcol=seagreen]
> 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:
|||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...
>
>
|||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...[vbcol=seagreen]
> Hi,
> I don't think I really get what you mean.
> "Andrew J. Kelly" wrote:
sql
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
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
|||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...
> tempdb).
> server?
> -
>
>
|||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[vbcol=seagreen]
> reserved is only a liitle.
> "Stephen Yuan Jiang [MSFT]" wrote:
space[vbcol=seagreen]
has a[vbcol=seagreen]
database[vbcol=seagreen]
particular[vbcol=seagreen]
object/index;[vbcol=seagreen]
size.[vbcol=seagreen]
query[vbcol=seagreen]
for[vbcol=seagreen]
the[vbcol=seagreen]
rights.[vbcol=seagreen]
file[vbcol=seagreen]
was[vbcol=seagreen]
lot[vbcol=seagreen]
> -- -- -- --
|||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...
> space?
> the
> space
> has a
> database
> particular
> object/index;
> size.
> query
> for
> the
> rights.
> file
> was
> lot
>
>
|||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...
> space?
> the
> space
> has a
> database
> particular
> object/index;
> size.
> query
> for
> the
> rights.
> file
> was
> lot
>
>
|||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...[vbcol=seagreen]
> 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:
|||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...
>
>
|||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...[vbcol=seagreen]
> Hi,
> I don't think I really get what you mean.
> "Andrew J. Kelly" wrote:
sql
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
>> >> > >
>> >> > >
>> >> > >
>> >>
>> >>
>> >>
>>
Sunday, March 25, 2012
database size and raid configuration
Hi
I need to provide following information:
1. Size of all the current databases on all sql server (2000 and 2005). Is
these a query I can use to get this information.
2. Database size requirement for next three years.
3. Backup space requirements (I know which database require simple and which
transactional log database backups).
4. Test database space requirements (I know which databases require test
database).
5. New SQL server reporting services (Server space requirement), I know
which databases require a reporting server.
6. Recommendation for RAID for live and test databases including logging.
Thanks
ontario, canada
When i select size of files using sql server using
"select name,filename,size from sysaltfiles" I get size of files as
File one size: file1.mdf = 4976
File two size: file2.ldf = 2504
File three size file3.mdf = 1360
File four size file4.ldf = 13408
When I see the size of files in the disk using windows explorer I get
different size
File one size: 39804 kb
File two size: 20032 kb
File three size:10,880 kb
File four size: 107,264 KB
Why is that difference in file sizes?
ontario, canada
"db" wrote:
> Hi
> I need to provide following information:
> 1. Size of all the current databases on all sql server (2000 and 2005). Is
> these a query I can use to get this information.
> 2. Database size requirement for next three years.
> 3. Backup space requirements (I know which database require simple and which
> transactional log database backups).
> 4. Test database space requirements (I know which databases require test
> database).
> 5. New SQL server reporting services (Server space requirement), I know
> which databases require a reporting server.
> 6. Recommendation for RAID for live and test databases including logging.
> Thanks
> --
> ontario, canada
|||Thanks Tibor.
1. I am using sysaltfiles and sp_databases to get the Size of all the
current databases on all sql server (2000 and 2005). Looks like it works.
2. Database size requirement for next three years. For last 1.5 years size
of databases have increased by 50%. What do you think i should project for
next three years assuming no new applications?
3. Can I use a sql script to find the size of all backup files (.bak) for
the databases on the servers? If yes what?
4. Test database space requirements (I know which databases require test
database). What is the ideal size?
5. We will have new SQL server reporting server. How should i decide size
of the reporting server?
6. Recommendation for RAID for live and test databases. I would like to go
with maximum performance... ?
ontario, canada
"Tibor Karaszi" wrote:
> The unit for sysaltfiles is in pages (one page is 8KB).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:BCE5FEFC-57F7-44B8-B23F-10CED667E7FB@.microsoft.com...
>
|||2) I would plan on 50-75% growth per year based on your very limited
information.
3) I would use vbscript to scan directories and gather backup size
information. If you have never cleaned out msdb, you can find sizes for
backups there in one of the backupset... tables. See BOL for backupset and
it's related tables to get details.
4) We cannot guide you in this area without a good deal more information.
5) Again, need much more information.
6) Maximum performance would probably be RAID10, with lots of 15Krpm
spindles. You could perhaps get better read performance with RAID5, but
update/insert/delete performance will suffer. There is a LOT more to
disk/file configuration, btw!
BTW, I strongly recommend you hire an expert for a day or three to assist
you in your project. LOTS of ways to go astray here, and LOTS of variables
come into play.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"db" <db@.discussions.microsoft.com> wrote in message
news:CDA4EAF9-424E-4E0F-8164-EBE289ECC407@.microsoft.com...[vbcol=seagreen]
> Thanks Tibor.
> 1. I am using sysaltfiles and sp_databases to get the Size of all the
> current databases on all sql server (2000 and 2005). Looks like it works.
> 2. Database size requirement for next three years. For last 1.5 years size
> of databases have increased by 50%. What do you think i should project for
> next three years assuming no new applications?
> 3. Can I use a sql script to find the size of all backup files (.bak) for
> the databases on the servers? If yes what?
> 4. Test database space requirements (I know which databases require test
> database). What is the ideal size?
> 5. We will have new SQL server reporting server. How should i decide size
> of the reporting server?
> 6. Recommendation for RAID for live and test databases. I would like to go
> with maximum performance... ?
> --
> ontario, canada
>
> "Tibor Karaszi" wrote:
I need to provide following information:
1. Size of all the current databases on all sql server (2000 and 2005). Is
these a query I can use to get this information.
2. Database size requirement for next three years.
3. Backup space requirements (I know which database require simple and which
transactional log database backups).
4. Test database space requirements (I know which databases require test
database).
5. New SQL server reporting services (Server space requirement), I know
which databases require a reporting server.
6. Recommendation for RAID for live and test databases including logging.
Thanks
ontario, canada
When i select size of files using sql server using
"select name,filename,size from sysaltfiles" I get size of files as
File one size: file1.mdf = 4976
File two size: file2.ldf = 2504
File three size file3.mdf = 1360
File four size file4.ldf = 13408
When I see the size of files in the disk using windows explorer I get
different size
File one size: 39804 kb
File two size: 20032 kb
File three size:10,880 kb
File four size: 107,264 KB
Why is that difference in file sizes?
ontario, canada
"db" wrote:
> Hi
> I need to provide following information:
> 1. Size of all the current databases on all sql server (2000 and 2005). Is
> these a query I can use to get this information.
> 2. Database size requirement for next three years.
> 3. Backup space requirements (I know which database require simple and which
> transactional log database backups).
> 4. Test database space requirements (I know which databases require test
> database).
> 5. New SQL server reporting services (Server space requirement), I know
> which databases require a reporting server.
> 6. Recommendation for RAID for live and test databases including logging.
> Thanks
> --
> ontario, canada
|||Thanks Tibor.
1. I am using sysaltfiles and sp_databases to get the Size of all the
current databases on all sql server (2000 and 2005). Looks like it works.
2. Database size requirement for next three years. For last 1.5 years size
of databases have increased by 50%. What do you think i should project for
next three years assuming no new applications?
3. Can I use a sql script to find the size of all backup files (.bak) for
the databases on the servers? If yes what?
4. Test database space requirements (I know which databases require test
database). What is the ideal size?
5. We will have new SQL server reporting server. How should i decide size
of the reporting server?
6. Recommendation for RAID for live and test databases. I would like to go
with maximum performance... ?
ontario, canada
"Tibor Karaszi" wrote:
> The unit for sysaltfiles is in pages (one page is 8KB).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:BCE5FEFC-57F7-44B8-B23F-10CED667E7FB@.microsoft.com...
>
|||2) I would plan on 50-75% growth per year based on your very limited
information.
3) I would use vbscript to scan directories and gather backup size
information. If you have never cleaned out msdb, you can find sizes for
backups there in one of the backupset... tables. See BOL for backupset and
it's related tables to get details.
4) We cannot guide you in this area without a good deal more information.
5) Again, need much more information.
6) Maximum performance would probably be RAID10, with lots of 15Krpm
spindles. You could perhaps get better read performance with RAID5, but
update/insert/delete performance will suffer. There is a LOT more to
disk/file configuration, btw!
BTW, I strongly recommend you hire an expert for a day or three to assist
you in your project. LOTS of ways to go astray here, and LOTS of variables
come into play.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"db" <db@.discussions.microsoft.com> wrote in message
news:CDA4EAF9-424E-4E0F-8164-EBE289ECC407@.microsoft.com...[vbcol=seagreen]
> Thanks Tibor.
> 1. I am using sysaltfiles and sp_databases to get the Size of all the
> current databases on all sql server (2000 and 2005). Looks like it works.
> 2. Database size requirement for next three years. For last 1.5 years size
> of databases have increased by 50%. What do you think i should project for
> next three years assuming no new applications?
> 3. Can I use a sql script to find the size of all backup files (.bak) for
> the databases on the servers? If yes what?
> 4. Test database space requirements (I know which databases require test
> database). What is the ideal size?
> 5. We will have new SQL server reporting server. How should i decide size
> of the reporting server?
> 6. Recommendation for RAID for live and test databases. I would like to go
> with maximum performance... ?
> --
> ontario, canada
>
> "Tibor Karaszi" wrote:
Thursday, March 22, 2012
database size and raid configuration
Hi
I need to provide following information:
1. Size of all the current databases on all sql server (2000 and 2005). Is
these a query I can use to get this information.
2. Database size requirement for next three years.
3. Backup space requirements (I know which database require simple and which
transactional log database backups).
4. Test database space requirements (I know which databases require test
database).
5. New SQL server reporting services (Server space requirement), I know
which databases require a reporting server.
6. Recommendation for RAID for live and test databases including logging.
Thanks
--
ontario, canadaWhen i select size of files using sql server using
"select name,filename,size from sysaltfiles" I get size of files as
File one size: file1.mdf = 4976
File two size: file2.ldf = 2504
File three size file3.mdf = 1360
File four size file4.ldf = 13408
When I see the size of files in the disk using windows explorer I get
different size
File one size: 39804 kb
File two size: 20032 kb
File three size:10,880 kb
File four size: 107,264 KB
Why is that difference in file sizes?
--
ontario, canada
"db" wrote:
> Hi
> I need to provide following information:
> 1. Size of all the current databases on all sql server (2000 and 2005). Is
> these a query I can use to get this information.
> 2. Database size requirement for next three years.
> 3. Backup space requirements (I know which database require simple and which
> transactional log database backups).
> 4. Test database space requirements (I know which databases require test
> database).
> 5. New SQL server reporting services (Server space requirement), I know
> which databases require a reporting server.
> 6. Recommendation for RAID for live and test databases including logging.
> Thanks
> --
> ontario, canada|||The unit for sysaltfiles is in pages (one page is 8KB).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"db" <db@.discussions.microsoft.com> wrote in message
news:BCE5FEFC-57F7-44B8-B23F-10CED667E7FB@.microsoft.com...
> When i select size of files using sql server using
> "select name,filename,size from sysaltfiles" I get size of files as
> File one size: file1.mdf = 4976
> File two size: file2.ldf = 2504
> File three size file3.mdf = 1360
> File four size file4.ldf = 13408
> When I see the size of files in the disk using windows explorer I get
> different size
> File one size: 39804 kb
> File two size: 20032 kb
> File three size:10,880 kb
> File four size: 107,264 KB
> Why is that difference in file sizes?
> --
> ontario, canada
>
> "db" wrote:
>> Hi
>> I need to provide following information:
>> 1. Size of all the current databases on all sql server (2000 and 2005). Is
>> these a query I can use to get this information.
>> 2. Database size requirement for next three years.
>> 3. Backup space requirements (I know which database require simple and which
>> transactional log database backups).
>> 4. Test database space requirements (I know which databases require test
>> database).
>> 5. New SQL server reporting services (Server space requirement), I know
>> which databases require a reporting server.
>> 6. Recommendation for RAID for live and test databases including logging.
>> Thanks
>> --
>> ontario, canada|||Thanks Tibor.
1. I am using sysaltfiles and sp_databases to get the Size of all the
current databases on all sql server (2000 and 2005). Looks like it works.
2. Database size requirement for next three years. For last 1.5 years size
of databases have increased by 50%. What do you think i should project for
next three years assuming no new applications'
3. Can I use a sql script to find the size of all backup files (.bak) for
the databases on the servers? If yes what?
4. Test database space requirements (I know which databases require test
database). What is the ideal size'
5. We will have new SQL server reporting server. How should i decide size
of the reporting server?
6. Recommendation for RAID for live and test databases. I would like to go
with maximum performance... ?
--
ontario, canada
"Tibor Karaszi" wrote:
> The unit for sysaltfiles is in pages (one page is 8KB).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:BCE5FEFC-57F7-44B8-B23F-10CED667E7FB@.microsoft.com...
> > When i select size of files using sql server using
> > "select name,filename,size from sysaltfiles" I get size of files as
> >
> > File one size: file1.mdf = 4976
> > File two size: file2.ldf = 2504
> > File three size file3.mdf = 1360
> > File four size file4.ldf = 13408
> >
> > When I see the size of files in the disk using windows explorer I get
> > different size
> >
> > File one size: 39804 kb
> > File two size: 20032 kb
> > File three size:10,880 kb
> > File four size: 107,264 KB
> >
> > Why is that difference in file sizes?
> > --
> > ontario, canada
> >
> >
> > "db" wrote:
> >
> >> Hi
> >>
> >> I need to provide following information:
> >> 1. Size of all the current databases on all sql server (2000 and 2005). Is
> >> these a query I can use to get this information.
> >> 2. Database size requirement for next three years.
> >> 3. Backup space requirements (I know which database require simple and which
> >> transactional log database backups).
> >> 4. Test database space requirements (I know which databases require test
> >> database).
> >> 5. New SQL server reporting services (Server space requirement), I know
> >> which databases require a reporting server.
> >> 6. Recommendation for RAID for live and test databases including logging.
> >>
> >> Thanks
> >> --
> >> ontario, canada
>|||2) I would plan on 50-75% growth per year based on your very limited
information.
3) I would use vbscript to scan directories and gather backup size
information. If you have never cleaned out msdb, you can find sizes for
backups there in one of the backupset... tables. See BOL for backupset and
it's related tables to get details.
4) We cannot guide you in this area without a good deal more information.
5) Again, need much more information.
6) Maximum performance would probably be RAID10, with lots of 15Krpm
spindles. You could perhaps get better read performance with RAID5, but
update/insert/delete performance will suffer. There is a LOT more to
disk/file configuration, btw!
BTW, I strongly recommend you hire an expert for a day or three to assist
you in your project. LOTS of ways to go astray here, and LOTS of variables
come into play.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"db" <db@.discussions.microsoft.com> wrote in message
news:CDA4EAF9-424E-4E0F-8164-EBE289ECC407@.microsoft.com...
> Thanks Tibor.
> 1. I am using sysaltfiles and sp_databases to get the Size of all the
> current databases on all sql server (2000 and 2005). Looks like it works.
> 2. Database size requirement for next three years. For last 1.5 years size
> of databases have increased by 50%. What do you think i should project for
> next three years assuming no new applications'
> 3. Can I use a sql script to find the size of all backup files (.bak) for
> the databases on the servers? If yes what?
> 4. Test database space requirements (I know which databases require test
> database). What is the ideal size'
> 5. We will have new SQL server reporting server. How should i decide size
> of the reporting server?
> 6. Recommendation for RAID for live and test databases. I would like to go
> with maximum performance... ?
> --
> ontario, canada
>
> "Tibor Karaszi" wrote:
>> The unit for sysaltfiles is in pages (one page is 8KB).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "db" <db@.discussions.microsoft.com> wrote in message
>> news:BCE5FEFC-57F7-44B8-B23F-10CED667E7FB@.microsoft.com...
>> > When i select size of files using sql server using
>> > "select name,filename,size from sysaltfiles" I get size of files as
>> >
>> > File one size: file1.mdf = 4976
>> > File two size: file2.ldf = 2504
>> > File three size file3.mdf = 1360
>> > File four size file4.ldf = 13408
>> >
>> > When I see the size of files in the disk using windows explorer I get
>> > different size
>> >
>> > File one size: 39804 kb
>> > File two size: 20032 kb
>> > File three size:10,880 kb
>> > File four size: 107,264 KB
>> >
>> > Why is that difference in file sizes?
>> > --
>> > ontario, canada
>> >
>> >
>> > "db" wrote:
>> >
>> >> Hi
>> >>
>> >> I need to provide following information:
>> >> 1. Size of all the current databases on all sql server (2000 and
>> >> 2005). Is
>> >> these a query I can use to get this information.
>> >> 2. Database size requirement for next three years.
>> >> 3. Backup space requirements (I know which database require simple and
>> >> which
>> >> transactional log database backups).
>> >> 4. Test database space requirements (I know which databases require
>> >> test
>> >> database).
>> >> 5. New SQL server reporting services (Server space requirement), I
>> >> know
>> >> which databases require a reporting server.
>> >> 6. Recommendation for RAID for live and test databases including
>> >> logging.
>> >>
>> >> Thanks
>> >> --
>> >> ontario, canadasql
I need to provide following information:
1. Size of all the current databases on all sql server (2000 and 2005). Is
these a query I can use to get this information.
2. Database size requirement for next three years.
3. Backup space requirements (I know which database require simple and which
transactional log database backups).
4. Test database space requirements (I know which databases require test
database).
5. New SQL server reporting services (Server space requirement), I know
which databases require a reporting server.
6. Recommendation for RAID for live and test databases including logging.
Thanks
--
ontario, canadaWhen i select size of files using sql server using
"select name,filename,size from sysaltfiles" I get size of files as
File one size: file1.mdf = 4976
File two size: file2.ldf = 2504
File three size file3.mdf = 1360
File four size file4.ldf = 13408
When I see the size of files in the disk using windows explorer I get
different size
File one size: 39804 kb
File two size: 20032 kb
File three size:10,880 kb
File four size: 107,264 KB
Why is that difference in file sizes?
--
ontario, canada
"db" wrote:
> Hi
> I need to provide following information:
> 1. Size of all the current databases on all sql server (2000 and 2005). Is
> these a query I can use to get this information.
> 2. Database size requirement for next three years.
> 3. Backup space requirements (I know which database require simple and which
> transactional log database backups).
> 4. Test database space requirements (I know which databases require test
> database).
> 5. New SQL server reporting services (Server space requirement), I know
> which databases require a reporting server.
> 6. Recommendation for RAID for live and test databases including logging.
> Thanks
> --
> ontario, canada|||The unit for sysaltfiles is in pages (one page is 8KB).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"db" <db@.discussions.microsoft.com> wrote in message
news:BCE5FEFC-57F7-44B8-B23F-10CED667E7FB@.microsoft.com...
> When i select size of files using sql server using
> "select name,filename,size from sysaltfiles" I get size of files as
> File one size: file1.mdf = 4976
> File two size: file2.ldf = 2504
> File three size file3.mdf = 1360
> File four size file4.ldf = 13408
> When I see the size of files in the disk using windows explorer I get
> different size
> File one size: 39804 kb
> File two size: 20032 kb
> File three size:10,880 kb
> File four size: 107,264 KB
> Why is that difference in file sizes?
> --
> ontario, canada
>
> "db" wrote:
>> Hi
>> I need to provide following information:
>> 1. Size of all the current databases on all sql server (2000 and 2005). Is
>> these a query I can use to get this information.
>> 2. Database size requirement for next three years.
>> 3. Backup space requirements (I know which database require simple and which
>> transactional log database backups).
>> 4. Test database space requirements (I know which databases require test
>> database).
>> 5. New SQL server reporting services (Server space requirement), I know
>> which databases require a reporting server.
>> 6. Recommendation for RAID for live and test databases including logging.
>> Thanks
>> --
>> ontario, canada|||Thanks Tibor.
1. I am using sysaltfiles and sp_databases to get the Size of all the
current databases on all sql server (2000 and 2005). Looks like it works.
2. Database size requirement for next three years. For last 1.5 years size
of databases have increased by 50%. What do you think i should project for
next three years assuming no new applications'
3. Can I use a sql script to find the size of all backup files (.bak) for
the databases on the servers? If yes what?
4. Test database space requirements (I know which databases require test
database). What is the ideal size'
5. We will have new SQL server reporting server. How should i decide size
of the reporting server?
6. Recommendation for RAID for live and test databases. I would like to go
with maximum performance... ?
--
ontario, canada
"Tibor Karaszi" wrote:
> The unit for sysaltfiles is in pages (one page is 8KB).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:BCE5FEFC-57F7-44B8-B23F-10CED667E7FB@.microsoft.com...
> > When i select size of files using sql server using
> > "select name,filename,size from sysaltfiles" I get size of files as
> >
> > File one size: file1.mdf = 4976
> > File two size: file2.ldf = 2504
> > File three size file3.mdf = 1360
> > File four size file4.ldf = 13408
> >
> > When I see the size of files in the disk using windows explorer I get
> > different size
> >
> > File one size: 39804 kb
> > File two size: 20032 kb
> > File three size:10,880 kb
> > File four size: 107,264 KB
> >
> > Why is that difference in file sizes?
> > --
> > ontario, canada
> >
> >
> > "db" wrote:
> >
> >> Hi
> >>
> >> I need to provide following information:
> >> 1. Size of all the current databases on all sql server (2000 and 2005). Is
> >> these a query I can use to get this information.
> >> 2. Database size requirement for next three years.
> >> 3. Backup space requirements (I know which database require simple and which
> >> transactional log database backups).
> >> 4. Test database space requirements (I know which databases require test
> >> database).
> >> 5. New SQL server reporting services (Server space requirement), I know
> >> which databases require a reporting server.
> >> 6. Recommendation for RAID for live and test databases including logging.
> >>
> >> Thanks
> >> --
> >> ontario, canada
>|||2) I would plan on 50-75% growth per year based on your very limited
information.
3) I would use vbscript to scan directories and gather backup size
information. If you have never cleaned out msdb, you can find sizes for
backups there in one of the backupset... tables. See BOL for backupset and
it's related tables to get details.
4) We cannot guide you in this area without a good deal more information.
5) Again, need much more information.
6) Maximum performance would probably be RAID10, with lots of 15Krpm
spindles. You could perhaps get better read performance with RAID5, but
update/insert/delete performance will suffer. There is a LOT more to
disk/file configuration, btw!
BTW, I strongly recommend you hire an expert for a day or three to assist
you in your project. LOTS of ways to go astray here, and LOTS of variables
come into play.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"db" <db@.discussions.microsoft.com> wrote in message
news:CDA4EAF9-424E-4E0F-8164-EBE289ECC407@.microsoft.com...
> Thanks Tibor.
> 1. I am using sysaltfiles and sp_databases to get the Size of all the
> current databases on all sql server (2000 and 2005). Looks like it works.
> 2. Database size requirement for next three years. For last 1.5 years size
> of databases have increased by 50%. What do you think i should project for
> next three years assuming no new applications'
> 3. Can I use a sql script to find the size of all backup files (.bak) for
> the databases on the servers? If yes what?
> 4. Test database space requirements (I know which databases require test
> database). What is the ideal size'
> 5. We will have new SQL server reporting server. How should i decide size
> of the reporting server?
> 6. Recommendation for RAID for live and test databases. I would like to go
> with maximum performance... ?
> --
> ontario, canada
>
> "Tibor Karaszi" wrote:
>> The unit for sysaltfiles is in pages (one page is 8KB).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "db" <db@.discussions.microsoft.com> wrote in message
>> news:BCE5FEFC-57F7-44B8-B23F-10CED667E7FB@.microsoft.com...
>> > When i select size of files using sql server using
>> > "select name,filename,size from sysaltfiles" I get size of files as
>> >
>> > File one size: file1.mdf = 4976
>> > File two size: file2.ldf = 2504
>> > File three size file3.mdf = 1360
>> > File four size file4.ldf = 13408
>> >
>> > When I see the size of files in the disk using windows explorer I get
>> > different size
>> >
>> > File one size: 39804 kb
>> > File two size: 20032 kb
>> > File three size:10,880 kb
>> > File four size: 107,264 KB
>> >
>> > Why is that difference in file sizes?
>> > --
>> > ontario, canada
>> >
>> >
>> > "db" wrote:
>> >
>> >> Hi
>> >>
>> >> I need to provide following information:
>> >> 1. Size of all the current databases on all sql server (2000 and
>> >> 2005). Is
>> >> these a query I can use to get this information.
>> >> 2. Database size requirement for next three years.
>> >> 3. Backup space requirements (I know which database require simple and
>> >> which
>> >> transactional log database backups).
>> >> 4. Test database space requirements (I know which databases require
>> >> test
>> >> database).
>> >> 5. New SQL server reporting services (Server space requirement), I
>> >> know
>> >> which databases require a reporting server.
>> >> 6. Recommendation for RAID for live and test databases including
>> >> logging.
>> >>
>> >> Thanks
>> >> --
>> >> ontario, canadasql
Wednesday, March 21, 2012
Database setup script
I have MSDE installed on my machine as was wondering what the NETSDK is in the following commands.
@.rem Uncomment the following line for MSDE
@.rem set DBNAME=(local)\NETSDK
set DBNAME=(local)\NETSDK
Thanks,
Bob HIt would appear that NETSDK is the "instance name" of the MSDE installation.
With SQL Server 2000 (and MSDE), multiple instances of SQL Server can be installed. The first uses the "default instance"; that is, to access it, simply reference the name of the server in a connection string. To reference a non-default instance, follow the server with \<instance name>sql
Monday, March 19, 2012
database script error
I just upgraded from sql7.0 to sql2000.
everything seemed to be fine.
but when I click on master database it shows the following
error;
Internet Explorer error
line: 307
char: 2
error: unspecified error
code: 0
url: res://C:\Program%20Files\Microsoft%20SQL%20Server\80
\Tools\Binn\Resources\1033\sqlmmc.rll/Tabs.html
and asks me if I wish to conntinue running scripts.
if I create a new database and try to view it, it works
fine, so is this a problem because of the upgrade.
I'm using Internet Explorer 5.5
thanks in advance,
DarrinDarrin,
Had that problem sometime back.Dont know what fixed it - IE version or some
SQL Server service pack.Anyways, here is a workaround - try changing the
view to something other than 'taskpad' temporarily and then return back.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Darrin" <darrin.adams@.eei.ericsson.se> wrote in message
news:0ba001c35062$d936cc10$a501280a@.phx.gbl...
> I just upgraded from sql7.0 to sql2000.
> everything seemed to be fine.
> but when I click on master database it shows the following
> error;
> Internet Explorer error
> line: 307
> char: 2
> error: unspecified error
> code: 0
> url: res://C:\Program%20Files\Microsoft%20SQL%20Server\80
> \Tools\Binn\Resources\1033\sqlmmc.rll/Tabs.html
> and asks me if I wish to conntinue running scripts.
>
> if I create a new database and try to view it, it works
> fine, so is this a problem because of the upgrade.
> I'm using Internet Explorer 5.5
> thanks in advance,
> Darrin
everything seemed to be fine.
but when I click on master database it shows the following
error;
Internet Explorer error
line: 307
char: 2
error: unspecified error
code: 0
url: res://C:\Program%20Files\Microsoft%20SQL%20Server\80
\Tools\Binn\Resources\1033\sqlmmc.rll/Tabs.html
and asks me if I wish to conntinue running scripts.
if I create a new database and try to view it, it works
fine, so is this a problem because of the upgrade.
I'm using Internet Explorer 5.5
thanks in advance,
DarrinDarrin,
Had that problem sometime back.Dont know what fixed it - IE version or some
SQL Server service pack.Anyways, here is a workaround - try changing the
view to something other than 'taskpad' temporarily and then return back.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Darrin" <darrin.adams@.eei.ericsson.se> wrote in message
news:0ba001c35062$d936cc10$a501280a@.phx.gbl...
> I just upgraded from sql7.0 to sql2000.
> everything seemed to be fine.
> but when I click on master database it shows the following
> error;
> Internet Explorer error
> line: 307
> char: 2
> error: unspecified error
> code: 0
> url: res://C:\Program%20Files\Microsoft%20SQL%20Server\80
> \Tools\Binn\Resources\1033\sqlmmc.rll/Tabs.html
> and asks me if I wish to conntinue running scripts.
>
> if I create a new database and try to view it, it works
> fine, so is this a problem because of the upgrade.
> I'm using Internet Explorer 5.5
> thanks in advance,
> Darrin
Thursday, March 8, 2012
Database Restore from a different db backup
Hi All,
I have the following case.
I have two databases in the same server.
First one: OTG
second one: OTG_MA
Both are backed up in the same drive with name as OTG.bak and
OTG_MA.bak
But I had the situation to restore the OTG_MA database from the
OTG.bak file.
When I used the restore command that was not successful and was
generating errors that I cannot restore from a different backups.
Could anyone please tell me how I can restore database from a
different backup?
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
Thanks a million in advance.
Best regards,
MamunRestore database OTG_MA from disk = 'c:\otg.bak' with
move file1 to 'c:\newloc', move file2 to 'c:\newloc2'
The files must be placed in a location OTHER than the original filenames...
You will also have to force the restore I suspect.
"Mamun" <mamun_ah@.hotmail.com> wrote in message
news:6012e7ab.0309300658.5fb97376@.posting.google.com...
> Hi All,
> I have the following case.
>
> I have two databases in the same server.
> First one: OTG
> second one: OTG_MA
> Both are backed up in the same drive with name as OTG.bak and
> OTG_MA.bak
> But I had the situation to restore the OTG_MA database from the
> OTG.bak file.
> When I used the restore command that was not successful and was
> generating errors that I cannot restore from a different backups.
> Could anyone please tell me how I can restore database from a
> different backup?
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
>
> Thanks a million in advance.
> Best regards,
> Mamun
I have the following case.
I have two databases in the same server.
First one: OTG
second one: OTG_MA
Both are backed up in the same drive with name as OTG.bak and
OTG_MA.bak
But I had the situation to restore the OTG_MA database from the
OTG.bak file.
When I used the restore command that was not successful and was
generating errors that I cannot restore from a different backups.
Could anyone please tell me how I can restore database from a
different backup?
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
Thanks a million in advance.
Best regards,
MamunRestore database OTG_MA from disk = 'c:\otg.bak' with
move file1 to 'c:\newloc', move file2 to 'c:\newloc2'
The files must be placed in a location OTHER than the original filenames...
You will also have to force the restore I suspect.
"Mamun" <mamun_ah@.hotmail.com> wrote in message
news:6012e7ab.0309300658.5fb97376@.posting.google.com...
> Hi All,
> I have the following case.
>
> I have two databases in the same server.
> First one: OTG
> second one: OTG_MA
> Both are backed up in the same drive with name as OTG.bak and
> OTG_MA.bak
> But I had the situation to restore the OTG_MA database from the
> OTG.bak file.
> When I used the restore command that was not successful and was
> generating errors that I cannot restore from a different backups.
> Could anyone please tell me how I can restore database from a
> different backup?
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
>
> Thanks a million in advance.
> Best regards,
> Mamun
Database Restore failure in SQL 2005
We are using following commands and options in SQL 2000 for backup and
restore a database:
Backup Database DatabaseName to Disk='file path' with INIT
Backup Log DatabaseName to Disk='file path' with INIT
Alter Database DatabaseName set offline with ROLLBACK IMMEDIATE
Restore Database From Disk='file path' with NORECOVERY
Restore Log From Disk='file path'
It fails in SQL 2005 bate 2. It complains the Log has not been backed up.
Does anybody have any ideal about it?
Just test once using workbench ..Do you get same error there as well..
"Siemens" wrote:
> We are using following commands and options in SQL 2000 for backup and
> restore a database:
> Backup Database DatabaseName to Disk='file path' with INIT
> Backup Log DatabaseName to Disk='file path' with INIT
> Alter Database DatabaseName set offline with ROLLBACK IMMEDIATE
> Restore Database From Disk='file path' with NORECOVERY
> Restore Log From Disk='file path'
> It fails in SQL 2005 bate 2. It complains the Log has not been backed up.
> Does anybody have any ideal about it?
|||Can you post the detailed error message?
Can you download the SQL 2005 June CTP and try it there?
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:1E28D028-80FF-422A-AC55-8CB5D3ADC41C@.microsoft.com...[vbcol=seagreen]
> Just test once using workbench ..Do you get same error there as well..
> "Siemens" wrote:
up.[vbcol=seagreen]
|||That is a new feature in 2005. If you want to restore over a database, SQL Server want you to backup
the tail of the log first. If you don't want to do that, you can use REPLACE with the restore
command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Siemens" <Siemens@.discussions.microsoft.com> wrote in message
news:CE1B6A41-57DC-4944-AC82-389E735FACD8@.microsoft.com...
> We are using following commands and options in SQL 2000 for backup and
> restore a database:
> Backup Database DatabaseName to Disk='file path' with INIT
> Backup Log DatabaseName to Disk='file path' with INIT
> Alter Database DatabaseName set offline with ROLLBACK IMMEDIATE
> Restore Database From Disk='file path' with NORECOVERY
> Restore Log From Disk='file path'
> It fails in SQL 2005 bate 2. It complains the Log has not been backed up.
> Does anybody have any ideal about it?
restore a database:
Backup Database DatabaseName to Disk='file path' with INIT
Backup Log DatabaseName to Disk='file path' with INIT
Alter Database DatabaseName set offline with ROLLBACK IMMEDIATE
Restore Database From Disk='file path' with NORECOVERY
Restore Log From Disk='file path'
It fails in SQL 2005 bate 2. It complains the Log has not been backed up.
Does anybody have any ideal about it?
Just test once using workbench ..Do you get same error there as well..
"Siemens" wrote:
> We are using following commands and options in SQL 2000 for backup and
> restore a database:
> Backup Database DatabaseName to Disk='file path' with INIT
> Backup Log DatabaseName to Disk='file path' with INIT
> Alter Database DatabaseName set offline with ROLLBACK IMMEDIATE
> Restore Database From Disk='file path' with NORECOVERY
> Restore Log From Disk='file path'
> It fails in SQL 2005 bate 2. It complains the Log has not been backed up.
> Does anybody have any ideal about it?
|||Can you post the detailed error message?
Can you download the SQL 2005 June CTP and try it there?
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:1E28D028-80FF-422A-AC55-8CB5D3ADC41C@.microsoft.com...[vbcol=seagreen]
> Just test once using workbench ..Do you get same error there as well..
> "Siemens" wrote:
up.[vbcol=seagreen]
|||That is a new feature in 2005. If you want to restore over a database, SQL Server want you to backup
the tail of the log first. If you don't want to do that, you can use REPLACE with the restore
command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Siemens" <Siemens@.discussions.microsoft.com> wrote in message
news:CE1B6A41-57DC-4944-AC82-389E735FACD8@.microsoft.com...
> We are using following commands and options in SQL 2000 for backup and
> restore a database:
> Backup Database DatabaseName to Disk='file path' with INIT
> Backup Log DatabaseName to Disk='file path' with INIT
> Alter Database DatabaseName set offline with ROLLBACK IMMEDIATE
> Restore Database From Disk='file path' with NORECOVERY
> Restore Log From Disk='file path'
> It fails in SQL 2005 bate 2. It complains the Log has not been backed up.
> Does anybody have any ideal about it?
Database Restore failure in SQL 2005
We are using following commands and options in SQL 2000 for backup and
restore a database:
Backup Database DatabaseName to Disk='file path' with INIT
Backup Log DatabaseName to Disk='file path' with INIT
Alter Database DatabaseName set offline with ROLLBACK IMMEDIATE
Restore Database From Disk='file path' with NORECOVERY
Restore Log From Disk='file path'
It fails in SQL 2005 bate 2. It complains the Log has not been backed up.
Does anybody have any ideal about it?Just test once using workbench ..Do you get same error there as well..
"Siemens" wrote:
> We are using following commands and options in SQL 2000 for backup and
> restore a database:
> Backup Database DatabaseName to Disk='file path' with INIT
> Backup Log DatabaseName to Disk='file path' with INIT
> Alter Database DatabaseName set offline with ROLLBACK IMMEDIATE
> Restore Database From Disk='file path' with NORECOVERY
> Restore Log From Disk='file path'
> It fails in SQL 2005 bate 2. It complains the Log has not been backed up.
> Does anybody have any ideal about it?|||Can you post the detailed error message?
Can you download the SQL 2005 June CTP and try it there?
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:1E28D028-80FF-422A-AC55-8CB5D3ADC41C@.microsoft.com...[vbcol=seagreen]
> Just test once using workbench ..Do you get same error there as well..
> "Siemens" wrote:
>
up.[vbcol=seagreen]|||That is a new feature in 2005. If you want to restore over a database, SQL S
erver want you to backup
the tail of the log first. If you don't want to do that, you can use REPLACE
with the restore
command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Siemens" <Siemens@.discussions.microsoft.com> wrote in message
news:CE1B6A41-57DC-4944-AC82-389E735FACD8@.microsoft.com...
> We are using following commands and options in SQL 2000 for backup and
> restore a database:
> Backup Database DatabaseName to Disk='file path' with INIT
> Backup Log DatabaseName to Disk='file path' with INIT
> Alter Database DatabaseName set offline with ROLLBACK IMMEDIATE
> Restore Database From Disk='file path' with NORECOVERY
> Restore Log From Disk='file path'
> It fails in SQL 2005 bate 2. It complains the Log has not been backed up.
> Does anybody have any ideal about it?
restore a database:
Backup Database DatabaseName to Disk='file path' with INIT
Backup Log DatabaseName to Disk='file path' with INIT
Alter Database DatabaseName set offline with ROLLBACK IMMEDIATE
Restore Database From Disk='file path' with NORECOVERY
Restore Log From Disk='file path'
It fails in SQL 2005 bate 2. It complains the Log has not been backed up.
Does anybody have any ideal about it?Just test once using workbench ..Do you get same error there as well..
"Siemens" wrote:
> We are using following commands and options in SQL 2000 for backup and
> restore a database:
> Backup Database DatabaseName to Disk='file path' with INIT
> Backup Log DatabaseName to Disk='file path' with INIT
> Alter Database DatabaseName set offline with ROLLBACK IMMEDIATE
> Restore Database From Disk='file path' with NORECOVERY
> Restore Log From Disk='file path'
> It fails in SQL 2005 bate 2. It complains the Log has not been backed up.
> Does anybody have any ideal about it?|||Can you post the detailed error message?
Can you download the SQL 2005 June CTP and try it there?
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:1E28D028-80FF-422A-AC55-8CB5D3ADC41C@.microsoft.com...[vbcol=seagreen]
> Just test once using workbench ..Do you get same error there as well..
> "Siemens" wrote:
>
up.[vbcol=seagreen]|||That is a new feature in 2005. If you want to restore over a database, SQL S
erver want you to backup
the tail of the log first. If you don't want to do that, you can use REPLACE
with the restore
command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Siemens" <Siemens@.discussions.microsoft.com> wrote in message
news:CE1B6A41-57DC-4944-AC82-389E735FACD8@.microsoft.com...
> We are using following commands and options in SQL 2000 for backup and
> restore a database:
> Backup Database DatabaseName to Disk='file path' with INIT
> Backup Log DatabaseName to Disk='file path' with INIT
> Alter Database DatabaseName set offline with ROLLBACK IMMEDIATE
> Restore Database From Disk='file path' with NORECOVERY
> Restore Log From Disk='file path'
> It fails in SQL 2005 bate 2. It complains the Log has not been backed up.
> Does anybody have any ideal about it?
Database Restore failure in SQL 2005
We are using following commands and options in SQL 2000 for backup and
restore a database:
Backup Database DatabaseName to Disk='file path' with INIT
Backup Log DatabaseName to Disk='file path' with INIT
Alter Database DatabaseName set offline with ROLLBACK IMMEDIATE
Restore Database From Disk='file path' with NORECOVERY
Restore Log From Disk='file path'
It fails in SQL 2005 bate 2. It complains the Log has not been backed up.
Does anybody have any ideal about it?Just test once using workbench ..Do you get same error there as well..
"Siemens" wrote:
> We are using following commands and options in SQL 2000 for backup and
> restore a database:
> Backup Database DatabaseName to Disk='file path' with INIT
> Backup Log DatabaseName to Disk='file path' with INIT
> Alter Database DatabaseName set offline with ROLLBACK IMMEDIATE
> Restore Database From Disk='file path' with NORECOVERY
> Restore Log From Disk='file path'
> It fails in SQL 2005 bate 2. It complains the Log has not been backed up.
> Does anybody have any ideal about it?|||Can you post the detailed error message?
Can you download the SQL 2005 June CTP and try it there?
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:1E28D028-80FF-422A-AC55-8CB5D3ADC41C@.microsoft.com...
> Just test once using workbench ..Do you get same error there as well..
> "Siemens" wrote:
> > We are using following commands and options in SQL 2000 for backup and
> > restore a database:
> > Backup Database DatabaseName to Disk='file path' with INIT
> > Backup Log DatabaseName to Disk='file path' with INIT
> > Alter Database DatabaseName set offline with ROLLBACK IMMEDIATE
> > Restore Database From Disk='file path' with NORECOVERY
> > Restore Log From Disk='file path'
> > It fails in SQL 2005 bate 2. It complains the Log has not been backed
up.
> > Does anybody have any ideal about it?|||That is a new feature in 2005. If you want to restore over a database, SQL Server want you to backup
the tail of the log first. If you don't want to do that, you can use REPLACE with the restore
command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Siemens" <Siemens@.discussions.microsoft.com> wrote in message
news:CE1B6A41-57DC-4944-AC82-389E735FACD8@.microsoft.com...
> We are using following commands and options in SQL 2000 for backup and
> restore a database:
> Backup Database DatabaseName to Disk='file path' with INIT
> Backup Log DatabaseName to Disk='file path' with INIT
> Alter Database DatabaseName set offline with ROLLBACK IMMEDIATE
> Restore Database From Disk='file path' with NORECOVERY
> Restore Log From Disk='file path'
> It fails in SQL 2005 bate 2. It complains the Log has not been backed up.
> Does anybody have any ideal about it?
restore a database:
Backup Database DatabaseName to Disk='file path' with INIT
Backup Log DatabaseName to Disk='file path' with INIT
Alter Database DatabaseName set offline with ROLLBACK IMMEDIATE
Restore Database From Disk='file path' with NORECOVERY
Restore Log From Disk='file path'
It fails in SQL 2005 bate 2. It complains the Log has not been backed up.
Does anybody have any ideal about it?Just test once using workbench ..Do you get same error there as well..
"Siemens" wrote:
> We are using following commands and options in SQL 2000 for backup and
> restore a database:
> Backup Database DatabaseName to Disk='file path' with INIT
> Backup Log DatabaseName to Disk='file path' with INIT
> Alter Database DatabaseName set offline with ROLLBACK IMMEDIATE
> Restore Database From Disk='file path' with NORECOVERY
> Restore Log From Disk='file path'
> It fails in SQL 2005 bate 2. It complains the Log has not been backed up.
> Does anybody have any ideal about it?|||Can you post the detailed error message?
Can you download the SQL 2005 June CTP and try it there?
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:1E28D028-80FF-422A-AC55-8CB5D3ADC41C@.microsoft.com...
> Just test once using workbench ..Do you get same error there as well..
> "Siemens" wrote:
> > We are using following commands and options in SQL 2000 for backup and
> > restore a database:
> > Backup Database DatabaseName to Disk='file path' with INIT
> > Backup Log DatabaseName to Disk='file path' with INIT
> > Alter Database DatabaseName set offline with ROLLBACK IMMEDIATE
> > Restore Database From Disk='file path' with NORECOVERY
> > Restore Log From Disk='file path'
> > It fails in SQL 2005 bate 2. It complains the Log has not been backed
up.
> > Does anybody have any ideal about it?|||That is a new feature in 2005. If you want to restore over a database, SQL Server want you to backup
the tail of the log first. If you don't want to do that, you can use REPLACE with the restore
command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Siemens" <Siemens@.discussions.microsoft.com> wrote in message
news:CE1B6A41-57DC-4944-AC82-389E735FACD8@.microsoft.com...
> We are using following commands and options in SQL 2000 for backup and
> restore a database:
> Backup Database DatabaseName to Disk='file path' with INIT
> Backup Log DatabaseName to Disk='file path' with INIT
> Alter Database DatabaseName set offline with ROLLBACK IMMEDIATE
> Restore Database From Disk='file path' with NORECOVERY
> Restore Log From Disk='file path'
> It fails in SQL 2005 bate 2. It complains the Log has not been backed up.
> Does anybody have any ideal about it?
Subscribe to:
Posts (Atom)