SQL Server Performance Forum – Threads Archive

truncating transaction log files

Hi guys
I just want to find out if running dbcc shrinkfile and backup log is transparent to users. So I don’t need to wait for out of office hours to run this processes. cheers

---

dbcc shrinkfile does have an effect, especially if you’re running it on data files. This will only slow down performance though. shrinkfile on the log shouldn’t have too much of an impact. likewise backup log, as long as your logs and backup volumes are on separate physical drives to your data. Tom Pullen
DBA, Oxfam GB

---

unfortunately they are on the same physical drive. The transaction log has grown to about 9GB and I just learnt about the server this morning because it’s one of those environments where nobody knows what is where…

---

well assuming it’s empty (i.e. truncated/backed up first), running shrinkfile on it shouldn’t take too long- it’ll just be removing freespace. shrinking a data file is much more intensive as data has to be moved around to create contiguous freespace to remove at the end of the file. Tom Pullen
DBA, Oxfam GB

---

Tom,
I have a database in my production which:
database size: 6.8GB
space available: 4.5GB Data space allocated: 2.5GB located on E:
Log space allocated: 4.3GB located on L: Reconvery Model: Full
Do you mean that if I run DBCC SHRINKDATABASE would be enough to truncate log file (or make its size smaller)? Is it safe to run on the production when no one is connected to that machine? Any practical advise? Thanks,

CanadaDBA

---

Yes, – do it out-of-hours. I much prefer dbcc shrinkfile() as you have more control, and can chose precisely what sizes you are going to shrink the files to. Also you should reindex your database after shirnking as the process of shrinking can itself cause fragmentation. Some free space in a DB file is desireable too; for example, for reindexing, you need to have freespace equivalent to 1.5 times the size of your largest table to give enough room for the sorting operations of clustered index creation (recreation). In your example I would shrink the data file to maybe 4Gb, leaving plenty of free space still available. Sometimes the log won’t shrink; you need to move the log through several virtual logs to get it to shrink: you can do this by creating dummy transactions (there are plenty of examples of this in other threads in this forum). Hope this helps.
Tom Pullen
DBA, Oxfam GB

---

P.S. Back up your db first, put it in simple recovery mode too, do the shrinking, put it back in full recovery mode then back it up at the end . Tom Pullen
DBA, Oxfam GB

---

Thanks for the complete answer specially for the p.s. part. The log file size is very important to me and I want to reduce it. In your example I would shrink the data file to maybe 4Gb, leaving plenty of free space still available. The data file ( on drive E: ) is 2.4GB. Are you referring to this file?

CanadaDBA

---

Yes. that would leave 1.6Gb free, right? Better to have free space – if it runs out, the file will need to auto-grow (if enabled). This auto-growth will 1. impact users 2. may cause file fragmentation. Are you very short of drive space? Is it needed for something else? If not, leave some spare in the data file – it’s good practice. Tom Pullen
DBA, Oxfam GB

---

Fortunatly, there are plenty of space available. I will do your suggestion. Thanks,
CanadaDBA

---

]]>