I am new to SQL and was given a SQL server to administer. There are lots of databases on the server and no one seems to know which one is used and which one is not. I try to do some cleanning.
1. Can I find out if there is any activity on a database so I can delete them off and how?
2. There are backup jobs that run every night which do a full backup. I am thinking of changing it to do full backup once a week and differential every night. These databases are updated daily. Some have lots of activities, some have less. Does that sound reasonable? and if I do differential backup, should I create separate back up file or should I add on to the full backup file?
Any comments or suggestions are greatly appreciated.
look up sqlmaint.exe utility, it'll give you the flexibility and at the same time can help you standardize the retention period on disk for those backups. if backups don't take long, then i'd continue with full nightly, and do db health checks weekly, along with reindexing or updating stats. delete backups older than 2 or 1 days if you are short on disk, and do tape backup nightly of those backup files, and you'll be in good shape.