I'm Keep separate the Transaction log file(.ldf) and Data file (.mdf)
But I would like to know is that i'm tuning on the right way on SQL Server Administration ,Could you please suggest to me the way or how to Tuning SQL server ,i'm looking forward to your answer.
P.S. Right now i'm got the problem with the Extension size of Transaction log file it grow everyday ,i've shrink it already but still more large size for my disk (i have 200 GB and i'm keep the .mdf and .ldf together in there ,now size of .ldf is 180GB) ,One More Question is how to resize the transaction log file to be small than 180 ,i'm wonder when i'm clear the transaction log file it will impact to my company data ,so could you all experts suggest me how to do? Is there the way resize to smaller than 180 GB but the data still not impact? or it's have no way to do that.
Thank you very much for your attention to read my problem ,i'm looking forward to your answer. That's very kind of you all.
If you shrink your log file, and it regrows, then the database needed that space, so stop shrinking the database - because each time you do that, and it regrows, SQL will furher fragment the file and that will increase the inefficiency. It also takes CPU effort to "grow" the file, which will mean that the application slows down (and, usually, file will grow through user activity, so that slow-down will happen at a busy time for users).
If you are using Full Recovery Model? then increase the frequency of Log backups.
Look at the log backups from the last few days, is there a particular time fo day when the log backups are biggest? What is happening at that time? For us the biggest log backups are during database maintenance (e.g. index rebuilds), and we increase the frequency of Log backups to every 2 minutes during that data maintenance.
P.S. I wouldn't call, what you have described, as "Tuning" but rather "Housekeeping" or "Maintenance"
No, that's fine, its just that repeatedly shrinking the file is a performance issue, and I recommend avoiding it as a Best Practice.
First of All I'd like to say Thank you very much Mr.Kristen,
I'm realize that shrink database it will be save more space for my transaction log ,so i was misunderstood with this point thanks again ,i'll change my Maintenance plan will no more shrink ,Right now i'm use Full Recovery Model for backup my base for everyday ,it's that make my transaction log more extension? Should i change to Differential Backup is that can reduce the size of transaction log? ,one thing that i was wonder ,Now you make me clear ,so i can clear all my transaction log file that's not impact for my data.
You need to take regular LOG backups. Are you doing that? or are you just taking a Full Backup (i.e. once a day?)
When you make a LOG backup it will clear the log file, and that part of the log file will be reused. If you do not take a log backup, and the log file fills up, then the log file will grow / be extended.
We take a LOG backup every 10 minutes. For a database which users are adding data to (i.e. it would be difficult to re-create that data if it was lost, because users would not remember everything they had changed in the day) then I think 10 minutes is a good interval. Worst case, in a disaster, you will lose the database entries / changes from only the last 10 minutes.
Perhaps it depends how you "clear all my transaction log file" Best way is to make a LOG Backup.
Step I clear my transaction log is
1.Change DB Recovery to Simple mode.
2.Shrink transaction log that database.
3.Change Back Recovery to Full mode.
Now my transaction log before was 180GB ,Right now remain 6 GB is that any problem with my DB? I have no backup transaction log ,i realize that if i backup full recovery model it will also backup transaction log ,so in the past i was backup full ,right now i already know why the transaction log always extremely expand ,Thank you very much again Mr.Kristen.
If you don't backup your Transaction Log then leave the recover model at Simple. BUT ... the only backup you will have is your last full / differential backup. If you make that backup only once each day then, worst case, you will lose 23 hours and 59 minutes of data if you have a disaster.
If you are able to repeat all the work that was done during the day then this may be suitable (it still costs time & money to repeat the work, so even if you CAN repeat the work you might not WANT to!!)
If you leave the Recover Model at FULL then make a Log Backup every 10 minutes. The maximum size of your Log File will be the amount of transactions in the busiest 10 minutes of the day. If you can NOT repeat the work in a day (e..g because people are speaking on telephone and typing direct into database, and have no paper-copy of what they entered) then make sure you have use Full Recovery Model and take a frequent Log Backup
Consider doing differentials during the day along with the full backup every day. In fact, with differentials, you should be able to skip the full backups every day. On most systems here, we only do full backups once a week, and differentials during the week (and log backups on dbs that need it).
More generally, tuning is about indexing and how the queries are written, particularly if there are really bad coding errors, such as functions against columns used in any WHERE clauses and JOINs.
On indexing, the first thing to do is determine and implement the best clustered index on every table. Then you can add necessary nonclustered indexes.
On queries, look at the top 5 or 10 worst-performing queries and correct any problems you see. As you tune a query, it should fall back on the worst list and other queries will show up, and then you tune those. It's an iterative / on-going process.
Best way is to make a LOG Backup.