9003 is a Scary Number

My last post talked about 9002 errors and how they *might* be corruption.

Recently I moved up to the next consecutive error number, a 9003 error:

-- 

Log Name:      Application
Source:        MSSQLSERVER
Date:          6/15/2017 4:49:59 AM
Event ID:      9003
Task Category: Server
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      Server1.domain.net
Description:
The log scan number (139943:2168:0) passed to log scan in database 'DBStuff' is not valid. 

This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). 

If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

--

<Cue Scary Music>

http://www.scaryforkids.com/pics/scary-music.jpg

--

Like the 9002, this is a seemingly scary error with the word “corruption” in it, but when I checked the server it appeared the database was online and I saw connections to DBStuff once I signed on.

The server was throwing a series of the errors constantly in the SQL Error Log (and Windows Application Log) for more than an hour by the time it was escalated to me – constantly every minute or less!

The DBStuff database was a relatively small database (13GB) so I went to run CHECKDB against it manually from a query window to look for corruption, but I couldn’t even get that far:

--

Msg 5901, Level 16, State 1, Line 1
One or more recovery units belonging to database 'DBStuff' failed to generate a checkpoint.  This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.

Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.

Msg 1823, Level 16, State 8, Line 1
A database snapshot cannot be created because it failed to start.

Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.

Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

Msg 9003, Level 20, State 9, Line 1
The log scan number (139943:2168:0) passed to log scan in database 'DBStuff' is not valid.

This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf).

If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

 --

Note that the last error in the series is again the 9003 error shown in the initial Log entry above.

Could there be corruption?

http://s2.quickmeme.com/img/a6/a646c45633cfab95a2aaf727480883aba7de652148fb3601db61853a62d3b3da.jpg

At that point I went Googling and found this from Nathan Courtine at dbi Services:

https://blog.dbi-services.com/sql-server-database-failed-to-generate-a-checkpoint/

At first I dismissed the idea because it is talking about replication and I didn’t see any replication on Server1, but when I went to look at the log_reuse_wait_desc as the article mentions, sure enough I found this:

--

SELECT name, log_reuse_wait_desc 

FROM sys.databases

WHERE name = N'DBStuff'

  

name	log_reuse_wait_desc
DBStuff	REPLICATION

--

As described in the article I tried running sp_removedbreplication against DBStuff since there is no apparent replication (publications or subscriptions) but as the author notes this did not resolve the issue in my case.

The fix described in the article involves setting up a new dummy table in the database in question and creating a publication for that dummy table, which will supposedly goose the system and reset the log_reuse_wait_desc from REPLICATION to some “normal” type such as NOTHING or CHECKPOINT.  After this you can remove the replication and the dummy table and be done.

The catch for us was that the replication components weren’t installed on the Server1 instance during its original install!

Microsoft SQL Server Management Studio is unable to access replication components because replication is not installed on this instance of SQL Server. For information about installing replication, see the topic Installing Replication in SQL Server Books Online. 

Replication components are not installed on this server. Run SQL Server Setup again and select the option to install replication. (Microsoft SQL Server, Error: 21028)

Sigh...

--

After mounting media and installing the Replication components the rest was relatively easy - add a dummy table, create a publication, then drop the publication and dummy table - just as described in the referenced post above.

Eventually we determined that the DBStuff database was restored from another server where it had been involved in a replication topology, which was the source of the offending bits.

--

Hope this helps!

It's Just Another 9002...another error like all the rest...

11:30pm on a Saturday night, and the pager went off…

Error: 5901, Severity: 16, State: 1.

One or more recovery units belonging to database 'i3_dialer' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.

That phrase – “due to database corruption” – always makes my stomach turn just a little…

https://img.memesuper.com/9b7a67d76f5de79439a92cc9ad703dda_im-coming-elizabeth-its-the-im-coming-elizabeth-meme_500-384.jpeg

Thankfully, this turned out to *not* be corruption. <Whew!>

There was a related message:

Could not write a checkpoint record in database DB_1 because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files.

…and then the real problem…

Error: 9002, Severity: 17, State: 9.

The transaction log for database DB_1 is full due to 'AVAILABILITY_REPLICA'.

--

Upon cursory examination, DB_1 was suspended (not moving data) in Sales_AG, with the AG being primary on node ServerA.  The other databases in the AG were all transferring data without apparent issue.

