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) Connect to the sharepoint database via sqlcmd
C:> sqlcmd -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

2) List the databases
1> select name, database_id from sys.databases;
2> go
name database_id
—————————————————————————
master 1
tempdb 2
model 3
msdb 4
SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b 5
SharePoint_AdminContent_b002cd77-a299-4ed7-998c-2fec58619fa5 6
WSS_Search_CALSHP1AP01 7
WSS_Content 8

3) Connect to the database that had the large logfile
1> use [SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b]
2> go

3) Get the current size of the files
1> select name, size from sys.sysfiles;
2> go
name size
——————————————————— —————————-
SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b 664
SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b_log 2171808
(2 rows affected)

4) Shrink the logfile
1> dbcc shrinkfile(‘SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b_log’, 2)
2> go
Cannot shrink log file 2 (SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b_log) because all logical log files are in use.

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
—— ———– ———– ———– ———– ————–
5 2 1986704 63 1986704 56
(1 rows affected)
DBCC execution completed. If DBCC printed error messages, contact your system ad ministrator.

1> BACKUP LOG [SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b] with truncate_only
2> go
1> dbcc shrinkfile(‘SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b_log’, 2)
2> go

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
—— ———– ———– ———– ———– ————–
5 2 256 63 256 56
(1 rows affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

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 t on ind.object_id = t.object_id
where ind.is_primary_key = 0
and ind.is_unique = 0
and ind.is_unique_constraint = 0
and t.is_ms_shipped = 0
order by t.name
, ind.index_id
, ic.index_column_id

table_name index_name index_column_id column_name
————— ————— —————— —————-
base_action PK_base_action 1 action_id
base_category PK_base_category 1 type_id
base_category PK_base_category 2 table_id

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

Example:
-- Turn on trace so SQLServer writes message to SQL Server log everytime check point occurs
DBCC TRACEON(3502, -1)

-- Display which traces are turned on and is it global or session based
DBCC TRACESTATUS(-1)

-- Turn off trace 3502
DBCC TRACEOFF(3502, -1)

Messages recorded in SQLServer log:
2011-03-26 17:28:24.840 spid52 DBCC TRACEON 3502, server process ID (SPID) 52. This is an informational message only; no user action is required.
2011-03-26 17:30:43.300 spid11s Ckpt dbid 5 started (8)
2011-03-26 17:30:43.300 spid11s About to log Checkpoint begin.
2011-03-26 17:30:44.320 spid11s Ckpt dbid 5 phase 1 ended (8)
2011-03-26 17:30:44.320 spid11s About to log Checkpoint end.
2011-03-26 17:30:44.330 spid11s Ckpt dbid 5 complete

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 will allow show you view execution plan of the SQL in SQL Server Managment Studio. To turn off this option by “”SET SHOWPLAN_XML OFF”
“SET SHOWPLAN_TEXT ON” – SQLServer will not execute the query, not include estimated row counts and statistics.
“SET SHOWPLAN_ALL ON” – SQLServer will not execute the query, it will include estimated row counts and statistics
“SET STATISTICS PROFILE ON” – SQLServer will execute the query and include estimated rows counts and statistics and actual row counts and statistics.
“SET STATISTICS XML ON” – SQLSever will execute the query and include estimated rows counts and statistics and actual row counts and statistics.

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, getdate()) -- last week
, DATEADD(yyyy, 1, getdate()) -- add next year

Output:
2011-02-11 00:07:18.990 2011-02-12 00:07:18.990 2011-02-09 00:07:18.990 2011-02-11 01:07:18.990 2011-02-11 00:37:18.990 2011-02-18 00:07:18.990 2011-02-04 00:07:18.990 2012-02-11 00:07:18.990 2011-02-11 01:07:18.930