DBCC PAGE

Can anyone tell me how to get the FileNumber & PageNumber
that a particualar row of data is physically stored on?
I need to check the physical contents of row using DBCC
PAGE but this needs to be given the FileNumber &
PageNumber as parameters.
Steve Morgan
MCP, MCDBA, MCT
Reach Telecom SQL Server DBA
Please reply to the group, not to me!look at www.sqlmag.com for InstantDoc #8097.
"Steve Morgan" <steve.morgan@.reachtelecom.co.ukNOSPAM> wrote in message
news:008301c37882$cc197d50$a401280a@.phx.gbl...
> Can anyone tell me how to get the FileNumber & PageNumber
> that a particualar row of data is physically stored on?
> I need to check the physical contents of row using DBCC
> PAGE but this needs to be given the FileNumber &
> PageNumber as parameters.
>
> Steve Morgan
> MCP, MCDBA, MCT
> Reach Telecom SQL Server DBA
> Please reply to the group, not to me!|||There is no direct way to map a row to a page number.
As the author of SQL Mag's article 8097, I can say that usually I use DBCC
IND to list all the file number, page number values for a table, and then I
start guessing. If there is a clustered index, you can narrow it down pretty
well, and DBCC IND also shows you the next and previous page so you can
build the linked list of ordered pages.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Steve Morgan" <steve.morgan@.reachtelecom.co.ukNOSPAM> wrote in message
news:008301c37882$cc197d50$a401280a@.phx.gbl...
> Can anyone tell me how to get the FileNumber & PageNumber
> that a particualar row of data is physically stored on?
> I need to check the physical contents of row using DBCC
> PAGE but this needs to be given the FileNumber &
> PageNumber as parameters.
>
> Steve Morgan
> MCP, MCDBA, MCT
> Reach Telecom SQL Server DBA
> Please reply to the group, not to me!|||Go to www.sqlfe.com and download the tool. You will find it easy to find
the pages allocated to your table.
--
Barry McAuslin
"Steve Morgan" <steve.morgan@.reachtelecom.co.ukNOSPAM> wrote in message
news:008301c37882$cc197d50$a401280a@.phx.gbl...
> Can anyone tell me how to get the FileNumber & PageNumber
> that a particualar row of data is physically stored on?
> I need to check the physical contents of row using DBCC
> PAGE but this needs to be given the FileNumber &
> PageNumber as parameters.
>
> Steve Morgan
> MCP, MCDBA, MCT
> Reach Telecom SQL Server DBA
> Please reply to the group, not to me!|||Interesting tool!
But it still might be a choice like between EM and QA, GUI or text command.
I can find the pages that BELONG to a table really quickly with DBCC IND.
But does this tool answer the poster's original question... how to find the
page a particular row is on?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:OSERX5OeDHA.1680@.TK2MSFTNGP09.phx.gbl...
> Go to www.sqlfe.com and download the tool. You will find it easy to find
> the pages allocated to your table.
> --
> Barry McAuslin
> "Steve Morgan" <steve.morgan@.reachtelecom.co.ukNOSPAM> wrote in message
> news:008301c37882$cc197d50$a401280a@.phx.gbl...
> > Can anyone tell me how to get the FileNumber & PageNumber
> > that a particualar row of data is physically stored on?
> >
> > I need to check the physical contents of row using DBCC
> > PAGE but this needs to be given the FileNumber &
> > PageNumber as parameters.
> >
> >
> >
> > Steve Morgan
> > MCP, MCDBA, MCT
> > Reach Telecom SQL Server DBA
> >
> > Please reply to the group, not to me!
>|||Not directly, but it is more of a follow on to your post. If you view the
indexes, select the table you are after from the list, then the extents that
the table are in will be highlighted. Double clicking on the extent will
give you the DBCC PAGE output from the pages in the extent.
I got the idea from a tool by another database vendor, thought I would like
that for MS SQL. I couldn't find anything! So after reading your book and
working out how to put it all together I decided to write it in my spare
time. Excellent book by the way.
Thanks
--
Barry McAuslin
http://www.sqlfe.com
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OEuTSGTeDHA.2432@.TK2MSFTNGP09.phx.gbl...
> Interesting tool!
> But it still might be a choice like between EM and QA, GUI or text
command.
> I can find the pages that BELONG to a table really quickly with DBCC IND.
> But does this tool answer the poster's original question... how to find
the
> page a particular row is on?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> news:OSERX5OeDHA.1680@.TK2MSFTNGP09.phx.gbl...
> > Go to www.sqlfe.com and download the tool. You will find it easy to
find
> > the pages allocated to your table.
> >
> > --
> > Barry McAuslin
> >
> > "Steve Morgan" <steve.morgan@.reachtelecom.co.ukNOSPAM> wrote in message
> > news:008301c37882$cc197d50$a401280a@.phx.gbl...
> > > Can anyone tell me how to get the FileNumber & PageNumber
> > > that a particualar row of data is physically stored on?
> > >
> > > I need to check the physical contents of row using DBCC
> > > PAGE but this needs to be given the FileNumber &
> > > PageNumber as parameters.
> > >
> > >
> > >
> > > Steve Morgan
> > > MCP, MCDBA, MCT
> > > Reach Telecom SQL Server DBA
> > >
> > > Please reply to the group, not to me!
> >
> >
>|||Interesting idea, but maybe you can fix the tool, so it will also work
against a case sensitive server. When I switch to the log I am getting a
FileID not found, looks like you are creating temp tables with very
inconsistent naming and later query for the wrong column name (case wise).
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:OSERX5OeDHA.1680@.TK2MSFTNGP09.phx.gbl...
> Go to www.sqlfe.com and download the tool. You will find it easy to find
> the pages allocated to your table.
> --
> Barry McAuslin
> "Steve Morgan" <steve.morgan@.reachtelecom.co.ukNOSPAM> wrote in message
> news:008301c37882$cc197d50$a401280a@.phx.gbl...
> > Can anyone tell me how to get the FileNumber & PageNumber
> > that a particualar row of data is physically stored on?
> >
> > I need to check the physical contents of row using DBCC
> > PAGE but this needs to be given the FileNumber &
> > PageNumber as parameters.
> >
> >
> >
> > Steve Morgan
> > MCP, MCDBA, MCT
> > Reach Telecom SQL Server DBA
> >
> > Please reply to the group, not to me!
>|||Thanks for that. I will have a fix on the web site in a couple of days.
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Gert E.R. Drapers" <GertD@.Online.SQLDev.Net> wrote in message
news:uKewyL9eDHA.3104@.TK2MSFTNGP11.phx.gbl...
> Interesting idea, but maybe you can fix the tool, so it will also work
> against a case sensitive server. When I switch to the log I am getting a
> FileID not found, looks like you are creating temp tables with very
> inconsistent naming and later query for the wrong column name (case wise).
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2003 All rights reserved.
> "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> news:OSERX5OeDHA.1680@.TK2MSFTNGP09.phx.gbl...
> > Go to www.sqlfe.com and download the tool. You will find it easy to
find
> > the pages allocated to your table.
> >
> > --
> > Barry McAuslin
> >
> > "Steve Morgan" <steve.morgan@.reachtelecom.co.ukNOSPAM> wrote in message
> > news:008301c37882$cc197d50$a401280a@.phx.gbl...
> > > Can anyone tell me how to get the FileNumber & PageNumber
> > > that a particualar row of data is physically stored on?
> > >
> > > I need to check the physical contents of row using DBCC
> > > PAGE but this needs to be given the FileNumber &
> > > PageNumber as parameters.
> > >
> > >
> > >
> > > Steve Morgan
> > > MCP, MCDBA, MCT
> > > Reach Telecom SQL Server DBA
> > >
> > > Please reply to the group, not to me!
> >
> >
>

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.

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.

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.

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?

