Clear SQl server Logs by run xp_cmdshell

how i can clear sql server logs by run xp_cmdshell stored procedure.
(my user in sql server is a admin).
i don't want use enterprise manager . i want write a query and use at xp_cmdshell .
befor thx.What logs are you talking about... SQLServer has error logs that you can cycle, the server keeps 5 logs active.

Or for the Transaction logs, you can use TQL to backup the transaction logs and truncate.

But do you use the Transaction logs, ie are you using them for Disaster recovery or such...
You can also turn off the transaction logs or at least turn them down. This is done buy changeing the databases logging method from Full (All Transactions Logged), to Bulk (Only bulk loading transactions), or Simple (No Logging).

There are also artilcles on SQLServercentral.com and MSDN on useing TSQL to shrink the logs and reduce wasted space.

Remember that All enterprise manager is is a front end for TSQL Commands to the database, all commands can be run from code.

Clear Cache sqlrs.ReportingServices2005

I'm using the Reporting Service web service to display my reports to the user.
I've deleted some test reports from the server. I've also gone to the
server, searched the test report names, and deleted those.
When I display the available reports using the Reporting Services web
service, the test reports are still there.
How do I remove Reporting Service reports from the server?
Thanks
--
RandyGo to Report Manager, click on report, properties, delete button.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"randy1200" <randy1200@.newsgroups.nospam> wrote in message
news:F873554A-B145-4800-B93F-65B3F6AFC955@.microsoft.com...
> I'm using the Reporting Service web service to display my reports to the
> user.
> I've deleted some test reports from the server. I've also gone to the
> server, searched the test report names, and deleted those.
> When I display the available reports using the Reporting Services web
> service, the test reports are still there.
> How do I remove Reporting Service reports from the server?
> Thanks
> --
> Randy

Choosing one record from many

I have a table that lists user ID's and their last login times, and most of the records have duplicates with the only difference being the date field showing the last login time. How can I retrieve only the most current record for each ID from this table?

For example, I have ID's ABC and XYZ. Both are listed in the table 6 times each, but I want a resultant table of only 2 records, one for ABC and one for XYZ, and each of these records is the one with the most current (latest)date.

I am using CR 8.5

ThanksWhy not do this in your SQL query, Select Distinct(yourId) from...|||Note: You cannot change the SELECT clause of the SQL statement.

This note is from Crystal Reports Online Help. It seems that when I open the "Show SQL Query", I can edit anything else but the SELECT clause. So it seems that I can't use the DISTINCT from here.

Any other help please!!!

Checkpoints

Hi,
i have a complex database. If a user (database is set to single user)
make changes, and the user want do redo these changes, i need a kind of
manual setted checkpoints. Such a checkpoint is set before changes
made.
The idea is, to set a checkpoint, (store all checkpoints), and when its
necassary you can revert to a checkpoint.
Is this possible or is there another technique to realize this?
Thank you in advance
AlexSQL Server has "database snapshot" that you could potentially use. You can r
estore from a database
snapshot. But there can only exist *one* snapshot of a database if you want
to restore from a (that)
snapshot.
But perhaps it is much cleaner to begin a transaction and rollback?
There's no built-in scheme where many users has many snapshots and you want
to revert to some
certain snapshot for one of the users without affecting the other users (unl
ess each user have their
own database). How would SQL Server understand what data that belongs to use
r A and revert only that
data? Not to speak about relationships between tables.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"alex" <earthquake.de@.freenet.de> wrote in message
news:1157460616.313916.180170@.d34g2000cwd.googlegroups.com...
> Hi,
> i have a complex database. If a user (database is set to single user)
> make changes, and the user want do redo these changes, i need a kind of
> manual setted checkpoints. Such a checkpoint is set before changes
> made.
> The idea is, to set a checkpoint, (store all checkpoints), and when its
> necassary you can revert to a checkpoint.
> Is this possible or is there another technique to realize this?
> Thank you in advance
> Alex
>|||Every change which is made, is encapsulated by an transaction and in
errorcase undone by the rollback or if success with commit.
So i want to set a checkpoint at start of editing the tables, and if
the user want to
undo his changes (created new datarows etc.) so the user can go to the
checkpoint, created at start editing.
So it is a single user instance all changes since last checkpoint
should be reverted.
A case of "higher level transaction"
Alex
Tibor Karaszi schrieb:
[vbcol=seagreen]
> SQL Server has "database snapshot" that you could potentially use. You can
restore from a database
> snapshot. But there can only exist *one* snapshot of a database if you wan
t to restore from a (that)
> snapshot.
> But perhaps it is much cleaner to begin a transaction and rollback?
> There's no built-in scheme where many users has many snapshots and you wan
t to revert to some
> certain snapshot for one of the users without affecting the other users (u
nless each user have their
> own database). How would SQL Server understand what data that belongs to u
ser A and revert only that
> data? Not to speak about relationships between tables.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "alex" <earthquake.de@.freenet.de> wrote in message
> news:1157460616.313916.180170@.d34g2000cwd.googlegroups.com...|||<DIV>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:e7Mln5O0GHA.772@.TK2MSFTNGP05.phx.gbl...</DIV>> SQL Server has "database
snapshot" that you could potentially use. You can restore from a database[vbcol=seagreen]
> snapshot. But there can only exist *one* snapshot of a database if you
> want to restore from a (that) snapshot.
> But perhaps it is much cleaner to begin a transaction and rollback?
> There's no built-in scheme where many users has many snapshots and you
> want to revert to some certain snapshot for one of the users without
> affecting the other users (unless each user have their own database). How
> would SQL Server understand what data that belongs to user A and revert
> only that data? Not to speak about relationships between tables.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "alex" <earthquake.de@.freenet.de> wrote in message
> news:1157460616.313916.180170@.d34g2000cwd.googlegroups.com...
A "marked transaction" can be your checkpoint. They are all stored in
msdb..logmarkhistory. You can restore a database backup and apply the
transaction log up through a marked transaction.
Using Marked Transactions (Full Recovery Model)
http://msdn2.microsoft.com/en-us/library/ms187014.aspx
David|||Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to prevent folks from
wasting time on already answered questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup), and
indicate that you've already posted elsewhere.
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"alex" <earthquake.de@.freenet.de> wrote in message
news:1157460616.313916.180170@.d34g2000cwd.googlegroups.com...
> Hi,
> i have a complex database. If a user (database is set to single user)
> make changes, and the user want do redo these changes, i need a kind of
> manual setted checkpoints. Such a checkpoint is set before changes
> made.
> The idea is, to set a checkpoint, (store all checkpoints), and when its
> necassary you can revert to a checkpoint.
> Is this possible or is there another technique to realize this?
> Thank you in advance
> Alex
>

Checkpoints

Hi,
i have a complex database. If a user (database is set to single user)
make changes, and the user want do redo these changes, i need a kind of
manual setted checkpoints. Such a checkpoint is set before changes
made.
The idea is, to set a checkpoint, (store all checkpoints), and when its
necassary you can revert to a checkpoint.
Is this possible or is there another technique to realize this?
Thank you in advance
AlexSQL Server has "database snapshot" that you could potentially use. You can restore from a database
snapshot. But there can only exist *one* snapshot of a database if you want to restore from a (that)
snapshot.
But perhaps it is much cleaner to begin a transaction and rollback?
There's no built-in scheme where many users has many snapshots and you want to revert to some
certain snapshot for one of the users without affecting the other users (unless each user have their
own database). How would SQL Server understand what data that belongs to user A and revert only that
data? Not to speak about relationships between tables.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"alex" <earthquake.de@.freenet.de> wrote in message
news:1157460616.313916.180170@.d34g2000cwd.googlegroups.com...
> Hi,
> i have a complex database. If a user (database is set to single user)
> make changes, and the user want do redo these changes, i need a kind of
> manual setted checkpoints. Such a checkpoint is set before changes
> made.
> The idea is, to set a checkpoint, (store all checkpoints), and when its
> necassary you can revert to a checkpoint.
> Is this possible or is there another technique to realize this?
> Thank you in advance
> Alex
>|||Every change which is made, is encapsulated by an transaction and in
errorcase undone by the rollback or if success with commit.
So i want to set a checkpoint at start of editing the tables, and if
the user want to
undo his changes (created new datarows etc.) so the user can go to the
checkpoint, created at start editing.
So it is a single user instance all changes since last checkpoint
should be reverted.
A case of "higher level transaction"
Alex
Tibor Karaszi schrieb:
> SQL Server has "database snapshot" that you could potentially use. You can restore from a database
> snapshot. But there can only exist *one* snapshot of a database if you want to restore from a (that)
> snapshot.
> But perhaps it is much cleaner to begin a transaction and rollback?
> There's no built-in scheme where many users has many snapshots and you want to revert to some
> certain snapshot for one of the users without affecting the other users (unless each user have their
> own database). How would SQL Server understand what data that belongs to user A and revert only that
> data? Not to speak about relationships between tables.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "alex" <earthquake.de@.freenet.de> wrote in message
> news:1157460616.313916.180170@.d34g2000cwd.googlegroups.com...
> > Hi,
> > i have a complex database. If a user (database is set to single user)
> > make changes, and the user want do redo these changes, i need a kind of
> >
> > manual setted checkpoints. Such a checkpoint is set before changes
> > made.
> >
> > The idea is, to set a checkpoint, (store all checkpoints), and when its
> > necassary you can revert to a checkpoint.
> > Is this possible or is there another technique to realize this?
> >
> > Thank you in advance
> > Alex
> >|||<DIV>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:e7Mln5O0GHA.772@.TK2MSFTNGP05.phx.gbl...</DIV>> SQL Server has "database
snapshot" that you could potentially use. You can restore from a database
> snapshot. But there can only exist *one* snapshot of a database if you
> want to restore from a (that) snapshot.
> But perhaps it is much cleaner to begin a transaction and rollback?
> There's no built-in scheme where many users has many snapshots and you
> want to revert to some certain snapshot for one of the users without
> affecting the other users (unless each user have their own database). How
> would SQL Server understand what data that belongs to user A and revert
> only that data? Not to speak about relationships between tables.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "alex" <earthquake.de@.freenet.de> wrote in message
> news:1157460616.313916.180170@.d34g2000cwd.googlegroups.com...
>> Hi,
>> i have a complex database. If a user (database is set to single user)
>> make changes, and the user want do redo these changes, i need a kind of
>> manual setted checkpoints. Such a checkpoint is set before changes
>> made.
>> The idea is, to set a checkpoint, (store all checkpoints), and when its
>> necassary you can revert to a checkpoint.
>> Is this possible or is there another technique to realize this?
A "marked transaction" can be your checkpoint. They are all stored in
msdb..logmarkhistory. You can restore a database backup and apply the
transaction log up through a marked transaction.
Using Marked Transactions (Full Recovery Model)
http://msdn2.microsoft.com/en-us/library/ms187014.aspx
David|||Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to prevent folks from
wasting time on already answered questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup), and
indicate that you've already posted elsewhere.
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"alex" <earthquake.de@.freenet.de> wrote in message
news:1157460616.313916.180170@.d34g2000cwd.googlegroups.com...
> Hi,
> i have a complex database. If a user (database is set to single user)
> make changes, and the user want do redo these changes, i need a kind of
> manual setted checkpoints. Such a checkpoint is set before changes
> made.
> The idea is, to set a checkpoint, (store all checkpoints), and when its
> necassary you can revert to a checkpoint.
> Is this possible or is there another technique to realize this?
> Thank you in advance
> Alex
>