I went to check the drive hosting the LDF log file and almost broke out laughing at midnight – it was a mount point that was 367MB.

That’s right, megabytes.

 

http://static.ddmcdn.com/gif/tallest-fishing-tales0.jpg

The DB_1 database was just over 1GB, with a 350MB LDF file.

Being an Availability Group, there wasn’t shared storage, so I went to the B node to look and see if something was sitting on the drive on that side – some extra file that was taking up space and preventing the log file from auto-growing, but I didn’t find one.

--

The regular Database Index Maintenance instigated this situation.  The largest object (table) in DB_1 is only 69MB, but the transaction LOG backup job only runs every fifteen minutes.  In a database this small it is quite possible that the index rebuild will cycle through all of the tables in the database between LOG backups, which would drive the LDF fill out towards the used space of the MDF data file, in this case 1GB.

In this specific situation, the LDF log file grew by 10% repeatedly until it hit 350MB.  At that point, there wasn’t room for another 10%/35MB so it couldn’t grow any more.

On a regular (non-availability group) database this would manifest as the 9002 errors for log full messages with a log_reuse_wait_desc of LOG_BACKUP – the most likely fix would be to take LOG backups, shrink the LDF log file if needed, and be done.  That was not the case here.

(Note that the log_reuse_wait_desc here wasn’t LOG_BACKUP, it was AVAILABILITY_REPLICA.  This shows that something was breaking the situation from the AG side before the lack of LOG_BACKUP could even be raised as a problem, but I didn’t notice that at the time.)

--

I added a second LDF log file (not a great answer but often a useful triage to get things moving) and that allowed DB_1 to function, but it did not allow the availability group data movement to resume.  To try to repair the situation, I removed DB_1 from the Sales_AG availability group.  (Note that this does not prevent applications from accessing the database, but rather it would not allow DB_1 to fail over to the B node if a failover occurred – but in the current state where data movement was suspended and non-resumable, a failover wouldn’t work anyway.)

I ran the LOG backup job manually multiple times and then tried to re-add the DB_1 database to the availability group – that action gave me a telling message that there wasn’t room on the B node to restore the LDF log file:

System.Data.SqlClient.SqlError: There is insufficient free space on disk volume 'X:\LOG' to create the database. The database requires 368050176 additional free bytes, while only 356515840 bytes are available.

http://s2.quickmeme.com/img/a7/a765c5be1bb9380025b17660b4782d2b5dc9fa3a9d50ede174a680733de1d383.jpg

 What I found surprised me even more than finding a 367MB mount point in the first place on ServerA – the mount point on ServerB was only 343 MB!

This case with the availability group made the situation especially nasty – by growing to 350MB the LDF log file was now past the size of the 343MB mount point on the B node.  This automatically suspended data movement for the DB_1 database (not the whole availability group – just the single database) while it was waiting for sufficient space to grow the LDF on the secondary node.

I have dealt with lots of clustering/mirroring/AG’s over the years, and I have never tripped over this particular issue before.  With traditional failover clustering, you have shared storage, so this isn’t an issue; with a “no shared storage” design, this is a real risk for both mirroring and AG’s, and in this case it was exaggerated by the tiny size of the mount.

I was able to shrink the LDF log file of DB_1 to 310MB, which fit inside the 343MB mount point on the B node.  At that point I was able to re-add DB_1 to Sales_AG, and after it was successfully back in the availability group the database showed in the monitor as successfully moving data from A>>B.

I removed the secondary LDF log file that I had added as it is not recommended to have multiple log files as a regular operating state. #TheMoreYouKnow

As a final step I shrunk the primary LDF log file a little further (shrinking is not great but is sometimes necessary) and then modified the FILEGROWTH for the file to grow in 10MB increments (rather than %).  I then manually capped the MAXSIZE of the file at 335MB so that if the file did fill again, it wouldn’t break the availability group in this same way.  The database would still throw 9002’s, but they would be for LOG_BACKUP, not AVAILABILITY_REPLICA.

I warned the client that they really needed to expand the mount point a little (storage is cheap, right?) to cover the needs of the index maintenance and to match the sizes.  I also noted that if the mount point were expanded, the manual cap would need to be lifted before the DB_1 LDF log file will take advantage of any extra space.

--

The cautionary tale here is about the mismatched storage – as unlikely as it may seems to have 343MB and 365MB mount points, the exact same thing could happen on a system with a 343GB and 367GB mounts with a file trying to grow past the smaller of the two sizes.

