I've created an index defrag script, based on a set of criteria it will
choose whether to do a reorganise or rebuild (online or offline) for a
selected index.. ..I've set that when it does a rebuild to set the recovery
model to bulk logged.. ..I set the recovery model just before the rebuild and
revert just after, I've since been thinking if it needs to 100 rebuilds it
will switch the recovery model 100 times for the duration of each rebuild..
...would this be bad practice?
Ben
What is your current recover model? Is it FULL? Do you perform BACKUP LOG
operation?
Have you noticed a big difference in terms of performance if you run it on
FULL or BULK LOGGED recover model?
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:5E7989D2-89AD-4AD1-9753-FB8BCE8BFD5F@.microsoft.com...
> I've created an index defrag script, based on a set of criteria it will
> choose whether to do a reorganise or rebuild (online or offline) for a
> selected index.. ..I've set that when it does a rebuild to set the
> recovery
> model to bulk logged.. ..I set the recovery model just before the rebuild
> and
> revert just after, I've since been thinking if it needs to 100 rebuilds it
> will switch the recovery model 100 times for the duration of each
> rebuild..
> ..would this be bad practice?
|||Current recovery model is Full, yes we do back up the logs.. ..this is to be
used on multiple servers with multiple databases so the frequency of log
backups can very greatly, the indexes this would be rebuilding are
potentially very large so I'm trying to avoid hammering the log in between
the log backups...
"Uri Dimant" wrote:
> Ben
> What is your current recover model? Is it FULL? Do you perform BACKUP LOG
> operation?
> Have you noticed a big difference in terms of performance if you run it on
> FULL or BULK LOGGED recover model?
>
>
> "Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
> news:5E7989D2-89AD-4AD1-9753-FB8BCE8BFD5F@.microsoft.com...
>
>
|||Ben
Well , if you have enuogh free space , I don't think you need to switch
between FULL and BULL LOGGED as well as i have not seem such performance
issues.
However , can you rebuild your indexes at night for example where workload
is low.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:5AF71BFA-7B0A-4692-B227-AB66F44442AE@.microsoft.com...[vbcol=seagreen]
> Current recovery model is Full, yes we do back up the logs.. ..this is to
> be
> used on multiple servers with multiple databases so the frequency of log
> backups can very greatly, the indexes this would be rebuilding are
> potentially very large so I'm trying to avoid hammering the log in between
> the log backups...
> "Uri Dimant" wrote:
|||Ben UK wrote:
> I've created an index defrag script, based on a set of criteria it will
> choose whether to do a reorganise or rebuild (online or offline) for a
> selected index.. ..I've set that when it does a rebuild to set the recovery
> model to bulk logged.. ..I set the recovery model just before the rebuild and
> revert just after, I've since been thinking if it needs to 100 rebuilds it
> will switch the recovery model 100 times for the duration of each rebuild..
> ..would this be bad practice?
I wouldn't say it's "bad practice". I would, however, be concerned
about the possibility of some bulk operation being performed while you
have the recovery model switched. In other words, if you're automating
this switch between Full and Bulk-Logged, how will you control or
prevent some other process from doing something non-logged? A scheduled
maintenance window seems like a much "safer" way of managing reindexes.
Just my 2-cents.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||That makes sense, thanks for the input Tracy
"Tracy McKibben" wrote:
> Ben UK wrote:
> I wouldn't say it's "bad practice". I would, however, be concerned
> about the possibility of some bulk operation being performed while you
> have the recovery model switched. In other words, if you're automating
> this switch between Full and Bulk-Logged, how will you control or
> prevent some other process from doing something non-logged? A scheduled
> maintenance window seems like a much "safer" way of managing reindexes.
> Just my 2-cents.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
Showing posts with label offline. Show all posts
Showing posts with label offline. Show all posts
Wednesday, March 28, 2012
Recovery Model
I've created an index defrag script, based on a set of criteria it will
choose whether to do a reorganise or rebuild (online or offline) for a
selected index.. ..I've set that when it does a rebuild to set the recovery
model to bulk logged.. ..I set the recovery model just before the rebuild an
d
revert just after, I've since been thinking if it needs to 100 rebuilds it
will switch the recovery model 100 times for the duration of each rebuild..
..would this be bad practice?Ben
What is your current recover model? Is it FULL? Do you perform BACKUP LOG
operation?
Have you noticed a big difference in terms of performance if you run it on
FULL or BULK LOGGED recover model?
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:5E7989D2-89AD-4AD1-9753-FB8BCE8BFD5F@.microsoft.com...
> I've created an index defrag script, based on a set of criteria it will
> choose whether to do a reorganise or rebuild (online or offline) for a
> selected index.. ..I've set that when it does a rebuild to set the
> recovery
> model to bulk logged.. ..I set the recovery model just before the rebuild
> and
> revert just after, I've since been thinking if it needs to 100 rebuilds it
> will switch the recovery model 100 times for the duration of each
> rebuild..
> ..would this be bad practice?|||Current recovery model is Full, yes we do back up the logs.. ..this is to be
used on multiple servers with multiple databases so the frequency of log
backups can very greatly, the indexes this would be rebuilding are
potentially very large so I'm trying to avoid hammering the log in between
the log backups...
"Uri Dimant" wrote:
> Ben
> What is your current recover model? Is it FULL? Do you perform BACKUP LOG
> operation?
> Have you noticed a big difference in terms of performance if you run it o
n
> FULL or BULK LOGGED recover model?
>
>
> "Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
> news:5E7989D2-89AD-4AD1-9753-FB8BCE8BFD5F@.microsoft.com...
>
>|||Ben
Well , if you have enuogh free space , I don't think you need to switch
between FULL and BULL LOGGED as well as i have not seem such performance
issues.
However , can you rebuild your indexes at night for example where workload
is low.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:5AF71BFA-7B0A-4692-B227-AB66F44442AE@.microsoft.com...[vbcol=seagreen]
> Current recovery model is Full, yes we do back up the logs.. ..this is to
> be
> used on multiple servers with multiple databases so the frequency of log
> backups can very greatly, the indexes this would be rebuilding are
> potentially very large so I'm trying to avoid hammering the log in between
> the log backups...
> "Uri Dimant" wrote:
>|||Ben UK wrote:
> I've created an index defrag script, based on a set of criteria it will
> choose whether to do a reorganise or rebuild (online or offline) for a
> selected index.. ..I've set that when it does a rebuild to set the recover
y
> model to bulk logged.. ..I set the recovery model just before the rebuild
and
> revert just after, I've since been thinking if it needs to 100 rebuilds it
> will switch the recovery model 100 times for the duration of each rebuild.
.
> ..would this be bad practice?
I wouldn't say it's "bad practice". I would, however, be concerned
about the possibility of some bulk operation being performed while you
have the recovery model switched. In other words, if you're automating
this switch between Full and Bulk-Logged, how will you control or
prevent some other process from doing something non-logged? A scheduled
maintenance window seems like a much "safer" way of managing reindexes.
Just my 2-cents.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||That makes sense, thanks for the input Tracy
"Tracy McKibben" wrote:
> Ben UK wrote:
> I wouldn't say it's "bad practice". I would, however, be concerned
> about the possibility of some bulk operation being performed while you
> have the recovery model switched. In other words, if you're automating
> this switch between Full and Bulk-Logged, how will you control or
> prevent some other process from doing something non-logged? A scheduled
> maintenance window seems like a much "safer" way of managing reindexes.
> Just my 2-cents.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
choose whether to do a reorganise or rebuild (online or offline) for a
selected index.. ..I've set that when it does a rebuild to set the recovery
model to bulk logged.. ..I set the recovery model just before the rebuild an
d
revert just after, I've since been thinking if it needs to 100 rebuilds it
will switch the recovery model 100 times for the duration of each rebuild..
..would this be bad practice?Ben
What is your current recover model? Is it FULL? Do you perform BACKUP LOG
operation?
Have you noticed a big difference in terms of performance if you run it on
FULL or BULK LOGGED recover model?
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:5E7989D2-89AD-4AD1-9753-FB8BCE8BFD5F@.microsoft.com...
> I've created an index defrag script, based on a set of criteria it will
> choose whether to do a reorganise or rebuild (online or offline) for a
> selected index.. ..I've set that when it does a rebuild to set the
> recovery
> model to bulk logged.. ..I set the recovery model just before the rebuild
> and
> revert just after, I've since been thinking if it needs to 100 rebuilds it
> will switch the recovery model 100 times for the duration of each
> rebuild..
> ..would this be bad practice?|||Current recovery model is Full, yes we do back up the logs.. ..this is to be
used on multiple servers with multiple databases so the frequency of log
backups can very greatly, the indexes this would be rebuilding are
potentially very large so I'm trying to avoid hammering the log in between
the log backups...
"Uri Dimant" wrote:
> Ben
> What is your current recover model? Is it FULL? Do you perform BACKUP LOG
> operation?
> Have you noticed a big difference in terms of performance if you run it o
n
> FULL or BULK LOGGED recover model?
>
>
> "Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
> news:5E7989D2-89AD-4AD1-9753-FB8BCE8BFD5F@.microsoft.com...
>
>|||Ben
Well , if you have enuogh free space , I don't think you need to switch
between FULL and BULL LOGGED as well as i have not seem such performance
issues.
However , can you rebuild your indexes at night for example where workload
is low.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:5AF71BFA-7B0A-4692-B227-AB66F44442AE@.microsoft.com...[vbcol=seagreen]
> Current recovery model is Full, yes we do back up the logs.. ..this is to
> be
> used on multiple servers with multiple databases so the frequency of log
> backups can very greatly, the indexes this would be rebuilding are
> potentially very large so I'm trying to avoid hammering the log in between
> the log backups...
> "Uri Dimant" wrote:
>|||Ben UK wrote:
> I've created an index defrag script, based on a set of criteria it will
> choose whether to do a reorganise or rebuild (online or offline) for a
> selected index.. ..I've set that when it does a rebuild to set the recover
y
> model to bulk logged.. ..I set the recovery model just before the rebuild
and
> revert just after, I've since been thinking if it needs to 100 rebuilds it
> will switch the recovery model 100 times for the duration of each rebuild.
.
> ..would this be bad practice?
I wouldn't say it's "bad practice". I would, however, be concerned
about the possibility of some bulk operation being performed while you
have the recovery model switched. In other words, if you're automating
this switch between Full and Bulk-Logged, how will you control or
prevent some other process from doing something non-logged? A scheduled
maintenance window seems like a much "safer" way of managing reindexes.
Just my 2-cents.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||That makes sense, thanks for the input Tracy
"Tracy McKibben" wrote:
> Ben UK wrote:
> I wouldn't say it's "bad practice". I would, however, be concerned
> about the possibility of some bulk operation being performed while you
> have the recovery model switched. In other words, if you're automating
> this switch between Full and Bulk-Logged, how will you control or
> prevent some other process from doing something non-logged? A scheduled
> maintenance window seems like a much "safer" way of managing reindexes.
> Just my 2-cents.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
Friday, March 9, 2012
recover crashed database
our sqlserver has crashed and try to attach mdf file to server but sql server
open this database in read only\suspect\offline\emergency mode.when we
execute "select" query we take
"torn page error".
and i also execute
"DBCC CheckDB
( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
and i take
"Could not run BEGIN TRANSACTION in database 'dbname' because the database
is in bypass recovery mode."
when execute
DBCC ALLOCDB ( 'dbname' )
i take 16 allocation errors on this database
i must take all data from this database how can i recover this database or
take complete datas
Hi
Did not have a last good full backup?
Also take a look at sp_resetstatus system stored procedure in the BOL
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql
> server
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>
|||no i havent.
i execute
sp_resetstatus 'dbname'
and take this message
"Prior to updating sysdatabases entry for database 'ott', mode = 0 and
status = -280 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already
correctly reset. No error and no changes made."
but i take same error when execute
DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )
|||Try this.
http://www.faqs.org/qa/qa-3213.html
AND IN FUTURE TAKE BACKUPS.
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:FBDF1BC8-7451-45A1-B02A-ADD289FDD21B@.microsoft.com...
> no i havent.
> i execute
> sp_resetstatus 'dbname'
> and take this message
> "Prior to updating sysdatabases entry for database 'ott', mode = 0 and
> status = -280 (status suspect_bit = 0).
> No row in sysdatabases was updated because mode and status are already
> correctly reset. No error and no changes made."
> but i take same error when execute
> DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )
|||i done all your writes but dont work yet.database status change to suspect
mode and dont permit to recover database
|||Or this
http://support.microsoft.com/default...b;en-us;165918
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>i done all your writes but dont work yet.database status change to suspect
> mode and dont permit to recover database
|||Sorry that's 6.5
Nik Marshall-Blank MCSD/MCDBA
"Nik Marshall-Blank (delete fcom for my email address)"
<NikMB@.fcomiNodefcom.afcomt> wrote in message
news:zLUZe.131890$in2.119840@.fe04.news.easynews.co m...
> Or this
> http://support.microsoft.com/default...b;en-us;165918
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
> news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>
|||Open a case with Microsoft Support and see if they have any means of salvaging any data. Of,
assuming that you can access the database at all, export all data to a new healthy database (lot of
work, yes). Some thoughts also here: http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql server
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>
open this database in read only\suspect\offline\emergency mode.when we
execute "select" query we take
"torn page error".
and i also execute
"DBCC CheckDB
( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
and i take
"Could not run BEGIN TRANSACTION in database 'dbname' because the database
is in bypass recovery mode."
when execute
DBCC ALLOCDB ( 'dbname' )
i take 16 allocation errors on this database
i must take all data from this database how can i recover this database or
take complete datas
Hi
Did not have a last good full backup?
Also take a look at sp_resetstatus system stored procedure in the BOL
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql
> server
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>
|||no i havent.
i execute
sp_resetstatus 'dbname'
and take this message
"Prior to updating sysdatabases entry for database 'ott', mode = 0 and
status = -280 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already
correctly reset. No error and no changes made."
but i take same error when execute
DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )
|||Try this.
http://www.faqs.org/qa/qa-3213.html
AND IN FUTURE TAKE BACKUPS.
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:FBDF1BC8-7451-45A1-B02A-ADD289FDD21B@.microsoft.com...
> no i havent.
> i execute
> sp_resetstatus 'dbname'
> and take this message
> "Prior to updating sysdatabases entry for database 'ott', mode = 0 and
> status = -280 (status suspect_bit = 0).
> No row in sysdatabases was updated because mode and status are already
> correctly reset. No error and no changes made."
> but i take same error when execute
> DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )
|||i done all your writes but dont work yet.database status change to suspect
mode and dont permit to recover database
|||Or this
http://support.microsoft.com/default...b;en-us;165918
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>i done all your writes but dont work yet.database status change to suspect
> mode and dont permit to recover database
|||Sorry that's 6.5
Nik Marshall-Blank MCSD/MCDBA
"Nik Marshall-Blank (delete fcom for my email address)"
<NikMB@.fcomiNodefcom.afcomt> wrote in message
news:zLUZe.131890$in2.119840@.fe04.news.easynews.co m...
> Or this
> http://support.microsoft.com/default...b;en-us;165918
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
> news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>
|||Open a case with Microsoft Support and see if they have any means of salvaging any data. Of,
assuming that you can access the database at all, export all data to a new healthy database (lot of
work, yes). Some thoughts also here: http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql server
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>
recover crashed database
our sqlserver has crashed and try to attach mdf file to server but sql server
open this database in read only\suspect\offline\emergency mode.when we
execute "select" query we take
"torn page error".
and i also execute
"DBCC CheckDB
( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
and i take
"Could not run BEGIN TRANSACTION in database 'dbname' because the database
is in bypass recovery mode."
when execute
DBCC ALLOCDB ( 'dbname' )
i take 16 allocation errors on this database
i must take all data from this database how can i recover this database or
take complete datasHi
Did not have a last good full backup?
Also take a look at sp_resetstatus system stored procedure in the BOL
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql
> server
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>|||no i havent.
i execute
sp_resetstatus 'dbname'
and take this message
"Prior to updating sysdatabases entry for database 'ott', mode = 0 and
status = -280 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already
correctly reset. No error and no changes made."
but i take same error when execute
DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )|||Try this.
http://www.faqs.org/qa/qa-3213.html
AND IN FUTURE TAKE BACKUPS.
--
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:FBDF1BC8-7451-45A1-B02A-ADD289FDD21B@.microsoft.com...
> no i havent.
> i execute
> sp_resetstatus 'dbname'
> and take this message
> "Prior to updating sysdatabases entry for database 'ott', mode = 0 and
> status = -280 (status suspect_bit = 0).
> No row in sysdatabases was updated because mode and status are already
> correctly reset. No error and no changes made."
> but i take same error when execute
> DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )|||i done all your writes but dont work yet.database status change to suspect
mode and dont permit to recover database|||Or this
http://support.microsoft.com/default.aspx?scid=kb;en-us;165918
--
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>i done all your writes but dont work yet.database status change to suspect
> mode and dont permit to recover database|||Sorry that's 6.5
--
Nik Marshall-Blank MCSD/MCDBA
"Nik Marshall-Blank (delete fcom for my email address)"
<NikMB@.fcomiNodefcom.afcomt> wrote in message
news:zLUZe.131890$in2.119840@.fe04.news.easynews.com...
> Or this
> http://support.microsoft.com/default.aspx?scid=kb;en-us;165918
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
> news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>>i done all your writes but dont work yet.database status change to suspect
>> mode and dont permit to recover database
>|||Open a case with Microsoft Support and see if they have any means of salvaging any data. Of,
assuming that you can access the database at all, export all data to a new healthy database (lot of
work, yes). Some thoughts also here: http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql server
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>
open this database in read only\suspect\offline\emergency mode.when we
execute "select" query we take
"torn page error".
and i also execute
"DBCC CheckDB
( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
and i take
"Could not run BEGIN TRANSACTION in database 'dbname' because the database
is in bypass recovery mode."
when execute
DBCC ALLOCDB ( 'dbname' )
i take 16 allocation errors on this database
i must take all data from this database how can i recover this database or
take complete datasHi
Did not have a last good full backup?
Also take a look at sp_resetstatus system stored procedure in the BOL
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql
> server
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>|||no i havent.
i execute
sp_resetstatus 'dbname'
and take this message
"Prior to updating sysdatabases entry for database 'ott', mode = 0 and
status = -280 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already
correctly reset. No error and no changes made."
but i take same error when execute
DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )|||Try this.
http://www.faqs.org/qa/qa-3213.html
AND IN FUTURE TAKE BACKUPS.
--
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:FBDF1BC8-7451-45A1-B02A-ADD289FDD21B@.microsoft.com...
> no i havent.
> i execute
> sp_resetstatus 'dbname'
> and take this message
> "Prior to updating sysdatabases entry for database 'ott', mode = 0 and
> status = -280 (status suspect_bit = 0).
> No row in sysdatabases was updated because mode and status are already
> correctly reset. No error and no changes made."
> but i take same error when execute
> DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )|||i done all your writes but dont work yet.database status change to suspect
mode and dont permit to recover database|||Or this
http://support.microsoft.com/default.aspx?scid=kb;en-us;165918
--
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>i done all your writes but dont work yet.database status change to suspect
> mode and dont permit to recover database|||Sorry that's 6.5
--
Nik Marshall-Blank MCSD/MCDBA
"Nik Marshall-Blank (delete fcom for my email address)"
<NikMB@.fcomiNodefcom.afcomt> wrote in message
news:zLUZe.131890$in2.119840@.fe04.news.easynews.com...
> Or this
> http://support.microsoft.com/default.aspx?scid=kb;en-us;165918
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
> news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>>i done all your writes but dont work yet.database status change to suspect
>> mode and dont permit to recover database
>|||Open a case with Microsoft Support and see if they have any means of salvaging any data. Of,
assuming that you can access the database at all, export all data to a new healthy database (lot of
work, yes). Some thoughts also here: http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql server
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>
recover crashed database
our sqlserver has crashed and try to attach mdf file to server but sql serve
r
open this database in read only\suspect\offline\emergency mode.when we
execute "select" query we take
"torn page error".
and i also execute
"DBCC CheckDB
( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
and i take
"Could not run BEGIN TRANSACTION in database 'dbname' because the database
is in bypass recovery mode."
when execute
DBCC ALLOCDB ( 'dbname' )
i take 16 allocation errors on this database
i must take all data from this database how can i recover this database or
take complete datasHi
Did not have a last good full backup?
Also take a look at sp_resetstatus system stored procedure in the BOL
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql
> server
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>|||no i havent.
i execute
sp_resetstatus 'dbname'
and take this message
"Prior to updating sysdatabases entry for database 'ott', mode = 0 and
status = -280 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already
correctly reset. No error and no changes made."
but i take same error when execute
DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )|||Try this.
http://www.faqs.org/qa/qa-3213.html
AND IN FUTURE TAKE BACKUPS.
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:FBDF1BC8-7451-45A1-B02A-ADD289FDD21B@.microsoft.com...
> no i havent.
> i execute
> sp_resetstatus 'dbname'
> and take this message
> "Prior to updating sysdatabases entry for database 'ott', mode = 0 and
> status = -280 (status suspect_bit = 0).
> No row in sysdatabases was updated because mode and status are already
> correctly reset. No error and no changes made."
> but i take same error when execute
> DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )|||i done all your writes but dont work yet.database status change to suspect
mode and dont permit to recover database|||Or this
http://support.microsoft.com/defaul...kb;en-us;165918
--
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>i done all your writes but dont work yet.database status change to suspect
> mode and dont permit to recover database|||Sorry that's 6.5
Nik Marshall-Blank MCSD/MCDBA
"Nik Marshall-Blank (delete fcom for my email address)"
<NikMB@.fcomiNodefcom.afcomt> wrote in message
news:zLUZe.131890$in2.119840@.fe04.news.easynews.com...
> Or this
> http://support.microsoft.com/defaul...kb;en-us;165918
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
> news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>|||Open a case with Microsoft Support and see if they have any means of salvagi
ng any data. Of,
assuming that you can access the database at all, export all data to a new h
ealthy database (lot of
work, yes). Some thoughts also here: http://www.karaszi.com/SQLServer/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql ser
ver
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>
r
open this database in read only\suspect\offline\emergency mode.when we
execute "select" query we take
"torn page error".
and i also execute
"DBCC CheckDB
( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
and i take
"Could not run BEGIN TRANSACTION in database 'dbname' because the database
is in bypass recovery mode."
when execute
DBCC ALLOCDB ( 'dbname' )
i take 16 allocation errors on this database
i must take all data from this database how can i recover this database or
take complete datasHi
Did not have a last good full backup?
Also take a look at sp_resetstatus system stored procedure in the BOL
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql
> server
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>|||no i havent.
i execute
sp_resetstatus 'dbname'
and take this message
"Prior to updating sysdatabases entry for database 'ott', mode = 0 and
status = -280 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already
correctly reset. No error and no changes made."
but i take same error when execute
DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )|||Try this.
http://www.faqs.org/qa/qa-3213.html
AND IN FUTURE TAKE BACKUPS.
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:FBDF1BC8-7451-45A1-B02A-ADD289FDD21B@.microsoft.com...
> no i havent.
> i execute
> sp_resetstatus 'dbname'
> and take this message
> "Prior to updating sysdatabases entry for database 'ott', mode = 0 and
> status = -280 (status suspect_bit = 0).
> No row in sysdatabases was updated because mode and status are already
> correctly reset. No error and no changes made."
> but i take same error when execute
> DBCC CheckDB ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )|||i done all your writes but dont work yet.database status change to suspect
mode and dont permit to recover database|||Or this
http://support.microsoft.com/defaul...kb;en-us;165918
--
Nik Marshall-Blank MCSD/MCDBA
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>i done all your writes but dont work yet.database status change to suspect
> mode and dont permit to recover database|||Sorry that's 6.5
Nik Marshall-Blank MCSD/MCDBA
"Nik Marshall-Blank (delete fcom for my email address)"
<NikMB@.fcomiNodefcom.afcomt> wrote in message
news:zLUZe.131890$in2.119840@.fe04.news.easynews.com...
> Or this
> http://support.microsoft.com/defaul...kb;en-us;165918
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
> news:B8E724A2-122E-40B0-84FB-18393E25F9EB@.microsoft.com...
>|||Open a case with Microsoft Support and see if they have any means of salvagi
ng any data. Of,
assuming that you can access the database at all, export all data to a new h
ealthy database (lot of
work, yes). Some thoughts also here: http://www.karaszi.com/SQLServer/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:9A4C6270-8982-4718-B5E6-D81183DEC6DA@.microsoft.com...
> our sqlserver has crashed and try to attach mdf file to server but sql ser
ver
> open this database in read only\suspect\offline\emergency mode.when we
> execute "select" query we take
> "torn page error".
> and i also execute
> "DBCC CheckDB
> ( 'dbname' ,REPAIR_ALLOW_DATA_LOSS )"
> and i take
> "Could not run BEGIN TRANSACTION in database 'dbname' because the database
> is in bypass recovery mode."
> when execute
> DBCC ALLOCDB ( 'dbname' )
> i take 16 allocation errors on this database
> i must take all data from this database how can i recover this database or
> take complete datas
>
Subscribe to:
Posts (Atom)