2 posts tagged “tobler”

View other items tagged “tobler:” Photos, Audio, Videos, Books or Links. View “tobler” on Vox.

SQL Server 2005 is the DEVIL

My job out in Denver is to be a DBA of a SQL Server 2005 database.  Seems simple enough, right?  All I do is load data in, run a small summary script, then make a new flat file out of the table created by the script.  EASY!  Not so much.


This particular table has been 2 weeks in the making.  One problem after another, after another.  First of all, I ran out of disk space on the original machine I was using.  Solution, add 2 1TB WD hard drives, and put them in RAID 0 for performance and to have 1 2TB drive.  Well, it helps when both drives are healthy and 1 doesn't have bad sectors out of the box!  Bastards!  Solution B) get another hard drive to replace the one that was DOA.  All is well in the world again.

Next problem, corrupt flat file sources.  First step, re-unarchive from the zip file given to me.  It could be a fluke in the decompression process.  No luck.  Put the word in for a new extract from the data source.  They don't understand why we need it and what we mean when we say "the data is corrupt".  One would thing that is pretty self explanatory, but I guess not.  I break it down to them on the hex level.  There is an ACK character in my data file.  How that POS got there, I have no clue, I can only guess a dropped packet or something.  Regardless, its F'ed up, I need a new one.
I get a new one, the dumb asses forgot to pull one of the 62 columns in the source table.  How the hell can you manage that?  Wouldn't you think they'd just recycle the script considering there were 136 flat files all with the same structure??  I guess thats asking too much.  Its too much like right.  Ask for yet another pull.  Finally, the short busses get it right! The data loads with out issue.

On to the next problem child.  Long story short on this one, there was a misc. column delimiter.  Only took me a week to find it.  Our fine friends in Redmond, with their cryptic ass error codes, and a seeming inability to add a few extra lines of code to SQL Server to tell me what row/record the error occurred on.  Again, I seem to ask too much.

All data loaded, hoo-ray!!  Now to run the summary script.  There are now ~800 million records in the table.  The data file and log file combine to +/- 500 gigs!  This is one massive table, and is going to take some time to run.  I can deal.
I execute the query, go for a smoke, five minutes later, I return to see there is an error.  The tempdb ran out of space so the query canceled.  Hrmm...C:\ is completely full.  Well then, easy solution, move the location of tempdb and templog.  

A little google action later, I find the T-SQL script to move tempdb and associated files.  We're in business!  The query looks like this:

USE master
GO
ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'd:\tempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'e:\templog.ldf')
GO
--Where 'd:' and 'e:' are the paths you want to relocate the files to.

I guess I missed the part at the top that says 'USE master'.  I guess I ran that inside of my HISTORY database.  The changes don't take effect until after SQL Server has been restarted.  I restart SQL Server, but it doesn't come back.  It kicks an error saying that it either can't find or doesn't have permission to change d:\tempdb.mdf.  I think this is an easy fix, I'll just copy the two from their original location.  Yeah, not so much.  I spend the next 4 hours learning about single user mode and sqlcmd and how to get the management studio to connect to a database in single user mode.  I try re-running the query, making sure to specify USE master this time.  The query completes, I restart SQL, and PRESTO! everything is working again.

I'm just super freaking happy that I have all that done now.  Talk about a weight being lifted.  I can now just cruise on through to the end of March, and maybe on to the first of May, assuming I don't get fired of the project.  That wouldn't exactly be a bad thing though.  This project is going right down the crapper, and I certainly wouldn't mind removing my name and presence from the abortion that is going to be delivered here.

Anywho...sorry for the long post.

Trust your Technolust!!

1 comment Tags: rant, sql server, hak5, sql server 2005, wess, tobler, hak5wess …

OMFG! A year and a half between posts!

HAI2U!

So its been a really freaking long time since the last time I posted!  My bad.

Never really been much of a blogger.  So here is the update in a nutshell:

Been working in Denver, CO for the last 2 months.  Fly out on Monday morning and fly back on Thursday afternoon.  Its a tough schedule, but you get used to it.  At least I get to be home on the weekends and get stuff done there.

Alanna (Ali) and I have out own apartment.  We got it in October.  Its our little crappy apartment, but its ours, and we've made it feel like home.  Me being me, and the handy man that I am, I've mounted my LCD to the wall, made some floating DVD shelves, ran my own cable line to the bedroom so we could watch TV in there, and just generally have modded the place to suite my needs.  Go figure, I can't have anything with out modifying it in some way!

Life has been pretty good, overall.  Staying busy for the most part.  The only thing that sucks is that with my schedule, I haven't been able to be as much of a part of the show as I'd like to.  It's really frustrating not being nearly as hands on as I used to.  I've been told that my contract is being extended, but I see no paper work supporting that, nor, at the very least, a scope of work to be done under said contract.  That'll be interesting to see how that unfolds.

Anyway, don't forget to check out the ShmooCon special edition of Hak.5 this week, complete with sound! w00t!

Later

2 comments Tags: hak5, hak.5, wess, tobler
Wess Tobler

About Me

Wess Tobler
United States
View my profile

Where the cool kids hang out

Neighborhood

Explore friends, family, friends & family, or entire neighborhood.

View my neighbors

Tags

View my tags

My Tumblr mini-blog