Some ideas for SQL "Cleaning" Product version 3.0
-
I have simple product, ApexSQL Clean, that analyzes unreferenced objects in database but we are going to rebuild it to improve the reference algorithm and add a lot of new "cleaning" features. Here is a short list. Can anybody think of anything else? Are these good items? Any feedback would be greatly appreciated
The most useful things in ApexSQL Clean, I think, would be following:
1. Recovering true dependences into "sysdepends"
2. Showing for possible dropping unused objects, datatypes, users, roles
3. Showing for possible dropping empty files and filegroups
4. Showing for possible dropping duplicated indexes and fk
5. Removing superfluous permissions (user and its role have the rights
- it is dubbing; user doesn't work with a table but has the right;
user has both transitive and direct rights)
6. Renaming constraints into sensible names (FK_8927346594 ->
FK_Table1_Col1_ref_Table2_Col5)
7. Analising sizes of blob fields and configuring "text in row"
if the case is favourable
8. "Smoothing" collations of columns as the database default collation
and "smoothing" all collations in a database as the server default collation
9. Corrections user-login links (useful when a database has been attached
or restored from another sql-server), removing "lost" users
10. Showing for possible dropping roles/users that do not have any rights
Brian Lockwood
President
ApexSQL - SQL Developer Essentials -
This would be a good thing to make a list for. Here are some quick thoughts.
1. Orphaned records (Yes, it can happen)
2. Uncommited transactions
3. Like your #2, but also in tempdb
4. SQL Server Agent jobs that are not scheduled or disabled.
5. Possibly truncating the Error Log, SQL Server Agent logs, and DTS package logs.
6. Could check for versioned stored procedures.
7. Old or hanging connections (processes) that do not seem to be used anymore.
"Keep Your Stick On the Ice" ..Red Green -
thx much - I'll kick out big discount coupons and maybe some free software to anybody who posts good info. on this forum. The more ideas we get the better version 3.0 will be.
Brian Lockwood
President
ApexSQL - SQL Developer Essentials -
Streamline Object ownership chain
Refresh view automatically when underline objects changed.
Cleanup job / maintenance history
Remove old differential backup after certain period
Recommaend index rebuild
-
thx!
Brian Lockwood
President
ApexSQL - SQL Developer Essentials -
* Clean up old/unused statistics
* Verify last statistics update; recommend updates
* Suggest tables that should be pinned
* Check for startup procedures
* Check for 'fake' user tables
* Possibly clean up SQL Mail's inbox
* Find linked or remote servers that aren't used anymore
I'll probably post again in here
"Keep Your Stick On the Ice" ..Red Green -
* shrink db / log files for simple recover mode database
* frequently recompiling procs
* fragmented indexes that needs
* identity gaps fix
* remove duplicate records from a table
* encrypting all sp/view/function definations
* weak passwords
* unwanted user objects in system tables
* clean tempdb without restart
* update / rebuild of full text search indexes
* remove / re-init existing replication
* remove / re-init log shipping
* filter out warning / error messages from sql error log
-- Amit
-
we are going through all of these posts to make a master list - there are some great ideas in here! keep them coming!
Brian Lockwood
President
ApexSQL - SQL Developer Essentials -
Not sure if it qualifies but something that I end up spending time on is deleting and/or archiving data out of various tables. I end up with a whole series of jobs to manage that, usually one per table.
-
thx Andy, yes - data is another area of opportunity - deleting dupes, orphaned children etc.
Brian Lockwood
President
ApexSQL - SQL Developer Essentials -
> 1. Orphaned records (Yes, it can happen)
Could you explain what does it mean?
> 6. Could check for versioned stored procedures.
What checks are required here?
Thank you.
Gleb Ufimtsev, ApexSQL team member
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply