back to article Junior dev decides to clear space for brewing boss, doesn't know what 'LDF' is, sooo...

The days are drawing in and the mornings are getting darker – so why not take a dose of Who, Me? to help lighten up your day. In this week’s instalment of El Reg’s weekly column filled with embarrassing reader cock-ups, we meet “Bert”, who was working at a large brewing firm at the point in question. “I was a junior dev as …

Silver badge

Re: Killing a database?

There are two types of people in the world, those who think they are paranoid enough and those who know they are not.

13
0
Reply
Silver badge

Re: Killing a database?

USB drives? Shit, talk about overkill! We moved that kind of data around on floppies.

Consider: 80 characters per card (72 for data in my example). 2000 cards per box. Maybe ten boxes on my handtruck. That's 1,440,000 bytes of data. How convenient ...

6
0
Reply
Silver badge

Re: Killing a database?

String, Dr.S? We were lucky if the boxes had lids!

(That's not a "Four Yorkshiremen" type comment, BTW, I'm serious.)

3
0
Reply
Silver badge

Re: Killing a database?

> Consider: 80 characters per card (72 for data in my example). 2000 cards per box. Maybe ten boxes on my handtruck. That's 1,440,000 bytes of data.

Wow. That'd almost hold the minified js files that a typical modern web app needs to launch.

5
0
Reply
Silver badge
Gimp

Re: Killing a database?

...my belt-and-suspenders (belt-and-braces to you Brits) approach...

No it's also belt-and-suspenders for me in the UK as well.

Wait, you mean at work don't you.

It's just PPE-->

0
0
Reply
Anonymous Coward

Something is missing about this tale

The .LDF file is locked by the SQL server process, it is not possible to simply right click and delete

6
2
Reply
Silver badge

Re: Something is missing about this tale

Whilst he was using Windows 2000 to VNC into the SQL server it doesn't say that the server itself was running Win 2000.

4
0
Reply

Re: Something is missing about this tale

It wouldn't have been running anything other that Windows if it ws MS SQL Server - NT4 and up

1
0
Reply
Anonymous Coward

Re: Something is missing about this tale

The .LDF file is locked by the SQL server process, it is not possible to simply right click and delete

Correction - shouldn't be possible.

File systems, especially back then, were rather nasty things that took bytes quite literally - often biting chunks out of your data. What should've been impossible and what was in fact actually quite possible at the worst possible time were the same thing :(

1
1
Reply
Silver badge

Miss(ed)-Step

In the 90s I was working on Hyperion Essbase, or Arbor Essbase as it was then known, one of the first OLAP systems.

It had a slight problem. Recalculating the Hypercube took <insert swear word here> ages if it was already full - in that recalculating a full cube would take, for example, 24 hours, whereas just loading the bottom row data it would take 1 - 2 hours.

As the corporate reporting database needed to be recalculated several times a day, obviously re-calculating the full database was out of the question (and this was on a "high end" server, with dual Pentium Pro processors and "bags" or RAM).

So, the "standard" procedure was:

1) Export bottom rows

2) Clear the database

3) Import bottom rows

4) Re-calculate

Being new on the project, yours truly promptly forgot step 1! Yikes!

A quick discussion with my colleague didn't help ease my mind. His advice was to use the backup from the previous run and blame the missing data on (l)user error, as opposed to administrator error! :-O

Instead, I went to the client and informed them that I had borked the export and that we would try and re-assemble the database from the previous exports and the transaction log. It took about half an hour longer than usual and only 2 transactions were missing.

I actually got brownie points from the Finance Manager for being honest...

32
0
Reply
Anonymous Coward

Re: Miss(ed)-Step

>His advice was to use the backup from the previous run and blame the missing data on (l)user error, as opposed to administrator error! :-O

Aha!!

2
0
Reply
Silver badge

Shrink an LDF file?

Back when I was less old, and less bitter and twisted, I remember asking the boss how to shrink an LDF file on an older version of MS SQL.

His response was that I should stop the server instance and delete the LDF file, then restart the service, and it should create a new smaller one...

So I did...