Configurations on AG partners don’t have to be exactly the same, but they should be darned close…and for storage – just make them the same, and save yourself this particular headache.

http://img01.deviantart.net/668d/i/2004/260/e/c/headache_by_cat_lovers.jpg

Hope this helps!

T-SQL Tuesday #89 – The O/S It is A-Changing

It's T-SQL Tuesday time again - the monthly blog party was started by Adam Machanic (blog/@AdamMachanic) and each month someone different selects a new topic.  This month's cycle is hosted by Koen Verbeeck (blog/@Ko_Ver) and his chosen topic is "The Times They Are A-Changing" considering changing technologies and how we see them impacting our careers in the future. (His idea came from a great article/webcast by Kendra Little (blog/@Kendra_Little) titled "Will the Cloud Eat My DBA Job?" - check it out too!)

--

I remember my first contact with this topic came a long time ago (relatively) from a very reliable source having a little April Fool's Day fun:

SQL Server on Linux
By Steve Jones, 2015/12/25 (first published: 2005/04/01)

A previous April Fools joke, but one of my favorites. Enjoy it today on this holiday - Steve.

I can't believe it. My sources at Micrsoft put out the word late yesterday that there is a project underway to port SQL Server to Linux!!

The exclusive word I got through some close, anonymous sources is that Microsoft realizes that they cannot stamp out Linus. Unlike OS/2 and the expensive, high end Unices, Linux is here to stay. Because of the decentralized work on it, the religous-like fever of its followers, and the high performance that it offers, the big boys, maybe just one big boy, at Microsoft have given in to the fact that it will forever be nipping at the heals of Windows.

And they know that all the work being done on clients for Exchange means that many sites that might want to switch the desktop, may still keep the server on Exchange and have a rich client front end that takes the place of Outlook. But don't rule against Outlook making a run at the Linux platform.

SQL Server, however, is a true platform that doesn't need a client to run against. With SQL Server 2005 and it's CLR integration, the platform actually expands into the application server space and can support many web development systems on a single server, within two highly integrated applications: SQL Server and IIS.
And with MySQL nipping away at many smaller installations that might have switched to SQL Server before, it's time to do something. So a top secret effort has been porting the CLR using Mono, along with the core relational engine to the Linux platform. The storage engine requires a complete rewrite as the physical storage model of Linux is radially different from that of Windows.

Integration Services, the next evolution of DTS, has already been ported with some impressive numbers of throughput. Given that many different sources need a high quality ETL engine, and a SQL Server license to handle this is still cheaper than IBM's Information Integration product and most other tools, there's hope that Integration Services will provide the foothold into Linux.

No work on pricing, but from hallway conversation it's looking like there will be another tier of licensing that will be less expensive than the Workgroup edition without CALs. There is still supposed to be a small, free version without Integration Services that competes against many small LAMP installations.

There isn't a stable complete product yet, heck, we don't even have the Windows version, but our best guess is that a SQL Server 2005b for Linux will release sometime in mid to late 2006 and the two platforms will then synch development over the next SQL Server cycle.

Moving onto a free platform is a difficuly and challenging task. Especially when you are coming from the proprietary software world. But Microsoft did it once before by moving into the free world of the Internet and taking on Netscape. I'm betting they will do it again.

And I'm sure...
that SQL Server...
is the best...;

April Fools!!!

Over ten years ago Steve Jones (blog/@way0utwest) of SQLServerCentral thought he was kidding but was actually looking forward into the future in which we are about to live.

--

The topic came up again on April Fool's Day a few years later...

How to run SQL Server on Linux
Posted on April 1, 2011 by retracement

UPDATE 2011-04-10: PLEASE NOTE THIS WAS AN APRILS FOOL JOKE 🙂 

Well I have finally cracked it, after years of trying (and a little help from the R2 release) to get SQL Server to install and run happily on the Linux Platform. I could hardly believe it when it all came together, but I’m very pleased that it has. What is even more exciting is that the performance of the SQL Engine is going through the roof.

I am not entirely sure why this is, but I am assuming it is partly related to the capabilities of the EXT4 filesystem and the latest Linux Kernel improvements.

So here’s how I did it :-

• Install WINE 

• Install the .NET 3.5 framework into WINE. This is very important, otherwise the SQL Server 2008R2 installer will fail. 

