January 2013 - Paul S. Randal

Don’t forget your system health!

Posted on: January 24, 2013 1:20 pm

We all obsess with making our databases and our clients’ databases healthy and fabulous, but often we forget about looking after ourselves.

Over the last few years I’ve steadily ballooned 50lbs over the weight I was when I married Kimberly, let myself get stressed, black bags under my eyes, and an unhealthy liking for Tater-Tots, pizza, Tim’s Cascade chips (they are so good!), and coffee. I used to go hiking, now I’m too busy. I used to do tons of hobby stuff, now I’m too busy. I used to cook lots, now I’m too busy.

Of course, I blame my team for all of this… just kidding! I blame myself.

Working in I.T. means sitting at a computer for large amounts of time – a sedentary practice for which our bodies did not evolve. Not only does it mess up body posture, but it’s really easy to pile on the pounds when you get sucked in to working, working, working.

Well, not for me any more. I’ve decided to take the bull by the horns this year and do some performance tuning on myself. Some of my SQLskills colleagues are disgustingly fit, and I want to be too.

I’m currently on the last day of a 5-day fast-and-cleanse to flush all the toxins out of my body and give me a kick-start into being healthy. I’ve also lost 15lbs in the last six days. It hasn’t been as bad as people make out – no crazy cravings, no horrible cramps and nausea. My black bags have gone and I’m sleeping much better. If you’re interested, the cleanse I did is this one.

I also plan to change my eating habits – reducing portions and cutting out junk food. And starting to exercise again – not in a manic way, but walking, jogging, and hiking. And I’m not going to be online all the time. I think the 50lbs will come off this year.

As far as stress goes, I’m taking reading breaks during work days, and I’ve started learning meditation. Nothing secular, mystical, or any of that – just to allow my mind to clear. Check out 8 Minute Meditation.

My benchmark sucks and I want to go back to the baseline I had 5 years ago.

Why am I blogging about this? Two reasons:

I don’t want to be able go back to my evil ways – now it’s public, I *have to do it :-)
To encourage you to think about your health. A lot of us are overweight, no matter how much we kid ourselves, which is bad news in the long run.

I’m really looking forward to hiking again in the Cascades, and having more efficient breathing when diving later this year.

I hope some of you get inspired to take similar control of your health – no-one else is going to do it for you.

[Edit 1/25/13: final results are 16.6lbs lost, total 20.6lbs down from the start of the year.]

Posted in: Personal

19 Comments

Free 24-hour pass for Pluralsight online courses

By: Paul Randal

Posted on: January 18, 2013 9:15 am

Those nice folks at Pluralsight are offering everyone on the planet a free 24-hour pass to check out all their online courses (more than 400 of them), including all of the ones we’ve recorded. There are no strings attached and no credit-card registration required – they’re just celebrating raising a whopping $27.5 million in investment funding.

BUT you’ve only got until January 31^st to take advantage of their offer. This is a really cool deal – just think, you could blaze through a few of our courses one day over a weekend.

Our courses…

We’ve got 13 courses up there, totaling more than 45 hours of training (and more than 25 more planned for 2013):

SQL Server: Myths and Misconceptions – see here
SQL Server: Transact-SQL Basic Data Modification – see here
SQL Server: Transact-SQL Basic Data Retrieval – see here
SQL Server: Collecting and Analyzing Trace Data – see here
SQL Server: Introduction to Extended Events – see here
SQL Server: Performance Troubleshooting Using Wait Statistics – see here
SQL Server: Benchmarking and Baselining – see here
SQL Server: Deadlock Analysis and Prevention – see here
Understanding Server Hardware – see here
SQL Server: Logging, Recovery, and the Transaction Log – see here
SQL Server: Common Performance Issue Patterns – see here
SQL Server 2012: Installation and Configuration – see here
SQL Server: Troubleshooting Query Plan Quality Issues – see here

How to register?

You can register for the free pass here. [Edit 4/14/15: as it says above, the offer was only valid in January 2013…]

Enjoy!

Posted in: Pluralsight, Training

Be the first to Reply!

Database mirroring: avoiding ‘cannot obtain a LOCK resource’ problems

By: Paul Randal

Posted on: January 15, 2013 1:35 pm

This is an interesting case that came up recently, and that I’ve seen a few times before, but which I haven’t seen explained anywhere.

Using database mirroring, you may see 1204 and 1454 errors on the mirror server with no other databases or activity on the mirror server apart from database mirroring:

2013-01-15 12:00:00.410 spid29s Error: 1204, Severity: 19, State: 4.
2013-01-15 12:00:00.410 spid29s The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.
2013-01-15 12:00:00.430 spid29s Error: 1454, Severity: 16, State: 1.
2013-01-15 12:00:00.430 spid29s Database mirroring will be suspended. Server instance 'instancename' encountered error 1204, state 4, severity 19 when it was acting as a mirroring partner for database 'dbname'. The database mirroring partners might try to recover automatically from the error and resume the mirroring session. For more information, view the error log for additional error messages.

With no activity on the mirror server apart from mirroring, why are locks being taken?

The answer is that locks must be acquired when a transaction is rolled back on the mirror and is just how logging and recovery work. I discussed this some in my post Lock logging and fast recovery back in 2009 – locks are acquired during rollback to ensure that other transactions don’t prevent rollback from completing. The behavior is the same when a mirrored transaction rolls back – the locks need to be acquired on the mirror before the rollback can commence.

You can easily see this for yourself. Create a mirroring session and then a constant insert workload of small transactions. If you look at the locks on the mirror server using sys.dm_tran_locks, you’ll see no locks being acquired in the mirror database. Now create a transaction on the principal server that inserts 10,000 records. Roll it back and quickly examine the locks on the mirror server – you’ll see thousands of locks being acquired in the mirror database.

Why can we see 1204 errors with database mirroring?

Each lock structure takes up a small amount of memory and the more locks that are acquired and held at any one time, the more memory is required for the lock structures. On a memory-constrained system it’s possible to run out of lock memory and result in error 1204.

With database mirroring, if the principal server has a lot more memory than the mirror server, and there are a lot of large rollbacks occurring, or there a multiple mirroring sessions where rollbacks are occurring, it’s possible that the mirror server will run out of lock memory, resulting in errors 1204 and 1454 as above.

How can we stop 1204 errors occurring?

The easiest fix for this (and to hopefully prevent it happening) is to ensure that the mirror server has the same amount of memory as the principal server – so the principal should not be able to create more locks than the mirror server can accommodate. Even then, you may still see these errors if there is a lot of rollback activity across mirrored databases and it all happens to be replayed on the mirror server at the same time – in which case you need more memory on the mirror server than on the principal.

You need to also ensure that nothing else is using memory on the mirror server – e.g. using database snapshots, or any other activity apart from just mirroring.

I hope this explanation helps prevent some of you out there wasting time trying to troubleshoot this issue.