A
A
Andrey Tumanchik2017-06-16 20:15:55
SQL Server
Andrey Tumanchik, 2017-06-16 20:15:55

What is the best way to serve MS SQL 2005?

Good day to all.
MS SQL Server 2005, about five users, 3 gigabyte database, full recovery model, inherited, previously not serviced at all, worked as is.
I have updated the server to the latest version and am now setting up a maintenance plan. The main maintenance plan checks the consistency of the database, backs up with a full backup twice a day and notifies the admin.
I would like to ask you about the shrink, different sources have different points of view on this operation. Basically, it is NOT advised to do any shrink. My log is growing at about 20 gigabytes per week. I made a copy from the working base, I run it on it.
I would like to understand:
1. Do I need to shrink the database and the log, having previously backed up everything? The file sizes are currently 2.8G base and 46G log.
2. Whether it is necessary to automate process of reorganization\rebuild of indexes?
3. What is the best way to deal with the log? Truncation, backup, shrink to zero? With my recovery model, it is not very critical, I would not want to change the recovery model yet.
4*. Should I change the recovery model, do I need to restart the server? Or at once everything is picked up "on the fly"?
Many thanks in advance to everyone!

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Andrey Tumanchik, 2017-07-04
@jsand

In short, no one here answered. I'll write myself.
1. Shrink log and set up a maintenance plan - a full backup at night with a preliminary check for consistency and sending mail to me after the backup. Log backup every two hours.
2. Manually made reorganization\rebuild of indexes depending on percent.
3. Log truncated to 1 Mb. With a log backup every two hours, it grows very slowly.
4. The recovery model did not change.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question