• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 484
  • Last Modified:

Housekeeping on SQL Server 2008 R2

Beside the "Shrink Database Task" on SQL Maintenance Plan, what other tasks do I normally need ? Do I need to purge any job log ?

Tks
0
AXISHK
Asked:
AXISHK
  • 5
  • 4
  • 3
2 Solutions
 
Shaun KlineLead Software EngineerCommented:
You should not perform the Shrink Database task. This causes fragmentation of you indexes and will cause performance problems.

At minimum, database backups and transaction log backup (for databases with a recovery model of Full or Bulk). You can also look at defragmenting or re-building your indexes on a periodic basic.

There are probably other tasks to consider and the other more seasoned experts here can assist.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Shrink Database is a task that you shouldn't do for a basis maintenance plan. You should even avoid it as Shaun explained to you.
Maintenance Plans are running Backups strategy and regular reindexes.
Jobs logs can be purged by configuring the SQL Server Agent.
0
 
AXISHKAuthor Commented:
When should I use Shrink Database Task ?

My SQL log is keep growing even after daily backup.  Where can I configure to  truncate the log after backup ?

Beside, where can I configure to purge the SQL job log ? Tks

Tks
0
[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
In a perfect world you wouldn't have the need to shrink database.
Which type of backups are you performing? For truncate the log you need to run transactional backups and depending on the use of the database it's almost sure that you need to run a tlog backup more than once by day (4h in 4h, 2h in 2h, ...).
0
 
Shaun KlineLead Software EngineerCommented:
One other thing you can consider doing is to not use the built-in maintenance plans and use a tested solution such as what is found at this site: https://ola.hallengren.com/.

The scripts that this person provides are free and provide a more granular control over four areas of maintenance: Backup, Index Rebuilds/Defrags, DB Integrity Check, and Update Statistics.
0
 
AXISHKAuthor Commented:
Only schedule database backup at night. Can I just add a maintenance task following the backup job to truncate the log ? Tks
0
 
Shaun KlineLead Software EngineerCommented:
Depending on your backup plan, you could run a full backup weekly, differential backups nightly (minus the night of the full backup) and then transaction log backups during the day. If you need to restore your database, you would need the weekly full backup, the differential backup from the night in question, and any transaction log backups for that day, depending on why your are restoring your database.

If you are not concerned with the backup file size, you can drop the differential backup for full nightly backups.

The transaction log backups are needed to keep the transaction log file from growing unchecked.
0
 
AXISHKAuthor Commented:
For full SQL backup at night, it will backup database and transaction log. I can use it to recover the full database, correct ?

If yes, I can simply add a additional task "DBCC SHRINKFILE (MySQLDatabase_Log, 50);" correct ?

Tks
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please forget the shrinkfile. As I understood you have only a full backup running daily. If you want to maintain that full backup it's ok but you need to add at least transaction log backups.
What you going to do if your database crashes during the day? You can only recover the day before and all the changes made during that day will be lost.
0
 
AXISHKAuthor Commented:
Need to shrink the transaction log as the SQL transaction is over 100GB... I want it to setup as 50MB. Can I issue the command in SQL maintenance job after full SQL Backup ?

Tks
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Like I said before, you shouldn't need to shrink databases but if you do I recommend that you do it in an ad-hoc solution instead of a permanent one, i.e. run it only once instead of including this task in a recursive job.
0
 
AXISHKAuthor Commented:
Tks
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now