Monday, March 22, 2010

Deleting millions of rows from SQL

Occasionally you can end up with very large databases that you need to purge old data from. Unfortunatly you cant delete all the old data in 1 chunk since it will cause you SQL server to stop serviceing anything else (and potentially fill up your logs). Below is a little script I used to delete small chunks of data quickly and efficiently.


while 1 = 1
begin

delete top ( 1000 )
from event_33
where TimeGenerated< getdate()-90

if @@rowcount = 0 BREAK

end

Friday, March 19, 2010

Reporting on space used in a SQL database

I had the need today to look at a database and quickly identify which tables were taking up the most space. Since there are over 400 tables, doing this by hand wasn't going to work. Below is the TSQL script I created based on the sp_spaceused stored procedure to catalog the data and output it into a result set.


declare @TableName varchar(100)
declare @output table([name] varchar(100), [rows] int, [reserved] varchar(50), [data] varchar(50), [index_size] varchar(50), [unused] varchar(50))
declare TableNames CURSOR FOR
select name from sys.tables order by 1
OPEN TableNames

FETCH NEXT FROM TableNames
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
insert into @output exec sp_spaceused @TableName
FETCH NEXT FROM TableNames
INTO @TableName
END

close TableNames
deallocate TableNames
select * from @output

Monday, March 08, 2010

Removing old hardware when P2V'ing systems

  1. Click Start, click Run, type cmd.exe, and then press ENTER.
  2. Type set devmgr_show_nonpresent_devices=1, and then press ENTER.
  3. Type Start DEVMGMT.MSC, and then press ENTER.
  4. Click View, and then click Show Hidden Devices.
  5. Expand the Network Adapters tree.
  6. Right-click the dimmed network adapter, and then click Uninstall.

Monday, March 01, 2010

Renaming a Redhat system

Occasionally you need to rename a server, and in Linux this could be very difficult (depending on the flavor you are running). Here are the easy steps to rename a Redhat system.
  1. Edit /etc/sysconfig/network
  2. Change the line that says HOSTNAME
  3. Reboot

VMware - No volume groups found

I recently P2V'd several RHEL and RHAS servers which failed to boot when powered on. They would begin booting and then report "No volume groups found" and "Kernel panic - not syncing: Attempted to kill init!". After a lot of searching, I found the following link with the key information: http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1002402

Resolution steps:
  1. Boot the VM with a rescue CD
  2. Type chroot /mnt/sysimage
  3. Make sure there are no references to /dev/hda in the following files
    • /etc/fstab
    • /boot/grub/grub.conf
    • /boot/grub/device.map
  4. Type grub-install --recheck /dev/sda
  5. Delete any files from /etc/modules.conf
  6. Edit /etc/modprobe.conf
    • change alias ethx <module> to alias ethx pcnet32
    • change alias scsi_hostadapterx <module> to alias scsi_hostadapterx mptscsih
    • change alias scsi_hostadapter <module> to alias scsi_hostadapter mptbase
  7. Type mkinitrd -v -f /boot/initrd-2.6.9-55.ELsmp.img 2.6.9-55.ELsmp (repeat for each initrd file)
  8. Reboot