Hi All, I always face issues while shrinking the transactional log for a databases on server. All of sudden I notice that log size is 40gb. Shrinking the trasactional log file using DBCC SHRINKFILE doesn't work and there is no drive space to take the DB backup. Can any one sugesst me what is the best possible action need to take for this on the production server? Is it possiblt to shrink or truncate40gb log to 10mb. Please advice on this.
Is the log full? You need to clear it out before shrinking it if it is. Back the log up if the database is in full recovery mode or execute a checkpopint if it's in simple recovery mode. To see if the log is full or not, use dbcc sqlperf(logspace).
Put the database in SIMPLE recovery and execute CHECKPOINT in the database. This will clear out the log. Then you can shrink it. You should do a full backup of the database afterwards. you should also put in place regular log backups, otherwise this will just happen again.
check what is the size on the model database too, as it goes by it. http://msmvps.com/blogs/ssqa/archive/2007/06/19/transaction-log-guidelines.aspx http://msmvps.com/blogs/ssqa/archiv...ks-and-transaction-log-is-filing-up-help.aspx