TRUNCATE TABLE L_MESSAGES
TRUNCATE TABLE L_NET
TRUNCATE TABLE L_GOUSERS
TRUNCATE TABLE LG_000_SYSLOG
TRUNCATE TABLE LG_HISTORY

DECLARE @FRM INT
DECLARE @DON INT
DECLARE @FN VARCHAR(3)
DECLARE @DN VARCHAR(2)
DECLARE @SQLF NVARCHAR(max)
DECLARE @SQLD NVARCHAR(max) 

SELECT @FRM=NR FROM L_CAPIFIRM ORDER BY NR DESC

WHILE @FRM IS NOT NULL
BEGIN
    SET @FN=RIGHT('000'+CONVERT(VARCHAR(3),@FRM),3)
    SET @SQLF='TRUNCATE TABLE LG_' + @FN +'_HISTORY;TRUNCATE TABLE LG_' + @FN +'_LOGREP;'
    PRINT @SQLF
    EXEC SP_EXECUTESQL @SQLF

    SELECT @DON=NR FROM L_CAPIPERIOD WHERE FIRMNR=@FRM ORDER BY NR DESC
    WHILE @DON IS NOT NULL
    BEGIN
        SET @DN=RIGHT('00'+CONVERT(VARCHAR(2),@DON),2)
        SET @SQLD='TRUNCATE TABLE LG_' + @FN + '_' + @DN + '_HISTORY;'
        PRINT @SQLD
        EXEC SP_EXECUTESQL @SQLD
        SELECT @DON=MIN(NR) FROM L_CAPIPERIOD WHERE FIRMNR=@FRM AND NR>@DON
    END
    SELECT @FRM=MIN(NR) FROM L_CAPIFIRM WHERE NR>@FRM
END
 

TRUNCATE TABLE L_NET
TRUNCATE TABLE L_SYSLOG
DECLARE @CA INT
DECLARE @CN VARCHAR(3)
DECLARE @SQLF NVARCHAR(max)

SELECT @CA=FLDNR FROM L_WORKSPACE ORDER BY FLDNR DESC
WHILE @CA IS NOT NULL
BEGIN
    SET @CN=RIGHT('000'+CONVERT(VARCHAR(3),@CA),3)
    SET @SQLF='TRUNCATE TABLE LG_' + @CN +'_PACKET;TRUNCATE TABLE LG_' + @CN +'_LOGREP;TRUNCATE TABLE LG_' + @CN +'_PACKETLOG;TRUNCATE TABLE LG_' + @CN +'_RULEHISTORY;'
    PRINT @SQLF
    EXEC SP_EXECUTESQL @SQLF
    SELECT @CA=MIN(FLDNR) FROM L_WORKSPACE WHERE FLDNR>@CA
END 

-- SQL PERFORMANS
ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION=ON;

ALTER DATABASE SCOPED CONFIGURATION
SET PARAMETER_SNIFFING =OFF;  

ALTER DATABASE SCOPED CONFIGURATION
SET QUERY_OPTIMIZER_HOTFIXES=ON;

ALTER DATABASE SCOPED CONFIGURATION
CLEAR PROCEDURE_CACHE;

ALTER DATABASE SCOPED CONFIGURATION
SET IDENTITY_CACHE=OFF;
---

--1- AŞAMA Check Database Integrity
DBCC CHECKDB WITH  PHYSICAL_ONLY

--2- AŞAMA Rebuild Index
Declare @TBnameB nvarchar(255)
Declare @SQLB nvarchar(max) 

select @TBnameB = OBJECT_NAME(i.object_id)
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats 
INNER JOIN sys.indexes i ON i.object_id = indexstats.object_id 
WHERE indexstats.avg_fragmentation_in_percent > 30 AND i.index_id = indexstats.index_id
ORDER BY indexstats.avg_fragmentation_in_percent DESC

while @TBnameB is not null
BEGIN
    set @SQLB='ALTER INDEX ALL ON [' + @TBnameB + '] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);'
    print @SQLB
    EXEC SP_EXECUTESQL @SQLB
    select @TBnameB = min(TABLE_NAME) from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' and TABLE_NAME > @TBnameB          
END

--3- AŞAMA Reorganize Index
Declare @TBnameO nvarchar(255)
Declare @SQLO nvarchar(max) 

select @TBnameO = OBJECT_NAME(i.object_id)
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats 
INNER JOIN sys.indexes i ON i.object_id = indexstats.object_id 
WHERE  i.index_id = indexstats.index_id
ORDER BY i.object_id

while @TBnameO is not null
BEGIN
    set @SQLO='ALTER INDEX ALL ON [' + @TBnameO + '] REORGANIZE  WITH ( LOB_COMPACTION = ON );'
    print @SQLO
    EXEC SP_EXECUTESQL @SQLO
    select @TBnameO = min(TABLE_NAME) from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' and TABLE_NAME > @TBnameO          
END

--4- AŞAMA Update Statistics
EXEC sp_updatestats

--5- AŞAMA
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"