DBA Query: Instance Information

As part of my weekly routine I like to take a holistic view of the production servers I am responsible for.  After backups and maintenance plans, the next most important role of the DBA is to keep an eye on the overall health of the production databases.

I have several queries that I use to provide me with a summary view of the databases.

Today’s post will be a query that provides a nice view at the instance level.  Of course there are always more points to pull, but this is what I culled the query down to.  You may want to add more, or delete something.

Rather than running this query for every instance, all you need to do is set up your central management servers (providing you have at least one SQL2008 or later server).  This will allow you to run the query and get the result set for each server.  For more info on central management servers see BOL.

So here’s my query for instance reporting…  Enjoy.

Instance Properties
  1. DECLARE    @xp_msver table(    id                    int
  2.                             , name                sysname
  3.                             , internal_value    int
  4.                             , character_value    nvarchar(4000) );
  6. INSERT INTO @xp_msver                    
  7.     EXEC    master.dbo.xp_msver ProcessorCount;
  8. INSERT INTO @xp_msver                    
  9.     EXEC    master.dbo.xp_msver PhysicalMemory;
  11. SELECT    CONVERT(int, CONVERT(char, current_timestamp, 112)) AS CaptureDate
  12.         , SERVERPROPERTY (‘ServerName’) AS [ServerName]
  13.         , SERVERPROPERTY (‘ComputerNamePhysicalNetBIOS’) AS [ComputerNamePhysicalNetBIOS]
  14.         , SERVERPROPERTY (‘MachineName’) AS [MachineName]
  15.         , SERVERPROPERTY (‘InstanceName’) AS [InstanceName]
  16.         , SERVERPROPERTY (‘Edition’) AS [SQL Server Edition]
  17.         , SERVERPROPERTY (‘ProductVersion’) AS [ProductVersion]
  18.         , SERVERPROPERTY (‘ProductLevel’) AS [ProductLevel]
  19.         , SERVERPROPERTY (‘IsClustered’) AS [IsClustered]
  20.         , SERVERPROPERTY (‘IsIntegratedSecurityOnly’) AS [IsIntegratedSecurityOnly]
  21.         , SERVERPROPERTY (‘IsSingleUser’) AS [IsSingleUser]
  22.         , SERVERPROPERTY (‘LicenseType’) AS [LicenseType]
  23.         , SERVERPROPERTY (‘NumLicenses’) AS [NumLicenses]
  24.         , SERVERPROPERTY (‘ProcessID’) AS [ProcessID]
  25.         , CAST(xp1.ProcessorCount AS int) AS ProcessorCount
  26.         , CAST(xp2.PhysicalMemory AS int) AS PhysicalMemory
  27.         , dt.DefaultTraceEnabled
  28.         –, df.DefaultTracePath
  29.         , rde.RemoteDACEnabled
  30.         , (    SELECT    create_date
  31.             FROM    sys.databases
  32.             WHERE    database_id = 2) AS SQLServerStartDate
  33.         , (    SELECT    DATEDIFF(day, create_date, current_timestamp)
  34.             FROM    sys.databases
  35.             WHERE    database_id = 2) As DaysSinceSQLServerStart
  36. FROM    (SELECT    character_value AS ProcessorCount FROM @xp_msver WHERE name = ‘ProcessorCount’) xp1
  37.         CROSS JOIN
  38.          (SELECT    internal_value AS PhysicalMemory FROM @xp_msver WHERE name = ‘PhysicalMemory’) xp2
  39.         CROSS JOIN
  40.          (SELECT value_in_use AS DefaultTraceEnabled FROM sys.configurations WHERE name = ‘default trace enabled’) dt
  41.         CROSS JOIN
  42.         (SELECT value_in_use AS RemoteDACEnabled FROM sys.configurations WHERE name = ‘remote admin connections’) rde
  43.         –CROSS JOIN
  44.         –(SELECT NULL AS DefaultTracePath) df
  45.         –(SELECT VALUE AS DefaultTracePath FROM fn_trace_getinfo(0) WHERE property = 2) df

Stay curious… keep learning.


Leave a Reply

You must be logged in to post a comment.

Using Gravatars in the comments - get your own and be recognized!

XHTML: These are some of the tags you can use: <a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>

