Hi All,
We are putting in a SQL 2000 Server and my boss wants to know how we can run "Determine how SQL-Server runs jobs automatically". Any help or KB articles would be appreciated.
Thanks,
JohnSQL Server scheduled jobs are run by the SQl Server Agent. Check Books
OnLine for more info.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"John Chase" <johnc@.hcd.net> wrote in message
news:3A34B4A6-BF80-469C-8BEC-B2D284A97094@.microsoft.com...
> Hi All,
> We are putting in a SQL 2000 Server and my boss wants to know how we can
run "Determine how SQL-Server runs jobs automatically". Any help or KB
articles would be appreciated.
> Thanks,
> John|||There is a service that gets installed with SQL Server called the SQL Server
Agent. Once this service is started, you can go into Enterprise
Manager ->your server->Management->SQL Server Agent->Jobs and create a new
job, and schedule it. Look in Books on Line once you have it installed, or
search the KB for SQL Server Agent. That should get you started.
Jackie
"John Chase" <johnc@.hcd.net> wrote in message
news:3A34B4A6-BF80-469C-8BEC-B2D284A97094@.microsoft.com...
> Hi All,
> We are putting in a SQL 2000 Server and my boss wants to know how we can
run "Determine how SQL-Server runs jobs automatically". Any help or KB
articles would be appreciated.
> Thanks,
> John
Showing posts with label determine. Show all posts
Showing posts with label determine. Show all posts
Monday, March 19, 2012
Friday, March 9, 2012
How do I find what version of SQL Server is installed?
Hello:
I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
editions.
Can you tell me how I can determine which flavor is installed?
I thought I had Standard Edition but when I tried to install Standard
Edition of the Reporting services, it said that it was a wrong type.
Thanks.
Venki
Sorry, I should have looked up the helpfile. I used the @.@.Version to realize
that I was running the developer's edition.
Venki
"vvenk" wrote:
> Hello:
> I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
> editions.
> Can you tell me how I can determine which flavor is installed?
> I thought I had Standard Edition but when I tried to install Standard
> Edition of the Reporting services, it said that it was a wrong type.
> Thanks.
> Venki
|||use master
select @.@.version
"vvenk" wrote:
> Hello:
> I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
> editions.
> Can you tell me how I can determine which flavor is installed?
> I thought I had Standard Edition but when I tried to install Standard
> Edition of the Reporting services, it said that it was a wrong type.
> Thanks.
> Venki
|||Hi,
@.@.Version will serve you requirement. But have a look into the below link to
get all the versions for each patch level sql server.
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=37
Thanks
Hari
SQL Server MVP
"vvenk" <vvenk@.discussions.microsoft.com> wrote in message
news:049E3C1C-ACF1-4D28-B659-475767027800@.microsoft.com...[vbcol=seagreen]
> Sorry, I should have looked up the helpfile. I used the @.@.Version to
> realize
> that I was running the developer's edition.
> Venki
> "vvenk" wrote:
I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
editions.
Can you tell me how I can determine which flavor is installed?
I thought I had Standard Edition but when I tried to install Standard
Edition of the Reporting services, it said that it was a wrong type.
Thanks.
Venki
Sorry, I should have looked up the helpfile. I used the @.@.Version to realize
that I was running the developer's edition.
Venki
"vvenk" wrote:
> Hello:
> I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
> editions.
> Can you tell me how I can determine which flavor is installed?
> I thought I had Standard Edition but when I tried to install Standard
> Edition of the Reporting services, it said that it was a wrong type.
> Thanks.
> Venki
|||use master
select @.@.version
"vvenk" wrote:
> Hello:
> I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
> editions.
> Can you tell me how I can determine which flavor is installed?
> I thought I had Standard Edition but when I tried to install Standard
> Edition of the Reporting services, it said that it was a wrong type.
> Thanks.
> Venki
|||Hi,
@.@.Version will serve you requirement. But have a look into the below link to
get all the versions for each patch level sql server.
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=37
Thanks
Hari
SQL Server MVP
"vvenk" <vvenk@.discussions.microsoft.com> wrote in message
news:049E3C1C-ACF1-4D28-B659-475767027800@.microsoft.com...[vbcol=seagreen]
> Sorry, I should have looked up the helpfile. I used the @.@.Version to
> realize
> that I was running the developer's edition.
> Venki
> "vvenk" wrote:
How do I find what version of SQL Server is installed?
Hello:
I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
editions.
Can you tell me how I can determine which flavor is installed?
I thought I had Standard Edition but when I tried to install Standard
Edition of the Reporting services, it said that it was a wrong type.
Thanks.
VenkiSorry, I should have looked up the helpfile. I used the @.@.Version to realize
that I was running the developer's edition.
Venki
"vvenk" wrote:
> Hello:
> I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
> editions.
> Can you tell me how I can determine which flavor is installed?
> I thought I had Standard Edition but when I tried to install Standard
> Edition of the Reporting services, it said that it was a wrong type.
> Thanks.
> Venki|||use master
select @.@.version
"vvenk" wrote:
> Hello:
> I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
> editions.
> Can you tell me how I can determine which flavor is installed?
> I thought I had Standard Edition but when I tried to install Standard
> Edition of the Reporting services, it said that it was a wrong type.
> Thanks.
> Venki|||Hi,
@.@.Version will serve you requirement. But have a look into the below link to
get all the versions for each patch level sql server.
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=37
Thanks
Hari
SQL Server MVP
"vvenk" <vvenk@.discussions.microsoft.com> wrote in message
news:049E3C1C-ACF1-4D28-B659-475767027800@.microsoft.com...
> Sorry, I should have looked up the helpfile. I used the @.@.Version to
> realize
> that I was running the developer's edition.
> Venki
> "vvenk" wrote:
>> Hello:
>> I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
>> editions.
>> Can you tell me how I can determine which flavor is installed?
>> I thought I had Standard Edition but when I tried to install Standard
>> Edition of the Reporting services, it said that it was a wrong type.
>> Thanks.
>> Venki
I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
editions.
Can you tell me how I can determine which flavor is installed?
I thought I had Standard Edition but when I tried to install Standard
Edition of the Reporting services, it said that it was a wrong type.
Thanks.
VenkiSorry, I should have looked up the helpfile. I used the @.@.Version to realize
that I was running the developer's edition.
Venki
"vvenk" wrote:
> Hello:
> I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
> editions.
> Can you tell me how I can determine which flavor is installed?
> I thought I had Standard Edition but when I tried to install Standard
> Edition of the Reporting services, it said that it was a wrong type.
> Thanks.
> Venki|||use master
select @.@.version
"vvenk" wrote:
> Hello:
> I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
> editions.
> Can you tell me how I can determine which flavor is installed?
> I thought I had Standard Edition but when I tried to install Standard
> Edition of the Reporting services, it said that it was a wrong type.
> Thanks.
> Venki|||Hi,
@.@.Version will serve you requirement. But have a look into the below link to
get all the versions for each patch level sql server.
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=37
Thanks
Hari
SQL Server MVP
"vvenk" <vvenk@.discussions.microsoft.com> wrote in message
news:049E3C1C-ACF1-4D28-B659-475767027800@.microsoft.com...
> Sorry, I should have looked up the helpfile. I used the @.@.Version to
> realize
> that I was running the developer's edition.
> Venki
> "vvenk" wrote:
>> Hello:
>> I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
>> editions.
>> Can you tell me how I can determine which flavor is installed?
>> I thought I had Standard Edition but when I tried to install Standard
>> Edition of the Reporting services, it said that it was a wrong type.
>> Thanks.
>> Venki
How do I find what version of SQL Server is installed?
Hello:
I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
editions.
Can you tell me how I can determine which flavor is installed?
I thought I had Standard Edition but when I tried to install Standard
Edition of the Reporting services, it said that it was a wrong type.
Thanks.
VenkiSorry, I should have looked up the helpfile. I used the @.@.Version to realize
that I was running the developer's edition.
Venki
"vvenk" wrote:
> Hello:
> I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
> editions.
> Can you tell me how I can determine which flavor is installed?
> I thought I had Standard Edition but when I tried to install Standard
> Edition of the Reporting services, it said that it was a wrong type.
> Thanks.
> Venki|||use master
select @.@.version
"vvenk" wrote:
> Hello:
> I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
> editions.
> Can you tell me how I can determine which flavor is installed?
> I thought I had Standard Edition but when I tried to install Standard
> Edition of the Reporting services, it said that it was a wrong type.
> Thanks.
> Venki|||Hi,
@.@.Version will serve you requirement. But have a look into the below link to
get all the versions for each patch level sql server.
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=37
Thanks
Hari
SQL Server MVP
"vvenk" <vvenk@.discussions.microsoft.com> wrote in message
news:049E3C1C-ACF1-4D28-B659-475767027800@.microsoft.com...[vbcol=seagreen]
> Sorry, I should have looked up the helpfile. I used the @.@.Version to
> realize
> that I was running the developer's edition.
> Venki
> "vvenk" wrote:
>
I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
editions.
Can you tell me how I can determine which flavor is installed?
I thought I had Standard Edition but when I tried to install Standard
Edition of the Reporting services, it said that it was a wrong type.
Thanks.
VenkiSorry, I should have looked up the helpfile. I used the @.@.Version to realize
that I was running the developer's edition.
Venki
"vvenk" wrote:
> Hello:
> I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
> editions.
> Can you tell me how I can determine which flavor is installed?
> I thought I had Standard Edition but when I tried to install Standard
> Edition of the Reporting services, it said that it was a wrong type.
> Thanks.
> Venki|||use master
select @.@.version
"vvenk" wrote:
> Hello:
> I know SQL Server 2000 come in Developer Edition, Standard and Enterprise
> editions.
> Can you tell me how I can determine which flavor is installed?
> I thought I had Standard Edition but when I tried to install Standard
> Edition of the Reporting services, it said that it was a wrong type.
> Thanks.
> Venki|||Hi,
@.@.Version will serve you requirement. But have a look into the below link to
get all the versions for each patch level sql server.
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=37
Thanks
Hari
SQL Server MVP
"vvenk" <vvenk@.discussions.microsoft.com> wrote in message
news:049E3C1C-ACF1-4D28-B659-475767027800@.microsoft.com...[vbcol=seagreen]
> Sorry, I should have looked up the helpfile. I used the @.@.Version to
> realize
> that I was running the developer's edition.
> Venki
> "vvenk" wrote:
>
Wednesday, March 7, 2012
How do I find IDENTITY columns on Table using T-SQL
Is there a query I can write against an INFORMATION_SCHEMA or against the system tables to determine if a column is an identity column?
Found it, a little obscure:
SELECT obj.[name], col.[name], col.[colstat], col.*
FROM [syscolumns] col
JOIN [sysobjects] obj
ON obj.[id] = col.[id]
WHERE obj.type = 'U'
AND col.[status] = 0x80
ORDER BY obj.[name]
Does anyone know a way of doing this using an INFORMATIO_SCHEMA view?
|||I posted that sometime ago:SELECT IsIdentity=COLUMNPROPERTY(id, name, 'IsIdentity')
FROM syscolumns WHERE OBJECT_NAME(id) = sometable_test'
Mit Information_schema views from
http://weblogs.asp.net/psteele/archive/2003/12/03/41051.aspx
select TABLE_NAME + '.' + COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') =
1
order by TABLE_NAME
HTH, Jens Suessmeyer.
http://www.sqlserver2005.,de
|||Here is some more ( in technicolor ;-) )
USE northwind
GO
DECLARE @.tableName VARCHAR(50)
SELECT @.tableName = 'orders'
--Use COLUMNPROPERTY and the syscolumns system table
SELECT COUNT(name) AS HasIdentity
FROM syscolumns
WHERE OBJECT_NAME(id) = @.tableName
AND COLUMNPROPERTY(id, name, 'IsIdentity') = 1
GO
DECLARE @.intObjectID INT
SELECT @.intObjectID =OBJECT_ID('orders')
--Use OBJECTPROPERTY and the TableHasIdentity property name
SELECT COALESCE(OBJECTPROPERTY(@.intObjectID, 'TableHasIdentity'),0) AS HasIdentity
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Sunday, February 19, 2012
how do I determine what tables/views are being accesed
How can i determine what tables/views are being accesed,
when, how and by who. I have attempted using profiler but
with no joy as yet.
Hi,
You can use a 3rd party tool as well. Tool is Entegra by Lumigent
(www.lumigent.com).
Did you tried the Event clause -- TSQL -- Batch starting option in Profiler
? That gives you all the DML commands executed / duration and
user who executed.
Thanks
Hari
MCDBA
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:175ec01c418a5$3164da20$a401280a@.phx.gbl...
> How can i determine what tables/views are being accesed,
> when, how and by who. I have attempted using profiler but
> with no joy as yet.
|||That only gives me the sp executed, i need to know what
tables/views are used.
basicly i want to know when a table/view is accesed as i
have identified several possible redundant tables and i
want to make sure that they never get accessed before i
remove them
>--Original Message--
>Hi,
>You can use a 3rd party tool as well. Tool is Entegra by
Lumigent
>(www.lumigent.com).
>Did you tried the Event clause -- TSQL -- Batch starting
option in Profiler
>? That gives you all the DML commands executed / duration
and
>user who executed.
>
>Thanks
>Hari
>MCDBA
>
>
>"Mat" <anonymous@.discussions.microsoft.com> wrote in
message
>news:175ec01c418a5$3164da20$a401280a@.phx.gbl...
but
>
>.
>
when, how and by who. I have attempted using profiler but
with no joy as yet.
Hi,
You can use a 3rd party tool as well. Tool is Entegra by Lumigent
(www.lumigent.com).
Did you tried the Event clause -- TSQL -- Batch starting option in Profiler
? That gives you all the DML commands executed / duration and
user who executed.
Thanks
Hari
MCDBA
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:175ec01c418a5$3164da20$a401280a@.phx.gbl...
> How can i determine what tables/views are being accesed,
> when, how and by who. I have attempted using profiler but
> with no joy as yet.
|||That only gives me the sp executed, i need to know what
tables/views are used.
basicly i want to know when a table/view is accesed as i
have identified several possible redundant tables and i
want to make sure that they never get accessed before i
remove them
>--Original Message--
>Hi,
>You can use a 3rd party tool as well. Tool is Entegra by
Lumigent
>(www.lumigent.com).
>Did you tried the Event clause -- TSQL -- Batch starting
option in Profiler
>? That gives you all the DML commands executed / duration
and
>user who executed.
>
>Thanks
>Hari
>MCDBA
>
>
>"Mat" <anonymous@.discussions.microsoft.com> wrote in
message
>news:175ec01c418a5$3164da20$a401280a@.phx.gbl...
but
>
>.
>
how do I determine what tables/views are being accesed
How can i determine what tables/views are being accesed,
when, how and by who. I have attempted using profiler but
with no joy as yet.Hi,
You can use a 3rd party tool as well. Tool is Entegra by Lumigent
(www.lumigent.com).
Did you tried the Event clause -- TSQL -- Batch starting option in Profiler
? That gives you all the DML commands executed / duration and
user who executed.
Thanks
Hari
MCDBA
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:175ec01c418a5$3164da20$a401280a@.phx
.gbl...
> How can i determine what tables/views are being accesed,
> when, how and by who. I have attempted using profiler but
> with no joy as yet.|||That only gives me the sp executed, i need to know what
tables/views are used.
basicly i want to know when a table/view is accesed as i
have identified several possible redundant tables and i
want to make sure that they never get accessed before i
remove them
>--Original Message--
>Hi,
>You can use a 3rd party tool as well. Tool is Entegra by
Lumigent
>(www.lumigent.com).
>Did you tried the Event clause -- TSQL -- Batch starting
option in Profiler
>? That gives you all the DML commands executed / duration
and
>user who executed.
>
>Thanks
>Hari
>MCDBA
>
>
>"Mat" <anonymous@.discussions.microsoft.com> wrote in
message
> news:175ec01c418a5$3164da20$a401280a@.phx
.gbl...
but
>
>.
>
when, how and by who. I have attempted using profiler but
with no joy as yet.Hi,
You can use a 3rd party tool as well. Tool is Entegra by Lumigent
(www.lumigent.com).
Did you tried the Event clause -- TSQL -- Batch starting option in Profiler
? That gives you all the DML commands executed / duration and
user who executed.
Thanks
Hari
MCDBA
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:175ec01c418a5$3164da20$a401280a@.phx
.gbl...
> How can i determine what tables/views are being accesed,
> when, how and by who. I have attempted using profiler but
> with no joy as yet.|||That only gives me the sp executed, i need to know what
tables/views are used.
basicly i want to know when a table/view is accesed as i
have identified several possible redundant tables and i
want to make sure that they never get accessed before i
remove them
>--Original Message--
>Hi,
>You can use a 3rd party tool as well. Tool is Entegra by
Lumigent
>(www.lumigent.com).
>Did you tried the Event clause -- TSQL -- Batch starting
option in Profiler
>? That gives you all the DML commands executed / duration
and
>user who executed.
>
>Thanks
>Hari
>MCDBA
>
>
>"Mat" <anonymous@.discussions.microsoft.com> wrote in
message
> news:175ec01c418a5$3164da20$a401280a@.phx
.gbl...
but
>
>.
>
How do I determine the table from index name
I ran a query to identify indexes with fragmentation problems, and it gives
me the index names. I cannot tell from the names what tables or views are
being indexed. I have been wondering through the system views, but so far
nothing jumps out at me (i.e. sys.table_indexes).
I don't think that this is something that has to be solved, since I rebuild
indexes once a week, but I would like to know: Starting wtih
sys.indexes.object_id, how can I determine the table or view name?
What query/mechanism are you using to generate the list of fragmented
indexes?
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
|||> Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
One method:
SELECT
name AS index_name,
OBJECT_NAME(object_id) AS object_name
FROM sys.indexes
Hope this helps.
Dan Guzman
SQL Server MVP
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
|||First, you might be re-inventing the wheel. You will find code in Books Online which does
defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and for
2005, sys.dm_db_index_physical_stats.
You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes a
database id as second parameter (very useful).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
|||My question began with a defragmentation query that I found in an Sql 2005
textbook, which produced a list of six suspects with OBJECT_NAME
(dt.object_id) = queue_messages_1003150619 or something similar. Since that
certainly didn't match any table or view in the database, I assumed it was
the name of an index. But the response from Dan Guzman includes a query that
shows that it is actually the name of the table or view - which I just said
doesn't exist. So now I'm really confused.
"Tibor Karaszi" wrote:
> First, you might be re-inventing the wheel. You will find code in Books Online which does
> defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and for
> 2005, sys.dm_db_index_physical_stats.
> You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes a
> database id as second parameter (very useful).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>
|||Probably a service broker queue. Perhaps you are using SB explicitly or for the internal usage of
SQL Server. One could argue that these should be hidden from us, I guess...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...[vbcol=seagreen]
> My question began with a defragmentation query that I found in an Sql 2005
> textbook, which produced a list of six suspects with OBJECT_NAME
> (dt.object_id) = queue_messages_1003150619 or something similar. Since that
> certainly didn't match any table or view in the database, I assumed it was
> the name of an index. But the response from Dan Guzman includes a query that
> shows that it is actually the name of the table or view - which I just said
> doesn't exist. So now I'm really confused.
> "Tibor Karaszi" wrote:
|||> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
I agree it's probably a queue, especially with that object name.
One could also argue not to hide these objects because objects other than
tables and views might be interesting too. If interested only in views and
tables, Bev can join to sys.objects and specify WHERE type IN('U', 'V').
Similarly, a join to object type-specific tables (sys.tables, sys.views) can
provide similar results.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:C180E8C0-6A2B-4881-A850-6872EC4A8F04@.microsoft.com...
> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...
>
me the index names. I cannot tell from the names what tables or views are
being indexed. I have been wondering through the system views, but so far
nothing jumps out at me (i.e. sys.table_indexes).
I don't think that this is something that has to be solved, since I rebuild
indexes once a week, but I would like to know: Starting wtih
sys.indexes.object_id, how can I determine the table or view name?
What query/mechanism are you using to generate the list of fragmented
indexes?
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
|||> Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
One method:
SELECT
name AS index_name,
OBJECT_NAME(object_id) AS object_name
FROM sys.indexes
Hope this helps.
Dan Guzman
SQL Server MVP
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
|||First, you might be re-inventing the wheel. You will find code in Books Online which does
defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and for
2005, sys.dm_db_index_physical_stats.
You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes a
database id as second parameter (very useful).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
|||My question began with a defragmentation query that I found in an Sql 2005
textbook, which produced a list of six suspects with OBJECT_NAME
(dt.object_id) = queue_messages_1003150619 or something similar. Since that
certainly didn't match any table or view in the database, I assumed it was
the name of an index. But the response from Dan Guzman includes a query that
shows that it is actually the name of the table or view - which I just said
doesn't exist. So now I'm really confused.
"Tibor Karaszi" wrote:
> First, you might be re-inventing the wheel. You will find code in Books Online which does
> defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and for
> 2005, sys.dm_db_index_physical_stats.
> You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes a
> database id as second parameter (very useful).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>
|||Probably a service broker queue. Perhaps you are using SB explicitly or for the internal usage of
SQL Server. One could argue that these should be hidden from us, I guess...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...[vbcol=seagreen]
> My question began with a defragmentation query that I found in an Sql 2005
> textbook, which produced a list of six suspects with OBJECT_NAME
> (dt.object_id) = queue_messages_1003150619 or something similar. Since that
> certainly didn't match any table or view in the database, I assumed it was
> the name of an index. But the response from Dan Guzman includes a query that
> shows that it is actually the name of the table or view - which I just said
> doesn't exist. So now I'm really confused.
> "Tibor Karaszi" wrote:
|||> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
I agree it's probably a queue, especially with that object name.
One could also argue not to hide these objects because objects other than
tables and views might be interesting too. If interested only in views and
tables, Bev can join to sys.objects and specify WHERE type IN('U', 'V').
Similarly, a join to object type-specific tables (sys.tables, sys.views) can
provide similar results.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:C180E8C0-6A2B-4881-A850-6872EC4A8F04@.microsoft.com...
> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...
>
How do I determine the table from index name
I ran a query to identify indexes with fragmentation problems, and it gives
me the index names. I cannot tell from the names what tables or views are
being indexed. I have been wondering through the system views, but so far
nothing jumps out at me (i.e. sys.table_indexes).
I don't think that this is something that has to be solved, since I rebuild
indexes once a week, but I would like to know: Starting wtih
sys.indexes.object_id, how can I determine the table or view name?What query/mechanism are you using to generate the list of fragmented
indexes?
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||> Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
One method:
SELECT
name AS index_name,
OBJECT_NAME(object_id) AS object_name
FROM sys.indexes
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||First, you might be re-inventing the wheel. You will find code in Books Online which does
defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and for
2005, sys.dm_db_index_physical_stats.
You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes a
database id as second parameter (very useful).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||My question began with a defragmentation query that I found in an Sql 2005
textbook, which produced a list of six suspects with OBJECT_NAME
(dt.object_id) = queue_messages_1003150619 or something similar. Since that
certainly didn't match any table or view in the database, I assumed it was
the name of an index. But the response from Dan Guzman includes a query that
shows that it is actually the name of the table or view - which I just said
doesn't exist. So now I'm really confused.
"Tibor Karaszi" wrote:
> First, you might be re-inventing the wheel. You will find code in Books Online which does
> defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and for
> 2005, sys.dm_db_index_physical_stats.
> You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes a
> database id as second parameter (very useful).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
> >I ran a query to identify indexes with fragmentation problems, and it gives
> > me the index names. I cannot tell from the names what tables or views are
> > being indexed. I have been wondering through the system views, but so far
> > nothing jumps out at me (i.e. sys.table_indexes).
> > I don't think that this is something that has to be solved, since I rebuild
> > indexes once a week, but I would like to know: Starting wtih
> > sys.indexes.object_id, how can I determine the table or view name?
>|||Probably a service broker queue. Perhaps you are using SB explicitly or for the internal usage of
SQL Server. One could argue that these should be hidden from us, I guess...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...
> My question began with a defragmentation query that I found in an Sql 2005
> textbook, which produced a list of six suspects with OBJECT_NAME
> (dt.object_id) = queue_messages_1003150619 or something similar. Since that
> certainly didn't match any table or view in the database, I assumed it was
> the name of an index. But the response from Dan Guzman includes a query that
> shows that it is actually the name of the table or view - which I just said
> doesn't exist. So now I'm really confused.
> "Tibor Karaszi" wrote:
>> First, you might be re-inventing the wheel. You will find code in Books Online which does
>> defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and
>> for
>> 2005, sys.dm_db_index_physical_stats.
>> You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes
>> a
>> database id as second parameter (very useful).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
>> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>> >I ran a query to identify indexes with fragmentation problems, and it gives
>> > me the index names. I cannot tell from the names what tables or views are
>> > being indexed. I have been wondering through the system views, but so far
>> > nothing jumps out at me (i.e. sys.table_indexes).
>> > I don't think that this is something that has to be solved, since I rebuild
>> > indexes once a week, but I would like to know: Starting wtih
>> > sys.indexes.object_id, how can I determine the table or view name?|||> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
I agree it's probably a queue, especially with that object name.
One could also argue not to hide these objects because objects other than
tables and views might be interesting too. If interested only in views and
tables, Bev can join to sys.objects and specify WHERE type IN('U', 'V').
Similarly, a join to object type-specific tables (sys.tables, sys.views) can
provide similar results.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:C180E8C0-6A2B-4881-A850-6872EC4A8F04@.microsoft.com...
> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...
>> My question began with a defragmentation query that I found in an Sql
>> 2005
>> textbook, which produced a list of six suspects with OBJECT_NAME
>> (dt.object_id) = queue_messages_1003150619 or something similar. Since
>> that
>> certainly didn't match any table or view in the database, I assumed it
>> was
>> the name of an index. But the response from Dan Guzman includes a query
>> that
>> shows that it is actually the name of the table or view - which I just
>> said
>> doesn't exist. So now I'm really confused.
>> "Tibor Karaszi" wrote:
>> First, you might be re-inventing the wheel. You will find code in Books
>> Online which does
>> defragmentation based on fragmentation level. For 2000, look in the DBCC
>> SHOWCONTIG topic, and for
>> 2005, sys.dm_db_index_physical_stats.
>> You can use the OBJECT_NAME function to resolve id to name. As of 2005
>> with sp2, this even takes a
>> database id as second parameter (very useful).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
>> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>> >I ran a query to identify indexes with fragmentation problems, and it
>> >gives
>> > me the index names. I cannot tell from the names what tables or views
>> > are
>> > being indexed. I have been wondering through the system views, but so
>> > far
>> > nothing jumps out at me (i.e. sys.table_indexes).
>> > I don't think that this is something that has to be solved, since I
>> > rebuild
>> > indexes once a week, but I would like to know: Starting wtih
>> > sys.indexes.object_id, how can I determine the table or view name?
>
me the index names. I cannot tell from the names what tables or views are
being indexed. I have been wondering through the system views, but so far
nothing jumps out at me (i.e. sys.table_indexes).
I don't think that this is something that has to be solved, since I rebuild
indexes once a week, but I would like to know: Starting wtih
sys.indexes.object_id, how can I determine the table or view name?What query/mechanism are you using to generate the list of fragmented
indexes?
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||> Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
One method:
SELECT
name AS index_name,
OBJECT_NAME(object_id) AS object_name
FROM sys.indexes
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||First, you might be re-inventing the wheel. You will find code in Books Online which does
defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and for
2005, sys.dm_db_index_physical_stats.
You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes a
database id as second parameter (very useful).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||My question began with a defragmentation query that I found in an Sql 2005
textbook, which produced a list of six suspects with OBJECT_NAME
(dt.object_id) = queue_messages_1003150619 or something similar. Since that
certainly didn't match any table or view in the database, I assumed it was
the name of an index. But the response from Dan Guzman includes a query that
shows that it is actually the name of the table or view - which I just said
doesn't exist. So now I'm really confused.
"Tibor Karaszi" wrote:
> First, you might be re-inventing the wheel. You will find code in Books Online which does
> defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and for
> 2005, sys.dm_db_index_physical_stats.
> You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes a
> database id as second parameter (very useful).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
> >I ran a query to identify indexes with fragmentation problems, and it gives
> > me the index names. I cannot tell from the names what tables or views are
> > being indexed. I have been wondering through the system views, but so far
> > nothing jumps out at me (i.e. sys.table_indexes).
> > I don't think that this is something that has to be solved, since I rebuild
> > indexes once a week, but I would like to know: Starting wtih
> > sys.indexes.object_id, how can I determine the table or view name?
>|||Probably a service broker queue. Perhaps you are using SB explicitly or for the internal usage of
SQL Server. One could argue that these should be hidden from us, I guess...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...
> My question began with a defragmentation query that I found in an Sql 2005
> textbook, which produced a list of six suspects with OBJECT_NAME
> (dt.object_id) = queue_messages_1003150619 or something similar. Since that
> certainly didn't match any table or view in the database, I assumed it was
> the name of an index. But the response from Dan Guzman includes a query that
> shows that it is actually the name of the table or view - which I just said
> doesn't exist. So now I'm really confused.
> "Tibor Karaszi" wrote:
>> First, you might be re-inventing the wheel. You will find code in Books Online which does
>> defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and
>> for
>> 2005, sys.dm_db_index_physical_stats.
>> You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes
>> a
>> database id as second parameter (very useful).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
>> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>> >I ran a query to identify indexes with fragmentation problems, and it gives
>> > me the index names. I cannot tell from the names what tables or views are
>> > being indexed. I have been wondering through the system views, but so far
>> > nothing jumps out at me (i.e. sys.table_indexes).
>> > I don't think that this is something that has to be solved, since I rebuild
>> > indexes once a week, but I would like to know: Starting wtih
>> > sys.indexes.object_id, how can I determine the table or view name?|||> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
I agree it's probably a queue, especially with that object name.
One could also argue not to hide these objects because objects other than
tables and views might be interesting too. If interested only in views and
tables, Bev can join to sys.objects and specify WHERE type IN('U', 'V').
Similarly, a join to object type-specific tables (sys.tables, sys.views) can
provide similar results.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:C180E8C0-6A2B-4881-A850-6872EC4A8F04@.microsoft.com...
> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...
>> My question began with a defragmentation query that I found in an Sql
>> 2005
>> textbook, which produced a list of six suspects with OBJECT_NAME
>> (dt.object_id) = queue_messages_1003150619 or something similar. Since
>> that
>> certainly didn't match any table or view in the database, I assumed it
>> was
>> the name of an index. But the response from Dan Guzman includes a query
>> that
>> shows that it is actually the name of the table or view - which I just
>> said
>> doesn't exist. So now I'm really confused.
>> "Tibor Karaszi" wrote:
>> First, you might be re-inventing the wheel. You will find code in Books
>> Online which does
>> defragmentation based on fragmentation level. For 2000, look in the DBCC
>> SHOWCONTIG topic, and for
>> 2005, sys.dm_db_index_physical_stats.
>> You can use the OBJECT_NAME function to resolve id to name. As of 2005
>> with sp2, this even takes a
>> database id as second parameter (very useful).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
>> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>> >I ran a query to identify indexes with fragmentation problems, and it
>> >gives
>> > me the index names. I cannot tell from the names what tables or views
>> > are
>> > being indexed. I have been wondering through the system views, but so
>> > far
>> > nothing jumps out at me (i.e. sys.table_indexes).
>> > I don't think that this is something that has to be solved, since I
>> > rebuild
>> > indexes once a week, but I would like to know: Starting wtih
>> > sys.indexes.object_id, how can I determine the table or view name?
>
How do I determine the table from index name
I ran a query to identify indexes with fragmentation problems, and it gives
me the index names. I cannot tell from the names what tables or views are
being indexed. I have been wondering through the system views, but so far
nothing jumps out at me (i.e. sys.table_indexes).
I don't think that this is something that has to be solved, since I rebuild
indexes once a week, but I would like to know: Starting wtih
sys.indexes.object_id, how can I determine the table or view name?What query/mechanism are you using to generate the list of fragmented
indexes?
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||> Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
One method:
SELECT
name AS index_name,
OBJECT_NAME(object_id) AS object_name
FROM sys.indexes
Hope this helps.
Dan Guzman
SQL Server MVP
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||First, you might be re-inventing the wheel. You will find code in Books Onli
ne which does
defragmentation based on fragmentation level. For 2000, look in the DBCC SHO
WCONTIG topic, and for
2005, sys.dm_db_index_physical_stats.
You can use the OBJECT_NAME function to resolve id to name. As of 2005 with
sp2, this even takes a
database id as second parameter (very useful).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I rebuil
d
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||My question began with a defragmentation query that I found in an Sql 2005
textbook, which produced a list of six suspects with OBJECT_NAME
(dt.object_id) = queue_messages_1003150619 or something similar. Since that
certainly didn't match any table or view in the database, I assumed it was
the name of an index. But the response from Dan Guzman includes a query tha
t
shows that it is actually the name of the table or view - which I just said
doesn't exist. So now I'm really confused.
"Tibor Karaszi" wrote:
> First, you might be re-inventing the wheel. You will find code in Books On
line which does
> defragmentation based on fragmentation level. For 2000, look in the DBCC S
HOWCONTIG topic, and for
> 2005, sys.dm_db_index_physical_stats.
> You can use the OBJECT_NAME function to resolve id to name. As of 2005 wit
h sp2, this even takes a
> database id as second parameter (very useful).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>|||Probably a service broker queue. Perhaps you are using SB explicitly or for
the internal usage of
SQL Server. One could argue that these should be hidden from us, I guess...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...[vbcol=seagreen]
> My question began with a defragmentation query that I found in an Sql 2005
> textbook, which produced a list of six suspects with OBJECT_NAME
> (dt.object_id) = queue_messages_1003150619 or something similar. Since th
at
> certainly didn't match any table or view in the database, I assumed it was
> the name of an index. But the response from Dan Guzman includes a query t
hat
> shows that it is actually the name of the table or view - which I just sai
d
> doesn't exist. So now I'm really confused.
> "Tibor Karaszi" wrote:
>|||> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
I agree it's probably a queue, especially with that object name.
One could also argue not to hide these objects because objects other than
tables and views might be interesting too. If interested only in views and
tables, Bev can join to sys.objects and specify WHERE type IN('U', 'V').
Similarly, a join to object type-specific tables (sys.tables, sys.views) can
provide similar results.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:C180E8C0-6A2B-4881-A850-6872EC4A8F04@.microsoft.com...
> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...
>
me the index names. I cannot tell from the names what tables or views are
being indexed. I have been wondering through the system views, but so far
nothing jumps out at me (i.e. sys.table_indexes).
I don't think that this is something that has to be solved, since I rebuild
indexes once a week, but I would like to know: Starting wtih
sys.indexes.object_id, how can I determine the table or view name?What query/mechanism are you using to generate the list of fragmented
indexes?
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||> Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
One method:
SELECT
name AS index_name,
OBJECT_NAME(object_id) AS object_name
FROM sys.indexes
Hope this helps.
Dan Guzman
SQL Server MVP
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||First, you might be re-inventing the wheel. You will find code in Books Onli
ne which does
defragmentation based on fragmentation level. For 2000, look in the DBCC SHO
WCONTIG topic, and for
2005, sys.dm_db_index_physical_stats.
You can use the OBJECT_NAME function to resolve id to name. As of 2005 with
sp2, this even takes a
database id as second parameter (very useful).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I rebuil
d
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||My question began with a defragmentation query that I found in an Sql 2005
textbook, which produced a list of six suspects with OBJECT_NAME
(dt.object_id) = queue_messages_1003150619 or something similar. Since that
certainly didn't match any table or view in the database, I assumed it was
the name of an index. But the response from Dan Guzman includes a query tha
t
shows that it is actually the name of the table or view - which I just said
doesn't exist. So now I'm really confused.
"Tibor Karaszi" wrote:
> First, you might be re-inventing the wheel. You will find code in Books On
line which does
> defragmentation based on fragmentation level. For 2000, look in the DBCC S
HOWCONTIG topic, and for
> 2005, sys.dm_db_index_physical_stats.
> You can use the OBJECT_NAME function to resolve id to name. As of 2005 wit
h sp2, this even takes a
> database id as second parameter (very useful).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>|||Probably a service broker queue. Perhaps you are using SB explicitly or for
the internal usage of
SQL Server. One could argue that these should be hidden from us, I guess...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...[vbcol=seagreen]
> My question began with a defragmentation query that I found in an Sql 2005
> textbook, which produced a list of six suspects with OBJECT_NAME
> (dt.object_id) = queue_messages_1003150619 or something similar. Since th
at
> certainly didn't match any table or view in the database, I assumed it was
> the name of an index. But the response from Dan Guzman includes a query t
hat
> shows that it is actually the name of the table or view - which I just sai
d
> doesn't exist. So now I'm really confused.
> "Tibor Karaszi" wrote:
>|||> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
I agree it's probably a queue, especially with that object name.
One could also argue not to hide these objects because objects other than
tables and views might be interesting too. If interested only in views and
tables, Bev can join to sys.objects and specify WHERE type IN('U', 'V').
Similarly, a join to object type-specific tables (sys.tables, sys.views) can
provide similar results.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:C180E8C0-6A2B-4881-A850-6872EC4A8F04@.microsoft.com...
> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...
>
How do I determine the IP number and TCP Port of a SQL instance
I'm trying to determine the specific IP address and the TCP port on which a SQL server is communicating.
As far as I can tell, I can't use SMO for this, since I need a method that is compatible with both SQL 2005 AND SQL 2000. Is there a work around or can I use DMO?
Since my target is to be able to use this code against multipel servers, it would be nice to find the method with the least overhead possible. I would also like to avoid being forced to read registries remotely.
--
Jakob BindsletLook at the ServerIPAddress and IPAddressProperty values within the Microsoft.SqlServer.Management.Smo.Wmi namespace.|||
http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/19/Default.aspx
HTH, jens K. Suessmeyer.
http://www.sqlserver2005.de|||According to BOL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9/html/6527388b-26b5-4ebb-8a52-0cf4415f9134.htm) the Microsoft.SqlServer.management.Smi.Wmi namespace isn't supported on SQL Server 2000 and below.|||
I don't see why the SQL Server version has any bearing on what information WMI will provide. I'm trying to work out the details but have run into a bit of confusion regarding the IP Address, but I'm getting the Port information back. I've blogged about it at http://sqljunkies.com/WebLog/marathonsqlguy/archive/2006/11/30/25785.aspx if you're interested.
|||Hi Jagoop,
Did you find a way to retrieve this information for both SQL 2000 as well as SQL 2005 instances?
Thanks,
|||AS WMI is not supported you will have to use the registry directly:
using Microsoft.Win32;
namespace SMOProject
{
class Program
{
static void Main(string[] args)
{
string SQLServerKey = @."SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib";
RegistryKey rk = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, "JensOne");
RegistryKey protocols = rk.OpenSubKey(SQLServerKey);
Console.WriteLine(SQLServerKey);
foreach(string s in protocols.GetSubKeyNames())
{
RegistryKey rkprotocol = protocols.OpenSubKey(s);
Console.WriteLine("\tProtocol: {0}",rkprotocol.Name );
foreach (string ValueName in rkprotocol.GetValueNames())
{
Console.WriteLine("\t\t[Type]:Value: [{0}]:{1}",ValueName, rkprotocol.GetValue(ValueName));
}
}
HTH, Jens K. Suessmeyer.
http.//www.sqlserver2005.de
|||I'd prefer to use DMO, instead of using the registry for this. It will also take care of the instance names for you.
See http://msdn2.microsoft.com/en-us/library/ms134942.aspx (Registry2).
Note you need to have the updated DMO version installed that ships with SQL Server 2005 and is also available as a redist as well (see the FAQ of this group) -- that is, if you plan to use it with SQL Server 2000 and 2005.
I need to lookup, but if I recall correctly it will work for SQL Server 2005 as well, as we aimed at making the port of SQL-DMO clients from 2000 to 2005 totally transparent.
|||
Thanks Jens,
If you have multiple ip addresses listening on different ports, the above registry location doesn't show that information. I tried searching for these values in the registry but couldn't find it anywhere. I have only seen this informaiton ouput to the SQL Server logs during the startup process. Is it possible to retrieve this information?
PK
|||This is in:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp
(For the first instance)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
How do I determine the IP number and TCP Port of a SQL instance
I'm trying to determine the specific IP address and the TCP port on which a SQL server is communicating.
As far as I can tell, I can't use SMO for this, since I need a method that is compatible with both SQL 2005 AND SQL 2000. Is there a work around or can I use DMO?
Since my target is to be able to use this code against multipel servers, it would be nice to find the method with the least overhead possible. I would also like to avoid being forced to read registries remotely.
--
Jakob BindsletLook at the ServerIPAddress and IPAddressProperty values within the Microsoft.SqlServer.Management.Smo.Wmi namespace.|||
http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/19/Default.aspx
HTH, jens K. Suessmeyer.
http://www.sqlserver2005.de|||According to BOL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9/html/6527388b-26b5-4ebb-8a52-0cf4415f9134.htm) the Microsoft.SqlServer.management.Smi.Wmi namespace isn't supported on SQL Server 2000 and below.|||
I don't see why the SQL Server version has any bearing on what information WMI will provide. I'm trying to work out the details but have run into a bit of confusion regarding the IP Address, but I'm getting the Port information back. I've blogged about it at http://sqljunkies.com/WebLog/marathonsqlguy/archive/2006/11/30/25785.aspx if you're interested.
|||Hi Jagoop,
Did you find a way to retrieve this information for both SQL 2000 as well as SQL 2005 instances?
Thanks,
|||AS WMI is not supported you will have to use the registry directly:
using Microsoft.Win32;
namespace SMOProject
{
class Program
{
static void Main(string[] args)
{
string SQLServerKey = @."SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib";
RegistryKey rk = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, "JensOne");
RegistryKey protocols = rk.OpenSubKey(SQLServerKey);
Console.WriteLine(SQLServerKey);
foreach(string s in protocols.GetSubKeyNames())
{
RegistryKey rkprotocol = protocols.OpenSubKey(s);
Console.WriteLine("\tProtocol: {0}",rkprotocol.Name );
foreach (string ValueName in rkprotocol.GetValueNames())
{
Console.WriteLine("\t\t[Type]:Value: [{0}]:{1}",ValueName, rkprotocol.GetValue(ValueName));
}
}
HTH, Jens K. Suessmeyer.
http.//www.sqlserver2005.de
|||I'd prefer to use DMO, instead of using the registry for this. It will also take care of the instance names for you.
See http://msdn2.microsoft.com/en-us/library/ms134942.aspx (Registry2).
Note you need to have the updated DMO version installed that ships with SQL Server 2005 and is also available as a redist as well (see the FAQ of this group) -- that is, if you plan to use it with SQL Server 2000 and 2005.
I need to lookup, but if I recall correctly it will work for SQL Server 2005 as well, as we aimed at making the port of SQL-DMO clients from 2000 to 2005 totally transparent.
|||
Thanks Jens,
If you have multiple ip addresses listening on different ports, the above registry location doesn't show that information. I tried searching for these values in the registry but couldn't find it anywhere. I have only seen this informaiton ouput to the SQL Server logs during the startup process. Is it possible to retrieve this information?
PK
|||This is in:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp
(For the first instance)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
How do I determine the current retention period for merge publicat
How do I determine the current retention period for merge publication ?
Nevermind I have found it.
"Russell" wrote:
> How do I determine the current retention period for merge publication ?
Nevermind I have found it.
"Russell" wrote:
> How do I determine the current retention period for merge publication ?
How do I determine the backup date of a restored Database..?
Hi All,
If the backups were taken on the same server, you may be able to dig
this out from the tables in msdb. You would have to start with
restorehistory and go backwards from there. I'm offering a query,
because I have worked very little with these tables myself. But they
are documented in Books Online, althoughly fairly briefly.
I have to a few backups restored from a set of backup files. The
backup files have been removed from the drive because of some storage
constrains...
For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...
Any help will be much appreciated.. Thanks in advance.
- AravinQuery Builder (querybuilder@.gmail.com) writes:
Quote:
Originally Posted by
I have to a few backups restored from a set of backup files. The
backup files have been removed from the drive because of some storage
constrains...
>
For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...
>
Any help will be much appreciated.. Thanks in advance.
If the backups were taken on the same server, you may be able to dig
this out from the tables in msdb. You would have to start with
restorehistory and go backwards from there. I'm offering a query,
because I have worked very little with these tables myself. But they
are documented in Books Online, althoughly fairly briefly.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
How do I determine the Backup Date of a restored Database from a backup File
Hi All,
I am in a delima here. I have to a few backups restored from a set of
backup file. The backup files have been removed from the drive because
of some storage constrains...
For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...
Any help will be much appreciated.. Thanks in advance.
- Aravin
Hello,
Take a look into below system tables in MSDB database.
BACKUPSET
BACKUPFILE
Please do not post seperately in different groups.
Thanks
Hari
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1170302235.450584.73840@.a34g2000cwb.googlegro ups.com...
> Hi All,
> I am in a delima here. I have to a few backups restored from a set of
> backup file. The backup files have been removed from the drive because
> of some storage constrains...
> For some data comparison reasons, I need to figure out the actual date
> of these backup files that is used to restore these databases. Is
> there any system table I can query to figure the actual backup times
> of these databases...
> Any help will be much appreciated.. Thanks in advance.
> - Aravin
>
I am in a delima here. I have to a few backups restored from a set of
backup file. The backup files have been removed from the drive because
of some storage constrains...
For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...
Any help will be much appreciated.. Thanks in advance.
- Aravin
Hello,
Take a look into below system tables in MSDB database.
BACKUPSET
BACKUPFILE
Please do not post seperately in different groups.
Thanks
Hari
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1170302235.450584.73840@.a34g2000cwb.googlegro ups.com...
> Hi All,
> I am in a delima here. I have to a few backups restored from a set of
> backup file. The backup files have been removed from the drive because
> of some storage constrains...
> For some data comparison reasons, I need to figure out the actual date
> of these backup files that is used to restore these databases. Is
> there any system table I can query to figure the actual backup times
> of these databases...
> Any help will be much appreciated.. Thanks in advance.
> - Aravin
>
How do I determine the Backup Date of a restored Database from a backup File
Hi All,
I am in a delima here. I have to a few backups restored from a set of
backup file. The backup files have been removed from the drive because
of some storage constrains...
For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...
Any help will be much appreciated.. Thanks in advance.
- AravinHello,
Take a look into below system tables in MSDB database.
BACKUPSET
BACKUPFILE
Please do not post seperately in different groups.
Thanks
Hari
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1170302235.450584.73840@.a34g2000cwb.googlegroups.com...
> Hi All,
> I am in a delima here. I have to a few backups restored from a set of
> backup file. The backup files have been removed from the drive because
> of some storage constrains...
> For some data comparison reasons, I need to figure out the actual date
> of these backup files that is used to restore these databases. Is
> there any system table I can query to figure the actual backup times
> of these databases...
> Any help will be much appreciated.. Thanks in advance.
> - Aravin
>|||... and there are also similar RESTORE tables in the msdb database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OnTgkxbRHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Hello,
>
> Take a look into below system tables in MSDB database.
> BACKUPSET
> BACKUPFILE
>
> Please do not post seperately in different groups.
>
> Thanks
> Hari
> "Query Builder" <querybuilder@.gmail.com> wrote in message
> news:1170302235.450584.73840@.a34g2000cwb.googlegroups.com...
>
I am in a delima here. I have to a few backups restored from a set of
backup file. The backup files have been removed from the drive because
of some storage constrains...
For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...
Any help will be much appreciated.. Thanks in advance.
- AravinHello,
Take a look into below system tables in MSDB database.
BACKUPSET
BACKUPFILE
Please do not post seperately in different groups.
Thanks
Hari
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1170302235.450584.73840@.a34g2000cwb.googlegroups.com...
> Hi All,
> I am in a delima here. I have to a few backups restored from a set of
> backup file. The backup files have been removed from the drive because
> of some storage constrains...
> For some data comparison reasons, I need to figure out the actual date
> of these backup files that is used to restore these databases. Is
> there any system table I can query to figure the actual backup times
> of these databases...
> Any help will be much appreciated.. Thanks in advance.
> - Aravin
>|||... and there are also similar RESTORE tables in the msdb database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OnTgkxbRHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Hello,
>
> Take a look into below system tables in MSDB database.
> BACKUPSET
> BACKUPFILE
>
> Please do not post seperately in different groups.
>
> Thanks
> Hari
> "Query Builder" <querybuilder@.gmail.com> wrote in message
> news:1170302235.450584.73840@.a34g2000cwb.googlegroups.com...
>
How do I determine the Backup Date of a restored Database from a backup File
Hi All,
I am in a delima here. I have to a few backups restored from a set of
backup file. The backup files have been removed from the drive because
of some storage constrains...
For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...
Any help will be much appreciated.. Thanks in advance.
- AravinHello,
Take a look into below system tables in MSDB database.
BACKUPSET
BACKUPFILE
Please do not post seperately in different groups.
Thanks
Hari
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1170302235.450584.73840@.a34g2000cwb.googlegroups.com...
> Hi All,
> I am in a delima here. I have to a few backups restored from a set of
> backup file. The backup files have been removed from the drive because
> of some storage constrains...
> For some data comparison reasons, I need to figure out the actual date
> of these backup files that is used to restore these databases. Is
> there any system table I can query to figure the actual backup times
> of these databases...
> Any help will be much appreciated.. Thanks in advance.
> - Aravin
>|||... and there are also similar RESTORE tables in the msdb database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OnTgkxbRHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Hello,
>
> Take a look into below system tables in MSDB database.
> BACKUPSET
> BACKUPFILE
>
> Please do not post seperately in different groups.
>
> Thanks
> Hari
> "Query Builder" <querybuilder@.gmail.com> wrote in message
> news:1170302235.450584.73840@.a34g2000cwb.googlegroups.com...
>> Hi All,
>> I am in a delima here. I have to a few backups restored from a set of
>> backup file. The backup files have been removed from the drive because
>> of some storage constrains...
>> For some data comparison reasons, I need to figure out the actual date
>> of these backup files that is used to restore these databases. Is
>> there any system table I can query to figure the actual backup times
>> of these databases...
>> Any help will be much appreciated.. Thanks in advance.
>> - Aravin
>
I am in a delima here. I have to a few backups restored from a set of
backup file. The backup files have been removed from the drive because
of some storage constrains...
For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...
Any help will be much appreciated.. Thanks in advance.
- AravinHello,
Take a look into below system tables in MSDB database.
BACKUPSET
BACKUPFILE
Please do not post seperately in different groups.
Thanks
Hari
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1170302235.450584.73840@.a34g2000cwb.googlegroups.com...
> Hi All,
> I am in a delima here. I have to a few backups restored from a set of
> backup file. The backup files have been removed from the drive because
> of some storage constrains...
> For some data comparison reasons, I need to figure out the actual date
> of these backup files that is used to restore these databases. Is
> there any system table I can query to figure the actual backup times
> of these databases...
> Any help will be much appreciated.. Thanks in advance.
> - Aravin
>|||... and there are also similar RESTORE tables in the msdb database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OnTgkxbRHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Hello,
>
> Take a look into below system tables in MSDB database.
> BACKUPSET
> BACKUPFILE
>
> Please do not post seperately in different groups.
>
> Thanks
> Hari
> "Query Builder" <querybuilder@.gmail.com> wrote in message
> news:1170302235.450584.73840@.a34g2000cwb.googlegroups.com...
>> Hi All,
>> I am in a delima here. I have to a few backups restored from a set of
>> backup file. The backup files have been removed from the drive because
>> of some storage constrains...
>> For some data comparison reasons, I need to figure out the actual date
>> of these backup files that is used to restore these databases. Is
>> there any system table I can query to figure the actual backup times
>> of these databases...
>> Any help will be much appreciated.. Thanks in advance.
>> - Aravin
>
How do I determine my Reporting Services version - again?
The version numer is 9.00.3186.00. I need to know what that means.
Specifically, is it SP2, which is required for a CRM update?
On searching through the newsgroup bulletins, I see that I am not the only
one who has wanted that kind of information, though unfortunately the
questions stemmed from the 8.0 release. I also did a search through the MSDN
library for ReportingService, and found this article: How to: Detect Version
Information (Reporting Services)
Unfortunately, 9.00.3186.00 doesn't appear in that article's chart. The
closest if 9.0.3042.00, which is service pack 2.
Can anyone help here?Type the following in Management Studio:
select SERVERPROPERTY('PRODUCTLEVEL')
--
Thank you,
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:360A50A8-629B-4D6C-A534-85BC8EBA48C2@.microsoft.com...
> The version numer is 9.00.3186.00. I need to know what that means.
> Specifically, is it SP2, which is required for a CRM update?
> On searching through the newsgroup bulletins, I see that I am not the only
> one who has wanted that kind of information, though unfortunately the
> questions stemmed from the 8.0 release. I also did a search through the
> MSDN
> library for ReportingService, and found this article: How to: Detect
> Version
> Information (Reporting Services)
> Unfortunately, 9.00.3186.00 doesn't appear in that article's chart. The
> closest if 9.0.3042.00, which is service pack 2.
> Can anyone help here?
Specifically, is it SP2, which is required for a CRM update?
On searching through the newsgroup bulletins, I see that I am not the only
one who has wanted that kind of information, though unfortunately the
questions stemmed from the 8.0 release. I also did a search through the MSDN
library for ReportingService, and found this article: How to: Detect Version
Information (Reporting Services)
Unfortunately, 9.00.3186.00 doesn't appear in that article's chart. The
closest if 9.0.3042.00, which is service pack 2.
Can anyone help here?Type the following in Management Studio:
select SERVERPROPERTY('PRODUCTLEVEL')
--
Thank you,
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:360A50A8-629B-4D6C-A534-85BC8EBA48C2@.microsoft.com...
> The version numer is 9.00.3186.00. I need to know what that means.
> Specifically, is it SP2, which is required for a CRM update?
> On searching through the newsgroup bulletins, I see that I am not the only
> one who has wanted that kind of information, though unfortunately the
> questions stemmed from the 8.0 release. I also did a search through the
> MSDN
> library for ReportingService, and found this article: How to: Detect
> Version
> Information (Reporting Services)
> Unfortunately, 9.00.3186.00 doesn't appear in that article's chart. The
> closest if 9.0.3042.00, which is service pack 2.
> Can anyone help here?
How do I determine if SQL Sever Login is disabled using T-Sql?
How do I determine if SQL Sever Login is disabled using T-Sql?
I can disable it and enabled it using the following T-Sql function:
ALTER LOGIN login [ENABLE | DISABLE]
I just want to know if it is enable or disabled?It's stored in the system table sys.server_principals, in the column called
is_disabled
However, in SQL Server 2005 system tables are not visible by default; you
must use the Dedicated Administrator Connection (DAC) which you can read
about in the Books Online.
--
HTH
Kalen Delaney, SQL Server MVP
<rodrigo.gloria@.gmail.com> wrote in message
news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
> How do I determine if SQL Sever Login is disabled using T-Sql?
> I can disable it and enabled it using the following T-Sql function:
> ALTER LOGIN login [ENABLE | DISABLE]
> I just want to know if it is enable or disabled?
>|||Hi,
You can also query the system view and look into column is_disabled.1
denotes the login is disabled
select * from sys.sql_logins
Tahnks
Hari
SQL Server MVP
<rodrigo.gloria@.gmail.com> wrote in message
news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
> How do I determine if SQL Sever Login is disabled using T-Sql?
> I can disable it and enabled it using the following T-Sql function:
> ALTER LOGIN login [ENABLE | DISABLE]
> I just want to know if it is enable or disabled?
>|||Thanks Hari. I totally blew that answer. I was doing some testing, and was
in a connection that was not a sysadmin, just a regular user. So when I
couldn't see server_principals, I assumed it was because it was a system
table.
Then of course logging in using the DAC, I was sysadmin, so I could see that
view.
So sys.server_principals is the answer.
If you check the BOL for sys.sql_logins, you'll see that it is a view based
on sys.server_principals, containing all the columns from that view, plus a
view others.
--
HTH
Kalen Delaney, SQL Server MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OLo8wkxxGHA.2168@.TK2MSFTNGP06.phx.gbl...
> Hi,
> You can also query the system view and look into column is_disabled.1
> denotes the login is disabled
> select * from sys.sql_logins
>
> Tahnks
> Hari
> SQL Server MVP
> <rodrigo.gloria@.gmail.com> wrote in message
> news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
>> How do I determine if SQL Sever Login is disabled using T-Sql?
>> I can disable it and enabled it using the following T-Sql function:
>> ALTER LOGIN login [ENABLE | DISABLE]
>> I just want to know if it is enable or disabled?
>|||Kalen Delaney wrote:
> Thanks Hari. I totally blew that answer. I was doing some testing, and was
> in a connection that was not a sysadmin, just a regular user. So when I
> couldn't see server_principals, I assumed it was because it was a system
> table.
> Then of course logging in using the DAC, I was sysadmin, so I could see that
> view.
> So sys.server_principals is the answer.
> If you check the BOL for sys.sql_logins, you'll see that it is a view based
> on sys.server_principals, containing all the columns from that view, plus a
> view others.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OLo8wkxxGHA.2168@.TK2MSFTNGP06.phx.gbl...
> > Hi,
> >
> > You can also query the system view and look into column is_disabled.1
> > denotes the login is disabled
> >
> > select * from sys.sql_logins
> >
> >
> >
> > Tahnks
> > Hari
> > SQL Server MVP
> >
> > <rodrigo.gloria@.gmail.com> wrote in message
> > news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
> >> How do I determine if SQL Sever Login is disabled using T-Sql?
> >>
> >> I can disable it and enabled it using the following T-Sql function:
> >>
> >> ALTER LOGIN login [ENABLE | DISABLE]
> >>
> >> I just want to know if it is enable or disabled?
> >>
> >
> >
Thank you very much.
I can disable it and enabled it using the following T-Sql function:
ALTER LOGIN login [ENABLE | DISABLE]
I just want to know if it is enable or disabled?It's stored in the system table sys.server_principals, in the column called
is_disabled
However, in SQL Server 2005 system tables are not visible by default; you
must use the Dedicated Administrator Connection (DAC) which you can read
about in the Books Online.
--
HTH
Kalen Delaney, SQL Server MVP
<rodrigo.gloria@.gmail.com> wrote in message
news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
> How do I determine if SQL Sever Login is disabled using T-Sql?
> I can disable it and enabled it using the following T-Sql function:
> ALTER LOGIN login [ENABLE | DISABLE]
> I just want to know if it is enable or disabled?
>|||Hi,
You can also query the system view and look into column is_disabled.1
denotes the login is disabled
select * from sys.sql_logins
Tahnks
Hari
SQL Server MVP
<rodrigo.gloria@.gmail.com> wrote in message
news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
> How do I determine if SQL Sever Login is disabled using T-Sql?
> I can disable it and enabled it using the following T-Sql function:
> ALTER LOGIN login [ENABLE | DISABLE]
> I just want to know if it is enable or disabled?
>|||Thanks Hari. I totally blew that answer. I was doing some testing, and was
in a connection that was not a sysadmin, just a regular user. So when I
couldn't see server_principals, I assumed it was because it was a system
table.
Then of course logging in using the DAC, I was sysadmin, so I could see that
view.
So sys.server_principals is the answer.
If you check the BOL for sys.sql_logins, you'll see that it is a view based
on sys.server_principals, containing all the columns from that view, plus a
view others.
--
HTH
Kalen Delaney, SQL Server MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OLo8wkxxGHA.2168@.TK2MSFTNGP06.phx.gbl...
> Hi,
> You can also query the system view and look into column is_disabled.1
> denotes the login is disabled
> select * from sys.sql_logins
>
> Tahnks
> Hari
> SQL Server MVP
> <rodrigo.gloria@.gmail.com> wrote in message
> news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
>> How do I determine if SQL Sever Login is disabled using T-Sql?
>> I can disable it and enabled it using the following T-Sql function:
>> ALTER LOGIN login [ENABLE | DISABLE]
>> I just want to know if it is enable or disabled?
>|||Kalen Delaney wrote:
> Thanks Hari. I totally blew that answer. I was doing some testing, and was
> in a connection that was not a sysadmin, just a regular user. So when I
> couldn't see server_principals, I assumed it was because it was a system
> table.
> Then of course logging in using the DAC, I was sysadmin, so I could see that
> view.
> So sys.server_principals is the answer.
> If you check the BOL for sys.sql_logins, you'll see that it is a view based
> on sys.server_principals, containing all the columns from that view, plus a
> view others.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OLo8wkxxGHA.2168@.TK2MSFTNGP06.phx.gbl...
> > Hi,
> >
> > You can also query the system view and look into column is_disabled.1
> > denotes the login is disabled
> >
> > select * from sys.sql_logins
> >
> >
> >
> > Tahnks
> > Hari
> > SQL Server MVP
> >
> > <rodrigo.gloria@.gmail.com> wrote in message
> > news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
> >> How do I determine if SQL Sever Login is disabled using T-Sql?
> >>
> >> I can disable it and enabled it using the following T-Sql function:
> >>
> >> ALTER LOGIN login [ENABLE | DISABLE]
> >>
> >> I just want to know if it is enable or disabled?
> >>
> >
> >
Thank you very much.
How do I determine if SQL Sever Login is disabled using T-Sql?
How do I determine if SQL Sever Login is disabled using T-Sql?
I can disable it and enabled it using the following T-Sql function:
ALTER LOGIN login [ENABLE | DISABLE]
I just want to know if it is enable or disabled?It's stored in the system table sys.server_principals, in the column called
is_disabled
However, in SQL Server 2005 system tables are not visible by default; you
must use the Dedicated Administrator Connection (DAC) which you can read
about in the Books Online.
HTH
Kalen Delaney, SQL Server MVP
<rodrigo.gloria@.gmail.com> wrote in message
news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
> How do I determine if SQL Sever Login is disabled using T-Sql?
> I can disable it and enabled it using the following T-Sql function:
> ALTER LOGIN login [ENABLE | DISABLE]
> I just want to know if it is enable or disabled?
>|||Hi,
You can also query the system view and look into column is_disabled.1
denotes the login is disabled
select * from sys.sql_logins
Tahnks
Hari
SQL Server MVP
<rodrigo.gloria@.gmail.com> wrote in message
news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
> How do I determine if SQL Sever Login is disabled using T-Sql?
> I can disable it and enabled it using the following T-Sql function:
> ALTER LOGIN login [ENABLE | DISABLE]
> I just want to know if it is enable or disabled?
>|||Thanks Hari. I totally blew that answer. I was doing some testing, and was
in a connection that was not a sysadmin, just a regular user. So when I
couldn't see server_principals, I assumed it was because it was a system
table.
Then of course logging in using the DAC, I was sysadmin, so I could see that
view.
So sys.server_principals is the answer.
If you check the BOL for sys.sql_logins, you'll see that it is a view based
on sys.server_principals, containing all the columns from that view, plus a
view others.
--
HTH
Kalen Delaney, SQL Server MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OLo8wkxxGHA.2168@.TK2MSFTNGP06.phx.gbl...
> Hi,
> You can also query the system view and look into column is_disabled.1
> denotes the login is disabled
> select * from sys.sql_logins
>
> Tahnks
> Hari
> SQL Server MVP
> <rodrigo.gloria@.gmail.com> wrote in message
> news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
>
I can disable it and enabled it using the following T-Sql function:
ALTER LOGIN login [ENABLE | DISABLE]
I just want to know if it is enable or disabled?It's stored in the system table sys.server_principals, in the column called
is_disabled
However, in SQL Server 2005 system tables are not visible by default; you
must use the Dedicated Administrator Connection (DAC) which you can read
about in the Books Online.
HTH
Kalen Delaney, SQL Server MVP
<rodrigo.gloria@.gmail.com> wrote in message
news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
> How do I determine if SQL Sever Login is disabled using T-Sql?
> I can disable it and enabled it using the following T-Sql function:
> ALTER LOGIN login [ENABLE | DISABLE]
> I just want to know if it is enable or disabled?
>|||Hi,
You can also query the system view and look into column is_disabled.1
denotes the login is disabled
select * from sys.sql_logins
Tahnks
Hari
SQL Server MVP
<rodrigo.gloria@.gmail.com> wrote in message
news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
> How do I determine if SQL Sever Login is disabled using T-Sql?
> I can disable it and enabled it using the following T-Sql function:
> ALTER LOGIN login [ENABLE | DISABLE]
> I just want to know if it is enable or disabled?
>|||Thanks Hari. I totally blew that answer. I was doing some testing, and was
in a connection that was not a sysadmin, just a regular user. So when I
couldn't see server_principals, I assumed it was because it was a system
table.
Then of course logging in using the DAC, I was sysadmin, so I could see that
view.
So sys.server_principals is the answer.
If you check the BOL for sys.sql_logins, you'll see that it is a view based
on sys.server_principals, containing all the columns from that view, plus a
view others.
--
HTH
Kalen Delaney, SQL Server MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OLo8wkxxGHA.2168@.TK2MSFTNGP06.phx.gbl...
> Hi,
> You can also query the system view and look into column is_disabled.1
> denotes the login is disabled
> select * from sys.sql_logins
>
> Tahnks
> Hari
> SQL Server MVP
> <rodrigo.gloria@.gmail.com> wrote in message
> news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
>
Subscribe to:
Posts (Atom)