Tag Archives: Tips

Finding the nodes in a Failover Cluster

Posted on by
Reply

I was recently asked by a friend, how to find the names of the nodes that are participating in a failover cluster.

The Dynamic Management View sys.dm_os_cluster_nodes will give this information. The following script will return the list of Node names in the cluster.

Note that the query was run on a 3 Node cluster, so the result set displays the 3 Nodes in the cluster.

If the same command is run on a stand alone machine, it would not return any node name indicating it is not a cluster.

— Bru Medishetty

Querying Multiple Servers in SQL Server 2008

Posted on by
1

SQL Server 2008 provides the functionality of querying multiple Servers from a single query window. This is helpful for tasks like retrieving server information such as Version, Edition, Instance Name, logins available across each instance, databases on the servers etc.

In order to query multiple servers the only prerequisite is that the servers need to be registered prior to run the query and of course, there should be a valid login to connect to those Server. The SQL Servers can be of other version such as SQL 2000 or 2005. In this example I have two SQL Server 2005 instances and one SQL Server 2008 instance all of them are installed on a single Machine.

For the purpose of this article, I assume you know how to register a SQL Server in the Local Server Groups. Open registered servers from View menu or using keyboard short-cut Ctrl + Alt+ G (picture below).

 

Right click on the server group, from the pop up menu choose New Query as shown in the picture below.

 A new query editor is opened which has is similar to a normal new query editor. The only difference you find is the status bar down at the the bottom of the query editor. The picture below displays the status bar and we see the difference with a usual query editor status bar. This status bar does not a single instance name rather, displays the Server Group Name, also SPID information is not displayed.

I am running a simple query to retrieve the list of the user Databases on all the instances in the server group. The query results is displayed along with the query. The Database names are displayed along with SQL Instance name.

Those of us who would like to find the backup information of the critical databases or to find the SQL Agent Jobs that failed across several instance, that can all be done from a single query in SQL Server 2008.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru that let’s you know when there is a new blog post.

–Bru Medishetty

View SQL Server Error Logs through T-SQL

Posted on by
4

When SQL Server error log is not initialized at a regular interval causing the error log file to grow into a huge file. Viewing that in the SQL Log File Viewer would be a time consuming task. 

This article will show how to view the SQL Server error log from a query analyzer in 2000 or query editor in later versions. As an additional benefit this code filters the log file and displays only the past 24 hours (default code). 

To view SQL Server error logs in SQL Server 2000

DECLARE @HOURS INT
SET @HOURS = 24

CREATE TABLE #ErrorLog
(ErrorLog Varchar(1000),
ContinuationRow INT)

INSERT INTO #ErrorLog
EXEC sp_readerrorlog

DELETE FROM #ErrorLog
WHERE
(LEFT(LTRIM(ErrorLog),4)
NOT LIKE DATEPART(YYYY,GETDATE())
AND ContinuationRow = 0)
OR ContinuationRow = 1
OR LEN(ErrorLog) < 25
OR
(CAST(LEFT(LTRIM(ErrorLog),23) AS DATETIME)
< CAST(DATEADD(HH,-@HOURS,GETDATE()) AS VARCHAR(23)))

SELECT * FROM #ErrorLog 

DROP TABLE #ErrorLog

To view SQL Server error logs in SQL Server 2005 / 2008 / 2008R2

DECLARE @HOURS INT
SET @HOURS = 24

CREATE TABLE #ErrorLog
(LogDate DateTime, ProcessInfo Varchar(50),
[Text] Varchar(4000))

INSERT INTO #ErrorLog
EXEC sp_readerrorlog

DELETE FROM #ErrorLog
WHERE LogDate < CAST(DATEADD(HH,-@HOURS,
GETDATE()) AS VARCHAR(23))

SELECT * FROM #ErrorLog 

DROP TABLE #ErrorLog

The script can be changed to display only data for the past n # of hours by changing the value of @Hours.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

—Bru Medishetty