They’re using HOW MUCH space?

This assumes a convention where databases are named after the responsible party with an underscore and a version number or description.  It also assumes that running this script is faster than remoting to the server and looking at the directory.  😉

declare @dbname varchar(255),
@CurrentRow int,
@RowsToProcess int,
@SQL nvarchar(max),
@size float

RowID int not null primary key identity(1,1),
dbname varchar(255),
owner varchar(80),
size float

insert into #dbs (dbname) select name from sys.databases where name not in ('master','msdb','model','tempdb')
set @RowsToProcess=@@ROWCOUNT

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
SET @CurrentRow=@CurrentRow+1
FROM #dbs
WHERE RowID=@CurrentRow
set @SQL = 'update #dbs set size=(SELECT sum(size) FROM ' + @dbname + '.sys.database_files) where dbname=''' + @dbname + ''''
set @SQL = 'update #dbs set owner=(SELECT CASE WHEN CHARINDEX(''_'', dbname) > 0 THEN LEFT(dbname, CHARINDEX(''_'', dbname)-1) ELSE dbname END AS Owner FROM #dbs where dbname=''' + @dbname + ''') where dbname=''' + @dbname + ''''

select owner, COUNT(owner) as DBCount, ((((SUM(size) * 8192)/1024)/1024)/1024) as TotalGB
from #dbs group by owner

drop table #dbs

Comments Off on They’re using HOW MUCH space?

Filed under scripting

SFTP – copy smallest files first

I needed to SFTP a bunch of files ranging in size from 40 meg to 130 gig.  The default behavior of “mput *” is to copy the files in alphabetical order, but what happens then is that all the smaller files pile up behind the large ones.  This is bad because there’s a time limit and I want to copy as many as possible before the time limit passes.  The solution?

Pre-sort them by size, smallest to largest.

These are encrypted database backups, so the script requires a SQL Server instance name.  You can, of course, edit that behavior.

This depends upon PuTTY and Pageant.  Create an SSH profile with key-based authentication and use Pageant to handle the key as described in the link.  My SSH profile in this case is named “Atlanta” because that’s where the files are being copied.

This also assumes that you have directories in the SFTP server named with the pattern servername-instance because I’m running multiple streams.  Also, the last thing the database backup does is copy a file called “flag.txt” to the backup directory so the script knows when backups are done and can start copying.

set instance=%~1
if %instance%z==z goto :_help

set flag=”f:\full backups\%instance%\flag.txt”


IF EXIST %flag% (
GOTO file_found
) else (
GOTO loop


dir “f:\full backups\%instance%” /os /b /a-d-h > c:\temp\%instance%.txt

echo lcd “f:\full backups\%instance%” >> c:\temp\%instance%-control.txt
echo cd %computername%-%instance% >> c:\temp\%instance%-control.txt

FOR /F %%L IN (c:\temp\%instance%.txt) DO (
echo put %%L >> c:\temp\%instance%-control.txt

echo quit >> c:\temp\%instance%-control.txt

“c:\Program Files (x86)\PuTTY\psftp.exe” Atlanta -b c:\temp\%instance%-control.txt

del c:\temp\%instance%-control.txt
del c:\temp\%instance%.txt

goto :_end

@echo Please specify an instance.


Linked as a separate file because smart quotes are not smart.

Comments Off on SFTP – copy smallest files first

Filed under scripting

Fun with PXE

At one of my previous positions, I was in charge of the PXE server.  I like PXE, so I set up a PXE server here, too.

It was a lot easier at my current place of employment, because I had access to the routers and the DHCP server.  It ended up being a straightforward Windows Deployment Services install.  I’m not only serving up boot and install images but also Acronis Recovery Media.  🙂

Alas, at the previous place, I was not allowed access to routers or DHCP.  This meant I had a lot of ‘splainin’ to do, and apparently it broke some of the network guys’ brains, so…

PXE uses DHCP (option 60). You can run PXE on a network with separate PXE and DHCP servers, but PXE does need to listen on DHCP.

It kind of goes like this:

PXE Client:  DHCPREQUEST, p.s. I'm a PXE Client.
DHCP Server:  DHCPACK, Here's your IP! p.s., PXE over there ->
PXE Server:  DHCPACK, Hello, I'll be your PXE server today. 

Note:  That second DHCPACK does not contain an IP number.  It only contains PXE information. (So, dude who kept demanding that I look at the scope of the IP addresses I was handing out?  You fail Reading/Listening Comprehension 101.)

So you’re going to need to make changes to the DHCP server (to specify to PXE clients where to get PXE services) and possibly the routers as well (to specify the PXE server as a DHCP helper).  You also need to make sure that the PXE server is answering on the DHCP ports (open them in the firewall).  If you have separate PXE and DHCP servers, the PXE server doesn’t need to serve up IP numbers, but it does need to answer (DHCPACK, option 60).

This is a pretty good description of how someone else got it working.

1 Comment

Filed under sysadmin

Nagios Event Handlers on Windows

Nagios event handlers are WHERE IT’S AT, BABY, YEAH!  There are some services that I can just automagically restart without any problems.  (WSUS, SQL Agent, etc.) This way, instead of notifying me, Nagios can just fix the problem for me and We Need Never Know.

These instructions assume I’m running NSClient++.

The script is

@echo off
net start %1
@exit 0

(This is kept intentionally minimal so it’ll be reusable.)  I’m referring to this in nsclient.ini, under the “; A list of scripts available to run from the CheckExternalScripts module. Syntax is: <command>=<script> <arguments>” header.

restartwsus=scripts\runcmd.bat wsusservice

On the Nagios server, I’ve defined the check in commands.cfg as:

define command{
 command_name restartwsus
 command_line /usr/lib/nagios/plugins/check_nrpe -H '$HOSTADDRESS$' -c restartwsus

and in the service definition as:

define service{
        use                     generic-service
        host_name               wsusserver
        service_description     WSUS
        contacts                me
        notification_options    w,c,r
        notification_period     24x7
        notification_interval   0
        check_command           check_nt!SERVICESTATE!-d SHOWALL -l WsusService
        event_handler           restartwsus

It looks like this is copy and paste-able.

Comments Off on Nagios Event Handlers on Windows

Filed under monitoring

A script for migrating to a new server

I stripped out a piece of company-specific logic, but…

List the databases you want to move in a file named “control.txt.”  (To migrate the entire server, paste the output of “select name from sys.databases where name not in (‘master’, ‘msdb’, ‘model’, ‘tempdb’).”)

Replace the example values in the below script with real information:


set controlfile=control.txt

set newipmdf=\\newserver\g$
set newipldf=\\newserver\e$
set oldserver=oldserver\Prod1
set oldmdfpath=d:\prod1
set newmdfpath=g:\data
set copymdfpath=m:\data
set newserver=newserver
set oldlogpath=e:\prod1
set newlogpath=e:\log
set copylogpath=l:\log
set movedmdfpath=%oldmdfpath%\moved
set movedldfpath=%oldlogpath%\moved

mkdir %movedmdfpath%
mkdir %movedldfpath%

net use m: %newipmdf%
net use l: %newipldf%

FOR /F %%L IN (%controlfile%%) DO (
  SET "line=%%L"
  ECHO !line!
  sqlcmd -E -S!oldserver! -Q"EXEC master.dbo.sp_detach_db @dbname = N'!line!'"
  copy "!oldmdfpath!\!line!.mdf" !copymdfpath!
  copy "!oldlogpath!\!line!_log.ldf" !copylogpath!
  sqlcmd -E -S!newserver! -Q"CREATE DATABASE [!line!] ON ( FILENAME = N'!newmdfpath!\!line!.mdf' ),( FILENAME = N'!newlogpath!\!line!_log.ldf' ) FOR ATTACH"
  move "!oldmdfpath!\!line!.mdf" !movedmdfpath!
  move "!oldlogpath!\!line!_log.ldf" !movedldfpath!

net use m: /z
net use l: /z

(The redacted data was a sharding-related item–updating a metadata table with the new location.  You can, of course, add your own logic after the attach, if needed.)

As always, I’ve included a downloadable version here.

Comments Off on A script for migrating to a new server

Filed under scripting

Nagios Twitter Notifications – working again!


Twitter changed their authentication, and my old Twitter notifications (based on Ed Voncken’s work) seized up and failed.  I had to update the python tweepy library to get them to work.

pip install tweepy –upgrade

And they’re back!

I love Twitter notifications, BTW.  <3

Comments Off on Nagios Twitter Notifications – working again!

Filed under monitoring

Fun with VLFs

Virtual Log Files.  Your database’s log file is made up of one or more virtual log files.

Our databases have too many. Basically, our LDFs are fragmented.

What to do about this?  Well, Dave Levy has a script to reduce the number of VLFs, but it’s to run against a single database.  I have over 8000 databases.  Yeah.  That’s going to have to be scripted.  I know, job security and all, but that’s crazy.

Also, as a Sysadmin/Production DBA, I like scripts.  Consistent, repeatable.

I’m going to strip out some company-specific code that won’t do you any good, but if there’s a particular set of databases you want to target, list them in a maintenance table and work from that, not sys.databases.  (That’s why the check to see if the database really exists code is there.)  You’ll be able to tell that I shamelessly pasted bits and pieces of Dave Levy’s script.  For control, there’s a @maxlogcount variable that you can set crazy high.  Also, you can comment out the EXEC sp_executesql bits and uncomment the print bits for test/debug/just show me what you were going to do purposes.

declare @RowsToProcess int,
@CurrentRow int = 0,
@DBexists int,
@DBname varchar(80),
@logcount int,
@SQL nvarchar(max),
@file_name sysname,
@file_size int,
@shrink_command nvarchar(max),
@alter_command nvarchar(max),
@maxlogcount int

set @maxlogcount=100

CREATE TABLE #vlfcount(RowID int not null primary key identity(1,1), dbname varchar(80), vlfcount int, file_name varchar(255), file_size int )

INSERT into #vlfcount (dbname) SELECT name FROM sys.databases order by name
SET @RowsToProcess=@@ROWCOUNT

create table #scratch (Field tinyint, FileSize bigint, StartOffset bigint, FSeqNo int, Status tinyint, Parity tinyint, CreateLSN numeric(25,0))

WHILE @CurrentRow<@RowsToProcess
SET @CurrentRow=@CurrentRow+1
FROM #vlfcount
WHERE RowID=@CurrentRow
select @DBexists = @@ROWCOUNT from sys.databases where name=@DBname
if @DBexists = 1
set @SQL=’insert into #scratch exec (”dbcc loginfo (””’+@DBname+””’)”)’
–print @SQL
set @SQL=’update #vlfcount set vlfcount=(select count(field) from #scratch) where DBName=”’ + @DBName + ””
–print @SQL
set @SQL = ‘update #vlfcount set file_name=(select name FROM ‘ + @DBName + ‘.sys.database_files WHERE type_desc = ”log”) where DBName=”’ + @DBName + ””
–print @SQL
set @SQL = ‘update #vlfcount set file_size=(select (size / 128) FROM ‘ + @DBName + ‘.sys.database_files WHERE type_desc = ”log”) where DBName=”’ + @DBName + ””
–print @SQL
delete from #scratch
set @DBexists=0

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
SET @CurrentRow=@CurrentRow+1
FROM #vlfcount
WHERE RowID=@CurrentRow
if @logcount > @maxlogcount
SELECT @shrink_command = ‘Use ‘ + @DBName + ‘;DBCC SHRINKFILE (N”’ + @file_name + ”’ , 0, TRUNCATEONLY)’
–PRINT @shrink_command
EXEC sp_executesql @shrink_command

SELECT @shrink_command = ‘Use ‘ + @DBName + ‘;DBCC SHRINKFILE (N”’ + @file_name + ”’ , 0)’
–PRINT @shrink_command
EXEC sp_executesql @shrink_command

SELECT @alter_command = ‘ALTER DATABASE [‘ + @DBname + ‘] MODIFY FILE (NAME = N”’ + @file_name + ”’, SIZE = ‘ + CAST(@file_size AS nvarchar) + ‘MB)’
–PRINT @alter_command
EXEC sp_executesql @alter_command

select MAX(vlfcount) as worst, AVG(vlfcount) as average from #vlfcount
select dbname, vlfcount from #vlfcount where vlfcount > @maxlogcount

drop table #scratch
drop table #vlfcount

As always, I’ve included a downloadable link here: Download.  Because smart quotes are not smart.

Comments Off on Fun with VLFs

Filed under scripting

Jabber Nagios Notifications – Working Again!

I like using non-email notifications, especially when monitoring, you know, email.  So I have notifications going out via twitter and google talk.  Of the three, the order of speediness is:

  1. Google Talk
  2. Twitter
  3. Email

So I was really sad when the google talk notifications stopped working late last week.  It took awhile for me to have time to fix them, though, and basically I just found a google groups post telling me what to do.  Namely, this at the top:

use IO::Socket::SSL;
no warnings ‘redefine’;
my $old_connect_SSL = \&IO::Socket::SSL::connect_SSL;
*IO::Socket::SSL::connect_SSL = sub {
my $sock = $_[0];
${*$sock}{_SSL_arguments}{SSL_cipher_list} = ‘RC4-MD5’;
goto $old_connect_SSL;

Merci beaucoups à Cédric Bouvier pour la correction!


Filed under monitoring

Goodbye, Blink.

I used to do web development. In Cold Fusion, a lot of the time. Cold Fusion is a language with tags that usually start with CF, like “<cf_query>”.

We had a customer who kept asking for the text to be bright red, huge, and blinking. (Not for the entire site, but for special words and phrases like “SALE!” and “ALL ITEMS 75% OFF!” and “YOU CAN’T GET A CHEAPER PRICE ANYWHERE ELSE ON THE INTERNET GUARANTEED!”) He did it often enough that we joked about writing a custom tag, <cf_ugly>.

Pronounced “see fugly.”


Boss: Yeah, [Name] likes your changes, but he wants you to wrap the text about the promotion in a see fugly tag.
Dev: DAMMIT!!!! *goes to edit*

See fugly.  See fugly stop.  Stop, fugly, stop.

[Link:  Firefox May Drop Support for <blink> Tags, Finally.]

Comments Off on Goodbye, Blink.

Filed under web

I am a winner!

I went to the Atlanta PowerShell Users Group tonight.  The topic was tips and tricks.  My trick was this script, which task scheduler reads to me in the morning. Assuming you use Hiveminder–and you should, because it’s awesome–you only have to edit the RSS locations for your location and for your to do list.

$weatherurl = "[yahoo weather RSS and city code]"
$hiveminderurl = "[hiveminder rss url here]"

You’ll need your yahoo weather city code–for example, this is Alpharetta, GA–and the RSS feed of your hiveminder to-do list for today.

I won a copy of PowerShell in Depth:  An Administrator’s Guide, by Don Jones, Richard Siddaway, and Jeffery Hicks.  Awesome!


Comments Off on I am a winner!

Filed under geekiness, powershell, scripting