Database Unit Testing: log

Showing posts with label log. Show all posts

Thursday, March 29, 2012

Database stored in .ldf file

Problem:
Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
Databases are set up in reverse. Data is stored in .ldf, and log files are
stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Kno
w
how to do this)
Can I shrink the logs even though they're in .mdf named files?
Is it ok to leave them alone in the reversed naming convention state?
Can I detach the databases, create a new .mdf, restore the database(.ldf)
from backup to the new .mdf file? If so, do I need to restore the log file
also, or can I start a new one from scratch?
Thanks for the help.Name is totally irrelevant to SQL Server, as well as extension. If you feel
it is OK, you can keep
the names. One option is to backup, detach (for safety - keep the files some
where else), and with
restore use the MOVE option to specify new file names. Or possibly only deta
ch, rename files and
attach specifying new correct file names (do this from QA not EM - QA gives
you full control over
the parameters used to sp_attach_db procedure).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick A" <Rick A@.discussions.microsoft.com> wrote in message
news:326AE016-3AAE-47B0-B216-E8EA16549B6A@.microsoft.com...
> Problem:
> Contractor migrated Great Plains databases to new server (Win2000, SQL2000
).
> Databases are set up in reverse. Data is stored in .ldf, and log files ar
e
> stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (K
now
> how to do this)
> Can I shrink the logs even though they're in .mdf named files?
> Is it ok to leave them alone in the reversed naming convention state?
> Can I detach the databases, create a new .mdf, restore the database(.ldf)
> from backup to the new .mdf file? If so, do I need to restore the log fil
e
> also, or can I start a new one from scratch?
> Thanks for the help.
>
>
>
>sql

Database stored in .ldf file

Problem:
Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
Databases are set up in reverse. Data is stored in .ldf, and log files are
stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
how to do this)
Can I shrink the logs even though they're in .mdf named files?
Is it ok to leave them alone in the reversed naming convention state?
Can I detach the databases, create a new .mdf, restore the database(.ldf)
from backup to the new .mdf file? If so, do I need to restore the log file
also, or can I start a new one from scratch?
Thanks for the help.
Name is totally irrelevant to SQL Server, as well as extension. If you feel it is OK, you can keep
the names. One option is to backup, detach (for safety - keep the files somewhere else), and with
restore use the MOVE option to specify new file names. Or possibly only detach, rename files and
attach specifying new correct file names (do this from QA not EM - QA gives you full control over
the parameters used to sp_attach_db procedure).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick A" <Rick A@.discussions.microsoft.com> wrote in message
news:326AE016-3AAE-47B0-B216-E8EA16549B6A@.microsoft.com...
> Problem:
> Contractor migrated Great Plains databases to new server (Win2000, SQL2000).
> Databases are set up in reverse. Data is stored in .ldf, and log files are
> stored in .mdf. Log file(.mdf) out of control and needs to be shrunk. (Know
> how to do this)
> Can I shrink the logs even though they're in .mdf named files?
> Is it ok to leave them alone in the reversed naming convention state?
> Can I detach the databases, create a new .mdf, restore the database(.ldf)
> from backup to the new .mdf file? If so, do I need to restore the log file
> also, or can I start a new one from scratch?
> Thanks for the help.
>
>
>
>

Database starting

What does it mean when your log says over and over and
over again (even up to an hour ago) that your database is
starting up and yet the database looks fine. You can
open it in Enterprise Manager, etc.
turn off "autoclose" at the database level.
"NewPoster" <anonymous@.discussions.microsoft.com> wrote in message
news:281401c4de06$ee625bd0$a301280a@.phx.gbl...
> What does it mean when your log says over and over and
> over again (even up to an hour ago) that your database is
> starting up and yet the database looks fine. You can
> open it in Enterprise Manager, etc.
|||It sounds like your database has autoclose set to on. In Enterprise manager
right click on your database and select properties. From properties choose
the options tab. Uncheck auto close. Click OK at the bottom of the window and
you should be OK.
"NewPoster" wrote:

> What does it mean when your log says over and over and
> over again (even up to an hour ago) that your database is
> starting up and yet the database looks fine. You can
> open it in Enterprise Manager, etc.
>
sql

Database starting

What does it mean when your log says over and over and
over again (even up to an hour ago) that your database is
starting up and yet the database looks fine. You can
open it in Enterprise Manager, etc.turn off "autoclose" at the database level.
"NewPoster" <anonymous@.discussions.microsoft.com> wrote in message
news:281401c4de06$ee625bd0$a301280a@.phx.gbl...
> What does it mean when your log says over and over and
> over again (even up to an hour ago) that your database is
> starting up and yet the database looks fine. You can
> open it in Enterprise Manager, etc.|||It sounds like your database has autoclose set to on. In Enterprise manager
right click on your database and select properties. From properties choose
the options tab. Uncheck auto close. Click OK at the bottom of the window and
you should be OK.
"NewPoster" wrote:
> What does it mean when your log says over and over and
> over again (even up to an hour ago) that your database is
> starting up and yet the database looks fine. You can
> open it in Enterprise Manager, etc.
>

Database space monitoring.

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

Database space monitoring.

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

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

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

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

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

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

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

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

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

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

Tuesday, March 27, 2012

database space

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

Database Snapshots, Mirroring, and Log Shipping

Can someone explain to me how/why you can create a database snapshot
against a mirrored database, but not a log shipped database?Some extra logic is required to keep the snapshot active during recovery.
This is built into the mirroring logic. Log shipping just uses normal log
restore logic.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<jalbenberg@.yahoo.com> wrote in message
news:1169249796.950379.163120@.q2g2000cwa.googlegroups.com...
> Can someone explain to me how/why you can create a database snapshot
> against a mirrored database, but not a log shipped database?
>

Thursday, March 29, 2012

Database stored in .ldf file

Database stored in .ldf file

Database starting

Database starting

Database space monitoring.

Database space monitoring.

Database space monitoring.

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

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

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

Tuesday, March 27, 2012

database space

database space

database space

Database Snapshots, Mirroring, and Log Shipping

Database Unit Testing

Blog Archive

About Me