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
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)…
— 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…
When a user account is created on SQLServer on 2005 with user account set to “Enforce password policy” and “Enforce password expiration”, once set this policy and expiration can’t be changed using. ALTER LOGIN test WITH CHECK_EXPIRATION = OFF; Or ALTER LOGIN test WITH CHECK_POLICY = OFF; SQLserver will return the following message Msg 15128,…
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…
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…
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,…
