microsoft/code-search

Code Search Admin scripts (SQL and PS) for managing a Code Search on-prem instance

Code-Search

Code Search Admin scripts (SQL and PS) for managing a Code Search on-prem instance

Troubleshooting

To find help on troubleshooting Azure DevOps Server follow this link.

code-search's Issues

The disk space is running out and the search not work

Hi,
We have very big TFS DB (about 1.6TB).
We have separate code server with disk space of 600GB, after few weeks the space is running out and the search not working.
How we can make a retention policy to delete data and clean up the space?
(For example - every 2 weeks 200GB will be deleted).
By the way, we have TFS 2017.3.
Thank you!

Azure_DevOps_Server_2019/ReIndexFiles.ps1

Has somebody already ran this script ;)

Invoke-Sqlcmd : Procedure or function 'prc_MergeFileLevelFailures' expects parameter '@maxFailureThreshold', which was not supplied.
At C:\Code-Search\Azure_DevOps_Server_2019\ReIndexFiles.ps1:67 char:21
+ ... ryResults = Invoke-Sqlcmd -InputFile $SqlFullPath -serverInstance $SQ ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

prc_MergeFileLevelFailures parameters:

-- Hash: 66F9F9467AC87713D89D5AB3A184B27B3A12FB6D
ALTER PROCEDURE [Search].[prc_MergeFileLevelFailures]
(
    @partitionId               INT,
    @indexingUnitId            INT,
    @fileLevelFailureRecords   Search.typ_FileFailureRecord READONLY,
    @maxFailureThreshold       INT
)

This repo is missing important files

There are important files that Microsoft projects should all have that are not present in this repository. A pull request has been opened to add the missing file(s). When the pr is merged this issue will be closed automatically.

Microsoft teams can learn more about this effort and share feedback within the open source guidance available internally.

Merge this pull request

Any updated script that can be used for azure sql db connection?

https://learn.microsoft.com/en-us/azure/devops/server/install/install-azure-sql?view=azure-devops-2022
It's possible to use ADO server with azure SQL db. but elastic search scipt provided here only works for onprem environment.

e.g.
$SqlFullPath = Join-Path $PWD -ChildPath 'CollectionDBDiagnosticScripts\IndexingUnitData.sql'
$queryResults = Invoke-Sqlcmd -InputFile $SqlFullPath -serverInstance $SQLServerInstance -database $CollectionDatabaseName

Invoke-Sqlcmd here uses windows logon, not azure managed identity. And there are many places in the script using Invoke-Sqlcmd. Hence it cannot connect to azure sql db.

Could you modify the script and share an updated version which can be used for azure sql db connection as well?

Support for Azure DevOps 2020

Hi team,
are you planning to release 2020 version?

/SearchDiagonistics/ConfigurationDBSearchDiagnostics.ps1 should require "Tfs collection database" as parameter

/SearchDiagonistics/ConfigurationDBSearchDiagnostics.ps1 executes the Sql script "ExtensionData.sql" that queries [tbl_RegistryItems] in the default tfs collection database.
This database may have a different name than the one hardcoded in the script.

My suggestion is to ask the collection database as a parameter for the PS script, having a default with the hardcoded name.

https://github.com/Microsoft/Code-Search/blob/3be409ea44ce2a58e5936f6534b4273b0d5b51e4/SearchDiagonistics/ConfigurationDBDiagnosticScripts/ExtensionData.sql#L6

Regards.

Add scripts that support multiple db instances

Can you add scripts that can handle multiple db instances?
At my company we have Tfs installed with a SQL Cluster, where the configuration is on one instance and the collection on another. These scripts aren't handling this right now.

SearchDiagnostics/CollectionDbSearchDiagnostics.ps1 log dir is created inside sql script folder

Hi.
This is not a bug, but a simple fix to make CollectionDBSearchDiagnostics.ps1 behave like ConfigurationDBSearchDiagnostics.ps1
The first script creates the log folder inside the SQL scripts folder instead of creating it in the root, as the second script does.

https://github.com/Microsoft/Code-Search/blob/3be409ea44ce2a58e5936f6534b4273b0d5b51e4/SearchDiagonistics/CollectionDBSearchDiagnostics.ps1#L20

If the sugestion is accepted and you are busy I could make a PR, since it is quite simple.

Regards

SqlServer module instead of SQLPS

