DBCC SHOWFILESTATS

Hello,
I′m testing the execution of my database backups with omniback. These
backups fails so i've made a trace and i get the following error: "Error:
7983, Severity: 14, State: 14"
"dbcc showfilestats( 1 )"
The user used to access the databases is a domain admin but in sql server i
only give him the backup operator database role.
If this user were sysadmin everything goes fine but i want to limit his
access.
Do you have any idea about this?
What does the documentation for the backup vendor say? Does it say that the login need to be
sysadmin. If not, you have a bug in their program. If it does, well...
You cannot grant permissions on this. Also, this is an undocumented command, so the backup vendor is
using it at their own risk.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <CC&JM@.discussions.microsoft.com> wrote in message
news:A094D1B4-135A-492B-ADAC-2C12CB9CAC35@.microsoft.com...
> Hello,
> Im testing the execution of my database backups with omniback. These
> backups fails so i've made a trace and i get the following error: "Error:
> 7983, Severity: 14, State: 14"
> "dbcc showfilestats( 1 )"
> The user used to access the databases is a domain admin but in sql server i
> only give him the backup operator database role.
> If this user were sysadmin everything goes fine but i want to limit his
> access.
> Do you have any idea about this?
>
|||Moreover, the SQL Backup alternatives typically want to use the Virtual
Backup Device Interface API. At this time, only system admins can make calls
with this interface. Backup Operators do not.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:

> What does the documentation for the backup vendor say? Does it say that the login need to be
> sysadmin. If not, you have a bug in their program. If it does, well...
> You cannot grant permissions on this. Also, this is an undocumented command, so the backup vendor is
> using it at their own risk.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CC&JM" <CC&JM@.discussions.microsoft.com> wrote in message
> news:A094D1B4-135A-492B-ADAC-2C12CB9CAC35@.microsoft.com...
>
>
|||That undocumented DBCC command is SA-only - there's no way around this.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:354E62F2-2E58-4D0A-BE1F-E1AA3D8262BE@.microsoft.com...
> Moreover, the SQL Backup alternatives typically want to use the Virtual
> Backup Device Interface API. At this time, only system admins can make
calls[vbcol=seagreen]
> with this interface. Backup Operators do not.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
the login need to be[vbcol=seagreen]
command, so the backup vendor is[vbcol=seagreen]
"Error:[vbcol=seagreen]
server i[vbcol=seagreen]
his[vbcol=seagreen]

DBCC SHOWFILESTATS

Hello,
I′m testing the execution of my database backups with omniback. These
backups fails so i've made a trace and i get the following error: "Error:
7983, Severity: 14, State: 14"
"dbcc showfilestats( 1 )"
The user used to access the databases is a domain admin but in sql server i
only give him the backup operator database role.
If this user were sysadmin everything goes fine but i want to limit his
access.
Do you have any idea about this?What does the documentation for the backup vendor say? Does it say that the
login need to be
sysadmin. If not, you have a bug in their program. If it does, well...
You cannot grant permissions on this. Also, this is an undocumented command,
so the backup vendor is
using it at their own risk.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <CC&JM@.discussions.microsoft.com> wrote in message
news:A094D1B4-135A-492B-ADAC-2C12CB9CAC35@.microsoft.com...
> Hello,
> Im testing the execution of my database backups with omniback. These
> backups fails so i've made a trace and i get the following error: "Error:
> 7983, Severity: 14, State: 14"
> "dbcc showfilestats( 1 )"
> The user used to access the databases is a domain admin but in sql server
i
> only give him the backup operator database role.
> If this user were sysadmin everything goes fine but i want to limit his
> access.
> Do you have any idea about this?
>|||Moreover, the SQL Backup alternatives typically want to use the Virtual
Backup Device Interface API. At this time, only system admins can make call
s
with this interface. Backup Operators do not.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:

> What does the documentation for the backup vendor say? Does it say that th
e login need to be
> sysadmin. If not, you have a bug in their program. If it does, well...
> You cannot grant permissions on this. Also, this is an undocumented comman
d, so the backup vendor is
> using it at their own risk.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CC&JM" <CC&JM@.discussions.microsoft.com> wrote in message
> news:A094D1B4-135A-492B-ADAC-2C12CB9CAC35@.microsoft.com...
>
>|||That undocumented DBCC command is SA-only - there's no way around this.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:354E62F2-2E58-4D0A-BE1F-E1AA3D8262BE@.microsoft.com...
> Moreover, the SQL Backup alternatives typically want to use the Virtual
> Backup Device Interface API. At this time, only system admins can make
calls[vbcol=seagreen]
> with this interface. Backup Operators do not.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
>
the login need to be[vbcol=seagreen]
command, so the backup vendor is[vbcol=seagreen]
"Error:[vbcol=seagreen]
server i[vbcol=seagreen]
his[vbcol=seagreen]

DBCC SHOWFILESTATS

Hello,
I´m testing the execution of my database backups with omniback. These
backups fails so i've made a trace and i get the following error: "Error:
7983, Severity: 14, State: 14"
"dbcc showfilestats( 1 )"
The user used to access the databases is a domain admin but in sql server i
only give him the backup operator database role.
If this user were sysadmin everything goes fine but i want to limit his
access.
Do you have any idea about this?What does the documentation for the backup vendor say? Does it say that the login need to be
sysadmin. If not, you have a bug in their program. If it does, well...
You cannot grant permissions on this. Also, this is an undocumented command, so the backup vendor is
using it at their own risk.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <CC&JM@.discussions.microsoft.com> wrote in message
news:A094D1B4-135A-492B-ADAC-2C12CB9CAC35@.microsoft.com...
> Hello,
> I´m testing the execution of my database backups with omniback. These
> backups fails so i've made a trace and i get the following error: "Error:
> 7983, Severity: 14, State: 14"
> "dbcc showfilestats( 1 )"
> The user used to access the databases is a domain admin but in sql server i
> only give him the backup operator database role.
> If this user were sysadmin everything goes fine but i want to limit his
> access.
> Do you have any idea about this?
>|||Moreover, the SQL Backup alternatives typically want to use the Virtual
Backup Device Interface API. At this time, only system admins can make calls
with this interface. Backup Operators do not.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:
> What does the documentation for the backup vendor say? Does it say that the login need to be
> sysadmin. If not, you have a bug in their program. If it does, well...
> You cannot grant permissions on this. Also, this is an undocumented command, so the backup vendor is
> using it at their own risk.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CC&JM" <CC&JM@.discussions.microsoft.com> wrote in message
> news:A094D1B4-135A-492B-ADAC-2C12CB9CAC35@.microsoft.com...
> > Hello,
> >
> > I´m testing the execution of my database backups with omniback. These
> > backups fails so i've made a trace and i get the following error: "Error:
> > 7983, Severity: 14, State: 14"
> > "dbcc showfilestats( 1 )"
> >
> > The user used to access the databases is a domain admin but in sql server i
> > only give him the backup operator database role.
> > If this user were sysadmin everything goes fine but i want to limit his
> > access.
> >
> > Do you have any idea about this?
> >
>
>|||That undocumented DBCC command is SA-only - there's no way around this.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:354E62F2-2E58-4D0A-BE1F-E1AA3D8262BE@.microsoft.com...
> Moreover, the SQL Backup alternatives typically want to use the Virtual
> Backup Device Interface API. At this time, only system admins can make
calls
> with this interface. Backup Operators do not.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
> > What does the documentation for the backup vendor say? Does it say that
the login need to be
> > sysadmin. If not, you have a bug in their program. If it does, well...
> >
> > You cannot grant permissions on this. Also, this is an undocumented
command, so the backup vendor is
> > using it at their own risk.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "CC&JM" <CC&JM@.discussions.microsoft.com> wrote in message
> > news:A094D1B4-135A-492B-ADAC-2C12CB9CAC35@.microsoft.com...
> > > Hello,
> > >
> > > I´m testing the execution of my database backups with omniback. These
> > > backups fails so i've made a trace and i get the following error:
"Error:
> > > 7983, Severity: 14, State: 14"
> > > "dbcc showfilestats( 1 )"
> > >
> > > The user used to access the databases is a domain admin but in sql
server i
> > > only give him the backup operator database role.
> > > If this user were sysadmin everything goes fine but i want to limit
his
> > > access.
> > >
> > > Do you have any idea about this?
> > >
> >
> >
> >sql

DBCC Showcontig