Checklist for troubleshooting user connectivity problem?

I posted something about this a few days ago, but no luck.
It's getting posted again with a different wrapper in hopes of catching the
attention of somebody who knows connectivity.
The dilemma is this:
---
1) I rebuilt my PC a few weeks ago, reinstalling SQL Server-Developer Version in
the process.
2) An app that I've been developing is now giving me trouble when trying to open
up an ADO connection to it's SQL Server back end.
3) ODBC connections to the DB in question work fine, no problems...same User/PW
as the attempted ADO connection.
4) I can get to the DB via Query Analyzer using the same User/PW with no
problem.
5) The error message reads: "Server does not exist or access denied."
6) The DB definately exists because of items 3 & 4.
7) I think the User/PW is ok, also because of items 3 & 4.
8) The only protocol installed it TCP/IP.
9) The same connection string worked against the same DB before the rebuild. I
didn't want to post the string - thinking that it might confuse the issue by
misleading somebody into thinking there was some problem with it... but it seems
inevitable that somebody will want to see it, so here it is:
Provider=SQLOLEDB;SERVER=SAG;DATABASE=TRETS;UID=Trets;PWD=trets
Again, the string is unchanged from the time it was working. Exactly the same,
precisely the same...
10) The text of the error message is "Server does not exist or access denied."
11) If I change the "Provider=" in the connection string to "Provider=Melvyn", a
different error message pops whose verbage explicitly says the provider was not
found...so I'm guessing that the Provider=SQLOLEDB is alive and well.
---
Seems like that leaves something to do with ADO talking to the server...but
what.
Talked to the client today...to discuss a change they want made...
SO: I'm getting desperate...
Suggestons would be nice - but the main thing I'm trolling for there is some
sort of checklist for debugging connectivity problems. I'm guessing somebody
somewhere at Microsoft has developed such a thing but where?
--
PeteCresswell| 10) The text of the error message is "Server does not exist or access
denied."
--
Try this:
INF: Potential Causes of the "SQL Server Does Not Exist or Access Denied"
Error Message
http://support.microsoft.com/?id=328306
Hope this helps,
--
Eric Cárdenas
SQL Server support|||RE/
>INF: Potential Causes of the "SQL Server Does Not Exist or Access Denied"
>Error Message
>http://support.microsoft.com/?id=328306
>Hope this helps,
Thanks. It led me right to the problem.
Norton Personal Firewall...Turn it off, problem goes away. Turn it back on,
problem resurfaces. (KB article 319432)...
Now I just have to figure out how to determine the IP address and TCP/IP port
number that my SQL Server is using and config the firewall accordingly...
For now, I just disconnect from the internet and turn the firewall off.
That was a loooooong week...
--
PeteCresswell|||| >INF: Potential Causes of the "SQL Server Does Not Exist or Access
Denied"
| >Error Message
| >http://support.microsoft.com/?id=328306
| >
|
| Thanks. It led me right to the problem.
|
| Norton Personal Firewall...Turn it off, problem goes away. Turn it
back on,
| problem resurfaces. (KB article 319432)...
|
| Now I just have to figure out how to determine the IP address and TCP/IP
port
| number that my SQL Server is using and config the firewall accordingly...
|
| For now, I just disconnect from the internet and turn the firewall off.
--
Hi Pete,
You can "ping" the SQL Server machine to determine the IP address. The
default TCP port for SQL Server is 1433.
Hope this helps,
--
Eric Cárdenas
SQL Server support|||RE/
>You can "ping" the SQL Server machine to determine the IP address. The
>default TCP port for SQL Server is 1433.
Thanks. I think there's something more going on there though. Haven't really
*read* the articles yet, but the picture I get from a quick scan is that there's
some other entity out there - or at least the server looks like something else -
when the initial contact to validate the user/pw occurs.
When I tried it against the firewall, the offender was "LocalHost", with what
looks like a port number ("2323") and a TCP/IP address of 127.0.0.1.
I just have to find some time and dig into it a little more...
--
PeteCresswell

Checking User & Expire of Update Possibility (by Trigger), How to?

Hi,

I have Table (RatesTable) every user can insert records to this table, and all users can see this records, this table contain the following columns:

RateID, Service, Rate, DateTime, User

Want I want is a code (trigger) in the database can do the following:

If user perform an Update request the code will check:

- if this recored inserted by the same user update command will be execute.

- if this recored inserted by other user: update command will not execute and return message.

- if more than 5 minutes passed the update command will not be execute and return message.

Yes, this can be done with a trigger but it really would be better for the update statement itself to decide whether or not the update is allowed by adding either a WHERE condition or an AND condition to the update statement to decide whether or not to allow the update.

Change the update statement from something like:

update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId

to something like

update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId
and user = @.currentUser
and dateTime >= dateadd (mi, -5, getdate())

|||

Thanks Kent Waldrop Ap07, but the problem is my all program use Datasets created by Data Source Configration Wizzard, is it possible to to add your code to the Dataset Designer?

and what if I want to put this code in Trigger?

thnx again,,,

|||Any help?|||

Here is a TRIGGER idea. While I agree with Kent that changing the UPDATE statement is a better option, I know from expereince that it is not always the solution that works.

The code idea below relies upon [User] being captured with the system_user system function (domain/username).

Code Snippet

CREATE TRIGGER tr_RatesTable_U_UserOnly
ON RatesTable
FOR UPDATE
AS
IF @.@.ROWCOUNT = 0
RETURN

DECLARE @.User varchar(50)

IF EXISTS
( SELECT *
FROM inserted
WHERE User <> system_user
)
BEGIN
ROLLBACK
RAISERROR('Cannot UpDate This Record', 16, 1)
RETURN
END

GO

|||

Hi,

I am not familiar with the Wizard stuff, but I would expect that it does not cover holding the logic for that. But should have a look on the resulting queries the wizard produces, maybe you are able to tweak the Update statement to cover your logic. Anyway, using a trigger could be another option:

CREATE TRIGGER TRG_UPD_SomeTable
ON SomeTable
FOR UPDATE
AS
BEGIN

IF NOT EXISTS(SELECT * From INSERTED WHERE User = SUSER_NAME AND DateTime <= DATEADD(s,-5,GETDATE()))
RAISERROR('Update not allowed',16,1)

END

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de

Checking User & Expire of Update Possibility (by Trigger), How to?

Hi,

I have Table (RatesTable) every user can insert records to this table, and all users can see this records, this table contain the following columns:

RateID, Service, Rate, DateTime, User

Want I want is a code (trigger) in the database can do the following:

If user perform an Update request the code will check:

- if this recored inserted by the same user update command will be execute.

- if this recored inserted by other user: update command will not execute and return message.

- if more than 5 minutes passed the update command will not be execute and return message.

Yes, this can be done with a trigger but it really would be better for the update statement itself to decide whether or not the update is allowed by adding either a WHERE condition or an AND condition to the update statement to decide whether or not to allow the update.

Change the update statement from something like:

update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId

to something like

update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId
and user = @.currentUser
and dateTime >= dateadd (mi, -5, getdate())

|||

Thanks Kent Waldrop Ap07, but the problem is my all program use Datasets created by Data Source Configration Wizzard, is it possible to to add your code to the Dataset Designer?

and what if I want to put this code in Trigger?

thnx again,,,

|||Any help?|||

Here is a TRIGGER idea. While I agree with Kent that changing the UPDATE statement is a better option, I know from expereince that it is not always the solution that works.

The code idea below relies upon [User] being captured with the system_user system function (domain/username).

Code Snippet

CREATE TRIGGER tr_RatesTable_U_UserOnly
ON RatesTable
FOR UPDATE
AS
IF @.@.ROWCOUNT = 0
RETURN

DECLARE @.User varchar(50)

IF EXISTS
( SELECT *
FROM inserted
WHERE User <> system_user
)
BEGIN
ROLLBACK
RAISERROR('Cannot UpDate This Record', 16, 1)
RETURN
END

GO

|||

Hi,

I am not familiar with the Wizard stuff, but I would expect that it does not cover holding the logic for that. But should have a look on the resulting queries the wizard produces, maybe you are able to tweak the Update statement to cover your logic. Anyway, using a trigger could be another option:

CREATE TRIGGER TRG_UPD_SomeTable
ON SomeTable
FOR UPDATE
AS
BEGIN

IF NOT EXISTS(SELECT * From INSERTED WHERE User = SUSER_NAME AND DateTime <= DATEADD(s,-5,GETDATE()))
RAISERROR('Update not allowed',16,1)

END

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de

Checking The Users Server Role

I would like to determine if a particular user has sysadmin server
role. Is there a way to do this via the connection string? Currently
our code checks if a login is valid using SQLDriverConnect, however we
need to be certain that the user can login and modify the schema.

Is it possible to fetch a user's server role to determine if it has a
sysadmin server role?Look for the IS_SRVROLEMEMBER function in Books Online.

Razvan|||Thanks.

Checking last user Identity on remote server

Is it possible to get the latest inserted Identity value on a remote server?
(The following gives a NULL result:
select ident_current('[my_linked_server].thedatabase.dbo.thetable')
)@.@.IDENTITY (assuming you mean server wide irrespective of the user\ connections etc).

HTH|||Beg your pardon - @.@.Identity is specific to session - sorry - didn't read every last line of BoL before hitting Submit.|||@.@.IDENTITY (assuming you mean server wide irrespective of the user\ connections etc).
HTH

Are you sure? I think that's what I tried first. Doesn't
it give the latest Id value used in the server where
you are (so to speak), not the remote server?|||Finally, I understood this is the way to do it. Just as feedback.

DECLARE @.return_status int
EXEC @.return_status = MyLinkedServer.the_database.dbo.a_stored_procedure
SELECT 'Return Status' = @.return_stat

The SP "a_stored_procedure" is stored on the linked server. It returns
Ident_Current for the appropriate table, using RETURN, simply.

Checking if user has a login

Greetings -
I'm working with a SQL Server 2000 database via an Access front-end and I'd
like to check if the user has a login (via a pass through query I imagine) on
the database before attempting to pass the connection string. The user
login name will be the same as their PC login name which I can pick up. Any
assistance is appreciated.
Thanks,
-b
Why not just try to login? If the user doesn't have a login, your login
attempt will fail and that will tell you all you want to know. You just have
to catch the error in the Access front-end.
It wouldn't be very secure if you could find out information about login
credentials without actually being logged in.
Jacco Schalkwijk
SQL Server MVP
"Robert Pfister" <rpfister@.hotmail.com> wrote in message
news:bYOdnSXvu-J5v2vcRVn-2w@.giganews.com...
> Greetings -
> I'm working with a SQL Server 2000 database via an Access front-end and
> I'd
> like to check if the user has a login (via a pass through query I imagine)
> on
> the database before attempting to pass the connection string. The user
> login name will be the same as their PC login name which I can pick up.
> Any
> assistance is appreciated.
> Thanks,
> -b
>

Checking if user has a login

Greetings -
I'm working with a SQL Server 2000 database via an Access front-end and I'd
like to check if the user has a login (via a pass through query I imagine) o
n
the database before attempting to pass the connection string. The user
login name will be the same as their PC login name which I can pick up. Any
assistance is appreciated.
Thanks,
-bWhy not just try to login? If the user doesn't have a login, your login
attempt will fail and that will tell you all you want to know. You just have
to catch the error in the Access front-end.
It wouldn't be very secure if you could find out information about login
credentials without actually being logged in.
Jacco Schalkwijk
SQL Server MVP
"Robert Pfister" <rpfister@.hotmail.com> wrote in message
news:bYOdnSXvu-J5v2vcRVn-2w@.giganews.com...
> Greetings -
> I'm working with a SQL Server 2000 database via an Access front-end and
> I'd
> like to check if the user has a login (via a pass through query I imagine)
> on
> the database before attempting to pass the connection string. The user
> login name will be the same as their PC login name which I can pick up.
> Any
> assistance is appreciated.
> Thanks,
> -b
>

Checking if user has a login

Greetings -
I'm working with a SQL Server 2000 database via an Access front-end and I'd
like to check if the user has a login (via a pass through query I imagine) o
n
the database before attempting to pass the connection string. The user
login name will be the same as their PC login name which I can pick up. Any
assistance is appreciated.
Thanks,
-bWhy not just try to login? If the user doesn't have a login, your login
attempt will fail and that will tell you all you want to know. You just have
to catch the error in the Access front-end.
It wouldn't be very secure if you could find out information about login
credentials without actually being logged in.
Jacco Schalkwijk
SQL Server MVP
"Robert Pfister" <rpfister@.hotmail.com> wrote in message
news:bYOdnSXvu-J5v2vcRVn-2w@.giganews.com...
> Greetings -
> I'm working with a SQL Server 2000 database via an Access front-end and
> I'd
> like to check if the user has a login (via a pass through query I imagine)
> on
> the database before attempting to pass the connection string. The user
> login name will be the same as their PC login name which I can pick up.
> Any
> assistance is appreciated.
> Thanks,
> -b
>

Checking Format of a string in Report Parameter

Is there a way to find out if the user entered the date in correct format for the report parameter? For example, I am using string data type and requring the user to enter date in yyyy/mm/dd format. Can I capture and prompt the user accordingly if the date string is not provided in the above format?

Why dont you create the parameter in a Datetime format and let the user enter it in a date time format. After that you use the functions to convert itto a string in your desired format.

|||

You might be able to take advantage of the isDate() function; however, at the moment I don't see how to apply it.

checking for last time a SQL 2000 user logged in?

Hi,
Is there a way to tell the last time a given user logged into a SQL 2000
Server?
thanks,
Joeyou could query sysprocesses and look at the login_time for the user
such as select login_time from sysprocesses where loginame = 'the logged on
user'
"Joe" <JoeD777@.lycos.com> wrote in message
news:ugshww4YEHA.2432@.tk2msftngp13.phx.gbl...
> Hi,
> Is there a way to tell the last time a given user logged into a SQL
2000
> Server?
> thanks,
> Joe
>|||Thanks Hassan but, that isn't really what I was looking for. Lets say I have
a user or two, who I don't have logged on in six months. Is there a system
table that I can query to see if it really has been six months since a users
last logon to my SQL Server?
I have an number of development databases/users and I want to stay on top of
old, unused accounts.
Thanks,
Joe
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eBfpT$4YEHA.3432@.TK2MSFTNGP10.phx.gbl...
> you could query sysprocesses and look at the login_time for the user
> such as select login_time from sysprocesses where loginame = 'the logged
on
> user'
>
> "Joe" <JoeD777@.lycos.com> wrote in message
> news:ugshww4YEHA.2432@.tk2msftngp13.phx.gbl...
> 2000
>|||Trickier, it is possible to set login auditing to all which will record all
successful and unsuccesful logins however this does have a tendency to fill
up the SQL errorlog and the NT Application log. Either that or some kind of
trace that runs everyday, loads into a table from which you extract the
login counts and then restarts itself (you need to stop the trace to load it
into a table)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Joe" <JoeD777@.lycos.com> wrote in message
news:uGXTrI5YEHA.3304@.TK2MSFTNGP09.phx.gbl...
> Thanks Hassan but, that isn't really what I was looking for. Lets say I
have
> a user or two, who I don't have logged on in six months. Is there a system
> table that I can query to see if it really has been six months since a
users
> last logon to my SQL Server?
> I have an number of development databases/users and I want to stay on top
of
> old, unused accounts.
> Thanks,
> Joe
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:eBfpT$4YEHA.3432@.TK2MSFTNGP10.phx.gbl...
> on
SQL[vbcol=seagreen]
>

checking dates with triggers or constraints

Hi,

I have a table that contains various data with a start and end date. When a user enters the information I need to check that the end date is older than the start date.

What is the easiest way to do this - using a trigger?

And is the code below correct ?

CREATE TRIGGER checkdate
ON Prodn_Problem
FOR INSERT,UPDATE
AS
DECLARE @.start_date datetime
SELECT @.start_date = (SELECT startdate FROM inserted)
DECLARE @.end_date datetime
SELECT @.end_date = (SELECT enddate FROM inserted)
BEGIN
IF(@.end_date < @.start_date)
ROLLBACK
END

Thanks.By using a trigger, you check the data when it's already inserted or updated in the table. When you use a check constraint you can verify before the information is entered. Another method is to create a stored procedure which does the checking an let you users enter information through this stored procedure.

CHECKDB table exclusion ?

Hi There

I have checked CHECKDB syntax in BOL, i just want to be sure.

In there no way to run CHECKDB on a user database and exclude certain tables, in SS2000.

I have 2 really huge tables , over a billion rows. Nightly i want to run CHECKDB on the database but i do NOT want CHECKTABLE run on these 2 tables as it takes hours.

Or is my only option to instead of running CHECKDB, writing a script that loops through user tables excluding these and running CHECKTABLE. If this is my only option can it be done with a cursor looping through user tables and running CHECKTABLE on each one?

Thanx

Your only option is to run CHECKTABLE with the specific tables. CHECKDB doesn't have any option to filter tables.

CHECKDB on master ?

