Tag Archives: database

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

Get Austrian subnets from Ripe database

Hi
Need from the ripe database the subnets in cidr of some specific country?
Here my nooby approach to get this done.

wget ftp://ftp.ripe.net/pub/stats/ripencc/delegated-ripencc-latest
vim ripeconvert.pl

Paste the following code:

#!/usr/bin/perl

%subnet =();
$subnet{'4'} = "30";
$subnet{'8'} = "29";
$subnet{'16'} = "28";
$subnet{'32'} = "27";
$subnet{'64'} = "26";
$subnet{'128'} = "25";
$subnet{'256'} = "24";
$subnet{'512'} = "23";
$subnet{'1024'} = "22";
$subnet{'2048'} = "21";
$subnet{'4096'} = "20";
$subnet{'8192'} = "19";
$subnet{'16384'} = "18";
$subnet{'32768'} = "17";
$subnet{'65536'} = "16";
$subnet{'131072'} = "15";
$subnet{'262144'} = "14";
$subnet{'524288'} = "13";
$subnet{'1048576'} = "12";
#special ripe database
$subnet{'768'} = "22";
$subnet{'1280'} = "21";
$subnet{'1536'} = "21";
$subnet{'2560'} = "20";
$subnet{'2816'} = "20";
$subnet{'3072'} = "20";
$subnet{'9216'} = "18";
$subnet{'12288'} = "18";
$subnet{'13312'} = "18";

open(INFO,"<delegated-ripencc-latest");
        @ripe = <INFO>;
close(INFO);

sort(@ripe);

foreach $data (@ripe) {
    chomp($data);
    if ( $data =~ /ipv4/ && $data =~ /AT/ ) {
        @sdata = split('\|',$data);
        print @sdata[3]."\/".$subnet{"@sdata[4]"}."\n";
    }
    if ( $data =~ /ipv6/ && $data =~ /AT/ ) {
        @sdata = split('\|',$data);
        print @sdata[3]."\/".@sdata[4]."\n";
    }
}
perl ripeconvert.pl

Have fun!

sogo auth with mysql and mysql(view) against ispconfig database

hi

fist step let mysql listen on: 0.0.0.0
therefore verify your /etc/mysql/my.cnf

cat /etc/mysql/my.cnf | grep bind
#bind-address		= 127.0.0.1

(should be done @ispconfig install)

create a mysql database and a user and grant all privileges to that user

CREATE USER 'sogo'@'1.1.1.1' IDENTIFIED BY  '***';
GRANT USAGE ON * . * TO  'sogo'@'1.1.1.1' IDENTIFIED BY  '***' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
CREATE DATABASE IF NOT EXISTS  `sogo` ;
GRANT ALL PRIVILEGES ON  `sogo` . * TO  'sogo'@'1.1.1.1;

use mysql database as root and create special view

use sogo;
CREATE VIEW sogo_auth_view AS
SELECT  email AS c_uid,
             email AS c_name,
             password AS c_password,
             name AS c_cn,
             email AS mail
FROM dbispconfig.mail_user
WHERE disableimap='n' AND postfix ='y';

in your sogo config set the following lines:

<key>userPasswordAlgorithm</key>
<string>crypt</string>
<key>viewURL</key>
<string>mysql://sogo:yourpass@1.1.1.2:3306/sogo/sogo_auth_view</string>

Have Fun!