• Change a couple of WINE settings to allow for 64bit M.U.G extensions. 

• Install the Application Release 1 for Linux Free Object Orientated Libraries by sudo apt-get install aP-R1l\ f-0Ol

Ensure that you run setup.exe from SQL Server R2 Enterprise Edition – please note that SQL Server 2008 Release 1.5 will not work, and I additionally had problems with the Standard Edition of R2 (but not entirely sure if there is a restriction here with SQL Standard Edition Licensing on  Linux).

SQL running happily in WINE on Linux Mint 10 (x64)

I think that the EXT4 filesystem is key to the success of your installation, since when I attempted this deployment using the EXT2 and EXT3 filesystems, SQL Server appeared to have issues installing.

I hope to provide more instructions and performance feedback to you all over the coming months. Enjoy!

Microsoft Certified Master Mark Broadbent (blog/@retracement) gave it a different spin - instead of a spoofed Microsoft announcement he set it up as "look what I did!" and didn't confirm the April Fool until several days later.

--

Microsoft got in on the act in March of 2016 (notable *not* on April Fool's Day) with a blog post headed with this logo:

Suddenly, it was all real.

--

When I read the announcement and subsequent blog posts and saw the follow-up webcasts, I thought one thing to myself over and over.

WHAT DO I DO KNOW?

I am an infrastructure DBA and have been for over seventeen years.  One of my strengths has always been my ability to look beyond SQL Server and dig into the operating system and its components, such as Windows Clustering.  

I don't know anything about Linux (other than recognizing this guy):

https://en.wikipedia.org/wiki/Tux

--

I am moderately ashamed to admit I did what many people do in this situation, and what I know many DBA's are still doing on this topic...

http://www.theecologist.org/siteimage/scale/800/600/388011.png

I quietly ignored it and went about my life and job, putting off the problem until later.

Time passed and Microsoft released a "public preview"/CTP of what they began calling "SQL Server vNext" for Linux, and it became more real.  Then they released another, and another - as of this writing the current CTP is version 1.4 (download it here).

I recently realized I hadn't progressed past my original query:

WHAT DO I DO KNOW?

--

I have determined that there are three parts to the answer of this question:

(1) I need to continue to play with the CTP's and be ready to embrace the as-yet-unannounced RTM version.  Everything I have seen so far has greatly impressed me with how similar the look and feel truly is to "normal" SQL Server on Windows.  Obviously there isn't a GUI-style Management Studio for non-GUI Linux, but connecting to a Linux-hosted SQL Server instance from a Windows-client Management Studio is basically seamless.  From a Linux host we can use SQLCMD and it looks and feels just like Windows SQLCMD.

(2) I need to commit myself to learning more about Linux, at least at the base levels.  I have written numerous times before about the never-ending quest for knowledge we are all on, and this is just the next topic to pursue in a long line.  I was happy to see that there are many classes on Pluralsight related to Linux and Linux administration.  This subject has been something that was down there on my priority list below Powershell and a few other things, but it needs to move up and will do so.

(Side note - if you don't have a Pluralsight subscription, I think you should - it is an awesome resource full of recorded classes on an unbelievable number of topics, including many classes on SQL Server.  You can get a ten-day free trial membership here.)

(3) I need to find more excuses to work with colleagues and potentially with clients on this new technology.  None of us exist in a vacuum, and everyone has a different spin on a given topic.  I recently attended a Microsoft workshop on this topic and it was very interesting to see the different insights from not only the variety of MS speakers but also the other workshop attendees.  Interacting with peers and clients will help me learn even more quickly by providing their insights and by requiring me to research and test to answer their questions.

--

As with all technology changes, this definitely *will* change our jobs, and like many other things in the SQL Server world, the answer to how it will affect any given individual is:

https://3.bp.blogspot.com/-TEjSrM9xdHA/Vr6kx1jKSeI/AAAAAAABBkI/B_dxYc8AX7Y/s1600/itdepends.jpg

The impact to you will be determined by your ability to adapt and learn - if you are willing to embrace change (a dirty phrase to many DBA's) and learn new things, this will be an exciting time - not only will you need to learn new things, you will find yourself interacting with a new class of I.T. Professionals you may have never spoken to before - the Linux Administrator.

https://catmacros.files.wordpress.com/2009/08/whoa_thats_epic_cat.jpg

Hope this helps!

Who Left Those Old Components in My New Database???

Recently a client requested some new transactional replication publications on a relatively new pre-production server.  The client has plenty of transactional and snapshot replication in their environment, including two other publications on this instance already, so this was just adding more to the existing pile.

Piece of cake, right?

https://s-media-cache-ak0.pinimg.com/564x/f2/de/6d/f2de6d3610642b866edcf76f7f86129a.jpg

I went through the new Publication Wizard (Shoot me - it is often the easiest way to handle a new "normal" publication) but this time I was greeted with a pair of errors:

Synonym 'dbo.MSreplication_subscriptions' refers to an invalid object.(Microsoft SQL Server, Error: 5313)

Object Reference not set to an instance of an object, (ConfigureDistWizard)

Um...OK...

My first instinct was that something was broken with the tools on the existing server - I RDP'ed to a different client server, and received the same errors when trying to create a publication for this server.

Hmm...

I have had experiences before where the REPL wizards just don't work (such as creating subscriptions to a higher version publication) so I said, "FINE, I'll just use the code!"

...but the sp_addpublication query threw me the same problem.

https://cdn.meme.am/cache/instances/folder61/250x250/71896061.jpg

At this point I figured that something must have been wrong with the replication components themselves - the server was new and not *really* production yet so maybe something had been fouled at setup.

***NOTE*** - I did *not* take the one step I should have at this point - since there were existing publications on a different user database, I should have paused and considered what was wrong with *this particular database* rather than with the instance at large - it would have saved us roughly 24 hours.

I advised the client I would script out the two existing publications and then completely disable and re-enable publishing and distribution on the instance and try again.

No good...

At this point the client suggested blowing away the VM and setting it up again - I could take backups of the existing user databases and restore them after they recreated the VM from scratch.  I told them  I could continue to investigate but they decided to go ahead with the rebuild.

Fast forward to the next day...

http://fandompodcast.com/wp-content/uploads/2016/01/tune-in-tomorrow.jpg

Even after the server rebuild, I found that the problem with the Publication Wizard still existed!  More than a little research found that it was due to the particular database still having some old replication components in it – older than the current version and therefore not cleaned up when I tried running sp_removedbreplication across the user databases to try to restart replication setup from scratch.  Since the problem was in one of the user databases it still existed after the rebuild since we just restored the old databases onto the new server!

I found the source of the problem (the old components) via the methodology described here http://willtonic.blogspot.com/2012/03/sql-2005-sp4-and-msreplicationsubscript.html and then mitigated it by renaming the two offending objects:

--

USE <dbname>

GO

EXEC sp_rename 'MSreplication_subscriptions', 'MSreplication_subscriptions_old'

GO

EXEC sp_rename 'MSsubscription_agents', 'MSsubscription_agents_old'

GO

--

This resolved the issue and allowed the New Publication Wizard to move forward. 

In the post-mortem, we found that the impacted database had originally been provided by a vendor and that it almost certainly had been migrated from an older version of SQL Server, hence the old components.

--

I want to draw attention to the fact that the blog post I referenced above doesn't have anything to do with errors in the replication New Publication wizard.  It discusses a "bug" in SQL Server 2005 the author encountered while trying to apply a 2005 service pack.  I found this post simply by spreading my search criteria from my specific error of "Synonym 'dbo.MSreplication_subscriptions' refers to an invalid object" down to simply "dbo.MSreplication_subscriptions error"

When troubleshooting (and searching for information online in general), never discount something simply because it doesn't match your specific situation.  You may find as in this case that there is some shared underlying problem that caused a certain set of symptoms for you but had manifest as a different yet similar set of symptoms for someone else.  If you are looking for a specific error or message and not finding anything, cast your net a little wider by reducing your criteria - always start with your specific message, but if you don't get anything useful try again, and instead of searching for "Really Long Error Message about this Object and Some Other Stuff" look instead for "<object> <other stuff> error/warning/whatever"

As always, proceed with caution, but this is true even when you *do* find a blog post/newsgroup post/Connect item/etc. that *does* exactly match your situation - just because renaming an index fixed Error 123456 for someone else doesn't necessarily mean it will for you - always consider the potential impact of any course of action *before* deploying it anywhere, even to Dev/Test.

https://imgflip.com/i/1mvtnu

Hope this helps!

Querying SQL and Windows Version Info with T-SQL

Just a quick one today - I see questions sometimes about polling Windows information from inside SQL Server itself.  There are a couple of frequently touted options:

--

(1)  SELECT @@VERSION

The most basic option, and it does return most of what we want but not in any kind of a pretty format:


Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
Oct 20 2015 15:36:27                  
Copyright (c) Microsoft Corporation                 
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

To just run a query and see the answer this is fine, but usually I like to be able to programmatically manipulate the data (such as an ORDER BY), and a result set that is one big text field (with embedded line feeds) is not a great way to go.

--

(2) exec master..xp_cmdshell 'systeminfo'

This is sort of cheating as it requires a call to xp_cmdshell to call a Windows command rather than anything truly inside SQL Server, but it does work (assuming you have xp_cmdshell enabled):

Host Name:               Instance01

OS Name:                   Microsoft Windows Server 2008 R2 Standard

OS Version:                6.1.7601 Service Pack 1 Build 7601

OS Manufacturer:           Microsoft Corporation

OS Configuration:          Member Server

OS Build Type:             Multiprocessor Free

Registered Owner:         MyCompany

Registered Organization:   MyCompany

Product ID:                00477-001-0000421-84319

Original Install Date:     3/13/2013, 8:28:33 AM

System Boot Time:          1/28/2017, 8:03:41 AM

System Manufacturer:       VMware, Inc.

System Model:              VMware Virtual Platform

System Type:               x64-based PC

Processor(s):              4 Processor(s) Installed.

[01]: Intel64 Family 6 Model 45 Stepping 2 GenuineIntel ~2893 Mhz

[02]: Intel64 Family 6 Model 45 Stepping 2 GenuineIntel ~2893 Mhz

[03]: Intel64 Family 6 Model 45 Stepping 2 GenuineIntel ~2893 Mhz

[04]: Intel64 Family 6 Model 45 Stepping 2 GenuineIntel ~2893 Mhz

BIOS Version:              Phoenix Technologies LTD 6.00, 9/21/2015

Windows Directory:         C:\Windows

System Directory:          C:\Windows\system32

Boot Device:               \Device\HarddiskVolume1

System Locale:             en-us;English (United States)

Input Locale:              en-us;English (United States)

Time Zone:                 (UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna

Total Physical Memory:     24,576 MB

Available Physical Memory: 5,405 MB

Virtual Memory: Max Size:  25,598 MB

Virtual Memory: Available: 6,182 MB

Virtual Memory: In Use:    19,416 MB

Page File Location(s):     D:\pagefile.sys

Domain:                   mydomain.com

Logon Server:              N/A

Hotfix(s):                 143 Hotfix(s) Installed.

[01]: KB2470949

[02]: KB2509553

[03]: KB2511455

[04]: KB2547244

[05]: KB2560656

[06]: KB2570947

[07]: KB2585542

[08]: KB2604115

[09]: KB2621440

[10]: KB2644615

[11]: KB2654428

[12]: KB2667402

[13]: KB2676562

[14]: KB2690533

[15]: KB2692929

[16]: KB2698365

[17]: KB2705219

[18]: KB2709715

[19]: KB2724197

[20]: KB2736422

[21]: KB2742599

[22]: KB2758857

[23]: KB2765809

[24]: KB2770660

[25]: KB2799494

[26]: KB2807986

[27]: KB2813170

[28]: KB2813347

[29]: KB2813430

[30]: KB2840149

[31]: KB2840631

[32]: KB2861698

[33]: KB2862152

[34]: KB2862330

[35]: KB2862335

[36]: KB2862973

[37]: KB2864202

[38]: KB2868038

[39]: KB2871997

[40]: KB2884256

[41]: KB2892074

[42]: KB2893294

[43]: KB2894844

[44]: KB2898851

[45]: KB2911501

[46]: KB2931356

[47]: KB2937610

[48]: KB2943357

[49]: KB2957189

[50]: KB2968294

[51]: KB2972100

[52]: KB2972211

[53]: KB2973112

[54]: KB2973201

[55]: KB2973351

[56]: KB2977292

[57]: KB2978120

[58]: KB2984972

[59]: KB2991963

[60]: KB2992611

[61]: KB3000483

[62]: KB3003743

[63]: KB3004361

[64]: KB3004375

[65]: KB3010788

[66]: KB3011780

[67]: KB3018238

[68]: KB3019978

[69]: KB3021674

[70]: KB3022777

[71]: KB3023215

[72]: KB3030377

[73]: KB3033889

[74]: KB3035126

[75]: KB3037574

[76]: KB3038314

[77]: KB3042553

[78]: KB3045685

[79]: KB3046017

[80]: KB3046269

[81]: KB3055642

[82]: KB3059317

[83]: KB3060716

[84]: KB3068457

[85]: KB3071756

[86]: KB3072305

[87]: KB3072630

[88]: KB3074543

[89]: KB3075220

[90]: KB3076895

[91]: KB3078601

[92]: KB3080446

[93]: KB3084135

[94]: KB3086255

[95]: KB3092601

[96]: KB3097989

[97]: KB3101722

[98]: KB3108371

[99]: KB3108381

[100]: KB3108664

[101]: KB3108670

[102]: KB3109103

[103]: KB3109560

[104]: KB3110329

[105]: KB3122648

[106]: KB3123479

[107]: KB3124275

[108]: KB3126587

[109]: KB3127220

[110]: KB3133043

[111]: KB3135983

[112]: KB3139398

[113]: KB3139914

[114]: KB3139940

[115]: KB3142024

[116]: KB3142042

[117]: KB3145739

[118]: KB3146706

[119]: KB3146963

[120]: KB3149090

[121]: KB3156016

[122]: KB3156017

[123]: KB3156019

[124]: KB3159398

[125]: KB3161949

[126]: KB3161958

[127]: KB3163245

[128]: KB3164033

[129]: KB3164035

[130]: KB3170455

[131]: KB3177186

[132]: KB3184122

[133]: KB3185911

[134]: KB3188740

[135]: KB3192321

[136]: KB3192391

[137]: KB3205394

[138]: KB3210131

[139]: KB3212642

[140]: KB958488

[141]: KB976902

[142]: KB976932

[143]: KB3212646

Network Card(s):           1 NIC(s) Installed.

[01]: Intel(R) PRO/1000 MT Network Connection

Connection Name: LAN Prod

DHCP Enabled:    No

IP address(es)

[01]: 192.168.22.33

https://i.ytimg.com/vi/eOJ32gNM0qc/hqdefault.jpg 

Well....assuming you have xp_cmdshell enabled *and* you want to know every single KB applied to your server, ever.

Again this isn't cleanly parseable, so it isn't the optimal answer.

--

(3) Glenn Berry

If you have read my blog at all you know I am a huge fan of Glenn Alan Berry's (blog/@GlennAlanBerry) "Diagnostic Information Queries" - commonly referred to as the "DMV Queries." (as seen here, here, here, and here.)  

http://s2.quickmeme.com/img/99/995c9a89f2eb1f869fb6fd7fc72ac143a76d6313f05cb0b8309813514eb0f876.jpg

Glenn has been maintaining this forever, and does an amazing job of both deciphering the DMVs and of cataloging submissions from other prominent SQL Server professionals (such as Jimmy May's great Disk Latency query).

The first relevant query is currently Query #1:

SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version Info];

As you can see this is just the combination of @@VERSION described above and @@SERVERNAME to return the instance's name.

The next (and more interesting) query is Query #3:

SELECT SERVERPROPERTY('MachineName') AS [MachineName], 

SERVERPROPERTY('ServerName') AS [ServerName],
SERVERPROPERTY('InstanceName') AS [Instance],
SERVERPROPERTY('IsClustered') AS [IsClustered],
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('ProductLevel') AS [ProductLevel],                                                       -- What servicing branch (RTM/SP/CU)
SERVERPROPERTY('ProductUpdateLevel') AS [ProductUpdateLevel],         -- Within a servicing branch, what CU# is applied
SERVERPROPERTY('ProductVersion') AS [ProductVersion],
SERVERPROPERTY('ProductMajorVersion') AS [ProductMajorVersion],
SERVERPROPERTY('ProductMinorVersion') AS [ProductMinorVersion],
SERVERPROPERTY('ProductBuild') AS [ProductBuild],
SERVERPROPERTY('ProductBuildType') AS [ProductBuildType],                    -- Is this a GDR or OD hotfix (NULL if on a CU build)
SERVERPROPERTY('ProductUpdateReference') AS [ProductUpdateReference], -- KB article number that is applicable for this build
SERVERPROPERTY('ProcessID') AS [ProcessID],
SERVERPROPERTY('Collation') AS [Collation],
SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled],
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],
SERVERPROPERTY('FilestreamConfiguredLevel') AS [FilestreamConfiguredLevel],
SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled],
SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus],
SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported],
SERVERPROPERTY('BuildClrVersion') AS [Build CLR Version]; 

