SQL Server Performance Forum – Threads Archive

T-Log size

Win2000 + SQL 2000 DB’s property – General tab: Database size: 4892MB
Space Available: 3029 MB DB’s property – Data file tab: 2268 MB Space allocated
DB’s property – Transaction log tab: 2625 MB Space allocated Recovery Model: FULL
I have set the Alert to notify me if the T-Log size over 2.5GB. I am receiving alert emails from the server indicating the T-Log size is over 2.5GB. Should I increase the T-Log size in alert setting? Or decrease the T-Log size? I get a full backup every night and T-Log every hours but why the transaction log file is growing up? What’s the best thing to do and what is your recommendation? CanadaDBA

---

(1) schedule backup and shrink log file;
(2) do you really need FULL Recovery, or Simple, or Bulk-Logged? -mingus

---

Schadule backup? but I have already set it to every night. Is it what you mean? Regarding recovery model, my server is a production server and I want to minimize the risk by choosing Full recovery model.

CanadaDBA

---

I meant, (Backup and Shrink) logfile. -mingus

---

First of all check what process are running and other DBCC checks during nightly schedule.
As a test shrink the Tlog to desired size and then monitor the activity for 2 days and see the growth of Transaction log file, if this behaviour is common then its better to define the max size reached in the test to the Tlog size. Truncating the log affects the records within the log file. Truncating the log marks the space used by the truncated records as available for reuse, but it doesn’t change the physical file’s size. You can force the physical log file to shrink in one of 2 ways. You can run the DBCC SHRINKFILE command on the log files; you can run DBCC SHRINKDATABASE, which shrinks every file in the database.
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.

---

Someone wrote me "Your transaction log is bigger that your database itself. That may not be a good sign". Is it true? If I use DBCC SHRINKFILE for TLOG, would I be at risk? In other words, what is its disadvantages? CanadaDBA

---

There is no potential risk in having Tlog size bigger than data file size, theoritically its strange to see. DBCC SHRINKFILE is the statement used to shrink the Tlog and no disadvantages as such I know as BOL refers The database being shrunk does not have to be in single-user mode; other users can be working in the database when the file is shrunk. You do not have to run SQL Server in single-user mode to shrink the system databases. HTH 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.

---

]]>