Q1. Yes.
Q2. Perform BACKUP LOG statement (this should be scheduled quite often to keep your log in a reasonable size) and then DBCC SHRINKFILE - if not helps at once, perform both (BACKUP LOG + DBCC) many times. If not helps after several executions - check if there is no open transaction blocking log reuse (use DBCC OPENTRAN to detect SPID running the transaction and then DBCC INPUTBUFFER to see the last statement of the SPID). You can go with your database into SINGLE_USER (use ALTER DATABASE and WITH ROLLBACK IMMEDIATE option) to rollback all running transactions (including those long running) and then perform log truncation.
Use DBCC SQLPERF(LOGSPACE) to check the size and usage space of the log.
Main Topics
Browse All Topics
by: angelIIIPosted on 2008-08-08 at 00:07:42ID: 22187955
> Transaction log size is also at a manageable 15MB avg size
sorry, but 15MB for the log file is FAR too small. especially with a DB of 80GB, the number of transactions will eventually be much more than just 15MB (or did you mean 15GB? -> that would be too large)
CREATING a clustered index will use some transaction space indeed.
having one should not increase/decrease log space as such...
>My transaction log backups job is being run every 15 mins successfully.
that's fine.
>Q2. How can I reclaim OS Space from log file ?
you can try the DBCC SHRINKFILE command. however, as log files are a circular buffer, in case the "internal write pointer" is at the end of the file, it won't do anything. however, after some more transactions (with the internal write pointer having moved forward, ie to the beginning of the file), + a log backup, then the command will succeed.
note however that you should not try to reduce the log file tooo small, as otherwise the performance of the database drops because it will have to grow the file again.
conclusion:
with regular t-log backups, the log file size should come to a stable size quickly.
if it does not, check out the output of DBCC OPENTRAN to see if you have some open transactions which keep the internal log buffers from being reclaimable.