SQL Xpress raise 10 Gb for BM DB and during upgrade BM 6.2.20 it breaks the BM Service :(

philippe.camelio_3885

Hello
I upgraded BM to 6.2.20 from 6.2.19 and the service Buildmaster is gone :(
I haven't seen the DB BuildMaster on the SQL Xpress was up to the 10 Gb limit.

any help is welcome

Best Regards

PhilippeC.

rhessinger

Hi @philippe-camelio_3885,

Just to verify, you are running the command line as an administrator correct? Can you also please verify the user you are trying to run the service as has the correct password and is not locked out on the domain?

Thanks,
Rich

philippe.camelio_3885

Hi
I haven't seen your answer.
I modify the topic as it is a DB problem not related to the upgrade
thanks for the fast reply

I think if I clean the DB, bmservice will work.

Any sql magic command to clean the DB ?

atripp

Hello;

That's a lot of data; most of the data will be taken up with the execution logs. You can use the sp_spaceused to verify it, checking the ScopedExecutionLogEntries table.

Usually you would want to use retention policies, but if SQL Server is blocking you from using the database, then you'll have to manually trim the logs, at least to get the software running again.

Let me share some SQL Code from that might help you do this.

First, find builds executions you don't want anymore; the code in the RetentionPolicies_GetBuildsWithLogsToPurge stored procedure might help, here is a way to find some builds you don't want.

	SELECT B.*
	  FROM [Builds_Extended] B
	       INNER JOIN [Applications] A
		           ON A.[Application_Id] = B.[Application_Id]
	 WHERE A.[ApplicationGroup_Id] = @ApplicationGroup_Id
       AND (@Pipeline_Name IS NULL OR B.[Pipeline_Name] = @Pipeline_Name)
	   AND (@DeployedReleasesOnly_Indicator = 'N' OR [ReleaseStatus_Name] = 'Deployed')
	   AND (@RejectedOnly_Indicator = 'N' OR [BuildStatus_Name] = 'Rejected')
	   AND (@AlwaysRetainAfter_Date IS NULL OR B.[CreatedOn_Date] < @AlwaysRetainAfter_Date)
       AND EXISTS(
	        SELECT TOP 1 SEL.[Scope_Sequence]
			  FROM [PipelineStageTargetExecutions_Extended] BEX
		INNER JOIN [ScopedExecutionLogs] SEL
		        ON SEL.[Execution_Id] = BEX.[Execution_Id]
                         WHERE B.[Application_Id] = BEX.[Application_Id]
                           AND B.[Release_Number] = BEX.[Release_Number]
                           AND B.[Build_Number] = BEX.[Build_Number]
        )
	 ORDER BY [CreatedOn_Date]

Then, you can do EXEC Builds_PurgeExecutionLogs @Build_Id = ??, where ?? is the ID of the build.

philippe.camelio_3885

@atripp
Thanks a lot.

I will try this to restore BM access
I had a plan to move to SQL Std, I will do it

philippe.camelio_3885

@philippe-camelio_3885

I have seen the dbo.ManualExecutions is the largest table 8 Go ....
It contains mainly Infrastructure sync info

Can I clear it safely ?

philippe.camelio_3885

@philippe-camelio_3885

Thanks @atripp, I made some space using using script based on your SQL and
I succeed to reinstall the service (thanks @rhessinger - cmd console has to be in admin mode).
I purged more logs from BM itself.
Therefore, the dbo.ManualExecutions did not change a lot .

I did not found a way to purge it from BM.

any Sql available ? - I made a test where I truncated the table and it was a very bad idea :)

atripp

How much space are you seeing ManualExecutions taking? About how many rows? It should be auto-pruning those.

In any case you should be able to just DELETE [Executions] WHERE [Execution_Id] = ??, and it will cascade the deletes as needed, to the other tables (ScopedExecutionLogEntries being the biggest). The manual execution data is not so useful in the mid-term, it's mostly helpful or short-term debugging.

philippe.camelio_3885

ManualExecutions: 162 K records / 8 Go of data
ScopedExecutionsLogsentries : 1099K records / 1.3 Go ofdata

atripp

@philippe-camelio_3885 FYI, we will add a checkbox for auto-purging soon, BM-3655