I have seen this message in Windows event log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
MohI'm not entirely certain about all 3 but there are at least 2 steps in a
recovery (whether a restore or a DB startup) and they are the
roll-forward and the roll-back. The roll-forward is "redoing" committed
transactions (they may have been written to the transaction log, as this
always happens first, but not yet written to the data file(s)). If the
server is shutdown properly, then this phase will should be very short,
if not non-existent. The roll-back phase is "undoing" transactions that
had not been committed at the recovery point (so at the point the server
was shut down there may have been some transactions in progress that
hadn't been committed yet). These 2 phases will put the data in a
consistent state as at the recovery point.
I'm guessing the 3rd stage in the startup recovery would be bringing the
database "online" so it will accept user connections (in a RESTORE
process the very 1st phase (before roll-forward and then roll-back) is
the data copy phase but this wouldn't be necessary for a startup
recovery because the data would already be in the data files). There's
a fairly good page about it in BOL: Understanding How Restore and
Recovery of Backups Work
<http://msdn2.microsoft.com/en-us/li...8SQL.90%29.aspx>
With SQL 2000 the database is unusable until the end of the roll-back
phase. With SQL 2005 the database is now usable after the end of the
roll-forward phase. From memory they coined this "fast recovery" or
something like that (Database Engine Availability Enhancements
<http://msdn2.microsoft.com/en-us/li...8SQL.90%29.aspx> ).
Hope this helps.
*mike hodgson*
http://sqlnerd.blogspot.com
Moh wrote:
>I have seen this message in Windows event log after restarting database.
>3450:
>Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
>(approximately 2152 more seconds) (Phase 2 of 3)
>Question is:
>What are these three phases.
>Thx,
>Moh
>|||Just a note that in SQL 2005 its only Enterprise edition that allows the dat
abase to be accessed before the rol back phase is complete.
--
Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:OJ64mwqYGHA.1016@.
TK2MSFTNGP03.phx.gbl...
I'm not entirely certain about all 3 but there are at least 2 steps in a rec
overy (whether a restore or a DB startup) and they are the roll-forward and
the roll-back. The roll-forward is "redoing" committed transactions (they m
ay have been written to the transaction log, as this always happens first, b
ut not yet written to the data file(s)). If the server is shutdown properly
, then this phase will should be very short, if not non-existent. The roll-
back phase is "undoing" transactions that had not been committed at the reco
very point (so at the point the server was shut down there may have been som
e transactions in progress that hadn't been committed yet). These 2 phases
will put the data in a consistent state as at the recovery point.
I'm guessing the 3rd stage in the startup recovery would be bringing the dat
abase "online" so it will accept user connections (in a RESTORE process the
very 1st phase (before roll-forward and then roll-back) is the data copy pha
se but this wouldn't be necessary for a startup recovery because the data wo
uld already be in the data files). There's a fairly good page about it in B
OL: Understanding How Restore and Recovery of Backups Work
With SQL 2000 the database is unusable until the end of the roll-back phase.
With SQL 2005 the database is now usable after the end of the roll-forward
phase. From memory they coined this "fast recovery" or something like that
(Database Engine Availability Enhancements).
Hope this helps.
mike hodgson
http://sqlnerd.blogspot.com
Moh wrote:
I have seen this message in Windows event log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
Moh
Showing posts with label log. Show all posts
Showing posts with label log. Show all posts
Friday, March 30, 2012
Recovery phases
I have seen this message in Windows event log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
MohThis is a multi-part message in MIME format.
--080209040300000504020608
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
I'm not entirely certain about all 3 but there are at least 2 steps in a
recovery (whether a restore or a DB startup) and they are the
roll-forward and the roll-back. The roll-forward is "redoing" committed
transactions (they may have been written to the transaction log, as this
always happens first, but not yet written to the data file(s)). If the
server is shutdown properly, then this phase will should be very short,
if not non-existent. The roll-back phase is "undoing" transactions that
had not been committed at the recovery point (so at the point the server
was shut down there may have been some transactions in progress that
hadn't been committed yet). These 2 phases will put the data in a
consistent state as at the recovery point.
I'm guessing the 3rd stage in the startup recovery would be bringing the
database "online" so it will accept user connections (in a RESTORE
process the very 1st phase (before roll-forward and then roll-back) is
the data copy phase but this wouldn't be necessary for a startup
recovery because the data would already be in the data files). There's
a fairly good page about it in BOL: Understanding How Restore and
Recovery of Backups Work
<http://msdn2.microsoft.com/en-us/library/ms191455%28SQL.90%29.aspx>
With SQL 2000 the database is unusable until the end of the roll-back
phase. With SQL 2005 the database is now usable after the end of the
roll-forward phase. From memory they coined this "fast recovery" or
something like that (Database Engine Availability Enhancements
<http://msdn2.microsoft.com/en-us/library/ms171001%28SQL.90%29.aspx>).
Hope this helps.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Moh wrote:
>I have seen this message in Windows event log after restarting database.
>3450:
>Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
>(approximately 2152 more seconds) (Phase 2 of 3)
>Question is:
>What are these three phases.
>Thx,
>Moh
>
--080209040300000504020608
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I'm not entirely certain about all 3 but there are at least 2 steps
in a recovery (whether a restore or a DB startup) and they are the
roll-forward and the roll-back. The roll-forward is "redoing"
committed transactions (they may have been written to the transaction
log, as this always happens first, but not yet written to the data
file(s)). If the server is shutdown properly, then this phase will
should be very short, if not non-existent. The roll-back phase is
"undoing" transactions that had not been committed at the recovery
point (so at the point the server was shut down there may have been
some transactions in progress that hadn't been committed yet). These 2
phases will put the data in a consistent state as at the recovery point.<br>
<br>
I'm guessing the 3rd stage in the startup recovery would be bringing
the database "online" so it will accept user connections (in a RESTORE
process the very 1st phase (before roll-forward and then roll-back) is
the data copy phase but this wouldn't be necessary for a startup
recovery because the data would already be in the data files). There's
a fairly good page about it in BOL: <a
href="http://links.10026.com/?link=Understanding">http://msdn2.microsoft.com/en-us/library/ms191455%28SQL.90%29.aspx">Understanding
How Restore and Recovery of Backups Work</a><br>
<br>
With SQL 2000 the database is unusable until the end of the roll-back
phase. With SQL 2005 the database is now usable after the end of the
roll-forward phase. From memory they coined this "fast recovery" or
something like that (<a
href="http://links.10026.com/?link=Database">http://msdn2.microsoft.com/en-us/library/ms171001%28SQL.90%29.aspx">Database
Engine Availability Enhancements</a>).<br>
<br>
Hope this helps.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Moh wrote:
<blockquote cite="mid8C953261-676E-41D5-80E7-4A2064EB9823@.microsoft.com"
type="cite">
<pre wrap="">I have seen this message in Windows event log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
Moh
</pre>
</blockquote>
</body>
</html>
--080209040300000504020608--|||This is a multi-part message in MIME format.
--=_NextPart_000_00F1_01C662E8.12DAC620
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Just a note that in SQL 2005 its only Enterprise edition that allows the =database to be accessed before the rol back phase is complete.
-- Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message =news:OJ64mwqYGHA.1016@.TK2MSFTNGP03.phx.gbl...
I'm not entirely certain about all 3 but there are at least 2 steps in =a recovery (whether a restore or a DB startup) and they are the =roll-forward and the roll-back. The roll-forward is "redoing" committed =transactions (they may have been written to the transaction log, as this =always happens first, but not yet written to the data file(s)). If the =server is shutdown properly, then this phase will should be very short, =if not non-existent. The roll-back phase is "undoing" transactions that =had not been committed at the recovery point (so at the point the server =was shut down there may have been some transactions in progress that =hadn't been committed yet). These 2 phases will put the data in a =consistent state as at the recovery point.
I'm guessing the 3rd stage in the startup recovery would be bringing =the database "online" so it will accept user connections (in a RESTORE =process the very 1st phase (before roll-forward and then roll-back) is =the data copy phase but this wouldn't be necessary for a startup =recovery because the data would already be in the data files). There's =a fairly good page about it in BOL: Understanding How Restore and =Recovery of Backups Work
With SQL 2000 the database is unusable until the end of the roll-back =phase. With SQL 2005 the database is now usable after the end of the =roll-forward phase. From memory they coined this "fast recovery" or =something like that (Database Engine Availability Enhancements).
Hope this helps.
--
mike hodgson
http://sqlnerd.blogspot.com=20
Moh wrote: I have seen this message in Windows event log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
Moh
--=_NextPart_000_00F1_01C662E8.12DAC620
Content-Type: text/html;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Just a note that in SQL 2005 its only =Enterprise edition that allows the database to be accessed before the rol back =phase is complete.
-- Simon SabinSQL Server MVPhttp://sqljunkies.com/weblog/simons">http://sqljunkies.com/weblog=/simons
"Mike Hodgson" wrote =in message news:OJ64mwqYGHA.1016=@.TK2MSFTNGP03.phx.gbl...I'm not entirely certain about all 3 but there are at least 2 steps in a =recovery (whether a restore or a DB startup) and they are the roll-forward and =the roll-back. The roll-forward is "redoing" committed transactions =(they may have been written to the transaction log, as this always happens =first, but not yet written to the data file(s)). If the server is =shutdown properly, then this phase will should be very short, if not non-existent. The roll-back phase is "undoing" transactions that =had not been committed at the recovery point (so at the point the server was =shut down there may have been some transactions in progress that hadn't been =committed yet). These 2 phases will put the data in a consistent state as =at the recovery point.I'm guessing the 3rd stage in the startup =recovery would be bringing the database "online" so it will accept user =connections (in a RESTORE process the very 1st phase (before roll-forward and then =roll-back) is the data copy phase but this wouldn't be necessary for a startup =recovery because the data would already be in the data files). There's a =fairly good page about it in BOL: Understanding How Restore and Recovery of Backups WorkWith SQL 2000 the =database is unusable until the end of the roll-back phase. With SQL 2005 =the database is now usable after the end of the roll-forward phase. =From memory they coined this "fast recovery" or something like that (Database Engine Availability Enhancements).Hope this =helps.
--mike =hodgsonhttp://sqlnerd.blogspot.com Moh wrote: I have seen this message in Windows event =log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
Moh
--=_NextPart_000_00F1_01C662E8.12DAC620--sql
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
MohThis is a multi-part message in MIME format.
--080209040300000504020608
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
I'm not entirely certain about all 3 but there are at least 2 steps in a
recovery (whether a restore or a DB startup) and they are the
roll-forward and the roll-back. The roll-forward is "redoing" committed
transactions (they may have been written to the transaction log, as this
always happens first, but not yet written to the data file(s)). If the
server is shutdown properly, then this phase will should be very short,
if not non-existent. The roll-back phase is "undoing" transactions that
had not been committed at the recovery point (so at the point the server
was shut down there may have been some transactions in progress that
hadn't been committed yet). These 2 phases will put the data in a
consistent state as at the recovery point.
I'm guessing the 3rd stage in the startup recovery would be bringing the
database "online" so it will accept user connections (in a RESTORE
process the very 1st phase (before roll-forward and then roll-back) is
the data copy phase but this wouldn't be necessary for a startup
recovery because the data would already be in the data files). There's
a fairly good page about it in BOL: Understanding How Restore and
Recovery of Backups Work
<http://msdn2.microsoft.com/en-us/library/ms191455%28SQL.90%29.aspx>
With SQL 2000 the database is unusable until the end of the roll-back
phase. With SQL 2005 the database is now usable after the end of the
roll-forward phase. From memory they coined this "fast recovery" or
something like that (Database Engine Availability Enhancements
<http://msdn2.microsoft.com/en-us/library/ms171001%28SQL.90%29.aspx>).
Hope this helps.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Moh wrote:
>I have seen this message in Windows event log after restarting database.
>3450:
>Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
>(approximately 2152 more seconds) (Phase 2 of 3)
>Question is:
>What are these three phases.
>Thx,
>Moh
>
--080209040300000504020608
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I'm not entirely certain about all 3 but there are at least 2 steps
in a recovery (whether a restore or a DB startup) and they are the
roll-forward and the roll-back. The roll-forward is "redoing"
committed transactions (they may have been written to the transaction
log, as this always happens first, but not yet written to the data
file(s)). If the server is shutdown properly, then this phase will
should be very short, if not non-existent. The roll-back phase is
"undoing" transactions that had not been committed at the recovery
point (so at the point the server was shut down there may have been
some transactions in progress that hadn't been committed yet). These 2
phases will put the data in a consistent state as at the recovery point.<br>
<br>
I'm guessing the 3rd stage in the startup recovery would be bringing
the database "online" so it will accept user connections (in a RESTORE
process the very 1st phase (before roll-forward and then roll-back) is
the data copy phase but this wouldn't be necessary for a startup
recovery because the data would already be in the data files). There's
a fairly good page about it in BOL: <a
href="http://links.10026.com/?link=Understanding">http://msdn2.microsoft.com/en-us/library/ms191455%28SQL.90%29.aspx">Understanding
How Restore and Recovery of Backups Work</a><br>
<br>
With SQL 2000 the database is unusable until the end of the roll-back
phase. With SQL 2005 the database is now usable after the end of the
roll-forward phase. From memory they coined this "fast recovery" or
something like that (<a
href="http://links.10026.com/?link=Database">http://msdn2.microsoft.com/en-us/library/ms171001%28SQL.90%29.aspx">Database
Engine Availability Enhancements</a>).<br>
<br>
Hope this helps.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Moh wrote:
<blockquote cite="mid8C953261-676E-41D5-80E7-4A2064EB9823@.microsoft.com"
type="cite">
<pre wrap="">I have seen this message in Windows event log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
Moh
</pre>
</blockquote>
</body>
</html>
--080209040300000504020608--|||This is a multi-part message in MIME format.
--=_NextPart_000_00F1_01C662E8.12DAC620
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Just a note that in SQL 2005 its only Enterprise edition that allows the =database to be accessed before the rol back phase is complete.
-- Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message =news:OJ64mwqYGHA.1016@.TK2MSFTNGP03.phx.gbl...
I'm not entirely certain about all 3 but there are at least 2 steps in =a recovery (whether a restore or a DB startup) and they are the =roll-forward and the roll-back. The roll-forward is "redoing" committed =transactions (they may have been written to the transaction log, as this =always happens first, but not yet written to the data file(s)). If the =server is shutdown properly, then this phase will should be very short, =if not non-existent. The roll-back phase is "undoing" transactions that =had not been committed at the recovery point (so at the point the server =was shut down there may have been some transactions in progress that =hadn't been committed yet). These 2 phases will put the data in a =consistent state as at the recovery point.
I'm guessing the 3rd stage in the startup recovery would be bringing =the database "online" so it will accept user connections (in a RESTORE =process the very 1st phase (before roll-forward and then roll-back) is =the data copy phase but this wouldn't be necessary for a startup =recovery because the data would already be in the data files). There's =a fairly good page about it in BOL: Understanding How Restore and =Recovery of Backups Work
With SQL 2000 the database is unusable until the end of the roll-back =phase. With SQL 2005 the database is now usable after the end of the =roll-forward phase. From memory they coined this "fast recovery" or =something like that (Database Engine Availability Enhancements).
Hope this helps.
--
mike hodgson
http://sqlnerd.blogspot.com=20
Moh wrote: I have seen this message in Windows event log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
Moh
--=_NextPart_000_00F1_01C662E8.12DAC620
Content-Type: text/html;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Just a note that in SQL 2005 its only =Enterprise edition that allows the database to be accessed before the rol back =phase is complete.
-- Simon SabinSQL Server MVPhttp://sqljunkies.com/weblog/simons">http://sqljunkies.com/weblog=/simons
"Mike Hodgson"
--mike =hodgsonhttp://sqlnerd.blogspot.com Moh wrote: I have seen this message in Windows event =log after restarting database.
3450:
Recovery of database 'IMAX_INV_Mgmt' (5) is 0% complete.
(approximately 2152 more seconds) (Phase 2 of 3)
Question is:
What are these three phases.
Thx,
Moh
--=_NextPart_000_00F1_01C662E8.12DAC620--sql
Recovery options when the transaction log is unavailable
Hi,
On MS SQL Server 2000 SP3, what are my recovery options when only the
database transaction log becomes unavailable and when there are active
transactions in the database?
Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
scenario, I have tried that.
Are there any other options and how much of data loss to expect in each case?
-- Many thanks, OskarIs the DB currenlty in Suspect state, have you tried...
sp_add_log_file_recover_suspect_db , Read BOL to get more information
regarding this.
For Active transactions on a damaged / corrupted database you can do a
transacgion log backup using NO_TRUNCATE option. This will backup all the
active transaction in the database. This command will be used only when your
database is damaged.
BACKUP LOG DBNAME TO DISK='C:\db_corr.trn' WITH NO_TRUNCATE
So once the DB is up..
Restorethe last TX log backup taken with NO_TRUNCATE. During this time
mention 'RECOVERY' during RESTORE
Please do share once you restore the DB or let us know if you have any other
queries...
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Oskar" wrote:
> Hi,
> On MS SQL Server 2000 SP3, what are my recovery options when only the
> database transaction log becomes unavailable and when there are active
> transactions in the database?
> Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
> scenario, I have tried that.
> Are there any other options and how much of data loss to expect in each case?
> -- Many thanks, Oskar
>|||Sorry, but neither of your suggestions work:
sp_add_log_file_recover_suspect_db 'TestDB1', 'TestDB1_Log',
'C:\TEMP\TestDB1_Log.LDF'
returned this error
Server: Msg 5004, Level 16, State 2, Line 1
To use ALTER DATABASE, the database must be in a writable state in which a
checkpoint can be executed.
ALTER DATABASE TestDB1 ADD LOG FILE(NAME = [TestDB1_Log], FILENAME ='C:\TEMP\TestDB1_Log.LDF' )
Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
incorrect.
Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
incorrect.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
and I can't backup the transaction log because it is not accessible:
backup tran TestDB1 to disk = 'C:\BACKUP\TestDB1_backup' with no_truncate
returns
Server: Msg 3447, Level 16, State 1, Line 1
Could not activate or scan all of the log files for database 'TestDB1'.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
incorrect.
So it seems, that only option I have in this case is to restore from backup.
-- Thanks, Oskar
"Sreejith G" wrote:
> Is the DB currenlty in Suspect state, have you tried...
> sp_add_log_file_recover_suspect_db , Read BOL to get more information
> regarding this.
> For Active transactions on a damaged / corrupted database you can do a
> transacgion log backup using NO_TRUNCATE option. This will backup all the
> active transaction in the database. This command will be used only when your
> database is damaged.
> BACKUP LOG DBNAME TO DISK='C:\db_corr.trn' WITH NO_TRUNCATE
> So once the DB is up..
> Restorethe last TX log backup taken with NO_TRUNCATE. During this time
> mention 'RECOVERY' during RESTORE
> Please do share once you restore the DB or let us know if you have any other
> queries...
>
>
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "Oskar" wrote:
> > Hi,
> >
> > On MS SQL Server 2000 SP3, what are my recovery options when only the
> > database transaction log becomes unavailable and when there are active
> > transactions in the database?
> > Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
> > scenario, I have tried that.
> > Are there any other options and how much of data loss to expect in each case?
> >
> > -- Many thanks, Oskar
> >|||In your first thread you said u tried detach/attach/sp_attach_single_file_db,
Below is the syntax and after detach you need not have to execute sp_attach
EXEC sp_detach_db @.dbname = 'pubs'
EXEC sp_attach_single_file_db @.dbname = 'pubs',
@.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
Can you answer the below questions.
Is the DB in SUSPECT state?
Do your DB have multiple data file?
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Oskar" wrote:
> Sorry, but neither of your suggestions work:
> sp_add_log_file_recover_suspect_db 'TestDB1', 'TestDB1_Log',
> 'C:\TEMP\TestDB1_Log.LDF'
> returned this error
> Server: Msg 5004, Level 16, State 2, Line 1
> To use ALTER DATABASE, the database must be in a writable state in which a
> checkpoint can be executed.
> ALTER DATABASE TestDB1 ADD LOG FILE(NAME = [TestDB1_Log], FILENAME => 'C:\TEMP\TestDB1_Log.LDF' )
> Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> incorrect.
> Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> incorrect.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> and I can't backup the transaction log because it is not accessible:
> backup tran TestDB1 to disk = 'C:\BACKUP\TestDB1_backup' with no_truncate
> returns
> Server: Msg 3447, Level 16, State 1, Line 1
> Could not activate or scan all of the log files for database 'TestDB1'.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP LOG is terminating abnormally.
> Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> incorrect.
> So it seems, that only option I have in this case is to restore from backup.
> -- Thanks, Oskar
> "Sreejith G" wrote:
> > Is the DB currenlty in Suspect state, have you tried...
> >
> > sp_add_log_file_recover_suspect_db , Read BOL to get more information
> > regarding this.
> >
> > For Active transactions on a damaged / corrupted database you can do a
> > transacgion log backup using NO_TRUNCATE option. This will backup all the
> > active transaction in the database. This command will be used only when your
> > database is damaged.
> >
> > BACKUP LOG DBNAME TO DISK='C:\db_corr.trn' WITH NO_TRUNCATE
> >
> > So once the DB is up..
> > Restorethe last TX log backup taken with NO_TRUNCATE. During this time
> > mention 'RECOVERY' during RESTORE
> >
> > Please do share once you restore the DB or let us know if you have any other
> > queries...
> >
> >
> >
> >
> >
> > --
> > Thanks,
> > Sree
> > [Please specify the version of Sql Server as we can save one thread and time
> > asking back if its 2000 or 2005]
> >
> >
> >
> > "Oskar" wrote:
> >
> > > Hi,
> > >
> > > On MS SQL Server 2000 SP3, what are my recovery options when only the
> > > database transaction log becomes unavailable and when there are active
> > > transactions in the database?
> > > Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
> > > scenario, I have tried that.
> > > Are there any other options and how much of data loss to expect in each case?
> > >
> > > -- Many thanks, Oskar
> > >|||1) Yes, it is suspect as you would expect it to be with inaccessible tran.
log file.
2) No, single data file.
-- Thanks, Oskar
"Sreejith G" wrote:
> In your first thread you said u tried detach/attach/sp_attach_single_file_db,
> Below is the syntax and after detach you need not have to execute sp_attach
> EXEC sp_detach_db @.dbname = 'pubs'
> EXEC sp_attach_single_file_db @.dbname = 'pubs',
> @.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
> Can you answer the below questions.
> Is the DB in SUSPECT state?
> Do your DB have multiple data file?
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "Oskar" wrote:
> > Sorry, but neither of your suggestions work:
> >
> > sp_add_log_file_recover_suspect_db 'TestDB1', 'TestDB1_Log',
> > 'C:\TEMP\TestDB1_Log.LDF'
> >
> > returned this error
> >
> > Server: Msg 5004, Level 16, State 2, Line 1
> > To use ALTER DATABASE, the database must be in a writable state in which a
> > checkpoint can be executed.
> > ALTER DATABASE TestDB1 ADD LOG FILE(NAME = [TestDB1_Log], FILENAME => > 'C:\TEMP\TestDB1_Log.LDF' )
> > Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> > incorrect.
> > Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> > incorrect.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> > and I can't backup the transaction log because it is not accessible:
> >
> > backup tran TestDB1 to disk = 'C:\BACKUP\TestDB1_backup' with no_truncate
> >
> > returns
> >
> > Server: Msg 3447, Level 16, State 1, Line 1
> > Could not activate or scan all of the log files for database 'TestDB1'.
> > Server: Msg 3013, Level 16, State 1, Line 1
> > BACKUP LOG is terminating abnormally.
> > Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> > incorrect.
> >
> > So it seems, that only option I have in this case is to restore from backup.
> >
> > -- Thanks, Oskar
> >
> > "Sreejith G" wrote:
> >
> > > Is the DB currenlty in Suspect state, have you tried...
> > >
> > > sp_add_log_file_recover_suspect_db , Read BOL to get more information
> > > regarding this.
> > >
> > > For Active transactions on a damaged / corrupted database you can do a
> > > transacgion log backup using NO_TRUNCATE option. This will backup all the
> > > active transaction in the database. This command will be used only when your
> > > database is damaged.
> > >
> > > BACKUP LOG DBNAME TO DISK='C:\db_corr.trn' WITH NO_TRUNCATE
> > >
> > > So once the DB is up..
> > > Restorethe last TX log backup taken with NO_TRUNCATE. During this time
> > > mention 'RECOVERY' during RESTORE
> > >
> > > Please do share once you restore the DB or let us know if you have any other
> > > queries...
> > >
> > >
> > >
> > >
> > >
> > > --
> > > Thanks,
> > > Sree
> > > [Please specify the version of Sql Server as we can save one thread and time
> > > asking back if its 2000 or 2005]
> > >
> > >
> > >
> > > "Oskar" wrote:
> > >
> > > > Hi,
> > > >
> > > > On MS SQL Server 2000 SP3, what are my recovery options when only the
> > > > database transaction log becomes unavailable and when there are active
> > > > transactions in the database?
> > > > Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
> > > > scenario, I have tried that.
> > > > Are there any other options and how much of data loss to expect in each case?
> > > >
> > > > -- Many thanks, Oskar
> > > >
On MS SQL Server 2000 SP3, what are my recovery options when only the
database transaction log becomes unavailable and when there are active
transactions in the database?
Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
scenario, I have tried that.
Are there any other options and how much of data loss to expect in each case?
-- Many thanks, OskarIs the DB currenlty in Suspect state, have you tried...
sp_add_log_file_recover_suspect_db , Read BOL to get more information
regarding this.
For Active transactions on a damaged / corrupted database you can do a
transacgion log backup using NO_TRUNCATE option. This will backup all the
active transaction in the database. This command will be used only when your
database is damaged.
BACKUP LOG DBNAME TO DISK='C:\db_corr.trn' WITH NO_TRUNCATE
So once the DB is up..
Restorethe last TX log backup taken with NO_TRUNCATE. During this time
mention 'RECOVERY' during RESTORE
Please do share once you restore the DB or let us know if you have any other
queries...
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Oskar" wrote:
> Hi,
> On MS SQL Server 2000 SP3, what are my recovery options when only the
> database transaction log becomes unavailable and when there are active
> transactions in the database?
> Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
> scenario, I have tried that.
> Are there any other options and how much of data loss to expect in each case?
> -- Many thanks, Oskar
>|||Sorry, but neither of your suggestions work:
sp_add_log_file_recover_suspect_db 'TestDB1', 'TestDB1_Log',
'C:\TEMP\TestDB1_Log.LDF'
returned this error
Server: Msg 5004, Level 16, State 2, Line 1
To use ALTER DATABASE, the database must be in a writable state in which a
checkpoint can be executed.
ALTER DATABASE TestDB1 ADD LOG FILE(NAME = [TestDB1_Log], FILENAME ='C:\TEMP\TestDB1_Log.LDF' )
Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
incorrect.
Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
incorrect.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
and I can't backup the transaction log because it is not accessible:
backup tran TestDB1 to disk = 'C:\BACKUP\TestDB1_backup' with no_truncate
returns
Server: Msg 3447, Level 16, State 1, Line 1
Could not activate or scan all of the log files for database 'TestDB1'.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
incorrect.
So it seems, that only option I have in this case is to restore from backup.
-- Thanks, Oskar
"Sreejith G" wrote:
> Is the DB currenlty in Suspect state, have you tried...
> sp_add_log_file_recover_suspect_db , Read BOL to get more information
> regarding this.
> For Active transactions on a damaged / corrupted database you can do a
> transacgion log backup using NO_TRUNCATE option. This will backup all the
> active transaction in the database. This command will be used only when your
> database is damaged.
> BACKUP LOG DBNAME TO DISK='C:\db_corr.trn' WITH NO_TRUNCATE
> So once the DB is up..
> Restorethe last TX log backup taken with NO_TRUNCATE. During this time
> mention 'RECOVERY' during RESTORE
> Please do share once you restore the DB or let us know if you have any other
> queries...
>
>
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "Oskar" wrote:
> > Hi,
> >
> > On MS SQL Server 2000 SP3, what are my recovery options when only the
> > database transaction log becomes unavailable and when there are active
> > transactions in the database?
> > Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
> > scenario, I have tried that.
> > Are there any other options and how much of data loss to expect in each case?
> >
> > -- Many thanks, Oskar
> >|||In your first thread you said u tried detach/attach/sp_attach_single_file_db,
Below is the syntax and after detach you need not have to execute sp_attach
EXEC sp_detach_db @.dbname = 'pubs'
EXEC sp_attach_single_file_db @.dbname = 'pubs',
@.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
Can you answer the below questions.
Is the DB in SUSPECT state?
Do your DB have multiple data file?
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Oskar" wrote:
> Sorry, but neither of your suggestions work:
> sp_add_log_file_recover_suspect_db 'TestDB1', 'TestDB1_Log',
> 'C:\TEMP\TestDB1_Log.LDF'
> returned this error
> Server: Msg 5004, Level 16, State 2, Line 1
> To use ALTER DATABASE, the database must be in a writable state in which a
> checkpoint can be executed.
> ALTER DATABASE TestDB1 ADD LOG FILE(NAME = [TestDB1_Log], FILENAME => 'C:\TEMP\TestDB1_Log.LDF' )
> Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> incorrect.
> Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> incorrect.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> and I can't backup the transaction log because it is not accessible:
> backup tran TestDB1 to disk = 'C:\BACKUP\TestDB1_backup' with no_truncate
> returns
> Server: Msg 3447, Level 16, State 1, Line 1
> Could not activate or scan all of the log files for database 'TestDB1'.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP LOG is terminating abnormally.
> Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> incorrect.
> So it seems, that only option I have in this case is to restore from backup.
> -- Thanks, Oskar
> "Sreejith G" wrote:
> > Is the DB currenlty in Suspect state, have you tried...
> >
> > sp_add_log_file_recover_suspect_db , Read BOL to get more information
> > regarding this.
> >
> > For Active transactions on a damaged / corrupted database you can do a
> > transacgion log backup using NO_TRUNCATE option. This will backup all the
> > active transaction in the database. This command will be used only when your
> > database is damaged.
> >
> > BACKUP LOG DBNAME TO DISK='C:\db_corr.trn' WITH NO_TRUNCATE
> >
> > So once the DB is up..
> > Restorethe last TX log backup taken with NO_TRUNCATE. During this time
> > mention 'RECOVERY' during RESTORE
> >
> > Please do share once you restore the DB or let us know if you have any other
> > queries...
> >
> >
> >
> >
> >
> > --
> > Thanks,
> > Sree
> > [Please specify the version of Sql Server as we can save one thread and time
> > asking back if its 2000 or 2005]
> >
> >
> >
> > "Oskar" wrote:
> >
> > > Hi,
> > >
> > > On MS SQL Server 2000 SP3, what are my recovery options when only the
> > > database transaction log becomes unavailable and when there are active
> > > transactions in the database?
> > > Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
> > > scenario, I have tried that.
> > > Are there any other options and how much of data loss to expect in each case?
> > >
> > > -- Many thanks, Oskar
> > >|||1) Yes, it is suspect as you would expect it to be with inaccessible tran.
log file.
2) No, single data file.
-- Thanks, Oskar
"Sreejith G" wrote:
> In your first thread you said u tried detach/attach/sp_attach_single_file_db,
> Below is the syntax and after detach you need not have to execute sp_attach
> EXEC sp_detach_db @.dbname = 'pubs'
> EXEC sp_attach_single_file_db @.dbname = 'pubs',
> @.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
> Can you answer the below questions.
> Is the DB in SUSPECT state?
> Do your DB have multiple data file?
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "Oskar" wrote:
> > Sorry, but neither of your suggestions work:
> >
> > sp_add_log_file_recover_suspect_db 'TestDB1', 'TestDB1_Log',
> > 'C:\TEMP\TestDB1_Log.LDF'
> >
> > returned this error
> >
> > Server: Msg 5004, Level 16, State 2, Line 1
> > To use ALTER DATABASE, the database must be in a writable state in which a
> > checkpoint can be executed.
> > ALTER DATABASE TestDB1 ADD LOG FILE(NAME = [TestDB1_Log], FILENAME => > 'C:\TEMP\TestDB1_Log.LDF' )
> > Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> > incorrect.
> > Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> > incorrect.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> > and I can't backup the transaction log because it is not accessible:
> >
> > backup tran TestDB1 to disk = 'C:\BACKUP\TestDB1_backup' with no_truncate
> >
> > returns
> >
> > Server: Msg 3447, Level 16, State 1, Line 1
> > Could not activate or scan all of the log files for database 'TestDB1'.
> > Server: Msg 3013, Level 16, State 1, Line 1
> > BACKUP LOG is terminating abnormally.
> > Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> > incorrect.
> >
> > So it seems, that only option I have in this case is to restore from backup.
> >
> > -- Thanks, Oskar
> >
> > "Sreejith G" wrote:
> >
> > > Is the DB currenlty in Suspect state, have you tried...
> > >
> > > sp_add_log_file_recover_suspect_db , Read BOL to get more information
> > > regarding this.
> > >
> > > For Active transactions on a damaged / corrupted database you can do a
> > > transacgion log backup using NO_TRUNCATE option. This will backup all the
> > > active transaction in the database. This command will be used only when your
> > > database is damaged.
> > >
> > > BACKUP LOG DBNAME TO DISK='C:\db_corr.trn' WITH NO_TRUNCATE
> > >
> > > So once the DB is up..
> > > Restorethe last TX log backup taken with NO_TRUNCATE. During this time
> > > mention 'RECOVERY' during RESTORE
> > >
> > > Please do share once you restore the DB or let us know if you have any other
> > > queries...
> > >
> > >
> > >
> > >
> > >
> > > --
> > > Thanks,
> > > Sree
> > > [Please specify the version of Sql Server as we can save one thread and time
> > > asking back if its 2000 or 2005]
> > >
> > >
> > >
> > > "Oskar" wrote:
> > >
> > > > Hi,
> > > >
> > > > On MS SQL Server 2000 SP3, what are my recovery options when only the
> > > > database transaction log becomes unavailable and when there are active
> > > > transactions in the database?
> > > > Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
> > > > scenario, I have tried that.
> > > > Are there any other options and how much of data loss to expect in each case?
> > > >
> > > > -- Many thanks, Oskar
> > > >
Recovery options when the transaction log is unavailable
Hi,
On MS SQL Server 2000 SP3, what are my recovery options when only the
database transaction log becomes unavailable and when there are active
transactions in the database?
Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
scenario, I have tried that.
Are there any other options and how much of data loss to expect in each case?
-- Many thanks, Oskar
Is the DB currenlty in Suspect state, have you tried...
sp_add_log_file_recover_suspect_db , Read BOL to get more information
regarding this.
For Active transactions on a damaged / corrupted database you can do a
transacgion log backup using NO_TRUNCATE option. This will backup all the
active transaction in the database. This command will be used only when your
database is damaged.
BACKUP LOG DBNAME TO DISK='C:\db_corr.trn' WITH NO_TRUNCATE
So once the DB is up..
Restorethe last TX log backup taken with NO_TRUNCATE. During this time
mention 'RECOVERY' during RESTORE
Please do share once you restore the DB or let us know if you have any other
queries...
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Oskar" wrote:
> Hi,
> On MS SQL Server 2000 SP3, what are my recovery options when only the
> database transaction log becomes unavailable and when there are active
> transactions in the database?
> Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
> scenario, I have tried that.
> Are there any other options and how much of data loss to expect in each case?
> -- Many thanks, Oskar
>
|||Sorry, but neither of your suggestions work:
sp_add_log_file_recover_suspect_db 'TestDB1', 'TestDB1_Log',
'C:\TEMP\TestDB1_Log.LDF'
returned this error
Server: Msg 5004, Level 16, State 2, Line 1
To use ALTER DATABASE, the database must be in a writable state in which a
checkpoint can be executed.
ALTER DATABASE TestDB1 ADD LOG FILE(NAME = [TestDB1_Log], FILENAME =
'C:\TEMP\TestDB1_Log.LDF' )
Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
incorrect.
Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
incorrect.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
and I can't backup the transaction log because it is not accessible:
backup tran TestDB1 to disk = 'C:\BACKUP\TestDB1_backup' with no_truncate
returns
Server: Msg 3447, Level 16, State 1, Line 1
Could not activate or scan all of the log files for database 'TestDB1'.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
incorrect.
So it seems, that only option I have in this case is to restore from backup.
-- Thanks, Oskar
"Sreejith G" wrote:
[vbcol=seagreen]
> Is the DB currenlty in Suspect state, have you tried...
> sp_add_log_file_recover_suspect_db , Read BOL to get more information
> regarding this.
> For Active transactions on a damaged / corrupted database you can do a
> transacgion log backup using NO_TRUNCATE option. This will backup all the
> active transaction in the database. This command will be used only when your
> database is damaged.
> BACKUP LOG DBNAME TO DISK='C:\db_corr.trn' WITH NO_TRUNCATE
> So once the DB is up..
> Restorethe last TX log backup taken with NO_TRUNCATE. During this time
> mention 'RECOVERY' during RESTORE
> Please do share once you restore the DB or let us know if you have any other
> queries...
>
>
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "Oskar" wrote:
|||In your first thread you said u tried detach/attach/sp_attach_single_file_db,
Below is the syntax and after detach you need not have to execute sp_attach
EXEC sp_detach_db @.dbname = 'pubs'
EXEC sp_attach_single_file_db @.dbname = 'pubs',
@.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
Can you answer the below questions.
Is the DB in SUSPECT state?
Do your DB have multiple data file?
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Oskar" wrote:
[vbcol=seagreen]
> Sorry, but neither of your suggestions work:
> sp_add_log_file_recover_suspect_db 'TestDB1', 'TestDB1_Log',
> 'C:\TEMP\TestDB1_Log.LDF'
> returned this error
> Server: Msg 5004, Level 16, State 2, Line 1
> To use ALTER DATABASE, the database must be in a writable state in which a
> checkpoint can be executed.
> ALTER DATABASE TestDB1 ADD LOG FILE(NAME = [TestDB1_Log], FILENAME =
> 'C:\TEMP\TestDB1_Log.LDF' )
> Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> incorrect.
> Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> incorrect.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> and I can't backup the transaction log because it is not accessible:
> backup tran TestDB1 to disk = 'C:\BACKUP\TestDB1_backup' with no_truncate
> returns
> Server: Msg 3447, Level 16, State 1, Line 1
> Could not activate or scan all of the log files for database 'TestDB1'.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP LOG is terminating abnormally.
> Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> incorrect.
> So it seems, that only option I have in this case is to restore from backup.
> -- Thanks, Oskar
> "Sreejith G" wrote:
|||1) Yes, it is suspect as you would expect it to be with inaccessible tran.
log file.
2) No, single data file.
-- Thanks, Oskar
"Sreejith G" wrote:
[vbcol=seagreen]
> In your first thread you said u tried detach/attach/sp_attach_single_file_db,
> Below is the syntax and after detach you need not have to execute sp_attach
> EXEC sp_detach_db @.dbname = 'pubs'
> EXEC sp_attach_single_file_db @.dbname = 'pubs',
> @.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
> Can you answer the below questions.
> Is the DB in SUSPECT state?
> Do your DB have multiple data file?
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "Oskar" wrote:
On MS SQL Server 2000 SP3, what are my recovery options when only the
database transaction log becomes unavailable and when there are active
transactions in the database?
Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
scenario, I have tried that.
Are there any other options and how much of data loss to expect in each case?
-- Many thanks, Oskar
Is the DB currenlty in Suspect state, have you tried...
sp_add_log_file_recover_suspect_db , Read BOL to get more information
regarding this.
For Active transactions on a damaged / corrupted database you can do a
transacgion log backup using NO_TRUNCATE option. This will backup all the
active transaction in the database. This command will be used only when your
database is damaged.
BACKUP LOG DBNAME TO DISK='C:\db_corr.trn' WITH NO_TRUNCATE
So once the DB is up..
Restorethe last TX log backup taken with NO_TRUNCATE. During this time
mention 'RECOVERY' during RESTORE
Please do share once you restore the DB or let us know if you have any other
queries...
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Oskar" wrote:
> Hi,
> On MS SQL Server 2000 SP3, what are my recovery options when only the
> database transaction log becomes unavailable and when there are active
> transactions in the database?
> Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
> scenario, I have tried that.
> Are there any other options and how much of data loss to expect in each case?
> -- Many thanks, Oskar
>
|||Sorry, but neither of your suggestions work:
sp_add_log_file_recover_suspect_db 'TestDB1', 'TestDB1_Log',
'C:\TEMP\TestDB1_Log.LDF'
returned this error
Server: Msg 5004, Level 16, State 2, Line 1
To use ALTER DATABASE, the database must be in a writable state in which a
checkpoint can be executed.
ALTER DATABASE TestDB1 ADD LOG FILE(NAME = [TestDB1_Log], FILENAME =
'C:\TEMP\TestDB1_Log.LDF' )
Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
incorrect.
Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
incorrect.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
and I can't backup the transaction log because it is not accessible:
backup tran TestDB1 to disk = 'C:\BACKUP\TestDB1_backup' with no_truncate
returns
Server: Msg 3447, Level 16, State 1, Line 1
Could not activate or scan all of the log files for database 'TestDB1'.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
incorrect.
So it seems, that only option I have in this case is to restore from backup.
-- Thanks, Oskar
"Sreejith G" wrote:
[vbcol=seagreen]
> Is the DB currenlty in Suspect state, have you tried...
> sp_add_log_file_recover_suspect_db , Read BOL to get more information
> regarding this.
> For Active transactions on a damaged / corrupted database you can do a
> transacgion log backup using NO_TRUNCATE option. This will backup all the
> active transaction in the database. This command will be used only when your
> database is damaged.
> BACKUP LOG DBNAME TO DISK='C:\db_corr.trn' WITH NO_TRUNCATE
> So once the DB is up..
> Restorethe last TX log backup taken with NO_TRUNCATE. During this time
> mention 'RECOVERY' during RESTORE
> Please do share once you restore the DB or let us know if you have any other
> queries...
>
>
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "Oskar" wrote:
|||In your first thread you said u tried detach/attach/sp_attach_single_file_db,
Below is the syntax and after detach you need not have to execute sp_attach
EXEC sp_detach_db @.dbname = 'pubs'
EXEC sp_attach_single_file_db @.dbname = 'pubs',
@.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
Can you answer the below questions.
Is the DB in SUSPECT state?
Do your DB have multiple data file?
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Oskar" wrote:
[vbcol=seagreen]
> Sorry, but neither of your suggestions work:
> sp_add_log_file_recover_suspect_db 'TestDB1', 'TestDB1_Log',
> 'C:\TEMP\TestDB1_Log.LDF'
> returned this error
> Server: Msg 5004, Level 16, State 2, Line 1
> To use ALTER DATABASE, the database must be in a writable state in which a
> checkpoint can be executed.
> ALTER DATABASE TestDB1 ADD LOG FILE(NAME = [TestDB1_Log], FILENAME =
> 'C:\TEMP\TestDB1_Log.LDF' )
> Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> incorrect.
> Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> incorrect.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> and I can't backup the transaction log because it is not accessible:
> backup tran TestDB1 to disk = 'C:\BACKUP\TestDB1_backup' with no_truncate
> returns
> Server: Msg 3447, Level 16, State 1, Line 1
> Could not activate or scan all of the log files for database 'TestDB1'.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP LOG is terminating abnormally.
> Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
> incorrect.
> So it seems, that only option I have in this case is to restore from backup.
> -- Thanks, Oskar
> "Sreejith G" wrote:
|||1) Yes, it is suspect as you would expect it to be with inaccessible tran.
log file.
2) No, single data file.
-- Thanks, Oskar
"Sreejith G" wrote:
[vbcol=seagreen]
> In your first thread you said u tried detach/attach/sp_attach_single_file_db,
> Below is the syntax and after detach you need not have to execute sp_attach
> EXEC sp_detach_db @.dbname = 'pubs'
> EXEC sp_attach_single_file_db @.dbname = 'pubs',
> @.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
> Can you answer the below questions.
> Is the DB in SUSPECT state?
> Do your DB have multiple data file?
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "Oskar" wrote:
Recovery options when the transaction log is unavailable
Hi,
On MS SQL Server 2000 SP3, what are my recovery options when only the
database transaction log becomes unavailable and when there are active
transactions in the database?
Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
scenario, I have tried that.
Are there any other options and how much of data loss to expect in each case
?
-- Many thanks, OskarIs the DB currenlty in Suspect state, have you tried...
sp_add_log_file_recover_suspect_db , Read BOL to get more information
regarding this.
For Active transactions on a damaged / corrupted database you can do a
transacgion log backup using NO_TRUNCATE option. This will backup all the
active transaction in the database. This command will be used only when your
database is damaged.
BACKUP LOG DBNAME TO DISK='C:\db_corr.trn' WITH NO_TRUNCATE
So once the DB is up..
Restorethe last TX log backup taken with NO_TRUNCATE. During this time
mention 'RECOVERY' during RESTORE
Please do share once you restore the DB or let us know if you have any other
queries...
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Oskar" wrote:
> Hi,
> On MS SQL Server 2000 SP3, what are my recovery options when only the
> database transaction log becomes unavailable and when there are active
> transactions in the database?
> Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
> scenario, I have tried that.
> Are there any other options and how much of data loss to expect in each ca
se?
> -- Many thanks, Oskar
>|||Sorry, but neither of your suggestions work:
sp_add_log_file_recover_suspect_db 'TestDB1', 'TestDB1_Log',
'C:\TEMP\TestDB1_Log.LDF'
returned this error
Server: Msg 5004, Level 16, State 2, Line 1
To use ALTER DATABASE, the database must be in a writable state in which a
checkpoint can be executed.
ALTER DATABASE TestDB1 ADD LOG FILE(NAME = [TestDB1_Log], FILENAME =
'C:\TEMP\TestDB1_Log.LDF' )
Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
incorrect.
Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
incorrect.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
and I can't backup the transaction log because it is not accessible:
backup tran TestDB1 to disk = 'C:\BACKUP\TestDB1_backup' with no_truncate
returns
Server: Msg 3447, Level 16, State 1, Line 1
Could not activate or scan all of the log files for database 'TestDB1'.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
incorrect.
So it seems, that only option I have in this case is to restore from backup.
-- Thanks, Oskar
"Sreejith G" wrote:
[vbcol=seagreen]
> Is the DB currenlty in Suspect state, have you tried...
> sp_add_log_file_recover_suspect_db , Read BOL to get more information
> regarding this.
> For Active transactions on a damaged / corrupted database you can do a
> transacgion log backup using NO_TRUNCATE option. This will backup all the
> active transaction in the database. This command will be used only when yo
ur
> database is damaged.
> BACKUP LOG DBNAME TO DISK='C:\db_corr.trn' WITH NO_TRUNCATE
> So once the DB is up..
> Restorethe last TX log backup taken with NO_TRUNCATE. During this time
> mention 'RECOVERY' during RESTORE
> Please do share once you restore the DB or let us know if you have any oth
er
> queries...
>
>
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread an
d time
> asking back if its 2000 or 2005]
>
> "Oskar" wrote:
>|||In your first thread you said u tried detach/attach/sp_attach_single_file_db
,
Below is the syntax and after detach you need not have to execute sp_attach
EXEC sp_detach_db @.dbname = 'pubs'
EXEC sp_attach_single_file_db @.dbname = 'pubs',
@.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
Can you answer the below questions.
Is the DB in SUSPECT state?
Do your DB have multiple data file?
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Oskar" wrote:
[vbcol=seagreen]
> Sorry, but neither of your suggestions work:
> sp_add_log_file_recover_suspect_db 'TestDB1', 'TestDB1_Log',
> 'C:\TEMP\TestDB1_Log.LDF'
> returned this error
> Server: Msg 5004, Level 16, State 2, Line 1
> To use ALTER DATABASE, the database must be in a writable state in which a
> checkpoint can be executed.
> ALTER DATABASE TestDB1 ADD LOG FILE(NAME = [TestDB1_Log], FILENAME =
> 'C:\TEMP\TestDB1_Log.LDF' )
> Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may b
e
> incorrect.
> Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may b
e
> incorrect.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> and I can't backup the transaction log because it is not accessible:
> backup tran TestDB1 to disk = 'C:\BACKUP\TestDB1_backup' with no_truncate
> returns
> Server: Msg 3447, Level 16, State 1, Line 1
> Could not activate or scan all of the log files for database 'TestDB1'.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP LOG is terminating abnormally.
> Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may b
e
> incorrect.
> So it seems, that only option I have in this case is to restore from backu
p.
> -- Thanks, Oskar
> "Sreejith G" wrote:
>|||1) Yes, it is suspect as you would expect it to be with inaccessible tran.
log file.
2) No, single data file.
-- Thanks, Oskar
"Sreejith G" wrote:
[vbcol=seagreen]
> In your first thread you said u tried detach/attach/sp_attach_single_file_
db,
> Below is the syntax and after detach you need not have to execute sp_attac
h
> EXEC sp_detach_db @.dbname = 'pubs'
> EXEC sp_attach_single_file_db @.dbname = 'pubs',
> @.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
> Can you answer the below questions.
> Is the DB in SUSPECT state?
> Do your DB have multiple data file?
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread an
d time
> asking back if its 2000 or 2005]
>
> "Oskar" wrote:
>
On MS SQL Server 2000 SP3, what are my recovery options when only the
database transaction log becomes unavailable and when there are active
transactions in the database?
Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
scenario, I have tried that.
Are there any other options and how much of data loss to expect in each case
?
-- Many thanks, OskarIs the DB currenlty in Suspect state, have you tried...
sp_add_log_file_recover_suspect_db , Read BOL to get more information
regarding this.
For Active transactions on a damaged / corrupted database you can do a
transacgion log backup using NO_TRUNCATE option. This will backup all the
active transaction in the database. This command will be used only when your
database is damaged.
BACKUP LOG DBNAME TO DISK='C:\db_corr.trn' WITH NO_TRUNCATE
So once the DB is up..
Restorethe last TX log backup taken with NO_TRUNCATE. During this time
mention 'RECOVERY' during RESTORE
Please do share once you restore the DB or let us know if you have any other
queries...
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Oskar" wrote:
> Hi,
> On MS SQL Server 2000 SP3, what are my recovery options when only the
> database transaction log becomes unavailable and when there are active
> transactions in the database?
> Sp_detach_db/sp_attach_db/sp_attach_single_file_db doesn't work in this
> scenario, I have tried that.
> Are there any other options and how much of data loss to expect in each ca
se?
> -- Many thanks, Oskar
>|||Sorry, but neither of your suggestions work:
sp_add_log_file_recover_suspect_db 'TestDB1', 'TestDB1_Log',
'C:\TEMP\TestDB1_Log.LDF'
returned this error
Server: Msg 5004, Level 16, State 2, Line 1
To use ALTER DATABASE, the database must be in a writable state in which a
checkpoint can be executed.
ALTER DATABASE TestDB1 ADD LOG FILE(NAME = [TestDB1_Log], FILENAME =
'C:\TEMP\TestDB1_Log.LDF' )
Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
incorrect.
Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
incorrect.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
and I can't backup the transaction log because it is not accessible:
backup tran TestDB1 to disk = 'C:\BACKUP\TestDB1_backup' with no_truncate
returns
Server: Msg 3447, Level 16, State 1, Line 1
Could not activate or scan all of the log files for database 'TestDB1'.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may be
incorrect.
So it seems, that only option I have in this case is to restore from backup.
-- Thanks, Oskar
"Sreejith G" wrote:
[vbcol=seagreen]
> Is the DB currenlty in Suspect state, have you tried...
> sp_add_log_file_recover_suspect_db , Read BOL to get more information
> regarding this.
> For Active transactions on a damaged / corrupted database you can do a
> transacgion log backup using NO_TRUNCATE option. This will backup all the
> active transaction in the database. This command will be used only when yo
ur
> database is damaged.
> BACKUP LOG DBNAME TO DISK='C:\db_corr.trn' WITH NO_TRUNCATE
> So once the DB is up..
> Restorethe last TX log backup taken with NO_TRUNCATE. During this time
> mention 'RECOVERY' during RESTORE
> Please do share once you restore the DB or let us know if you have any oth
er
> queries...
>
>
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread an
d time
> asking back if its 2000 or 2005]
>
> "Oskar" wrote:
>|||In your first thread you said u tried detach/attach/sp_attach_single_file_db
,
Below is the syntax and after detach you need not have to execute sp_attach
EXEC sp_detach_db @.dbname = 'pubs'
EXEC sp_attach_single_file_db @.dbname = 'pubs',
@.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
Can you answer the below questions.
Is the DB in SUSPECT state?
Do your DB have multiple data file?
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Oskar" wrote:
[vbcol=seagreen]
> Sorry, but neither of your suggestions work:
> sp_add_log_file_recover_suspect_db 'TestDB1', 'TestDB1_Log',
> 'C:\TEMP\TestDB1_Log.LDF'
> returned this error
> Server: Msg 5004, Level 16, State 2, Line 1
> To use ALTER DATABASE, the database must be in a writable state in which a
> checkpoint can be executed.
> ALTER DATABASE TestDB1 ADD LOG FILE(NAME = [TestDB1_Log], FILENAME =
> 'C:\TEMP\TestDB1_Log.LDF' )
> Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may b
e
> incorrect.
> Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may b
e
> incorrect.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> and I can't backup the transaction log because it is not accessible:
> backup tran TestDB1 to disk = 'C:\BACKUP\TestDB1_backup' with no_truncate
> returns
> Server: Msg 3447, Level 16, State 1, Line 1
> Could not activate or scan all of the log files for database 'TestDB1'.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP LOG is terminating abnormally.
> Device activation error. The physical file name 'E:\TestDB1_Log.LDF' may b
e
> incorrect.
> So it seems, that only option I have in this case is to restore from backu
p.
> -- Thanks, Oskar
> "Sreejith G" wrote:
>|||1) Yes, it is suspect as you would expect it to be with inaccessible tran.
log file.
2) No, single data file.
-- Thanks, Oskar
"Sreejith G" wrote:
[vbcol=seagreen]
> In your first thread you said u tried detach/attach/sp_attach_single_file_
db,
> Below is the syntax and after detach you need not have to execute sp_attac
h
> EXEC sp_detach_db @.dbname = 'pubs'
> EXEC sp_attach_single_file_db @.dbname = 'pubs',
> @.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
> Can you answer the below questions.
> Is the DB in SUSPECT state?
> Do your DB have multiple data file?
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread an
d time
> asking back if its 2000 or 2005]
>
> "Oskar" wrote:
>
Recovery Models
In an attempt to manage the size of the transaction log , I would like to use
the
Simple verses the Full Recovery model. Other than the loss of transaction log
backup and recovery only to the last full or differential backup, are
there any
other “landmines” I should consider? Thanks for being there to help. Regards
Ed.
No landmines, but remember the tlog will still grow to accept the largest
single transaction you ever do, so the log might still get quite large, and
it does not shrink automatically.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
> use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
> log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
> Regards
> Ed.
>
|||I thing that features that come with transaction log are many time worthier
than the time you lose learning how to deal with transaction log. So put
some effort on learning and you will have great benefit.
Regards,
Daniel
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
Regards
> Ed.
>
|||Dealing with the size of the transaction log is about as difficult as
dealing with any of the other maitenance plans. You are doing the other
maitenance options aren't you? Because if you are, the transaction log will
grow to the size of the single largest transaction, which is typically the
Database Reorganization plan, REINDEX.
Now, also consider what the transaction log really is. It is akin to the
general ledger of an accounting system. It records every action take
against a database the takes the database image from one snapshot to
another. The FULL and DIFFERENTIALS are only the snapshots. The
transaction logs are the actual work modifications that get you between the
two.
Sincerely,
Anthony Thomas
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
In an attempt to manage the size of the transaction log , I would like to
use
the
Simple verses the Full Recovery model. Other than the loss of transaction
log
backup and recovery only to the last full or differential backup, are
there any
other "landmines" I should consider? Thanks for being there to help.
Regards
Ed.
the
Simple verses the Full Recovery model. Other than the loss of transaction log
backup and recovery only to the last full or differential backup, are
there any
other “landmines” I should consider? Thanks for being there to help. Regards
Ed.
No landmines, but remember the tlog will still grow to accept the largest
single transaction you ever do, so the log might still get quite large, and
it does not shrink automatically.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
> use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
> log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
> Regards
> Ed.
>
|||I thing that features that come with transaction log are many time worthier
than the time you lose learning how to deal with transaction log. So put
some effort on learning and you will have great benefit.
Regards,
Daniel
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
Regards
> Ed.
>
|||Dealing with the size of the transaction log is about as difficult as
dealing with any of the other maitenance plans. You are doing the other
maitenance options aren't you? Because if you are, the transaction log will
grow to the size of the single largest transaction, which is typically the
Database Reorganization plan, REINDEX.
Now, also consider what the transaction log really is. It is akin to the
general ledger of an accounting system. It records every action take
against a database the takes the database image from one snapshot to
another. The FULL and DIFFERENTIALS are only the snapshots. The
transaction logs are the actual work modifications that get you between the
two.
Sincerely,
Anthony Thomas
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
In an attempt to manage the size of the transaction log , I would like to
use
the
Simple verses the Full Recovery model. Other than the loss of transaction
log
backup and recovery only to the last full or differential backup, are
there any
other "landmines" I should consider? Thanks for being there to help.
Regards
Ed.
Recovery Models
In an attempt to manage the size of the transaction log , I would like to us
e
the
Simple verses the Full Recovery model. Other than the loss of transaction lo
g
backup and recovery only to the last full or differential backup, are
there any
other “landmines” I should consider? Thanks for being there to help. Reg
ards
Ed.No landmines, but remember the tlog will still grow to accept the largest
single transaction you ever do, so the log might still get quite large, and
it does not shrink automatically.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
> use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
> log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
> Regards
> Ed.
>|||I thing that features that come with transaction log are many time worthier
than the time you lose learning how to deal with transaction log. So put
some effort on learning and you will have great benefit.
Regards,
Daniel
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
Regards
> Ed.
>|||Dealing with the size of the transaction log is about as difficult as
dealing with any of the other maitenance plans. You are doing the other
maitenance options aren't you? Because if you are, the transaction log will
grow to the size of the single largest transaction, which is typically the
Database Reorganization plan, REINDEX.
Now, also consider what the transaction log really is. It is akin to the
general ledger of an accounting system. It records every action take
against a database the takes the database image from one snapshot to
another. The FULL and DIFFERENTIALS are only the snapshots. The
transaction logs are the actual work modifications that get you between the
two.
Sincerely,
Anthony Thomas
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
In an attempt to manage the size of the transaction log , I would like to
use
the
Simple verses the Full Recovery model. Other than the loss of transaction
log
backup and recovery only to the last full or differential backup, are
there any
other "landmines" I should consider? Thanks for being there to help.
Regards
Ed.
e
the
Simple verses the Full Recovery model. Other than the loss of transaction lo
g
backup and recovery only to the last full or differential backup, are
there any
other “landmines” I should consider? Thanks for being there to help. Reg
ards
Ed.No landmines, but remember the tlog will still grow to accept the largest
single transaction you ever do, so the log might still get quite large, and
it does not shrink automatically.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
> use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
> log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
> Regards
> Ed.
>|||I thing that features that come with transaction log are many time worthier
than the time you lose learning how to deal with transaction log. So put
some effort on learning and you will have great benefit.
Regards,
Daniel
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
Regards
> Ed.
>|||Dealing with the size of the transaction log is about as difficult as
dealing with any of the other maitenance plans. You are doing the other
maitenance options aren't you? Because if you are, the transaction log will
grow to the size of the single largest transaction, which is typically the
Database Reorganization plan, REINDEX.
Now, also consider what the transaction log really is. It is akin to the
general ledger of an accounting system. It records every action take
against a database the takes the database image from one snapshot to
another. The FULL and DIFFERENTIALS are only the snapshots. The
transaction logs are the actual work modifications that get you between the
two.
Sincerely,
Anthony Thomas
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
In an attempt to manage the size of the transaction log , I would like to
use
the
Simple verses the Full Recovery model. Other than the loss of transaction
log
backup and recovery only to the last full or differential backup, are
there any
other "landmines" I should consider? Thanks for being there to help.
Regards
Ed.
Recovery Models
In an attempt to manage the size of the transaction log , I would like to use
the
Simple verses the Full Recovery model. Other than the loss of transaction log
backup and recovery only to the last full or differential backup, are
there any
other â'landminesâ' I should consider? Thanks for being there to help. Regards
Ed.No landmines, but remember the tlog will still grow to accept the largest
single transaction you ever do, so the log might still get quite large, and
it does not shrink automatically.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
> use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
> log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
> Regards
> Ed.
>|||I thing that features that come with transaction log are many time worthier
than the time you lose learning how to deal with transaction log. So put
some effort on learning and you will have great benefit.
Regards,
Daniel
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
Regards
> Ed.
>|||Dealing with the size of the transaction log is about as difficult as
dealing with any of the other maitenance plans. You are doing the other
maitenance options aren't you? Because if you are, the transaction log will
grow to the size of the single largest transaction, which is typically the
Database Reorganization plan, REINDEX.
Now, also consider what the transaction log really is. It is akin to the
general ledger of an accounting system. It records every action take
against a database the takes the database image from one snapshot to
another. The FULL and DIFFERENTIALS are only the snapshots. The
transaction logs are the actual work modifications that get you between the
two.
Sincerely,
Anthony Thomas
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
In an attempt to manage the size of the transaction log , I would like to
use
the
Simple verses the Full Recovery model. Other than the loss of transaction
log
backup and recovery only to the last full or differential backup, are
there any
other "landmines" I should consider? Thanks for being there to help.
Regards
Ed.
the
Simple verses the Full Recovery model. Other than the loss of transaction log
backup and recovery only to the last full or differential backup, are
there any
other â'landminesâ' I should consider? Thanks for being there to help. Regards
Ed.No landmines, but remember the tlog will still grow to accept the largest
single transaction you ever do, so the log might still get quite large, and
it does not shrink automatically.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
> use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
> log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
> Regards
> Ed.
>|||I thing that features that come with transaction log are many time worthier
than the time you lose learning how to deal with transaction log. So put
some effort on learning and you will have great benefit.
Regards,
Daniel
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
Regards
> Ed.
>|||Dealing with the size of the transaction log is about as difficult as
dealing with any of the other maitenance plans. You are doing the other
maitenance options aren't you? Because if you are, the transaction log will
grow to the size of the single largest transaction, which is typically the
Database Reorganization plan, REINDEX.
Now, also consider what the transaction log really is. It is akin to the
general ledger of an accounting system. It records every action take
against a database the takes the database image from one snapshot to
another. The FULL and DIFFERENTIALS are only the snapshots. The
transaction logs are the actual work modifications that get you between the
two.
Sincerely,
Anthony Thomas
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
In an attempt to manage the size of the transaction log , I would like to
use
the
Simple verses the Full Recovery model. Other than the loss of transaction
log
backup and recovery only to the last full or differential backup, are
there any
other "landmines" I should consider? Thanks for being there to help.
Regards
Ed.
Recovery Model reverts to Simple
We are using Sql Server 2000 and for some reason the recovery model
automatically changes from FULL to simple when we get a notification that are
log file backup job fails. Need suggestions are welcomed...Is that the msdb database?
"Mike Moore" wrote:
> We are using Sql Server 2000 and for some reason the recovery model
> automatically changes from FULL to simple when we get a notification that are
> log file backup job fails. Need suggestions are welcomed...|||Yes...you are correct.
"Sasan Saidi" wrote:
> Is that the msdb database?
> "Mike Moore" wrote:
> > We are using Sql Server 2000 and for some reason the recovery model
> > automatically changes from FULL to simple when we get a notification that are
> > log file backup job fails. All suggestions are welcomed...|||Even though you change the recovery model of the msdb database to 'Full', it
will automatically be reset to 'Simple' the next time that SQL Server Agent
is restarted. This is by design to prevent the msdb transaction from growing
to an unmanageable size. You will need to perform only full database backups
on the msdb database.
--
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Mike Moore" wrote:
> Yes...you are correct.
> "Sasan Saidi" wrote:
> > Is that the msdb database?
> >
> > "Mike Moore" wrote:
> >
> > > We are using Sql Server 2000 and for some reason the recovery model
> > > automatically changes from FULL to simple when we get a notification that are
> > > log file backup job fails. All suggestions are welcomed...|||We are currently doing full database backups on the msdb database.
"Sasan Saidi" wrote:
> Even though you change the recovery model of the msdb database to 'Full', it
> will automatically be reset to 'Simple' the next time that SQL Server Agent
> is restarted. This is by design to prevent the msdb transaction from growing
> to an unmanageable size. You will need to perform only full database backups
> on the msdb database.
> --
> "I saw it work in a cartoon once so I am pretty sure I can do it."
> "Mike Moore" wrote:
> > Yes...you are correct.
> >
> > "Sasan Saidi" wrote:
> >
> > > Is that the msdb database?
> > >
> > > "Mike Moore" wrote:
> > >
> > > > We are using Sql Server 2000 and for some reason the recovery model
> > > > automatically changes from FULL to simple when we get a notification that are
> > > > log file backup job fails. All suggestions are welcomed...
automatically changes from FULL to simple when we get a notification that are
log file backup job fails. Need suggestions are welcomed...Is that the msdb database?
"Mike Moore" wrote:
> We are using Sql Server 2000 and for some reason the recovery model
> automatically changes from FULL to simple when we get a notification that are
> log file backup job fails. Need suggestions are welcomed...|||Yes...you are correct.
"Sasan Saidi" wrote:
> Is that the msdb database?
> "Mike Moore" wrote:
> > We are using Sql Server 2000 and for some reason the recovery model
> > automatically changes from FULL to simple when we get a notification that are
> > log file backup job fails. All suggestions are welcomed...|||Even though you change the recovery model of the msdb database to 'Full', it
will automatically be reset to 'Simple' the next time that SQL Server Agent
is restarted. This is by design to prevent the msdb transaction from growing
to an unmanageable size. You will need to perform only full database backups
on the msdb database.
--
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Mike Moore" wrote:
> Yes...you are correct.
> "Sasan Saidi" wrote:
> > Is that the msdb database?
> >
> > "Mike Moore" wrote:
> >
> > > We are using Sql Server 2000 and for some reason the recovery model
> > > automatically changes from FULL to simple when we get a notification that are
> > > log file backup job fails. All suggestions are welcomed...|||We are currently doing full database backups on the msdb database.
"Sasan Saidi" wrote:
> Even though you change the recovery model of the msdb database to 'Full', it
> will automatically be reset to 'Simple' the next time that SQL Server Agent
> is restarted. This is by design to prevent the msdb transaction from growing
> to an unmanageable size. You will need to perform only full database backups
> on the msdb database.
> --
> "I saw it work in a cartoon once so I am pretty sure I can do it."
> "Mike Moore" wrote:
> > Yes...you are correct.
> >
> > "Sasan Saidi" wrote:
> >
> > > Is that the msdb database?
> > >
> > > "Mike Moore" wrote:
> > >
> > > > We are using Sql Server 2000 and for some reason the recovery model
> > > > automatically changes from FULL to simple when we get a notification that are
> > > > log file backup job fails. All suggestions are welcomed...
Wednesday, March 28, 2012
Recovery Model of Subscription Database
Hi:
I am having lot of log problems with Subscription databases. Currently all my subscription databases are on Full recovery mode. I am thinking to change them to simple because I don't I will be doing point in time recovery of them.
Do the subcription databases have to be on Full mode? Can I change them to simple to keep my log small and then I do not have to backups of my logs also? Please let me know.
Thanks
No they don't, simple will be fine, but what's the problem you are having?|||Thanks for replying. Log of the subscription database is about 96% full. I tired to truncate it but still size stays around the same. Even though I changed the recovery model to simple, it should truncate it at every checkpoint. The size is staying about the same.
There are some active transactions sitting in the log. By truncating it multiple times, it should remove (purge) them. Actually log agent was stopped manually. Probably these active transactions may be partial and do not how to commit. Any ideas will be helpful.
Thanks
|||Mike Husar wrote:
There are some active transactions sitting in the log. By truncating it multiple times, it should remove (purge) them. Actually log agent was stopped manually. Probably these active transactions may be partial and do not how to commit. Any ideas will be helpful.
You mention logreader agent, which should only affect published database. Are you talking about published or subscriber databases? If published database, only transactions that are marked as replicated (txns that the log reader agent has already replicated) can be truncated. Non-replicated txns will not be truncated.
|||Actually it is not the logreader agent. It is the log of subcription database that I was not able to truncate.
|||Then you need to find out what's blocking. Usually this is due to active backup or restore, an active transaction, or some kind of diagnostic log scan.|||You can also try restarting the server to clear those active/open transactions, but then the uncommitted transactions will get rolled back.sqlrecovery model change log
Hi
Is there any log of changes in recovery model?
If not directly, is there any way to know whether a
recovery model has changed in the past?
Thanks,
RonenMaybe profiler security audit event (Audit object derived event or Audit
object event) might show this...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ronen" <anonymous@.discussions.microsoft.com> wrote in message
news:013d01c3d4e9$a259f9e0$a001280a@.phx.gbl...
> Hi
> Is there any log of changes in recovery model?
> If not directly, is there any way to know whether a
> recovery model has changed in the past?
> Thanks,
> Ronen
Is there any log of changes in recovery model?
If not directly, is there any way to know whether a
recovery model has changed in the past?
Thanks,
RonenMaybe profiler security audit event (Audit object derived event or Audit
object event) might show this...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ronen" <anonymous@.discussions.microsoft.com> wrote in message
news:013d01c3d4e9$a259f9e0$a001280a@.phx.gbl...
> Hi
> Is there any log of changes in recovery model?
> If not directly, is there any way to know whether a
> recovery model has changed in the past?
> Thanks,
> Ronen
Recovery Model backup
I'm currently performing a database backup at noon and 8pm
every evening, along with a transacation log backup at
10am and 2pm. My Recoery Model is set to "Full" and have
realized that the transaction log has every transaction
ever done against the database, getting pretty large.
Considering the backups that I'm doing, is it better to
set the Recovery Model to "Simple" and set the "autogrow"
and "autoshrink" on. I'm not sure what my target size
should be if I perform a dbcc shrinkfile operation, so
autoshrink seems the right thing to do? I need a little
advice on this on.
Thank you,
RandyRandy,
> Considering the backups that I'm doing, is it better to
> set the Recovery Model to "Simple" and set the "autogrow"
> and "autoshrink" on.
Simple recovery model means you cannot take transaction log backups.Also,
the transaction log file would get 'truncated' (emptied not shrinked) after
a log backup.In cases where you have only the database backups, Well, the
database backups just reflect the database when the backup was done.What
about the transactions that were for the interval between 8pm and next noon?
For complete data recovery to the point in time, you need transaction log
backups.
Autogrow should be always on even when you have given considerable space
beforehand itself to the database files.Autoshrink should be OFF for a
production system as this may consume valuable resources during peak time.
>>so
> autoshrink seems the right thing to do?
No for reasons said above.Why do you want to shrink the transaction log
files when its anyways going to grow for the OLTP traffic?Monitor the .ldf
growth for a period of time and in case you find that the transaction log
never crosses x mb/gb, then go ahead and do a DBCC SHRINKFILE .You may find
the below articles useful...
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/default.aspx?scid=kb;EN-US;110139
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/support/kb/Articles/q272/3/18.asp
INF: Considerations for Autogrow and Autoshrink Configuration
http://www.support.microsoft.com/?id=315512
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Rand Monroe" <randmo@.ix.netcom.com> wrote in message
news:00a001c35790$c3fed1f0$a601280a@.phx.gbl...
> I'm currently performing a database backup at noon and 8pm
> every evening, along with a transacation log backup at
> 10am and 2pm. My Recoery Model is set to "Full" and have
> realized that the transaction log has every transaction
> ever done against the database, getting pretty large.
> Considering the backups that I'm doing, is it better to
> set the Recovery Model to "Simple" and set the "autogrow"
> and "autoshrink" on. I'm not sure what my target size
> should be if I perform a dbcc shrinkfile operation, so
> autoshrink seems the right thing to do? I need a little
> advice on this on.
> Thank you,
> Randy
every evening, along with a transacation log backup at
10am and 2pm. My Recoery Model is set to "Full" and have
realized that the transaction log has every transaction
ever done against the database, getting pretty large.
Considering the backups that I'm doing, is it better to
set the Recovery Model to "Simple" and set the "autogrow"
and "autoshrink" on. I'm not sure what my target size
should be if I perform a dbcc shrinkfile operation, so
autoshrink seems the right thing to do? I need a little
advice on this on.
Thank you,
RandyRandy,
> Considering the backups that I'm doing, is it better to
> set the Recovery Model to "Simple" and set the "autogrow"
> and "autoshrink" on.
Simple recovery model means you cannot take transaction log backups.Also,
the transaction log file would get 'truncated' (emptied not shrinked) after
a log backup.In cases where you have only the database backups, Well, the
database backups just reflect the database when the backup was done.What
about the transactions that were for the interval between 8pm and next noon?
For complete data recovery to the point in time, you need transaction log
backups.
Autogrow should be always on even when you have given considerable space
beforehand itself to the database files.Autoshrink should be OFF for a
production system as this may consume valuable resources during peak time.
>>so
> autoshrink seems the right thing to do?
No for reasons said above.Why do you want to shrink the transaction log
files when its anyways going to grow for the OLTP traffic?Monitor the .ldf
growth for a period of time and in case you find that the transaction log
never crosses x mb/gb, then go ahead and do a DBCC SHRINKFILE .You may find
the below articles useful...
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/default.aspx?scid=kb;EN-US;110139
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/support/kb/Articles/q272/3/18.asp
INF: Considerations for Autogrow and Autoshrink Configuration
http://www.support.microsoft.com/?id=315512
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Rand Monroe" <randmo@.ix.netcom.com> wrote in message
news:00a001c35790$c3fed1f0$a601280a@.phx.gbl...
> I'm currently performing a database backup at noon and 8pm
> every evening, along with a transacation log backup at
> 10am and 2pm. My Recoery Model is set to "Full" and have
> realized that the transaction log has every transaction
> ever done against the database, getting pretty large.
> Considering the backups that I'm doing, is it better to
> set the Recovery Model to "Simple" and set the "autogrow"
> and "autoshrink" on. I'm not sure what my target size
> should be if I perform a dbcc shrinkfile operation, so
> autoshrink seems the right thing to do? I need a little
> advice on this on.
> Thank you,
> Randy
recovery model and dbcc dbreindex
Hello,
I have a log file that grows significantly during DBCC DBREINDEX. Does
anyone know of a good reason why I shouldn't switch the recovery model
from FULL to BULK_LOGGED, perform DBCC DBREINDEX and then switch back to
FULL again?
Thanks,
Craig.
"...how we thwart the natural love of learning by leaving the natural
method of teaching what each wishes to learn, and insisting that you
shall learn what you have no taste or capacity for."
Just watch the size of the following tlog backup (read about details for bulk logged in Books
Online). Also, during time period while in bulk mode and if during that time you perform minimally
logged operations, forthcoming log backup will not have point in time restore (STOPAT). Also, if db
files crash during this same period, not backing up tlog using NO_TRUNCATE option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Craig H." <spam@.thehurley.com> wrote in message news:ejUEgayLFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a log file that grows significantly during DBCC DBREINDEX. Does anyone know of a good
> reason why I shouldn't switch the recovery model from FULL to BULK_LOGGED, perform DBCC DBREINDEX
> and then switch back to FULL again?
> Thanks,
> Craig.
>
> --
> "...how we thwart the natural love of learning by leaving the natural method of teaching what each
> wishes to learn, and insisting that you shall learn what you have no taste or capacity for."
|||On 2005-03-22 13:41, Tibor Karaszi wrote:
> Just watch the size of the following tlog backup (read about details
> for bulk logged in Books Online). Also, during time period while in
> bulk mode and if during that time you perform minimally logged
> operations, forthcoming log backup will not have point in time
> restore (STOPAT). Also, if db files crash during this same period,
> not backing up tlog using NO_TRUNCATE option.
>
So the rate at which the log file grows during DBCC DBREINDEX will
decrease and regular inserts will still be logged while the db is using
the BULK_LOGGED model... this will do fine.
Craig.
"The power of accurate observation is frequently called cynicism by
those who don't have it."
I have a log file that grows significantly during DBCC DBREINDEX. Does
anyone know of a good reason why I shouldn't switch the recovery model
from FULL to BULK_LOGGED, perform DBCC DBREINDEX and then switch back to
FULL again?
Thanks,
Craig.
"...how we thwart the natural love of learning by leaving the natural
method of teaching what each wishes to learn, and insisting that you
shall learn what you have no taste or capacity for."
Just watch the size of the following tlog backup (read about details for bulk logged in Books
Online). Also, during time period while in bulk mode and if during that time you perform minimally
logged operations, forthcoming log backup will not have point in time restore (STOPAT). Also, if db
files crash during this same period, not backing up tlog using NO_TRUNCATE option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Craig H." <spam@.thehurley.com> wrote in message news:ejUEgayLFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a log file that grows significantly during DBCC DBREINDEX. Does anyone know of a good
> reason why I shouldn't switch the recovery model from FULL to BULK_LOGGED, perform DBCC DBREINDEX
> and then switch back to FULL again?
> Thanks,
> Craig.
>
> --
> "...how we thwart the natural love of learning by leaving the natural method of teaching what each
> wishes to learn, and insisting that you shall learn what you have no taste or capacity for."
|||On 2005-03-22 13:41, Tibor Karaszi wrote:
> Just watch the size of the following tlog backup (read about details
> for bulk logged in Books Online). Also, during time period while in
> bulk mode and if during that time you perform minimally logged
> operations, forthcoming log backup will not have point in time
> restore (STOPAT). Also, if db files crash during this same period,
> not backing up tlog using NO_TRUNCATE option.
>
So the rate at which the log file grows during DBCC DBREINDEX will
decrease and regular inserts will still be logged while the db is using
the BULK_LOGGED model... this will do fine.
Craig.
"The power of accurate observation is frequently called cynicism by
those who don't have it."
recovery model and dbcc dbreindex
Hello,
I have a log file that grows significantly during DBCC DBREINDEX. Does
anyone know of a good reason why I shouldn't switch the recovery model
from FULL to BULK_LOGGED, perform DBCC DBREINDEX and then switch back to
FULL again?
Thanks,
Craig.
"...how we thwart the natural love of learning by leaving the natural
method of teaching what each wishes to learn, and insisting that you
shall learn what you have no taste or capacity for."Just watch the size of the following tlog backup (read about details for bul
k logged in Books
Online). Also, during time period while in bulk mode and if during that time
you perform minimally
logged operations, forthcoming log backup will not have point in time restor
e (STOPAT). Also, if db
files crash during this same period, not backing up tlog using NO_TRUNCATE o
ption.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Craig H." <spam@.thehurley.com> wrote in message news:ejUEgayLFHA.3296@.TK2MSFTNGP15.phx.gbl.
.
> Hello,
> I have a log file that grows significantly during DBCC DBREINDEX. Does an
yone know of a good
> reason why I shouldn't switch the recovery model from FULL to BULK_LOGGED,
perform DBCC DBREINDEX
> and then switch back to FULL again?
> Thanks,
> Craig.
>
> --
> "...how we thwart the natural love of learning by leaving the natural meth
od of teaching what each
> wishes to learn, and insisting that you shall learn what you have no taste or capa
city for."|||On 2005-03-22 13:41, Tibor Karaszi wrote:
> Just watch the size of the following tlog backup (read about details
> for bulk logged in Books Online). Also, during time period while in
> bulk mode and if during that time you perform minimally logged
> operations, forthcoming log backup will not have point in time
> restore (STOPAT). Also, if db files crash during this same period,
> not backing up tlog using NO_TRUNCATE option.
>
So the rate at which the log file grows during DBCC DBREINDEX will
decrease and regular inserts will still be logged while the db is using
the BULK_LOGGED model... this will do fine.
Craig.
"The power of accurate observation is frequently called cynicism by
those who don't have it."
I have a log file that grows significantly during DBCC DBREINDEX. Does
anyone know of a good reason why I shouldn't switch the recovery model
from FULL to BULK_LOGGED, perform DBCC DBREINDEX and then switch back to
FULL again?
Thanks,
Craig.
"...how we thwart the natural love of learning by leaving the natural
method of teaching what each wishes to learn, and insisting that you
shall learn what you have no taste or capacity for."Just watch the size of the following tlog backup (read about details for bul
k logged in Books
Online). Also, during time period while in bulk mode and if during that time
you perform minimally
logged operations, forthcoming log backup will not have point in time restor
e (STOPAT). Also, if db
files crash during this same period, not backing up tlog using NO_TRUNCATE o
ption.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Craig H." <spam@.thehurley.com> wrote in message news:ejUEgayLFHA.3296@.TK2MSFTNGP15.phx.gbl.
.
> Hello,
> I have a log file that grows significantly during DBCC DBREINDEX. Does an
yone know of a good
> reason why I shouldn't switch the recovery model from FULL to BULK_LOGGED,
perform DBCC DBREINDEX
> and then switch back to FULL again?
> Thanks,
> Craig.
>
> --
> "...how we thwart the natural love of learning by leaving the natural meth
od of teaching what each
> wishes to learn, and insisting that you shall learn what you have no taste or capa
city for."|||On 2005-03-22 13:41, Tibor Karaszi wrote:
> Just watch the size of the following tlog backup (read about details
> for bulk logged in Books Online). Also, during time period while in
> bulk mode and if during that time you perform minimally logged
> operations, forthcoming log backup will not have point in time
> restore (STOPAT). Also, if db files crash during this same period,
> not backing up tlog using NO_TRUNCATE option.
>
So the rate at which the log file grows during DBCC DBREINDEX will
decrease and regular inserts will still be logged while the db is using
the BULK_LOGGED model... this will do fine.
Craig.
"The power of accurate observation is frequently called cynicism by
those who don't have it."
recovery model and dbcc dbreindex
Hello,
I have a log file that grows significantly during DBCC DBREINDEX. Does
anyone know of a good reason why I shouldn't switch the recovery model
from FULL to BULK_LOGGED, perform DBCC DBREINDEX and then switch back to
FULL again?
Thanks,
Craig.
--
"...how we thwart the natural love of learning by leaving the natural
method of teaching what each wishes to learn, and insisting that you
shall learn what you have no taste or capacity for."Just watch the size of the following tlog backup (read about details for bulk logged in Books
Online). Also, during time period while in bulk mode and if during that time you perform minimally
logged operations, forthcoming log backup will not have point in time restore (STOPAT). Also, if db
files crash during this same period, not backing up tlog using NO_TRUNCATE option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Craig H." <spam@.thehurley.com> wrote in message news:ejUEgayLFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a log file that grows significantly during DBCC DBREINDEX. Does anyone know of a good
> reason why I shouldn't switch the recovery model from FULL to BULK_LOGGED, perform DBCC DBREINDEX
> and then switch back to FULL again?
> Thanks,
> Craig.
>
> --
> "...how we thwart the natural love of learning by leaving the natural method of teaching what each
> wishes to learn, and insisting that you shall learn what you have no taste or capacity for."|||On 2005-03-22 13:41, Tibor Karaszi wrote:
> Just watch the size of the following tlog backup (read about details
> for bulk logged in Books Online). Also, during time period while in
> bulk mode and if during that time you perform minimally logged
> operations, forthcoming log backup will not have point in time
> restore (STOPAT). Also, if db files crash during this same period,
> not backing up tlog using NO_TRUNCATE option.
>
So the rate at which the log file grows during DBCC DBREINDEX will
decrease and regular inserts will still be logged while the db is using
the BULK_LOGGED model... this will do fine.
Craig.
"The power of accurate observation is frequently called cynicism by
those who don't have it."
I have a log file that grows significantly during DBCC DBREINDEX. Does
anyone know of a good reason why I shouldn't switch the recovery model
from FULL to BULK_LOGGED, perform DBCC DBREINDEX and then switch back to
FULL again?
Thanks,
Craig.
--
"...how we thwart the natural love of learning by leaving the natural
method of teaching what each wishes to learn, and insisting that you
shall learn what you have no taste or capacity for."Just watch the size of the following tlog backup (read about details for bulk logged in Books
Online). Also, during time period while in bulk mode and if during that time you perform minimally
logged operations, forthcoming log backup will not have point in time restore (STOPAT). Also, if db
files crash during this same period, not backing up tlog using NO_TRUNCATE option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Craig H." <spam@.thehurley.com> wrote in message news:ejUEgayLFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a log file that grows significantly during DBCC DBREINDEX. Does anyone know of a good
> reason why I shouldn't switch the recovery model from FULL to BULK_LOGGED, perform DBCC DBREINDEX
> and then switch back to FULL again?
> Thanks,
> Craig.
>
> --
> "...how we thwart the natural love of learning by leaving the natural method of teaching what each
> wishes to learn, and insisting that you shall learn what you have no taste or capacity for."|||On 2005-03-22 13:41, Tibor Karaszi wrote:
> Just watch the size of the following tlog backup (read about details
> for bulk logged in Books Online). Also, during time period while in
> bulk mode and if during that time you perform minimally logged
> operations, forthcoming log backup will not have point in time
> restore (STOPAT). Also, if db files crash during this same period,
> not backing up tlog using NO_TRUNCATE option.
>
So the rate at which the log file grows during DBCC DBREINDEX will
decrease and regular inserts will still be logged while the db is using
the BULK_LOGGED model... this will do fine.
Craig.
"The power of accurate observation is frequently called cynicism by
those who don't have it."
Recovery Mode.
I want to ask one question regarding copying the full backup, differential
and then log into another machine and
restore automatically but I want to know about the recovery mode I have to
set on that machine so that only read
and restoration can be take place and Yes I don't want to create the
disaster recovery plan.
Can anyone add his/her comments.
Thanks
Hi,
Recovery model will not really matter since you are going to keep the
database in READ_ONLY mode. See the below command
to set the database to READ ONLY mode.
ALTER DATABASE <DBNAME> SET READ_ONLY
Thanks
Hari
SQL Server MVP
"Rogers" <Rogers@.discussions.microsoft.com> wrote in message
news:ACC08DB9-FCA1-4EB8-AFD2-4DAFB3D2BE98@.microsoft.com...
>I want to ask one question regarding copying the full backup, differential
> and then log into another machine and
> restore automatically but I want to know about the recovery mode I have to
> set on that machine so that only read
> and restoration can be take place and Yes I don't want to create the
> disaster recovery plan.
> Can anyone add his/her comments.
> Thanks
|||So, in the read only mode we can restore the database right...
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uYv$sqDPFHA.2748@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi,
> Recovery model will not really matter since you are going to keep the
> database in READ_ONLY mode. See the below command
> to set the database to READ ONLY mode.
> ALTER DATABASE <DBNAME> SET READ_ONLY
> Thanks
> Hari
> SQL Server MVP
>
> "Rogers" <Rogers@.discussions.microsoft.com> wrote in message
> news:ACC08DB9-FCA1-4EB8-AFD2-4DAFB3D2BE98@.microsoft.com...
differential[vbcol=seagreen]
to
>
|||Hi John,
Yes, But while restoring you must use STANDBY (undo file name) file name in
RESTORE DATABASE and RESTORE LOG command.
eg:-
RESTORE DATABASE TESTDB FROM DISK='d:\backup.bak' with
standy='d:\stanby.fil'
After this you could start restoring the Transaction logs based on a
frequency. To load the transaction logs you could use the below command
RESTORE LOG TESTDB FROM DISK='d:\backuplog.bak' with standy='d:\stanby.fil'
Thanks
Hari
SQL Server MVP
"John" <naissani@.hotmail.com> wrote in message
news:uCaXLEMPFHA.1096@.tk2msftngp13.phx.gbl...
> So, in the read only mode we can restore the database right...
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uYv$sqDPFHA.2748@.TK2MSFTNGP09.phx.gbl...
> differential
> to
>
sql
and then log into another machine and
restore automatically but I want to know about the recovery mode I have to
set on that machine so that only read
and restoration can be take place and Yes I don't want to create the
disaster recovery plan.
Can anyone add his/her comments.
Thanks
Hi,
Recovery model will not really matter since you are going to keep the
database in READ_ONLY mode. See the below command
to set the database to READ ONLY mode.
ALTER DATABASE <DBNAME> SET READ_ONLY
Thanks
Hari
SQL Server MVP
"Rogers" <Rogers@.discussions.microsoft.com> wrote in message
news:ACC08DB9-FCA1-4EB8-AFD2-4DAFB3D2BE98@.microsoft.com...
>I want to ask one question regarding copying the full backup, differential
> and then log into another machine and
> restore automatically but I want to know about the recovery mode I have to
> set on that machine so that only read
> and restoration can be take place and Yes I don't want to create the
> disaster recovery plan.
> Can anyone add his/her comments.
> Thanks
|||So, in the read only mode we can restore the database right...
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uYv$sqDPFHA.2748@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi,
> Recovery model will not really matter since you are going to keep the
> database in READ_ONLY mode. See the below command
> to set the database to READ ONLY mode.
> ALTER DATABASE <DBNAME> SET READ_ONLY
> Thanks
> Hari
> SQL Server MVP
>
> "Rogers" <Rogers@.discussions.microsoft.com> wrote in message
> news:ACC08DB9-FCA1-4EB8-AFD2-4DAFB3D2BE98@.microsoft.com...
differential[vbcol=seagreen]
to
>
|||Hi John,
Yes, But while restoring you must use STANDBY (undo file name) file name in
RESTORE DATABASE and RESTORE LOG command.
eg:-
RESTORE DATABASE TESTDB FROM DISK='d:\backup.bak' with
standy='d:\stanby.fil'
After this you could start restoring the Transaction logs based on a
frequency. To load the transaction logs you could use the below command
RESTORE LOG TESTDB FROM DISK='d:\backuplog.bak' with standy='d:\stanby.fil'
Thanks
Hari
SQL Server MVP
"John" <naissani@.hotmail.com> wrote in message
news:uCaXLEMPFHA.1096@.tk2msftngp13.phx.gbl...
> So, in the read only mode we can restore the database right...
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uYv$sqDPFHA.2748@.TK2MSFTNGP09.phx.gbl...
> differential
> to
>
sql
Recovery Mode Full vs Bulk_Logged
I am trying to determine how to create smaller log backups during indexing.
I am using SQL2K SP3 and currently reindex half of my 60GB each week
producing 30 GB of log file dumps. Readings from various sources suggest
that changing the db's recovery to BULK_LOGGED from FULL while reindexing
results in reduced logging. After changing to BULK_LOGGED while indexing, my
dumps are still 30 GB. My initial testing confirmed that the log did not
grow as large so I am surprised that the dumpfiles are still just as large.
The backup files are about the same size regardless of recovery setting.
For a test I restored my database to a test server and checked to make sure
the recovery mode was still FULL. I then set up a maintenance plan to dump
the log every 10 minutes. I let this run through 2 dumps of the log. I then
did a dbcc dbreindex on a large table. It produced 1330 MB of log in the
transaction log as shown in EM with the taskpad view. When the maintenance
job ran it produced a 1330 MB dumpfile as expected and reduced the amount of
space used in the trans log as shown in EM. I then changed the recovery mode
to BULK_LOGGED and allowed the maintenance job to run several times. I then
did dbcc reindex on a different large table. The transaction log grew only a
small amount but when the log dumped it created a 675 MB file! Not believing
this I detached the db and deleted the old tran log. I then reattached the
db with a new, clean log of 504 KB. I allowed the log to dump creating a 79
KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
large table. It grew the transaction log to 35 MB of which only 17.7 MB was
used (The log was set to grow by 10%). Why was only 17.7 MB used but the log
grew to 35 MB? I would think that it would have had to have at least 32 MB
used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the trans
log dumped to disk. The file was 648 MB. How does 35 MB of Trans log create
a dump file 648 MB in size?
Can anyone explain this?It is all dependant on how SQL stores, backs up, and restores log
information. The BULK LOGGED recovery model writes allocation information
to the log instead of full transactional information when BULK INSERT or one
of its variants is used. This would not be enough to recover, so when you
back up the log SQL adds in the data, expanding the log backup so you can
apply it and recover the database.
So, bulk logged will save on-disk space, but not backup space.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Dean Nicholson" <deann@.dtn.com> wrote in message
news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
indexing.
my
large.
sure
then
of
mode
then
a
believing
79
was
log
trans
create
richard
"Dean Nicholson" <deann@.dtn.com> wrote in message
news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
indexing.
my
large.
sure
then
of
mode
then
a
believing
79
was
log
trans
create
on can be to do DBCC
INDEXDEFRAG instead of DBREINDEX. And then do REINDEX at a less frequent int
erval (if at all).
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Dean Nicholson" <deann@.dtn.com> wrote in message news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...[QU
OTE]
> I am trying to determine how to create smaller log backups during indexing
.
> I am using SQL2K SP3 and currently reindex half of my 60GB each week
> producing 30 GB of log file dumps. Readings from various sources suggest
> that changing the db's recovery to BULK_LOGGED from FULL while reindexing
> results in reduced logging. After changing to BULK_LOGGED while indexing,
my
> dumps are still 30 GB. My initial testing confirmed that the log did not
> grow as large so I am surprised that the dumpfiles are still just as large
.
> The backup files are about the same size regardless of recovery setting.
> For a test I restored my database to a test server and checked to make sur
e
> the recovery mode was still FULL. I then set up a maintenance plan to dump
> the log every 10 minutes. I let this run through 2 dumps of the log. I the
n
> did a dbcc dbreindex on a large table. It produced 1330 MB of log in the
> transaction log as shown in EM with the taskpad view. When the maintenance
> job ran it produced a 1330 MB dumpfile as expected and reduced the amount
of
> space used in the trans log as shown in EM. I then changed the recovery mo
de
> to BULK_LOGGED and allowed the maintenance job to run several times. I the
n
> did dbcc reindex on a different large table. The transaction log grew only
a
> small amount but when the log dumped it created a 675 MB file! Not believi
ng
> this I detached the db and deleted the old tran log. I then reattached the
> db with a new, clean log of 504 KB. I allowed the log to dump creating a
79
> KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
> large table. It grew the transaction log to 35 MB of which only 17.7 MB wa
s
> used (The log was set to grow by 10%). Why was only 17.7 MB used but the l
og
> grew to 35 MB? I would think that it would have had to have at least 32 MB
> used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the tran
s
> log dumped to disk. The file was 648 MB. How does 35 MB of Trans log creat
e
> a dump file 648 MB in size?
> Can anyone explain this?
>[/QUOTE]|||I have looked at BOL on this and have done some testing. My bosses ask 'IS
DBCC INDEXDEFRAG safe and effective? How can you prove it?' Effective I can
prove. Safe I say I just have to trust MS. I suppose you recommend it? That
would help. Also DBREINDEX is easier to use, simple cursor to supply table
names. Do you have a script that will supply the necessary variables for
INDEXDEFRAG. I have a db with 1200+ tables that have > 1 row and I don't
think I want to do _WA_% or hind% indexes. I haven't found the correct
values to exclude these indexes from queries with out doing NOT LIKE .
Also. In response to Geoff's answer. How and where does SQL server save the
necessary info to use to fill in the info when it backs up everything to
disk?
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u%23nTsZM5DHA.1504@.TK2MSFTNGP12.phx.gbl...
option can be to do DBCC
interval (if at all).
http://groups.google.com/groups?oi=...ublic.sqlserver
news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
will run DBCC INDEXDEFRAG with the correct parameters. You may want to
modify it for your own needs.
SQL gets the data from the data file. You may find that some deletes are
blocked until you do a log backup once you complete a bulk-logged operation.
That is to make sure the data doesn't get lost.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Dean Nicholson" <deann@.dtn.com> wrote in message
news:Ofb5woO5DHA.2560@.TK2MSFTNGP09.phx.gbl...
can
That
the
in
http://groups.google.com/groups?oi=...ublic.sqlserver
-9 month ago. I've had
not problems whatsoever. However, our databases are small (up to 2GB data) a
nd we didn't really
suffer from performance issues in the first place.
I haven't read anywhere of any problems "safe-wise" with INDEXDEFRAG.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Dean Nicholson" <deann@.dtn.com> wrote in message news:Ofb5woO5DHA.2560@.TK2MSFTNGP09.phx.gbl...[QU
OTE]
> I have looked at BOL on this and have done some testing. My bosses ask 'IS
> DBCC INDEXDEFRAG safe and effective? How can you prove it?' Effective I c
an
> prove. Safe I say I just have to trust MS. I suppose you recommend it? Tha
t
> would help. Also DBREINDEX is easier to use, simple cursor to supply table
> names. Do you have a script that will supply the necessary variables for
> INDEXDEFRAG. I have a db with 1200+ tables that have > 1 row and I don't
> think I want to do _WA_% or hind% indexes. I haven't found the correct
> values to exclude these indexes from queries with out doing NOT LIKE .
> Also. In response to Geoff's answer. How and where does SQL server save th
e
> necessary info to use to fill in the info when it backs up everything to
> disk?
> Thanks
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:u%23nTsZM5DHA.1504@.TK2MSFTNGP12.phx.gbl...
> option can be to do DBCC
> interval (if at all).
> [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url
]
> news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
> indexing.
> reindexing
> indexing, my
> large.
> sure
> dump
> then
> the
> maintenance
> amount of
> mode
> then
> only a
> believing
> the
> a 79
> was
> log
> MB
> trans
> create
>[/QUOTE]
I am using SQL2K SP3 and currently reindex half of my 60GB each week
producing 30 GB of log file dumps. Readings from various sources suggest
that changing the db's recovery to BULK_LOGGED from FULL while reindexing
results in reduced logging. After changing to BULK_LOGGED while indexing, my
dumps are still 30 GB. My initial testing confirmed that the log did not
grow as large so I am surprised that the dumpfiles are still just as large.
The backup files are about the same size regardless of recovery setting.
For a test I restored my database to a test server and checked to make sure
the recovery mode was still FULL. I then set up a maintenance plan to dump
the log every 10 minutes. I let this run through 2 dumps of the log. I then
did a dbcc dbreindex on a large table. It produced 1330 MB of log in the
transaction log as shown in EM with the taskpad view. When the maintenance
job ran it produced a 1330 MB dumpfile as expected and reduced the amount of
space used in the trans log as shown in EM. I then changed the recovery mode
to BULK_LOGGED and allowed the maintenance job to run several times. I then
did dbcc reindex on a different large table. The transaction log grew only a
small amount but when the log dumped it created a 675 MB file! Not believing
this I detached the db and deleted the old tran log. I then reattached the
db with a new, clean log of 504 KB. I allowed the log to dump creating a 79
KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
large table. It grew the transaction log to 35 MB of which only 17.7 MB was
used (The log was set to grow by 10%). Why was only 17.7 MB used but the log
grew to 35 MB? I would think that it would have had to have at least 32 MB
used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the trans
log dumped to disk. The file was 648 MB. How does 35 MB of Trans log create
a dump file 648 MB in size?
Can anyone explain this?It is all dependant on how SQL stores, backs up, and restores log
information. The BULK LOGGED recovery model writes allocation information
to the log instead of full transactional information when BULK INSERT or one
of its variants is used. This would not be enough to recover, so when you
back up the log SQL adds in the data, expanding the log backup so you can
apply it and recover the database.
So, bulk logged will save on-disk space, but not backup space.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Dean Nicholson" <deann@.dtn.com> wrote in message
news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
quote:
> I am trying to determine how to create smaller log backups during
indexing.
quote:
> I am using SQL2K SP3 and currently reindex half of my 60GB each week
> producing 30 GB of log file dumps. Readings from various sources suggest
> that changing the db's recovery to BULK_LOGGED from FULL while reindexing
> results in reduced logging. After changing to BULK_LOGGED while indexing,
my
quote:
> dumps are still 30 GB. My initial testing confirmed that the log did not
> grow as large so I am surprised that the dumpfiles are still just as
large.
quote:
> The backup files are about the same size regardless of recovery setting.
> For a test I restored my database to a test server and checked to make
sure
quote:
> the recovery mode was still FULL. I then set up a maintenance plan to dump
> the log every 10 minutes. I let this run through 2 dumps of the log. I
then
quote:
> did a dbcc dbreindex on a large table. It produced 1330 MB of log in the
> transaction log as shown in EM with the taskpad view. When the maintenance
> job ran it produced a 1330 MB dumpfile as expected and reduced the amount
of
quote:
> space used in the trans log as shown in EM. I then changed the recovery
mode
quote:
> to BULK_LOGGED and allowed the maintenance job to run several times. I
then
quote:
> did dbcc reindex on a different large table. The transaction log grew only
a
quote:
> small amount but when the log dumped it created a 675 MB file! Not
believing
quote:
> this I detached the db and deleted the old tran log. I then reattached the
> db with a new, clean log of 504 KB. I allowed the log to dump creating a
79
quote:
> KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
> large table. It grew the transaction log to 35 MB of which only 17.7 MB
was
quote:
> used (The log was set to grow by 10%). Why was only 17.7 MB used but the
log
quote:
> grew to 35 MB? I would think that it would have had to have at least 32 MB
> used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the
trans
quote:
> log dumped to disk. The file was 648 MB. How does 35 MB of Trans log
create
quote:|||I tackled the same problem by using sqllitespeed backup.
> a dump file 648 MB in size?
> Can anyone explain this?
>
richard
"Dean Nicholson" <deann@.dtn.com> wrote in message
news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
quote:
> I am trying to determine how to create smaller log backups during
indexing.
quote:
> I am using SQL2K SP3 and currently reindex half of my 60GB each week
> producing 30 GB of log file dumps. Readings from various sources suggest
> that changing the db's recovery to BULK_LOGGED from FULL while reindexing
> results in reduced logging. After changing to BULK_LOGGED while indexing,
my
quote:
> dumps are still 30 GB. My initial testing confirmed that the log did not
> grow as large so I am surprised that the dumpfiles are still just as
large.
quote:
> The backup files are about the same size regardless of recovery setting.
> For a test I restored my database to a test server and checked to make
sure
quote:
> the recovery mode was still FULL. I then set up a maintenance plan to dump
> the log every 10 minutes. I let this run through 2 dumps of the log. I
then
quote:
> did a dbcc dbreindex on a large table. It produced 1330 MB of log in the
> transaction log as shown in EM with the taskpad view. When the maintenance
> job ran it produced a 1330 MB dumpfile as expected and reduced the amount
of
quote:
> space used in the trans log as shown in EM. I then changed the recovery
mode
quote:
> to BULK_LOGGED and allowed the maintenance job to run several times. I
then
quote:
> did dbcc reindex on a different large table. The transaction log grew only
a
quote:
> small amount but when the log dumped it created a 675 MB file! Not
believing
quote:
> this I detached the db and deleted the old tran log. I then reattached the
> db with a new, clean log of 504 KB. I allowed the log to dump creating a
79
quote:
> KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
> large table. It grew the transaction log to 35 MB of which only 17.7 MB
was
quote:
> used (The log was set to grow by 10%). Why was only 17.7 MB used but the
log
quote:
> grew to 35 MB? I would think that it would have had to have at least 32 MB
quote:
> used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the
trans
quote:
> log dumped to disk. The file was 648 MB. How does 35 MB of Trans log
create
quote:|||Geoff explained the reason for the behavior. I just want to add that an opti
> a dump file 648 MB in size?
> Can anyone explain this?
>
on can be to do DBCC
INDEXDEFRAG instead of DBREINDEX. And then do REINDEX at a less frequent int
erval (if at all).
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Dean Nicholson" <deann@.dtn.com> wrote in message news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...[QU
OTE]
> I am trying to determine how to create smaller log backups during indexing
.
> I am using SQL2K SP3 and currently reindex half of my 60GB each week
> producing 30 GB of log file dumps. Readings from various sources suggest
> that changing the db's recovery to BULK_LOGGED from FULL while reindexing
> results in reduced logging. After changing to BULK_LOGGED while indexing,
my
> dumps are still 30 GB. My initial testing confirmed that the log did not
> grow as large so I am surprised that the dumpfiles are still just as large
.
> The backup files are about the same size regardless of recovery setting.
> For a test I restored my database to a test server and checked to make sur
e
> the recovery mode was still FULL. I then set up a maintenance plan to dump
> the log every 10 minutes. I let this run through 2 dumps of the log. I the
n
> did a dbcc dbreindex on a large table. It produced 1330 MB of log in the
> transaction log as shown in EM with the taskpad view. When the maintenance
> job ran it produced a 1330 MB dumpfile as expected and reduced the amount
of
> space used in the trans log as shown in EM. I then changed the recovery mo
de
> to BULK_LOGGED and allowed the maintenance job to run several times. I the
n
> did dbcc reindex on a different large table. The transaction log grew only
a
> small amount but when the log dumped it created a 675 MB file! Not believi
ng
> this I detached the db and deleted the old tran log. I then reattached the
> db with a new, clean log of 504 KB. I allowed the log to dump creating a
79
> KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
> large table. It grew the transaction log to 35 MB of which only 17.7 MB wa
s
> used (The log was set to grow by 10%). Why was only 17.7 MB used but the l
og
> grew to 35 MB? I would think that it would have had to have at least 32 MB
> used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the tran
s
> log dumped to disk. The file was 648 MB. How does 35 MB of Trans log creat
e
> a dump file 648 MB in size?
> Can anyone explain this?
>[/QUOTE]|||I have looked at BOL on this and have done some testing. My bosses ask 'IS
DBCC INDEXDEFRAG safe and effective? How can you prove it?' Effective I can
prove. Safe I say I just have to trust MS. I suppose you recommend it? That
would help. Also DBREINDEX is easier to use, simple cursor to supply table
names. Do you have a script that will supply the necessary variables for
INDEXDEFRAG. I have a db with 1200+ tables that have > 1 row and I don't
think I want to do _WA_% or hind% indexes. I haven't found the correct
values to exclude these indexes from queries with out doing NOT LIKE .
Also. In response to Geoff's answer. How and where does SQL server save the
necessary info to use to fill in the info when it backs up everything to
disk?
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u%23nTsZM5DHA.1504@.TK2MSFTNGP12.phx.gbl...
quote:
> Geoff explained the reason for the behavior. I just want to add that an
option can be to do DBCC
quote:
> INDEXDEFRAG instead of DBREINDEX. And then do REINDEX at a less frequent
interval (if at all).
quote:
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
quote:
>
> "Dean Nicholson" <deann@.dtn.com> wrote in message
news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
quote:|||There is a template stored procedure in BOL under 'DBCC SHOWCONTIG' that
indexing.[QUOTE]
reindexing[QUOTE]
indexing, my[QUOTE]
large.[QUOTE]
sure[QUOTE]
dump[QUOTE]
then[QUOTE]
the[QUOTE]
maintenance[QUOTE]
amount of[QUOTE]
mode[QUOTE]
then[QUOTE]
only a[QUOTE]
believing[QUOTE]
the[QUOTE]
a 79[QUOTE]
was[QUOTE]
log[QUOTE]
MB[QUOTE]
trans[QUOTE]
create[QUOTE]
>
will run DBCC INDEXDEFRAG with the correct parameters. You may want to
modify it for your own needs.
SQL gets the data from the data file. You may find that some deletes are
blocked until you do a log backup once you complete a bulk-logged operation.
That is to make sure the data doesn't get lost.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Dean Nicholson" <deann@.dtn.com> wrote in message
news:Ofb5woO5DHA.2560@.TK2MSFTNGP09.phx.gbl...
quote:
> I have looked at BOL on this and have done some testing. My bosses ask 'IS
> DBCC INDEXDEFRAG safe and effective? How can you prove it?' Effective I
can
quote:
> prove. Safe I say I just have to trust MS. I suppose you recommend it?
That
quote:
> would help. Also DBREINDEX is easier to use, simple cursor to supply table
> names. Do you have a script that will supply the necessary variables for
> INDEXDEFRAG. I have a db with 1200+ tables that have > 1 row and I don't
> think I want to do _WA_% or hind% indexes. I haven't found the correct
> values to exclude these indexes from queries with out doing NOT LIKE .
> Also. In response to Geoff's answer. How and where does SQL server save
the
quote:
> necessary info to use to fill in the info when it backs up everything to
> disk?
> Thanks
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
quote:
> message news:u%23nTsZM5DHA.1504@.TK2MSFTNGP12.phx.gbl...
> option can be to do DBCC
> interval (if at all).
>
http://groups.google.com/groups?oi=...ublic.sqlserver
quote:|||On our internal SQL Server, I switched from DBREINDEX to INDEXDEFRAG about 6
> news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
> indexing.
suggest[QUOTE]
> reindexing
> indexing, my
not[QUOTE]
> large.
setting.[QUOTE]
> sure
> dump
> then
> the
> maintenance
> amount of
recovery[QUOTE]
> mode
> then
> only a
> believing
> the
creating[QUOTE]
> a 79
another[QUOTE]
MB[QUOTE]
> was
the[QUOTE]
> log
32[QUOTE]
> MB
> trans
> create
>
-9 month ago. I've had
not problems whatsoever. However, our databases are small (up to 2GB data) a
nd we didn't really
suffer from performance issues in the first place.
I haven't read anywhere of any problems "safe-wise" with INDEXDEFRAG.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Dean Nicholson" <deann@.dtn.com> wrote in message news:Ofb5woO5DHA.2560@.TK2MSFTNGP09.phx.gbl...[QU
OTE]
> I have looked at BOL on this and have done some testing. My bosses ask 'IS
> DBCC INDEXDEFRAG safe and effective? How can you prove it?' Effective I c
an
> prove. Safe I say I just have to trust MS. I suppose you recommend it? Tha
t
> would help. Also DBREINDEX is easier to use, simple cursor to supply table
> names. Do you have a script that will supply the necessary variables for
> INDEXDEFRAG. I have a db with 1200+ tables that have > 1 row and I don't
> think I want to do _WA_% or hind% indexes. I haven't found the correct
> values to exclude these indexes from queries with out doing NOT LIKE .
> Also. In response to Geoff's answer. How and where does SQL server save th
e
> necessary info to use to fill in the info when it backs up everything to
> disk?
> Thanks
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:u%23nTsZM5DHA.1504@.TK2MSFTNGP12.phx.gbl...
> option can be to do DBCC
> interval (if at all).
> [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url
]
> news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
> indexing.
> reindexing
> indexing, my
> large.
> sure
> dump
> then
> the
> maintenance
> amount of
> mode
> then
> only a
> believing
> the
> a 79
> was
> log
> MB
> trans
> create
>[/QUOTE]
Recovery Mode Full vs Bulk_Logged
I am trying to determine how to create smaller log backups during indexing.
I am using SQL2K SP3 and currently reindex half of my 60GB each week
producing 30 GB of log file dumps. Readings from various sources suggest
that changing the db's recovery to BULK_LOGGED from FULL while reindexing
results in reduced logging. After changing to BULK_LOGGED while indexing, my
dumps are still 30 GB. My initial testing confirmed that the log did not
grow as large so I am surprised that the dumpfiles are still just as large.
The backup files are about the same size regardless of recovery setting.
For a test I restored my database to a test server and checked to make sure
the recovery mode was still FULL. I then set up a maintenance plan to dump
the log every 10 minutes. I let this run through 2 dumps of the log. I then
did a dbcc dbreindex on a large table. It produced 1330 MB of log in the
transaction log as shown in EM with the taskpad view. When the maintenance
job ran it produced a 1330 MB dumpfile as expected and reduced the amount of
space used in the trans log as shown in EM. I then changed the recovery mode
to BULK_LOGGED and allowed the maintenance job to run several times. I then
did dbcc reindex on a different large table. The transaction log grew only a
small amount but when the log dumped it created a 675 MB file! Not believing
this I detached the db and deleted the old tran log. I then reattached the
db with a new, clean log of 504 KB. I allowed the log to dump creating a 79
KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
large table. It grew the transaction log to 35 MB of which only 17.7 MB was
used (The log was set to grow by 10%). Why was only 17.7 MB used but the log
grew to 35 MB? I would think that it would have had to have at least 32 MB
used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the trans
log dumped to disk. The file was 648 MB. How does 35 MB of Trans log create
a dump file 648 MB in size?
Can anyone explain this?It is all dependant on how SQL stores, backs up, and restores log
information. The BULK LOGGED recovery model writes allocation information
to the log instead of full transactional information when BULK INSERT or one
of its variants is used. This would not be enough to recover, so when you
back up the log SQL adds in the data, expanding the log backup so you can
apply it and recover the database.
So, bulk logged will save on-disk space, but not backup space.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Dean Nicholson" <deann@.dtn.com> wrote in message
news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
> I am trying to determine how to create smaller log backups during
indexing.
> I am using SQL2K SP3 and currently reindex half of my 60GB each week
> producing 30 GB of log file dumps. Readings from various sources suggest
> that changing the db's recovery to BULK_LOGGED from FULL while reindexing
> results in reduced logging. After changing to BULK_LOGGED while indexing,
my
> dumps are still 30 GB. My initial testing confirmed that the log did not
> grow as large so I am surprised that the dumpfiles are still just as
large.
> The backup files are about the same size regardless of recovery setting.
> For a test I restored my database to a test server and checked to make
sure
> the recovery mode was still FULL. I then set up a maintenance plan to dump
> the log every 10 minutes. I let this run through 2 dumps of the log. I
then
> did a dbcc dbreindex on a large table. It produced 1330 MB of log in the
> transaction log as shown in EM with the taskpad view. When the maintenance
> job ran it produced a 1330 MB dumpfile as expected and reduced the amount
of
> space used in the trans log as shown in EM. I then changed the recovery
mode
> to BULK_LOGGED and allowed the maintenance job to run several times. I
then
> did dbcc reindex on a different large table. The transaction log grew only
a
> small amount but when the log dumped it created a 675 MB file! Not
believing
> this I detached the db and deleted the old tran log. I then reattached the
> db with a new, clean log of 504 KB. I allowed the log to dump creating a
79
> KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
> large table. It grew the transaction log to 35 MB of which only 17.7 MB
was
> used (The log was set to grow by 10%). Why was only 17.7 MB used but the
log
> grew to 35 MB? I would think that it would have had to have at least 32 MB
> used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the
trans
> log dumped to disk. The file was 648 MB. How does 35 MB of Trans log
create
> a dump file 648 MB in size?
> Can anyone explain this?
>|||I tackled the same problem by using sqllitespeed backup.
richard
"Dean Nicholson" <deann@.dtn.com> wrote in message
news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
> I am trying to determine how to create smaller log backups during
indexing.
> I am using SQL2K SP3 and currently reindex half of my 60GB each week
> producing 30 GB of log file dumps. Readings from various sources suggest
> that changing the db's recovery to BULK_LOGGED from FULL while reindexing
> results in reduced logging. After changing to BULK_LOGGED while indexing,
my
> dumps are still 30 GB. My initial testing confirmed that the log did not
> grow as large so I am surprised that the dumpfiles are still just as
large.
> The backup files are about the same size regardless of recovery setting.
> For a test I restored my database to a test server and checked to make
sure
> the recovery mode was still FULL. I then set up a maintenance plan to dump
> the log every 10 minutes. I let this run through 2 dumps of the log. I
then
> did a dbcc dbreindex on a large table. It produced 1330 MB of log in the
> transaction log as shown in EM with the taskpad view. When the maintenance
> job ran it produced a 1330 MB dumpfile as expected and reduced the amount
of
> space used in the trans log as shown in EM. I then changed the recovery
mode
> to BULK_LOGGED and allowed the maintenance job to run several times. I
then
> did dbcc reindex on a different large table. The transaction log grew only
a
> small amount but when the log dumped it created a 675 MB file! Not
believing
> this I detached the db and deleted the old tran log. I then reattached the
> db with a new, clean log of 504 KB. I allowed the log to dump creating a
79
> KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
> large table. It grew the transaction log to 35 MB of which only 17.7 MB
was
> used (The log was set to grow by 10%). Why was only 17.7 MB used but the
log
> grew to 35 MB? I would think that it would have had to have at least 32 MB
> used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the
trans
> log dumped to disk. The file was 648 MB. How does 35 MB of Trans log
create
> a dump file 648 MB in size?
> Can anyone explain this?
>|||Geoff explained the reason for the behavior. I just want to add that an option can be to do DBCC
INDEXDEFRAG instead of DBREINDEX. And then do REINDEX at a less frequent interval (if at all).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dean Nicholson" <deann@.dtn.com> wrote in message news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
> I am trying to determine how to create smaller log backups during indexing.
> I am using SQL2K SP3 and currently reindex half of my 60GB each week
> producing 30 GB of log file dumps. Readings from various sources suggest
> that changing the db's recovery to BULK_LOGGED from FULL while reindexing
> results in reduced logging. After changing to BULK_LOGGED while indexing, my
> dumps are still 30 GB. My initial testing confirmed that the log did not
> grow as large so I am surprised that the dumpfiles are still just as large.
> The backup files are about the same size regardless of recovery setting.
> For a test I restored my database to a test server and checked to make sure
> the recovery mode was still FULL. I then set up a maintenance plan to dump
> the log every 10 minutes. I let this run through 2 dumps of the log. I then
> did a dbcc dbreindex on a large table. It produced 1330 MB of log in the
> transaction log as shown in EM with the taskpad view. When the maintenance
> job ran it produced a 1330 MB dumpfile as expected and reduced the amount of
> space used in the trans log as shown in EM. I then changed the recovery mode
> to BULK_LOGGED and allowed the maintenance job to run several times. I then
> did dbcc reindex on a different large table. The transaction log grew only a
> small amount but when the log dumped it created a 675 MB file! Not believing
> this I detached the db and deleted the old tran log. I then reattached the
> db with a new, clean log of 504 KB. I allowed the log to dump creating a 79
> KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
> large table. It grew the transaction log to 35 MB of which only 17.7 MB was
> used (The log was set to grow by 10%). Why was only 17.7 MB used but the log
> grew to 35 MB? I would think that it would have had to have at least 32 MB
> used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the trans
> log dumped to disk. The file was 648 MB. How does 35 MB of Trans log create
> a dump file 648 MB in size?
> Can anyone explain this?
>|||I have looked at BOL on this and have done some testing. My bosses ask 'IS
DBCC INDEXDEFRAG safe and effective? How can you prove it?' Effective I can
prove. Safe I say I just have to trust MS. I suppose you recommend it? That
would help. Also DBREINDEX is easier to use, simple cursor to supply table
names. Do you have a script that will supply the necessary variables for
INDEXDEFRAG. I have a db with 1200+ tables that have > 1 row and I don't
think I want to do _WA_% or hind% indexes. I haven't found the correct
values to exclude these indexes from queries with out doing NOT LIKE .
Also. In response to Geoff's answer. How and where does SQL server save the
necessary info to use to fill in the info when it backs up everything to
disk?
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u%23nTsZM5DHA.1504@.TK2MSFTNGP12.phx.gbl...
> Geoff explained the reason for the behavior. I just want to add that an
option can be to do DBCC
> INDEXDEFRAG instead of DBREINDEX. And then do REINDEX at a less frequent
interval (if at all).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Dean Nicholson" <deann@.dtn.com> wrote in message
news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
> > I am trying to determine how to create smaller log backups during
indexing.
> > I am using SQL2K SP3 and currently reindex half of my 60GB each week
> > producing 30 GB of log file dumps. Readings from various sources suggest
> > that changing the db's recovery to BULK_LOGGED from FULL while
reindexing
> > results in reduced logging. After changing to BULK_LOGGED while
indexing, my
> > dumps are still 30 GB. My initial testing confirmed that the log did not
> > grow as large so I am surprised that the dumpfiles are still just as
large.
> > The backup files are about the same size regardless of recovery setting.
> >
> > For a test I restored my database to a test server and checked to make
sure
> > the recovery mode was still FULL. I then set up a maintenance plan to
dump
> > the log every 10 minutes. I let this run through 2 dumps of the log. I
then
> > did a dbcc dbreindex on a large table. It produced 1330 MB of log in
the
> > transaction log as shown in EM with the taskpad view. When the
maintenance
> > job ran it produced a 1330 MB dumpfile as expected and reduced the
amount of
> > space used in the trans log as shown in EM. I then changed the recovery
mode
> > to BULK_LOGGED and allowed the maintenance job to run several times. I
then
> > did dbcc reindex on a different large table. The transaction log grew
only a
> > small amount but when the log dumped it created a 675 MB file! Not
believing
> > this I detached the db and deleted the old tran log. I then reattached
the
> > db with a new, clean log of 504 KB. I allowed the log to dump creating
a 79
> > KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
> > large table. It grew the transaction log to 35 MB of which only 17.7 MB
was
> > used (The log was set to grow by 10%). Why was only 17.7 MB used but the
log
> > grew to 35 MB? I would think that it would have had to have at least 32
MB
> > used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the
trans
> > log dumped to disk. The file was 648 MB. How does 35 MB of Trans log
create
> > a dump file 648 MB in size?
> >
> > Can anyone explain this?
> >
> >
>|||There is a template stored procedure in BOL under 'DBCC SHOWCONTIG' that
will run DBCC INDEXDEFRAG with the correct parameters. You may want to
modify it for your own needs.
SQL gets the data from the data file. You may find that some deletes are
blocked until you do a log backup once you complete a bulk-logged operation.
That is to make sure the data doesn't get lost.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Dean Nicholson" <deann@.dtn.com> wrote in message
news:Ofb5woO5DHA.2560@.TK2MSFTNGP09.phx.gbl...
> I have looked at BOL on this and have done some testing. My bosses ask 'IS
> DBCC INDEXDEFRAG safe and effective? How can you prove it?' Effective I
can
> prove. Safe I say I just have to trust MS. I suppose you recommend it?
That
> would help. Also DBREINDEX is easier to use, simple cursor to supply table
> names. Do you have a script that will supply the necessary variables for
> INDEXDEFRAG. I have a db with 1200+ tables that have > 1 row and I don't
> think I want to do _WA_% or hind% indexes. I haven't found the correct
> values to exclude these indexes from queries with out doing NOT LIKE .
> Also. In response to Geoff's answer. How and where does SQL server save
the
> necessary info to use to fill in the info when it backs up everything to
> disk?
> Thanks
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:u%23nTsZM5DHA.1504@.TK2MSFTNGP12.phx.gbl...
> > Geoff explained the reason for the behavior. I just want to add that an
> option can be to do DBCC
> > INDEXDEFRAG instead of DBREINDEX. And then do REINDEX at a less frequent
> interval (if at all).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Dean Nicholson" <deann@.dtn.com> wrote in message
> news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
> > > I am trying to determine how to create smaller log backups during
> indexing.
> > > I am using SQL2K SP3 and currently reindex half of my 60GB each week
> > > producing 30 GB of log file dumps. Readings from various sources
suggest
> > > that changing the db's recovery to BULK_LOGGED from FULL while
> reindexing
> > > results in reduced logging. After changing to BULK_LOGGED while
> indexing, my
> > > dumps are still 30 GB. My initial testing confirmed that the log did
not
> > > grow as large so I am surprised that the dumpfiles are still just as
> large.
> > > The backup files are about the same size regardless of recovery
setting.
> > >
> > > For a test I restored my database to a test server and checked to make
> sure
> > > the recovery mode was still FULL. I then set up a maintenance plan to
> dump
> > > the log every 10 minutes. I let this run through 2 dumps of the log. I
> then
> > > did a dbcc dbreindex on a large table. It produced 1330 MB of log in
> the
> > > transaction log as shown in EM with the taskpad view. When the
> maintenance
> > > job ran it produced a 1330 MB dumpfile as expected and reduced the
> amount of
> > > space used in the trans log as shown in EM. I then changed the
recovery
> mode
> > > to BULK_LOGGED and allowed the maintenance job to run several times. I
> then
> > > did dbcc reindex on a different large table. The transaction log grew
> only a
> > > small amount but when the log dumped it created a 675 MB file! Not
> believing
> > > this I detached the db and deleted the old tran log. I then reattached
> the
> > > db with a new, clean log of 504 KB. I allowed the log to dump
creating
> a 79
> > > KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on
another
> > > large table. It grew the transaction log to 35 MB of which only 17.7
MB
> was
> > > used (The log was set to grow by 10%). Why was only 17.7 MB used but
the
> log
> > > grew to 35 MB? I would think that it would have had to have at least
32
> MB
> > > used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the
> trans
> > > log dumped to disk. The file was 648 MB. How does 35 MB of Trans log
> create
> > > a dump file 648 MB in size?
> > >
> > > Can anyone explain this?
> > >
> > >
> >
> >
>|||On our internal SQL Server, I switched from DBREINDEX to INDEXDEFRAG about 6-9 month ago. I've had
not problems whatsoever. However, our databases are small (up to 2GB data) and we didn't really
suffer from performance issues in the first place.
I haven't read anywhere of any problems "safe-wise" with INDEXDEFRAG.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dean Nicholson" <deann@.dtn.com> wrote in message news:Ofb5woO5DHA.2560@.TK2MSFTNGP09.phx.gbl...
> I have looked at BOL on this and have done some testing. My bosses ask 'IS
> DBCC INDEXDEFRAG safe and effective? How can you prove it?' Effective I can
> prove. Safe I say I just have to trust MS. I suppose you recommend it? That
> would help. Also DBREINDEX is easier to use, simple cursor to supply table
> names. Do you have a script that will supply the necessary variables for
> INDEXDEFRAG. I have a db with 1200+ tables that have > 1 row and I don't
> think I want to do _WA_% or hind% indexes. I haven't found the correct
> values to exclude these indexes from queries with out doing NOT LIKE .
> Also. In response to Geoff's answer. How and where does SQL server save the
> necessary info to use to fill in the info when it backs up everything to
> disk?
> Thanks
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u%23nTsZM5DHA.1504@.TK2MSFTNGP12.phx.gbl...
> > Geoff explained the reason for the behavior. I just want to add that an
> option can be to do DBCC
> > INDEXDEFRAG instead of DBREINDEX. And then do REINDEX at a less frequent
> interval (if at all).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Dean Nicholson" <deann@.dtn.com> wrote in message
> news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
> > > I am trying to determine how to create smaller log backups during
> indexing.
> > > I am using SQL2K SP3 and currently reindex half of my 60GB each week
> > > producing 30 GB of log file dumps. Readings from various sources suggest
> > > that changing the db's recovery to BULK_LOGGED from FULL while
> reindexing
> > > results in reduced logging. After changing to BULK_LOGGED while
> indexing, my
> > > dumps are still 30 GB. My initial testing confirmed that the log did not
> > > grow as large so I am surprised that the dumpfiles are still just as
> large.
> > > The backup files are about the same size regardless of recovery setting.
> > >
> > > For a test I restored my database to a test server and checked to make
> sure
> > > the recovery mode was still FULL. I then set up a maintenance plan to
> dump
> > > the log every 10 minutes. I let this run through 2 dumps of the log. I
> then
> > > did a dbcc dbreindex on a large table. It produced 1330 MB of log in
> the
> > > transaction log as shown in EM with the taskpad view. When the
> maintenance
> > > job ran it produced a 1330 MB dumpfile as expected and reduced the
> amount of
> > > space used in the trans log as shown in EM. I then changed the recovery
> mode
> > > to BULK_LOGGED and allowed the maintenance job to run several times. I
> then
> > > did dbcc reindex on a different large table. The transaction log grew
> only a
> > > small amount but when the log dumped it created a 675 MB file! Not
> believing
> > > this I detached the db and deleted the old tran log. I then reattached
> the
> > > db with a new, clean log of 504 KB. I allowed the log to dump creating
> a 79
> > > KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
> > > large table. It grew the transaction log to 35 MB of which only 17.7 MB
> was
> > > used (The log was set to grow by 10%). Why was only 17.7 MB used but the
> log
> > > grew to 35 MB? I would think that it would have had to have at least 32
> MB
> > > used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the
> trans
> > > log dumped to disk. The file was 648 MB. How does 35 MB of Trans log
> create
> > > a dump file 648 MB in size?
> > >
> > > Can anyone explain this?
> > >
> > >
> >
> >
>sql
I am using SQL2K SP3 and currently reindex half of my 60GB each week
producing 30 GB of log file dumps. Readings from various sources suggest
that changing the db's recovery to BULK_LOGGED from FULL while reindexing
results in reduced logging. After changing to BULK_LOGGED while indexing, my
dumps are still 30 GB. My initial testing confirmed that the log did not
grow as large so I am surprised that the dumpfiles are still just as large.
The backup files are about the same size regardless of recovery setting.
For a test I restored my database to a test server and checked to make sure
the recovery mode was still FULL. I then set up a maintenance plan to dump
the log every 10 minutes. I let this run through 2 dumps of the log. I then
did a dbcc dbreindex on a large table. It produced 1330 MB of log in the
transaction log as shown in EM with the taskpad view. When the maintenance
job ran it produced a 1330 MB dumpfile as expected and reduced the amount of
space used in the trans log as shown in EM. I then changed the recovery mode
to BULK_LOGGED and allowed the maintenance job to run several times. I then
did dbcc reindex on a different large table. The transaction log grew only a
small amount but when the log dumped it created a 675 MB file! Not believing
this I detached the db and deleted the old tran log. I then reattached the
db with a new, clean log of 504 KB. I allowed the log to dump creating a 79
KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
large table. It grew the transaction log to 35 MB of which only 17.7 MB was
used (The log was set to grow by 10%). Why was only 17.7 MB used but the log
grew to 35 MB? I would think that it would have had to have at least 32 MB
used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the trans
log dumped to disk. The file was 648 MB. How does 35 MB of Trans log create
a dump file 648 MB in size?
Can anyone explain this?It is all dependant on how SQL stores, backs up, and restores log
information. The BULK LOGGED recovery model writes allocation information
to the log instead of full transactional information when BULK INSERT or one
of its variants is used. This would not be enough to recover, so when you
back up the log SQL adds in the data, expanding the log backup so you can
apply it and recover the database.
So, bulk logged will save on-disk space, but not backup space.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Dean Nicholson" <deann@.dtn.com> wrote in message
news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
> I am trying to determine how to create smaller log backups during
indexing.
> I am using SQL2K SP3 and currently reindex half of my 60GB each week
> producing 30 GB of log file dumps. Readings from various sources suggest
> that changing the db's recovery to BULK_LOGGED from FULL while reindexing
> results in reduced logging. After changing to BULK_LOGGED while indexing,
my
> dumps are still 30 GB. My initial testing confirmed that the log did not
> grow as large so I am surprised that the dumpfiles are still just as
large.
> The backup files are about the same size regardless of recovery setting.
> For a test I restored my database to a test server and checked to make
sure
> the recovery mode was still FULL. I then set up a maintenance plan to dump
> the log every 10 minutes. I let this run through 2 dumps of the log. I
then
> did a dbcc dbreindex on a large table. It produced 1330 MB of log in the
> transaction log as shown in EM with the taskpad view. When the maintenance
> job ran it produced a 1330 MB dumpfile as expected and reduced the amount
of
> space used in the trans log as shown in EM. I then changed the recovery
mode
> to BULK_LOGGED and allowed the maintenance job to run several times. I
then
> did dbcc reindex on a different large table. The transaction log grew only
a
> small amount but when the log dumped it created a 675 MB file! Not
believing
> this I detached the db and deleted the old tran log. I then reattached the
> db with a new, clean log of 504 KB. I allowed the log to dump creating a
79
> KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
> large table. It grew the transaction log to 35 MB of which only 17.7 MB
was
> used (The log was set to grow by 10%). Why was only 17.7 MB used but the
log
> grew to 35 MB? I would think that it would have had to have at least 32 MB
> used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the
trans
> log dumped to disk. The file was 648 MB. How does 35 MB of Trans log
create
> a dump file 648 MB in size?
> Can anyone explain this?
>|||I tackled the same problem by using sqllitespeed backup.
richard
"Dean Nicholson" <deann@.dtn.com> wrote in message
news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
> I am trying to determine how to create smaller log backups during
indexing.
> I am using SQL2K SP3 and currently reindex half of my 60GB each week
> producing 30 GB of log file dumps. Readings from various sources suggest
> that changing the db's recovery to BULK_LOGGED from FULL while reindexing
> results in reduced logging. After changing to BULK_LOGGED while indexing,
my
> dumps are still 30 GB. My initial testing confirmed that the log did not
> grow as large so I am surprised that the dumpfiles are still just as
large.
> The backup files are about the same size regardless of recovery setting.
> For a test I restored my database to a test server and checked to make
sure
> the recovery mode was still FULL. I then set up a maintenance plan to dump
> the log every 10 minutes. I let this run through 2 dumps of the log. I
then
> did a dbcc dbreindex on a large table. It produced 1330 MB of log in the
> transaction log as shown in EM with the taskpad view. When the maintenance
> job ran it produced a 1330 MB dumpfile as expected and reduced the amount
of
> space used in the trans log as shown in EM. I then changed the recovery
mode
> to BULK_LOGGED and allowed the maintenance job to run several times. I
then
> did dbcc reindex on a different large table. The transaction log grew only
a
> small amount but when the log dumped it created a 675 MB file! Not
believing
> this I detached the db and deleted the old tran log. I then reattached the
> db with a new, clean log of 504 KB. I allowed the log to dump creating a
79
> KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
> large table. It grew the transaction log to 35 MB of which only 17.7 MB
was
> used (The log was set to grow by 10%). Why was only 17.7 MB used but the
log
> grew to 35 MB? I would think that it would have had to have at least 32 MB
> used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the
trans
> log dumped to disk. The file was 648 MB. How does 35 MB of Trans log
create
> a dump file 648 MB in size?
> Can anyone explain this?
>|||Geoff explained the reason for the behavior. I just want to add that an option can be to do DBCC
INDEXDEFRAG instead of DBREINDEX. And then do REINDEX at a less frequent interval (if at all).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dean Nicholson" <deann@.dtn.com> wrote in message news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
> I am trying to determine how to create smaller log backups during indexing.
> I am using SQL2K SP3 and currently reindex half of my 60GB each week
> producing 30 GB of log file dumps. Readings from various sources suggest
> that changing the db's recovery to BULK_LOGGED from FULL while reindexing
> results in reduced logging. After changing to BULK_LOGGED while indexing, my
> dumps are still 30 GB. My initial testing confirmed that the log did not
> grow as large so I am surprised that the dumpfiles are still just as large.
> The backup files are about the same size regardless of recovery setting.
> For a test I restored my database to a test server and checked to make sure
> the recovery mode was still FULL. I then set up a maintenance plan to dump
> the log every 10 minutes. I let this run through 2 dumps of the log. I then
> did a dbcc dbreindex on a large table. It produced 1330 MB of log in the
> transaction log as shown in EM with the taskpad view. When the maintenance
> job ran it produced a 1330 MB dumpfile as expected and reduced the amount of
> space used in the trans log as shown in EM. I then changed the recovery mode
> to BULK_LOGGED and allowed the maintenance job to run several times. I then
> did dbcc reindex on a different large table. The transaction log grew only a
> small amount but when the log dumped it created a 675 MB file! Not believing
> this I detached the db and deleted the old tran log. I then reattached the
> db with a new, clean log of 504 KB. I allowed the log to dump creating a 79
> KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
> large table. It grew the transaction log to 35 MB of which only 17.7 MB was
> used (The log was set to grow by 10%). Why was only 17.7 MB used but the log
> grew to 35 MB? I would think that it would have had to have at least 32 MB
> used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the trans
> log dumped to disk. The file was 648 MB. How does 35 MB of Trans log create
> a dump file 648 MB in size?
> Can anyone explain this?
>|||I have looked at BOL on this and have done some testing. My bosses ask 'IS
DBCC INDEXDEFRAG safe and effective? How can you prove it?' Effective I can
prove. Safe I say I just have to trust MS. I suppose you recommend it? That
would help. Also DBREINDEX is easier to use, simple cursor to supply table
names. Do you have a script that will supply the necessary variables for
INDEXDEFRAG. I have a db with 1200+ tables that have > 1 row and I don't
think I want to do _WA_% or hind% indexes. I haven't found the correct
values to exclude these indexes from queries with out doing NOT LIKE .
Also. In response to Geoff's answer. How and where does SQL server save the
necessary info to use to fill in the info when it backs up everything to
disk?
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u%23nTsZM5DHA.1504@.TK2MSFTNGP12.phx.gbl...
> Geoff explained the reason for the behavior. I just want to add that an
option can be to do DBCC
> INDEXDEFRAG instead of DBREINDEX. And then do REINDEX at a less frequent
interval (if at all).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Dean Nicholson" <deann@.dtn.com> wrote in message
news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
> > I am trying to determine how to create smaller log backups during
indexing.
> > I am using SQL2K SP3 and currently reindex half of my 60GB each week
> > producing 30 GB of log file dumps. Readings from various sources suggest
> > that changing the db's recovery to BULK_LOGGED from FULL while
reindexing
> > results in reduced logging. After changing to BULK_LOGGED while
indexing, my
> > dumps are still 30 GB. My initial testing confirmed that the log did not
> > grow as large so I am surprised that the dumpfiles are still just as
large.
> > The backup files are about the same size regardless of recovery setting.
> >
> > For a test I restored my database to a test server and checked to make
sure
> > the recovery mode was still FULL. I then set up a maintenance plan to
dump
> > the log every 10 minutes. I let this run through 2 dumps of the log. I
then
> > did a dbcc dbreindex on a large table. It produced 1330 MB of log in
the
> > transaction log as shown in EM with the taskpad view. When the
maintenance
> > job ran it produced a 1330 MB dumpfile as expected and reduced the
amount of
> > space used in the trans log as shown in EM. I then changed the recovery
mode
> > to BULK_LOGGED and allowed the maintenance job to run several times. I
then
> > did dbcc reindex on a different large table. The transaction log grew
only a
> > small amount but when the log dumped it created a 675 MB file! Not
believing
> > this I detached the db and deleted the old tran log. I then reattached
the
> > db with a new, clean log of 504 KB. I allowed the log to dump creating
a 79
> > KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
> > large table. It grew the transaction log to 35 MB of which only 17.7 MB
was
> > used (The log was set to grow by 10%). Why was only 17.7 MB used but the
log
> > grew to 35 MB? I would think that it would have had to have at least 32
MB
> > used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the
trans
> > log dumped to disk. The file was 648 MB. How does 35 MB of Trans log
create
> > a dump file 648 MB in size?
> >
> > Can anyone explain this?
> >
> >
>|||There is a template stored procedure in BOL under 'DBCC SHOWCONTIG' that
will run DBCC INDEXDEFRAG with the correct parameters. You may want to
modify it for your own needs.
SQL gets the data from the data file. You may find that some deletes are
blocked until you do a log backup once you complete a bulk-logged operation.
That is to make sure the data doesn't get lost.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Dean Nicholson" <deann@.dtn.com> wrote in message
news:Ofb5woO5DHA.2560@.TK2MSFTNGP09.phx.gbl...
> I have looked at BOL on this and have done some testing. My bosses ask 'IS
> DBCC INDEXDEFRAG safe and effective? How can you prove it?' Effective I
can
> prove. Safe I say I just have to trust MS. I suppose you recommend it?
That
> would help. Also DBREINDEX is easier to use, simple cursor to supply table
> names. Do you have a script that will supply the necessary variables for
> INDEXDEFRAG. I have a db with 1200+ tables that have > 1 row and I don't
> think I want to do _WA_% or hind% indexes. I haven't found the correct
> values to exclude these indexes from queries with out doing NOT LIKE .
> Also. In response to Geoff's answer. How and where does SQL server save
the
> necessary info to use to fill in the info when it backs up everything to
> disk?
> Thanks
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:u%23nTsZM5DHA.1504@.TK2MSFTNGP12.phx.gbl...
> > Geoff explained the reason for the behavior. I just want to add that an
> option can be to do DBCC
> > INDEXDEFRAG instead of DBREINDEX. And then do REINDEX at a less frequent
> interval (if at all).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Dean Nicholson" <deann@.dtn.com> wrote in message
> news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
> > > I am trying to determine how to create smaller log backups during
> indexing.
> > > I am using SQL2K SP3 and currently reindex half of my 60GB each week
> > > producing 30 GB of log file dumps. Readings from various sources
suggest
> > > that changing the db's recovery to BULK_LOGGED from FULL while
> reindexing
> > > results in reduced logging. After changing to BULK_LOGGED while
> indexing, my
> > > dumps are still 30 GB. My initial testing confirmed that the log did
not
> > > grow as large so I am surprised that the dumpfiles are still just as
> large.
> > > The backup files are about the same size regardless of recovery
setting.
> > >
> > > For a test I restored my database to a test server and checked to make
> sure
> > > the recovery mode was still FULL. I then set up a maintenance plan to
> dump
> > > the log every 10 minutes. I let this run through 2 dumps of the log. I
> then
> > > did a dbcc dbreindex on a large table. It produced 1330 MB of log in
> the
> > > transaction log as shown in EM with the taskpad view. When the
> maintenance
> > > job ran it produced a 1330 MB dumpfile as expected and reduced the
> amount of
> > > space used in the trans log as shown in EM. I then changed the
recovery
> mode
> > > to BULK_LOGGED and allowed the maintenance job to run several times. I
> then
> > > did dbcc reindex on a different large table. The transaction log grew
> only a
> > > small amount but when the log dumped it created a 675 MB file! Not
> believing
> > > this I detached the db and deleted the old tran log. I then reattached
> the
> > > db with a new, clean log of 504 KB. I allowed the log to dump
creating
> a 79
> > > KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on
another
> > > large table. It grew the transaction log to 35 MB of which only 17.7
MB
> was
> > > used (The log was set to grow by 10%). Why was only 17.7 MB used but
the
> log
> > > grew to 35 MB? I would think that it would have had to have at least
32
> MB
> > > used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the
> trans
> > > log dumped to disk. The file was 648 MB. How does 35 MB of Trans log
> create
> > > a dump file 648 MB in size?
> > >
> > > Can anyone explain this?
> > >
> > >
> >
> >
>|||On our internal SQL Server, I switched from DBREINDEX to INDEXDEFRAG about 6-9 month ago. I've had
not problems whatsoever. However, our databases are small (up to 2GB data) and we didn't really
suffer from performance issues in the first place.
I haven't read anywhere of any problems "safe-wise" with INDEXDEFRAG.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dean Nicholson" <deann@.dtn.com> wrote in message news:Ofb5woO5DHA.2560@.TK2MSFTNGP09.phx.gbl...
> I have looked at BOL on this and have done some testing. My bosses ask 'IS
> DBCC INDEXDEFRAG safe and effective? How can you prove it?' Effective I can
> prove. Safe I say I just have to trust MS. I suppose you recommend it? That
> would help. Also DBREINDEX is easier to use, simple cursor to supply table
> names. Do you have a script that will supply the necessary variables for
> INDEXDEFRAG. I have a db with 1200+ tables that have > 1 row and I don't
> think I want to do _WA_% or hind% indexes. I haven't found the correct
> values to exclude these indexes from queries with out doing NOT LIKE .
> Also. In response to Geoff's answer. How and where does SQL server save the
> necessary info to use to fill in the info when it backs up everything to
> disk?
> Thanks
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u%23nTsZM5DHA.1504@.TK2MSFTNGP12.phx.gbl...
> > Geoff explained the reason for the behavior. I just want to add that an
> option can be to do DBCC
> > INDEXDEFRAG instead of DBREINDEX. And then do REINDEX at a less frequent
> interval (if at all).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Dean Nicholson" <deann@.dtn.com> wrote in message
> news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
> > > I am trying to determine how to create smaller log backups during
> indexing.
> > > I am using SQL2K SP3 and currently reindex half of my 60GB each week
> > > producing 30 GB of log file dumps. Readings from various sources suggest
> > > that changing the db's recovery to BULK_LOGGED from FULL while
> reindexing
> > > results in reduced logging. After changing to BULK_LOGGED while
> indexing, my
> > > dumps are still 30 GB. My initial testing confirmed that the log did not
> > > grow as large so I am surprised that the dumpfiles are still just as
> large.
> > > The backup files are about the same size regardless of recovery setting.
> > >
> > > For a test I restored my database to a test server and checked to make
> sure
> > > the recovery mode was still FULL. I then set up a maintenance plan to
> dump
> > > the log every 10 minutes. I let this run through 2 dumps of the log. I
> then
> > > did a dbcc dbreindex on a large table. It produced 1330 MB of log in
> the
> > > transaction log as shown in EM with the taskpad view. When the
> maintenance
> > > job ran it produced a 1330 MB dumpfile as expected and reduced the
> amount of
> > > space used in the trans log as shown in EM. I then changed the recovery
> mode
> > > to BULK_LOGGED and allowed the maintenance job to run several times. I
> then
> > > did dbcc reindex on a different large table. The transaction log grew
> only a
> > > small amount but when the log dumped it created a 675 MB file! Not
> believing
> > > this I detached the db and deleted the old tran log. I then reattached
> the
> > > db with a new, clean log of 504 KB. I allowed the log to dump creating
> a 79
> > > KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
> > > large table. It grew the transaction log to 35 MB of which only 17.7 MB
> was
> > > used (The log was set to grow by 10%). Why was only 17.7 MB used but the
> log
> > > grew to 35 MB? I would think that it would have had to have at least 32
> MB
> > > used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the
> trans
> > > log dumped to disk. The file was 648 MB. How does 35 MB of Trans log
> create
> > > a dump file 648 MB in size?
> > >
> > > Can anyone explain this?
> > >
> > >
> >
> >
>sql
Subscribe to:
Posts (Atom)