6

I'm a new SQL Server DBA in a new company. Just recently, they have handed me access to the server. There are about 50 OLTP databases with different sizes, some ranging 250MB and the biggest database is 25GB with a total size of 150GB data, 100GB logs for all 50 databases.

I would like to know what could be done if you are handed out access to the instance the first time? The action plan in my head right now is to run a server-side trace and perfmon to see what's going on with the database

Is this a good plan to begin with running that server-side trace? I would like to get some insights on what to normally do in this case.

Also, they have this job that does full backup of all the 50 databases in one job every day. Is that something that needs to be separated in chunks? My thought on this one is that it's probably OK to backup all the databases in one job since the size is not that big, and still can afford to do daily full backups because of the size. (Let me know if im wrong). And according to them, they can live with the server being down for 5 days. So it seems like the RTO is very flexible.

What are the necessary things that I need to know to better perform the administration of the databases?

Improve this question
4
  • 1
    Please review sp_Blitz and IMO don't start a new job with a server side trace. Start with wait stats, perfmon, and other things that are less load intensive. You don't want to be capturing it on the server itself only to have it fill up, or it eats up resources during critical end of month calculations and so fourth. Check out using extended events instead of profiler. It's much safer.
    – Ali Razeghi - AWS
    Commented Apr 13, 2016 at 22:50
  • @AliRazeghi, Thanks for the advice. From the research that I did, I thought that a server-side trace would not harm the server, using profiler will. What do you mean by not capturing it on the server itself? Is there any way that I can capture data by running it on another server? Im a bit confused
    – Keith Rivera
    Commented Apr 13, 2016 at 23:04
  • Probably not the first task, but once a suitable opportunity arises, practice restoring from backups, preferably to a new/reinstalled server.
    – jpa
    Commented Apr 14, 2016 at 9:46
  • @rk13 thanks for pointing out trace/profiler. To this day I interchange them sometimes improperly. I was pointing out some of the other issues I've seen with server traces whereas a extended event will let you log all the data on another machine completely. It might be cool to check out in this case. If you're comfortable with the trace though go for it! There's nothing wrong with a quick working solution that won't have a negative impact. I'm personally just overly cautious.
    – Ali Razeghi - AWS
    Commented Apr 14, 2016 at 22:06

1 Answer 1

Reset to default
9

I think sp_Blitz by good folks at Brent Ozar Unlimited is the best way to go with a new to me server.

https://www.brentozar.com/blitz/

https://www.brentozar.com/first-aid/sql-server-downloads/

Improve this answer
4
  • Hi thanks for this. Where do you normally store this storedproc? Can you just store this in one of the user databases and not on the system databases?
    – Keith Rivera
    Commented Apr 13, 2016 at 23:06
  • 2
    Right from Ozar and Co, "It’ll install sp_Blitz® in your master database by default, but you can put it in another database if you prefer and it’ll still work fine."
    – tpet
    Commented Apr 13, 2016 at 23:09
  • 1
    Aww, thanks sir, glad you like it! This use case is exactly why I wrote this thing - I got so tired of people handing me servers that were loaded with ugly surprises.
    – Brent Ozar
    Commented Apr 14, 2016 at 10:30
  • Can you add a little bit to your answer for the uninitiated explaining what this does and what information it gives (helps with link rot as well)
    – Matt Wilko
    Commented Apr 14, 2016 at 11:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.