Good thing I copied the LDF file to another location, 'cos when I tried restarting the service it wouldn't come back up, and it definitely didn't create a new fresh transaction log like he said it would!

26
0
Reply

If MS SQL server is either not backed up (shudder) or incompetently backed up, then setting recovery mode to simple at least saves you from an ever growing log file, though I recommend a competent backup as the proper solution....

13
0
Reply
Silver badge

This ^^^ is the answer, for those of you who cannot for whatever reason do a full backup.

Change the database mode to simple, then run the shrink command on the log file, then change the mode back.

What you should do is a full backup, but if we all had the resources to stick to best practises all the time, then we'd all be a lot happier I expect.

13
0
Reply
Anonymous Coward

One problem are sites the insist on cold backups of database files, instead of going through database backup utilities, or specific backup agents that knows about database backup APIs (because lack of knowledge, and costs...)

So maybe they just stop the instance, backup (maybe just the data file...), restart it but many of the "cleanups" a proper backup would do are bypassed.

There are good reasons while SQL Server is by default setup in "backup mode", or Oracle in "ARCHIVELOG" one. They are safer, but will fill your disks also if you don't know what you're doing,

and it is true that many applications that install an instance behind your back (i.e. VMWare....) won't tell you what to do if you're not an experienced enough DBA with that kind of engine.

3
0
Reply

errr you can do a transaction log backup that just simply truncates the log contents without doing an actual backup. No need to switch recovery model of the database.

0
0
Reply
Coat

FTFY

If we all had the resources to stick to best practises all the time, then we'd all be a lot poorer I expect.

There's money to be made cleaning up the mess after some short-sighted penny pinching!

2
0
Reply
Silver badge

Re: FTFY

I'm sick of messes. At my age, the object is to make a clean getaway. As I tell prospective new clients "It's my job to ensure we see as little of each other as possible".

10
0
Reply

I'm calling BS

I'm calling BS on this entire story. You can't delete the LDF file while SQL server is running as the file is in use and thus locked. If you delete it while SQL isn't running, SQL server just creates a new one when it starts.

4
9
Reply
Silver badge

Re: I'm calling BS

Have you ever tried? Hint: There are many versions of SQL Server and they run on many different versions of Windows. They didn't even fix date handling till 2008 IIRC.

16
1
Reply
Anonymous Coward

"...so I figured that, as well as updating the software, I'd free up some disk space"

ah yes, the well intentioned cockup. the day you learn not to do what's right, but what ur told.

best laid schemes o' mice and men... and all that.

8
1
Reply
Anonymous Coward

So the LDF file had 'gone for a Burton'

4
0
Reply

Back in the day

With SQL Server 6.5 I was diagnosing an issue that ultimately came down to SQL not having enough memory allocated to it. Back then you had to specify how much RAM you wanted it to use (I assume it's more clever now but I don't really know).

Anyway, it was running on NT4 and I set it to use half the available memory. I forget how much it had - 256mb or something - but completely forgot it was in 2Kb increments so setting it to 128Mb really meant 256Mb.

Net result was the machine booted, SQL started and immediately took all the available RAM and the whole thing blue screened. Obviously, this was whilst I was at the client site, whilst remote controlling their server which was in a data center elsewhere.

We had to get a technician out to put more RAM in the server in the end. They weren't impressed...

13
0
Reply

Re: Back in the day

Anyway, it was running on NT4 and I set it to use half the available memory. I forget how much it had - 256mb or something - but completely forgot it was in 2Kb increments so setting it to 128Mb really meant 256Mb.

Yes. Because memory was specified in 2K pages just like with Sybase SQL Server/Adaptive Server Enterprise, that MS SQL Server has its roots in.

Net result was the machine booted, SQL started and immediately took all the available RAM and the whole thing blue screened. Obviously, this was whilst I was at the client site, whilst remote controlling their server which was in a data center elsewhere.

We had to get a technician out to put more RAM in the server in the end. They weren't impressed...

Or you could have just started it in minimal single-user mode with -f command line option, and then used sp_configure to resize memory to more acceptable size.

