What’s the worst advice you’ve ever seen(got) on a SQL Server Database…?

Guys, let me ask you this. What’s the worst advice you ever heard something related to SQL Server? Today I’ve seen a public forum where bunch of people are discussing on “How to Shrink a SQL Server Database”. Believe me guys…I can say, not even a single reply came from a real DBA and the guy who was looking for solution, tried all the bogus recommendations given by people without really understanding what he’s doing and how things work in SQL Server.

let me share few highlights! ( Forum Discussions in Red)

Q) I’ve deleted whole bunch of data from SQL Server with delete statement and why my SQL Server Database size not reduced? Google says to Shrink, How can I do that?

Re1: Shrink your LDF File, if it doesn’t work Shrink MDF File. If you Shrink your Database, your DB Performance Increases. It is advised to Shrink the Database everytime you take a Full backup. – OMG!!…Really? I’ve no idea where did he got this advice from.

Re2:  I Agree with Re1, actually your performance increases if you Shrink your DB!!

Now the person who asked the question goes “I’m trying to shrink Log file, but size is not reducing”

Re3: Do a Log backup with Truncate_only Option and then Shrink the File.(Interestingly some other guy is advocating this approach)

Now comes the Best part….

Re4: Take Log Backup, LDF file Size should reduce by this. If this doesn’t work Rebuild the Log File, taking the Database Offline. After doing both the steps change the Rec Model to Simple from Full. Even if this doesn’t work your last option is to restart SQL Server Services.

I couldn’t read any further and closed my window immediately!  Holyyyyyy…………..This made my day. I was laughing rolling all over the floor!

Guys, This is my advice – Don’t fall in trap of every advice you see floating all over internet. Don’t trust anyone’s advice blindly(Including me). You’ll end up loosing your Job, if you do without really understanding what people are advising you to do. There are many places where you can get real useful advises on SQL Server. Look for MSDN, BOL, Technet, SQLCAT, post your Q with #sqlhelp in Twitter. There are many kind and awesome MCM’s and MVP’s who can suggest you correct recommendations and make you understand what really SQL does when you do something on your SQL Server. It’s up to you Whom to trust and whom not to trust….

Also, I’ve seen many cases where people just don’t admit that they don’t know something! What’s wrong in it saying “I don’t know” if you really don’t know?? It is better to admit “I Don’t know, or at least say I’m not sure though” instead of advising some stupid recommendations right?…

Again…as i said earlier, It’s all up to you to decide whom to trust and whom not to.

Cheers!

Advertisements

PetaBytes(6000 TB) SQL Baby – Single SQL Server DB Backup File!!!!!!

Well, Today will be one of my Most memorable days in my SQL Server Career! Anyone Curious Why?? We got a Database Backup from one of our Vendors sizing 6 PB!!!!@$@$$%$@!!!! Yes, You are reading it right. It’s 6000 Tera Bytes! Let me tell you guys the story in short…

It was  just an another typical day for our DBA team and we all were busy with some routine tasks and some ongoing projects…All of a sudden, one of our DBAs was all excited to see a Backup file on a Network Share with a size of 6PB and..we all went to his cube for double checking the size of file what windows is showing. Yes, he was correct…That’s the largest Backup file(Well, any file in that matter) which I never saw in my entire life so far!

See the Screenshot below….

Please Ignore the Ugly Black Spots and just look at the size 😀

Well, am not sure if we could ever restore that file with native methods. First of all, is it a valid Backup file? Am not sure! What in the world are they storing in that Database? I Don’t Know…Even I’m very very curious to see what it turns out ultimately. I’ll chase my fellow DBA for sure to see what is this all about 😀

I just couldn’t stop sharing this with you all before I get more info on this. I Bet, this is the largest Backup File you might’ve ever seen(for maximum viewers of this post)!

If this is a valid Backup file and If the Database Size is really 6 PB, Then, I can proudly shout, our team supported one of the worlds largest SQL Server Databases 😀

I can’t even imagine how to maintain such a huge Database with all routine Maintenance tasks which a typical SQL Server Database needs!!!

How to Truncate/Delete all Tables in a SQL Server Database?

How to Truncate/Delete all Tables in a SQL Server Database in three simple steps????

Howdy! In this post let’s see how to truncate/delete all the tables in a given SQL Server Database and few important considerations while doing so.

Today, I had a request from one of my Application teams to copy a database from Prod to QA and truncate all the tables. It was a very small Database(7.2 GB) but had 250+ tables with manyyyyyyy Foreign Keys. So, I had to deal with Constraints before SQL Server allows me to truncate any table which is being referenced by some other table. Basically I’ve to follow the child-parent hierarchy to do so. I started with scripting out all the Foreign Keys and thought of dropping all of them and do a truncate and then recreating. Well, immediately something came to my mind….”How about Disabling Constraints and do my work and then re-enabling Constraints”

Imp Note: We can’t Truncate tables with just Disabling Constraints. SQL allows only if you drop them physically.

So, what is the Option I’m left with. Well, Delete is my weapon now.

How I did this was..

Step 1:  Disable all the Constrains

Exec sp_msforeachtable “Alter Table ? NOCHECK Constraint all”

Step 2: Delete data from all the Tables

Exec sp_msforeachtable “Delete From ?”

Step 3: Re-enable all the constraints which we disabled in Step 1

Exec sp_msforeachtable “Alter Table ? With Check Check Constraint all”

I Know, I Know….sp_msforeachtable is old school technique and deprecated, but am Okay as long as I Know there’s no harm using it and making my life easy 😀

So, what should we keep in mind if we are considering this approach??

