I have to repair a database with repair_allow_data_loss level, so I start
SQL Server on single-user mode with the following command
sqlservr.exe -c -m
and run the repair command:
dbcc checkdb ('blade', repair_allow_data_loss)
I get the following error that doesn't make sense, since I am already in
single user mode:
Server: Msg 7919, Level 16, State 2, Line 1
Repair statement not processed. Database needs to be in single user mode.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Anyone can help?You don't need to start the server in single user mode for the DBCC, just
place the *database* in single-user-mode. You can do this with ALTER
DATABASE:
ALTER DATABASE MyDaabase
SET SINGLE_USER
Note that it's usually better to restore from backup than resort to DBCC
CHECKDB ... REPAIR_ALLOW_DATA_LOSS. However, the option is useful if you
have no backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"savvas" <savvas@.blade.com.cy> wrote in message
news:uI0sdJFLGHA.3944@.tk2msftngp13.phx.gbl...
>I have to repair a database with repair_allow_data_loss level, so I start
> SQL Server on single-user mode with the following command
> sqlservr.exe -c -m
> and run the repair command:
> dbcc checkdb ('blade', repair_allow_data_loss)
> I get the following error that doesn't make sense, since I am already in
> single user mode:
> Server: Msg 7919, Level 16, State 2, Line 1
> Repair statement not processed. Database needs to be in single user mode.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Anyone can help?
>
Showing posts with label repair_allow_data_loss. Show all posts
Showing posts with label repair_allow_data_loss. Show all posts
Wednesday, March 7, 2012
repair_allow_data_loss Repair statement not processed. Database needs to be in single
I have to repair a database with repair_allow_data_loss level, so I start
SQL Server on single-user mode with the following command
sqlservr.exe -c -m
and run the repair command:
dbcc checkdb ('blade', repair_allow_data_loss)
I get the following error that doesn't make sense, since I am already in
single user mode:
Server: Msg 7919, Level 16, State 2, Line 1
Repair statement not processed. Database needs to be in single user mode.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Anyone can help?You don't need to start the server in single user mode for the DBCC, just
place the *database* in single-user-mode. You can do this with ALTER
DATABASE:
ALTER DATABASE MyDaabase
SET SINGLE_USER
Note that it's usually better to restore from backup than resort to DBCC
CHECKDB ... REPAIR_ALLOW_DATA_LOSS. However, the option is useful if you
have no backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"savvas" <savvas@.blade.com.cy> wrote in message
news:uI0sdJFLGHA.3944@.tk2msftngp13.phx.gbl...
>I have to repair a database with repair_allow_data_loss level, so I start
> SQL Server on single-user mode with the following command
> sqlservr.exe -c -m
> and run the repair command:
> dbcc checkdb ('blade', repair_allow_data_loss)
> I get the following error that doesn't make sense, since I am already in
> single user mode:
> Server: Msg 7919, Level 16, State 2, Line 1
> Repair statement not processed. Database needs to be in single user mode.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Anyone can help?
>
SQL Server on single-user mode with the following command
sqlservr.exe -c -m
and run the repair command:
dbcc checkdb ('blade', repair_allow_data_loss)
I get the following error that doesn't make sense, since I am already in
single user mode:
Server: Msg 7919, Level 16, State 2, Line 1
Repair statement not processed. Database needs to be in single user mode.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Anyone can help?You don't need to start the server in single user mode for the DBCC, just
place the *database* in single-user-mode. You can do this with ALTER
DATABASE:
ALTER DATABASE MyDaabase
SET SINGLE_USER
Note that it's usually better to restore from backup than resort to DBCC
CHECKDB ... REPAIR_ALLOW_DATA_LOSS. However, the option is useful if you
have no backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"savvas" <savvas@.blade.com.cy> wrote in message
news:uI0sdJFLGHA.3944@.tk2msftngp13.phx.gbl...
>I have to repair a database with repair_allow_data_loss level, so I start
> SQL Server on single-user mode with the following command
> sqlservr.exe -c -m
> and run the repair command:
> dbcc checkdb ('blade', repair_allow_data_loss)
> I get the following error that doesn't make sense, since I am already in
> single user mode:
> Server: Msg 7919, Level 16, State 2, Line 1
> Repair statement not processed. Database needs to be in single user mode.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Anyone can help?
>
REPAIR_ALLOW_DATA_LOSS
Is there any way to know if running dbcc with REPAIR_ALLOW_DATA_LOSS has
actually resulted in data loss?
Thanks,
Bob Castleman
DBA PoseurIf you run the CHECKDB with the minimal settings first it will not attempt
any repair that will loose data. If that doesn't work then there is a good
chance the next step will result in loss of data. Remember a restore from
good backups is always the preferred method for fixing issues like that.
Andrew J. Kelly SQL MVP
"Bob Castleman" <nomail@.here> wrote in message
news:OMKIZ5kWFHA.3076@.TK2MSFTNGP12.phx.gbl...
> Is there any way to know if running dbcc with REPAIR_ALLOW_DATA_LOSS has
> actually resulted in data loss?
> Thanks,
> Bob Castleman
> DBA Poseur
>|||Thanks.
We normally do a CHECKDB with minimal settings as part of regular
maintenance. Restoring from backup seems a little drastic without at least
reviewing the specific tables that are showing the problems, don't you
think?
If the data file is corrupt, what does this imply about the stability of the
transaction logs? Specifically, we had a drive fail today in our data array.
It caused one of about 200 databases to throw a sev 24 error and ultimately
required a REPAIR_ALLOW_DATA_LOSS. In this case, this we restored form
backup as the data tables involved were not critical to any business
operations. But now I am wondering if we could have backed up the
transaction log immediately following the sev24 alert and applied that to
the backups and tlogs already available (we do tlogs every 4 hours). Or is
the only way to reduce the window of possible data loss is to decrease the
interval of tlog backups. As I am writing this I'm thinking that if it
throws a sev24 there's likely going to be an incomplete transaction in the
tlog that would get rolled back, correct? It seems to me some of the log
management tools I've seen might be able to apply the tlog up until the
point of the sev24 alert. Or am I tilting at windmills?
Bob
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23N6FF9lWFHA.612@.TK2MSFTNGP12.phx.gbl...
> If you run the CHECKDB with the minimal settings first it will not attempt
> any repair that will loose data. If that doesn't work then there is a
> good chance the next step will result in loss of data. Remember a restore
> from good backups is always the preferred method for fixing issues like
> that.
> --
> Andrew J. Kelly SQL MVP
>
> "Bob Castleman" <nomail@.here> wrote in message
> news:OMKIZ5kWFHA.3076@.TK2MSFTNGP12.phx.gbl...
>|||Bob,
You can always attempt to backup the log. If it does not give any errors
you have a good shot at using it. The transaction will either be complete or
not. There is no middle ground even with a drive failure. If the tran does
not include the commit and all is well it will be rolled back when you
recover the db. If you are worried about data loss then increasing the
amount of log backups is the answer. See if these help answer any of your
questions:
http://support.microsoft.com/defaul...b;EN-US;Q253817
http://support.microsoft.com/?kbid=321836
http://support.microsoft.com/servic...0/wct120500.asp
Talks about recovering from the log about half way down
http://support.microsoft.com/defaul...0&Product=sql2k
Some backup info near the bottom
Andrew J. Kelly SQL MVP
"Bob Castleman" <nomail@.here> wrote in message
news:%23Wb4qImWFHA.712@.TK2MSFTNGP14.phx.gbl...
> Thanks.
> We normally do a CHECKDB with minimal settings as part of regular
> maintenance. Restoring from backup seems a little drastic without at least
> reviewing the specific tables that are showing the problems, don't you
> think?
> If the data file is corrupt, what does this imply about the stability of
> the transaction logs? Specifically, we had a drive fail today in our data
> array. It caused one of about 200 databases to throw a sev 24 error and
> ultimately required a REPAIR_ALLOW_DATA_LOSS. In this case, this we
> restored form backup as the data tables involved were not critical to any
> business operations. But now I am wondering if we could have backed up the
> transaction log immediately following the sev24 alert and applied that to
> the backups and tlogs already available (we do tlogs every 4 hours). Or is
> the only way to reduce the window of possible data loss is to decrease the
> interval of tlog backups. As I am writing this I'm thinking that if it
> throws a sev24 there's likely going to be an incomplete transaction in the
> tlog that would get rolled back, correct? It seems to me some of the log
> management tools I've seen might be able to apply the tlog up until the
> point of the sev24 alert. Or am I tilting at windmills?
> Bob
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23N6FF9lWFHA.612@.TK2MSFTNGP12.phx.gbl...
>
actually resulted in data loss?
Thanks,
Bob Castleman
DBA PoseurIf you run the CHECKDB with the minimal settings first it will not attempt
any repair that will loose data. If that doesn't work then there is a good
chance the next step will result in loss of data. Remember a restore from
good backups is always the preferred method for fixing issues like that.
Andrew J. Kelly SQL MVP
"Bob Castleman" <nomail@.here> wrote in message
news:OMKIZ5kWFHA.3076@.TK2MSFTNGP12.phx.gbl...
> Is there any way to know if running dbcc with REPAIR_ALLOW_DATA_LOSS has
> actually resulted in data loss?
> Thanks,
> Bob Castleman
> DBA Poseur
>|||Thanks.
We normally do a CHECKDB with minimal settings as part of regular
maintenance. Restoring from backup seems a little drastic without at least
reviewing the specific tables that are showing the problems, don't you
think?
If the data file is corrupt, what does this imply about the stability of the
transaction logs? Specifically, we had a drive fail today in our data array.
It caused one of about 200 databases to throw a sev 24 error and ultimately
required a REPAIR_ALLOW_DATA_LOSS. In this case, this we restored form
backup as the data tables involved were not critical to any business
operations. But now I am wondering if we could have backed up the
transaction log immediately following the sev24 alert and applied that to
the backups and tlogs already available (we do tlogs every 4 hours). Or is
the only way to reduce the window of possible data loss is to decrease the
interval of tlog backups. As I am writing this I'm thinking that if it
throws a sev24 there's likely going to be an incomplete transaction in the
tlog that would get rolled back, correct? It seems to me some of the log
management tools I've seen might be able to apply the tlog up until the
point of the sev24 alert. Or am I tilting at windmills?
Bob
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23N6FF9lWFHA.612@.TK2MSFTNGP12.phx.gbl...
> If you run the CHECKDB with the minimal settings first it will not attempt
> any repair that will loose data. If that doesn't work then there is a
> good chance the next step will result in loss of data. Remember a restore
> from good backups is always the preferred method for fixing issues like
> that.
> --
> Andrew J. Kelly SQL MVP
>
> "Bob Castleman" <nomail@.here> wrote in message
> news:OMKIZ5kWFHA.3076@.TK2MSFTNGP12.phx.gbl...
>|||Bob,
You can always attempt to backup the log. If it does not give any errors
you have a good shot at using it. The transaction will either be complete or
not. There is no middle ground even with a drive failure. If the tran does
not include the commit and all is well it will be rolled back when you
recover the db. If you are worried about data loss then increasing the
amount of log backups is the answer. See if these help answer any of your
questions:
http://support.microsoft.com/defaul...b;EN-US;Q253817
http://support.microsoft.com/?kbid=321836
http://support.microsoft.com/servic...0/wct120500.asp
Talks about recovering from the log about half way down
http://support.microsoft.com/defaul...0&Product=sql2k
Some backup info near the bottom
Andrew J. Kelly SQL MVP
"Bob Castleman" <nomail@.here> wrote in message
news:%23Wb4qImWFHA.712@.TK2MSFTNGP14.phx.gbl...
> Thanks.
> We normally do a CHECKDB with minimal settings as part of regular
> maintenance. Restoring from backup seems a little drastic without at least
> reviewing the specific tables that are showing the problems, don't you
> think?
> If the data file is corrupt, what does this imply about the stability of
> the transaction logs? Specifically, we had a drive fail today in our data
> array. It caused one of about 200 databases to throw a sev 24 error and
> ultimately required a REPAIR_ALLOW_DATA_LOSS. In this case, this we
> restored form backup as the data tables involved were not critical to any
> business operations. But now I am wondering if we could have backed up the
> transaction log immediately following the sev24 alert and applied that to
> the backups and tlogs already available (we do tlogs every 4 hours). Or is
> the only way to reduce the window of possible data loss is to decrease the
> interval of tlog backups. As I am writing this I'm thinking that if it
> throws a sev24 there's likely going to be an incomplete transaction in the
> tlog that would get rolled back, correct? It seems to me some of the log
> management tools I've seen might be able to apply the tlog up until the
> point of the sev24 alert. Or am I tilting at windmills?
> Bob
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23N6FF9lWFHA.612@.TK2MSFTNGP12.phx.gbl...
>
Subscribe to:
Posts (Atom)