I've been running above procedure using the "with all_indexes, fast;" option
on a table in my database. This table has 18 indexes. The results in this
procedure refer to index 1, 2, ..., 18. I don't relate to ID number as my
indexes all have english names (e.g. Account, LastName, etc.). I'm not sure
which index ID is referring to what named index. Are these ID numbers simply
the order in which they appear when I see the list in Enterprise Manager,
Manage Indexes option? Is there a way to have the DBCC SHOWCONTIG procedure
reflect the actual index names I had assigned?
Thanks, Jim
Hi Jim
What version are you using? In SQL 2005 there is a replacement for DBCC
SHOWCONTIG that allows you to filter the results and add more information to
the output. The output is much more readable, too. DBCC is not a procedure,
so there is very little you can do to modify how it works.
You cannot assume the index numbers are the same as the order the indexes
come back in the EM list. You can run this query to see what number goes
with what index: (substitute your own table name, of course)
SELECT indid, name
FROM sysindexes
WHERE id = object_id('your-table-name')
AND indexproperty(id,name, 'isStatistics') = 0
AND indexproperty(id,name, 'isHypothetical') = 0
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jim B" <JB@.lightning.com> wrote in message
news:1BE95CD5-1977-47C9-8778-57DBA4A9F183@.microsoft.com...
> I've been running above procedure using the "with all_indexes, fast;"
> option
> on a table in my database. This table has 18 indexes. The results in
> this
> procedure refer to index 1, 2, ..., 18. I don't relate to ID number as my
> indexes all have english names (e.g. Account, LastName, etc.). I'm not
> sure
> which index ID is referring to what named index. Are these ID numbers
> simply
> the order in which they appear when I see the list in Enterprise Manager,
> Manage Indexes option? Is there a way to have the DBCC SHOWCONTIG
> procedure
> reflect the actual index names I had assigned?
> --
> Thanks, Jim
|||Kalen. Thanks for the quick reply. I have to to support sites that use
either SQL2000 or SQL2005. What would be the better option to DBCC
SHOWCONTIG that you mentioned for SQL2005. The query you gave me worked
great in both 2000 and 2005. Thanks again for help.
Thanks, Jim
"Kalen Delaney" wrote:
> Hi Jim
> What version are you using? In SQL 2005 there is a replacement for DBCC
> SHOWCONTIG that allows you to filter the results and add more information to
> the output. The output is much more readable, too. DBCC is not a procedure,
> so there is very little you can do to modify how it works.
> You cannot assume the index numbers are the same as the order the indexes
> come back in the EM list. You can run this query to see what number goes
> with what index: (substitute your own table name, of course)
> SELECT indid, name
> FROM sysindexes
> WHERE id = object_id('your-table-name')
> AND indexproperty(id,name, 'isStatistics') = 0
> AND indexproperty(id,name, 'isHypothetical') = 0
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Jim B" <JB@.lightning.com> wrote in message
> news:1BE95CD5-1977-47C9-8778-57DBA4A9F183@.microsoft.com...
>
>
|||Jim
In SQL 2005 there is a table valued function called
sys.dm_db_index_physical_stats. It returns a LOT of information, but you can
filter it in any way you like, and since it returns table results, you can
join it to the sys.indexes to get the index name, and you can restrict the
columns coming back to just what you need.
You should read about it in BOL first, and if you have a SQL Server Magazine
subscription, you can find a couple of articles I wrote about it on their
site.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jim B" <JB@.lightning.com> wrote in message
news:1E20DDE1-6435-4ED1-A4F4-E7402C3B05B2@.microsoft.com...[vbcol=seagreen]
> Kalen. Thanks for the quick reply. I have to to support sites that use
> either SQL2000 or SQL2005. What would be the better option to DBCC
> SHOWCONTIG that you mentioned for SQL2005. The query you gave me worked
> great in both 2000 and 2005. Thanks again for help.
> --
> Thanks, Jim
>
> "Kalen Delaney" wrote:
Showing posts with label procedure. Show all posts
Showing posts with label procedure. Show all posts
Thursday, March 22, 2012
DBCC SHOWCONTIG
Cross-posted in: microsoft.public.sqlserver.programming &
microsoft.public.sqlserver.server
I have a procedure that loops through the databases and then their tables
and I then want to run DBCC SHOWCONTIG on each table. However, it seems that
DBCC SHOWCONTIG is insisting on only running in the current database, as
defined by the USE statement, or perhaps the database the procedure is being
run from , not sure as BOL is vague on this point.
I tried adding:
SET @.SQL = 'USE ' + @.DB
EXEC sp_executesql @.SQL
Which seems to execute fine.
I then execute:
EXEC ('DBCC SHOWCONTIG(''' + @.DB + '.' + @.Owner + '.' + @.Table + ''')')
I also tried to get and use the OBJECT_ID.
but I continue to get the same error:
Server: Msg 2506, Level 16, State 1, Line 1
Could not find a table or object name 'Data.dbo.sysobjects' in database
'master'.
I have a bad feeling that DBCC SHOWCONTIG can only be run in the current
database, hope I'm wrong.Jay,
> SET @.SQL = 'USE ' + @.DB
> EXEC sp_executesql @.SQL
But it does not mean you are now in @.DB. Current context has not changed.
use northwind
go
exec sp_executesql N'use pubs'
go
select db_name()
go
You have to add the USE statement as part of the batch.
exec sp_executesql N'use AdventureWorks; dbcc showcontig (''dbo.ErrorLog'',
1)'
go
AMB
"Jay" wrote:
> Cross-posted in: microsoft.public.sqlserver.programming &
> microsoft.public.sqlserver.server
> I have a procedure that loops through the databases and then their tables
> and I then want to run DBCC SHOWCONTIG on each table. However, it seems that
> DBCC SHOWCONTIG is insisting on only running in the current database, as
> defined by the USE statement, or perhaps the database the procedure is being
> run from , not sure as BOL is vague on this point.
> I tried adding:
> SET @.SQL = 'USE ' + @.DB
> EXEC sp_executesql @.SQL
> Which seems to execute fine.
> I then execute:
> EXEC ('DBCC SHOWCONTIG(''' + @.DB + '.' + @.Owner + '.' + @.Table + ''')')
> I also tried to get and use the OBJECT_ID.
> but I continue to get the same error:
> Server: Msg 2506, Level 16, State 1, Line 1
> Could not find a table or object name 'Data.dbo.sysobjects' in database
> 'master'.
> I have a bad feeling that DBCC SHOWCONTIG can only be run in the current
> database, hope I'm wrong.
>
>|||Works great, thanks
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3498EDC5-3762-4881-88AF-1E387056AE95@.microsoft.com...
> Jay,
>> SET @.SQL = 'USE ' + @.DB
>> EXEC sp_executesql @.SQL
> But it does not mean you are now in @.DB. Current context has not changed.
> use northwind
> go
> exec sp_executesql N'use pubs'
> go
> select db_name()
> go
> You have to add the USE statement as part of the batch.
> exec sp_executesql N'use AdventureWorks; dbcc showcontig
> (''dbo.ErrorLog'',
> 1)'
> go
>
> AMB
> "Jay" wrote:
>> Cross-posted in: microsoft.public.sqlserver.programming &
>> microsoft.public.sqlserver.server
>> I have a procedure that loops through the databases and then their tables
>> and I then want to run DBCC SHOWCONTIG on each table. However, it seems
>> that
>> DBCC SHOWCONTIG is insisting on only running in the current database, as
>> defined by the USE statement, or perhaps the database the procedure is
>> being
>> run from , not sure as BOL is vague on this point.
>> I tried adding:
>> SET @.SQL = 'USE ' + @.DB
>> EXEC sp_executesql @.SQL
>> Which seems to execute fine.
>> I then execute:
>> EXEC ('DBCC SHOWCONTIG(''' + @.DB + '.' + @.Owner + '.' + @.Table + ''')')
>> I also tried to get and use the OBJECT_ID.
>> but I continue to get the same error:
>> Server: Msg 2506, Level 16, State 1, Line 1
>> Could not find a table or object name 'Data.dbo.sysobjects' in database
>> 'master'.
>> I have a bad feeling that DBCC SHOWCONTIG can only be run in the current
>> database, hope I'm wrong.
>>
microsoft.public.sqlserver.server
I have a procedure that loops through the databases and then their tables
and I then want to run DBCC SHOWCONTIG on each table. However, it seems that
DBCC SHOWCONTIG is insisting on only running in the current database, as
defined by the USE statement, or perhaps the database the procedure is being
run from , not sure as BOL is vague on this point.
I tried adding:
SET @.SQL = 'USE ' + @.DB
EXEC sp_executesql @.SQL
Which seems to execute fine.
I then execute:
EXEC ('DBCC SHOWCONTIG(''' + @.DB + '.' + @.Owner + '.' + @.Table + ''')')
I also tried to get and use the OBJECT_ID.
but I continue to get the same error:
Server: Msg 2506, Level 16, State 1, Line 1
Could not find a table or object name 'Data.dbo.sysobjects' in database
'master'.
I have a bad feeling that DBCC SHOWCONTIG can only be run in the current
database, hope I'm wrong.Jay,
> SET @.SQL = 'USE ' + @.DB
> EXEC sp_executesql @.SQL
But it does not mean you are now in @.DB. Current context has not changed.
use northwind
go
exec sp_executesql N'use pubs'
go
select db_name()
go
You have to add the USE statement as part of the batch.
exec sp_executesql N'use AdventureWorks; dbcc showcontig (''dbo.ErrorLog'',
1)'
go
AMB
"Jay" wrote:
> Cross-posted in: microsoft.public.sqlserver.programming &
> microsoft.public.sqlserver.server
> I have a procedure that loops through the databases and then their tables
> and I then want to run DBCC SHOWCONTIG on each table. However, it seems that
> DBCC SHOWCONTIG is insisting on only running in the current database, as
> defined by the USE statement, or perhaps the database the procedure is being
> run from , not sure as BOL is vague on this point.
> I tried adding:
> SET @.SQL = 'USE ' + @.DB
> EXEC sp_executesql @.SQL
> Which seems to execute fine.
> I then execute:
> EXEC ('DBCC SHOWCONTIG(''' + @.DB + '.' + @.Owner + '.' + @.Table + ''')')
> I also tried to get and use the OBJECT_ID.
> but I continue to get the same error:
> Server: Msg 2506, Level 16, State 1, Line 1
> Could not find a table or object name 'Data.dbo.sysobjects' in database
> 'master'.
> I have a bad feeling that DBCC SHOWCONTIG can only be run in the current
> database, hope I'm wrong.
>
>|||Works great, thanks
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3498EDC5-3762-4881-88AF-1E387056AE95@.microsoft.com...
> Jay,
>> SET @.SQL = 'USE ' + @.DB
>> EXEC sp_executesql @.SQL
> But it does not mean you are now in @.DB. Current context has not changed.
> use northwind
> go
> exec sp_executesql N'use pubs'
> go
> select db_name()
> go
> You have to add the USE statement as part of the batch.
> exec sp_executesql N'use AdventureWorks; dbcc showcontig
> (''dbo.ErrorLog'',
> 1)'
> go
>
> AMB
> "Jay" wrote:
>> Cross-posted in: microsoft.public.sqlserver.programming &
>> microsoft.public.sqlserver.server
>> I have a procedure that loops through the databases and then their tables
>> and I then want to run DBCC SHOWCONTIG on each table. However, it seems
>> that
>> DBCC SHOWCONTIG is insisting on only running in the current database, as
>> defined by the USE statement, or perhaps the database the procedure is
>> being
>> run from , not sure as BOL is vague on this point.
>> I tried adding:
>> SET @.SQL = 'USE ' + @.DB
>> EXEC sp_executesql @.SQL
>> Which seems to execute fine.
>> I then execute:
>> EXEC ('DBCC SHOWCONTIG(''' + @.DB + '.' + @.Owner + '.' + @.Table + ''')')
>> I also tried to get and use the OBJECT_ID.
>> but I continue to get the same error:
>> Server: Msg 2506, Level 16, State 1, Line 1
>> Could not find a table or object name 'Data.dbo.sysobjects' in database
>> 'master'.
>> I have a bad feeling that DBCC SHOWCONTIG can only be run in the current
>> database, hope I'm wrong.
>>
Wednesday, March 21, 2012
DBCC PROCCACHE question
How can I change procedure cache values.
Is it possible to change them only for one procedure.
If I have a huge procedure and small one I would like to allocate
values for both of them accordingly."inna" <mednyk@.hotmail.com> wrote in message
news:347a408b.0409150533.65df88b3@.posting.google.c om...
> How can I change procedure cache values.
> Is it possible to change them only for one procedure.
> If I have a huge procedure and small one I would like to allocate
> values for both of them accordingly.
Is it possible to change them only for one procedure.
If I have a huge procedure and small one I would like to allocate
values for both of them accordingly."inna" <mednyk@.hotmail.com> wrote in message
news:347a408b.0409150533.65df88b3@.posting.google.c om...
> How can I change procedure cache values.
> Is it possible to change them only for one procedure.
> If I have a huge procedure and small one I would like to allocate
> values for both of them accordingly.
You can't - MSSQL creates execution plans and manages the procedure cache
automatically. You can recompile a procedure, which forces MSSQL to create a
new plan (for example, after adding a new index), and you can empty the
entire cache, but that's about it. Check out "Execution Plan Caching and
Reuse" in Books Online for more details on how the cache works.
If you're having a specific problem, perhaps you could give some more
information, and someone may be able to suggest something.
Simon
Monday, March 19, 2012
DBCC My.dll (FREE)
Hello!
I wrote an extended stored procedure "MyStoredProcedure" implemented in
My.dll.
I registered "MyStoredProcedure" using :
USE master
EXEC sp_addextendedproc MyStoredProcedure, 'My.dll'
GO
My problem comes when I want to replace My.dll with a newer version.
How should I proceed if I don't want to stop SQL server.
Note : DBCC My.dll (FREE) raises an exception.
Is there another way to free My.dll?
Thank you!
What's the exception? I'm guessing its complaining about the DLL name.
Trying using
DBCC My (FREE)
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:Ooql9BK0EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I wrote an extended stored procedure "MyStoredProcedure" implemented in
> My.dll.
> I registered "MyStoredProcedure" using :
>
> USE master
> EXEC sp_addextendedproc MyStoredProcedure, 'My.dll'
> GO
>
> My problem comes when I want to replace My.dll with a newer version.
> How should I proceed if I don't want to stop SQL server.
> Note : DBCC My.dll (FREE) raises an exception.
> Is there another way to free My.dll?
>
> Thank you!
>
|||The first time I try to free My.dll using
DBCC My (FREE)
I receive the following exception :
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
If I execute the free command one more time, exception is not generated.
The second execution produces the following message for a succesful result :
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I wrote an extended stored procedure "MyStoredProcedure" implemented in
My.dll.
I registered "MyStoredProcedure" using :
USE master
EXEC sp_addextendedproc MyStoredProcedure, 'My.dll'
GO
My problem comes when I want to replace My.dll with a newer version.
How should I proceed if I don't want to stop SQL server.
Note : DBCC My.dll (FREE) raises an exception.
Is there another way to free My.dll?
Thank you!
What's the exception? I'm guessing its complaining about the DLL name.
Trying using
DBCC My (FREE)
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:Ooql9BK0EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I wrote an extended stored procedure "MyStoredProcedure" implemented in
> My.dll.
> I registered "MyStoredProcedure" using :
>
> USE master
> EXEC sp_addextendedproc MyStoredProcedure, 'My.dll'
> GO
>
> My problem comes when I want to replace My.dll with a newer version.
> How should I proceed if I don't want to stop SQL server.
> Note : DBCC My.dll (FREE) raises an exception.
> Is there another way to free My.dll?
>
> Thank you!
>
|||The first time I try to free My.dll using
DBCC My (FREE)
I receive the following exception :
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
If I execute the free command one more time, exception is not generated.
The second execution produces the following message for a succesful result :
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC My.dll (FREE)
Hello!
I wrote an extended stored procedure "MyStoredProcedure" implemented in
My.dll.
I registered "MyStoredProcedure" using :
USE master
EXEC sp_addextendedproc MyStoredProcedure, 'My.dll'
GO
My problem comes when I want to replace My.dll with a newer version.
How should I proceed if I don't want to stop SQL server.
Note : DBCC My.dll (FREE) raises an exception.
Is there another way to free My.dll?
Thank you!What's the exception? I'm guessing its complaining about the DLL name.
Trying using
DBCC My (FREE)
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:Ooql9BK0EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I wrote an extended stored procedure "MyStoredProcedure" implemented in
> My.dll.
> I registered "MyStoredProcedure" using :
>
> USE master
> EXEC sp_addextendedproc MyStoredProcedure, 'My.dll'
> GO
>
> My problem comes when I want to replace My.dll with a newer version.
> How should I proceed if I don't want to stop SQL server.
> Note : DBCC My.dll (FREE) raises an exception.
> Is there another way to free My.dll?
>
> Thank you!
>|||The first time I try to free My.dll using
DBCC My (FREE)
I receive the following exception :
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
If I execute the free command one more time, exception is not generated.
The second execution produces the following message for a succesful result :
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I wrote an extended stored procedure "MyStoredProcedure" implemented in
My.dll.
I registered "MyStoredProcedure" using :
USE master
EXEC sp_addextendedproc MyStoredProcedure, 'My.dll'
GO
My problem comes when I want to replace My.dll with a newer version.
How should I proceed if I don't want to stop SQL server.
Note : DBCC My.dll (FREE) raises an exception.
Is there another way to free My.dll?
Thank you!What's the exception? I'm guessing its complaining about the DLL name.
Trying using
DBCC My (FREE)
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:Ooql9BK0EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I wrote an extended stored procedure "MyStoredProcedure" implemented in
> My.dll.
> I registered "MyStoredProcedure" using :
>
> USE master
> EXEC sp_addextendedproc MyStoredProcedure, 'My.dll'
> GO
>
> My problem comes when I want to replace My.dll with a newer version.
> How should I proceed if I don't want to stop SQL server.
> Note : DBCC My.dll (FREE) raises an exception.
> Is there another way to free My.dll?
>
> Thank you!
>|||The first time I try to free My.dll using
DBCC My (FREE)
I receive the following exception :
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
If I execute the free command one more time, exception is not generated.
The second execution produces the following message for a succesful result :
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC My.dll (FREE)
Hello!
I wrote an extended stored procedure "MyStoredProcedure" implemented in
My.dll.
I registered "MyStoredProcedure" using :
USE master
EXEC sp_addextendedproc MyStoredProcedure, 'My.dll'
GO
My problem comes when I want to replace My.dll with a newer version.
How should I proceed if I don't want to stop SQL server.
Note : DBCC My.dll (FREE) raises an exception.
Is there another way to free My.dll?
Thank you!What's the exception? I'm guessing its complaining about the DLL name.
Trying using
DBCC My (FREE)
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:Ooql9BK0EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I wrote an extended stored procedure "MyStoredProcedure" implemented in
> My.dll.
> I registered "MyStoredProcedure" using :
>
> USE master
> EXEC sp_addextendedproc MyStoredProcedure, 'My.dll'
> GO
>
> My problem comes when I want to replace My.dll with a newer version.
> How should I proceed if I don't want to stop SQL server.
> Note : DBCC My.dll (FREE) raises an exception.
> Is there another way to free My.dll?
>
> Thank you!
>|||The first time I try to free My.dll using
DBCC My (FREE)
I receive the following exception :
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
If I execute the free command one more time, exception is not generated.
The second execution produces the following message for a succesful result :
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I wrote an extended stored procedure "MyStoredProcedure" implemented in
My.dll.
I registered "MyStoredProcedure" using :
USE master
EXEC sp_addextendedproc MyStoredProcedure, 'My.dll'
GO
My problem comes when I want to replace My.dll with a newer version.
How should I proceed if I don't want to stop SQL server.
Note : DBCC My.dll (FREE) raises an exception.
Is there another way to free My.dll?
Thank you!What's the exception? I'm guessing its complaining about the DLL name.
Trying using
DBCC My (FREE)
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:Ooql9BK0EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I wrote an extended stored procedure "MyStoredProcedure" implemented in
> My.dll.
> I registered "MyStoredProcedure" using :
>
> USE master
> EXEC sp_addextendedproc MyStoredProcedure, 'My.dll'
> GO
>
> My problem comes when I want to replace My.dll with a newer version.
> How should I proceed if I don't want to stop SQL server.
> Note : DBCC My.dll (FREE) raises an exception.
> Is there another way to free My.dll?
>
> Thank you!
>|||The first time I try to free My.dll using
DBCC My (FREE)
I receive the following exception :
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
If I execute the free command one more time, exception is not generated.
The second execution produces the following message for a succesful result :
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Sunday, March 11, 2012
DBCC INDEXDEFRAG
I've automated defragging my indexes with a stored procedure, but can't
capture the results of the defrag into a table. How can I go about this?
I've tried:
Declare @.sql varchar(1000)
Select @.sql = 'DBCC INDEXDEFRAG(''SOSManager'',''tmpKeys'','
'PK_tmpKeys'')'
Insert Into maint_IXDEFRAG (PagesScanned, PagesMoved, PagesRemoved)
exec(@.sql)
but I get the following error:
Cannot perform a IndexDefrag operation inside a user transaction. Terminate
the transaction and reissue the statement.
Ideas?The error message is pretty clear on this. An INSERT is one transaction, and
one of the thing with
INDEXDEFRAG is that it isn't all in one transaction (otherwise it would have
to keep locks etc.).
How about instead of having EXEC('string') in your INSERT, you have xp_cmdsh
ell from there you use
OSQL.EXE to execute your DBCC command? You will only get one column back fro
m xp_cmdshell so do some
post processing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
news:95EB0997-1324-4E22-A55D-97C6B9A258F4@.microsoft.com...
> I've automated defragging my indexes with a stored procedure, but can't
> capture the results of the defrag into a table. How can I go about this?
> I've tried:
> Declare @.sql varchar(1000)
> Select @.sql = 'DBCC INDEXDEFRAG(''SOSManager'',''tmpKeys'','
'PK_tmpKeys'')
'
> Insert Into maint_IXDEFRAG (PagesScanned, PagesMoved, PagesRemoved)
> exec(@.sql)
> but I get the following error:
> Cannot perform a IndexDefrag operation inside a user transaction. Terminat
e
> the transaction and reissue the statement.
> Ideas?
capture the results of the defrag into a table. How can I go about this?
I've tried:
Declare @.sql varchar(1000)
Select @.sql = 'DBCC INDEXDEFRAG(''SOSManager'',''tmpKeys'','
'PK_tmpKeys'')'
Insert Into maint_IXDEFRAG (PagesScanned, PagesMoved, PagesRemoved)
exec(@.sql)
but I get the following error:
Cannot perform a IndexDefrag operation inside a user transaction. Terminate
the transaction and reissue the statement.
Ideas?The error message is pretty clear on this. An INSERT is one transaction, and
one of the thing with
INDEXDEFRAG is that it isn't all in one transaction (otherwise it would have
to keep locks etc.).
How about instead of having EXEC('string') in your INSERT, you have xp_cmdsh
ell from there you use
OSQL.EXE to execute your DBCC command? You will only get one column back fro
m xp_cmdshell so do some
post processing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
news:95EB0997-1324-4E22-A55D-97C6B9A258F4@.microsoft.com...
> I've automated defragging my indexes with a stored procedure, but can't
> capture the results of the defrag into a table. How can I go about this?
> I've tried:
> Declare @.sql varchar(1000)
> Select @.sql = 'DBCC INDEXDEFRAG(''SOSManager'',''tmpKeys'','
'PK_tmpKeys'')
'
> Insert Into maint_IXDEFRAG (PagesScanned, PagesMoved, PagesRemoved)
> exec(@.sql)
> but I get the following error:
> Cannot perform a IndexDefrag operation inside a user transaction. Terminat
e
> the transaction and reissue the statement.
> Ideas?
Friday, February 24, 2012
DBCC Commands
Hi
Can we use
DBCC CHECKIDENT ('dbo.MyTable', RESEED, 0)
Command in Stored procedure. Which executes daily.
Thanks
Sridhar K
yes u can very well do. You can create a sp and then schedule the sp though Job. else u can include the script directly in Job
what is the probelm u faced when u tried?
Madhu
|||Thanks Madhu
I'm not having any problem. But I would like to know and as per my knowledge am not sure that these statements can execute daily on DB. Somebody said we have to use it periodically in maintenance.
Thanks
Sridhar K
|||Yes there are dbcc commands which used for database maintenance. Those are generally used periodically like DBCC DBREINDEX/IndexDefrag/Checkalloc/CheckDB etc etc...
Madhu
dbcc checktable(syslogs)
i have a procedure on sql 6.5 that runs the command dbcc checktable(syslogs)
.
I am migrating this to sql 2000 and am not sure how to replace this. I know
that it is checking the integrity of the log file but what is the equivalent
command in 2000 ?
Thanks in advance.The transaction log isn't a table anymore, so no need to do integrity checki
ng from a table-side
perspective. From SQL7 and onwards, the typical integrity checking performed
is DBCC CHECKDB and
DBCC CHECKCATALOG.
The old CHECKTABLE(syslogs) was often done to correct space usage informatio
n for the transaction
log (if memory serves me). This is not needed for the transaction log, DBCC
SQLPERF(logspace) isn't
dependent on sysindexes anymore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"billu" <billu@.discussions.microsoft.com> wrote in message
news:79BF04DB-287C-466B-9076-A81F32308435@.microsoft.com...
>i have a procedure on sql 6.5 that runs the command dbcc checktable(syslogs
).
> I am migrating this to sql 2000 and am not sure how to replace this. I kno
w
> that it is checking the integrity of the log file but what is the equivale
nt
> command in 2000 ?
> Thanks in advance.
.
I am migrating this to sql 2000 and am not sure how to replace this. I know
that it is checking the integrity of the log file but what is the equivalent
command in 2000 ?
Thanks in advance.The transaction log isn't a table anymore, so no need to do integrity checki
ng from a table-side
perspective. From SQL7 and onwards, the typical integrity checking performed
is DBCC CHECKDB and
DBCC CHECKCATALOG.
The old CHECKTABLE(syslogs) was often done to correct space usage informatio
n for the transaction
log (if memory serves me). This is not needed for the transaction log, DBCC
SQLPERF(logspace) isn't
dependent on sysindexes anymore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"billu" <billu@.discussions.microsoft.com> wrote in message
news:79BF04DB-287C-466B-9076-A81F32308435@.microsoft.com...
>i have a procedure on sql 6.5 that runs the command dbcc checktable(syslogs
).
> I am migrating this to sql 2000 and am not sure how to replace this. I kno
w
> that it is checking the integrity of the log file but what is the equivale
nt
> command in 2000 ?
> Thanks in advance.
Dbcc Checkident
Dear All,
I'm trying to run the DBCC CHECKIDENT procedure to reset identity seed to 1 as I need it in a temp table I create in a stored procedure.
Is there a way of running this procedure or one which resets identity seed under an account which is not part of the sys-admin group?From BOL about DBCC CHECKIDENT:
Caller must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.
Meaning that the user that created the temporary table also has permission to execute DBCC CHECKIDENT on it.|||Thanks for that. It works when I'm logged on as Administrator on the test server and also with an admin account on the live server.
However I get the following:
User '______.Gorgenyi' does not have permission to run DBCC CHECKIDENT for object '#tblRsWeek'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
All users get the same message. This is an ASP.NET web app.
Any suggestions?|||It seems like DBCC CHECKIDENT behaves differently on temporary tables. I haven't tested it myself so I can't give you any details on how and why.
A workaround to reset the identity to 1 is to truncate the temprary table.|||Thanks, TRUNCATE TABLE sorted the problem, no need to use DBCC Checkident
I'm trying to run the DBCC CHECKIDENT procedure to reset identity seed to 1 as I need it in a temp table I create in a stored procedure.
Is there a way of running this procedure or one which resets identity seed under an account which is not part of the sys-admin group?From BOL about DBCC CHECKIDENT:
Caller must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.
Meaning that the user that created the temporary table also has permission to execute DBCC CHECKIDENT on it.|||Thanks for that. It works when I'm logged on as Administrator on the test server and also with an admin account on the live server.
However I get the following:
User '______.Gorgenyi' does not have permission to run DBCC CHECKIDENT for object '#tblRsWeek'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
All users get the same message. This is an ASP.NET web app.
Any suggestions?|||It seems like DBCC CHECKIDENT behaves differently on temporary tables. I haven't tested it myself so I can't give you any details on how and why.
A workaround to reset the identity to 1 is to truncate the temprary table.|||Thanks, TRUNCATE TABLE sorted the problem, no need to use DBCC Checkident
Subscribe to:
Posts (Atom)