MachineName	ServerName	Instance	IsClustered
EDCRMDB01V	EDCRMDB01V	NULL	0
ComputerNamePhysicalNetBIOS	Edition	ProductLevel	ProductUpdateLevel
EDCRMDB01V	Enterprise Edition (64-bit)	SP3	NULL
ProductVersion	ProductMajorVersion	ProductMinorVersion	ProductBuild
11.0.6020.0	11	0	6020
ProductBuildType	ProductUpdateReference	ProcessID	Collation
NULL	KB3072779	1988	Latin1_General_CI_AI
IsFullTextInstalled	IsIntegratedSecurityOnly	FilestreamConfiguredLevel	IsHadrEnabled
1	0	0	0
HadrManagerStatus	IsXTPSupported	Build CLR Version
2	NULL	v4.0.30319

This query introduces the concept of the SERVERPROPERTY() function and shows some of the many fields that it can retrieve.  On of the nicest things about SERVERPROPERTY() to me is that it cleanly parses out the Major and Minor versions of SQL Server - this is the most direct way to find out that your instance is Version 11 (SQL 2012).

The limitation for our purposes here is that it doesn't return any information about the Operating System version (here we are talking about Windows because these hooks just don't play into Linux)...you get a little external information like the NETBIOS name, but nothing about the actual Windows Version.

