Cluster Logs in Windows Server 2008??

In this Blog post, let’s focus on where/how we can review Failover Cluster Logs on Windows Server 2008 and above. As most of us know on Windows Server 2003 Cluster, we used to have  “cluster.log” file on each node participating in cluster, which contains debug information. FYI, One can locate these files in “%systemroot% \ cluster” Folder. But how about cluster log files in Windows Server 2008/2008R2?? Uhuhh…It’s not something which you can review directly by navigating to systemroot folder. Below is the screenshot of that folder in my cluster.

You can see a folder called “REPORTS” in the above screenshot where all the cluster Validation Reports will be stored by default. attaching below Screenshot Just to prove, that cluster.log file can’t be located in the “reports” folder as well 🙂

Starting Windows Server 2008, cluster logs are managed by something called as “Windows Event Tracing“. Just an FYI, If you are interested, You can pull all the current  running traces by opening “perfmon” and navigating to Data Collector Sets. (Shown Below in the Screenshot)

So, as any other logs, cluster logs are stored in “C:\Windows\System32\winevt\Logs” folder with “etl” extension as you can see below.

Well, so How to read those .ETL files??

For that, we have to use “cluster.exe” command with “/gen” switch. Basically this will generate a human readable text file in your “Reports” folder.

Syntax: Cluster log /gen

Output:

 

As you can see in the above Screenshot, it will communicate with all the nodes in your cluster. In my scenario, Node2 is offline(Powered down).  BTW, even though Node2 is down, it will create “Cluster.txt” file in your Reports Folder with related information.

So, how to generate Logs related to a specific Node?

You have to use “/NODE” switch with your cluster log syntax. Please see below Screenshot.

As you can see, this time, we had no RPC Errors.

So, there is lot to explore/learn in 2008 Failover Clustering, if you are using 2003 since long time, things got changed drastically. There are lot of other options/switches available with cluster.exe. Even you can limit the size if you are interested. BTW, everything which I’ve shown here can be achieved via Powershell Cmdlets as well!

Hope this is informative….

sp_configure and sys.Configurations

This would be a very small blogpost, Well, actually an informational Post, no Issues and no troubleshooting shown in this post!

We all, being a DBA will be running “sp_configure” every now and then on our SQL Instances to see what are the configured settings on the Instances. okay, okay….If you don’t have this Practice, make this a habit from right now. Document your Instance settings in your Run Book and compare with them each time you run this and you can analyze what/why/when/who changed accordingly.

Okay, By default if we run “sp_configure” on our Instance, we’ll be getting only 17 Rows(as of SQL Server 2012 RC0).

So are those the only configurable Settings we’ve in SQL Server? Nope!

Actually, you can get all the configurable options from querying sys.configurations, where it returns 69 Rows as shown below.

Is_Dynamic = 1 implies, no need of running RECONFIGURE Statement after changing the setting.

Is_Advanced = 1 implies, we should run RECONFIGURE Statement after changing a setting.

Now, for sp_configure to return all the 69 Options(rows) all we’ve to do is enable “show advanced options” and run “RECONFIGURE” as shown below.

Once, done issuing “SP_Configure” will return all the options available as you can see below.

Now, you can see 69 rows being returned. Cheers!

SQL Server Error Logs??

Couple of days ago, I had conversation with a Jr.DBA regarding SQL Server Error Logs! She was confused with “Error Logs” under SQL Server Agent Node and “SQL Server Logs” under Management Node. In Fact, she was assuming that both are same which is not the case!!!

Also, I heard many times from many confused DBA’s that SQL Server Error logs can’t be accessed when the SQL Services are Offline, which is absolute Bogus!! SQL Server Error log is nothing but a plain Text file located in our “LOG” folder by default which can be opened using Notepad when you are not able to connect to SQL and see via SSMS.

In this blog post, let me make it clear what/Where are SQL Server Logs and what/Where are SQL Agent logs are. Please see below Screenshot.

As you can see we’ve “SQL Server Logs” – Under Management Node and “Error Logs”- Under SQL Server Agent.

Note: All these Error Logs(SQL Server Logs and SQL Agent Logs and any Dumps being created) will be located in “C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\Log” location. – In my case, I’ve SQL Server 2008R2 and a named Instance ‘PROD’, hence folder “MSSQL10_50.PROD”. “LOG” folder is not for storing your Transaction Logs!!!

If you are confused even now, You can use T-SQL to read your Error Log.

How to read SQL Server Error Log from T-SQL??

Method 1: EXEC XP_READERRORLOG

Method 2: EXEC SP_READERRORLOG – This is UnDocumented way! But this gives lot more flexibility to select the number and filter the contents etc…For example I want to read the contents from my 5th Error Log which has the word “Master”.  For this, all i want to enter in my Query Pane is Exec Sp_ReadErrorlog  5, 1, ‘master‘. As you can see below I got only 4 records returned where ever word “master” is present.

Hope this helps…!

Few Useful(often Ignored) DBCC Commands…

Thought of compiling all the useful DBCC Commands for DBA related to Memory Management within SQL Server. Well,  I’ve seen many DBA’s unaware of the below very useful commands staying under assumption, CHECKDB is the only DBCC Command we’ve in SQL Server.

Okay! Let’s see what do SQL Server offer to DBA’s to do  a diagnosis of SQL Server Memory.

DBCC DROPCLEANBUFFERS : This Command will remove all the Data Pages from the Buffer Cache. Basically, you can issue this if you want to clear your Buffer Cache.

DBCC FREEPROCCACHE: This command will remove all the execution plans from Procedure Cache.

DBCC FREESYSTEMCACHE(‘ALL’) : This command will remove all the Unused entries from all the available caches. It includes Plan cache as well!

DBCC FREESESSIONCACHE : This command will Flush the Distributed Query Connection(Linked Servers) Cache on SQL Instance.

DBCC FLUSHPROCINDB(DB_ID) : This is a pretty useful command which clears Query Plans related to a given Database.
Note: This is an Undocumented Command. 

Hope this is useful! Be very cautious and understand what you are doing before issuing these Commands blindly on your Production SQL Instances to resolve an issue, especially on an Instance which is shared by Multiple applications with many Databases.

The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’

Today I was setting up a Linked Server from one SQL Server 2008R2 Instance to another SQL Server 2005 Instance with one of the Databases as a Catalog and I was able to create the Linked Server and Tested it successfully, but the Developer was getting an annoying Error as shown below…

The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’. (.Net SqlClient Data Provider)

What does it mean? It is complaining about a missing permission from Resource Database, which really can’t be touched by any means in terms of Permissions from our SSMS. ( Remember, Resource DB can be accessed just from File System as mdf and ldf files from your Binaries Folder)

So, how to fix this annoying Error?

My Scenario:

1. I’ve a created  a Linked Server using “Microsoft OLEDB Provider for SQL Server” as  Provider by choosing Server type as Other Provider  instead of going with SQL Server. Don’t ask me why 😀

2. I’ve logged in as a user which is not a SysAdmin and when i try to expand the Catalogs from Linked Server I got  this below error message, as you can see in below Screenshot.

Please note I’ve logged in using login “Linked” – I’ve created a test login called “Linked” for this Demo Purpose…

How to Fix this?

1.Map the User to MASTER Database( By Def. It will be added to Public Role)

2.USE MASTER

GO

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO LOGIN;

Once, I did this, I’m able to access the Linked Server without any Issues as you can see below…

Howdyyyyyyy! Hope this helps