Clearing NPS-LOG database

 use [NPS-LOG]

go

 

----------------------------------------------

declare @logs_period int = 31

-- how many days should be saved in database

----------------------------------------------

 

declare @mindate datetime

declare @period_to_delete int

DECLARE @i int = 0

declare @deletion_point date

 

set @mindate = (SELECT TOP (1) timestamp FROM [NPS-LOG].[dbo].[accounting_data] order by timestamp asc)

print cast(@mindate as date)

 

set @period_to_delete =  datediff(d, @mindate,  GETDATE()) - @logs_period

print @period_to_delete

 

WHILE @i < @period_to_delete

BEGIN

    set @deletion_point = dateadd(d, @i, @mindate)

    print @deletion_point

    delete from [NPS-LOG].[dbo].[accounting_data] where  [timestamp] < @deletion_point

    checkpoint

    dbcc shrinkfile('NPS-LOG_log')

    SET @i = @i + 1

END

checkpoint

dbcc shrinkfile ('NPS-LOG', 16, NOTRUNCATE)

checkpoint

dbcc shrinkfile ('NPS-LOG', TRUNCATEONLY)

SQL memory usage

 SELECT object_name, counter_name, cntr_value/1024/1024 AS 'Total Server Memory (KB)'

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Total Server Memory (KB)'

SELECT (physical_memory_in_use_kb / 1024) AS CurrentlyUsedMemorybySQLServer

    ,(locked_page_allocations_kb / 1024) AS Locked_pages_used_Sqlserver_MB

    ,(total_virtual_address_space_kb / 1024) AS Total_VAS_in_MB

    ,process_physical_memory_low

    ,process_virtual_memory_low

FROM sys.dm_os_process_memory;

Shrink Log files

 declare @dbn varchar(max), @dbf varchar(max), @cmd varchar(max)

declare  CURS cursor for

SELECT 

    db.name AS [Database Name], 

    mf.name AS [Logical Name] 

FROM 

     sys.master_files AS mf

     INNER JOIN sys.databases AS db ON

            db.database_id = mf.database_id

where mf.type_desc = 'LOG' and db.database_id > 4;

open CURS;

FETCH NEXT FROM curs INTO @dbn, @dbf

WHILE @@FETCH_STATUS = 0

BEGIN

--print @dbn; print @dbf

select @cmd=''

