All:
Short of breaking out the cluster administator tool (GUI), given a
serversnetbios or DNS name, how can I deduce the SQL server names (and
instances) that may be present on that server. Ideally, I could pass
something into a VBSCRIPT function, and get the desired information,
but at this point, even just passing something at the command line that
is parsable would be useful.
Long version of the story is I have a list of 250 servers, but the
physical server name does not always map well to the SQL name,
especailly when instances are involved.
Does this make sense?
thanks,
d.
Look up the SQM-DMO method ListAvailableSQLServers in BOL. You can take
that and filter by InstanceName and ServiceName properties.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<google@.dcbarry.com> wrote in message
news:1141177148.783918.95800@.v46g2000cwv.googlegro ups.com...
> All:
> Short of breaking out the cluster administator tool (GUI), given a
> serversnetbios or DNS name, how can I deduce the SQL server names (and
> instances) that may be present on that server. Ideally, I could pass
> something into a VBSCRIPT function, and get the desired information,
> but at this point, even just passing something at the command line that
> is parsable would be useful.
>
> Long version of the story is I have a list of 250 servers, but the
> physical server name does not always map well to the SQL name,
> especailly when instances are involved.
>
> Does this make sense?
> thanks,
> d.
>
|||You may find SQLPing.exe by Chip Andrews perfect for this. It talks to UDP
1434 or SQL Browser, but you only need to supply a NetBIOS name, a DNS name,
or even an IP address. It'll return all the SQL Server instance names along
with a few other things. This is a very simple program with C# source code
you can compile yourself.
Google SQLPing.exe, and you'll find it.
Linchi
"google@.dcbarry.com" wrote:
> All:
> Short of breaking out the cluster administator tool (GUI), given a
> serversnetbios or DNS name, how can I deduce the SQL server names (and
> instances) that may be present on that server. Ideally, I could pass
> something into a VBSCRIPT function, and get the desired information,
> but at this point, even just passing something at the command line that
> is parsable would be useful.
>
> Long version of the story is I have a list of 250 servers, but the
> physical server name does not always map well to the SQL name,
> especailly when instances are involved.
>
> Does this make sense?
> thanks,
> d.
>
Showing posts with label physical. Show all posts
Showing posts with label physical. Show all posts
Sunday, February 26, 2012
Friday, February 24, 2012
Get physical size of indexes
How do I get the physical size of all individual indexes in a database.
Thanks in advance.
Hello
There is a topic in SQL Books Online titled "Estimating the Size of a Table
with a Clustered Index" which explains the calculation that you can use to
calculate the approximate size of the clustered and non-clustered index for
a table.
If you want to check the actual size of an existing index, refer to the
Books Online topic on "sysindexes" table. The "dpages", "reserved" and
"used" fields indicate the number of 8K pages as explained in the help
topic. Also please note that you may have to perform DBCC UPDATEUSAGE to
get accurate numbers in these fields.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Be aware however that the dpages, etc , and the info received from
sp_spaceused do NOT get updated with each insert, update or delete. They may
be inaccurate. They OFTEN are inaccurate...
Use DBCC Updateusage
or sp_spaceused @.updateusage = true
to force SQL to read the data and update the size information...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ferde" <Ferde@.discussions.microsoft.com> wrote in message
news:3CF862DA-044A-49E8-ABE3-0F61D90D249A@.microsoft.com...
> How do I get the physical size of all individual indexes in a database.
> Thanks in advance.
>
Thanks in advance.
Hello
There is a topic in SQL Books Online titled "Estimating the Size of a Table
with a Clustered Index" which explains the calculation that you can use to
calculate the approximate size of the clustered and non-clustered index for
a table.
If you want to check the actual size of an existing index, refer to the
Books Online topic on "sysindexes" table. The "dpages", "reserved" and
"used" fields indicate the number of 8K pages as explained in the help
topic. Also please note that you may have to perform DBCC UPDATEUSAGE to
get accurate numbers in these fields.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Be aware however that the dpages, etc , and the info received from
sp_spaceused do NOT get updated with each insert, update or delete. They may
be inaccurate. They OFTEN are inaccurate...
Use DBCC Updateusage
or sp_spaceused @.updateusage = true
to force SQL to read the data and update the size information...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ferde" <Ferde@.discussions.microsoft.com> wrote in message
news:3CF862DA-044A-49E8-ABE3-0F61D90D249A@.microsoft.com...
> How do I get the physical size of all individual indexes in a database.
> Thanks in advance.
>
Get physical size of indexes
How do I get the physical size of all individual indexes in a database.
Thanks in advance.Hello
There is a topic in SQL Books Online titled "Estimating the Size of a Table
with a Clustered Index" which explains the calculation that you can use to
calculate the approximate size of the clustered and non-clustered index for
a table.
If you want to check the actual size of an existing index, refer to the
Books Online topic on "sysindexes" table. The "dpages", "reserved" and
"used" fields indicate the number of 8K pages as explained in the help
topic. Also please note that you may have to perform DBCC UPDATEUSAGE to
get accurate numbers in these fields.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Be aware however that the dpages, etc , and the info received from
sp_spaceused do NOT get updated with each insert, update or delete. They may
be inaccurate. They OFTEN are inaccurate...
Use DBCC Updateusage
or sp_spaceused @.updateusage = true
to force SQL to read the data and update the size information...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ferde" <Ferde@.discussions.microsoft.com> wrote in message
news:3CF862DA-044A-49E8-ABE3-0F61D90D249A@.microsoft.com...
> How do I get the physical size of all individual indexes in a database.
> Thanks in advance.
>
Thanks in advance.Hello
There is a topic in SQL Books Online titled "Estimating the Size of a Table
with a Clustered Index" which explains the calculation that you can use to
calculate the approximate size of the clustered and non-clustered index for
a table.
If you want to check the actual size of an existing index, refer to the
Books Online topic on "sysindexes" table. The "dpages", "reserved" and
"used" fields indicate the number of 8K pages as explained in the help
topic. Also please note that you may have to perform DBCC UPDATEUSAGE to
get accurate numbers in these fields.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Be aware however that the dpages, etc , and the info received from
sp_spaceused do NOT get updated with each insert, update or delete. They may
be inaccurate. They OFTEN are inaccurate...
Use DBCC Updateusage
or sp_spaceused @.updateusage = true
to force SQL to read the data and update the size information...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ferde" <Ferde@.discussions.microsoft.com> wrote in message
news:3CF862DA-044A-49E8-ABE3-0F61D90D249A@.microsoft.com...
> How do I get the physical size of all individual indexes in a database.
> Thanks in advance.
>
Get physical size of indexes
How do I get the physical size of all individual indexes in a database.
Thanks in advance.Hello
There is a topic in SQL Books Online titled "Estimating the Size of a Table
with a Clustered Index" which explains the calculation that you can use to
calculate the approximate size of the clustered and non-clustered index for
a table.
If you want to check the actual size of an existing index, refer to the
Books Online topic on "sysindexes" table. The "dpages", "reserved" and
"used" fields indicate the number of 8K pages as explained in the help
topic. Also please note that you may have to perform DBCC UPDATEUSAGE to
get accurate numbers in these fields.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Be aware however that the dpages, etc , and the info received from
sp_spaceused do NOT get updated with each insert, update or delete. They may
be inaccurate. They OFTEN are inaccurate...
Use DBCC Updateusage
or sp_spaceused @.updateusage = true
to force SQL to read the data and update the size information...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ferde" <Ferde@.discussions.microsoft.com> wrote in message
news:3CF862DA-044A-49E8-ABE3-0F61D90D249A@.microsoft.com...
> How do I get the physical size of all individual indexes in a database.
> Thanks in advance.
>
Thanks in advance.Hello
There is a topic in SQL Books Online titled "Estimating the Size of a Table
with a Clustered Index" which explains the calculation that you can use to
calculate the approximate size of the clustered and non-clustered index for
a table.
If you want to check the actual size of an existing index, refer to the
Books Online topic on "sysindexes" table. The "dpages", "reserved" and
"used" fields indicate the number of 8K pages as explained in the help
topic. Also please note that you may have to perform DBCC UPDATEUSAGE to
get accurate numbers in these fields.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Be aware however that the dpages, etc , and the info received from
sp_spaceused do NOT get updated with each insert, update or delete. They may
be inaccurate. They OFTEN are inaccurate...
Use DBCC Updateusage
or sp_spaceused @.updateusage = true
to force SQL to read the data and update the size information...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ferde" <Ferde@.discussions.microsoft.com> wrote in message
news:3CF862DA-044A-49E8-ABE3-0F61D90D249A@.microsoft.com...
> How do I get the physical size of all individual indexes in a database.
> Thanks in advance.
>
Subscribe to:
Posts (Atom)