Record Locks: sp3

Showing posts with label sp3. Show all posts

Friday, March 30, 2012

Recovery question

Hello everybody,
I'd like to get some help about Recovery model.
My scenario:
MS SQL Server 2K SP3
Backup with C.A. Brighstor Enterprise Backup
If i choose the simple recovery model this will impact me if i use a 3rd
Paty backup tool?
Thanks
Leandro Loureiro dos Santos
Vitoria/ES
BrazilDepends on what the 3:rd party backup tool does. Use simple recovery model
if you don't do transaction log backups. If you do transaction log backups,
use full recovery model.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:%232M0lqK8DHA.2540@.TK2MSFTNGP11.phx.gbl...
> Hello everybody,
> I'd like to get some help about Recovery model.
> My scenario:
> MS SQL Server 2K SP3
> Backup with C.A. Brighstor Enterprise Backup
> If i choose the simple recovery model this will impact me if i use a 3rd
> Paty backup tool?
> Thanks
> Leandro Loureiro dos Santos
> Vitoria/ES
> Brazil
>
>

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

Recovery options when the transaction log is unavailable

Recovery of database 'abcdef'...

Hi Everybody,
We have an web-based application which connects to MSSQL Database 2000 + SP3.
Few ASP jobs failed says "timedout error" while connecting to one of database 'abcdef'. The other similar jobs connects to other databases are running fine. The application is an Web-based Application. The Database Server is MSSQL 2000+SP3+Standard Editi
on. Replication has been configured from this server to the other similar database server. Type of Replication is Merge. This DB Server is having around 24 Databases.
The database size of 'abcdef' is 1200 MB (Data 850 MB, Log 400 MB). DBCC & Re-index Jobs runs for this DB Server every weekend.
The OS for this DB server is Windows advanced Server + 4CPU + 2 GB Memory. It's a dedicated MSSQL Server.
We thought it's a resouce constraint, so we rebooted the Server. The Job was initiated, it ran well without any problem.
After the reboot, I checked the MSSQL ErrorLog, the following informational messgs. been registered.
************************************************** *****************************
2004-04-28 15:28:57.85 spid21 Recovery of database 'abcdef' (18) is 0% complete (approximately 154 more seconds) (Phase 2 of 3).
2004-04-28 15:28:58.02 spid21 Recovery of database 'abcdef' (18) is 3% complete (approximately 55 more seconds) (Phase 2 of 3).
2004-04-28 15:28:58.24 spid21 Recovery of database 'abcdef'(18) is 11% complete (approximately 23 more seconds) (Phase 2 of 3).
2004-04-28 15:28:59.90 spid21 Recovery of database 'abcdef' (18) is 30% complete (approximately 16 more seconds) (Phase 2 of 3).
2004-04-28 15:29:01.14 spid55 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'.
2004-04-28 15:29:01.45 spid21 Recovery of database 'abcdef' (18) is 74% complete (approximately 5 more seconds) (Phase 2 of 3).
2004-04-28 15:29:06.01 spid21 Recovery of database 'abcdef' (18) is 97% complete (approximately 0 more seconds) (Phase 2 of 3).
2004-04-28 15:29:06.41 spid21 Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more seconds) (Phase 2 of 3).
2004-04-28 15:29:06.46 spid21 Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more seconds) (Phase 3 of 3).
2004-04-28 15:29:06.79 spid21 Recovery of database 'abcdef' (18) is 100% complete (approximately 0 more seconds) (Phase 3 of 3).
2004-04-28 15:29:06.79 spid21 1 transactions rolled back in database 'abcdef' (18).
2004-04-28 15:29:06.80 spid21 Recovery is checkpointing database 'abcdef'(18)
************************************************** *****************************
The above mesgs didn't come for other databases. Can anybody tell why it displayed like this for database 'abcdef' and what it means,
"
Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more seconds) (Phase 2 of 3).
".
Is that database is in Corruption stage...
tks in advance,
vasum
I've seen this when a user killed the SQL Server service after attempting to
kill a transaction and giving up on the rollback operation.
When the service comes back up, it needs to do a lot of repair.
I suppose this could also happen if you trip on the power card, or something
like that.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:85584AE2-2D6B-4976-B32D-1ACA7B4F1915@.microsoft.com...
> Hi Everybody,
> We have an web-based application which connects to MSSQL Database 2000 +
> SP3.
> Few ASP jobs failed says "timedout error" while connecting to one of
> database 'abcdef'. The other similar jobs connects to other databases are
> running fine. The application is an Web-based Application. The Database
> Server is MSSQL 2000+SP3+Standard Edition. Replication has been configured
> from this server to the other similar database server. Type of Replication
> is Merge. This DB Server is having around 24 Databases.
> The database size of 'abcdef' is 1200 MB (Data 850 MB, Log 400 MB). DBCC &
> Re-index Jobs runs for this DB Server every weekend.
> The OS for this DB server is Windows advanced Server + 4CPU + 2 GB Memory.
> It's a dedicated MSSQL Server.
> We thought it's a resouce constraint, so we rebooted the Server. The Job
> was initiated, it ran well without any problem.
> After the reboot, I checked the MSSQL ErrorLog, the following
> informational messgs. been registered.
> ************************************************** *****************************
> 2004-04-28 15:28:57.85 spid21 Recovery of database 'abcdef' (18) is 0%
> complete (approximately 154 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:58.02 spid21 Recovery of database 'abcdef' (18) is 3%
> complete (approximately 55 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:58.24 spid21 Recovery of database 'abcdef'(18) is 11%
> complete (approximately 23 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:59.90 spid21 Recovery of database 'abcdef' (18) is 30%
> complete (approximately 16 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:01.14 spid55 Using 'xpstar.dll' version '2000.80.760'
> to execute extended stored procedure 'sp_MSgetversion'.
> 2004-04-28 15:29:01.45 spid21 Recovery of database 'abcdef' (18) is 74%
> complete (approximately 5 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.01 spid21 Recovery of database 'abcdef' (18) is 97%
> complete (approximately 0 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.41 spid21 Recovery of database 'abcdef' (18) is 99%
> complete (approximately 0 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.46 spid21 Recovery of database 'abcdef' (18) is 99%
> complete (approximately 0 more seconds) (Phase 3 of 3).
> 2004-04-28 15:29:06.79 spid21 Recovery of database 'abcdef' (18) is
> 100% complete (approximately 0 more seconds) (Phase 3 of 3).
> 2004-04-28 15:29:06.79 spid21 1 transactions rolled back in database
> 'abcdef' (18).
> 2004-04-28 15:29:06.80 spid21 Recovery is checkpointing database
> 'abcdef'(18)
> ************************************************** *****************************
> The above mesgs didn't come for other databases. Can anybody tell why it
> displayed like this for database 'abcdef' and what it means,
> "
> Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more
> seconds) (Phase 2 of 3).
> ".
> Is that database is in Corruption stage...
> tks in advance,
> vasum
|||Aaron is correct. These entries are from your database going through
transaction recovery. SQL has to walk through the transaction log from the
last checkpoint and roll transactions either forward or back depending on
their commit status. Some databases have very recent checkpoints or no long
running transactions so they recover very quickly. SQL will give progress
reports on recovery when it thinks the recovery will take a while. The
long-running transaction probably was the original cause of your timeout
errors. You can use DBCC OPENTRAN to determine if there is a long-running
transaction in a particular database. I suspect this is related to your
merge replication, but that is just a guess.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:85584AE2-2D6B-4976-B32D-1ACA7B4F1915@.microsoft.com...
> Hi Everybody,
> We have an web-based application which connects to MSSQL Database 2000 +
SP3.
> Few ASP jobs failed says "timedout error" while connecting to one of
database 'abcdef'. The other similar jobs connects to other databases are
running fine. The application is an Web-based Application. The Database
Server is MSSQL 2000+SP3+Standard Edition. Replication has been configured
from this server to the other similar database server. Type of Replication
is Merge. This DB Server is having around 24 Databases.
> The database size of 'abcdef' is 1200 MB (Data 850 MB, Log 400 MB). DBCC &
Re-index Jobs runs for this DB Server every weekend.
> The OS for this DB server is Windows advanced Server + 4CPU + 2 GB Memory.
It's a dedicated MSSQL Server.
> We thought it's a resouce constraint, so we rebooted the Server. The Job
was initiated, it ran well without any problem.
> After the reboot, I checked the MSSQL ErrorLog, the following
informational messgs. been registered.
>
************************************************** **************************
***
> 2004-04-28 15:28:57.85 spid21 Recovery of database 'abcdef' (18) is 0%
complete (approximately 154 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:58.02 spid21 Recovery of database 'abcdef' (18) is 3%
complete (approximately 55 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:58.24 spid21 Recovery of database 'abcdef'(18) is 11%
complete (approximately 23 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:59.90 spid21 Recovery of database 'abcdef' (18) is 30%
complete (approximately 16 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:01.14 spid55 Using 'xpstar.dll' version '2000.80.760'
to execute extended stored procedure 'sp_MSgetversion'.
> 2004-04-28 15:29:01.45 spid21 Recovery of database 'abcdef' (18) is 74%
complete (approximately 5 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.01 spid21 Recovery of database 'abcdef' (18) is 97%
complete (approximately 0 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.41 spid21 Recovery of database 'abcdef' (18) is 99%
complete (approximately 0 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.46 spid21 Recovery of database 'abcdef' (18) is 99%
complete (approximately 0 more seconds) (Phase 3 of 3).
> 2004-04-28 15:29:06.79 spid21 Recovery of database 'abcdef' (18) is
100% complete (approximately 0 more seconds) (Phase 3 of 3).
> 2004-04-28 15:29:06.79 spid21 1 transactions rolled back in database
'abcdef' (18).
> 2004-04-28 15:29:06.80 spid21 Recovery is checkpointing database
'abcdef'(18)
>
************************************************** **************************
***
> The above mesgs didn't come for other databases. Can anybody tell why it
displayed like this for database 'abcdef' and what it means,
> "
> Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more
seconds) (Phase 2 of 3).
> ".
> Is that database is in Corruption stage...
> tks in advance,
> vasum
|||Thanks for the reply.
In this case, nobody has killed the SQL Server Service or removing the trip on the Power Card. Since it a very critical
server, only few people have access to the Server.
Apart from these problems any other problem which makes these mesg to display in the SQL Server Errorlog.
tks in advance,
vasum
|||If the transaction recovery not happened, will that database would have gone to corrupt state. what other implications expected if transaction recovery not happened.
tks in advance,
vasum
|||vasum
Did you mean that your transaction log file gets corrupted?
Perform RESTORE DATABASE with RECOVERY option
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:A69F9633-7198-4400-97DB-CD0F252E0F34@.microsoft.com...
> If the transaction recovery not happened, will that database would have
gone to corrupt state. what other implications expected if transaction
recovery not happened.
> tks in advance,
> vasum
|||No. I am saying that the 'Transaction log corrupted' in that database.
I am just asking what other problems are expected if the recovery of database not happened and which situtation make
the database server to display this kind of informational mesg.
vasum
|||Well, if your Transaction log corrupted during the recovery process you will
get an error and the database will be displayed in suspect mode. So you will
need to perfom restore database with recovery option or detach and the
reatach your database.
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:85D59D9B-FB3E-4D0B-8C4D-A83EDD6A527C@.microsoft.com...
> No. I am saying that the 'Transaction log corrupted' in that database.
> I am just asking what other problems are expected if the recovery of
database not happened and which situtation make
> the database server to display this kind of informational mesg.
> vasum
|||Recovery is about transactional consistancy. Recovery is NOT about fixing
corruption. SQL runs recovery so that the transactional engine can bring
the database to a transactionally consistant state. Again, every database
is 'recovered' on SQL system startup. Some take very little time, some can
take a long time. I once had an 8 hour transaction crash the server (Older
version) and it took 8 hours to roll it back. Expensive lesson.
SQL will not allow you to access the database until the recovery process is
completed. If an underlying data file is corrupted, you will need to
restore according to your recovery plan. Exactly how you do this and how
much data is lost depends on your choice of recovery model and your backup
procedures.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:A69F9633-7198-4400-97DB-CD0F252E0F34@.microsoft.com...
> If the transaction recovery not happened, will that database would have
gone to corrupt state. what other implications expected if transaction
recovery not happened.
> tks in advance,
> vasum
|||So you mean to say it's normal kind of informational mesg. We can ignore this.
Why I raised this in forum is, out of 24 databases, only for this database this kind of informational mesgs. displayed and few jobs which connects to this database were also failed saying "timedout error" but at the time users able to connect to this data
base through isqlw and do queries...I think this could be also the reason, Merge Replication is happening in that database as I said earlier. Before server reboot, we didn't stop the merge agents and we went for reboot. At the time some transaction would
have been published to the subscriber. May be this could also made this type of informational mesgs. to display in the errorlog. But same thing should happen to other databases also, right
"
2004-04-28 15:28:57.85 spid21 Recovery of database 'abcdef' (18) is 0% complete (approximately 154 more seconds) (Phase 2 of 3).
- - -
- - -
2004-04-28 15:29:06.46 spid21 Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more seconds) (Phase 3 of 3).
2004-04-28 15:29:06.79 spid21 Recovery of database 'abcdef' (18) is 100% complete (approximately 0 more seconds) (Phase 3 of 3).
"
Pls. correct me if I am wrong...
vasum

Recovery of database 'abcdef'...

Hi Everybody,
We have an web-based application which connects to MSSQL Database 2000 + SP3
.
Few ASP jobs failed says "timedout error" while connecting to one of databas
e 'abcdef'. The other similar jobs connects to other databases are running
fine. The application is an Web-based Application. The Database Server is MS
SQL 2000+SP3+Standard Editi
on. Replication has been configured from this server to the other similar da
tabase server. Type of Replication is Merge. This DB Server is having around
24 Databases.
The database size of 'abcdef' is 1200 MB (Data 850 MB, Log 400 MB). DBCC & R
e-index Jobs runs for this DB Server every weekend.
The OS for this DB server is Windows advanced Server + 4CPU + 2 GB Memory. I
t's a dedicated MSSQL Server.
We thought it's a resouce constraint, so we rebooted the Server. The Job was
initiated, it ran well without any problem.
After the reboot, I checked the MSSQL ErrorLog, the following informational
messgs. been registered.
****************************************
************************************
***
2004-04-28 15:28:57.85 spid21 Recovery of database 'abcdef' (18) is 0% co
mplete (approximately 154 more seconds) (Phase 2 of 3).
2004-04-28 15:28:58.02 spid21 Recovery of database 'abcdef' (18) is 3% co
mplete (approximately 55 more seconds) (Phase 2 of 3).
2004-04-28 15:28:58.24 spid21 Recovery of database 'abcdef'(18) is 11% co
mplete (approximately 23 more seconds) (Phase 2 of 3).
2004-04-28 15:28:59.90 spid21 Recovery of database 'abcdef' (18) is 30% c
omplete (approximately 16 more seconds) (Phase 2 of 3).
2004-04-28 15:29:01.14 spid55 Using 'xpstar.dll' version '2000.80.760' to
execute extended stored procedure 'sp_MSgetversion'.
2004-04-28 15:29:01.45 spid21 Recovery of database 'abcdef' (18) is 74% c
omplete (approximately 5 more seconds) (Phase 2 of 3).
2004-04-28 15:29:06.01 spid21 Recovery of database 'abcdef' (18) is 97% c
omplete (approximately 0 more seconds) (Phase 2 of 3).
2004-04-28 15:29:06.41 spid21 Recovery of database 'abcdef' (18) is 99% c
omplete (approximately 0 more seconds) (Phase 2 of 3).
2004-04-28 15:29:06.46 spid21 Recovery of database 'abcdef' (18) is 99% c
omplete (approximately 0 more seconds) (Phase 3 of 3).
2004-04-28 15:29:06.79 spid21 Recovery of database 'abcdef' (18) is 100%
complete (approximately 0 more seconds) (Phase 3 of 3).
2004-04-28 15:29:06.79 spid21 1 transactions rolled back in database 'abc
def' (18).
2004-04-28 15:29:06.80 spid21 Recovery is checkpointing database 'abcdef'
(18)
****************************************
************************************
***
The above mesgs didn't come for other databases. Can anybody tell why it dis
played like this for database 'abcdef' and what it means,
"
Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more sec
onds) (Phase 2 of 3).
".
Is that database is in Corruption stage...
tks in advance,
vasumI've seen this when a user killed the SQL Server service after attempting to
kill a transaction and giving up on the rollback operation.
When the service comes back up, it needs to do a lot of repair.
I suppose this could also happen if you trip on the power card, or something
like that.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:85584AE2-2D6B-4976-B32D-1ACA7B4F1915@.microsoft.com...
> Hi Everybody,
> We have an web-based application which connects to MSSQL Database 2000 +
> SP3.
> Few ASP jobs failed says "timedout error" while connecting to one of
> database 'abcdef'. The other similar jobs connects to other databases are
> running fine. The application is an Web-based Application. The Database
> Server is MSSQL 2000+SP3+Standard Edition. Replication has been configured
> from this server to the other similar database server. Type of Replication
> is Merge. This DB Server is having around 24 Databases.
> The database size of 'abcdef' is 1200 MB (Data 850 MB, Log 400 MB). DBCC &
> Re-index Jobs runs for this DB Server every weekend.
> The OS for this DB server is Windows advanced Server + 4CPU + 2 GB Memory.
> It's a dedicated MSSQL Server.
> We thought it's a resouce constraint, so we rebooted the Server. The Job
> was initiated, it ran well without any problem.
> After the reboot, I checked the MSSQL ErrorLog, the following
> informational messgs. been registered.
> ****************************************
**********************************
*****
> 2004-04-28 15:28:57.85 spid21 Recovery of database 'abcdef' (18) is 0%
> complete (approximately 154 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:58.02 spid21 Recovery of database 'abcdef' (18) is 3%
> complete (approximately 55 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:58.24 spid21 Recovery of database 'abcdef'(18) is 11%
> complete (approximately 23 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:59.90 spid21 Recovery of database 'abcdef' (18) is 30%
> complete (approximately 16 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:01.14 spid55 Using 'xpstar.dll' version '2000.80.760'
> to execute extended stored procedure 'sp_MSgetversion'.
> 2004-04-28 15:29:01.45 spid21 Recovery of database 'abcdef' (18) is 74%
> complete (approximately 5 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.01 spid21 Recovery of database 'abcdef' (18) is 97%
> complete (approximately 0 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.41 spid21 Recovery of database 'abcdef' (18) is 99%
> complete (approximately 0 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.46 spid21 Recovery of database 'abcdef' (18) is 99%
> complete (approximately 0 more seconds) (Phase 3 of 3).
> 2004-04-28 15:29:06.79 spid21 Recovery of database 'abcdef' (18) is
> 100% complete (approximately 0 more seconds) (Phase 3 of 3).
> 2004-04-28 15:29:06.79 spid21 1 transactions rolled back in database
> 'abcdef' (18).
> 2004-04-28 15:29:06.80 spid21 Recovery is checkpointing database
> 'abcdef'(18)
> ****************************************
**********************************
*****
> The above mesgs didn't come for other databases. Can anybody tell why it
> displayed like this for database 'abcdef' and what it means,
> "
> Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more
> seconds) (Phase 2 of 3).
> ".
> Is that database is in Corruption stage...
> tks in advance,
> vasum|||Aaron is correct. These entries are from your database going through
transaction recovery. SQL has to walk through the transaction log from the
last checkpoint and roll transactions either forward or back depending on
their commit status. Some databases have very recent checkpoints or no long
running transactions so they recover very quickly. SQL will give progress
reports on recovery when it thinks the recovery will take a while. The
long-running transaction probably was the original cause of your timeout
errors. You can use DBCC OPENTRAN to determine if there is a long-running
transaction in a particular database. I suspect this is related to your
merge replication, but that is just a guess.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:85584AE2-2D6B-4976-B32D-1ACA7B4F1915@.microsoft.com...
> Hi Everybody,
> We have an web-based application which connects to MSSQL Database 2000 +
SP3.
> Few ASP jobs failed says "timedout error" while connecting to one of
database 'abcdef'. The other similar jobs connects to other databases are
running fine. The application is an Web-based Application. The Database
Server is MSSQL 2000+SP3+Standard Edition. Replication has been configured
from this server to the other similar database server. Type of Replication
is Merge. This DB Server is having around 24 Databases.
> The database size of 'abcdef' is 1200 MB (Data 850 MB, Log 400 MB). DBCC &
Re-index Jobs runs for this DB Server every weekend.
> The OS for this DB server is Windows advanced Server + 4CPU + 2 GB Memory.
It's a dedicated MSSQL Server.
> We thought it's a resouce constraint, so we rebooted the Server. The Job
was initiated, it ran well without any problem.
> After the reboot, I checked the MSSQL ErrorLog, the following
informational messgs. been registered.
>
****************************************
************************************
***
> 2004-04-28 15:28:57.85 spid21 Recovery of database 'abcdef' (18) is 0%
complete (approximately 154 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:58.02 spid21 Recovery of database 'abcdef' (18) is 3%
complete (approximately 55 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:58.24 spid21 Recovery of database 'abcdef'(18) is 11%
complete (approximately 23 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:59.90 spid21 Recovery of database 'abcdef' (18) is 30%
complete (approximately 16 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:01.14 spid55 Using 'xpstar.dll' version '2000.80.760'
to execute extended stored procedure 'sp_MSgetversion'.
> 2004-04-28 15:29:01.45 spid21 Recovery of database 'abcdef' (18) is 74%
complete (approximately 5 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.01 spid21 Recovery of database 'abcdef' (18) is 97%
complete (approximately 0 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.41 spid21 Recovery of database 'abcdef' (18) is 99%
complete (approximately 0 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.46 spid21 Recovery of database 'abcdef' (18) is 99%
complete (approximately 0 more seconds) (Phase 3 of 3).
> 2004-04-28 15:29:06.79 spid21 Recovery of database 'abcdef' (18) is
100% complete (approximately 0 more seconds) (Phase 3 of 3).
> 2004-04-28 15:29:06.79 spid21 1 transactions rolled back in database
'abcdef' (18).
> 2004-04-28 15:29:06.80 spid21 Recovery is checkpointing database
'abcdef'(18)
>
****************************************
************************************
***
> The above mesgs didn't come for other databases. Can anybody tell why it
displayed like this for database 'abcdef' and what it means,
> "
> Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more
seconds) (Phase 2 of 3).
> ".
> Is that database is in Corruption stage...
> tks in advance,
> vasum|||Thanks for the reply.
In this case, nobody has killed the SQL Server Service or removing the trip
on the Power Card. Since it a very critical
server, only few people have access to the Server.
Apart from these problems any other problem which makes these mesg to displa
y in the SQL Server Errorlog.
tks in advance,
vasum|||If the transaction recovery not happened, will that database would have gone
to corrupt state. what other implications expected if transaction recovery
not happened.
tks in advance,
vasum|||vasum
Did you mean that your transaction log file gets corrupted?
Perform RESTORE DATABASE with RECOVERY option
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:A69F9633-7198-4400-97DB-CD0F252E0F34@.microsoft.com...
> If the transaction recovery not happened, will that database would have
gone to corrupt state. what other implications expected if transaction
recovery not happened.
> tks in advance,
> vasum|||No. I am saying that the 'Transaction log corrupted' in that database.
I am just asking what other problems are expected if the recovery of databas
e not happened and which situtation make
the database server to display this kind of informational mesg.
vasum|||Well, if your Transaction log corrupted during the recovery process you will
get an error and the database will be displayed in suspect mode. So you will
need to perfom restore database with recovery option or detach and the
reatach your database.
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:85D59D9B-FB3E-4D0B-8C4D-A83EDD6A527C@.microsoft.com...
> No. I am saying that the 'Transaction log corrupted' in that database.
> I am just asking what other problems are expected if the recovery of
database not happened and which situtation make
> the database server to display this kind of informational mesg.
> vasum|||Recovery is about transactional consistancy. Recovery is NOT about fixing
corruption. SQL runs recovery so that the transactional engine can bring
the database to a transactionally consistant state. Again, every database
is 'recovered' on SQL system startup. Some take very little time, some can
take a long time. I once had an 8 hour transaction crash the server (Older
version) and it took 8 hours to roll it back. Expensive lesson.
SQL will not allow you to access the database until the recovery process is
completed. If an underlying data file is corrupted, you will need to
restore according to your recovery plan. Exactly how you do this and how
much data is lost depends on your choice of recovery model and your backup
procedures.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:A69F9633-7198-4400-97DB-CD0F252E0F34@.microsoft.com...
> If the transaction recovery not happened, will that database would have
gone to corrupt state. what other implications expected if transaction
recovery not happened.
> tks in advance,
> vasum|||So you mean to say it's normal kind of informational mesg. We can ignore thi
s.
Why I raised this in forum is, out of 24 databases, only for this database t
his kind of informational mesgs. displayed and few jobs which connects to th
is database were also failed saying "timedout error" but at the time users a
ble to connect to this data
base through isqlw and do queries...I think this could be also the reason,
Merge Replication is happening in that database as I said earlier. Before se
rver reboot, we didn't stop the merge agents and we went for reboot. At the
time some transaction would
have been published to the subscriber. May be this could also made this type
of informational mesgs. to display in the errorlog. But same thing should h
appen to other databases also, right
"
2004-04-28 15:28:57.85 spid21 Recovery of database 'abcdef' (18) is 0% co
mplete (approximately 154 more seconds) (Phase 2 of 3).
- - -
- - -
2004-04-28 15:29:06.46 spid21 Recovery of database 'abcdef' (18) is 99% c
omplete (approximately 0 more seconds) (Phase 3 of 3).
2004-04-28 15:29:06.79 spid21 Recovery of database 'abcdef' (18) is 100%
complete (approximately 0 more seconds) (Phase 3 of 3).
"
Pls. correct me if I am wrong...
vasum

Recovery of database 'abcdef'...

Hi Everybody
We have an web-based application which connects to MSSQL Database 2000 + SP3
Few ASP jobs failed says "timedout error" while connecting to one of database 'abcdef'. The other similar jobs connects to other databases are running fine. The application is an Web-based Application. The Database Server is MSSQL 2000+SP3+Standard Edition. Replication has been configured from this server to the other similar database server. Type of Replication is Merge. This DB Server is having around 24 Databases.
The database size of 'abcdef' is 1200 MB (Data 850 MB, Log 400 MB). DBCC & Re-index Jobs runs for this DB Server every weekend
The OS for this DB server is Windows advanced Server + 4CPU + 2 GB Memory. It's a dedicated MSSQL Server
We thought it's a resouce constraint, so we rebooted the Server. The Job was initiated, it ran well without any problem
After the reboot, I checked the MSSQL ErrorLog, the following informational messgs. been registered.
******************************************************************************
2004-04-28 15:28:57.85 spid21 Recovery of database 'abcdef' (18) is 0% complete (approximately 154 more seconds) (Phase 2 of 3)
2004-04-28 15:28:58.02 spid21 Recovery of database 'abcdef' (18) is 3% complete (approximately 55 more seconds) (Phase 2 of 3)
2004-04-28 15:28:58.24 spid21 Recovery of database 'abcdef'(18) is 11% complete (approximately 23 more seconds) (Phase 2 of 3)
2004-04-28 15:28:59.90 spid21 Recovery of database 'abcdef' (18) is 30% complete (approximately 16 more seconds) (Phase 2 of 3)
2004-04-28 15:29:01.14 spid55 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'
2004-04-28 15:29:01.45 spid21 Recovery of database 'abcdef' (18) is 74% complete (approximately 5 more seconds) (Phase 2 of 3)
2004-04-28 15:29:06.01 spid21 Recovery of database 'abcdef' (18) is 97% complete (approximately 0 more seconds) (Phase 2 of 3)
2004-04-28 15:29:06.41 spid21 Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more seconds) (Phase 2 of 3)
2004-04-28 15:29:06.46 spid21 Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more seconds) (Phase 3 of 3)
2004-04-28 15:29:06.79 spid21 Recovery of database 'abcdef' (18) is 100% complete (approximately 0 more seconds) (Phase 3 of 3)
2004-04-28 15:29:06.79 spid21 1 transactions rolled back in database 'abcdef' (18)
2004-04-28 15:29:06.80 spid21 Recovery is checkpointing database 'abcdef'(18
******************************************************************************
The above mesgs didn't come for other databases. Can anybody tell why it displayed like this for database 'abcdef' and what it means
Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more seconds) (Phase 2 of 3).
"
Is that database is in Corruption stage..
tks in advance
vasumI've seen this when a user killed the SQL Server service after attempting to
kill a transaction and giving up on the rollback operation.
When the service comes back up, it needs to do a lot of repair.
I suppose this could also happen if you trip on the power card, or something
like that.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:85584AE2-2D6B-4976-B32D-1ACA7B4F1915@.microsoft.com...
> Hi Everybody,
> We have an web-based application which connects to MSSQL Database 2000 +
> SP3.
> Few ASP jobs failed says "timedout error" while connecting to one of
> database 'abcdef'. The other similar jobs connects to other databases are
> running fine. The application is an Web-based Application. The Database
> Server is MSSQL 2000+SP3+Standard Edition. Replication has been configured
> from this server to the other similar database server. Type of Replication
> is Merge. This DB Server is having around 24 Databases.
> The database size of 'abcdef' is 1200 MB (Data 850 MB, Log 400 MB). DBCC &
> Re-index Jobs runs for this DB Server every weekend.
> The OS for this DB server is Windows advanced Server + 4CPU + 2 GB Memory.
> It's a dedicated MSSQL Server.
> We thought it's a resouce constraint, so we rebooted the Server. The Job
> was initiated, it ran well without any problem.
> After the reboot, I checked the MSSQL ErrorLog, the following
> informational messgs. been registered.
> *******************************************************************************
> 2004-04-28 15:28:57.85 spid21 Recovery of database 'abcdef' (18) is 0%
> complete (approximately 154 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:58.02 spid21 Recovery of database 'abcdef' (18) is 3%
> complete (approximately 55 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:58.24 spid21 Recovery of database 'abcdef'(18) is 11%
> complete (approximately 23 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:59.90 spid21 Recovery of database 'abcdef' (18) is 30%
> complete (approximately 16 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:01.14 spid55 Using 'xpstar.dll' version '2000.80.760'
> to execute extended stored procedure 'sp_MSgetversion'.
> 2004-04-28 15:29:01.45 spid21 Recovery of database 'abcdef' (18) is 74%
> complete (approximately 5 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.01 spid21 Recovery of database 'abcdef' (18) is 97%
> complete (approximately 0 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.41 spid21 Recovery of database 'abcdef' (18) is 99%
> complete (approximately 0 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.46 spid21 Recovery of database 'abcdef' (18) is 99%
> complete (approximately 0 more seconds) (Phase 3 of 3).
> 2004-04-28 15:29:06.79 spid21 Recovery of database 'abcdef' (18) is
> 100% complete (approximately 0 more seconds) (Phase 3 of 3).
> 2004-04-28 15:29:06.79 spid21 1 transactions rolled back in database
> 'abcdef' (18).
> 2004-04-28 15:29:06.80 spid21 Recovery is checkpointing database
> 'abcdef'(18)
> *******************************************************************************
> The above mesgs didn't come for other databases. Can anybody tell why it
> displayed like this for database 'abcdef' and what it means,
> "
> Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more
> seconds) (Phase 2 of 3).
> ".
> Is that database is in Corruption stage...
> tks in advance,
> vasum|||Aaron is correct. These entries are from your database going through
transaction recovery. SQL has to walk through the transaction log from the
last checkpoint and roll transactions either forward or back depending on
their commit status. Some databases have very recent checkpoints or no long
running transactions so they recover very quickly. SQL will give progress
reports on recovery when it thinks the recovery will take a while. The
long-running transaction probably was the original cause of your timeout
errors. You can use DBCC OPENTRAN to determine if there is a long-running
transaction in a particular database. I suspect this is related to your
merge replication, but that is just a guess.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:85584AE2-2D6B-4976-B32D-1ACA7B4F1915@.microsoft.com...
> Hi Everybody,
> We have an web-based application which connects to MSSQL Database 2000 +
SP3.
> Few ASP jobs failed says "timedout error" while connecting to one of
database 'abcdef'. The other similar jobs connects to other databases are
running fine. The application is an Web-based Application. The Database
Server is MSSQL 2000+SP3+Standard Edition. Replication has been configured
from this server to the other similar database server. Type of Replication
is Merge. This DB Server is having around 24 Databases.
> The database size of 'abcdef' is 1200 MB (Data 850 MB, Log 400 MB). DBCC &
Re-index Jobs runs for this DB Server every weekend.
> The OS for this DB server is Windows advanced Server + 4CPU + 2 GB Memory.
It's a dedicated MSSQL Server.
> We thought it's a resouce constraint, so we rebooted the Server. The Job
was initiated, it ran well without any problem.
> After the reboot, I checked the MSSQL ErrorLog, the following
informational messgs. been registered.
>
****************************************************************************
***
> 2004-04-28 15:28:57.85 spid21 Recovery of database 'abcdef' (18) is 0%
complete (approximately 154 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:58.02 spid21 Recovery of database 'abcdef' (18) is 3%
complete (approximately 55 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:58.24 spid21 Recovery of database 'abcdef'(18) is 11%
complete (approximately 23 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:59.90 spid21 Recovery of database 'abcdef' (18) is 30%
complete (approximately 16 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:01.14 spid55 Using 'xpstar.dll' version '2000.80.760'
to execute extended stored procedure 'sp_MSgetversion'.
> 2004-04-28 15:29:01.45 spid21 Recovery of database 'abcdef' (18) is 74%
complete (approximately 5 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.01 spid21 Recovery of database 'abcdef' (18) is 97%
complete (approximately 0 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.41 spid21 Recovery of database 'abcdef' (18) is 99%
complete (approximately 0 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.46 spid21 Recovery of database 'abcdef' (18) is 99%
complete (approximately 0 more seconds) (Phase 3 of 3).
> 2004-04-28 15:29:06.79 spid21 Recovery of database 'abcdef' (18) is
100% complete (approximately 0 more seconds) (Phase 3 of 3).
> 2004-04-28 15:29:06.79 spid21 1 transactions rolled back in database
'abcdef' (18).
> 2004-04-28 15:29:06.80 spid21 Recovery is checkpointing database
'abcdef'(18)
>
****************************************************************************
***
> The above mesgs didn't come for other databases. Can anybody tell why it
displayed like this for database 'abcdef' and what it means,
> "
> Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more
seconds) (Phase 2 of 3).
> ".
> Is that database is in Corruption stage...
> tks in advance,
> vasum|||Thanks for the reply
In this case, nobody has killed the SQL Server Service or removing the trip on the Power Card. Since it a very critica
server, only few people have access to the Server
Apart from these problems any other problem which makes these mesg to display in the SQL Server Errorlog
tks in advance
vasum|||If the transaction recovery not happened, will that database would have gone to corrupt state. what other implications expected if transaction recovery not happened
tks in advance
vasum|||vasum
Did you mean that your transaction log file gets corrupted?
Perform RESTORE DATABASE with RECOVERY option
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:A69F9633-7198-4400-97DB-CD0F252E0F34@.microsoft.com...
> If the transaction recovery not happened, will that database would have
gone to corrupt state. what other implications expected if transaction
recovery not happened.
> tks in advance,
> vasum|||No. I am saying that the 'Transaction log corrupted' in that database
I am just asking what other problems are expected if the recovery of database not happened and which situtation mak
the database server to display this kind of informational mesg
vasum|||Well, if your Transaction log corrupted during the recovery process you will
get an error and the database will be displayed in suspect mode. So you will
need to perfom restore database with recovery option or detach and the
reatach your database.
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:85D59D9B-FB3E-4D0B-8C4D-A83EDD6A527C@.microsoft.com...
> No. I am saying that the 'Transaction log corrupted' in that database.
> I am just asking what other problems are expected if the recovery of
database not happened and which situtation make
> the database server to display this kind of informational mesg.
> vasum|||Recovery is about transactional consistancy. Recovery is NOT about fixing
corruption. SQL runs recovery so that the transactional engine can bring
the database to a transactionally consistant state. Again, every database
is 'recovered' on SQL system startup. Some take very little time, some can
take a long time. I once had an 8 hour transaction crash the server (Older
version) and it took 8 hours to roll it back. Expensive lesson.
SQL will not allow you to access the database until the recovery process is
completed. If an underlying data file is corrupted, you will need to
restore according to your recovery plan. Exactly how you do this and how
much data is lost depends on your choice of recovery model and your backup
procedures.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:A69F9633-7198-4400-97DB-CD0F252E0F34@.microsoft.com...
> If the transaction recovery not happened, will that database would have
gone to corrupt state. what other implications expected if transaction
recovery not happened.
> tks in advance,
> vasum|||So you mean to say it's normal kind of informational mesg. We can ignore this
Why I raised this in forum is, out of 24 databases, only for this database this kind of informational mesgs. displayed and few jobs which connects to this database were also failed saying "timedout error" but at the time users able to connect to this database through isqlw and do queries...I think this could be also the reason, Merge Replication is happening in that database as I said earlier. Before server reboot, we didn't stop the merge agents and we went for reboot. At the time some transaction would have been published to the subscriber. May be this could also made this type of informational mesgs. to display in the errorlog. But same thing should happen to other databases also, right
2004-04-28 15:28:57.85 spid21 Recovery of database 'abcdef' (18) is 0% complete (approximately 154 more seconds) (Phase 2 of 3)
- - -
- -
2004-04-28 15:29:06.46 spid21 Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more seconds) (Phase 3 of 3)
2004-04-28 15:29:06.79 spid21 Recovery of database 'abcdef' (18) is 100% complete (approximately 0 more seconds) (Phase 3 of 3)
Pls. correct me if I am wrong...
vasum|||It depends.
Recovery is related to the number and size of open transactions and
uncommitted data in the database. If the database was checkpointed
immediately before the shutdown and didn't have any long-running
transactions, then the recovery will be short. SQL can estimate the amount
of work to be done and will update the event log as necessary. Read the
following topic in BOL for the details on how this process really works.
SQL Server Architecture | Relational Database Engine Architecture |
Transactions Architecture | Transaction Recovery.
For some reason, Transaction Recovery isn't in the index so you have to find
this through Contents.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:3AD49B0F-7185-4D3A-B78B-B54CAB861999@.microsoft.com...
> So you mean to say it's normal kind of informational mesg. We can ignore
this.
> Why I raised this in forum is, out of 24 databases, only for this database
this kind of informational mesgs. displayed and few jobs which connects to
this database were also failed saying "timedout error" but at the time users
able to connect to this database through isqlw and do queries...I think
this could be also the reason, Merge Replication is happening in that
database as I said earlier. Before server reboot, we didn't stop the merge
agents and we went for reboot. At the time some transaction would have been
published to the subscriber. May be this could also made this type of
informational mesgs. to display in the errorlog. But same thing should
happen to other databases also, right
> "
> 2004-04-28 15:28:57.85 spid21 Recovery of database 'abcdef' (18) is 0%
complete (approximately 154 more seconds) (Phase 2 of 3).
> - - -
> - - -
> 2004-04-28 15:29:06.46 spid21 Recovery of database 'abcdef' (18) is 99%
complete (approximately 0 more seconds) (Phase 3 of 3).
> 2004-04-28 15:29:06.79 spid21 Recovery of database 'abcdef' (18) is
100% complete (approximately 0 more seconds) (Phase 3 of 3).
> "
> Pls. correct me if I am wrong...
> vasum

Wednesday, March 28, 2012

Recovery model

Hello everybody,
I'd like to get some help about Recovery model.
My scenario:
MS SQL Server 2K SP3
Backup with C.A. Brighstor Enterprise Backup
If i choose the simple recovery model this will impact me if i use a 3rd
Paty backup tool?
Thanks
Leandro Loureiro dos Santos
Vitoria/ES
BrazilLeandro Loureiro dos Santos wrote:
> Hello everybody,
> I'd like to get some help about Recovery model.
> My scenario:
> MS SQL Server 2K SP3
> Backup with C.A. Brighstor Enterprise Backup
> If i choose the simple recovery model this will impact me if i use a
> 3rd Paty backup tool?
> Thanks
> Leandro Loureiro dos Santos
> Vitoria/ES
> Brazil
I cannot speak specifics however the short answer should be yes.
Changing the recovery model is affectively telling SQL to treat the
transaction log differently.
Best stick to Full.
Most people use a third party backup tool to back up the SQL disk
backup devices. This has many benefits and you are relying less on
third party vendor support with regards to integration with another
product (which can also work out cheaper too ;-)
Br,
Mark Broadbent
mcdba , mcse+i
=============sql

Recovery Mode Full vs Bulk_Logged

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,

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

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

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

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:

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

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,

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

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

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

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:

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

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

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

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

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:

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

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

Friday, March 30, 2012

Recovery question

Recovery options when the transaction log is unavailable

Recovery options when the transaction log is unavailable

Recovery options when the transaction log is unavailable

Recovery of database 'abcdef'...

Recovery of database 'abcdef'...

Recovery of database 'abcdef'...

Wednesday, March 28, 2012

Recovery model

Recovery Mode Full vs Bulk_Logged

Recovery Mode Full vs Bulk_Logged

Record Locks

Blog Archive

About Me