SQL Server Performance Forum – Threads Archive

Database Maintenance Plan

Hi Guys..need some advise. Right now I have 4 databases on 1 of my server,I guess there are no maintence plans about backup and recovery.This is all my production server looks like right now.
1.Its setup for everyday complete backup with FULL recovery model and they are keeping only 3 days old backup.
2.mdf,ldf,ndf files are on same drive and backup files are on another drive. But I want to have a new maintenace plan where I shud not have any issues like unexpected growth in log files and place all files in the right place. Is there any article only regarding this so that I can follow that and proceed.
Thanks!
"He laughs best who laughs last"

You can deploy a new maintenance plan to backup the user & system databases seperately, also a seperate plan for transaction log backup every 15 or 30 minutes, that will enable to keepup the size of transaction log in a managable way. If you have the optimization jobs such as reindex and checkdb then ensure to keep an eye on transaction log and perform log backup immediately to take care of its size. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I have a question If I am taking a regular ldf file backup, how come the ldf file size is maintained without growing? Thanks!
"He laughs best who laughs last"

You can find a lot of articles in our forum (see Search in top). Also a lot of post. But I have a question: If you have one full backup per day, I don’t understand why you have full recovery model. With full recovery model, you should full backup said each 2 or 3 days and transaction log backup said each 15-30 minuts. The transaction log backup, also reduce log. Other alternative is differencial backup with (or not) transaction log backup. The idea with full recovery is to restore loosing only 15-30 minuts no all working day. Sorry, when I begin to write no answer were here[:I].
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Luis
so you mean that if I have a full backup everyday then no need of having FULL recovery model instead just have SIMPLE model? If so how log files are maintained with SIMPLE model. Thanks!
"He laughs best who laughs last"

In production server allways is better to have Full recovery model for the reason I said before.
About your question with Simple model you don’t have any issue with big log and when you backup full all log will backup too.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

As Luis said, there are very few reasons why you would NOT run your db’s in Full Recovery Model. If you determine, Simple Recovery is "good enough" then you’re potentially at risk to lose one whole day of work in case of disaster. In most OLTP environments this is not acceptable. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Hi, can you call xp_sqlmaint without a plan id? I am trying to write my own script using xp_sqlmaint outside of any maintenance plan. Is that possible?

Kindly, begin a new thread with your question.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Sorry about that. I already got my answer from books online and other newsgroup. Thanks.

Reddy
SIMPLE or FULL recovery model never controls the transaction log sizes, BOL refers
At a checkpoint, SQL Server ensures that all transaction log records and database pages modified are written to disk. During the recovery process of each database that occurs when SQL Server is restarted, a transaction must be rolled forward only when it is not known whether all the data modifications in the transaction were actually written from the SQL Server buffer cache to disk. Because a checkpoint forces all modified pages to disk, it represents the point at which the startup recovery must start rolling forward transactions. Because all pages modified before the checkpoint are guaranteed to be on disk, there is no need to roll forward anything done before the checkpoint.
If you leave auto-grow on transaction log then when it reaches 90% then SQL will attempt to increase the size of Tlog until the transaction fits in to the log. BACKUP LOG or keeping database recovery model in SIMPLE (checkpoint) will attempt the Tlog to truncate virtual log to accomodate next set of transactions. If you attempt to backup Tlog every 15 minutes the log will not grow and if you have any optimization jobs then it will increase the size to fit those set of transactions in Tlog file.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>