Can I get data loss from running CHECKDB on master (or user databases)
if the consistency errors are in indexes?
Here are the results of CHECKDB on master:
Server: Msg 8928, Level 16, State 1, Line 2
Object ID 12, index ID 0: Page (1:364) could not be processed. See
other errors for details.
Server: Msg 8944, Level 16, State 1, Line 2
Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
(ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
DBCC results for 'master'.
DBCC results for 'sysobjects'.
There are 1272 rows in 23 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 104 rows in 4 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 4884 rows in 85 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 2024 rows in 952 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 788 rows in 4 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 17 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 5282 rows in 28 pages for object 'sysdepends'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'sysdepends' (object ID 12).
DBCC results for 'sysreferences'.
There are 0 rows in 0 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysdatabases'.
There are 309 rows in 13 pages for object 'sysdatabases'.
DBCC results for 'sysxlogins'.
There are 465 rows in 14 pages for object 'sysxlogins'.
DBCC results for 'sysdevices'.
There are 8 rows in 1 pages for object 'sysdevices'.
DBCC results for 'sysmessages'.
There are 3831 rows in 165 pages for object 'sysmessages'.
DBCC results for 'sysconfigures'.
There are 38 rows in 1 pages for object 'sysconfigures'.
DBCC results for 'sysservers'.
There are 7 rows in 1 pages for object 'sysservers'.
DBCC results for 'syslanguages'.
There are 33 rows in 3 pages for object 'syslanguages'.
DBCC results for 'syscharsets'.
There are 114 rows in 33 pages for object 'syscharsets'.
DBCC results for 'sysaltfiles'.
There are 618 rows in 123 pages for object 'sysaltfiles'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'spt_monitor'.
There are 1 rows in 1 pages for object 'spt_monitor'.
DBCC results for 'spt_values'.
There are 730 rows in 7 pages for object 'spt_values'.
DBCC results for 'spt_fallback_db'.
There are 0 rows in 0 pages for object 'spt_fallback_db'.
DBCC results for 'spt_fallback_dev'.
There are 0 rows in 0 pages for object 'spt_fallback_dev'.
DBCC results for 'spt_fallback_usg'.
There are 0 rows in 0 pages for object 'spt_fallback_usg'.
DBCC results for 'spt_provider_types'.
There are 25 rows in 1 pages for object 'spt_provider_types'.
DBCC results for 'spt_datatype_info_ext'.
There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
DBCC results for 'MSreplication_options'.
There are 2 rows in 1 pages for object 'MSreplication_options'.
DBCC results for 'spt_datatype_info'.
There are 36 rows in 1 pages for object 'spt_datatype_info'.
DBCC results for 'spt_server_info'.
There are 29 rows in 1 pages for object 'spt_server_info'.
CHECKDB found 0 allocation errors and 2 consistency errors in database
'master'.
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKDB (master ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.If the corruption is limited to non-clustered indexes (with index ID > 1),
then you won't lose data. In your case, the corrupt page is in the
sysdepends heap so you will lose data from that table. This system table
stores info about the dependencies between views, triggers, sprocs and the
objects they reference. I'm not sure how the system will behave if you run
repair - which will delete the page (because the internal pointer in the
record that points to the variable-length column offset table is pointing
off the end of the record - and so the page could be grossly corrupt).
A far better course of action is to run restore in this case. Do you have a
valid backup?
See my blog post at
https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/07/620319.aspx
for more details.
Thanks
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <daveg.01@.gmail.com> wrote in message
news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
> Can I get data loss from running CHECKDB on master (or user databases)
> if the consistency errors are in indexes?
>
> Here are the results of CHECKDB on master:
> Server: Msg 8928, Level 16, State 1, Line 2
> Object ID 12, index ID 0: Page (1:364) could not be processed. See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 2
> Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> DBCC results for 'master'.
> DBCC results for 'sysobjects'.
> There are 1272 rows in 23 pages for object 'sysobjects'.
> DBCC results for 'sysindexes'.
> There are 104 rows in 4 pages for object 'sysindexes'.
> DBCC results for 'syscolumns'.
> There are 4884 rows in 85 pages for object 'syscolumns'.
> DBCC results for 'systypes'.
> There are 26 rows in 1 pages for object 'systypes'.
> DBCC results for 'syscomments'.
> There are 2024 rows in 952 pages for object 'syscomments'.
> DBCC results for 'sysfiles1'.
> There are 2 rows in 1 pages for object 'sysfiles1'.
> DBCC results for 'syspermissions'.
> There are 788 rows in 4 pages for object 'syspermissions'.
> DBCC results for 'sysusers'.
> There are 17 rows in 1 pages for object 'sysusers'.
> DBCC results for 'sysproperties'.
> There are 0 rows in 0 pages for object 'sysproperties'.
> DBCC results for 'sysdepends'.
> There are 5282 rows in 28 pages for object 'sysdepends'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'sysdepends' (object ID 12).
> DBCC results for 'sysreferences'.
> There are 0 rows in 0 pages for object 'sysreferences'.
> DBCC results for 'sysfulltextcatalogs'.
> There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> DBCC results for 'sysfulltextnotify'.
> There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> DBCC results for 'sysdatabases'.
> There are 309 rows in 13 pages for object 'sysdatabases'.
> DBCC results for 'sysxlogins'.
> There are 465 rows in 14 pages for object 'sysxlogins'.
> DBCC results for 'sysdevices'.
> There are 8 rows in 1 pages for object 'sysdevices'.
> DBCC results for 'sysmessages'.
> There are 3831 rows in 165 pages for object 'sysmessages'.
> DBCC results for 'sysconfigures'.
> There are 38 rows in 1 pages for object 'sysconfigures'.
> DBCC results for 'sysservers'.
> There are 7 rows in 1 pages for object 'sysservers'.
> DBCC results for 'syslanguages'.
> There are 33 rows in 3 pages for object 'syslanguages'.
> DBCC results for 'syscharsets'.
> There are 114 rows in 33 pages for object 'syscharsets'.
> DBCC results for 'sysaltfiles'.
> There are 618 rows in 123 pages for object 'sysaltfiles'.
> DBCC results for 'sysfilegroups'.
> There are 1 rows in 1 pages for object 'sysfilegroups'.
> DBCC results for 'spt_monitor'.
> There are 1 rows in 1 pages for object 'spt_monitor'.
> DBCC results for 'spt_values'.
> There are 730 rows in 7 pages for object 'spt_values'.
> DBCC results for 'spt_fallback_db'.
> There are 0 rows in 0 pages for object 'spt_fallback_db'.
> DBCC results for 'spt_fallback_dev'.
> There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> DBCC results for 'spt_fallback_usg'.
> There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> DBCC results for 'spt_provider_types'.
> There are 25 rows in 1 pages for object 'spt_provider_types'.
> DBCC results for 'spt_datatype_info_ext'.
> There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> DBCC results for 'MSreplication_options'.
> There are 2 rows in 1 pages for object 'MSreplication_options'.
> DBCC results for 'spt_datatype_info'.
> There are 36 rows in 1 pages for object 'spt_datatype_info'.
> DBCC results for 'spt_server_info'.
> There are 29 rows in 1 pages for object 'spt_server_info'.
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'master'.
> repair_allow_data_loss is the minimum repair level for the errors found
> by DBCC CHECKDB (master ).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>|||Thanks Paul!!!!!
I am trying to understand how to interpret the results of CHECKDB. I
can't seem to find enough detail to understand what is going on in BOL.
I think we are going to open a ticket with Microsoft support to assist
us.
Our latest backup overlaps the disk failure. Based off time, it looks
like the backup was 80-90% complete before the disk went bad.
The next backup we have is over a week old. It looks like our backup
to tape got bumped by other jobs which ran over :(
Paul S Randal [MS] wrote:
> If the corruption is limited to non-clustered indexes (with index ID > 1),
> then you won't lose data. In your case, the corrupt page is in the
> sysdepends heap so you will lose data from that table. This system table
> stores info about the dependencies between views, triggers, sprocs and the
> objects they reference. I'm not sure how the system will behave if you run
> repair - which will delete the page (because the internal pointer in the
> record that points to the variable-length column offset table is pointing
> off the end of the record - and so the page could be grossly corrupt).
> A far better course of action is to run restore in this case. Do you have a
> valid backup?
> See my blog post at
> https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/07/620319.aspx
> for more details.
> Thanks
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dave" <daveg.01@.gmail.com> wrote in message
> news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
> > Can I get data loss from running CHECKDB on master (or user databases)
> > if the consistency errors are in indexes?
> >
> >
> > Here are the results of CHECKDB on master:
> >
> > Server: Msg 8928, Level 16, State 1, Line 2
> > Object ID 12, index ID 0: Page (1:364) could not be processed. See
> > other errors for details.
> > Server: Msg 8944, Level 16, State 1, Line 2
> > Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> > DBCC results for 'master'.
> > DBCC results for 'sysobjects'.
> > There are 1272 rows in 23 pages for object 'sysobjects'.
> > DBCC results for 'sysindexes'.
> > There are 104 rows in 4 pages for object 'sysindexes'.
> > DBCC results for 'syscolumns'.
> > There are 4884 rows in 85 pages for object 'syscolumns'.
> > DBCC results for 'systypes'.
> > There are 26 rows in 1 pages for object 'systypes'.
> > DBCC results for 'syscomments'.
> > There are 2024 rows in 952 pages for object 'syscomments'.
> > DBCC results for 'sysfiles1'.
> > There are 2 rows in 1 pages for object 'sysfiles1'.
> > DBCC results for 'syspermissions'.
> > There are 788 rows in 4 pages for object 'syspermissions'.
> > DBCC results for 'sysusers'.
> > There are 17 rows in 1 pages for object 'sysusers'.
> > DBCC results for 'sysproperties'.
> > There are 0 rows in 0 pages for object 'sysproperties'.
> > DBCC results for 'sysdepends'.
> > There are 5282 rows in 28 pages for object 'sysdepends'.
> > CHECKDB found 0 allocation errors and 2 consistency errors in table
> > 'sysdepends' (object ID 12).
> > DBCC results for 'sysreferences'.
> > There are 0 rows in 0 pages for object 'sysreferences'.
> > DBCC results for 'sysfulltextcatalogs'.
> > There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> > DBCC results for 'sysfulltextnotify'.
> > There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> > DBCC results for 'sysdatabases'.
> > There are 309 rows in 13 pages for object 'sysdatabases'.
> > DBCC results for 'sysxlogins'.
> > There are 465 rows in 14 pages for object 'sysxlogins'.
> > DBCC results for 'sysdevices'.
> > There are 8 rows in 1 pages for object 'sysdevices'.
> > DBCC results for 'sysmessages'.
> > There are 3831 rows in 165 pages for object 'sysmessages'.
> > DBCC results for 'sysconfigures'.
> > There are 38 rows in 1 pages for object 'sysconfigures'.
> > DBCC results for 'sysservers'.
> > There are 7 rows in 1 pages for object 'sysservers'.
> > DBCC results for 'syslanguages'.
> > There are 33 rows in 3 pages for object 'syslanguages'.
> > DBCC results for 'syscharsets'.
> > There are 114 rows in 33 pages for object 'syscharsets'.
> > DBCC results for 'sysaltfiles'.
> > There are 618 rows in 123 pages for object 'sysaltfiles'.
> > DBCC results for 'sysfilegroups'.
> > There are 1 rows in 1 pages for object 'sysfilegroups'.
> > DBCC results for 'spt_monitor'.
> > There are 1 rows in 1 pages for object 'spt_monitor'.
> > DBCC results for 'spt_values'.
> > There are 730 rows in 7 pages for object 'spt_values'.
> > DBCC results for 'spt_fallback_db'.
> > There are 0 rows in 0 pages for object 'spt_fallback_db'.
> > DBCC results for 'spt_fallback_dev'.
> > There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> > DBCC results for 'spt_fallback_usg'.
> > There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> > DBCC results for 'spt_provider_types'.
> > There are 25 rows in 1 pages for object 'spt_provider_types'.
> > DBCC results for 'spt_datatype_info_ext'.
> > There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> > DBCC results for 'MSreplication_options'.
> > There are 2 rows in 1 pages for object 'MSreplication_options'.
> > DBCC results for 'spt_datatype_info'.
> > There are 36 rows in 1 pages for object 'spt_datatype_info'.
> > DBCC results for 'spt_server_info'.
> > There are 29 rows in 1 pages for object 'spt_server_info'.
> > CHECKDB found 0 allocation errors and 2 consistency errors in database
> > 'master'.
> > repair_allow_data_loss is the minimum repair level for the errors found
> > by DBCC CHECKDB (master ).
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >|||Before you undertake the effort of restoring your currently damaged master db
from backup a) make sure no more disk problems are happening - else you may
just get corruption again. If raid were the disks successfully rebuild
/and/or have they past consistency check after rebuild?
Restore your master backup first as a user DB e.g. master2 - watch out for
file location and physical filename - e.g just create a separate diretory.
Once restored run check db through it to see whether you already had
problems at the time this bacup was taken. If yes
Lookup rebuildm in BOL and on msdn - which will allow you to rebuild your
system databases.
Corruption in master and model are frowned upon and PSS will typically
recommend to restore from backup. In case you dont have a recent backup of
master. Make a backup to disk of your master and msdb before going any
further now. The rest is fairly cookie cutter - detach your user DBs script
out logins (see also sp_help_revlogin) run rebuildm bring back your logins -
attach userdbs back and restore the backup of msdb you took above.
Good luck.
"Dave" wrote:
> Can I get data loss from running CHECKDB on master (or user databases)
> if the consistency errors are in indexes?
>
> Here are the results of CHECKDB on master:
> Server: Msg 8928, Level 16, State 1, Line 2
> Object ID 12, index ID 0: Page (1:364) could not be processed. See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 2
> Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> DBCC results for 'master'.
> DBCC results for 'sysobjects'.
> There are 1272 rows in 23 pages for object 'sysobjects'.
> DBCC results for 'sysindexes'.
> There are 104 rows in 4 pages for object 'sysindexes'.
> DBCC results for 'syscolumns'.
> There are 4884 rows in 85 pages for object 'syscolumns'.
> DBCC results for 'systypes'.
> There are 26 rows in 1 pages for object 'systypes'.
> DBCC results for 'syscomments'.
> There are 2024 rows in 952 pages for object 'syscomments'.
> DBCC results for 'sysfiles1'.
> There are 2 rows in 1 pages for object 'sysfiles1'.
> DBCC results for 'syspermissions'.
> There are 788 rows in 4 pages for object 'syspermissions'.
> DBCC results for 'sysusers'.
> There are 17 rows in 1 pages for object 'sysusers'.
> DBCC results for 'sysproperties'.
> There are 0 rows in 0 pages for object 'sysproperties'.
> DBCC results for 'sysdepends'.
> There are 5282 rows in 28 pages for object 'sysdepends'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'sysdepends' (object ID 12).
> DBCC results for 'sysreferences'.
> There are 0 rows in 0 pages for object 'sysreferences'.
> DBCC results for 'sysfulltextcatalogs'.
> There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> DBCC results for 'sysfulltextnotify'.
> There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> DBCC results for 'sysdatabases'.
> There are 309 rows in 13 pages for object 'sysdatabases'.
> DBCC results for 'sysxlogins'.
> There are 465 rows in 14 pages for object 'sysxlogins'.
> DBCC results for 'sysdevices'.
> There are 8 rows in 1 pages for object 'sysdevices'.
> DBCC results for 'sysmessages'.
> There are 3831 rows in 165 pages for object 'sysmessages'.
> DBCC results for 'sysconfigures'.
> There are 38 rows in 1 pages for object 'sysconfigures'.
> DBCC results for 'sysservers'.
> There are 7 rows in 1 pages for object 'sysservers'.
> DBCC results for 'syslanguages'.
> There are 33 rows in 3 pages for object 'syslanguages'.
> DBCC results for 'syscharsets'.
> There are 114 rows in 33 pages for object 'syscharsets'.
> DBCC results for 'sysaltfiles'.
> There are 618 rows in 123 pages for object 'sysaltfiles'.
> DBCC results for 'sysfilegroups'.
> There are 1 rows in 1 pages for object 'sysfilegroups'.
> DBCC results for 'spt_monitor'.
> There are 1 rows in 1 pages for object 'spt_monitor'.
> DBCC results for 'spt_values'.
> There are 730 rows in 7 pages for object 'spt_values'.
> DBCC results for 'spt_fallback_db'.
> There are 0 rows in 0 pages for object 'spt_fallback_db'.
> DBCC results for 'spt_fallback_dev'.
> There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> DBCC results for 'spt_fallback_usg'.
> There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> DBCC results for 'spt_provider_types'.
> There are 25 rows in 1 pages for object 'spt_provider_types'.
> DBCC results for 'spt_datatype_info_ext'.
> There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> DBCC results for 'MSreplication_options'.
> There are 2 rows in 1 pages for object 'MSreplication_options'.
> DBCC results for 'spt_datatype_info'.
> There are 36 rows in 1 pages for object 'spt_datatype_info'.
> DBCC results for 'spt_server_info'.
> There are 29 rows in 1 pages for object 'spt_server_info'.
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'master'.
> repair_allow_data_loss is the minimum repair level for the errors found
> by DBCC CHECKDB (master ).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>|||Look in MSDN, and the recent SQL Server 2000 downloadable BOL refreshes. I
documented every error message that CHECKDB can return (SQL Server 2005 ones
are done but haven't made it out to the wild yet)
Can you select * from sysdepends in master? You may be lucky and there's
nothing there, in which case (once you've made sure you know exactly why the
problem occured and have taken steps to make sure it doesn't happen again),
you should be able to get away with running repair rather than going back to
your old backups. And, of course, get a much better backup strategy.
Thanks
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <daveg.01@.gmail.com> wrote in message
news:1150827802.442472.143860@.u72g2000cwu.googlegroups.com...
> Thanks Paul!!!!!
> I am trying to understand how to interpret the results of CHECKDB. I
> can't seem to find enough detail to understand what is going on in BOL.
> I think we are going to open a ticket with Microsoft support to assist
> us.
> Our latest backup overlaps the disk failure. Based off time, it looks
> like the backup was 80-90% complete before the disk went bad.
> The next backup we have is over a week old. It looks like our backup
> to tape got bumped by other jobs which ran over :(
>
> Paul S Randal [MS] wrote:
>> If the corruption is limited to non-clustered indexes (with index ID >
>> 1),
>> then you won't lose data. In your case, the corrupt page is in the
>> sysdepends heap so you will lose data from that table. This system table
>> stores info about the dependencies between views, triggers, sprocs and
>> the
>> objects they reference. I'm not sure how the system will behave if you
>> run
>> repair - which will delete the page (because the internal pointer in the
>> record that points to the variable-length column offset table is pointing
>> off the end of the record - and so the page could be grossly corrupt).
>> A far better course of action is to run restore in this case. Do you have
>> a
>> valid backup?
>> See my blog post at
>> https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/07/620319.aspx
>> for more details.
>> Thanks
>> --
>> Paul Randal
>> Lead Program Manager, Microsoft SQL Server Storage Engine
>> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Dave" <daveg.01@.gmail.com> wrote in message
>> news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
>> > Can I get data loss from running CHECKDB on master (or user databases)
>> > if the consistency errors are in indexes?
>> >
>> >
>> > Here are the results of CHECKDB on master:
>> >
>> > Server: Msg 8928, Level 16, State 1, Line 2
>> > Object ID 12, index ID 0: Page (1:364) could not be processed. See
>> > other errors for details.
>> > Server: Msg 8944, Level 16, State 1, Line 2
>> > Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
>> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
>> > DBCC results for 'master'.
>> > DBCC results for 'sysobjects'.
>> > There are 1272 rows in 23 pages for object 'sysobjects'.
>> > DBCC results for 'sysindexes'.
>> > There are 104 rows in 4 pages for object 'sysindexes'.
>> > DBCC results for 'syscolumns'.
>> > There are 4884 rows in 85 pages for object 'syscolumns'.
>> > DBCC results for 'systypes'.
>> > There are 26 rows in 1 pages for object 'systypes'.
>> > DBCC results for 'syscomments'.
>> > There are 2024 rows in 952 pages for object 'syscomments'.
>> > DBCC results for 'sysfiles1'.
>> > There are 2 rows in 1 pages for object 'sysfiles1'.
>> > DBCC results for 'syspermissions'.
>> > There are 788 rows in 4 pages for object 'syspermissions'.
>> > DBCC results for 'sysusers'.
>> > There are 17 rows in 1 pages for object 'sysusers'.
>> > DBCC results for 'sysproperties'.
>> > There are 0 rows in 0 pages for object 'sysproperties'.
>> > DBCC results for 'sysdepends'.
>> > There are 5282 rows in 28 pages for object 'sysdepends'.
>> > CHECKDB found 0 allocation errors and 2 consistency errors in table
>> > 'sysdepends' (object ID 12).
>> > DBCC results for 'sysreferences'.
>> > There are 0 rows in 0 pages for object 'sysreferences'.
>> > DBCC results for 'sysfulltextcatalogs'.
>> > There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
>> > DBCC results for 'sysfulltextnotify'.
>> > There are 0 rows in 0 pages for object 'sysfulltextnotify'.
>> > DBCC results for 'sysdatabases'.
>> > There are 309 rows in 13 pages for object 'sysdatabases'.
>> > DBCC results for 'sysxlogins'.
>> > There are 465 rows in 14 pages for object 'sysxlogins'.
>> > DBCC results for 'sysdevices'.
>> > There are 8 rows in 1 pages for object 'sysdevices'.
>> > DBCC results for 'sysmessages'.
>> > There are 3831 rows in 165 pages for object 'sysmessages'.
>> > DBCC results for 'sysconfigures'.
>> > There are 38 rows in 1 pages for object 'sysconfigures'.
>> > DBCC results for 'sysservers'.
>> > There are 7 rows in 1 pages for object 'sysservers'.
>> > DBCC results for 'syslanguages'.
>> > There are 33 rows in 3 pages for object 'syslanguages'.
>> > DBCC results for 'syscharsets'.
>> > There are 114 rows in 33 pages for object 'syscharsets'.
>> > DBCC results for 'sysaltfiles'.
>> > There are 618 rows in 123 pages for object 'sysaltfiles'.
>> > DBCC results for 'sysfilegroups'.
>> > There are 1 rows in 1 pages for object 'sysfilegroups'.
>> > DBCC results for 'spt_monitor'.
>> > There are 1 rows in 1 pages for object 'spt_monitor'.
>> > DBCC results for 'spt_values'.
>> > There are 730 rows in 7 pages for object 'spt_values'.
>> > DBCC results for 'spt_fallback_db'.
>> > There are 0 rows in 0 pages for object 'spt_fallback_db'.
>> > DBCC results for 'spt_fallback_dev'.
>> > There are 0 rows in 0 pages for object 'spt_fallback_dev'.
>> > DBCC results for 'spt_fallback_usg'.
>> > There are 0 rows in 0 pages for object 'spt_fallback_usg'.
>> > DBCC results for 'spt_provider_types'.
>> > There are 25 rows in 1 pages for object 'spt_provider_types'.
>> > DBCC results for 'spt_datatype_info_ext'.
>> > There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
>> > DBCC results for 'MSreplication_options'.
>> > There are 2 rows in 1 pages for object 'MSreplication_options'.
>> > DBCC results for 'spt_datatype_info'.
>> > There are 36 rows in 1 pages for object 'spt_datatype_info'.
>> > DBCC results for 'spt_server_info'.
>> > There are 29 rows in 1 pages for object 'spt_server_info'.
>> > CHECKDB found 0 allocation errors and 2 consistency errors in database
>> > 'master'.
>> > repair_allow_data_loss is the minimum repair level for the errors found
>> > by DBCC CHECKDB (master ).
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>|||Paul
Yes I can select * from sysdepends
Are you saying that I can repair the master database or I cannot?
It looks like our backups are corrupt too. I am not sure when the
original errors were introduced. It looks like I might have to use a
procedure similar to what Sassan described above.
Paul S Randal [MS] wrote:
> Look in MSDN, and the recent SQL Server 2000 downloadable BOL refreshes. I
> documented every error message that CHECKDB can return (SQL Server 2005 ones
> are done but haven't made it out to the wild yet)
> Can you select * from sysdepends in master? You may be lucky and there's
> nothing there, in which case (once you've made sure you know exactly why the
> problem occured and have taken steps to make sure it doesn't happen again),
> you should be able to get away with running repair rather than going back to
> your old backups. And, of course, get a much better backup strategy.
> Thanks
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dave" <daveg.01@.gmail.com> wrote in message
> news:1150827802.442472.143860@.u72g2000cwu.googlegroups.com...
> > Thanks Paul!!!!!
> >
> > I am trying to understand how to interpret the results of CHECKDB. I
> > can't seem to find enough detail to understand what is going on in BOL.
> >
> > I think we are going to open a ticket with Microsoft support to assist
> > us.
> >
> > Our latest backup overlaps the disk failure. Based off time, it looks
> > like the backup was 80-90% complete before the disk went bad.
> >
> > The next backup we have is over a week old. It looks like our backup
> > to tape got bumped by other jobs which ran over :(
> >
> >
> > Paul S Randal [MS] wrote:
> >> If the corruption is limited to non-clustered indexes (with index ID >
> >> 1),
> >> then you won't lose data. In your case, the corrupt page is in the
> >> sysdepends heap so you will lose data from that table. This system table
> >> stores info about the dependencies between views, triggers, sprocs and
> >> the
> >> objects they reference. I'm not sure how the system will behave if you
> >> run
> >> repair - which will delete the page (because the internal pointer in the
> >> record that points to the variable-length column offset table is pointing
> >> off the end of the record - and so the page could be grossly corrupt).
> >>
> >> A far better course of action is to run restore in this case. Do you have
> >> a
> >> valid backup?
> >>
> >> See my blog post at
> >> https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/07/620319.aspx
> >> for more details.
> >>
> >> Thanks
> >>
> >> --
> >> Paul Randal
> >> Lead Program Manager, Microsoft SQL Server Storage Engine
> >> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Dave" <daveg.01@.gmail.com> wrote in message
> >> news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
> >> > Can I get data loss from running CHECKDB on master (or user databases)
> >> > if the consistency errors are in indexes?
> >> >
> >> >
> >> > Here are the results of CHECKDB on master:
> >> >
> >> > Server: Msg 8928, Level 16, State 1, Line 2
> >> > Object ID 12, index ID 0: Page (1:364) could not be processed. See
> >> > other errors for details.
> >> > Server: Msg 8944, Level 16, State 1, Line 2
> >> > Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> >> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> >> > DBCC results for 'master'.
> >> > DBCC results for 'sysobjects'.
> >> > There are 1272 rows in 23 pages for object 'sysobjects'.
> >> > DBCC results for 'sysindexes'.
> >> > There are 104 rows in 4 pages for object 'sysindexes'.
> >> > DBCC results for 'syscolumns'.
> >> > There are 4884 rows in 85 pages for object 'syscolumns'.
> >> > DBCC results for 'systypes'.
> >> > There are 26 rows in 1 pages for object 'systypes'.
> >> > DBCC results for 'syscomments'.
> >> > There are 2024 rows in 952 pages for object 'syscomments'.
> >> > DBCC results for 'sysfiles1'.
> >> > There are 2 rows in 1 pages for object 'sysfiles1'.
> >> > DBCC results for 'syspermissions'.
> >> > There are 788 rows in 4 pages for object 'syspermissions'.
> >> > DBCC results for 'sysusers'.
> >> > There are 17 rows in 1 pages for object 'sysusers'.
> >> > DBCC results for 'sysproperties'.
> >> > There are 0 rows in 0 pages for object 'sysproperties'.
> >> > DBCC results for 'sysdepends'.
> >> > There are 5282 rows in 28 pages for object 'sysdepends'.
> >> > CHECKDB found 0 allocation errors and 2 consistency errors in table
> >> > 'sysdepends' (object ID 12).
> >> > DBCC results for 'sysreferences'.
> >> > There are 0 rows in 0 pages for object 'sysreferences'.
> >> > DBCC results for 'sysfulltextcatalogs'.
> >> > There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> >> > DBCC results for 'sysfulltextnotify'.
> >> > There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> >> > DBCC results for 'sysdatabases'.
> >> > There are 309 rows in 13 pages for object 'sysdatabases'.
> >> > DBCC results for 'sysxlogins'.
> >> > There are 465 rows in 14 pages for object 'sysxlogins'.
> >> > DBCC results for 'sysdevices'.
> >> > There are 8 rows in 1 pages for object 'sysdevices'.
> >> > DBCC results for 'sysmessages'.
> >> > There are 3831 rows in 165 pages for object 'sysmessages'.
> >> > DBCC results for 'sysconfigures'.
> >> > There are 38 rows in 1 pages for object 'sysconfigures'.
> >> > DBCC results for 'sysservers'.
> >> > There are 7 rows in 1 pages for object 'sysservers'.
> >> > DBCC results for 'syslanguages'.
> >> > There are 33 rows in 3 pages for object 'syslanguages'.
> >> > DBCC results for 'syscharsets'.
> >> > There are 114 rows in 33 pages for object 'syscharsets'.
> >> > DBCC results for 'sysaltfiles'.
> >> > There are 618 rows in 123 pages for object 'sysaltfiles'.
> >> > DBCC results for 'sysfilegroups'.
> >> > There are 1 rows in 1 pages for object 'sysfilegroups'.
> >> > DBCC results for 'spt_monitor'.
> >> > There are 1 rows in 1 pages for object 'spt_monitor'.
> >> > DBCC results for 'spt_values'.
> >> > There are 730 rows in 7 pages for object 'spt_values'.
> >> > DBCC results for 'spt_fallback_db'.
> >> > There are 0 rows in 0 pages for object 'spt_fallback_db'.
> >> > DBCC results for 'spt_fallback_dev'.
> >> > There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> >> > DBCC results for 'spt_fallback_usg'.
> >> > There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> >> > DBCC results for 'spt_provider_types'.
> >> > There are 25 rows in 1 pages for object 'spt_provider_types'.
> >> > DBCC results for 'spt_datatype_info_ext'.
> >> > There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> >> > DBCC results for 'MSreplication_options'.
> >> > There are 2 rows in 1 pages for object 'MSreplication_options'.
> >> > DBCC results for 'spt_datatype_info'.
> >> > There are 36 rows in 1 pages for object 'spt_datatype_info'.
> >> > DBCC results for 'spt_server_info'.
> >> > There are 29 rows in 1 pages for object 'spt_server_info'.
> >> > CHECKDB found 0 allocation errors and 2 consistency errors in database
> >> > 'master'.
> >> > repair_allow_data_loss is the minimum repair level for the errors found
> >> > by DBCC CHECKDB (master ).
> >> > DBCC execution completed. If DBCC printed error messages, contact your
> >> > system administrator.
> >> >
> >|||I suggest you take a backup of master and try running repair, and also the
other stuff I suggest below.
Thanks
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <daveg.01@.gmail.com> wrote in message
news:1150905664.179677.169920@.c74g2000cwc.googlegroups.com...
> Paul
> Yes I can select * from sysdepends
> Are you saying that I can repair the master database or I cannot?
>
> It looks like our backups are corrupt too. I am not sure when the
> original errors were introduced. It looks like I might have to use a
> procedure similar to what Sassan described above.
>
>
>
> Paul S Randal [MS] wrote:
>> Look in MSDN, and the recent SQL Server 2000 downloadable BOL refreshes.
>> I
>> documented every error message that CHECKDB can return (SQL Server 2005
>> ones
>> are done but haven't made it out to the wild yet)
>> Can you select * from sysdepends in master? You may be lucky and there's
>> nothing there, in which case (once you've made sure you know exactly why
>> the
>> problem occured and have taken steps to make sure it doesn't happen
>> again),
>> you should be able to get away with running repair rather than going back
>> to
>> your old backups. And, of course, get a much better backup strategy.
>> Thanks
>> --
>> Paul Randal
>> Lead Program Manager, Microsoft SQL Server Storage Engine
>> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Dave" <daveg.01@.gmail.com> wrote in message
>> news:1150827802.442472.143860@.u72g2000cwu.googlegroups.com...
>> > Thanks Paul!!!!!
>> >
>> > I am trying to understand how to interpret the results of CHECKDB. I
>> > can't seem to find enough detail to understand what is going on in BOL.
>> >
>> > I think we are going to open a ticket with Microsoft support to assist
>> > us.
>> >
>> > Our latest backup overlaps the disk failure. Based off time, it looks
>> > like the backup was 80-90% complete before the disk went bad.
>> >
>> > The next backup we have is over a week old. It looks like our backup
>> > to tape got bumped by other jobs which ran over :(
>> >
>> >
>> > Paul S Randal [MS] wrote:
>> >> If the corruption is limited to non-clustered indexes (with index ID >
>> >> 1),
>> >> then you won't lose data. In your case, the corrupt page is in the
>> >> sysdepends heap so you will lose data from that table. This system
>> >> table
>> >> stores info about the dependencies between views, triggers, sprocs and
>> >> the
>> >> objects they reference. I'm not sure how the system will behave if you
>> >> run
>> >> repair - which will delete the page (because the internal pointer in
>> >> the
>> >> record that points to the variable-length column offset table is
>> >> pointing
>> >> off the end of the record - and so the page could be grossly corrupt).
>> >>
>> >> A far better course of action is to run restore in this case. Do you
>> >> have
>> >> a
>> >> valid backup?
>> >>
>> >> See my blog post at
>> >> https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/07/620319.aspx
>> >> for more details.
>> >>
>> >> Thanks
>> >>
>> >> --
>> >> Paul Randal
>> >> Lead Program Manager, Microsoft SQL Server Storage Engine
>> >> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>> >>
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >> "Dave" <daveg.01@.gmail.com> wrote in message
>> >> news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
>> >> > Can I get data loss from running CHECKDB on master (or user
>> >> > databases)
>> >> > if the consistency errors are in indexes?
>> >> >
>> >> >
>> >> > Here are the results of CHECKDB on master:
>> >> >
>> >> > Server: Msg 8928, Level 16, State 1, Line 2
>> >> > Object ID 12, index ID 0: Page (1:364) could not be processed. See
>> >> > other errors for details.
>> >> > Server: Msg 8944, Level 16, State 1, Line 2
>> >> > Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
>> >> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
>> >> > DBCC results for 'master'.
>> >> > DBCC results for 'sysobjects'.
>> >> > There are 1272 rows in 23 pages for object 'sysobjects'.
>> >> > DBCC results for 'sysindexes'.
>> >> > There are 104 rows in 4 pages for object 'sysindexes'.
>> >> > DBCC results for 'syscolumns'.
>> >> > There are 4884 rows in 85 pages for object 'syscolumns'.
>> >> > DBCC results for 'systypes'.
>> >> > There are 26 rows in 1 pages for object 'systypes'.
>> >> > DBCC results for 'syscomments'.
>> >> > There are 2024 rows in 952 pages for object 'syscomments'.
>> >> > DBCC results for 'sysfiles1'.
>> >> > There are 2 rows in 1 pages for object 'sysfiles1'.
>> >> > DBCC results for 'syspermissions'.
>> >> > There are 788 rows in 4 pages for object 'syspermissions'.
>> >> > DBCC results for 'sysusers'.
>> >> > There are 17 rows in 1 pages for object 'sysusers'.
>> >> > DBCC results for 'sysproperties'.
>> >> > There are 0 rows in 0 pages for object 'sysproperties'.
>> >> > DBCC results for 'sysdepends'.
>> >> > There are 5282 rows in 28 pages for object 'sysdepends'.
>> >> > CHECKDB found 0 allocation errors and 2 consistency errors in table
>> >> > 'sysdepends' (object ID 12).
>> >> > DBCC results for 'sysreferences'.
>> >> > There are 0 rows in 0 pages for object 'sysreferences'.
>> >> > DBCC results for 'sysfulltextcatalogs'.
>> >> > There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
>> >> > DBCC results for 'sysfulltextnotify'.
>> >> > There are 0 rows in 0 pages for object 'sysfulltextnotify'.
>> >> > DBCC results for 'sysdatabases'.
>> >> > There are 309 rows in 13 pages for object 'sysdatabases'.
>> >> > DBCC results for 'sysxlogins'.
>> >> > There are 465 rows in 14 pages for object 'sysxlogins'.
>> >> > DBCC results for 'sysdevices'.
>> >> > There are 8 rows in 1 pages for object 'sysdevices'.
>> >> > DBCC results for 'sysmessages'.
>> >> > There are 3831 rows in 165 pages for object 'sysmessages'.
>> >> > DBCC results for 'sysconfigures'.
>> >> > There are 38 rows in 1 pages for object 'sysconfigures'.
>> >> > DBCC results for 'sysservers'.
>> >> > There are 7 rows in 1 pages for object 'sysservers'.
>> >> > DBCC results for 'syslanguages'.
>> >> > There are 33 rows in 3 pages for object 'syslanguages'.
>> >> > DBCC results for 'syscharsets'.
>> >> > There are 114 rows in 33 pages for object 'syscharsets'.
>> >> > DBCC results for 'sysaltfiles'.
>> >> > There are 618 rows in 123 pages for object 'sysaltfiles'.
>> >> > DBCC results for 'sysfilegroups'.
>> >> > There are 1 rows in 1 pages for object 'sysfilegroups'.
>> >> > DBCC results for 'spt_monitor'.
>> >> > There are 1 rows in 1 pages for object 'spt_monitor'.
>> >> > DBCC results for 'spt_values'.
>> >> > There are 730 rows in 7 pages for object 'spt_values'.
>> >> > DBCC results for 'spt_fallback_db'.
>> >> > There are 0 rows in 0 pages for object 'spt_fallback_db'.
>> >> > DBCC results for 'spt_fallback_dev'.
>> >> > There are 0 rows in 0 pages for object 'spt_fallback_dev'.
>> >> > DBCC results for 'spt_fallback_usg'.
>> >> > There are 0 rows in 0 pages for object 'spt_fallback_usg'.
>> >> > DBCC results for 'spt_provider_types'.
>> >> > There are 25 rows in 1 pages for object 'spt_provider_types'.
>> >> > DBCC results for 'spt_datatype_info_ext'.
>> >> > There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
>> >> > DBCC results for 'MSreplication_options'.
>> >> > There are 2 rows in 1 pages for object 'MSreplication_options'.
>> >> > DBCC results for 'spt_datatype_info'.
>> >> > There are 36 rows in 1 pages for object 'spt_datatype_info'.
>> >> > DBCC results for 'spt_server_info'.
>> >> > There are 29 rows in 1 pages for object 'spt_server_info'.
>> >> > CHECKDB found 0 allocation errors and 2 consistency errors in
>> >> > database
>> >> > 'master'.
>> >> > repair_allow_data_loss is the minimum repair level for the errors
>> >> > found
>> >> > by DBCC CHECKDB (master ).
>> >> > DBCC execution completed. If DBCC printed error messages, contact
>> >> > your
>> >> > system administrator.
>> >> >
>> >
>|||PSS asked me to rebuild it. We have our application back up and
running now.
Thanks for all the help guys! I have a much better understanding of
database corruption, backup, and restore now.
I will make sure we get on a sensible backup program.
Paul S Randal [MS] wrote:
> I suggest you take a backup of master and try running repair, and also the
> other stuff I suggest below.
> Thanks
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dave" <daveg.01@.gmail.com> wrote in message
> news:1150905664.179677.169920@.c74g2000cwc.googlegroups.com...
> > Paul
> >
> > Yes I can select * from sysdepends
> >
> > Are you saying that I can repair the master database or I cannot?
> >
> >
> > It looks like our backups are corrupt too. I am not sure when the
> > original errors were introduced. It looks like I might have to use a
> > procedure similar to what Sassan described above.
> >
> >
> >
> >
> >
> >
> > Paul S Randal [MS] wrote:
> >> Look in MSDN, and the recent SQL Server 2000 downloadable BOL refreshes.
> >> I
> >> documented every error message that CHECKDB can return (SQL Server 2005
> >> ones
> >> are done but haven't made it out to the wild yet)
> >>
> >> Can you select * from sysdepends in master? You may be lucky and there's
> >> nothing there, in which case (once you've made sure you know exactly why
> >> the
> >> problem occured and have taken steps to make sure it doesn't happen
> >> again),
> >> you should be able to get away with running repair rather than going back
> >> to
> >> your old backups. And, of course, get a much better backup strategy.
> >>
> >> Thanks
> >>
> >> --
> >> Paul Randal
> >> Lead Program Manager, Microsoft SQL Server Storage Engine
> >> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Dave" <daveg.01@.gmail.com> wrote in message
> >> news:1150827802.442472.143860@.u72g2000cwu.googlegroups.com...
> >> > Thanks Paul!!!!!
> >> >
> >> > I am trying to understand how to interpret the results of CHECKDB. I
> >> > can't seem to find enough detail to understand what is going on in BOL.
> >> >
> >> > I think we are going to open a ticket with Microsoft support to assist
> >> > us.
> >> >
> >> > Our latest backup overlaps the disk failure. Based off time, it looks
> >> > like the backup was 80-90% complete before the disk went bad.
> >> >
> >> > The next backup we have is over a week old. It looks like our backup
> >> > to tape got bumped by other jobs which ran over :(
> >> >
> >> >
> >> > Paul S Randal [MS] wrote:
> >> >> If the corruption is limited to non-clustered indexes (with index ID >
> >> >> 1),
> >> >> then you won't lose data. In your case, the corrupt page is in the
> >> >> sysdepends heap so you will lose data from that table. This system
> >> >> table
> >> >> stores info about the dependencies between views, triggers, sprocs and
> >> >> the
> >> >> objects they reference. I'm not sure how the system will behave if you
> >> >> run
> >> >> repair - which will delete the page (because the internal pointer in
> >> >> the
> >> >> record that points to the variable-length column offset table is
> >> >> pointing
> >> >> off the end of the record - and so the page could be grossly corrupt).
> >> >>
> >> >> A far better course of action is to run restore in this case. Do you
> >> >> have
> >> >> a
> >> >> valid backup?
> >> >>
> >> >> See my blog post at
> >> >> https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/07/620319.aspx
> >> >> for more details.
> >> >>
> >> >> Thanks
> >> >>
> >> >> --
> >> >> Paul Randal
> >> >> Lead Program Manager, Microsoft SQL Server Storage Engine
> >> >> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> >> >>
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >> >> "Dave" <daveg.01@.gmail.com> wrote in message
> >> >> news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
> >> >> > Can I get data loss from running CHECKDB on master (or user
> >> >> > databases)
> >> >> > if the consistency errors are in indexes?
> >> >> >
> >> >> >
> >> >> > Here are the results of CHECKDB on master:
> >> >> >
> >> >> > Server: Msg 8928, Level 16, State 1, Line 2
> >> >> > Object ID 12, index ID 0: Page (1:364) could not be processed. See
> >> >> > other errors for details.
> >> >> > Server: Msg 8944, Level 16, State 1, Line 2
> >> >> > Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> >> >> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> >> >> > DBCC results for 'master'.
> >> >> > DBCC results for 'sysobjects'.
> >> >> > There are 1272 rows in 23 pages for object 'sysobjects'.
> >> >> > DBCC results for 'sysindexes'.
> >> >> > There are 104 rows in 4 pages for object 'sysindexes'.
> >> >> > DBCC results for 'syscolumns'.
> >> >> > There are 4884 rows in 85 pages for object 'syscolumns'.
> >> >> > DBCC results for 'systypes'.
> >> >> > There are 26 rows in 1 pages for object 'systypes'.
> >> >> > DBCC results for 'syscomments'.
> >> >> > There are 2024 rows in 952 pages for object 'syscomments'.
> >> >> > DBCC results for 'sysfiles1'.
> >> >> > There are 2 rows in 1 pages for object 'sysfiles1'.
> >> >> > DBCC results for 'syspermissions'.
> >> >> > There are 788 rows in 4 pages for object 'syspermissions'.
> >> >> > DBCC results for 'sysusers'.
> >> >> > There are 17 rows in 1 pages for object 'sysusers'.
> >> >> > DBCC results for 'sysproperties'.
> >> >> > There are 0 rows in 0 pages for object 'sysproperties'.
> >> >> > DBCC results for 'sysdepends'.
> >> >> > There are 5282 rows in 28 pages for object 'sysdepends'.
> >> >> > CHECKDB found 0 allocation errors and 2 consistency errors in table
> >> >> > 'sysdepends' (object ID 12).
> >> >> > DBCC results for 'sysreferences'.
> >> >> > There are 0 rows in 0 pages for object 'sysreferences'.
> >> >> > DBCC results for 'sysfulltextcatalogs'.
> >> >> > There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> >> >> > DBCC results for 'sysfulltextnotify'.
> >> >> > There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> >> >> > DBCC results for 'sysdatabases'.
> >> >> > There are 309 rows in 13 pages for object 'sysdatabases'.
> >> >> > DBCC results for 'sysxlogins'.
> >> >> > There are 465 rows in 14 pages for object 'sysxlogins'.
> >> >> > DBCC results for 'sysdevices'.
> >> >> > There are 8 rows in 1 pages for object 'sysdevices'.
> >> >> > DBCC results for 'sysmessages'.
> >> >> > There are 3831 rows in 165 pages for object 'sysmessages'.
> >> >> > DBCC results for 'sysconfigures'.
> >> >> > There are 38 rows in 1 pages for object 'sysconfigures'.
> >> >> > DBCC results for 'sysservers'.
> >> >> > There are 7 rows in 1 pages for object 'sysservers'.
> >> >> > DBCC results for 'syslanguages'.
> >> >> > There are 33 rows in 3 pages for object 'syslanguages'.
> >> >> > DBCC results for 'syscharsets'.
> >> >> > There are 114 rows in 33 pages for object 'syscharsets'.
> >> >> > DBCC results for 'sysaltfiles'.
> >> >> > There are 618 rows in 123 pages for object 'sysaltfiles'.
> >> >> > DBCC results for 'sysfilegroups'.
> >> >> > There are 1 rows in 1 pages for object 'sysfilegroups'.
> >> >> > DBCC results for 'spt_monitor'.
> >> >> > There are 1 rows in 1 pages for object 'spt_monitor'.
> >> >> > DBCC results for 'spt_values'.
> >> >> > There are 730 rows in 7 pages for object 'spt_values'.
> >> >> > DBCC results for 'spt_fallback_db'.
> >> >> > There are 0 rows in 0 pages for object 'spt_fallback_db'.
> >> >> > DBCC results for 'spt_fallback_dev'.
> >> >> > There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> >> >> > DBCC results for 'spt_fallback_usg'.
> >> >> > There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> >> >> > DBCC results for 'spt_provider_types'.
> >> >> > There are 25 rows in 1 pages for object 'spt_provider_types'.
> >> >> > DBCC results for 'spt_datatype_info_ext'.
> >> >> > There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> >> >> > DBCC results for 'MSreplication_options'.
> >> >> > There are 2 rows in 1 pages for object 'MSreplication_options'.
> >> >> > DBCC results for 'spt_datatype_info'.
> >> >> > There are 36 rows in 1 pages for object 'spt_datatype_info'.
> >> >> > DBCC results for 'spt_server_info'.
> >> >> > There are 29 rows in 1 pages for object 'spt_server_info'.
> >> >> > CHECKDB found 0 allocation errors and 2 consistency errors in
> >> >> > database
> >> >> > 'master'.
> >> >> > repair_allow_data_loss is the minimum repair level for the errors
> >> >> > found
> >> >> > by DBCC CHECKDB (master ).
> >> >> > DBCC execution completed. If DBCC printed error messages, contact
> >> >> > your
> >> >> > system administrator.
> >> >> >
> >> >
> >