Transaction Log Usage | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Transaction Log Usage

LO guys. I have currently got a Transaction Log which in Enterprise Manager shows as 13gig in size. After backing up it still says 13gig, is this assigned space, but only 25meg is being used. Whats the best practice to get the size down somewhat. P.S this is not the same log as a previous post of mine. Cheers.
You should run DBCC SHRINKFILE. Check BOL for more details. Bambola.
Always check from QA also by executing DBCC SQLPERF(LOGSPACE) which shows exact usage of Tlog. Also make a note BACKUP LOG doesn’t reduce the physical size of Tlog (.LDF), it truncates the log space used. You should execute DBCC SHRINKFILE to reduce the size of Tlog. Refer to books online for more information. BTW, what kind of Maint.jobs are scheduled on the database and how about Tlog backup schedule? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I would recommend keeping the transaction log at some reasonable size as if you reduce the size too much, the transaction log will expand as and when required during regular business operations and this expansion process hogs up resources. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
If you just want to reduce the size of the T-Log, the DBCC commands mentioned above will do. In addition and for future peace of mind, it may be worth your while to think about:
  • Your T-Log growth parameters in relation to the kind of primary DML activity in your database

  • Your database recovery model
Nathan H.O.
Moderator
SQL-Server-Performance.com
That is the reason I’m asking clarification for the job and as a test define the size and perform all housekeeping jobs to get the size and assign the resulted value. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Ok guys here is an update.
I have currently set the maint Plan to backup the transaction log on an hourly basis, BUT the connection as such is so slow it fails after a period of time, and we currently have a TLog of 20 gig but only 10gig to back it up. Cheers.
Then firstly find out why the connection is slow and check whether any issues with network between the servers. If you’re sure then shrink the Tlog size using DBCC SHRINKFILE as referred above. BTW, have you tried manual Tlog backup job by executing using SQLAgent and specify WITH INIT to a file. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
The transaction log backup should be taken to local machine and then copied to the remote machine. If in the current maint. plan, it is taken to the same machine, then what is the connection that you are talking about? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Thats a good point raised by Gaurav, while executing the Maint.plan check the current activity on the server. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>