DBCC DBREINDEX not working

I have a stored proc that identifies indexes that need to be
defragmented ( LogicalFragmentation > 20% ) using DBCC SHOWCONTIG.
This works fine and shows me all of the candidate indexes to be
defragged. I am using DBCC DBREINDEX to rebuild the indexes. What I
noticed yesterday is that it seems that DBREINDEX is not actually doing
anything. I would check the target indexes, run DBREINDEX, then check
again. The same list of indexes came up unchanged.

What is strange is although I am not specifying "WITH NO_INFOMSGS" this
is the only output from the DBREINDEX:
"DBCC execution completed. If DBCC printed error messages, contact your
system administrator."

According to BOL DBREINDEX should return a result set if "NO_INFOMSGS"
is not specified, but if "NO_INFOMSGS" is supplied the prior message is
what is returned. This doesn't seem to be working correctly, or if it
is, I have no idea where any error messages are being returned.

Anyone have any ideas?In this case, the documentation from Books Online is incorrect. The
documentation has been corrected in SQL Server 2005, where it says:

Result Sets

Whether any one of the options, except NO_INFOMSGS, is specified
(the table name must be specified), DBCC DBREINDEX returns:

DBCC execution completed. If DBCC printed error messages,
contact your system administrator.

Razvan|||Nice documentation.

Thanks for the heads-up, Razvan.

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 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