Monthly Archives: May 2013

“Digital Death”

The WisCON panel description:

Who gets your ebooks when you die? Your Twitter feed? The baby book that mostly exists on LJ? Do you have an estate plan for all these intangible but valuable assets? When you go, do you want your pages taken down or kept up for all time? Who do you trust to preserve or annihilate your online presence? The legal status of digital media is still a little fuzzy. With more of us and our parents moving that direction, we should think about this not just for ourselves, but our elders. What is the digital equivalent of inheriting grandad’s books, or is it even possible now? Join the discussion on legacies, files, and virtual tombstones.

Yeah, I was on that 8am panel.  😉  With another sysadmin, mind you!  The two sysadmins had two basic approaches to the topic:

  • Love your data.  Cuddle your data.  Back up your data.
  • Embrace bit rot.  All things die, including your data.  Don’t get attached to data, because Buddha says attachment leads to suffering.

I’ll open with my final comment, which is that this is not for you.  It’s a form of consideration for your survivors, and they’ll decide what’s valuable or not in the end.

What if your family is hostile, and doesn’t see the value in what you consider valuable?  Well, if you disagree strenuously, make your works public domain and put them online.  I put a lot of family photos on Flickr, Creative Commons Attributions No Derivatives so my family could download any of them without contortions.  I also had a friend at one point who died, and her mother found out she wrote fan fiction with same-sex romances in it, and her mother embraced that as part of who her daughter was.  (The moderator suggested that “I love my dead slash-writing daughter!” was a whole other possible panel topic.)  That could have gone in a completely different direction, though.

Frankly, I’m not really that concerned about my ebooks, movies, music, etc.  I’m more concerned about my facebook/twitter/personal websites, particularly the obituary sites I put up for my parents.  So my greatest concern with that is the technical know-how associated with maintaining them.  I’d rather they be left up, though, because I had another friend who was sick but didn’t intend to die, and she pre-scheduled a lot of WordPress posts that were tied to her Twitter and Facebook, and those started rolling in after she died.  “New music!” and a link to her playing.  Our first reaction was, “Wahhhhh,” but after a couple of months it was kind of nice.

On the opposite end, I have another friend who has an arrangement with a buddy that if he dies, the buddy will come in and wipe his hard drive so his Mom won’t find his porn or nekkid pictures.

Don’t count on encryption.  Even the best encryption standards get broken over time, so it’ll just make your hard drive a puzzle.  Then it turns out to be a case of whether or not someone thinks the drive is worth the effort.  (“Oh, it’s porn.  2D, how quaint!”)  Do think about the portability of your data (plain text, or HTML, which is plain text with a little markup that can be easily stripped out).

Obituary sites:  I wrote my Mom’s site because someone quoted my sister some exorbitant sum for an obituary online for some limited amount of time, and I said, “Bah, I’ll put it up on my own site.”  So I did, and it remains, and at least one friend of hers found out she was dead by googling her.  My father’s site… well.  My stepmother is not a native English speaker and was really upset, so the funeral home wrote the obituary and it didn’t mention that he had children.  My site for him outranks that other obituary in google–SEO REPRESENT.  However, antispam and upgrading the software is a thing, and does require technical know-how.  You might want a digital executor to be in charge of things like that.  In particular, the spambots will find the site and post 5000 links to “Buy Viagra!” if you let them, and that’ll feel like someone spraypainting on their headstones.  So be prepared.

Last but not least, I’ll reiterate that it’s not really about you.  If you believe in an afterlife, you’ll be in heaven and won’t be too concerned about your Facebook.  If you don’t, you won’t exist and won’t care about your Facebook.  It’s about being considerate to your survivors.

Comments Off on “Digital Death”

Filed under cons

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
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT
@DBname=dbname
FROM #vlfcount
WHERE RowID=@CurrentRow
select @DBexists = @@ROWCOUNT from sys.databases where name=@DBname
if @DBexists = 1
BEGIN
set @SQL=’insert into #scratch exec (”dbcc loginfo (””’+@DBname+””’)”)’
–print @SQL
EXEC SP_EXECUTESQL @SQL
set @SQL=’update #vlfcount set vlfcount=(select count(field) from #scratch) where DBName=”’ + @DBName + ””
–print @SQL
EXEC SP_EXECUTESQL @SQL
set @SQL = ‘update #vlfcount set file_name=(select name FROM ‘ + @DBName + ‘.sys.database_files WHERE type_desc = ”log”) where DBName=”’ + @DBName + ””
–print @SQL
EXEC SP_EXECUTESQL @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
EXEC SP_EXECUTESQL @SQL
delete from #scratch
set @DBexists=0
END
END

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT
@DBname=dbname,
@logcount=vlfcount,
@file_name=file_name,
@file_size=file_size
FROM #vlfcount
WHERE RowID=@CurrentRow
if @logcount > @maxlogcount
begin
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
END
END

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