Hi
We have a DB in which we have created two file groups. Both are currently
Primary file group. what is the best way to find out which objects are in
which file group?
Thanks,
This doesn't make sense. "Both are currently Primary file group." Only one
filegroup can be the primary filegroup.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Moh" <mabbas@.Pier1.com> wrote in message
news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
> Hi
> We have a DB in which we have created two file groups. Both are currently
> Primary file group. what is the best way to find out which objects are in
> which file group?
> Thanks,
>
|||I can mail you screen shot of EM where two files are listed as primary file
groups.
Moh
"Tom Moreau" wrote:
> This doesn't make sense. "Both are currently Primary file group." Only one
> filegroup can be the primary filegroup.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Moh" <mabbas@.Pier1.com> wrote in message
> news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
>
>
|||That's not what you said. You said "we have created two file groups". Now,
you're saying you created two files. Which is it?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Moh" <mabbas@.Pier1.com> wrote in message
news:A0603CAB-EDF1-46BF-B449-D989432DF7DF@.microsoft.com...[vbcol=seagreen]
>I can mail you screen shot of EM where two files are listed as primary file
> groups.
> Moh
> "Tom Moreau" wrote:
|||By the way for your knowledge, you can have more than one files belongs to a
File group(Primary FG or any other). you can not have one file belongs to
more than one FG.
Does that mamke sense?
thx,
Moh
Sr DBA
Pier 1 Imports
mabbas@.Pier1.com
"Tom Moreau" wrote:
> This doesn't make sense. "Both are currently Primary file group." Only one
> filegroup can be the primary filegroup.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Moh" <mabbas@.Pier1.com> wrote in message
> news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
>
>
|||I'm already aware of that. So, what exactly is the problem?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Moh" <mabbas@.Pier1.com> wrote in message
news:D6BCED88-53DB-4DA8-BC41-00E355065EF0@.microsoft.com...[vbcol=seagreen]
> By the way for your knowledge, you can have more than one files belongs to
> a
> File group(Primary FG or any other). you can not have one file belongs to
> more than one FG.
> Does that mamke sense?
> thx,
> Moh
> --
> Sr DBA
> Pier 1 Imports
> mabbas@.Pier1.com
>
> "Tom Moreau" wrote:
|||a filegroup is composed of one or more files
Objects are placed on filegroups and not files
If you wanted to know on which filegroup an object is placed, use sp_help
@.objectname
or the undocumented sp_objectfilegroup @.objid
If you wanted to know the list of all objects that belong to a filegroup
you can query sysindexes and group by the result by groupid
Med Bouchenafa
"Moh" <mabbas@.Pier1.com> a crit dans le message de news:
B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
> Hi
> We have a DB in which we have created two file groups. Both are currently
> Primary file group. what is the best way to find out which objects are in
> which file group?
> Thanks,
>
|||Dear M Abbas,
From your 3 posts in this Response, it is clear that you need to improve in
following areas:
1. English Language Skills: There are both grammatical as well as spelling
mistakes in your posts. Example of Grammatical Mistake: ‘Both are currently
Primary file group’. Example of Spelling Mistake: ‘….mamke sense’.
2. Behavioural Skills: Tom Moreau didn’t make any wrong statements. It is
you who made wrong statement in the 1st post by saying ‘…we have created two
file groups. Both are currently Primary file group’. Still, you have
expressed anger in your 3rd post.
3. SQL Server Skills.
Remember, that the first person to benefit from these improvements will be
you. So choice is yours.
"Moh" wrote:
[vbcol=seagreen]
> By the way for your knowledge, you can have more than one files belongs to a
> File group(Primary FG or any other). you can not have one file belongs to
> more than one FG.
> Does that mamke sense?
> thx,
> Moh
> --
> Sr DBA
> Pier 1 Imports
> mabbas@.Pier1.com
>
> "Tom Moreau" wrote:
sql
Showing posts with label belongs. Show all posts
Showing posts with label belongs. Show all posts
Friday, March 23, 2012
how to find which object belongs to which File group
Hi
We have a DB in which we have created two file groups. Both are currently
Primary file group. what is the best way to find out which objects are in
which file group?
Thanks,This doesn't make sense. "Both are currently Primary file group." Only one
filegroup can be the primary filegroup.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Moh" <mabbas@.Pier1.com> wrote in message
news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
> Hi
> We have a DB in which we have created two file groups. Both are currently
> Primary file group. what is the best way to find out which objects are in
> which file group?
> Thanks,
>|||I can mail you screen shot of EM where two files are listed as primary file
groups.
Moh
"Tom Moreau" wrote:
> This doesn't make sense. "Both are currently Primary file group." Only on
e
> filegroup can be the primary filegroup.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Moh" <mabbas@.Pier1.com> wrote in message
> news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
>
>|||That's not what you said. You said "we have created two file groups". Now,
you're saying you created two files. Which is it?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Moh" <mabbas@.Pier1.com> wrote in message
news:A0603CAB-EDF1-46BF-B449-D989432DF7DF@.microsoft.com...[vbcol=seagreen]
>I can mail you screen shot of EM where two files are listed as primary file
> groups.
> Moh
> "Tom Moreau" wrote:
>|||By the way for your knowledge, you can have more than one files belongs to a
File group(Primary FG or any other). you can not have one file belongs to
more than one FG.
Does that mamke sense?
thx,
Moh
--
Sr DBA
Pier 1 Imports
mabbas@.Pier1.com
"Tom Moreau" wrote:
> This doesn't make sense. "Both are currently Primary file group." Only on
e
> filegroup can be the primary filegroup.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Moh" <mabbas@.Pier1.com> wrote in message
> news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
>
>|||I'm already aware of that. So, what exactly is the problem?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Moh" <mabbas@.Pier1.com> wrote in message
news:D6BCED88-53DB-4DA8-BC41-00E355065EF0@.microsoft.com...[vbcol=seagreen]
> By the way for your knowledge, you can have more than one files belongs to
> a
> File group(Primary FG or any other). you can not have one file belongs to
> more than one FG.
> Does that mamke sense?
> thx,
> Moh
> --
> Sr DBA
> Pier 1 Imports
> mabbas@.Pier1.com
>
> "Tom Moreau" wrote:
>|||a filegroup is composed of one or more files
Objects are placed on filegroups and not files
If you wanted to know on which filegroup an object is placed, use sp_help
@.objectname
or the undocumented sp_objectfilegroup @.objid
If you wanted to know the list of all objects that belong to a filegroup
you can query sysindexes and group by the result by groupid
Med Bouchenafa
"Moh" <mabbas@.Pier1.com> a crit dans le message de news:
B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
> Hi
> We have a DB in which we have created two file groups. Both are currently
> Primary file group. what is the best way to find out which objects are in
> which file group?
> Thanks,
>|||Dear M Abbas,
From your 3 posts in this Response, it is clear that you need to improve in
following areas:
1. English Language Skills: There are both grammatical as well as spelling
mistakes in your posts. Example of Grammatical Mistake: ‘Both are currentl
y
Primary file group’. Example of Spelling Mistake: ‘….mamke sense’.
2. Behavioural Skills: Tom Moreau didn’t make any wrong statements. It is
you who made wrong statement in the 1st post by saying ‘…we have created
two
file groups. Both are currently Primary file group’. Still, you have
expressed anger in your 3rd post.
3. SQL Server Skills.
Remember, that the first person to benefit from these improvements will be
you. So choice is yours.
"Moh" wrote:
[vbcol=seagreen]
> By the way for your knowledge, you can have more than one files belongs to
a
> File group(Primary FG or any other). you can not have one file belongs to
> more than one FG.
> Does that mamke sense?
> thx,
> Moh
> --
> Sr DBA
> Pier 1 Imports
> mabbas@.Pier1.com
>
> "Tom Moreau" wrote:
>
We have a DB in which we have created two file groups. Both are currently
Primary file group. what is the best way to find out which objects are in
which file group?
Thanks,This doesn't make sense. "Both are currently Primary file group." Only one
filegroup can be the primary filegroup.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Moh" <mabbas@.Pier1.com> wrote in message
news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
> Hi
> We have a DB in which we have created two file groups. Both are currently
> Primary file group. what is the best way to find out which objects are in
> which file group?
> Thanks,
>|||I can mail you screen shot of EM where two files are listed as primary file
groups.
Moh
"Tom Moreau" wrote:
> This doesn't make sense. "Both are currently Primary file group." Only on
e
> filegroup can be the primary filegroup.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Moh" <mabbas@.Pier1.com> wrote in message
> news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
>
>|||That's not what you said. You said "we have created two file groups". Now,
you're saying you created two files. Which is it?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Moh" <mabbas@.Pier1.com> wrote in message
news:A0603CAB-EDF1-46BF-B449-D989432DF7DF@.microsoft.com...[vbcol=seagreen]
>I can mail you screen shot of EM where two files are listed as primary file
> groups.
> Moh
> "Tom Moreau" wrote:
>|||By the way for your knowledge, you can have more than one files belongs to a
File group(Primary FG or any other). you can not have one file belongs to
more than one FG.
Does that mamke sense?
thx,
Moh
--
Sr DBA
Pier 1 Imports
mabbas@.Pier1.com
"Tom Moreau" wrote:
> This doesn't make sense. "Both are currently Primary file group." Only on
e
> filegroup can be the primary filegroup.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Moh" <mabbas@.Pier1.com> wrote in message
> news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
>
>|||I'm already aware of that. So, what exactly is the problem?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Moh" <mabbas@.Pier1.com> wrote in message
news:D6BCED88-53DB-4DA8-BC41-00E355065EF0@.microsoft.com...[vbcol=seagreen]
> By the way for your knowledge, you can have more than one files belongs to
> a
> File group(Primary FG or any other). you can not have one file belongs to
> more than one FG.
> Does that mamke sense?
> thx,
> Moh
> --
> Sr DBA
> Pier 1 Imports
> mabbas@.Pier1.com
>
> "Tom Moreau" wrote:
>|||a filegroup is composed of one or more files
Objects are placed on filegroups and not files
If you wanted to know on which filegroup an object is placed, use sp_help
@.objectname
or the undocumented sp_objectfilegroup @.objid
If you wanted to know the list of all objects that belong to a filegroup
you can query sysindexes and group by the result by groupid
Med Bouchenafa
"Moh" <mabbas@.Pier1.com> a crit dans le message de news:
B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
> Hi
> We have a DB in which we have created two file groups. Both are currently
> Primary file group. what is the best way to find out which objects are in
> which file group?
> Thanks,
>|||Dear M Abbas,
From your 3 posts in this Response, it is clear that you need to improve in
following areas:
1. English Language Skills: There are both grammatical as well as spelling
mistakes in your posts. Example of Grammatical Mistake: ‘Both are currentl
y
Primary file group’. Example of Spelling Mistake: ‘….mamke sense’.
2. Behavioural Skills: Tom Moreau didn’t make any wrong statements. It is
you who made wrong statement in the 1st post by saying ‘…we have created
two
file groups. Both are currently Primary file group’. Still, you have
expressed anger in your 3rd post.
3. SQL Server Skills.
Remember, that the first person to benefit from these improvements will be
you. So choice is yours.
"Moh" wrote:
[vbcol=seagreen]
> By the way for your knowledge, you can have more than one files belongs to
a
> File group(Primary FG or any other). you can not have one file belongs to
> more than one FG.
> Does that mamke sense?
> thx,
> Moh
> --
> Sr DBA
> Pier 1 Imports
> mabbas@.Pier1.com
>
> "Tom Moreau" wrote:
>
how to find which object belongs to which File group
Hi
We have a DB in which we have created two file groups. Both are currently
Primary file group. what is the best way to find out which objects are in
which file group?
Thanks,This doesn't make sense. "Both are currently Primary file group." Only one
filegroup can be the primary filegroup.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Moh" <mabbas@.Pier1.com> wrote in message
news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
> Hi
> We have a DB in which we have created two file groups. Both are currently
> Primary file group. what is the best way to find out which objects are in
> which file group?
> Thanks,
>|||I can mail you screen shot of EM where two files are listed as primary file
groups.
Moh
"Tom Moreau" wrote:
> This doesn't make sense. "Both are currently Primary file group." Only one
> filegroup can be the primary filegroup.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Moh" <mabbas@.Pier1.com> wrote in message
> news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
> > Hi
> > We have a DB in which we have created two file groups. Both are currently
> > Primary file group. what is the best way to find out which objects are in
> > which file group?
> >
> > Thanks,
> >
> >
>
>|||That's not what you said. You said "we have created two file groups". Now,
you're saying you created two files. Which is it?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Moh" <mabbas@.Pier1.com> wrote in message
news:A0603CAB-EDF1-46BF-B449-D989432DF7DF@.microsoft.com...
>I can mail you screen shot of EM where two files are listed as primary file
> groups.
> Moh
> "Tom Moreau" wrote:
>> This doesn't make sense. "Both are currently Primary file group." Only
>> one
>> filegroup can be the primary filegroup.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada tom@.cips.ca
>> www.pinpub.com
>> "Moh" <mabbas@.Pier1.com> wrote in message
>> news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
>> > Hi
>> > We have a DB in which we have created two file groups. Both are
>> > currently
>> > Primary file group. what is the best way to find out which objects are
>> > in
>> > which file group?
>> >
>> > Thanks,
>> >
>> >
>>|||By the way for your knowledge, you can have more than one files belongs to a
File group(Primary FG or any other). you can not have one file belongs to
more than one FG.
Does that mamke sense?
thx,
Moh
--
Sr DBA
Pier 1 Imports
mabbas@.Pier1.com
"Tom Moreau" wrote:
> This doesn't make sense. "Both are currently Primary file group." Only one
> filegroup can be the primary filegroup.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Moh" <mabbas@.Pier1.com> wrote in message
> news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
> > Hi
> > We have a DB in which we have created two file groups. Both are currently
> > Primary file group. what is the best way to find out which objects are in
> > which file group?
> >
> > Thanks,
> >
> >
>
>|||I'm already aware of that. So, what exactly is the problem?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Moh" <mabbas@.Pier1.com> wrote in message
news:D6BCED88-53DB-4DA8-BC41-00E355065EF0@.microsoft.com...
> By the way for your knowledge, you can have more than one files belongs to
> a
> File group(Primary FG or any other). you can not have one file belongs to
> more than one FG.
> Does that mamke sense?
> thx,
> Moh
> --
> Sr DBA
> Pier 1 Imports
> mabbas@.Pier1.com
>
> "Tom Moreau" wrote:
>> This doesn't make sense. "Both are currently Primary file group." Only
>> one
>> filegroup can be the primary filegroup.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada tom@.cips.ca
>> www.pinpub.com
>> "Moh" <mabbas@.Pier1.com> wrote in message
>> news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
>> > Hi
>> > We have a DB in which we have created two file groups. Both are
>> > currently
>> > Primary file group. what is the best way to find out which objects are
>> > in
>> > which file group?
>> >
>> > Thanks,
>> >
>> >
>>|||a filegroup is composed of one or more files
Objects are placed on filegroups and not files
If you wanted to know on which filegroup an object is placed, use sp_help
@.objectname
or the undocumented sp_objectfilegroup @.objid
If you wanted to know the list of all objects that belong to a filegroup
you can query sysindexes and group by the result by groupid
Med Bouchenafa
"Moh" <mabbas@.Pier1.com> a écrit dans le message de news:
B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
> Hi
> We have a DB in which we have created two file groups. Both are currently
> Primary file group. what is the best way to find out which objects are in
> which file group?
> Thanks,
>|||Dear M Abbas,
From your 3 posts in this Response, it is clear that you need to improve in
following areas:
1. English Language Skills: There are both grammatical as well as spelling
mistakes in your posts. Example of Grammatical Mistake: â'Both are currently
Primary file groupâ'. Example of Spelling Mistake: â'â?¦.mamke senseâ'.
2. Behavioural Skills: Tom Moreau didnâ't make any wrong statements. It is
you who made wrong statement in the 1st post by saying â'â?¦we have created two
file groups. Both are currently Primary file groupâ'. Still, you have
expressed anger in your 3rd post.
3. SQL Server Skills.
Remember, that the first person to benefit from these improvements will be
you. So choice is yours.
"Moh" wrote:
> By the way for your knowledge, you can have more than one files belongs to a
> File group(Primary FG or any other). you can not have one file belongs to
> more than one FG.
> Does that mamke sense?
> thx,
> Moh
> --
> Sr DBA
> Pier 1 Imports
> mabbas@.Pier1.com
>
> "Tom Moreau" wrote:
> > This doesn't make sense. "Both are currently Primary file group." Only one
> > filegroup can be the primary filegroup.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada tom@.cips.ca
> > www.pinpub.com
> >
> > "Moh" <mabbas@.Pier1.com> wrote in message
> > news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
> > > Hi
> > > We have a DB in which we have created two file groups. Both are currently
> > > Primary file group. what is the best way to find out which objects are in
> > > which file group?
> > >
> > > Thanks,
We have a DB in which we have created two file groups. Both are currently
Primary file group. what is the best way to find out which objects are in
which file group?
Thanks,This doesn't make sense. "Both are currently Primary file group." Only one
filegroup can be the primary filegroup.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Moh" <mabbas@.Pier1.com> wrote in message
news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
> Hi
> We have a DB in which we have created two file groups. Both are currently
> Primary file group. what is the best way to find out which objects are in
> which file group?
> Thanks,
>|||I can mail you screen shot of EM where two files are listed as primary file
groups.
Moh
"Tom Moreau" wrote:
> This doesn't make sense. "Both are currently Primary file group." Only one
> filegroup can be the primary filegroup.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Moh" <mabbas@.Pier1.com> wrote in message
> news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
> > Hi
> > We have a DB in which we have created two file groups. Both are currently
> > Primary file group. what is the best way to find out which objects are in
> > which file group?
> >
> > Thanks,
> >
> >
>
>|||That's not what you said. You said "we have created two file groups". Now,
you're saying you created two files. Which is it?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Moh" <mabbas@.Pier1.com> wrote in message
news:A0603CAB-EDF1-46BF-B449-D989432DF7DF@.microsoft.com...
>I can mail you screen shot of EM where two files are listed as primary file
> groups.
> Moh
> "Tom Moreau" wrote:
>> This doesn't make sense. "Both are currently Primary file group." Only
>> one
>> filegroup can be the primary filegroup.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada tom@.cips.ca
>> www.pinpub.com
>> "Moh" <mabbas@.Pier1.com> wrote in message
>> news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
>> > Hi
>> > We have a DB in which we have created two file groups. Both are
>> > currently
>> > Primary file group. what is the best way to find out which objects are
>> > in
>> > which file group?
>> >
>> > Thanks,
>> >
>> >
>>|||By the way for your knowledge, you can have more than one files belongs to a
File group(Primary FG or any other). you can not have one file belongs to
more than one FG.
Does that mamke sense?
thx,
Moh
--
Sr DBA
Pier 1 Imports
mabbas@.Pier1.com
"Tom Moreau" wrote:
> This doesn't make sense. "Both are currently Primary file group." Only one
> filegroup can be the primary filegroup.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Moh" <mabbas@.Pier1.com> wrote in message
> news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
> > Hi
> > We have a DB in which we have created two file groups. Both are currently
> > Primary file group. what is the best way to find out which objects are in
> > which file group?
> >
> > Thanks,
> >
> >
>
>|||I'm already aware of that. So, what exactly is the problem?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Moh" <mabbas@.Pier1.com> wrote in message
news:D6BCED88-53DB-4DA8-BC41-00E355065EF0@.microsoft.com...
> By the way for your knowledge, you can have more than one files belongs to
> a
> File group(Primary FG or any other). you can not have one file belongs to
> more than one FG.
> Does that mamke sense?
> thx,
> Moh
> --
> Sr DBA
> Pier 1 Imports
> mabbas@.Pier1.com
>
> "Tom Moreau" wrote:
>> This doesn't make sense. "Both are currently Primary file group." Only
>> one
>> filegroup can be the primary filegroup.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada tom@.cips.ca
>> www.pinpub.com
>> "Moh" <mabbas@.Pier1.com> wrote in message
>> news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
>> > Hi
>> > We have a DB in which we have created two file groups. Both are
>> > currently
>> > Primary file group. what is the best way to find out which objects are
>> > in
>> > which file group?
>> >
>> > Thanks,
>> >
>> >
>>|||a filegroup is composed of one or more files
Objects are placed on filegroups and not files
If you wanted to know on which filegroup an object is placed, use sp_help
@.objectname
or the undocumented sp_objectfilegroup @.objid
If you wanted to know the list of all objects that belong to a filegroup
you can query sysindexes and group by the result by groupid
Med Bouchenafa
"Moh" <mabbas@.Pier1.com> a écrit dans le message de news:
B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
> Hi
> We have a DB in which we have created two file groups. Both are currently
> Primary file group. what is the best way to find out which objects are in
> which file group?
> Thanks,
>|||Dear M Abbas,
From your 3 posts in this Response, it is clear that you need to improve in
following areas:
1. English Language Skills: There are both grammatical as well as spelling
mistakes in your posts. Example of Grammatical Mistake: â'Both are currently
Primary file groupâ'. Example of Spelling Mistake: â'â?¦.mamke senseâ'.
2. Behavioural Skills: Tom Moreau didnâ't make any wrong statements. It is
you who made wrong statement in the 1st post by saying â'â?¦we have created two
file groups. Both are currently Primary file groupâ'. Still, you have
expressed anger in your 3rd post.
3. SQL Server Skills.
Remember, that the first person to benefit from these improvements will be
you. So choice is yours.
"Moh" wrote:
> By the way for your knowledge, you can have more than one files belongs to a
> File group(Primary FG or any other). you can not have one file belongs to
> more than one FG.
> Does that mamke sense?
> thx,
> Moh
> --
> Sr DBA
> Pier 1 Imports
> mabbas@.Pier1.com
>
> "Tom Moreau" wrote:
> > This doesn't make sense. "Both are currently Primary file group." Only one
> > filegroup can be the primary filegroup.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada tom@.cips.ca
> > www.pinpub.com
> >
> > "Moh" <mabbas@.Pier1.com> wrote in message
> > news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
> > > Hi
> > > We have a DB in which we have created two file groups. Both are currently
> > > Primary file group. what is the best way to find out which objects are in
> > > which file group?
> > >
> > > Thanks,
Monday, March 19, 2012
How to find table from pageno in trace 1204 output?
We have trace output (trace flags 1204/1205) showing that
a deadlock occurred on a page (PAG).
How can we detemine ** which table ** the page belongs to?
(PAG is represented as PAG:db_id:file_id:page_no)
TIA, -- Brian
Here is the trace output:
Deadlock encountered ... Printing deadlock information
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Wait-for graph
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:1
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
2004-02-11 15:22:31.09 spid4 Grant List 0::
2004-02-11 15:22:31.09 spid4 Owner:0x28090dc0 Mode:
IX Flg:0x0 Ref:69 Life:02000000 SPID:70 ECID:0
2004-02-11 15:22:31.09 spid4 SPID: 70 ECID: 0
Statement Type: DELETE Line #: 12
2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
sp_executesql;1
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:4 Ec:(0x5CD00098)
Value:0x533e9d00 Cost:(0/0)
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:2
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241034 CleanCnt:1 Mode: SIU Flags: 0x2
2004-02-11 15:22:31.09 spid4 Grant List 0::
2004-02-11 15:22:31.09 spid4 Owner:0x5341eba0 Mode:
S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:3
2004-02-11 15:22:31.09 spid4 SPID: 65 ECID: 3
Statement Type: INSERT Line #: 118
2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
sp_executesql;1
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: IX SPID:70 ECID:0 Ec:(0x63C1D528)
Value:0x5344cb60 Cost:(0/43544)
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:3
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
2004-02-11 15:22:31.09 spid4 Wait List:
2004-02-11 15:22:31.09 spid4 Owner:0x533e9d00 Mode:
S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:4
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:3 Ec:(0x5CD02098)
Value:0x5341ebc0 Cost:(0/0)
2004-02-11 15:22:31.09 spid4 Victim Resource Owner:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:3 Ec:(0x5CD02098)
Value:0x5341ebc0 Cost:(0/0)
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 End deadlock search
1082 ... a deadlock was found.
2004-02-11 15:22:31.09 spid4 --
--Hi Brian
You can use dbcc page() to dump the page header, read the object id & follow
it back through index, to the table etc. Keep in mind that there are various
page types, but given this is a deadlock resource coming from a delete, it's
likely an index / table page.
There's a utility at www.sqlfe.com which helps with reading raw pages,
rather than using dbcc page..
HTH
Regards,
Greg Linwood
SQL Server MVP
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:f1ff01c3f113$4551fea0$a601280a@.phx.gbl...
> We have trace output (trace flags 1204/1205) showing that
> a deadlock occurred on a page (PAG).
> How can we detemine ** which table ** the page belongs to?
> (PAG is represented as PAG:db_id:file_id:page_no)
> TIA, -- Brian
> Here is the trace output:
> Deadlock encountered ... Printing deadlock information
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Wait-for graph
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:1
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Grant List 0::
> 2004-02-11 15:22:31.09 spid4 Owner:0x28090dc0 Mode:
> IX Flg:0x0 Ref:69 Life:02000000 SPID:70 ECID:0
> 2004-02-11 15:22:31.09 spid4 SPID: 70 ECID: 0
> Statement Type: DELETE Line #: 12
> 2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
> sp_executesql;1
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:4 Ec:(0x5CD00098)
> Value:0x533e9d00 Cost:(0/0)
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:2
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241034 CleanCnt:1 Mode: SIU Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Grant List 0::
> 2004-02-11 15:22:31.09 spid4 Owner:0x5341eba0 Mode:
> S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:3
> 2004-02-11 15:22:31.09 spid4 SPID: 65 ECID: 3
> Statement Type: INSERT Line #: 118
> 2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
> sp_executesql;1
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: IX SPID:70 ECID:0 Ec:(0x63C1D528)
> Value:0x5344cb60 Cost:(0/43544)
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:3
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Wait List:
> 2004-02-11 15:22:31.09 spid4 Owner:0x533e9d00 Mode:
> S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:4
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:3 Ec:(0x5CD02098)
> Value:0x5341ebc0 Cost:(0/0)
> 2004-02-11 15:22:31.09 spid4 Victim Resource Owner:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:3 Ec:(0x5CD02098)
> Value:0x5341ebc0 Cost:(0/0)
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 End deadlock search
> 1082 ... a deadlock was found.
> 2004-02-11 15:22:31.09 spid4 --
> --
>
a deadlock occurred on a page (PAG).
How can we detemine ** which table ** the page belongs to?
(PAG is represented as PAG:db_id:file_id:page_no)
TIA, -- Brian
Here is the trace output:
Deadlock encountered ... Printing deadlock information
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Wait-for graph
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:1
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
2004-02-11 15:22:31.09 spid4 Grant List 0::
2004-02-11 15:22:31.09 spid4 Owner:0x28090dc0 Mode:
IX Flg:0x0 Ref:69 Life:02000000 SPID:70 ECID:0
2004-02-11 15:22:31.09 spid4 SPID: 70 ECID: 0
Statement Type: DELETE Line #: 12
2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
sp_executesql;1
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:4 Ec:(0x5CD00098)
Value:0x533e9d00 Cost:(0/0)
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:2
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241034 CleanCnt:1 Mode: SIU Flags: 0x2
2004-02-11 15:22:31.09 spid4 Grant List 0::
2004-02-11 15:22:31.09 spid4 Owner:0x5341eba0 Mode:
S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:3
2004-02-11 15:22:31.09 spid4 SPID: 65 ECID: 3
Statement Type: INSERT Line #: 118
2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
sp_executesql;1
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: IX SPID:70 ECID:0 Ec:(0x63C1D528)
Value:0x5344cb60 Cost:(0/43544)
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:3
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
2004-02-11 15:22:31.09 spid4 Wait List:
2004-02-11 15:22:31.09 spid4 Owner:0x533e9d00 Mode:
S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:4
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:3 Ec:(0x5CD02098)
Value:0x5341ebc0 Cost:(0/0)
2004-02-11 15:22:31.09 spid4 Victim Resource Owner:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:3 Ec:(0x5CD02098)
Value:0x5341ebc0 Cost:(0/0)
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 End deadlock search
1082 ... a deadlock was found.
2004-02-11 15:22:31.09 spid4 --
--Hi Brian
You can use dbcc page() to dump the page header, read the object id & follow
it back through index, to the table etc. Keep in mind that there are various
page types, but given this is a deadlock resource coming from a delete, it's
likely an index / table page.
There's a utility at www.sqlfe.com which helps with reading raw pages,
rather than using dbcc page..
HTH
Regards,
Greg Linwood
SQL Server MVP
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:f1ff01c3f113$4551fea0$a601280a@.phx.gbl...
> We have trace output (trace flags 1204/1205) showing that
> a deadlock occurred on a page (PAG).
> How can we detemine ** which table ** the page belongs to?
> (PAG is represented as PAG:db_id:file_id:page_no)
> TIA, -- Brian
> Here is the trace output:
> Deadlock encountered ... Printing deadlock information
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Wait-for graph
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:1
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Grant List 0::
> 2004-02-11 15:22:31.09 spid4 Owner:0x28090dc0 Mode:
> IX Flg:0x0 Ref:69 Life:02000000 SPID:70 ECID:0
> 2004-02-11 15:22:31.09 spid4 SPID: 70 ECID: 0
> Statement Type: DELETE Line #: 12
> 2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
> sp_executesql;1
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:4 Ec:(0x5CD00098)
> Value:0x533e9d00 Cost:(0/0)
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:2
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241034 CleanCnt:1 Mode: SIU Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Grant List 0::
> 2004-02-11 15:22:31.09 spid4 Owner:0x5341eba0 Mode:
> S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:3
> 2004-02-11 15:22:31.09 spid4 SPID: 65 ECID: 3
> Statement Type: INSERT Line #: 118
> 2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
> sp_executesql;1
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: IX SPID:70 ECID:0 Ec:(0x63C1D528)
> Value:0x5344cb60 Cost:(0/43544)
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:3
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Wait List:
> 2004-02-11 15:22:31.09 spid4 Owner:0x533e9d00 Mode:
> S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:4
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:3 Ec:(0x5CD02098)
> Value:0x5341ebc0 Cost:(0/0)
> 2004-02-11 15:22:31.09 spid4 Victim Resource Owner:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:3 Ec:(0x5CD02098)
> Value:0x5341ebc0 Cost:(0/0)
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 End deadlock search
> 1082 ... a deadlock was found.
> 2004-02-11 15:22:31.09 spid4 --
> --
>
How to find table from pageno in trace 1204 output?
We have trace output (trace flags 1204/1205) showing that
a deadlock occurred on a page (PAG).
How can we detemine ** which table ** the page belongs to?
(PAG is represented as PAG:db_id:file_id:page_no)
TIA, -- Brian
Here is the trace output:
Deadlock encountered ... Printing deadlock information
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Wait-for graph
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:1
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
2004-02-11 15:22:31.09 spid4 Grant List 0::
2004-02-11 15:22:31.09 spid4 Owner:0x28090dc0 Mode:
IX Flg:0x0 Ref:69 Life:02000000 SPID:70 ECID:0
2004-02-11 15:22:31.09 spid4 SPID: 70 ECID: 0
Statement Type: DELETE Line #: 12
2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
sp_executesql;1
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:4 Ec
0x5CD00098)
Value:0x533e9d00 Cost0/0)
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:2
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241034 CleanCnt:1 Mode: SIU Flags: 0x2
2004-02-11 15:22:31.09 spid4 Grant List 0::
2004-02-11 15:22:31.09 spid4 Owner:0x5341eba0 Mode:
S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:3
2004-02-11 15:22:31.09 spid4 SPID: 65 ECID: 3
Statement Type: INSERT Line #: 118
2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
sp_executesql;1
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: IX SPID:70 ECID:0 Ec0x63C1D528)
Value:0x5344cb60 Cost0/43544)
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:3
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
2004-02-11 15:22:31.09 spid4 Wait List:
2004-02-11 15:22:31.09 spid4 Owner:0x533e9d00 Mode:
S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:4
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:3 Ec0x5CD02098)
Value:0x5341ebc0 Cost0/0)
2004-02-11 15:22:31.09 spid4 Victim Resource Owner:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:3 Ec0x5CD02098)
Value:0x5341ebc0 Cost0/0)
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 End deadlock search
1082 ... a deadlock was found.
2004-02-11 15:22:31.09 spid4 --
--Hi Brian
You can use dbcc page() to dump the page header, read the object id & follow
it back through index, to the table etc. Keep in mind that there are various
page types, but given this is a deadlock resource coming from a delete, it's
likely an index / table page.
There's a utility at www.sqlfe.com which helps with reading raw pages,
rather than using dbcc page..
HTH
Regards,
Greg Linwood
SQL Server MVP
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:f1ff01c3f113$4551fea0$a601280a@.phx.gbl...
> We have trace output (trace flags 1204/1205) showing that
> a deadlock occurred on a page (PAG).
> How can we detemine ** which table ** the page belongs to?
> (PAG is represented as PAG:db_id:file_id:page_no)
> TIA, -- Brian
> Here is the trace output:
> Deadlock encountered ... Printing deadlock information
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Wait-for graph
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:1
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Grant List 0::
> 2004-02-11 15:22:31.09 spid4 Owner:0x28090dc0 Mode:
> IX Flg:0x0 Ref:69 Life:02000000 SPID:70 ECID:0
> 2004-02-11 15:22:31.09 spid4 SPID: 70 ECID: 0
> Statement Type: DELETE Line #: 12
> 2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
> sp_executesql;1
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:4 Ec0x5CD00098)
> Value:0x533e9d00 Cost0/0)
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:2
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241034 CleanCnt:1 Mode: SIU Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Grant List 0::
> 2004-02-11 15:22:31.09 spid4 Owner:0x5341eba0 Mode:
> S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:3
> 2004-02-11 15:22:31.09 spid4 SPID: 65 ECID: 3
> Statement Type: INSERT Line #: 118
> 2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
> sp_executesql;1
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: IX SPID:70 ECID:0 Ec0x63C1D528)
> Value:0x5344cb60 Cost0/43544)
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:3
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Wait List:
> 2004-02-11 15:22:31.09 spid4 Owner:0x533e9d00 Mode:
> S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:4
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:3 Ec0x5CD02098)
> Value:0x5341ebc0 Cost0/0)
> 2004-02-11 15:22:31.09 spid4 Victim Resource Owner:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:3 Ec0x5CD02098)
> Value:0x5341ebc0 Cost0/0)
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 End deadlock search
> 1082 ... a deadlock was found.
> 2004-02-11 15:22:31.09 spid4 --
> --
>
a deadlock occurred on a page (PAG).
How can we detemine ** which table ** the page belongs to?
(PAG is represented as PAG:db_id:file_id:page_no)
TIA, -- Brian
Here is the trace output:
Deadlock encountered ... Printing deadlock information
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Wait-for graph
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:1
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
2004-02-11 15:22:31.09 spid4 Grant List 0::
2004-02-11 15:22:31.09 spid4 Owner:0x28090dc0 Mode:
IX Flg:0x0 Ref:69 Life:02000000 SPID:70 ECID:0
2004-02-11 15:22:31.09 spid4 SPID: 70 ECID: 0
Statement Type: DELETE Line #: 12
2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
sp_executesql;1
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:4 Ec
0x5CD00098)Value:0x533e9d00 Cost
0/0)2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:2
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241034 CleanCnt:1 Mode: SIU Flags: 0x2
2004-02-11 15:22:31.09 spid4 Grant List 0::
2004-02-11 15:22:31.09 spid4 Owner:0x5341eba0 Mode:
S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:3
2004-02-11 15:22:31.09 spid4 SPID: 65 ECID: 3
Statement Type: INSERT Line #: 118
2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
sp_executesql;1
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: IX SPID:70 ECID:0 Ec
0x63C1D528)Value:0x5344cb60 Cost
0/43544)2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:3
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
2004-02-11 15:22:31.09 spid4 Wait List:
2004-02-11 15:22:31.09 spid4 Owner:0x533e9d00 Mode:
S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:4
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:3 Ec
0x5CD02098)Value:0x5341ebc0 Cost
0/0)2004-02-11 15:22:31.09 spid4 Victim Resource Owner:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:3 Ec
0x5CD02098)Value:0x5341ebc0 Cost
0/0)2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 End deadlock search
1082 ... a deadlock was found.
2004-02-11 15:22:31.09 spid4 --
--Hi Brian
You can use dbcc page() to dump the page header, read the object id & follow
it back through index, to the table etc. Keep in mind that there are various
page types, but given this is a deadlock resource coming from a delete, it's
likely an index / table page.
There's a utility at www.sqlfe.com which helps with reading raw pages,
rather than using dbcc page..
HTH
Regards,
Greg Linwood
SQL Server MVP
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:f1ff01c3f113$4551fea0$a601280a@.phx.gbl...
> We have trace output (trace flags 1204/1205) showing that
> a deadlock occurred on a page (PAG).
> How can we detemine ** which table ** the page belongs to?
> (PAG is represented as PAG:db_id:file_id:page_no)
> TIA, -- Brian
> Here is the trace output:
> Deadlock encountered ... Printing deadlock information
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Wait-for graph
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:1
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Grant List 0::
> 2004-02-11 15:22:31.09 spid4 Owner:0x28090dc0 Mode:
> IX Flg:0x0 Ref:69 Life:02000000 SPID:70 ECID:0
> 2004-02-11 15:22:31.09 spid4 SPID: 70 ECID: 0
> Statement Type: DELETE Line #: 12
> 2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
> sp_executesql;1
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:4 Ec
0x5CD00098)> Value:0x533e9d00 Cost
0/0)> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:2
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241034 CleanCnt:1 Mode: SIU Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Grant List 0::
> 2004-02-11 15:22:31.09 spid4 Owner:0x5341eba0 Mode:
> S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:3
> 2004-02-11 15:22:31.09 spid4 SPID: 65 ECID: 3
> Statement Type: INSERT Line #: 118
> 2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
> sp_executesql;1
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: IX SPID:70 ECID:0 Ec
0x63C1D528)> Value:0x5344cb60 Cost
0/43544)> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:3
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Wait List:
> 2004-02-11 15:22:31.09 spid4 Owner:0x533e9d00 Mode:
> S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:4
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:3 Ec
0x5CD02098)> Value:0x5341ebc0 Cost
0/0)> 2004-02-11 15:22:31.09 spid4 Victim Resource Owner:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:3 Ec
0x5CD02098)> Value:0x5341ebc0 Cost
0/0)> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 End deadlock search
> 1082 ... a deadlock was found.
> 2004-02-11 15:22:31.09 spid4 --
> --
>
Sunday, February 19, 2012
How to find a table...
Hi all,
I have a large database with many tables created by a 3rd party vendor.
I would like to be able to find which table a field belongs to without
searching through every single table. Is there some tool out there to
enable such a search?
ThanksSelect * from INFORMATION_SCHEMA.COLUMNS Where Column_Name like
'<YourColumnName>'
HTH, Jens Suessmeyer.|||To find all occurences of a *column* named "foo" use
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='foo'
"AGB" <andyglbl@.gmail.com> wrote in message
news:1126623946.728072.130780@.f14g2000cwb.googlegroups.com...
> Hi all,
> I have a large database with many tables created by a 3rd party vendor.
> I would like to be able to find which table a field belongs to without
> searching through every single table. Is there some tool out there to
> enable such a search?
> Thanks
>|||Here's an example, that searches for tables with a column name of 'Alias':
SELECT TABLE_SCHEMA AS TableOwner, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'Alias'
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"AGB" <andyglbl@.gmail.com> wrote in message
news:1126623946.728072.130780@.f14g2000cwb.googlegroups.com...
Hi all,
I have a large database with many tables created by a 3rd party vendor.
I would like to be able to find which table a field belongs to without
searching through every single table. Is there some tool out there to
enable such a search?
Thanks|||There is a feature in Query Analyzer for searching objects across a server
by name and/or object type. Look under the menu option Tools.. Object
Search.. New..
"AGB" <andyglbl@.gmail.com> wrote in message
news:1126623946.728072.130780@.f14g2000cwb.googlegroups.com...
> Hi all,
> I have a large database with many tables created by a 3rd party vendor.
> I would like to be able to find which table a field belongs to without
> searching through every single table. Is there some tool out there to
> enable such a search?
> Thanks
>|||hi,
All replies to this will solve the problem ... no doubt about this.
There is one interesting tool to perform such activities more in advance
SQLcompare...from redgate.
thanxs
Kishor
"AGB" wrote:
> Hi all,
> I have a large database with many tables created by a 3rd party vendor.
> I would like to be able to find which table a field belongs to without
> searching through every single table. Is there some tool out there to
> enable such a search?
> Thanks
>
I have a large database with many tables created by a 3rd party vendor.
I would like to be able to find which table a field belongs to without
searching through every single table. Is there some tool out there to
enable such a search?
ThanksSelect * from INFORMATION_SCHEMA.COLUMNS Where Column_Name like
'<YourColumnName>'
HTH, Jens Suessmeyer.|||To find all occurences of a *column* named "foo" use
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='foo'
"AGB" <andyglbl@.gmail.com> wrote in message
news:1126623946.728072.130780@.f14g2000cwb.googlegroups.com...
> Hi all,
> I have a large database with many tables created by a 3rd party vendor.
> I would like to be able to find which table a field belongs to without
> searching through every single table. Is there some tool out there to
> enable such a search?
> Thanks
>|||Here's an example, that searches for tables with a column name of 'Alias':
SELECT TABLE_SCHEMA AS TableOwner, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'Alias'
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"AGB" <andyglbl@.gmail.com> wrote in message
news:1126623946.728072.130780@.f14g2000cwb.googlegroups.com...
Hi all,
I have a large database with many tables created by a 3rd party vendor.
I would like to be able to find which table a field belongs to without
searching through every single table. Is there some tool out there to
enable such a search?
Thanks|||There is a feature in Query Analyzer for searching objects across a server
by name and/or object type. Look under the menu option Tools.. Object
Search.. New..
"AGB" <andyglbl@.gmail.com> wrote in message
news:1126623946.728072.130780@.f14g2000cwb.googlegroups.com...
> Hi all,
> I have a large database with many tables created by a 3rd party vendor.
> I would like to be able to find which table a field belongs to without
> searching through every single table. Is there some tool out there to
> enable such a search?
> Thanks
>|||hi,
All replies to this will solve the problem ... no doubt about this.
There is one interesting tool to perform such activities more in advance
SQLcompare...from redgate.
thanxs
Kishor
"AGB" wrote:
> Hi all,
> I have a large database with many tables created by a 3rd party vendor.
> I would like to be able to find which table a field belongs to without
> searching through every single table. Is there some tool out there to
> enable such a search?
> Thanks
>
Subscribe to:
Posts (Atom)