The daily (database) WTF: #TSQL2sday #88 Roundup
Last duty for me as a T-SQL Tuesday host is to write up a summary of all the posts. Soooo, here we go… Malathi Mahadevan (b|t) wrote in https://curiousaboutdata.com/2017/03/12/tsql2sday-daily-database-wtf/ about that first consultancy job, where she was fired pointing out that the backup script was failing because the script didn't filter TEMPDB out of the list of databases to backup. Later, the organization suffered a major outage, but didn't have backups of databases with names greater than TEMPDB.
Morale of the story: Test your restore procedures and be humble when you are a consultant (even though you are right) Arun Sirpal (b|l|t) wrote the post Extreme situations require extreme commands about the need to do an emergency repair using DBCC CHECKDB because he didn’t have any backups available.
Morale of the story: do have backups of (transaction logs) available Rob Farley (b|l|t) wrote the blog post http://sqlblog.com/blogs/rob_farley/archive/2017/03/14/sql-wtf-for-t-sql-tuesday-88.aspx starting out by pointing out why you as a consultant (or employee) shouldn’t talk bad about whatever horror you see at their site (I totally agree that one never hang out companies “out to dry”. The stories on the daily wtf site have been anonymized, and I hope that all contributions to this month’s topic have been as well). Then he told the story about the (heap) table which was more than 400MB in size. For 8 small rows.
Morale of the story: Behave and don’t b*** about your clients/employer. And understand that heaps are not suited as the storage model for tables that have a large amount of deletes. James Anderson (b|l|t) wrote the post Is Your Data Open to Abuse? about a company he used to work for, where they had a very liberal attitude towards the sa account…
Morale of the story: Hardening is not that hard. And at least don’t share the sa account. Andy Yun (b|l|t) tells the story in Surprise – Those Aren’t Test Accounts! about the undocumented test data, which turned out to be production data during a data clean up job. Morale of the story: Make sure that you have a (tested) restore procedure, when cleaning up data in production. Robert Davis (b|l|t) wrote four small stories about failed mirroring failovers, the DBA team where the normal was “When you’re on call, you just set your alarm to go off every hour to check for emails. It’s no big deal,” how the SAN admin was looking at the metrics from the old SAN, and about the sysadmin, who wanted to implement database replication as a spare time project. in My Top WTF Moments Morale of the story: Sometimes, the stories of the real world exceeds the ones from the movies. Taiob Ali (b|l|t) wrote in Communication is the Key about two people testing the same wait statistics script in the same environment at the same time, messing up samples. Morale of the story: Make sure to communicate not only what code needs to be tested, but also where. Deborah Melkin (b|l|t) tells the story of the high-transaction table with 50+ nullable columns in her post https://debthedba.wordpress.com/2017/03/14/t-sql-tuesday-88-the-daily-db-wtf/ Morale of the story: test your hypotheses (and read Kalen Delaney’s book Microsoft SQL Server 2012 Internals to learn exactly how column data is stored in the page)
Ryan Booz (b|l|t) wrote the story What? All of the constraints are disabled? about the database schema generated from the ERWin tool, with 721 Foreign Key constraints and not one of them is enabled, and where all check constraints were implemented using triggers. Morale of the story: know your tools and what they generate for you (or don’t). And question the design if objects are named ‘r_0013’ or ‘IF345TableABC’. Kevin Hill (b|l|t) wrote a list about the worst (mis) configuration of a SQL Server he has ever seen in the post http://dallasdbas.com/tsql-tuesday-the-daily-wtw/ Koen Verbeeck (b|l|t) wrote the story of a BI project, which performed very well on the consultants laptop, but not so well when deployed to the “server” commissioned by the sales person closing the deal in the post http://sqlkover.com/tsqltuesday-88-the-daily-database-wtf/ Morale of the story: When your laptop is more powerful than the server, it is not a server. David Alcock (b|l|t) wrote the story where the external “SQL expert consultant” was experimenting with many SQL Server settings “optimizing” for performance, including the expert advice of “implementing daily shrinks of the databases” in the post http://sqlclarity.blogspot.dk/2017/03/t-sql-tuesday-daily-database-wtf.html Morale of the story: Ultimately, WTF moments will always happen, the key is how we react and how we learn from them. Rebecca Mitchell (b|l|t) wrote the story of how the security badge system wasn’t working anymore in the post https://tsqlprincess.wordpress.com/2017/03/14/temporary-insanity/
Go read why, it is a fun surprising ending… Morale of the story: Just don’t do what this particular vendor did. Shane O'Neill (b|t) wrote the post The Biggest Danger to your Database: Me about how modifying an index can take more than one try. And is brave enough to admit that the culprit was… himself. Morale of the story: Maybe try your changes in the test system before you do it in production? Raul Gonzalez (b|l|t) wrote the post The security audit WTF where he shares a T-SQL script, which can “return Server logins with server roles and permissions, logins included if it is a Windows Group, groups the login belong to and database users mapped to the login” Kenneth Fisher (b|l|t) wrote about that Limits are not goals! Where he makes the case of not adding 32,000 databases to an instance, or maxing out on the number of tables in a database in the post https://sqlstudies.com/2017/03/14/goals-vs-limits/ Morale of the story: don't do it just because you can,
Meagan Longoria (b|l|t) wrote the story Insufficient Disk Space about the importance of having a DBA to check a database before going into production. Morale of the story: Make sure you have (and test) your backups (both full and transaction logs) for your production databases. And please don’t put the data and log files for the production database on the C-drive…
Ewald Cress (b|l|t) wrote the post The string length server on architects, home-grown frameworks and how to validate that the length of a form field is less than 20 characters. Morale of the story: just don’t do it like that? Adam Machanic (b|l|t) wrote a post about the guru Jonathan who had on a nice looking suit and shiny shoes. He had a smooth demeanor and introduced himself as “a consultant and SQL guru.” Adams job was just to performance tune the production database with no need for a test environment, and just “work some magic...” Morale of the story: Sometimes, you just need to walk away and never look back, knowing that what you have seen can never be unseen. Steve Jones (b|l|t) wrote the post http://www.sqlservercentral.com/blogs/steve_jones/2017/03/14/t-sql-tuesday-88-wtf/ about (the lack of) source control, encrypted stored procedures on the production servers, and quite an alternative approach to source code branching. Morale of the story: Source control is also a good thing for database code Riley Major (b|t) wrote a blog post called Mmm… fudge about all the problems they were too “ignorant” or too “lazy” to fix. And then I remember that “they” was “me”. And a bit on using NULL as defaults for parameters in stored procedures.
Morale of the story: By all means, review and critique code, but do so with an open mind and without judgment. A code review is an opportunity for both parties to learn Wow! 21 blog posts on crazy database experiences. Gave me some good smiles, giggles and a few genuine WTF moments.
I you want host your own T-SQL Tuesday, then go check out the rules and contact Adam Machanic. He doesn’t bite, and I’m sure he’d love to have you host the event. Being a host is a fun way to meet new fellow bloggers online, get some inspiration, and get other people reading your blog. Cheers, K