We need something more...

--

(4) My Answer

As is often the case, my best answer is a cross between a couple of the previous options to take the best of both... kind of like this:

http://weknowmemes.com/wp-content/uploads/2014/02/hilarious-animal-hybrids.jpg

(Sorry - I found that picture and couldn't resist...)


This query uses string functions to parse @@VERSION and extract some of the information (and then returns pretty text via a pair of CASE statements) and also uses some of the more meaningful SERVERPROPERTY() values.

The string to parse the Windows Version Number out of @@VERSION came from here - much easier than trying to backtrack it myself!

SELECT SERVERPROPERTY('ServerName') AS [SQLServerName]

, SERVERPROPERTY('ProductVersion') AS [SQLProductVersion]

, SERVERPROPERTY('ProductMajorVersion') AS [ProductMajorVersion]
, SERVERPROPERTY('ProductMinorVersion') AS [ProductMinorVersion]
, SERVERPROPERTY('ProductBuild') AS [ProductBuild]
, CASE LEFT(CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion')),4) 

WHEN '8.00' THEN 'SQL Server 2000'

WHEN '9.00' THEN 'SQL Server 2005'

WHEN '10.0' THEN 'SQL Server 2008'

WHEN '10.5' THEN 'SQL Server 2008 R2'

WHEN '11.0' THEN 'SQL Server 2012'

WHEN '12.0' THEN 'SQL Server 2014'

ELSE 'SQL Server 2016+'

END AS [SQLVersionBuild]

, SERVERPROPERTY('ProductLevel') AS [SQLServicePack]

, SERVERPROPERTY('Edition') AS [SQLEdition]

, RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3) as [WindowsVersionNumber]

, CASE RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)

WHEN '5.0' THEN 'Windows 2000'

WHEN '5.1' THEN 'Windows XP'

WHEN '5.2' THEN 'Windows Server 2003/2003 R2'

WHEN '6.0' THEN 'Windows Server 2008/Windows Vista'

WHEN '6.1' THEN 'Windows Server 2008 R2/Windows 7'

WHEN '6.2' THEN 'Windows Server 2012/Windows 8'

ELSE 'Windows 2012 R2+'

END AS [WindowsVersionBuild]

This allows me to return a pretty result set and also to extract number values and simple strings that I can then programmatically act upon (or sort/filter in Excel):

SQLServerName	SQLProductVersion	ProductMajorVersion	ProductMinorVersion	ProductBuild
EDCRMDB01V	11.0.6020.0	11	0	6020
SQLVersionBuild	SQLServicePack	SQLEdition	WindowsVersionNumber	WindowsVersionBuild
SQL Server 2012	SP3	Enterprise Edition (64-bit)	6.1	Windows Server 2008 R2/Windows 7

--

Hope this helps!