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

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)... Continue Reading →

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... Continue Reading →

DBCC TRACEON

There are 2 types of DBCC TRACEON (session or global). Session trace is turned on that session only and global trace is visible for all connections on the server. Trace#: 3502 - This trace writes information to SQL Server log every time a check point occurs SQLServer 2008 R2 trace flags SQLServer 2005 trace flags... Continue Reading →

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... Continue Reading →

Date manipulation

For SQLServer using arthemetic and DATEADD one can modify date select getdate() -- today , getdate() + 1 -- tomorrow , getdate() - 2 -- day before yesterday , DATEADD(hh, 1, getdate()) -- add 1 hour , DATEADD(mi, 30, getdate()) -- add 30 minutes , DATEADD(d, 7, getdate()) -- add next week , DATEADD(d, -7,... Continue Reading →

How to find/modify SQLServer Agent logfile location?

To get the location of SQLServer Agent log file, the log file is called SQLAGENT.out DECLARE @oem_errorlog nvarchar(255) EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'ErrorLogFile', @oem_errorlog OUTPUT, N'no_output' PRINT @oem_errorlog Sample Output: C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT To modify location and name of SQLServer Agent logfile USE [msdb] GO EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'C:\temp\SQLAGENT.OUT' GO To recycle SQLServer Agent... Continue Reading →

Create a free website or blog at WordPress.com.

Up ↑