Clear Connections command

Scenario: You have a database that is in use and you want to detach it. You
use enterprise manager to Detach Database. A dialog box appears that lists
the Database status. It says there are 3 Connections using this database.
Next to the number of connections is a Clear button that will drop the active
connections so that you can continue the process of detaching the database.
Does anyone know the command equivalent to pressing the Clear button?
I don't want to use the graphical interface and I assume there must be some
way to issue a command that would do the same thing as pressing the Clear
button in the GUI.
I don't actually want to detach the database - I am just trying to clear all
connections other than my own. Once I finish clearing connections, I want to
issue a command to temporarily put the database into single user mode.
Alter Database FOO set SINGLE_USER with Rollback Immediate
Everything you want in one command. See the ALTER DATABASE command in BOL
for details.
To set it back to 'normal'
Alter Database FOO set MULTI_USER
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cathedr@.wa.state.gov" <cathedrwastategov@.discussions.microsoft.com> wrote
in message news:7EA72915-DF9B-437B-9E08-AE54D210AB9C@.microsoft.com...
> Scenario: You have a database that is in use and you want to detach it.
You
> use enterprise manager to Detach Database. A dialog box appears that
lists
> the Database status. It says there are 3 Connections using this database.
> Next to the number of connections is a Clear button that will drop the
active
> connections so that you can continue the process of detaching the
database.
> Does anyone know the command equivalent to pressing the Clear button?
> I don't want to use the graphical interface and I assume there must be
some
> way to issue a command that would do the same thing as pressing the Clear
> button in the GUI.
> I don't actually want to detach the database - I am just trying to clear
all
> connections other than my own. Once I finish clearing connections, I want
to
> issue a command to temporarily put the database into single user mode.
sqlsql

Clear Connections command

Scenario: You have a database that is in use and you want to detach it. You
use enterprise manager to Detach Database. A dialog box appears that lists
the Database status. It says there are 3 Connections using this database.
Next to the number of connections is a Clear button that will drop the active
connections so that you can continue the process of detaching the database.
Does anyone know the command equivalent to pressing the Clear button?
I don't want to use the graphical interface and I assume there must be some
way to issue a command that would do the same thing as pressing the Clear
button in the GUI.
I don't actually want to detach the database - I am just trying to clear all
connections other than my own. Once I finish clearing connections, I want to
issue a command to temporarily put the database into single user mode.Alter Database FOO set SINGLE_USER with Rollback Immediate
Everything you want in one command. See the ALTER DATABASE command in BOL
for details.
To set it back to 'normal'
Alter Database FOO set MULTI_USER
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cathedr@.wa.state.gov" <cathedrwastategov@.discussions.microsoft.com> wrote
in message news:7EA72915-DF9B-437B-9E08-AE54D210AB9C@.microsoft.com...
> Scenario: You have a database that is in use and you want to detach it.
You
> use enterprise manager to Detach Database. A dialog box appears that
lists
> the Database status. It says there are 3 Connections using this database.
> Next to the number of connections is a Clear button that will drop the
active
> connections so that you can continue the process of detaching the
database.
> Does anyone know the command equivalent to pressing the Clear button?
> I don't want to use the graphical interface and I assume there must be
some
> way to issue a command that would do the same thing as pressing the Clear
> button in the GUI.
> I don't actually want to detach the database - I am just trying to clear
all
> connections other than my own. Once I finish clearing connections, I want
to
> issue a command to temporarily put the database into single user mode.

Clean Caches command vs recompile

Before I do performance check for stored procedure, I always clean the
caches using the following commands.
DBCC DROPCLEANBUFFERS
Go
DBCC FREEPROCCACHE
GO
But sometimes I think it's annoying as it basically purges all the caches
from the SQL server.
So if I just test one procedure's performance, I can just add 'WITH
recompile' keyword in the end, right?
is that the same thing since this way stored procedure will not use the
caches even caches exist.no, it's not the same thing. both 'with recompile' and 'dbcc freeprocbuffer'
will force recompile of the procedure, but 'dbcc dropcleanbuffers' will
purge the data cache (which is the reason it's not advisable to do it on a
production server).
dean
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%23Ut4V3EFFHA.1084@.tk2msftngp13.phx.gbl...
>
> Before I do performance check for stored procedure, I always clean the
> caches using the following commands.
>
>
> DBCC DROPCLEANBUFFERS
> Go
> DBCC FREEPROCCACHE
> GO
>
> But sometimes I think it's annoying as it basically purges all the caches
> from the SQL server.
> So if I just test one procedure's performance, I can just add 'WITH
> recompile' keyword in the end, right?
> is that the same thing since this way stored procedure will not use the
> caches even caches exist.
>
>
>
>

checkpoint command and backup log