It would be nice to start switching to SqlServer module instead of SQLPS, which is no longer being updated and is only available with SQL Server.
In general, when managing Code Search instance, it is not convenient to switch to SQL server and run part of the scripts there. Sometimes, in bigger organizations, there is a dedicated team, who is responsible for managing and maintaining SQL Servers and you typically do not get sufficient access to the server to run scripts. Which is why it would be a way better to be able to manage the Code Search instance from the same box, where it is installed.

I think, amending PS scripts to start depending on SqlServer PS module would be beneficial.

How do I search using powershell on premise TFS ?

I am running TFS 2017 on prem update 2

$tfsUrl = 'https://tfs:444/tfs'
$projectId = 'd18ff858-xxxxxxxx'
$teamId = '9907188a-xxxxxxxx'
$tfsRepos = "$tfsUrl/Common/$projectId/_apis/search/codesearchresults?api-version=4.1-preview.1"
$secureCredential = Get-Credential
$allResults = (Invoke-RestMethod -Uri $tfsRepos -Method Post -Credential $secureCredential).value
$allResults= $allResults | Select -first 20

I am getting this error

Invoke-RestMethod :
Page not found. - Microsoft Team Foundation Server

Reference ( Could not find TFS Api reference only for the VSTS one is there )
https://docs.microsoft.com/en-us/rest/api/vsts/search/code%20search%20results/fetch%20code%20search%20results

Unable to disable indexing of specific projects

Is there a way to disable indexing specific projects? I've poked around the Search schema in the database, and I was curious if I could mark a project as IsDeleted in the tbl_ClassificationNode table or if there's another method I should try.

Any plan to support TFS2017 update 3 ?

Scripts will not run. Expecting other dependency "SQLPS"

My server was brought to its knees rapidly trying to first setup this feature.
I need to stop the processes in order to first up the resources...but the instructions are not working.

According to this release page, I should be able to easily launch the provided scripts.
https://www.visualstudio.com/en-us/docs/search/administration/#stop-indexing

However, those blow up trying to run:

PS D:\CodeSearchScripts> .\StopSearchIndexing.ps1
Security warning
Run only scripts that you trust. While scripts from the internet can be useful, this script can potentially harm your
computer. Do you want to run D:\CodeSearchScripts\StopSearchIndexing.ps1?
[D] Do not run [R] Run once [S] Suspend [?] Help (default is "D"): R

cmdlet StopSearchIndexing.ps1 at command pipeline position 1
Supply values for the following parameters:
(Type !? for Help.)
ServerInstance: myDBServer
ConfigurationDatabaseName: Tfs_Configuration
**_Import-Module : The specified module 'SQLPS' was not loaded because no valid module file was found in any module
directory.
At D:\CodeSearchScripts\StopSearchIndexing.ps1:11 char:1

• Import-Module -Name SQLPS

• - CategoryInfo          : ResourceUnavailable: (SQLPS:String) [Import-Module], FileNotFoundException
  - FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Commands.ImportModuleCommand
  
  

Invoke-Sqlcmd : The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable
program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At D:\CodeSearchScripts\StopSearchIndexing.ps1:12 char:1

• Invoke-Sqlcmd -InputFile "StopSearchIndexing.sql" -serverInstance $ServerInstanc ...

• - CategoryInfo          : ObjectNotFound: (Invoke-Sqlcmd:String) [], CommandNotFoundException
  - FullyQualifiedErrorId : CommandNotFoundException_**
  

\ExcludedTfvcFoldersForIndexing.ps1 folder format

The documentation does not specify how to format TFVC folder paths.

Which one of these is it?

• $/Project/Folder1,$/Project/Folder2
• /Folder1,/Folder2
• Folder1,Folder2

I can do a PR on the existing Read-Host doc (which is currently "Specify comma separated list of folders to Exclude from Indexing") to clarify this once I know what is the correct format.

Additionally, I'm assuming this exclusion works recursively on all sub folders. What if I want to exclude only that folder?

Scripts Erroring / Not Existing

Hello,

I am trying to run the latest version of these scripts against a TFS 2017 installation and I'm having not much luck.

https://github.com/Microsoft/Code-Search/blob/master/ResumeSearchIndexing.ps1#L24

Is looking for a StartSearchIndexing.sql script that doesn't exist now (it used to looking at history).

The CheckIndexingStatus script throws the following errors:

Supply values for the following parameters:
(Type !? for Help.)
SQLServerInstance: sql-tfs01
CollectionDatabaseName: Tfs_DefaultCollection
ConfigurationDatabaseName: Tfs_Configuration
CollectionName: DefaultCollection
Days: 10
Checking indexing state for last 10 days
Select-object : Property "IndexingCompletedCount" cannot be found.
At C:\Users\richardca\Downloads\Code-Search-master (1)\Code-Search-master\CheckIndexingStatus.ps1:57 char:37
+     $resultState = $queryResults  | Select-object  -ExpandProperty  IndexingComp ...
+                                     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (System.Data.DataRow:PSObject) [Select-Object], PSArgumentException
    + FullyQualifiedErrorId : ExpandPropertyNotFound,Microsoft.PowerShell.Commands.SelectObjectCommand

Select-object : Property "IndexingCompletedCount" cannot be found.
At C:\Users\richardca\Downloads\Code-Search-master (1)\Code-Search-master\CheckIndexingStatus.ps1:58 char:39
+     $resultMessage = $queryResults  | Select-object  -ExpandProperty  IndexingCo ...
+                                       ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (System.Data.DataRow:PSObject) [Select-Object], PSArgumentException
    + FullyQualifiedErrorId : ExpandPropertyNotFound,Microsoft.PowerShell.Commands.SelectObjectCommand

C:\Users\richardca\Downloads\Code-Search-master (1)\Code-Search-master\CheckIndexingStatus.ps1 : Collection indexing was not triggered with this message:
At line:1 char:1
+ .\CheckIndexingStatus.ps1
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,CheckIndexingStatus.ps1

No repositories completed fresh indexing in this collection in last 10 days
No repositories are currently in indexing state.
No repositories are currently in file discovery phase.
PS C:\Users\richardca\Downloads\Code-Search-master (1)\Code-Search-master>

TFS 2018.2 Wiki reindex

Hi,
I had to reindex search indexes, code and workitem are working well, but wiki search is still in state "We are not able to show results because one or more projects in your collection are still being indexed" and cannot be restored. 2018RTW does not support wiki reindex and I'm no able to understand/write that script.
Thanks.

This repo is missing a LICENSE file

This repository is currently missing a LICENSE file.

A license helps users understand how to use your project in a compliant manner. You can find the standard MIT license Microsoft uses at: https://github.com/microsoft/repo-templates/blob/main/shared/LICENSE.

If you would like to learn more about open source licenses, please visit the document at https://aka.ms/license (Microsoft-internal guidance).

collections with spaces in their names make the scripts fail

In case a collection has a space character in its name, the scripts won't work since it doesn't properly put quotes around the string parameters

Can't search forked repos

I can't enable searching for a forked repository. We use forks in our organization to allow different business units to build features and be merged into the parent repo once approved. We need to be able to search the forks as well.

Handling for renamed projects

Although I think this may be related to a refresh job that never finishes correctly, we see results from both the old project name and the new project name.
Is there any steps to follow to correct this? EX: push and resume indexing?

tbl_JobYield missing error

Truncate table search.tbl_JobYield fails in CleanUpCollectionIndexingState_IndexDelete.sql.
Seems that the table is (no longer) present?

Azul Zulu links are out of date

There is a 8u202 released now, but all the update instructions still point to 8u201.
The download links should point to the download page https://www.azul.com/downloads/zulu/zulu-windows/ and what Java Version the should use so they don't follow your instructions and install insecure versions.

Script is referencing the undefined variable $configLogDir

Hi,
problem: in ./SearchDiagonistics/CollectionDBSearchDiagnostics.ps1, $configLogDir is used instead of $collectionLogDir

https://github.com/Microsoft/Code-Search/blob/3be409ea44ce2a58e5936f6534b4273b0d5b51e4/SearchDiagonistics/CollectionDBSearchDiagnostics.ps1#L122

It is a simple fix just to replace the variable name.

If you are very busy, i could make the replace.

regards.

can't execute re-indexcoderepository.ps1 in TFS_2017Update2

https://github.com/Microsoft/Code-Search/blob/master/TFS_2017Update2/Re-IndexingCodeRepository.ps1#L38

in SqlScripts\QueueCodeRe-IndexingJob.sql.sql. This scripts execute with error.

SQL Server Instances fail in Update2

Need to quote serverInstance as in:
-serverInstance "$SQLServerInstance"

just looking are RecentIndexingActivity