DBCC Permissions

Hi,

I want to be able to run the following statement:

DBCC log (tbl_Name)

But I get this error:

Server: Msg 2571, Level 14, State 1, Line 1
User 'HQ\exd188' does not have permission to run DBCC log.

What server permissions would I need to run this statement.

TIA,
EricHere's what I found:

<QUOTE>
From: David
Date: 08-08-03 01:15
Subject: Re: How can I read the content of transaction log?

------------------------
Hi Wallace,

Well, Peter is right, it is very difficult to get some piece of information
out from the log.
It depends on what you are looking for. Have a look at dbcc log command:

dbcc log (dbid, object_id, pagenum, rownum, nrecords, type, printopt)

- dbid: the id of your database in sysdatabases (or you can get it with
db_id("my_db"))
- object_id: if you know especially on which object the transactions you're
looking for are related.
- pagenum / rownum: (optional) you can add the page number (if you don't
have rownum) for this object
or the transaction ID (if you can supply a rownum)
- nrecords: number of records to examine. Put a negative value to get the
last n records.
- type: the type of transaction you can find. It goes from -1 (all records)
to 36. Each number from 1 to 36
represents a xact. For example: 4 for XREC_INSERT, 5 for XREC_DELETE, 9
for XREC_MODIFY, etc...
You can find the list at http://www.kaleidatech.com/dbcc2.htm
- printopt: the print option: 0 if you want only headers, 1 if you want
header + data.

For example, if I want the header of the 20 last records of every xact on my
object_id 263671987 in my database 4:
1>dbcc traceon(3604)
2>go
1>dbcc log(4,263671987,0,0,-20,-1,0);
2>go

If the log is empty, it will say:

LOG SCAN DEFINITION:
Database id : 4
Backward scan: starting at end of log
log records for object id 263671987
maximum of 20 log records.

LOG RECORDS:

Total number of log records 0
DBCC execution completed. If DBCC printed error messages, contact a user
with System Administrator (SA) role.

If it is not, you will find hexa information. Have the closest look at the
timestamps. They will tell you when
the transaction occured.

You can have a look also at Thierry Antinolfi's DBA Devil website. He
provides a C++ built-in LogExplorer.
http://perso.wanadoo.fr/dbadevil/en/logexpl.html

Chrz, -]

David.

<Wallace> a crit dans le message de news:
3f31cd08.51ec.846930886@.sybase.com
[/QUOTE]

DBCC CHECKTABLE on a View

I'm trying to run the following statement:
DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
But I get this error message:
Server: Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'vLostSaleCount'. Check sysobjects.
So I run this statement:
SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
And get this result:
name id xtype uid info status base_schema_ver replinfo parent_obj crdate
ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate
version deltrig instrig updtrig seltrig category cache
vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03 13:19:14.993 0
16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
(1 row(s) affected)
What is going on here? TIA!
CHECKTABLE is checking physical consistency of the data. A view doesn't store any data so there is
nothing to check. Run CHECKTABLE on the tables that the view is querying instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:OK8fPsVPGHA.812@.TK2MSFTNGP10.phx.gbl...
> I'm trying to run the following statement:
> DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
> But I get this error message:
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named 'vLostSaleCount'. Check sysobjects.
> So I run this statement:
> SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
> And get this result:
> name id xtype uid info status base_schema_ver replinfo parent_obj crdate
> ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate
> version deltrig instrig updtrig seltrig category cache
> vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03 13:19:14.993 0
> 16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
> (1 row(s) affected)
> What is going on here? TIA!
>
|||Thanks! I'm taking your word for it, but that seems to be in conflict with
what BOL says. Is BOL in error? Here is DBCC CHECKTABLE from BOL:
DBCC CHECKTABLE
Checks the integrity of the data, index, text, ntext, and image pages for
the specified table or indexed view.
Syntax
DBCC CHECKTABLE
( 'table_name' | 'view_name'
[ , NOINDEX
| index_id
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD }
]
) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMAHDuVPGHA.648@.TK2MSFTNGP14.phx.gbl...
> CHECKTABLE is checking physical consistency of the data. A view doesn't
store any data so there is
> nothing to check. Run CHECKTABLE on the tables that the view is querying
instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in
message[vbcol=seagreen]
> news:OK8fPsVPGHA.812@.TK2MSFTNGP10.phx.gbl...
sysobjects.[vbcol=seagreen]
refdate[vbcol=seagreen]
13:19:14.993 0
>
|||Is the view qualified with a name other than DBO?
"Ron Hinds" wrote:

