Is SQL Server Enterprise Edition, or Standard?  You might try experimenting with max server memory (maybe set it a little lower to give the OS a slightly larger slice of...

Dan

Sounds like you've anticipated that someone is going to suggest using a splitter function.  If you can't create one, not even in tempdb, then you'll need to modify...

Phani

You need to speak to the administrator of the server (the DBA?) and ask to be granted the necessary access to the database.  If you don't have it,...

Looks like when you join your source and target tables on those four columns, you have more than one value of No_Of_Attempts in your source table for one or more combinations...

This is going to fail if the user who created the record has since been deleted.  Why don't you change the function so it gets the user name instead of...

Please can we see the code?  Have you tried EXECUTE AS OWNER or dbo?  You should be using sys.database_principals instead of syslogins, since the latter is deprecated.

John

SQL Server Browser only works for named instances, so if your instance is a default instance, you won't need it.  Assuming you have a named instance, though, since it is...

Have you tried applying SP2?  Better still, why not go straight to SSMS 2016?

John

Paul

That's your answer, then.  Index rebuilds need space in the database, which is freed up at the end of the operation.  Consider using Ola Hallengren's database maintenance solution...

Paul

Where do you get that figure of 42-50%?  Please will you post the results of sp_spaceused?  How big is the largest table in the database, and how often...

Paul

Your log file is tiny, and your data file is growing by only a GB at a time.  Is auto-shrink enabled?
SELECT DATABASEPROPERTYEX('MyDB','isAutoShrink')

What are...

Paul

Please will you show us the results of this query?

SELECT
    name
,    size/128 AS SizeMb
,    growth/128 AS GrowthMB
,    is_percent_growth
FROM MyDB.sys.database_files

John

Have you tried this?

John

WITH ConverttoNumber AS (
    SELECT
        ID
    ,    CAST(LEFT(ID + '000',4) AS int) AS Nbr
    FROM StgTemp
    )
SELECT DISTINCT ID
FROM ConverttoNumber
WHERE Nbr BETWEEN 3500 and 3999
OR...

Cast to int and then it's simple to find values that fall in a certain range.

John