Sybase on UNIX of course would be even easier as configuration is always written to a text file on filesystem, but I digress.

4
0
Reply
Silver badge
Coat

<obvious comment>

He could organise an SQL restore, but could he organise a piss-up?

4
0
Reply

The Great thing about SQL Server is how easy it is to set up and use with a little bit of knowledge. Unfortunately that's also its biggest problem. I've lost count of the number of databases I've seen where its set to full recovery model but no transaction log backup. Back when I first started I was very helpful and would set up the backup. These days I just send a warning email to the DBA... and then re-send it when the server falls over as its run out of space (I know I'm Evil, but in my defence, I find people learn far quicker from mistakes).

8
0
Reply

Beware of abnormally large files on root directory...

...specially if they match the amount of RAM memory.

Classical who me? here...

Things like hiberfil.sys, pagefile.sys on windows... some other OSes store the entire memory load of the OS in a ginormous file on root, that is used when you reboot to commit all the changes... pretty much like Windows, but with much more cryptic names. And no .sys extension to help you.

I heard of such classic instances of someone deleting these key files... and that system refusing to reboot. At all. Or when it did, it looked like something freshly installed, without the millions of patches, updates, and configs tuned to that machine... systems that upon startup are asking for keyboard layout and Time Zones... telltale clues that Very Bad Things Happened™.

I may have done that myself... when Windows was not adamant about keeping those files...

6
0
Reply
Silver badge
FAIL

Re: Beware of abnormally large files on root directory...

Advice about 25 years too late for younger me: don't delete /unix

8
0
Reply
Silver badge

Re: Beware of abnormally large files on root directory...

A friend deleted /vmunix on a largish Sun system back in ~1988 ... fortunately on a Saturday afternoon. Also fortunately, he had enough sense to call me before he started "fixing it". All was well come Monday morning, and I had free beer for the rest of the month.

1
1
Reply
Silver badge

Call me clueless, but is it

"Latter Day Fail?"

2
0
Reply

PSA

The system adminisrator, by default, knows nothing about databases.

If you assume otherwise you make an ass of yourself, not of the sysadmin.

That is all.

3
0
Reply
Bronze badge

Re: PSA

+1. In the classical period (1995-2005) being an enterprise DBA was pretty hard. You had to know your OS inside out, you had to understand how to do fast I/O and then you had to design the database, as in tune how tables should be laid out for maximal performance. Wasnae easy, cap'n.

These days, I'm not so sure (I'm out of the biz). Do Oracle admins still worry about tuning the SGA for example? Does anyone use raw block devices? Or is it all self-tuning and autonomous for all but the most esoteric setups?

4
0
Reply

Re: PSA

"Or is it all self-tuning and autonomous for all but the most esoteric setups?"

More or less.

You can get cheap all-flash storage arrays that can do 100,000 x more IOPS than you had access to during the classical period, and of course nobody runs databases against pentium 3s with only a few Mb of RAM...

So unless you have top tier requirements It's easy to just throw hardware at a problem to make poor design go away.

5
0
Reply

Re: PSA

You slacked off on the last statement there.

To clarify: throwing hardware at a problem helps hide poor designs. Only intelligent folks like us can make poor designs go away.

3
0
Reply
Silver badge
Flame

Re: PSA

"So unless you have top tier requirements It's easy to just throw hardware at a problem to make poor design go away."

This is so true. Can't count the number of places that have suggested we need bigger boxes to cope with the all the data Yet 10 minutes later you realise it is not just poor design, it's friggin' criminal.

System and user db's on the same disk. Data and log files on the same disk. and then of course the clincher it's all sitting on the OS drive.

6
0
Reply

Re: PSA

+1. In the classical period (1995-2005) being an enterprise DBA was pretty hard. You had to know your OS inside out, you had to understand how to do fast I/O and then you had to design the database, as in tune how tables should be laid out for maximal performance. Wasnae easy, cap'n.

Hear hear. I miss those days. You really ended up knowing the systems, both hardware and OS, better than most of the sysadmins.

