Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Housekeeping on SQL Server 2008 R2

Posted on 2014-09-16
12
Medium Priority
?
461 Views
Last Modified: 2014-09-17
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
Comment
Question by:AXISHK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 27

Assisted Solution

by:Shaun Kline
Shaun Kline earned 1000 total points
ID: 40325282
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
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40325376
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
 

Author Comment

by:AXISHK
ID: 40325415
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40325435
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
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 40325477
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
 

Author Comment

by:AXISHK
ID: 40325488
Only schedule database backup at night. Can I just add a maintenance task following the backup job to truncate the log ? Tks
0
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 40325524
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
 

Author Comment

by:AXISHK
ID: 40325588
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
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40325639
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
 

Author Comment

by:AXISHK
ID: 40327014
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
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 1000 total points
ID: 40327372
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
 

Author Closing Comment

by:AXISHK
ID: 40327499
Tks
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question