MSSQL - Finding running queries

Hi all,

I've just written this SQL for MS SQL 2005. It returns running sql queries.


select CN.session_id, ST.text, ST.dbid, ES.last_request_start_time,
ES.last_request_end_time, ES.login_name, ES.status
from sys.dm_exec_connections CN
join sys.dm_exec_sessions ES ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) as ST
where ES.status = 'running'

Trev

DHCP DDNS and BIND part deux

Hi all,

Configuring DHCP on Ubuntu 10.04.

This is a part 2, but can equally be used as a part 1. Install dhcp3 using apt-get.

The first thing you'll see is that after you install it it'll fail to start. That is deliberate. Leave it down.

First thing to do is to tell DHCP what ethernet port to use.

nano /etc/defaults/dhcp3-server

under the interfaces line, in the speech marks, put your interface. For example "eth1"

Save and close that. That's the easy bit.

Now we need to setup the bits and pieces.

nano /etc/dhcp3/dhcpd.conf

Either edit this one, or back it up. Anyway, the lines you want are these:

ddns-update-style interim;
ignore client-updates;

and these (I've left the comment line in so you can see where in the file I did this):

# A slightly different configuration for an internal subnet.
subnet 192.168.1.0 netmask 255.255.255.0 {
range 192.168.1.70 192.168.1.150;
option domain-name-servers [your DNS server NAME];
option domain-name "[your domain name]";
option routers 192.168.1.254;
option broadcast-address 192.168.1.255;
default-lease-time 600;
max-lease-time 7200;
}

include "/etc/bind/rndc.key";

zone 1.168.192.in-addr.arpa. {
primary [your DNS server IP];
key "rndc-key";
}
zone newburytechinfo.co.uk. {
primary [your DNS server IP];
key "rndc-key";
}

These are fairly easy to read. The last 2 bits on the zone. This is to automatically update your DNS server with your clients IPs and names. The include line, includes the key from BIND.

Change the IP ranges and whatnot for your network, turn off your current DHCP server, start this one.

sudo service dhcp3 start

On your PC type this:

sudo dhclient

You should get an IP preconfigured with the right gateway, and your dns server as first. You should also be able to ping it from another box.

That's it.

Oh. Some messing about might be required.

Do these lines if you start having errors:

cd /etc/apparmor.d
nano usr.sbin.named

add this line:

/etc/bind/zones/** rw,

save and close.

nano usr.sbin.dhcpd3

add these lines (no its not pretty, this is a hack):

/etc/bind/ rw,
/etc/bind/** rw,

Save and close.

Run this:

sudo service apparmor restart

That's it. It should all work.

Thanks for reading

Trev

DHCP DDNS and BIND

Hi all,

I've just installed BIND and DHCP onto my server as my router wasn't dealing very well with either role.

Here's how I did it on, yes, Ubuntu 10.04.

First, install BIND and DHCP3 (You can either sudo or switch to root).

apt-get install bind9 && apt-get install dhcp3-server

Lets sort out bind9 first.

nano /etc/bind/named.conf

Make a quick note of the include statements. As you can see these files of config are added to keep things simple. Anyway, on the end of this file add these lines:

controls {
inet 127.0.0.1 allow {localhost; } keys { "rndc-key";};
};

This is for later so DHCP can update DNS. Save it and lets do the next one, it's one on the include list.

nano /etc/bind/named.conf.options

Stick your forwarders in here. Cunningly where it says forwarders. Here is my setting with my ISPs 2 DNS servers in:

forwarders {
87.194.255.154;
87.194.255.155;
};

Save it, lets do the next one. Again its in the includes list from earlier.

nano /etc/bind/named.conf.local

Bit more difficult this one. You need to create some zones. Mine isn't visible from the internet so bugger it, you can have mine.

zone "newburytechinfo.co.uk"{
type master;
file "/etc/bind/zones/newburytechinfo.co.uk.db";
allow-update { key "rndc-key"; };
//allow-update { localhost; localnets; };
notify yes;
};

See what I've done? All you have to do is replace my domain name, with your internal domain name. // = comment. This is while I was debugging. If you don't care what on your network can update your DNS use that line instead of the key line.

Next up, reverse DNS. Stay where you are and create a new zone like this(again this is mine):

zone "1.168.192.in-addr.arpa"{
type master;
file "/etc/bind/zones/rev.1.168.192.in-addr.arpa";
allow-update { key "rndc-key"; };
notify yes;
};

Ok? Nice and simple this. For the zone, if your computers IP is (for example) 192.168.1.15, chop the last number and dot off, then reverse it. Then add ".in-addr.arpa" on the end. If your computers IP is 10.242.192.24, it'll end up looking like this: "192.242.10.in-addr.arpa".

Obviously change the "file" line up there too to match the zone. Last bit, stick this line on the end:

include "/etc/bind/rndc.key";

Save it.

Ok, next bit. Create a zones directory:

mkdir /etc/bind/zones

go in it

cd /etc/bind/zones

You know the 2 filenames referenced above in that script I gave you?

create a new file with your domain name, like this:

nano newburytechinfo.co.uk.db

You'll notice this file is referenced above in the zone config. Now we need to put the A records in. These I will clean. Cut and paste this. Change example.com to your domain name and the IP for the A record on the last line.

$ORIGIN .
$TTL 38400 ; 10 hours 40 minutes
example.com IN SOA example.com. admin.example.com. (
2007031009 ; serial
28800 ; refresh (8 hours)
3600 ; retry (1 hour)
604800 ; expire (1 week)
38400 ; minimum (10 hours 40 minutes)
)
NS example.com.
MX 10 example.com.
$ORIGIN example.com.
mail A 192.168.1.1

Right. Oh, by the by, that last line? If you want to give another server a fixed DNS name, chuck it in, in the same way. Save and exit.

Bounce the bind9 service:

service bind9 restart

Want to do a quick test? On your PC type this:

nano /etc/resolv.conf

delete everything (or put it somewhere else, write it down, back up your file, I'm not your mum), and put this in:

nameserver [your DNS server IP]

save it, now try and go to a website.

Have a coffee, you've just done your first DNS server.

Tell you what, I'll do DHCP as a seperate article!

Thanks for reading

Trev

Kill all my processes! muhahaha!

Hi all,

This is an interesting command line a friend of mine wrote, I'm posting it here because its handy for killing database processes that are spinning and you can't sort it out nicely:

ps -ef |grep [someprocesseswiththesamename]|grep -Po "^\w+\s+\d+"| grep -Po "\d+"| sed "s/^/|kill -9 /" |sh

Say you have 50 apache2 services and apache is playing up so you want to kill all of them. (An example only...). You would do this:

ps -ef| grep apache2 |grep -Po "^\w+\s+\d+"|grep -Po "\d+"|sed "s/^/kill -9 /"|sh

Quick description:

ps -ef : List processes

grep apache2: Chops down the ps -ef to only show lines with apache2 in

grep -Po "^\w+\s+\d+" : Display just the name and PID

grep -Po "\d+" :Display just the PID

sed "s/^/kill -9 /" :build the command line, replaces the beginning of line char with "kill -9" the PID from the previous line gets appended.

sh: Pipe all that lot to the shell

Thanks for reading,

Trev

apt-get: Hash Sum mismatch

Hi All,

So, sometimes, just when you think that apt-cacher-ng is your amazing solution, or you have managed to get apt-get going through a proxy, you get hit by something like this:

Failed to fetch http://gb.archive.ubuntu.com/ubuntu/dists/lucid-updates/main/source/Sources.bz2 Hash Sum mismatch

That's no good is it? You try wget and that works fine, but apt just will not work. Here's how to fix it. Unfortunately I don't know what causes it. For me it has worked for ages and just broke one day and refused to work. Try the following (run as root):

touch /etc/apt/apt.conf.d/no-cache
nano /etc/apt/apt.conf.d/no-cache

type this line in and save it:

Acquire::http {No-Cache=True;};

The run:

apt-get update

This should now work with no problems.

Thanks for reading.

Trev