Solved

Housekeeping on SQL Server 2008 R2

Posted on 2014-09-16
12
401 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
  • 5
  • 4
  • 3
12 Comments
 
LVL 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 250 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 48

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 48

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 26

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 26

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 48

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 48

Accepted Solution

by:
Vitor Montalvão earned 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

830 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