Sometimes you see someone with toilet paper sticking to their shoe, their shirt tucked into their underwear, or badly in need of a tissue to get rid of whatever that is hanging out of their nose. It’s only right to find a discreet way to let them know they should do some quick cleanup before they get completely embarrassed.
The same embarrassing “oops” exist for SQL Server. There are some practices and configurations which just make it look like the DBA may not have learned about this “internet” thing yet.
But, truthfully, it’s very simple to accidentally embarrass yourself. Here are the top 10 (well, 11) signs that your skills or your SQL Server are badly in need of an update:
- You have databases that haven’t been backed up recently
- Your last DBCC CheckDB is over 2 weeks old
- You have jobs that shrink files– or autoshrink enabled
- You truncate your transaction log
- You’re running the RTM version of SQL Server
- You use SQL 2000 style commands like DBCC SHOWCONTIG and DBCC REINDEX on later versions of SQL Server
- Priority Boost is enabled
- Power savings is enabled
- App accounts have SA or db_owner permissions
- It’s a 32-bit SQL Server
- Your tempdb has only one data file
Even if you know better, have you checked your servers to make sure that you don’t have toilet paper on your shoe without realizing it? Run our free sp_blitz® script to check for most of these issues, and more.
9 Comments. Leave new
It’s starts hanging out with the MongoDB servers
LOL. If you love your SQL Server, set it free. If it comes back to you as MongoDB, well, it was never yours in the first place.
The database backups you do have are stored locally.
You go to run sp_whoisactive, but it isn’t there.
Hey Kendra, Neat post and Happy New Year.
Tempdb having just one data file is not that bad right? I prefer to add files only if there are issues with contention.
We recommend a preliminary sizing of the number of tempdb files as part of setup because it’s such a common issue.
There’s no downside to some preventive medicine on this one, so you just get a lot of egg on your face if you don’t try to prevent the issue at all and then it gets bad.
Edit: added “number of” for clarity 🙂
Sure,definitely a good idea to be proactive than being reactive !
One data file is good – why should I go for multiple tempdev files? I don’t have any contentions on my server. This sounds like a suggestion that is out of touch of reality. I guess this is hear say – not tested.
Hey there,
Each item in the post has a link on it to a page that explains more about it. Contention on SGAM and PFS pages in tempdb is a longstanding problem in SQL Server that can be prevented in most cases by a little bit of configuration of your tempdb files. It’s not hard to reproduce the issue and definitely isn’t hearsay– I encourage you to learn more about it. Best of luck!
Kendra
I’m sorry but this gave me the biggest laugh possible and it isn’t heresay…