This is maybe a dumb question but I couldn't find a definitive answer
on BOL.

Looking at my backup script. If I issue a CHECKPOINT, does this truly
force all transaction log entries to the data file? Therefore, making
it unnecessary to BACKUP log (just BACKUP database is needed).

LouisLouis (louisducnguyen@.gmail.com) writes:
> This is maybe a dumb question but I couldn't find a definitive answer
> on BOL.
> Looking at my backup script. If I issue a CHECKPOINT, does this truly
> force all transaction log entries to the data file? Therefore, making
> it unnecessary to BACKUP log (just BACKUP database is needed).

The answer to your actual question may be yes, but the answer to the
implicit question about BACKUP log to be unnecessary is no.

Either you are running your database in simple recovery mode, in which
case you don't have to backup the log anyway.

Or you are running your database in full or hulk-logged mode, in which
case you must backup the log, or else the log till continue to grow. And
if you want up-to-the-point recovery, you want the transaction log.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Checking Database Recovery Mode

Hi,
How can I find out what the current recovery mode of a database is?
I know that I can set it with ALTER DATABASE command but I don't know how to
check to see what the current mode is. Could you please someone help me?
I didn't find anything in sysdatabases table.
Thank you,
RosieTry:
SELECT DATABASEPROPERTYEX('MyDatabase', 'Recovery')
Hope this helps.
Dan Guzman
SQL Server MVP
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:3EB4D6F6-7813-40C0-990B-2FF8E3EEDEE1@.microsoft.com...
> Hi,
> How can I find out what the current recovery mode of a database is?
> I know that I can set it with ALTER DATABASE command but I don't know how
> to
> check to see what the current mode is. Could you please someone help me?
> I didn't find anything in sysdatabases table.
> Thank you,
> Rosie
>|||Great! Thank you for your help.
Rosie
"Dan Guzman" wrote:

> Try:
> SELECT DATABASEPROPERTYEX('MyDatabase', 'Recovery')
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rosie" <Rosie@.discussions.microsoft.com> wrote in message
> news:3EB4D6F6-7813-40C0-990B-2FF8E3EEDEE1@.microsoft.com...
>
>|||I'm glad I was able to help.
Dan Guzman
SQL Server MVP
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:685AF33B-8EBC-4BE5-9D15-556A1712A1CD@.microsoft.com...[vbcol=seagreen]
> Great! Thank you for your help.
> Rosie
> "Dan Guzman" wrote:
>

Checking Database Recovery Mode

Hi,
How can I find out what the current recovery mode of a database is?
I know that I can set it with ALTER DATABASE command but I don't know how to
check to see what the current mode is. Could you please someone help me?
I didn't find anything in sysdatabases table.
Thank you,
RosieTry:
SELECT DATABASEPROPERTYEX('MyDatabase', 'Recovery')
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:3EB4D6F6-7813-40C0-990B-2FF8E3EEDEE1@.microsoft.com...
> Hi,
> How can I find out what the current recovery mode of a database is?
> I know that I can set it with ALTER DATABASE command but I don't know how
> to
> check to see what the current mode is. Could you please someone help me?
> I didn't find anything in sysdatabases table.
> Thank you,
> Rosie
>|||Great! Thank you for your help.
Rosie
"Dan Guzman" wrote:
> Try:
> SELECT DATABASEPROPERTYEX('MyDatabase', 'Recovery')
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rosie" <Rosie@.discussions.microsoft.com> wrote in message
> news:3EB4D6F6-7813-40C0-990B-2FF8E3EEDEE1@.microsoft.com...
> > Hi,
> >
> > How can I find out what the current recovery mode of a database is?
> >
> > I know that I can set it with ALTER DATABASE command but I don't know how
> > to
> > check to see what the current mode is. Could you please someone help me?
> >
> > I didn't find anything in sysdatabases table.
> >
> > Thank you,
> > Rosie
> >
>
>|||I'm glad I was able to help.
--
Dan Guzman
SQL Server MVP
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:685AF33B-8EBC-4BE5-9D15-556A1712A1CD@.microsoft.com...
> Great! Thank you for your help.
> Rosie
> "Dan Guzman" wrote:
>> Try:
>> SELECT DATABASEPROPERTYEX('MyDatabase', 'Recovery')
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Rosie" <Rosie@.discussions.microsoft.com> wrote in message
>> news:3EB4D6F6-7813-40C0-990B-2FF8E3EEDEE1@.microsoft.com...
>> > Hi,
>> >
>> > How can I find out what the current recovery mode of a database is?
>> >
>> > I know that I can set it with ALTER DATABASE command but I don't know
>> > how
>> > to
>> > check to see what the current mode is. Could you please someone help
>> > me?
>> >
>> > I didn't find anything in sysdatabases table.
>> >
>> > Thank you,
>> > Rosie
>> >
>>

