Author Archives: paranoids

mssql database transaction log blown

hi there

sometimes you have to fix an mssql server where transaction log is full. due to that the database is more or less unusable

*) check for big tables

SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 as Size
FROM sys.dm_db_partition_stats, sys.objects 
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id 
GROUP BY sys.objects.name
ORDER BY Size DESC;
GO

*) create backup of mssql db

BACKUP DATABASE yourdbname
TO DISK = 'e:\backup\db-bkup.sql'
GO

*) backup transaction log of mssql

BACKUP LOG yourdbname TO DISK = 'e:\backup\yourdb_log.trn' WITH INIT;

*) shrink transaction log

USE yourdbname;
GO
DBCC SHRINKFILE(yourdbname_log, 200);
GO
USE [master];
GO
ALTER DATABASE yourdbname 
  MODIFY FILE
  (NAME = yourdbname_log, SIZE = 200MB, FILEGROWTH = 10MB);
GO

*) disable/reduce transaction log (if you don’t need it)

ALTER DATABASE yourdbname SET RECOVERY SIMPLE;
USE yourdbname;
GO
CHECKPOINT;
GO
CHECKPOINT; -- run twice to ensure file wrap-around
GO
DBCC SHRINKFILE(yourdbname_log, 200); -- unit is set in MBs
GO

*) select data from table

SELECT [a]
      ,[b]
      ,[c]
      ,[d]
      ,[e]
      ,[f]
 FROM [yourdbname].[dbo].[yourtablename] WHERE timestamp < DATEADD(MONTH, -36, GETDATE()) ORDER BY timestamp ASC

*) delete data from table older than 3 years

DELETE FROM [yourdbname].[dbo].[yourtablename] WHERE timestamp < DATEADD(MONTH, -36, GETDATE())

*) shrink databasefile (.mdf) to small size

ALTER DATABASE yourdbname SET RECOVERY SIMPLE;

mssql will produce immense IO and transaction logfile size

USE [yourdbname];
DBCC SHRINKFILE(yourdbname_dat, TargetSize);

*) limit ram usage of mssql server

properties of database-server -> memory -> maximum server memory

have fun

Ubuntu 18.04 Network Configuration ipv4 ipv6 Dual Stack

Hi

no /etc/network/interfaces ?

Let’s go for it πŸ™‚

vim /etc/netplan/01-netcfg.yaml

network:
  version: 2
  renderer: networkd
  ethernets:
    eno1:
      dhcp4: no
      dhcp6: no
      addresses: [192.168.1.2/24, "XXXX:XXXX:XXXX:XXXX::2/64"]
      gateway4: 192.168.1.1
      gateway6: XXXX:XXXX:XXXX:XXXX::1
      nameservers:
        addresses: [1.1.1.1, 1.0.0.1]

here a bridged example

network:
  version: 2
  renderer: networkd
  ethernets:
    eno1:
      dhcp4: no
      dhcp6: no
  bridges:
    br0:
      interfaces: [eno1]
      dhcp4: no
      dhcp6: no
      addresses: [192.168.1.2/24, "XXXX:XXXX:XXXX:XXXX::2/64"]
      gateway4: 192.168.1.1
      gateway6: XXXX:XXXX:XXXX:XXXX::1
      nameservers:
        addresses: [1.1.1.1, 1.0.0.1]

here a bridged example with vlans

network:
  version: 2
  renderer: networkd
  ethernets:
    enp5s0f0:
      dhcp4: no
      dhcp6: no
  vlans:
    vlan302:
      id: 302
      link: enp5s0f0
      dhcp4: no
      dhcp6: no
    vlan412:
      id: 412
      link: enp5s0f0
      dhcp4: no
      dhcp6: no
  bridges:
    br0:
      interfaces: [vlan302]
      dhcp4: no
      dhcp6: no
      addresses: [1.1.1.2/24]
      gateway4: 1.1.1.1
      nameservers:
        addresses: [1.1.1.1, 1.0.0.1]
    br1:
      interfaces: [vlan412]
      dhcp4: no
      dhcp6: no

the spaces are mandatory!

Have fun

Migrate Windows 10 from HDD to smaller SSD with clonezilla

Hi

Here the Howto as always
You might need some advanced knowledge with parted and clonezilla.

1)
Create a Backup of the working system. Just in case πŸ™‚

Shrink your HDD via windows disk management to fit to your SSD size.

If windows won’t let it shrink to the preferred size you have to disable hibernate, pagefile, and system protection temporary.
You might also want to remove the windows update backups with windows disk clean utility.

Make with clonezilla a full disk backup.

I like to disconnect the old HDD when backup with clonezilla has finished. Just in case πŸ™‚

2)
When clonezilla livecd has started, open an new terminal and create a new partition table with parted according to you old partition table on the HDD (GPT, MSDOS, Boot Partition, Windows Partition and Windows recovery Partition) on your fresh SSD.
The new partitions must fit at least the size of your partitions from backup. You shrunk them down before. Bigger no problem.
In case of MSDOS don’t forget to set the boot flag.

3)
Start clonezilla and choose partition backup. Restore full disk won’t work, in most cases the SSD is smaller than your HDD.

4)
In case of MSDOS partition table write MBR to the new SSD:
Write MBR with Windows Recovery from install media or ms-sys

Windows Recovery Disk:

bootrec /fixmbr
bootrec /rebuildbcd
bootrec /fixboot

Linux:

ms-sys -7 /dev/sdx
ms-sys -n /dev/sdx1

If you have issues with bootrec

bcdboot sourcedriveletter:\windows /s destrinationdriveletter:

Have fun!

nut upsd usbhid-ups segfault error 4 in libc-2.23.so APC Back-UPS RS 500 ubuntu 16.04

Hi

Following error when I tried to start upsd

kernel: [588428.295337] usbhid-ups[15008]: segfault at 0 ip 00007f7fcad3f7d4 sp 00007ffd824c5178 error 4 in libc-2.23.so[7f7fcabf0000+1bf000]

The driver seems to start fine with (with root privileges):

upsdrvctl -u root start

I’ve not further investigated. Just edit the Initscript:

vim /etc/init.d/nut-server

UPSD_OPTIONS="-u root"

line 79

! $upsdrvctl -u root start >/dev/null 2>&1 && \

Have fun!