Oracle tries to be clever and decide things for you. It does acceptably for most of the time. But not always. And being Oracle is buggy as hell. And it can get it really horribly wrong. It is also even worse piece of bloated crap than what it used to be.

All-flash or tiered is cheap so that takes care of I/O performance. Of course raw blocks weren't just for performance, it was also to bypass filesystem cache, after all you cache rows in memory anyway, but also for ensuring that writes that the database thinks have been written to disk have actually been written to disk.

Having said all that, even if everything is faster and more automatic, the data volumes and business requirements have increased too so there is still very much need for skilled DBAs. Don't forget lusers have not improved even if hardware and software may have done.

5
0
Reply
Bronze badge

How low can you go.

Stitching the bits to make a pass. Repeat after me. You can never have enough back-ups and never delete anything. #checkmate

3
0
Reply
Anonymous Coward

Once upon a time

A long time ago

In a company far, far away.....

My boss killed the company inventory control system by organizing it - all the different types of files were put in there own separate directories, all nice and tidy.

7
0
Reply
Anonymous Coward

1967

When I was 5, I spent the day at Dad's work, a multi-national market research company.

I was tasked with taking a loose, and rather large handful of sorted punch-cards to the computer room on another floor. All on my own.

I made it OK. If I hadn't, I'm sure supermarkets across the country would have stocked the wrong brand of beans on their shelves.

2
0
Reply
JLV
Silver badge

I was working as an account manager/do-everything-guy at the local branch of an up and coming software multinational. I was tech guy at core, but a dev, not a sysadmin.

A livid customer's IT manager contacted my boss about our app's database crashing on them. He had never liked us, we were chosen by the biz side over his objections, so his moment to gloat. Yes, his "DBA" had been backing up faithfully every day, but now they had lost a full day's work since this was a late afternoon crash. So he wanted our RDMS vendor to examine his database core dump. IMMEDIATELY and he had already notified his C-levels of the snafu.

Dragged in our local CTO - yes, we were that small, and we looked at the Gupta SQL database manual together.

- open at index

- Restore DB, page 35, yes.

- But, that's gonna lose them that full day. That's what they're bitching about.

- (5 minutes later) Wait, what's that Log Journal Replay thingy?

- OK, let's just call them and tell them to restore and then follow procedure at page 42 of manual to replay the day's transactions.

Never heard of Mr IT again, but the customer's business person told us he came off as a proper ass when it became obvious they had never attempted a recovery or even read up on how to do one. Remember how upper management was notified? It's amazing how some people are incompetent at even management games.

The biz guy was pretty happy, they only lost 15 minutes or so of data. First time I got an inkling of what an LDF was for ;-)

5
0
Reply
Silver badge

Pointless Shibboleth o' the Day

I am annoyed for no good reason when people refer to SQL Server, a Microsoft DBMS product, as "SQL", which is the name of a standard language for expressing relational database queries and other operations. I knew SQL. SQL was a friend of mine. And you, SQL Server, are not SQL.

I would like everyone to refrain forevermore from doing so, beginning immediately.

Thank you.

4
1
Reply

20+ years ago I was primary support for the state's ambulance dispatch system - 000/999/911 calls come in and are sent to the ambulance dispatcher to send a resource. For some reason we were doing an upgrade in the early morning (less emergency calls in case something goes wrong) and as a precaution my off-sider was on-site as well. It was a home-grown Pick system running on Pr1me minis with a 3GL green screen front end. The upgrade went fine, but while I was looking around the system I saw a file that I thought wasn't needed so I deleted it. Immediate chaos - everything stopped. Mad scramble for paper and pencil so emergency calls could be recorded and ambulances dispatched. Fortunately we had a live backup system so after a few minutes of panic, we copied the missing file from the backup system and restarted and everything's back to normal.

Always got by off-sider to look over my shoulder after that and he didn't like playing with the system.

2
0
Reply

Page:

POST COMMENT House rules

Not a member of The Register? Create a new account here.

  • Enter your comment

  • Add an icon

Anonymous cowards cannot choose their icon

Biting the hand that feeds IT © 1998–2018