House keeping projects for new year

Question

House keeping projects for new year

JakDBA

SSCommitted

Points: 1610
More actions
December 13, 2016 at 7:50 pm

#401204

Friends I am planning of doing some house keeping / eenhancements projects ... any suggestions ?? Version MSSQL 2014.
@JayMunnangi

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 1

Are you looking for general ideas here?

I'd make sure your backups are working well and meeting your RPO/RTO for each db. Script out logins, linked servers and jobs to be sure that you have up to date versions all the time. Maybe automate this.

Monitor space, calculate the time when you'll need to add space to each mdf/ldf, as well as when disks will fill. Use this to plan for storage needs.

Review and examine rights and permissions. Try to get the principle of least privilege implemented.

Ensure indexing and statistics maintenance works well for the environment in each database.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

Some housekeeping things to consider:

- if you use SQL Agent, clean old job history in MSDB https://msdn.microsoft.com/en-us/library/ms175044.aspx

- clean old backup history in MSDB https://msdn.microsoft.com/en-us/library/ms188328.aspx

- if you use DB mail, clean old mail history https://www.mssqltips.com/sqlservertip/1732/sql-server-database-mail-cleanup-procedures/

Those all let you control the length of history you want to keep for each one.

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 3

JakDBA (12/13/2016)
Friends I am planning of doing some house keeping / eenhancements projects ... any suggestions ?? Version MSSQL 2014.

Yes. Assuming that you've already ensured that backups are actually happening and you've done regular testing for restores, find the top 5 queries that use the most CPU and the top 5 that use the most reads and fix them. If it turns out the queries are from managed code, help the Developers fix them, possibly converting some of them to stored procedures (and you'll need to demonstrably prove the benefit in performance and resource usage).

It's the one thing that most people seem to never getting around to doing. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JakDBA SSCommitted Points: 1610 More actions · Answer 4

That's rite.. and all your suggestions are very valuable.

Thanks

@JayMunnangi