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?
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
— 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?
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, Level 16, State 1, Line 1 The CHECK_POLICY and CHECK_EXPIRATION … Continue reading The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON
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 … Continue reading DBCC TRACEON
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
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 … Continue reading Date manipulation