custom scholarship essay editing sites gb do my top creative essay on brexit professional cover letter writer services gb professional critical essay editor website us cheap creative writing ghostwriter site usa applebees kitchen roman empire civil war professional papers editing services us best dissertation proposal writers for hire for phd cheap college problem solving sample professional personal statement writers sites online best personal statement ghostwriters websites for phd to kill a mockingbird miss stephanie professional definition essay writer service for college professional article writing service uk cheap university essay writer website for college best university custom essay help custom papers ghostwriters service for masters top term paper writers services best best essay writers for hire for school professional research paper writer site for mba do my top thesis proposal pay to write custom essay on usa pay for my world literature content the ways we lie best homework ghostwriters for hire gb esl mba custom essay example top college essay proofreading for hire au pay for u.s. history and government paper professional admission paper ghostwriting for hire popular presentation proofreading services us professional mba report sample cheap masters essay writer sites for mba pay to get poetry dissertation introduction help with custom persuasive essay on civil war esl best essay ghostwriting service for school john calvin martin luther professional reflective essay editing website gb professional speech writers service au help writing composition dissertation procrastination essay introduction write my finance papers best biography editing websites online help with environmental studies term paper top literature review editor site gb help me write professional scholarship essay on donald trump top best essay ghostwriters websites for college best report editor website for school calculus essay editor service top thesis statement writing for hire online top resume proofreading sites for phd buy classic english literature course work write me mathematics dissertation proposal best assignment ghostwriters service for masters classic english literature essay sample popular university essay editing website us top research paper proofreading for hire for phd popular cheap essay writer service uk type my analysis essay on hillary cheap critical essay writer websites for university professional cv writers websites au pay to get cheap admission essay on hacking popular literature review writer websites for mba popular essays writing services ca help me write analysis essay on founding fathers esl essays editing site for masters pay to do nursing personal statement top argumentative essay editing sites au cheap cheap essay writer websites us pay for my persuasive essay on lincoln top analysis essay editing site online literature review editing service popular university essay writer websites usa cheap persuasive essay writer websites for university help me write art & architecture literature review best cv editing service usa movie review editor sites ca pay to get esl persuasive essay on donald trump cheap admission paper writing services gb custom critical analysis essay ghostwriter service for phd best dissertation proposal editor services au esl essays proofreading for hire au top essay ghostwriting site for phd popular assignment ghostwriters websites gb pay to do business personal statement top case study editor websites for school popular annotated bibliography ghostwriting services uk esl thesis proposal ghostwriter services au esl report ghostwriting for hire au top application letter writers website for masters best critical thinking proofreading site usa write my environmental studies report poetry essay ghostwriters websites popular article writing websites for masters professional assignment writer for hire for university university essay editor website ca esl essays writers services for masters popular speech editing website cheap reflective essay writers service type my world affairs dissertation chapter professional application letter writers for hire popular creative essay writing website online custom admission paper writing website professional dissertation conclusion editor site online esl phd essay ghostwriting service for mba esl papers ghostwriters sites us bibliography editing websites ca cheap literature review editor sites ca top masters essay writing website gb custom literature review editor websites ca esl mba phd essay samples custom annotated bibliography ghostwriter for hire uk professional research paper writing websites online pay to do math dissertation introduction custom mba creative essay topics gay marriage essay best home work writing services for university cheap definition essay writing websites for university write my esl masters essay on trump cheap presentation ghostwriter website for school custom creative writing writers websites for university custom dissertation ghostwriters websites for phd pay for tourism problem solving problem solving proofreading website usa custom custom essay ghostwriter service gb best scholarship essay ghostwriters website professional argumentative essay editor services for university cheap dissertation hypothesis ghostwriting site for masters esl dissertation chapter writers sites gb life without a father esl creative writing editor sites best assignment writer sites for school ethics research paper topics best college essay editing services for masters esl admission paper writer sites for college professional best essay ghostwriting sites gb pay to do health dissertation results application letter ghostwriter for hire usa popular custom essay editing site us best school essay ghostwriter sites popular thesis editor for hire online cheap critical thinking writing services for phd custom paper writers websites online esl assignment ghostwriter for hire au art & architecture essay writing for hire top best essay editing for hire uk popular dissertation hypothesis writers sites for university esl university dissertation abstract topics popular resume ghostwriter site uk theme in the story of an hour esl university essay ghostwriting services for masters popular mba admission paper help popular content ghostwriters websites uk cheap case study ghostwriting services popular research proposal editor for hire gb professional university essay writer services gb custom course work writers websites top creative essay ghostwriting sites for college help with my professional phd essay on civil war professional dissertation proposal writers websites online custom admission essay writing websites usa custom dissertation methodology writer websites online professional dissertation introduction ghostwriter services us esl paper writer services for mba do my education problem solving top persuasive essay ghostwriter services usa cheap biography writing services online top dissertation hypothesis ghostwriters websites uk esl dissertation writing service online esl personal statement editor service for college custom academic essay editor for hire for university top resume ghostwriters site online geology editor websites esl cover letter editor for hire gb write custom admission essay on hillary popular dissertation results writers service for masters case study proofreading website us professional critical essay editing site ca custom essay ghostwriters for hire us popular article proofreading for hire for phd cheap article review proofreading site ca cheap assignment ghostwriting site online popular best essay editor websites custom admission essay ghostwriters services analysis proofreading services gb cheap expository essay ghostwriters for hire for mba order government resume best university essay proofreading site for college custom school phd essay topic professional curriculum vitae writing service for phd write my botany research proposal esl home work editing services for school esl presentation writing service for college my ideal partner would be pay for my algebra dissertation hypothesis top rhetorical analysis essay proofreading services usa pay to do earth science biography esl application letter editing website for university custom cv editing websites for college professional homework proofreading website uk cassius quotes julius caesar mesopotamia afterlife beliefs esl literature review ghostwriters services for masters academic essay on hillary 7 4 gd knowledge cheap research paper proofreading sites for mba custom essays writer for hire for mba university essay writer websites gb geology essay editing for hire esl movie review ghostwriting website esl resume proofreading sites ca top business plan writer websites ca best course work ghostwriting for hire for mba pay to get music resume communication essay proofreading website cheap thesis statement writer for hire popular rhetorical analysis essay writers sites for university all quiet on the western front theme top business plan ghostwriter for hire gb charlotte lucas quotes esl academic essay ghostwriting for hire for school popular term paper ghostwriting websites ca help writing bibliography online buy cheap dissertation hypothesis professional research paper proofreading websites for school professional cv proofreading site au best movie review editor services au do my top custom essay on presidential elections cheap literature review ghostwriters sites online top dissertation methodology ghostwriters sites for masters custom presentation proofreading website for university best college letter examples girl jamaica kincaid essay popular cover letter proofreading for hire for university type my top college essay on trump write popular rhetorical analysis essay on brexit write top definition essay on presidential elections the movie the odyssey popular course work writers services for college cheap critical analysis essay editor for hire us custom research proposal proofreading for hire for mba pay for my sociology personal statement esl business plan proofreading site usa professional rhetorical analysis essay ghostwriter websites us top school essay editing for hire for university top biography editing sites gb professional dissertation introduction writers website gb top problem solving writer sites gb cheap college dissertation results ideas lion king short story professional report ghostwriter service ca esl term paper ghostwriters service best critical analysis essay ghostwriter site for school custom dissertation methodology ghostwriter websites esl biography writers service for university esl phd essay writing sites type my critical essay on shakespeare esl problem solving editor for hire for masters popular article writer for hire for school write tourism article review pay for esl essays online professional ghostwriting website for university best masters cv ideas do my professional college essay on brexit help with technology research paper top dissertation chapter ghostwriting website ca esl annotated bibliography writer sites for mba custom bibliography writing services ca custom dissertation conclusion editor site us definition essay writing site us custom mba case study samples best content writer sites gb conflict of the crucible popular dissertation chapter editing website for mba top blog ghostwriters service for college best admission essay writers service gb do my statistics dissertation hypothesis professional annotated bibliography ghostwriting websites custom dissertation chapter ghostwriters for hire au what is an allegorical novel cheap college essay ghostwriters site for mba coodes rebellion best papers writing for hire online esl cv editor site uk professional creative writing proofreading service for university cheap academic essay editor websites for masters type my statistics admission essay thomas hobbes believed best personal essay editing service ca cheap college movie review sample popular scholarship essay proofreading for hire uk popular papers ghostwriting for hire for mba top blog ghostwriting sites au top dissertation methodology editing services online popular college persuasive essay advice popular biography editing website uk top research proposal ghostwriter websites ca pay for best critical essay on donald trump best college blog post help best curriculum vitae ghostwriter sites us pay to do homework causes of american revolution essay esl school essay writers for hire ca esl college essay editing site gb professional dissertation methodology ghostwriter for hire ca custom assignment ghostwriter services online do my esl expository essay on usa help me write popular movie review top argumentative essay ghostwriter service online custom dissertation methodology ghostwriter sites usa custom dissertation ghostwriters sites au pay to get top critical analysis essay on presidential elections pay for professional expository essay on hacking write my calculus course work type my sociology annotated bibliography blog ghostwriter sites gb professional admission paper writing site online professional research proposal editing site for masters great gatsby roaring 20s persuasive essay writers sites online professional admission essay editor sites online best thesis statement ghostwriting services for school popular reflective essay ghostwriting website usa professional resume editor services au help writing health cv esl admission paper ghostwriters website for school university essay ghostwriter sites cheap custom essay writers for hire cheap admission paper ghostwriters site for university top dissertation chapter editing sites online examples of hubris in the odyssey best cv ghostwriting sites for mba dad and dave on our selection top thesis proposal ghostwriter services for university custom course work editing site for masters appearance vs reality hamlet professional admission paper writing for hire gb popular movie review ghostwriters site au popular phd essay ghostwriting sites ca esl critical analysis essay editing for hire best biography editor site au popular cv writers site for masters write top dissertation proposal online romero film top dissertation writers websites uk write esl literature review online top definition essay editing website for mba custom masters essay ghostwriter for hire online sonnet 97 shakespeare best thesis proposal ghostwriter services professional homework ghostwriter for hire for school best dissertation abstract writers websites us pay to do best custom essay write top dissertation chapter online esl persuasive essay ghostwriter website for mba write my environmental studies homework custom phd papers sample best critical thinking writers service for phd top cv writer service uk creative writer websites online best assignment editor sites au popular research proposal editor services pay to write economics dissertation conclusion write popular custom essay on presidential elections cheap homework ghostwriter for hire for masters best business plan ghostwriters sites uk esl dissertation chapter writer websites for phd custom descriptive essay editing sites for masters popular business plan writer for hire for masters custom descriptive essay writer website for school order professional best essay on donald trump pay to get speech curriculum vitae cheap university essay writer websites catch 22 themes popular creative essay ghostwriters websites gb top college essay ghostwriting sites for school esl university critical essay samples top masters essay editing for hire uk top argumentative essay writer service for masters order professional admission paper academic essay editor website gb what america means to me essay best dissertation hypothesis ghostwriting services uk professional custom essay ghostwriting for hire au best letter ghostwriters site usa best essays ghostwriting for hire online professional speech writing service for college top dissertation introduction writing website for masters esl descriptive essay writer for hire us help writing dissertation abstract online professional annotated bibliography ghostwriting website for mba top university university essay help cheap custom essay writing website for college popular scholarship essay ghostwriters services uk professional thesis proposal editing services ca custom persuasive essay writing websites for school best essay writers website for mba write popular resume online esl report proofreading sites ca professional phd essay writer service for college popular blog post writing sites us popular case study writers sites for school stephen kings the body esl expository essay ghostwriting for hire for masters buy earth science case study professional report writing websites online do my government literature review popular cv writer website gb custom creative essay editing service for school the story of an hour response popular dissertation methodology ghostwriting site us best critical essay editor site for phd best school essay ghostwriter site uk oral history essay top bibliography proofreading website for university custom academic essay ghostwriter site usa top dissertation results editing service au popular college research proposal advice professional annotated bibliography proofreading website uk professional cover letter writer services ca popular phd essay writing service uk esl academic essay editor service usa professional cv editing for hire for mba do my top cheap essay on shakespeare best phd blog post examples esl scholarship essay proofreading websites us problem solving editing for hire uk creative essay editing services us cheap literature review ghostwriters websites au best admission paper writing services for phd custom article ghostwriting site for phd help with my shakespeare studies thesis proposal buy custom admission essay on brexit custom essay writer for hire gb professional dissertation chapter writing services cheap college bibliography custom movie review writing service for college cheap rhetorical analysis essay ghostwriting sites for masters popular paper editing service ca green willow japanese folktale top dissertation results editor services pay to get theater studies research paper how is the crucible an allegory for mccarthyism essay top dissertation introduction writer services uk esl research paper writers sites was the cold war inevitable essay professional cheap essay ghostwriter sites gb pay to get english research proposal do my professional papers online best speech editing website for mba pay for my marketing dissertation popular descriptive essay editor for hire for university professional university descriptive essay sample popular homework editor sites for school top bibliography proofreading sites for masters esl phd expository essay assistance esl critical essay editing sites for masters popular university essay writers service us esl report editing for hire for university popular definition essay editor service au write esl school essay on civil war best admission essay proofreading sites au professional case study editor sites for mba cheap university essay on donald trump esl paper writer sites au type my law bibliography management essay samples cheap resume writer website for phd professional business plan writing services usa custom presentation ghostwriting sites for phd write me professional scholarship essay on donald trump popular problem solving editor site for mba best ghostwriters site uk pay for my custom masters essay on brexit best mba essay ghostwriting for hire us popular creative writing proofreading sites au cheap admission paper ghostwriting services gb popular thesis statement proofreading service for college best blog ghostwriters sites gb top book review proofreading site gb professional creative essay ghostwriters site for college best thesis statement ghostwriter for hire ca do my best definition essay on presidential elections esl term paper editing for hire for mba affirmations ferndale mi popular personal essay ghostwriting website for mba popular reflective essay writers service online cheap school essay editor service for college type my nursing presentation top home work editing website ca professional bibliography proofreading services top cover letter editor site ca best definition essay ghostwriters sites for school write me esl dissertation professional movie review writers websites ca top cover letter editor site popular literature review editor sites gb pay to do geometry letter buy environmental studies paper best school essay writer site for school