The Very First thing is your Transaction log. How much room do you’ve for your T-log to grow is single most important consideration for this approach. Because Step2 will run as a single Hugee…..transaction. Let me say this in other Words, My Database was in SIMPLE Recovery Mode and I was monitoring the T-Log constantly..It grew upto 15.4 GB for doing this Delete(and the entire Database Size was 7.2 GB). Just Make sure….you are good here before taking this approach for a Medium-Large Database. Once you are done with this, do not forget to bring down your T-Log size accordingly.

Hope this helps!

SQL Server 2012 RTM(Trial) Released today:)

At laaast…..Microsoft released SQL 2012 RTM(Trial) today!! They also made Feature Pack(s) Available for download. Go ahead and Download and Install RTM along with desired feature packs and Brand new SQL Server Data Tools 🙂 Official RTM will be released in early April 2012!

SQL Server 2012 RTM (Eval) can be downloaded from

http://www.microsoft.com/sqlserver/en/us/get-sql-server/try-it.aspx

or

http://www.microsoft.com/download/en/details.aspx?id=29066

For Feature pack, visit

http://www.microsoft.com/download/en/details.aspx?id=29065

For Data Tools, visit

http://msdn.microsoft.com/en-us/data/hh297027

Have Fun testing RTM 🙂

Still running on SQL Server 2000/2005 today??

Are you running your business still on Backend SQL Server 2000 or SQL Server 2005 Databases? Well, let me tell you this. Go ahead and Upgrade to SQL Server 2008R2 now, at least give it a consideration right now. Plan for it, work with your architects and DBA’s for options you’ve.

Beware, Microsoft has announced End of Extended Support(April 2013) as well for SQL 2000. In other words you’ve to run your business on Unsupported SQL Server Version with Self-help Online Support only!!!!

If you are running on SQL 2005 and unaware of support policy, SQL 2005 is already running on Extended Support as of now. Mainstream support has already been ended on April 2011.

For more information and for understanding the options you’ve in place if your shop is still running on Older Versions of SQL Server, please refer to

 http://www.microsoft.com/sqlserver/en/us/support/support-updates.aspx.

Work Ethics/Morals

Work ethic is a set of values based on hard work and diligence. It is also a belief in the moral benefit of work and its ability to enhance character. An example would be the protestant work ethic. A work ethic may include being reliable, having initiative, or pursuing new skills. – Extracted from Wikipedia!

Why are Work Ethics/Morals so important no matter how good you are at/with your technology? Well, the “ethics” which you carry will define your real “you”. It’s not always Just about how good you are Technically, sometimes, I would say many times, all it matters is how/what’s your behavior with your peers.  IMHO, being professional, no matter what our jobs is…is bare minimum expectation any fellow worker would expect from you. We should learn how to be “Professional” before becoming IT Professionals! Being polite is no bad, even if you are a Pro in your technology. In fact, the more polite we are, more the people like you and it adds you lot of applauds and cheers to your Bucket!…

Let me ask this, What would be your opinion on a person who always say” Yes, I know it, i know how to do it”  just as a default Statement/Answer, but if asked to do it, he/she has no idea how/what was the question about? I really don’t understand why people fear to admit that they don’t know things. Being a SQL DBA, I admit I’m not an Expert/ GURU in each and every aspect of SQL Server. In fact No one can be a true Guru in each and every aspect of SQL Server. Well, SQL Server is not Notepad Application which you’ll come to know all the options available in couple of minutes/hours!!  Well, this is about First set of people.

Now, let me tell you about second set of people..sighsss…………!

They claim that they know everything, well, but If they are asked for same to show they’ll no where close to solution. But still, they tend not to agree that they “don’t know”. Well,…Being a good professional ;), assume that I’ve started to show how things work, they get a phone call(to his/her personal Mobile) and they left my cubicle. What exactly they are achieving here? Well, nothing! One thing is clear is that they know nothing and they are trying to escape from things and more importantly they created an impression to me that, they are “Worthless“. What if the same person sleeps in a conference room while you are trying to explain something new?? It happened to me recently and believe me, I was like “Reallyyyyyyyyyyyy”

What would I feel about them??(Just my Personal Opinion,YMMV)

In my humble Opinion, they are not worthy enough to receive my help/attention! Yes, that’s what comes to my mind as a first thought. Why should i really care, if he/she him/herslef has no zeal towards what they are doing for survival???…

1. They know nothing except faking things all around the place. I will never ever offer any help, because as per His/her statement ” They already know it ;)” anyways even if I try to help them, they will try to escape. – They think that they are in Safe Zone by escaping from situation, but in reality I got them!! It doesn’t take more than few minutes to get to know about his/her skills in what ever work they are doing!…

2. I’ll never offer help for carrying such ruthless attitude!

3. They are not fit for this Job. Yes, I mean it!!

4.They are  Useless and my Company is paying him/her money for no worth.

5. I would never give +ve feedback on him/her to my Management.(Of course, they are of no worth to my organization)

Bottom Line : First Learn how to be professional, Then you can learn about your Technology. Don’t shy away, If you don’t know something, say it loud, ” I Don’t know that, can you please shed some light on this? ” There’s nothing wrong in saying ” Yes, I don’t know”!

What is IPv6? Why should we care about it?

In my leisure time I was going through few of the recent advancements which world is moving towards in Information Technology. I found IPv6 Interesting of all. Like me, if you are curious about knowing what IPv6 is and how it offers advantages over IPv4, this short animated video will help you.
I guess we are not far away moving all our Servers in our Data Centers to IPv6 from IPv4.

Video from Explania folks:

 

Also, an interesting and very informative Pic from Focus.

Image Source: http://www.focus.com/images/view/49504/

Hope this gives you all a good Kick Start exploring about IPv6.