Does anyone know why i get this answer:
DBCC execution completed. If DBCC printed error messages,
contact your system administrator. when i do a dbcc
showcontig ofa table? BUt another table gives me results..
Any explanation?
ThanxsPerhaps that table doesn't have any indexes? Use sp_helpindex to check which indexes each table has.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"smiley" <anonymous@.discussions.microsoft.com> wrote in message
news:059601c3adcc$d57d2cf0$a401280a@.phx.gbl...
> Does anyone know why i get this answer:
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator. when i do a dbcc
> showcontig ofa table? BUt another table gives me results..
> Any explanation?
> Thanxssql

DBCC DBREINDEX Help

DBCC DBREINDEX ('pubs.dbo.authors', UPKCL_auidind, 80)
when the above given command is executed i get the result as
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
instead of
Index (ID = 1) is being rebuilt.
Index (ID = 2) is being rebuilt.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
how can i get the individual index id once they are reindexed?
pls respond me as soon as possible
Regards
Sudarshan SelvarajaWhy do you need the individual index ID? You specified an index so you
should know what it's ID is. In your case only that one index will be
rebuilt.
Andrew J. Kelly SQL MVP
"sudarshan selvaraja" <sudarshanselvaraja@.discussions.microsoft.com> wrote
in message news:136BB536-3A7D-462E-9FC0-9A714A40FF2C@.microsoft.com...
> DBCC DBREINDEX ('pubs.dbo.authors', UPKCL_auidind, 80)
> when the above given command is executed i get the result as
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> instead of
> Index (ID = 1) is being rebuilt.
> Index (ID = 2) is being rebuilt.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> how can i get the individual index id once they are reindexed?
> pls respond me as soon as possible
> Regards
> Sudarshan Selvaraja
>
>|||Andrew,
He has taken the example from BOL. Coming to think of it, though I never
realised, I have never got any message when index is being rebuilt.
I do it for defrag once in a while, so use showcontig after I run this.
And, to answer your question. The following doesn't return any messages
either.
use northwind
DBCC DBREINDEX (Employees, '', 80)
And its got two indexes.|||To be honest I can't remember if it shows these messages or not. But if you
reindex a clustered index that is not unique it will have to rebuild all the
non-clustered indexes as well. This is due to the way it enforces uniqueness
on the clustered index in 2000. So what you see may depend on if the
clustered index is unique or not.
Andrew J. Kelly SQL MVP
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:1AC7EEDA-F769-4375-82FB-DD4F9AD9F235@.microsoft.com...
> Andrew,
> He has taken the example from BOL. Coming to think of it, though I never
> realised, I have never got any message when index is being rebuilt.
> I do it for defrag once in a while, so use showcontig after I run this.
> And, to answer your question. The following doesn't return any messages
> either.
> use northwind
> DBCC DBREINDEX (Employees, '', 80)
> And its got two indexes.|||In BOL they have mentioned that when DBCC DBREINDEX is executed the output
will be
Index (ID = 1) is being rebuilt.
Index (ID = 2) is being rebuilt.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator
Actually i need this because when i reindex all the tables in the db i need
to know what are the index reindexed.
Regards
Sudarshan Selvaraja
"Andrew J. Kelly" wrote:

> To be honest I can't remember if it shows these messages or not. But if y
ou
> reindex a clustered index that is not unique it will have to rebuild all t
he
> non-clustered indexes as well. This is due to the way it enforces uniquene
ss
> on the clustered index in 2000. So what you see may depend on if the
> clustered index is unique or not.
> --
> Andrew J. Kelly SQL MVP
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:1AC7EEDA-F769-4375-82FB-DD4F9AD9F235@.microsoft.com...
>
>|||USE Northwind --Enter the name of the database you want to reindex
go
DECLARE @.TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@.TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
When the above given code is executed i get the output as
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I dont know exactly what are the index reindexed ...any suggestion pls
"sudarshan selvaraja" wrote:
> In BOL they have mentioned that when DBCC DBREINDEX is executed the output
> will be
> Index (ID = 1) is being rebuilt.
> Index (ID = 2) is being rebuilt.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator
> Actually i need this because when i reindex all the tables in the db i nee
d
> to know what are the index reindexed.
> Regards
> Sudarshan Selvaraja
>
> "Andrew J. Kelly" wrote:
>|||This will reindex all of them. Do you really need to know which ones when
it is all of them? You can find out what indexes are on each table by
looking in sysindexes or sp_helpindex. You can also build a cursor based on
either and rebuild the indexes one at a time but why bother if you are going
to do them all anyway? There is an example in BOL under DBCC SHOWCONTIG
that will only reindex or Defrag indexes above a certain fragmentation
level. Maybe this is more of what you want.
Andrew J. Kelly SQL MVP
"sudarshan selvaraja" <sudarshanselvaraja@.discussions.microsoft.com> wrote
in message news:BE1AB6D9-40BC-40F4-8E62-679497A6BB61@.microsoft.com...
> USE Northwind --Enter the name of the database you want to reindex
> go
> DECLARE @.TableName varchar(255)
> DECLARE TableCursor CURSOR FOR
> SELECT table_name FROM information_schema.tables
> WHERE table_type = 'base table'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DBCC DBREINDEX(@.TableName,' ',90)
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> When the above given code is executed i get the output as
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> I dont know exactly what are the index reindexed ...any suggestion pls
>
>
> "sudarshan selvaraja" wrote:
>|||Ok thx andrew i thought i could get the individual index id when it is
reindexed..any how it seems that i can use the code in BOL given with
showcontig..
"Andrew J. Kelly" wrote:

> This will reindex all of them. Do you really need to know which ones when
> it is all of them? You can find out what indexes are on each table by
> looking in sysindexes or sp_helpindex. You can also build a cursor based o
n
> either and rebuild the indexes one at a time but why bother if you are goi
ng
> to do them all anyway? There is an example in BOL under DBCC SHOWCONTIG
> that will only reindex or Defrag indexes above a certain fragmentation
> level. Maybe this is more of what you want.
> --
> Andrew J. Kelly SQL MVP
>
> "sudarshan selvaraja" <sudarshanselvaraja@.discussions.microsoft.com> wrote
> in message news:BE1AB6D9-40BC-40F4-8E62-679497A6BB61@.microsoft.com...
>
>

DBCC DBREINDEX does nothing

Has anyone had the problem with DBCC DBREINDEX on a table/index not doing anything.
You just get the usual "DBCC execution completed. If DBCC printed error messages, contact your system administrator." message.
I have tried:
DBCC DBREINDEX ('<table_name>')
DBCC DBREINDEX (<table_name>)
DBCC DBREINDEX ('<table_name>', '')
DBCC DBREINDEX ('<table_name>', '<index_name>')
DBCC DBREINDEX ('<table_name>', '<index_name>', 80)

Maybe its "optimising" and not thinking anything needs updating.

Should I look for an alternative way of rebuilding an index like dropping and recreating?

The beauty with DBCC DBREINDEX ('<table_name>') would have been that I was going to get the list of user tables in the database and call it for each table to simply rebuild all indexes.For small tables (up to around 1000 pages), DBCC is pretty quick. Your hardware will also determine the speed of the operation, as well. In order to see if DBCC is actually doing anything, you should run dbcc showcontig before and after to see if the density has changed. Note also for very small tables (under 8 pages), DBCC DBREINDEX has no effect. Neither does drop/rebuild of indexes. These tables are simply too small to actually have the data rearranged.|||From the help it suggests you get something like:

Index (ID = 1) is being rebuilt.
Index (ID = 2) is being rebuilt.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.|||I have never seen those messages (Index (ID = 1) is being rebuilt). Doesn't seem to return messages even with trace flag 3604 turned on. The article says it was updated July 2003, too. Looks like you may have found a bug in the documentation.|||I also do exactly what you mention (do the reindex on all tables in my database from the system list of user tables) on a weekly basis, and just get the summary data that you mention in your posts. I have never seen the interim "status" message in my reindex logs, and as mentioned perform the operation on my complete database (approx 35 tables) on a weekly basis.|||It looks like DBCC DBREINDEX isn't the answer.
In my case I need to drop and recreate the indexes for them to be properly rebuilt.
While I can write a script with an entry for every single index this is hard work as there are a lot of indexes.

I really would like to write a script to loop through the tables, get the index info and drop and recreate them in a single statement.
The problem with this is that the database has foreign keys.
Therefore tables need to be done in a certain order.
If you drop an index a foreign key is based on, it naturally produces an error.