Well it really is something you have to decide for yourself, because Second Life (SL) is so open-ended.
Some people do not like to refer to SL as a game - they consider it to be a virtual world. Maybe because so many people use it for so many different things, a recreation or hobby might be a better description for it. You certainly can play in SL, but you can do so many other things too. First and foremost, it's a social network thing - a place where people make friends and chat. In that respect, it's like a chat room. but you have the added bonus of having an avatar to represent you and he/she can interact in ways you never can in a chat room, because your avatar can engage in activities. You can meet a friend and go shopping, go to a club and dance together, explore places together, or take part in something like car racing, scuba diving, horse riding or skating. Live concerts are popular SL. If you have musical skills, you can stream your live performance into SL and your avatar can represent you in-world by playing a guitar or piano.
SL itself is totally free to download, install and use. There is an option to upgrade to a premium account for a monthly fee, but you really only need that if you want to buy land directly from the makers of SL. For this, you need to be pretty wealthy in real life, or to have a successful business that will cover this expense.
There are lots of free things around to customise your avatar with, but for a really personal touch, you will eventually need money. This is the part that scares a lot of people off from SL, but it's not as bad as you may think. To buy money, you add your credit/debit card details to your account page (on the SL website). You will never be charged anything for doing this and no money will be withdrawn from your account unless you do something stupid like give out your password to another resident. You just have to make sure you upgrade your payment method, NOT upgrade your account (they are two different things on your account page). Once back in SL, you can now buy money by clicking on the 'L$' button at the top of the screen. L$ = Linden Dollars and L$1000 will cost you around $4.80 US. When you add your card details, you will discover that you are limited to buying no more than L$1000 per day for a while. This is so that you don't go crazy with your spending.
Really though, unless you know from the moment you arrive that you are hopelessly in love with SL, stick to the freebies while you find your feet. Despite what some people may tell you, you don't have to have a job in SL to get by or to enjoy it. In fact, don't waste your time looking for a job, because no one will employ you until you have been around for at least a month and have picked up some skills. What you should do though, is make a friend when you first arrive in SL. You will be at a place called Orientation Island and everyone there will be new arrivals just like you. A newbie friend is an invaluable asset, because as soon as you get out into the wider SL world, you will be amongst the SL veterans. They will regularly use words such as 'IMs', 'prims', 'sims'. 'lag', 'rezzing', 'LMs', 'TPs', 'AO' and lots of other stuff that will confuse you. SL has it's own language - which you will very quickly learn. When you are new, SL can be a bit daunting and if you are on your own, it's common that you feel really stupid for not knowing what is going on. The fact is, we all started like this. With a friend who is as new as you are, you quickly realise you are not dumb. You learn together and help each other out. And company early on is essential, or you can feel isolated.
Do I think it's fun? Yes. But I've been with the same SL partner for well over 2 years now and we keep each other company. Oh yes - you can get married in SL too. I have friends from all over the world in SL and we do a lot of things together. I've just rented a new house in SL on the outskirts of a beautiful French-style town, so we have been decorating that and adding furniture. Earlier today I went to see my friend's band play a concert in SL which was fun - and then I went on a treasure hunt (which resulted in me getting some really nice clothes and jewellery for my avatar for free). On any given day at any time, there is always something going on somewhere in SL.
I advise you not to join SL at a weekend as it can take forever to log in for the first time. There are only around 200 copies of the Orientation Island and each only admits around 50 people at a time. Thousands of people try out SL for the first time at weekends. It's so difficult to get in and so hard to move around the starting areas (lag), that many of these people think that's how it must always be and give up. If you have no choice and have to join at a weekend, don't be put off by the lag.
Despite what I may say in favour of SL - or what anyone may say against it - you really do need to try it for yourself - which is what I did. Give it at least a couple of weeks with at least an hour most days in SL - more is better. Although that time may allow you to start enjoying it, it could also knock off the initial polish. By that time, you will know if you truly like SL. If you get stuck, hit my Yahoo Answers page and drop me a message.
You can join SL here: http://secondlife.com