select @cmd = 'use ['+@dbn+']; ALTER DATABASE ['+@dbn+'] MODIFY FILE (NAME = '''+@dbf+''', SIZE = 64, FILEGROWTH = 16MB); DBCC SHRINKFILE('''+@dbf+''',64);';

--print @cmd;

exec (@cmd);

FETCH NEXT FROM curs INTO @dbn, @dbf

end 

close CURS;

deallocate CURS;

SQL replication monitoring

 --SELECT * FROM [distribution].dbo.MSReplication_monitordata WHERE status not in(1,2,3,4)

SELECT * FROM [distribution].dbo.MSReplication_monitordata 

where not( agent_type=1 and status=2)

and not(agent_type = 2 and status=3)

3--2 - error

/*

agent_type

1 = Snapshot Agent

2 = Log Reader Agent

3 = Distribution Agent

4 = Merge Agent

9 = Queue Reader Agent

status

1 = Started

2 = Succeeded

3 = In progress

4 = Idle

5 = Retrying

6 = Failed

*/

-- 1 = Snapshot Agent / 2 = Succeeded

SELECT * FROM [distribution].dbo.MSReplication_monitordata where agent_type=1 and status<>2 

-- 2 = Log Reader Agent / 3 = In progress

SELECT * FROM [distribution].dbo.MSReplication_monitordata where agent_type=2 and status <>3

-- 3 = Distribution Agent / 3 = In progress, 4 = Idle 

SELECT * FROM [distribution].dbo.MSReplication_monitordata where agent_type=3 and (status <>3 or status <>4)

SELECT count(1) FROM [distribution].dbo.MSReplication_monitordata 

where 

(agent_type=1 and status<>2)

or (agent_type=2 and status <>3)

or (agent_type=3 and (status <>3 and status <>4) )

SELECT * FROM [distribution].dbo.MSReplication_monitordata 

Re-register VSS Writers for Server 2012-2016

 

1. Create a batch file by:
   1. Copy the following list:
      • net stop vss
      • net stop swprv
      • regsvr32 /s ole32.dll
      • regsvr32 /s oleaut32.dll
      • regsvr32 /s vss_ps.dll
      • vssvc /register
      • regsvr32 /s /i swprv.dll
      • regsvr32 /s /i eventcls.dll
      • regsvr32 /s es.dll
      • regsvr32 /s stdprov.dll
      • regsvr32 /s vssui.dll
      • regsvr32 /s msxml.dll
      • regsvr32 /s msxml3.dll
      • regsvr32 /s msxml4.dll
      • vssvc /register
      • net start swprv
      • net start vss
   2. Open a blank text file
   3. Paste the copied list
   4. Save As fixvss.bat to an easily accessible location like the desktop of the workstation
2. Right-click batch file > Run as administrator
3. Immediately reboot device

SureBackup NAS mounts

 восстановление работы SureBackup Testlab

  An error occurred during host configuration. 192.168.88.6: Fault "PlatformConfigFaultFault", detail "Operation failed, diagnostics report: Unable to get console path for volume, VeeamBackup_TS-FS01"  
[24.08.2023 12:38:19] <10> Error         An error occurred during host configuration. . Failed to mount NFS volume (192.168.88.6:/VeeamBackup_TS-FS01). 192.168.88.6: Fault "PlatformConfigFaultFault", detail "Operation failed, diagnostics report: Unable to get console path for volume, VeeamBackup_TS-FS01"  

  An error occurred during host configuration. 192.168.88.6: Fault "PlatformConfigFaultFault", detail "Operation failed, diagnostics report: Unable to get console path for volume, VeeamBackup_TS-FS01"  
[24.08.2023 12:38:19] <10> Error         An error occurred during host configuration. . Failed to mount NFS volume (192.168.88.6:/VeeamBackup_TS-FS01). 192.168.88.6: Fault "PlatformConfigFaultFault", detail "Operation failed, diagnostics report: Unable to get console path for volume, VeeamBackup_TS-FS01"  

Fault "DuplicateNameFault", detail "<DuplicateNameFault xmlns="urn:vim25" xsi:type="DuplicateName" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><name>VeeamBackup_srv-bkp2

 Лечение такое:  
1. Заходим в консоль ESXi и смотрим список подключений
[root@esxi:~] esxcfg-nas -l
в моем случае пусто
2. Отключаем VeeamBackup_TS-FS01, где TS-FS01 имя сервера
[root@esxi:~] esxcfg-nas -d VeeamBackup_TS-FS01
IORM: failed to disable IORM: Unable to get console path for volume, VeeamBackup_TS-FS01
NAS volume VeeamBackup_TS-FS01 deleted.
3. Все работает. Повторное монтирование образа виртуальной машины проходит успешно

MSDistribution cleanup

 EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 24

select count(*) from distribution..msrepl_commands

select count(*) from distribution..msrepl_transactions

go

DECLARE @rowcountCom int = 5000000

DELETE TOP(@rowcountCom) MSrepl_commands WITH (PAGLOCK) 

FROM MSrepl_commands 

WITH (INDEX(ucMSrepl_commands)) 

DELETE TOP(@rowcountCom) MSrepl_commands WITH (PAGLOCK) 

FROM MSrepl_commands 

WITH (INDEX(ucMSrepl_commands)) 

DELETE TOP(@rowcountCom) MSrepl_commands WITH (PAGLOCK) 

FROM MSrepl_commands 

WITH (INDEX(ucMSrepl_commands)) 

go

DELETE TOP(@rowcountCom) MSrepl_commands WITH (PAGLOCK) 

FROM MSrepl_commands 

WITH (INDEX(ucMSrepl_commands)) 

Read acces to SQL JOBS

 use msdb

CREATE USER "Group_or_USer" FOR LOGIN "Group_or_USer"";
ALTER ROLE SQLAgentReaderRole add member "Group_or_USer""

Read access to Stored Procedures

use [Database]

SELECT N'GRANT VIEW DEFINITION ON '

    + QUOTENAME(SPECIFIC_SCHEMA)

    + N'.'

    + QUOTENAME(SPECIFIC_NAME)

    + N' to "Group_or_User";'

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = 'PROCEDURE';

Fail2Ban 65535 elements in IPSET limit

Edit file /etc/fail2ban/action.d/iptables-ipset-proto4.conf

add/change maxelem parameter:

actionstart = ipset --create f2b-<name> iphash hashsize 32768 maxelem 200000

Save file, than restart Fail2Ban service. To check applied parameter execute
# ipset -L | grep "Header"

Powershell TLS

 Get-PSRepository

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force

Register-PSRepository -Default -InstallationPolicy Trusted

Перенос БД tempdab на новое место

-- Новый каталог для базы tempdb
DECLARE @Path as NVARCHAR(400) = 'E:\SQL_Temp'
DECLARE @physicalName NVARCHAR(500), @logicalName NVARCHAR(500)
DECLARE @SQLString NVARCHAR(400)

USE master;

DECLARE fnc CURSOR LOCAL FAST_FORWARD FOR
(
SELECT
name,
physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb')
)
OPEN fnc;
FETCH fnc INTO @logicalName, @physicalName;
WHILE @@FETCH_STATUS=0

BEGIN
SET @SQLString = '
ALTER DATABASE tempdb
MODIFY FILE (NAME = ''' + @logicalName
+ ''', FILENAME = ''' + @Path + '\'
+ REVERSE(SUBSTRING(REVERSE(@physicalName), 1, CHARINDEX('\', REVERSE(@physicalName))-1))
+ ''');'
PRINT @SQLString
EXEC sp_executesql @SQLString

FETCH fnc INTO @logicalName, @physicalName;

END;

CLOSE fnc;

DEALLOCATE fnc;

RDP cert manual changing

Добавить новый сертификат в Personal Store LocalMachine

Находим отпечаток нового сертификата

Get-Childitem Cert:\LocalMachine\My

выполняем 

wmic /namespace:\\root\CIMV2\TerminalServices PATH Win32_TSGeneralSetting Set SSLCertificateSHA1Hash="полученный_отпечаток_нового_сертификата"

проверяем, что всё хорошо

Get-WmiObject -Class "Win32_TSGeneralSetting" -Namespace root\cimv2\terminalservices | select SSLCertificateSHA1Hash

net stop SessionEnv

net start SessionEnv

Установка Confluence на Debian 11 + MSSQL (Windows 2019)

SQL

Установка Windows и SQL 

Эти действия проводятся в лучших традициях и настройках по мануалам Best Practice. 

Server Collation для MS SQL по требованиям Atlassian - Latin1_General_CI_AI.
Не забываем поставить SSMS.

Создаём пользователя MSSQL

USE [master]

GO

MSSQL Agent XPs

 sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

Bulk change ownership in MSSQL

For Jobs

USE msdb ;  
GO  
EXEC dbo.sp_manage_jobs_by_login  
    @action = N'REASSIGN',  
    @current_owner_login_name = N'old_account',  
    @new_owner_login_name = N'new_account' ;  

GO  

For Databases

DECLARE @command varchar(1000) 

SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'')
BEGIN USE [?]
    exec sp_changedbowner ''sa'' 
END' 

EXEC sp_MSforeachdb @command 

Windows+Office KMS on Debian 11

 Установка

apt-get update && apt-get upgrade -y
apt-get install -y git gcc make net-tools mc

cd ~
git clone https://github.com/Wind4/vlmcsd

MS SQL total memory usage

 select

(physical_memory_in_use_kb/1024) Physical_Memory_usedby_Sqlserver_MB,

(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,--RAM+ Pagefile

process_physical_memory_low,

process_virtual_memory_low

from sys. dm_os_process_memory

MS SQL размеры таблиц в БД

 SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB DESC, t.Name

Backup SQLExpress databases

SQL_batch.sql

declare @dbname varchar(50);
declare @sql varchar(max);
declare @path varchar(50);
set @path = 'F:\Backup.SQL\Backups\'
declare mycur cursor for select name  from sys.databases where database_id>4
open mycur
fetch next from mycur into @dbname;
while @@FETCH_STATUS =0 
begin
set @sql = 'backup database ['+@dbname+'] to disk='''+@path+format( getdate(), 'yyyyMMdd-hhmm')+'-'+@dbname+'.bak'';';
print @sql
exec(@sql);
fetch next from mycur into @dbname;
end
close mycur;
deallocate mycur;

CMD file

sqlcmd -E -S .\sqlexpress -i F:\Backup.SQL\Maintenance\SQL_batch.sql

forFiles /p "F:\Backup.SQL\Backups" /s /d -30 /c "cmd /c del @file"