Checking backup file questions

Hi All,

I have several questions about checking backup file :

a. When SQL Server receive a command :
BACKUP DATABASE test to DISK = '\\pc1\netdb\BTEST',
does it verify BTEST file before ending the job ?
b. Is there any special command for checking / verifying
a backup file ?
c. If I send a restore command using a file infected
by a virus, what action will SQL Server take ?

Thanks in advance
John S

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!> a. When SQL Server receive a command :
> BACKUP DATABASE test to DISK = '\\pc1\netdb\BTEST',
> does it verify BTEST file before ending the job ?

No.

> b. Is there any special command for checking / verifying
> a backup file ?

You can use RESTORE VERIFYONLY to ensure the backup file format is valid
like the example below. However, this does only cursory validation. The
only way to be certain the backup is valid is to perform a test restore.

RESTORE VERIFYONLY
FROM DISK = '\\pc1\netdb\BTEST'

> c. If I send a restore command using a file infected
> by a virus, what action will SQL Server take ?

SQL Server will try to perform the restore but there is no telling what may
happen if the backup file was tampered with.

--
Hope this helps.

Dan Guzman
SQL Server MVP

CHECKDB allocation error

Hello all,
I've got this error as result of CHECKDB command:
CHECKDB found 1 allocation errors and 4 consistency errors in database
'Data'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (Data ).
It looks like the CHECKDB with repair_allow_data_loss can solve the problem.
Is there any way to know beforehand, what data will be lost?
Thanks,
petiHi Peti
It would be useful to see the exact errors, but you may want to look at DBCC
PAGE, googling for "DBCC CHECKDB" AND "DBCC PAGE" comes up with quite a few
results including http://tinyurl.com/y9rk25 and http://tinyurl.com/yjabup
Reading "Inside SQL Server 2000" by Kalen Delany ISBN 0-7356-0998-5 and "The
Guru's Guide to Transact-SQL" by Ken Henderson ISBN 0-201-61576-2 will give
you useful information on these as well.
John
"peti" wrote:
> Hello all,
> I've got this error as result of CHECKDB command:
> CHECKDB found 1 allocation errors and 4 consistency errors in database
> 'Data'.
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKDB (Data ).
> It looks like the CHECKDB with repair_allow_data_loss can solve the problem.
> Is there any way to know beforehand, what data will be lost?
> Thanks,
> peti
>
>|||Thank you John,
I've read those posts but I'm still confused - for the error 8981, if the
dbbcc checkdb REPAIR_ALLOW_DATA_LOSS works fine, how can I find what data is
missing?
DBCC CHECKDB outcome:
DBCC results for 'conflict_edu_Script_tab'.
Server: Msg 8979, Level 16, State 1, Line 1
Table error: Object ID 772614241, index ID 1. Page (1:482455) is missing
references from parent (unknown) and previous (page (1:482777)) nodes.
Possible bad root entry in sysindexes.
There are 0 rows in 0 pages for object 'conflict_edu_Script_tab'.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53072
...................
DBCC results for 'cse_CaseFolder_tab'.
Server: Msg 8981, Level 16, State 1, Line 1
Table error: Object ID 772614241, index ID 1. The next pointer of (1:482455)
refers to page (1:482776). Neither (1:482776) nor its parent were
encountered. Possible bad chain linkage.
Server: Msg 8981, Level 16, State 1, Line 1
Table error: Object ID 772614241, index ID 1. The next pointer of (1:493336)
refers to page (1:482778). Neither (1:482778) nor its parent were
encountered. Possible bad chain linkage.
There are 4622077 rows in 91565 pages for object 'cse_CaseFolder_tab'.
CHECKDB found 0 allocation errors and 3 consistency errors in table
'cse_Case_tab' (object ID 772614241).
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63537
..........
There are 29 rows in 1 pages for object 'cse_Note_tab'.
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'DB', index 'cse_Case_tab.cse_Case_ind7' (ID
1520633106) (index ID 7). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:24702:1) with values (CTId = 10 and ToCTA = NULL and CCId = 1
and CFId = 1049716 and CaseId = 1) points to the data row identified by ().
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_6f7a.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_6f76.asp
/peti
"peti" <bpetigaspar@.gmail.com> wrote in message
news:eh59ee$j2v$3@.news.al.sw.ericsson.se...
> Hello all,
> I've got this error as result of CHECKDB command:
> CHECKDB found 1 allocation errors and 4 consistency errors in database
> 'Data'.
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKDB (Data ).
> It looks like the CHECKDB with repair_allow_data_loss can solve the
problem.
> Is there any way to know beforehand, what data will be lost?
> Thanks,
> peti
>|||Hi Peti
If there are a large number of errors the most likely cause would be a
hardware fault you would first need to eliminate what caused it before trying
to repair the database. You can always drop and re-create indexes to try and
remove any inconsistencies, what does the NOINDEX option for CHECKDB return?
John
"peti" wrote:
> Thank you John,
> I've read those posts but I'm still confused - for the error 8981, if the
> dbbcc checkdb REPAIR_ALLOW_DATA_LOSS works fine, how can I find what data is
> missing?
> DBCC CHECKDB outcome:
>
> DBCC results for 'conflict_edu_Script_tab'.
> Server: Msg 8979, Level 16, State 1, Line 1
> Table error: Object ID 772614241, index ID 1. Page (1:482455) is missing
> references from parent (unknown) and previous (page (1:482777)) nodes.
> Possible bad root entry in sysindexes.
> There are 0 rows in 0 pages for object 'conflict_edu_Script_tab'.
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53072
> ...................
> DBCC results for 'cse_CaseFolder_tab'.
> Server: Msg 8981, Level 16, State 1, Line 1
> Table error: Object ID 772614241, index ID 1. The next pointer of (1:482455)
> refers to page (1:482776). Neither (1:482776) nor its parent were
> encountered. Possible bad chain linkage.
> Server: Msg 8981, Level 16, State 1, Line 1
> Table error: Object ID 772614241, index ID 1. The next pointer of (1:493336)
> refers to page (1:482778). Neither (1:482778) nor its parent were
> encountered. Possible bad chain linkage.
> There are 4622077 rows in 91565 pages for object 'cse_CaseFolder_tab'.
> CHECKDB found 0 allocation errors and 3 consistency errors in table
> 'cse_Case_tab' (object ID 772614241).
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63537
> ...........
> There are 29 rows in 1 pages for object 'cse_Note_tab'.
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'DB', index 'cse_Case_tab.cse_Case_ind7' (ID
> 1520633106) (index ID 7). Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:24702:1) with values (CTId = 10 and ToCTA = NULL and CCId = 1
> and CFId = 1049716 and CaseId = 1) points to the data row identified by ().
>
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_6f7a.asp
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_6f76.asp
> /peti
>
>
> "peti" <bpetigaspar@.gmail.com> wrote in message
> news:eh59ee$j2v$3@.news.al.sw.ericsson.se...
> > Hello all,
> >
> > I've got this error as result of CHECKDB command:
> >
> > CHECKDB found 1 allocation errors and 4 consistency errors in database
> > 'Data'.
> > repair_allow_data_loss is the minimum repair level for the errors found by
> > DBCC CHECKDB (Data ).
> >
> > It looks like the CHECKDB with repair_allow_data_loss can solve the
> problem.
> > Is there any way to know beforehand, what data will be lost?
> >
> > Thanks,
> > peti
> >
> >
>
>|||It was a power failure.
In my case the clustered index is corupt (Object ID 772614241, index ID 1).
Does it mean data loss?
What means data loss - only one row or the entire table?
/peti
"peti" <bpetigaspar@.gmail.com> wrote in message
news:eh5qb1$s3r$1@.news.al.sw.ericsson.se...
> Thank you John,
> I've read those posts but I'm still confused - for the error 8981, if the
> dbbcc checkdb REPAIR_ALLOW_DATA_LOSS works fine, how can I find what data
is
> missing?
> DBCC CHECKDB outcome:
>
> DBCC results for 'conflict_edu_Script_tab'.
> Server: Msg 8979, Level 16, State 1, Line 1
> Table error: Object ID 772614241, index ID 1. Page (1:482455) is missing
> references from parent (unknown) and previous (page (1:482777)) nodes.
> Possible bad root entry in sysindexes.
> There are 0 rows in 0 pages for object 'conflict_edu_Script_tab'.
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53072
> ...................
> DBCC results for 'cse_CaseFolder_tab'.
> Server: Msg 8981, Level 16, State 1, Line 1
> Table error: Object ID 772614241, index ID 1. The next pointer of
(1:482455)
> refers to page (1:482776). Neither (1:482776) nor its parent were
> encountered. Possible bad chain linkage.
> Server: Msg 8981, Level 16, State 1, Line 1
> Table error: Object ID 772614241, index ID 1. The next pointer of
(1:493336)
> refers to page (1:482778). Neither (1:482778) nor its parent were
> encountered. Possible bad chain linkage.
> There are 4622077 rows in 91565 pages for object 'cse_CaseFolder_tab'.
> CHECKDB found 0 allocation errors and 3 consistency errors in table
> 'cse_Case_tab' (object ID 772614241).
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63537
> ..........
> There are 29 rows in 1 pages for object 'cse_Note_tab'.
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'DB', index 'cse_Case_tab.cse_Case_ind7' (ID
> 1520633106) (index ID 7). Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:24702:1) with values (CTId = 10 and ToCTA = NULL and CCId = 1
> and CFId = 1049716 and CaseId = 1) points to the data row identified by
().
>
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_6f7a.asp
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_6f76.asp
> /peti
>
>
> "peti" <bpetigaspar@.gmail.com> wrote in message
> news:eh59ee$j2v$3@.news.al.sw.ericsson.se...
> > Hello all,
> >
> > I've got this error as result of CHECKDB command:
> >
> > CHECKDB found 1 allocation errors and 4 consistency errors in database
> > 'Data'.
> > repair_allow_data_loss is the minimum repair level for the errors found
by
> > DBCC CHECKDB (Data ).
> >
> > It looks like the CHECKDB with repair_allow_data_loss can solve the
> problem.
> > Is there any way to know beforehand, what data will be lost?
> >
> > Thanks,
> > peti
> >
> >
>|||Hi
The index page is missing it's parent reference it does not necessarily mean
that it contains data. Examples of the syntax and using DBCC PAGE can be
found at http://www.sql-server-performance.com/dbcc_commands.asp and
http://www.sql-server-performance.com/gv_index_data_structures.asp. I assume
that you are persuing this route because you don't have a good backup that
you can roll forward? make sure you backup before correcting the database,
and you may want to compare what you have against the last good backup you
have with something like SQL Data Compare from Red Gate www.red-gate.com, or
compare the pre-repared database to the post-repaired database (which may
only confirm that you have already lost the data!).
John
"peti" wrote:
> It was a power failure.
> In my case the clustered index is corupt (Object ID 772614241, index ID 1).
> Does it mean data loss?
> What means data loss - only one row or the entire table?
> /peti
> "peti" <bpetigaspar@.gmail.com> wrote in message
> news:eh5qb1$s3r$1@.news.al.sw.ericsson.se...
> > Thank you John,
> >
> > I've read those posts but I'm still confused - for the error 8981, if the
> > dbbcc checkdb REPAIR_ALLOW_DATA_LOSS works fine, how can I find what data
> is
> > missing?
> >
> > DBCC CHECKDB outcome:
> >
> >
> >
> > DBCC results for 'conflict_edu_Script_tab'.
> > Server: Msg 8979, Level 16, State 1, Line 1
> > Table error: Object ID 772614241, index ID 1. Page (1:482455) is missing
> > references from parent (unknown) and previous (page (1:482777)) nodes.
> > Possible bad root entry in sysindexes.
> > There are 0 rows in 0 pages for object 'conflict_edu_Script_tab'.
> > http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53072
> >
> > ...................
> >
> > DBCC results for 'cse_CaseFolder_tab'.
> > Server: Msg 8981, Level 16, State 1, Line 1
> > Table error: Object ID 772614241, index ID 1. The next pointer of
> (1:482455)
> > refers to page (1:482776). Neither (1:482776) nor its parent were
> > encountered. Possible bad chain linkage.
> > Server: Msg 8981, Level 16, State 1, Line 1
> > Table error: Object ID 772614241, index ID 1. The next pointer of
> (1:493336)
> > refers to page (1:482778). Neither (1:482778) nor its parent were
> > encountered. Possible bad chain linkage.
> > There are 4622077 rows in 91565 pages for object 'cse_CaseFolder_tab'.
> > CHECKDB found 0 allocation errors and 3 consistency errors in table
> > 'cse_Case_tab' (object ID 772614241).
> >
> > http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63537
> >
> > ..........
> >
> > There are 29 rows in 1 pages for object 'cse_Note_tab'.
> > Server: Msg 8952, Level 16, State 1, Line 1
> > Table error: Database 'DB', index 'cse_Case_tab.cse_Case_ind7' (ID
> > 1520633106) (index ID 7). Extra or invalid key for the keys:
> > Server: Msg 8956, Level 16, State 1, Line 1
> > Index row (1:24702:1) with values (CTId = 10 and ToCTA = NULL and CCId = 1
> > and CFId = 1049716 and CaseId = 1) points to the data row identified by
> ().
> >
> >
> >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_6f7a.asp
> >
> >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_6f76.asp
> >
> > /peti
> >
> >
> >
> >
> >
> > "peti" <bpetigaspar@.gmail.com> wrote in message
> > news:eh59ee$j2v$3@.news.al.sw.ericsson.se...
> > > Hello all,
> > >
> > > I've got this error as result of CHECKDB command:
> > >
> > > CHECKDB found 1 allocation errors and 4 consistency errors in database
> > > 'Data'.
> > > repair_allow_data_loss is the minimum repair level for the errors found
> by
> > > DBCC CHECKDB (Data ).
> > >
> > > It looks like the CHECKDB with repair_allow_data_loss can solve the
> > problem.
> > > Is there any way to know beforehand, what data will be lost?
> > >
> > > Thanks,
> > > peti
> > >
> > >
> >
> >
>
>|||Hello John,
This is the result of DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS :
......
CHECKDB found 1 allocation errors and 6 consistency errors in database
'Data'.
CHECKDB fixed 1 allocation errors and 6 consistency errors in database
'Data'.
.......
Then, the CHECKDB command reported only consistency errors:
.........
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'cse_Case_tab' (ID 1520633106). Missing or invalid
key in index 'cse_Case_ind7' (ID 7) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:401723:0) identified by (RID = (1:401723:0) CenterId = 1 and
CaseFolderId = 1049723 and CaseId = 1) has index values (CaseTypeId = 10 and
ToCaseTypeArea = ').
CHECKDB found 0 allocation errors and 4 consistency errors in table
'cse_Case_tab' (object ID 1520633106).
CHECKDB found 0 allocation errors and 4 consistency errors in database
'Data'.
repair_fast is the minimum repair level for the errors found by DBCC
CHECKDB (Data ).
........
Repair fast didn't solved the consistency errors so I run DBCC DBREINDEX
against cse_Case_ind7 index. Then CHECKDB reported no errors.
Red Gate still reports "differences" between databases. Some rows are
reported as being only in the old DB but the same number of rows with the
same content are reported as being present only in the new DB. So the same
data is logically in both databases. I consider this no problem!?
The transactional replication is still in place. Should I stop the
replication before DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS ? I suppose yes
because of single user mode requirement.
regards,
peti
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:CB6FA2CE-02D0-4095-8DF7-B7ED410D7FEE@.microsoft.com...
> Hi
> The index page is missing it's parent reference it does not necessarily
mean
> that it contains data. Examples of the syntax and using DBCC PAGE can be
> found at http://www.sql-server-performance.com/dbcc_commands.asp and
> http://www.sql-server-performance.com/gv_index_data_structures.asp. I
assume
> that you are persuing this route because you don't have a good backup that
> you can roll forward? make sure you backup before correcting the database,
> and you may want to compare what you have against the last good backup you
> have with something like SQL Data Compare from Red Gate www.red-gate.com,
or
> compare the pre-repared database to the post-repaired database (which may
> only confirm that you have already lost the data!).
> John
> "peti" wrote:
> > It was a power failure.
> >
> > In my case the clustered index is corupt (Object ID 772614241, index ID
1).
> > Does it mean data loss?
> >
> > What means data loss - only one row or the entire table?
> >
> > /peti
> >
> > "peti" <bpetigaspar@.gmail.com> wrote in message
> > news:eh5qb1$s3r$1@.news.al.sw.ericsson.se...
> > > Thank you John,
> > >
> > > I've read those posts but I'm still confused - for the error 8981, if
the
> > > dbbcc checkdb REPAIR_ALLOW_DATA_LOSS works fine, how can I find what
data
> > is
> > > missing?
> > >
> > > DBCC CHECKDB outcome:
> > >
> > >
> > >
> > > DBCC results for 'conflict_edu_Script_tab'.
> > > Server: Msg 8979, Level 16, State 1, Line 1
> > > Table error: Object ID 772614241, index ID 1. Page (1:482455) is
missing
> > > references from parent (unknown) and previous (page (1:482777)) nodes.
> > > Possible bad root entry in sysindexes.
> > > There are 0 rows in 0 pages for object 'conflict_edu_Script_tab'.
> > > http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53072
> > >
> > > ...................
> > >
> > > DBCC results for 'cse_CaseFolder_tab'.
> > > Server: Msg 8981, Level 16, State 1, Line 1
> > > Table error: Object ID 772614241, index ID 1. The next pointer of
> > (1:482455)
> > > refers to page (1:482776). Neither (1:482776) nor its parent were
> > > encountered. Possible bad chain linkage.
> > > Server: Msg 8981, Level 16, State 1, Line 1
> > > Table error: Object ID 772614241, index ID 1. The next pointer of
> > (1:493336)
> > > refers to page (1:482778). Neither (1:482778) nor its parent were
> > > encountered. Possible bad chain linkage.
> > > There are 4622077 rows in 91565 pages for object 'cse_CaseFolder_tab'.
> > > CHECKDB found 0 allocation errors and 3 consistency errors in table
> > > 'cse_Case_tab' (object ID 772614241).
> > >
> > > http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63537
> > >
> > > ..........
> > >
> > > There are 29 rows in 1 pages for object 'cse_Note_tab'.
> > > Server: Msg 8952, Level 16, State 1, Line 1
> > > Table error: Database 'DB', index 'cse_Case_tab.cse_Case_ind7' (ID
> > > 1520633106) (index ID 7). Extra or invalid key for the keys:
> > > Server: Msg 8956, Level 16, State 1, Line 1
> > > Index row (1:24702:1) with values (CTId = 10 and ToCTA = NULL and CCId
= 1
> > > and CFId = 1049716 and CaseId = 1) points to the data row identified
by
> > ().
> > >
> > >
> > >
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_6f7a.asp
> > >
> > >
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_6f76.asp
> > >
> > > /peti
> > >
> > >
> > >
> > >
> > >
> > > "peti" <bpetigaspar@.gmail.com> wrote in message
> > > news:eh59ee$j2v$3@.news.al.sw.ericsson.se...
> > > > Hello all,
> > > >
> > > > I've got this error as result of CHECKDB command:
> > > >
> > > > CHECKDB found 1 allocation errors and 4 consistency errors in
database
> > > > 'Data'.
> > > > repair_allow_data_loss is the minimum repair level for the errors
found
> > by
> > > > DBCC CHECKDB (Data ).
> > > >
> > > > It looks like the CHECKDB with repair_allow_data_loss can solve the
> > > problem.
> > > > Is there any way to know beforehand, what data will be lost?
> > > >
> > > > Thanks,
> > > > peti
> > > >
> > > >
> > >
> > >
> >
> >
> >|||Hi Peti
I don't use replication, but I would stop it to avoid potentially a
significant number of changes being passed over the network. If you are
replicating in full any of the tables that have shown errors then you may
want to see if comparing the replicated database and the current live
database shows any differences.
John
"peti" wrote:
> Hello John,
> This is the result of DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS :
> ......
> CHECKDB found 1 allocation errors and 6 consistency errors in database
> 'Data'.
> CHECKDB fixed 1 allocation errors and 6 consistency errors in database
> 'Data'.
> ........
> Then, the CHECKDB command reported only consistency errors:
> ..........
> Server: Msg 8951, Level 16, State 1, Line 1
> Table error: Table 'cse_Case_tab' (ID 1520633106). Missing or invalid
> key in index 'cse_Case_ind7' (ID 7) for the row:
> Server: Msg 8955, Level 16, State 1, Line 1
> Data row (1:401723:0) identified by (RID = (1:401723:0) CenterId = 1 and
> CaseFolderId = 1049723 and CaseId = 1) has index values (CaseTypeId = 10 and
> ToCaseTypeArea = ').
> CHECKDB found 0 allocation errors and 4 consistency errors in table
> 'cse_Case_tab' (object ID 1520633106).
> CHECKDB found 0 allocation errors and 4 consistency errors in database
> 'Data'.
> repair_fast is the minimum repair level for the errors found by DBCC
> CHECKDB (Data ).
> .........
> Repair fast didn't solved the consistency errors so I run DBCC DBREINDEX
> against cse_Case_ind7 index. Then CHECKDB reported no errors.
> Red Gate still reports "differences" between databases. Some rows are
> reported as being only in the old DB but the same number of rows with the
> same content are reported as being present only in the new DB. So the same
> data is logically in both databases. I consider this no problem!?
> The transactional replication is still in place. Should I stop the
> replication before DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS ? I suppose yes
> because of single user mode requirement.
> regards,
> peti
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:CB6FA2CE-02D0-4095-8DF7-B7ED410D7FEE@.microsoft.com...
> > Hi
> >
> > The index page is missing it's parent reference it does not necessarily
> mean
> > that it contains data. Examples of the syntax and using DBCC PAGE can be
> > found at http://www.sql-server-performance.com/dbcc_commands.asp and
> > http://www.sql-server-performance.com/gv_index_data_structures.asp. I
> assume
> > that you are persuing this route because you don't have a good backup that
> > you can roll forward? make sure you backup before correcting the database,
> > and you may want to compare what you have against the last good backup you
> > have with something like SQL Data Compare from Red Gate www.red-gate.com,
> or
> > compare the pre-repared database to the post-repaired database (which may
> > only confirm that you have already lost the data!).
> >
> > John
> >
> > "peti" wrote:
> >
> > > It was a power failure.
> > >
> > > In my case the clustered index is corupt (Object ID 772614241, index ID
> 1).
> > > Does it mean data loss?
> > >
> > > What means data loss - only one row or the entire table?
> > >
> > > /peti
> > >
> > > "peti" <bpetigaspar@.gmail.com> wrote in message
> > > news:eh5qb1$s3r$1@.news.al.sw.ericsson.se...
> > > > Thank you John,
> > > >
> > > > I've read those posts but I'm still confused - for the error 8981, if
> the
> > > > dbbcc checkdb REPAIR_ALLOW_DATA_LOSS works fine, how can I find what
> data
> > > is
> > > > missing?
> > > >
> > > > DBCC CHECKDB outcome:
> > > >
> > > >
> > > >
> > > > DBCC results for 'conflict_edu_Script_tab'.
> > > > Server: Msg 8979, Level 16, State 1, Line 1
> > > > Table error: Object ID 772614241, index ID 1. Page (1:482455) is
> missing
> > > > references from parent (unknown) and previous (page (1:482777)) nodes.
> > > > Possible bad root entry in sysindexes.
> > > > There are 0 rows in 0 pages for object 'conflict_edu_Script_tab'.
> > > > http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53072
> > > >
> > > > ...................
> > > >
> > > > DBCC results for 'cse_CaseFolder_tab'.
> > > > Server: Msg 8981, Level 16, State 1, Line 1
> > > > Table error: Object ID 772614241, index ID 1. The next pointer of
> > > (1:482455)
> > > > refers to page (1:482776). Neither (1:482776) nor its parent were
> > > > encountered. Possible bad chain linkage.
> > > > Server: Msg 8981, Level 16, State 1, Line 1
> > > > Table error: Object ID 772614241, index ID 1. The next pointer of
> > > (1:493336)
> > > > refers to page (1:482778). Neither (1:482778) nor its parent were
> > > > encountered. Possible bad chain linkage.
> > > > There are 4622077 rows in 91565 pages for object 'cse_CaseFolder_tab'.
> > > > CHECKDB found 0 allocation errors and 3 consistency errors in table
> > > > 'cse_Case_tab' (object ID 772614241).
> > > >
> > > > http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63537
> > > >
> > > > ..........
> > > >
> > > > There are 29 rows in 1 pages for object 'cse_Note_tab'.
> > > > Server: Msg 8952, Level 16, State 1, Line 1
> > > > Table error: Database 'DB', index 'cse_Case_tab.cse_Case_ind7' (ID
> > > > 1520633106) (index ID 7). Extra or invalid key for the keys:
> > > > Server: Msg 8956, Level 16, State 1, Line 1
> > > > Index row (1:24702:1) with values (CTId = 10 and ToCTA = NULL and CCId
> = 1
> > > > and CFId = 1049716 and CaseId = 1) points to the data row identified
> by
> > > ().
> > > >
> > > >
> > > >
> > >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_6f7a.asp
> > > >
> > > >
> > >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_6f76.asp
> > > >
> > > > /peti
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "peti" <bpetigaspar@.gmail.com> wrote in message
> > > > news:eh59ee$j2v$3@.news.al.sw.ericsson.se...
> > > > > Hello all,
> > > > >
> > > > > I've got this error as result of CHECKDB command:
> > > > >
> > > > > CHECKDB found 1 allocation errors and 4 consistency errors in
> database
> > > > > 'Data'.
> > > > > repair_allow_data_loss is the minimum repair level for the errors
> found
> > > by
> > > > > DBCC CHECKDB (Data ).
> > > > >
> > > > > It looks like the CHECKDB with repair_allow_data_loss can solve the
> > > > problem.
> > > > > Is there any way to know beforehand, what data will be lost?
> > > > >
> > > > > Thanks,
> > > > > peti
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> > >
>
>

Check Volume Free Space

Hello,
Can you tell me if there is any command that can tell me
the free space in a volume?
I need to create one alarm, that send to the event viewer
one message if one volume has less than x free space.
Best regards
Here is an article that should help you with that.
http://databasejournal.com/features/...le.php/3080501
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2b601c4521d$5fe4dce0$a401280a@.phx.gbl...
> Hello,
> Can you tell me if there is any command that can tell me
> the free space in a volume?
> I need to create one alarm, that send to the event viewer
> one message if one volume has less than x free space.
> Best regards

Check Volume Free Space

Hello,
Can you tell me if there is any command that can tell me
the free space in a volume?
I need to create one alarm, that send to the event viewer
one message if one volume has less than x free space.
Best regardsHere is an article that should help you with that.
http://databasejournal.com/features/mssql/article.php/3080501
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2b601c4521d$5fe4dce0$a401280a@.phx.gbl...
> Hello,
> Can you tell me if there is any command that can tell me
> the free space in a volume?
> I need to create one alarm, that send to the event viewer
> one message if one volume has less than x free space.
> Best regards

Check Volume Free Space

Hello,
Can you tell me if there is any command that can tell me
the free space in a volume?
I need to create one alarm, that send to the event viewer
one message if one volume has less than x free space.
Best regardsHere is an article that should help you with that.
http://databasejournal.com/features...cle.php/3080501
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2b601c4521d$5fe4dce0$a401280a@.phx
.gbl...
> Hello,
> Can you tell me if there is any command that can tell me
> the free space in a volume?
> I need to create one alarm, that send to the event viewer
> one message if one volume has less than x free space.
> Best regards