Memory and CPU Information Findings Query

SELECT SERVERPROPERTY ('servername') as Servername, name, [value], [value_in_use] 

FROM [sys].[configurations] 

WHERE [name] like '%memory%' or name like '%ad hoc%' or name like '%cpu%'

GO

select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers

where status='VISIBLE ONLINE'

Truncate Log If Initial Size Exceed

SET NOCOUNT ON;

DECLARE @DBNAME SYSNAME, @LogFileName sysname
DECLARE @InitialSize  INT
DECLARE @ExistingSize int
SET @DBNAME = 'DB-ODS-CLIENTNAME'

SELECT TOP 1 @InitialSize = (size*8)/1024, @LogFileName = NAME

      FROM sys.master_files
WHERE
      DB_NAME(database_id) = @DBNAME AND TYPE = 1;

     

 
SELECT @InitialSize
SELECT
      @ExistingSize =
    SUM(CASE WHEN counter_name = 'Log File(s) Size (KB)' THEN cntr_value/1024 END)
FROM
    sys.dm_os_performance_counters
WHERE
    counter_name IN ('Data File(s) Size (KB)', 'Log File(s) Size (KB)')
    AND instance_name <> '_Total' AND instance_name = @DBNAME
GROUP BY
    instance_name;

   

SELECT @ExistingSize
     IF @ExistingSize < @InitialSize

    BEGIN

            PRINT 'Do the Truncation here'

            DECLARE @folderName VARCHAR(1000)

            select top 1 @folderName=   LEFT(a.physical_device_name, LEN(a.physical_device_name) - CHARINDEX('\', REVERSE(a.physical_device_name)))

            from msdb.dbo.backupmediafamily a

            join msdb.dbo.backupset b

            on (a.media_set_id=b.media_set_id)

            where database_name= @DBNAME and b.type='L';

        

      

            DECLARE @folderNameFull varchar(1000)

            select top 1 @folderNameFull=   LEFT(a.physical_device_name, LEN(a.physical_device_name) - CHARINDEX('\', REVERSE(a.physical_device_name)))

            from msdb.dbo.backupmediafamily a

            join msdb.dbo.backupset b

            on (a.media_set_id=b.media_set_id)

            where database_name= @DBNAME and b.type='D';

          -- Get Date To Join in the FileName  

            DECLARE @date VARCHAR(20)

            SELECT @date = replace(replace(replace(LEFT(CONVERT(VARCHAR, GETDATE(), 120), 20),'-',''),' ',''),':','')

            select @folderName

            SELECT  @date

            select @DBNAME

           

            SELECT @folderName = @folderName + '\' + @DBNAME + @date + '.trn'

            SELECT @folderNameFull = @folderNameFull + '\' + @DBNAME + @date + '.bak'

    

            SELECT @folderName;

            declare @sql varchar(max)

            set @sql ='

           USE [master];

            BACKUP DATABASE [' + @DBNAME +']  TO  DISK = ''' + @folderNameFull +'''   WITH INIT,COMPRESSION,  STATS = 10;

            BACKUP LOG [' + @DBNAME +']  TO  DISK = ''' + @folderName+'''   WITH INIT,COMPRESSION,  STATS = 10;

           

            ALTER DATABASE [' + @DBNAME +'] SET RECOVERY SIMPLE WITH NO_WAIT;

            USE [' +@DBNAME +'];

            DBCC SHRINKFILE ([' +@LogFileName+']  , 0)

            USE [master];

            ALTER DATABASE [' + @DBNAME +']  SET RECOVERY FULL WITH NO_WAIT;

            '

         

            Print @sql

            Exec (@SQL)

      END

   

    ELSE

   

    BEGIN

               PRINT ' No Truncation Required'

    END

SET NOCOUNT OFF;