> I'm trying to run the following statement:
> DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
> But I get this error message:
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named 'vLostSaleCount'. Check sysobjects.
> So I run this statement:
> SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
> And get this result:
> name id xtype uid info status base_schema_ver replinfo parent_obj crdate
> ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate
> version deltrig instrig updtrig seltrig category cache
> vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03 13:19:14.993 0
> 16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
> (1 row(s) affected)
> What is going on here? TIA!
>
>
|||No - I even tried qualifying it with dbo and still got the error.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:0309BF46-E4A8-4F84-8B12-68B767AE40B6@.microsoft.com...[vbcol=seagreen]
> Is the view qualified with a name other than DBO?
> "Ron Hinds" wrote:
sysobjects.[vbcol=seagreen]
refdate[vbcol=seagreen]
13:19:14.993 0[vbcol=seagreen]
|||BOL is talking about indexed views.
An indexed view is not a 'normal' view and use database storage.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ron Hinds" wrote:

> Thanks! I'm taking your word for it, but that seems to be in conflict with
> what BOL says. Is BOL in error? Here is DBCC CHECKTABLE from BOL:
> DBCC CHECKTABLE
> Checks the integrity of the data, index, text, ntext, and image pages for
> the specified table or indexed view.
> Syntax
> DBCC CHECKTABLE
> ( 'table_name' | 'view_name'
> [ , NOINDEX
> | index_id
> | { REPAIR_ALLOW_DATA_LOSS
> | REPAIR_FAST
> | REPAIR_REBUILD }
> ]
> ) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
> [ , [ TABLOCK ] ]
> [ , [ ESTIMATEONLY ] ]
> [ , [ PHYSICAL_ONLY ] ]
> }
> ]
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uMAHDuVPGHA.648@.TK2MSFTNGP14.phx.gbl...
> store any data so there is
> instead.
> message
> sysobjects.
> refdate
> 13:19:14.993 0
>
>
|||Note "*indexed* view". Do you have an index on that view?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:%23I6bXxVPGHA.2236@.TK2MSFTNGP15.phx.gbl...
> Thanks! I'm taking your word for it, but that seems to be in conflict with
> what BOL says. Is BOL in error? Here is DBCC CHECKTABLE from BOL:
> DBCC CHECKTABLE
> Checks the integrity of the data, index, text, ntext, and image pages for
> the specified table or indexed view.
> Syntax
> DBCC CHECKTABLE
> ( 'table_name' | 'view_name'
> [ , NOINDEX
> | index_id
> | { REPAIR_ALLOW_DATA_LOSS
> | REPAIR_FAST
> | REPAIR_REBUILD }
> ]
> ) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
> [ , [ TABLOCK ] ]
> [ , [ ESTIMATEONLY ] ]
> [ , [ PHYSICAL_ONLY ] ]
> }
> ]
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uMAHDuVPGHA.648@.TK2MSFTNGP14.phx.gbl...
> store any data so there is
> instead.
> message
> sysobjects.
> refdate
> 13:19:14.993 0
>
|||I see. Thanks guys!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uA5Qo$VPGHA.720@.TK2MSFTNGP14.phx.gbl...
> Note "*indexed* view". Do you have an index on that view?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in
message[vbcol=seagreen]
> news:%23I6bXxVPGHA.2236@.TK2MSFTNGP15.phx.gbl...
with[vbcol=seagreen]
for[vbcol=seagreen]
in[vbcol=seagreen]
querying[vbcol=seagreen]
crdate
>

DBCC CHECKTABLE on a View

I'm trying to run the following statement:
DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
But I get this error message:
Server: Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'vLostSaleCount'. Check sysobjects.
So I run this statement:
SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
And get this result:
name id xtype uid info status base_schema_ver replinfo parent_obj crdate
ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate
version deltrig instrig updtrig seltrig category cache
vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03 13:19:14.993 0
16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
(1 row(s) affected)
What is going on here? TIA!CHECKTABLE is checking physical consistency of the data. A view doesn't stor
e any data so there is
nothing to check. Run CHECKTABLE on the tables that the view is querying ins
tead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ron Hinds" < __ron__dontspamme@.wedontlikespam_garagei
q.com> wrote in message
news:OK8fPsVPGHA.812@.TK2MSFTNGP10.phx.gbl...
> I'm trying to run the following statement:
> DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
> But I get this error message:
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named 'vLostSaleCount'. Check sysobjects.
> So I run this statement:
> SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
> And get this result:
> name id xtype uid info status base_schema_ver replinfo parent_obj crdate
> ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate
> version deltrig instrig updtrig seltrig category cache
> vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03 13:19:14.993
0
> 16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
> (1 row(s) affected)
> What is going on here? TIA!
>|||Thanks! I'm taking your word for it, but that seems to be in conflict with
what BOL says. Is BOL in error? Here is DBCC CHECKTABLE from BOL:
DBCC CHECKTABLE
Checks the integrity of the data, index, text, ntext, and image pages for
the specified table or indexed view.
Syntax
DBCC CHECKTABLE
( 'table_name' | 'view_name'
[ , NOINDEX
| index_id
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD }
]
) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMAHDuVPGHA.648@.TK2MSFTNGP14.phx.gbl...
> CHECKTABLE is checking physical consistency of the data. A view doesn't
store any data so there is
> nothing to check. Run CHECKTABLE on the tables that the view is querying
instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ron Hinds" < __ron__dontspamme@.wedontlikespam_garagei
q.com> wrote in
message
> news:OK8fPsVPGHA.812@.TK2MSFTNGP10.phx.gbl...
sysobjects.[vbcol=seagreen]
refdate[vbcol=seagreen]
13:19:14.993 0[vbcol=seagreen]
>|||Is the view qualified with a name other than DBO?
"Ron Hinds" wrote:

> I'm trying to run the following statement:
> DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
> But I get this error message:
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named 'vLostSaleCount'. Check sysobjects.
> So I run this statement:
> SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
> And get this result:
> name id xtype uid info status base_schema_ver replinfo parent_obj crdate
> ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate
> version deltrig instrig updtrig seltrig category cache
> vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03 13:19:14.993
0
> 16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
> (1 row(s) affected)
> What is going on here? TIA!
>
>|||No - I even tried qualifying it with dbo and still got the error.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:0309BF46-E4A8-4F84-8B12-68B767AE40B6@.microsoft.com...[vbcol=seagreen]
> Is the view qualified with a name other than DBO?
> "Ron Hinds" wrote:
>
sysobjects.[vbcol=seagreen]
refdate[vbcol=seagreen]
13:19:14.993 0[vbcol=seagreen]|||BOL is talking about indexed views.
An indexed view is not a 'normal' view and use database storage.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ron Hinds" wrote:

> Thanks! I'm taking your word for it, but that seems to be in conflict with
> what BOL says. Is BOL in error? Here is DBCC CHECKTABLE from BOL:
> DBCC CHECKTABLE
> Checks the integrity of the data, index, text, ntext, and image pages for
> the specified table or indexed view.
> Syntax
> DBCC CHECKTABLE
> ( 'table_name' | 'view_name'
> [ , NOINDEX
> | index_id
> | { REPAIR_ALLOW_DATA_LOSS
> | REPAIR_FAST
> | REPAIR_REBUILD }
> ]
> ) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
> [ , [ TABLOCK ] ]
> [ , [ ESTIMATEONLY ] ]
> [ , [ PHYSICAL_ONLY ] ]
> }
> ]
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uMAHDuVPGHA.648@.TK2MSFTNGP14.phx.gbl...
> store any data so there is
> instead.
> message
> sysobjects.
> refdate
> 13:19:14.993 0
>
>|||Note "*indexed* view". Do you have an index on that view?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ron Hinds" < __ron__dontspamme@.wedontlikespam_garagei
q.com> wrote in message
news:%23I6bXxVPGHA.2236@.TK2MSFTNGP15.phx.gbl...
> Thanks! I'm taking your word for it, but that seems to be in conflict with
> what BOL says. Is BOL in error? Here is DBCC CHECKTABLE from BOL:
> DBCC CHECKTABLE
> Checks the integrity of the data, index, text, ntext, and image pages for
> the specified table or indexed view.
> Syntax
> DBCC CHECKTABLE
> ( 'table_name' | 'view_name'
> [ , NOINDEX
> | index_id
> | { REPAIR_ALLOW_DATA_LOSS
> | REPAIR_FAST
> | REPAIR_REBUILD }
> ]
> ) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
> [ , [ TABLOCK ] ]
> [ , [ ESTIMATEONLY ] ]
> [ , [ PHYSICAL_ONLY ] ]
> }
> ]
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uMAHDuVPGHA.648@.TK2MSFTNGP14.phx.gbl...
> store any data so there is
> instead.
> message
> sysobjects.
> refdate
> 13:19:14.993 0
>|||I see. Thanks guys!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uA5Qo$VPGHA.720@.TK2MSFTNGP14.phx.gbl...
> Note "*indexed* view". Do you have an index on that view?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ron Hinds" < __ron__dontspamme@.wedontlikespam_garagei
q.com> wrote in
message
> news:%23I6bXxVPGHA.2236@.TK2MSFTNGP15.phx.gbl...
with[vbcol=seagreen]
for[vbcol=seagreen]
in[vbcol=seagreen]
querying[vbcol=seagreen]
crdate[vbcol=seagreen]
>