Category: SQL Server

General DBA, SQL Server

How to find the current/active machine in SQLServer cluster?

Using of the functions below one can find the current/active machine in SQLServer cluster. Select ServerProperty(‘ComputerNamePhysicalNetBIOS’) NODE2 — fn_virtualservernodes() returns all the nodes in cluster, the first record returned is the active select top 1 * from fn_virtualservernodes() NODE2 select * from fn_virtualservernodes() NODE2 NODE1 Advertisements Continue reading How to find the current/active machine in SQLServer cluster?

Alex Lima2 Comments
General DBA, SQL Server

Shirking sharepoint database

We had an issue the other day when one of the Sharepoint database logs became extremely large as the server where it was installed didn’t have SQLServer Management Studio so using “sqlcmd” we were able connect to the database and shrink the logfile and it ran on SQLServer lite Edition. Here are the steps: 1) Connect to the sharepoint database via sqlcmd C:> sqlcmd -S … Continue reading Shirking sharepoint database

Alex LimaLeave a comment
SQL Server

How to list indexes and the column names?

— Lists user indexes that are not primary and not unique indexes in SQLServer 2005 and up select t.name as table_name , ind.name as index_name , ic.index_column_id — column order in the index , col.name from sys.indexes ind inner join sys.index_columns ic on ind.object_id = ic.object_id and ind.index_id = ic.index_id inner join sys.columns col on ic.object_id = col.object_id and ic.column_id = col.column_id inner join sys.tables … Continue reading How to list indexes and the column names?

Alex LimaLeave a comment
SQL Server, Tuning

How to execution plan on SQLServer

In SQLServer Managment Studio to the execution plan one can use one of the following SET commands “SET SHOWPLAN_XML ON” – SQLServer will not execute the SQL and it will return execution plan and has estimated row counts and stats in form of a XML document. The output displayed in SQL Server management studio can be stored with .sqlplan extension using “Save Results As..” which … Continue reading How to execution plan on SQLServer

Alex LimaLeave a comment