Removing multiple spaces using recursive solution

This function will replace multiple spaces into single space.

Recursive Solution:


CREATE FUNCTION [dbo].[ufn_TrimSpaces]
(
@strDetails varchar(100)
)
RETURNS varchar(100)
AS
BEGIN

WHILE CHARINDEX('  ',@strDetails) > 0
BEGIN
SET @strDetails = REPLACE(@strDetails, '  ',' ')
END
RETURN LTRIM(RTRIM(@strDetails))
END

to test:


declare @test varchar(100)
set @test = '     This is    sample   text     '

select dbo.ufn_TrimSpaces(@test)

Fix Restored DB backup user login account

Run the script below to fix the user account login from restored db backup.

Use
sp_change_users_login 'auto_fix' ,''

Restoring from backup: System.Data.SqlClient.SqlError: File cannot be restored over the existing

Restore from backup encountered error: System.Data.SqlClient.SqlError: File "" cannot be restored over the existing: 
Reissue the RESTORE statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE to identify an alternate location.

Solution: Click the option page and check the Overwrite the existing database (With Replace). see image below.

Shrink SharePoint Config Log files

1. Backup your sharepoint config log files.
2. Execute the query belowusing Microsoft SQL Server Management Studio.

Note: change to your specific sharepoint config file names.

USE SharePoint_Config
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE SharePoint_Config
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 50 MB.
DBCC SHRINKFILE (SharePoint_Config_log, 50);  -- here 2 is the file ID for trasaction log file,you can also mention the log file name (dbname_log)
GO
-- Reset the database recovery model.
ALTER DATABASE SharePoint_Config
SET RECOVERY FULL;
GO