Hi, I create one Database each with one subscription. The problem I am facing is that the respective transaction logs keep on growing. I want to truncate all the overgrown transaction logs. How do I do that ? Is the any specific way of doing so? Also is there any command(DBCC command) through which I can know the over all space that is being used by all the transaction logs? Thanks in advance. Durgesh.
Hi Durgesh, The query has been answered in this fora alot times. Simply search for transaction in search option and you will find many useful dbcc commands to do that, many useful links to achive what you need and many useful tips and messages.
Hi Durgesh, What you can do is create SP & decide the threshold, then compare it with TX log file size and then if it exceeds backup the log file to a dynamic location and then truncate.
Following link gives you a good tool http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=7845 ---------------------------------------- http://spaces.msn.com/members/dineshasanka
Whatever RECOVERY model you deploy one thing keep in mind in order to keep the size of Transaction log you must truncate the log using BACKUP LOG or TRUNCATE method that minimized the virtual size of Transaction log. Make sure you perform full backup once the log truncation method is deployed in the case of SIMPLE or BULK_LOGGED recovery model. The problem I am facing is that the respective transaction logs keep on growing. I want to truncate all the overgrown transaction logs. The problem is you may not have any scheduled job to address transaction log backup, so you must deploy a backup job or intermittent BACKUP LOG .. WITH TRUNCATE_ONLY to truncate the log. If you're performing huge inserts then ensure to keep it batches. 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.
Thanks! dineshasanka. I was actually confused between shrink a log file and truncate a log file and I still wonder is it the same thing or different. I mean to truncate a log file we need to shrink it right? moreover i did not find the DBCC command to know the amount of space used by all the log files. could anybody please guide me to a link? Thanks in advance. Durgesh.
sp_helpdb '<DatabaseName>' gives you all the deteails of log files ---------------------------------------- http://spaces.msn.com/members/dineshasanka
Thanks! satya. One more question, Is it neccesary to write USE %DBName% before we truncate the log of that database for example..... USE TTEST1 BACKUP LOG TTEST1 WITH TRUNCATE_ONLY DBCC SHRINKFILE ('TTEST1_log') Will this work? (Default database selected is master) BACKUP LOG TTEST1 WITH TRUNCATE_ONLY DBCC SHRINKFILE ('TTEST1_log') This doesnt work actually .........we need to select the database..... Durgesh.
hi, quote:Originally posted by durgesh Thanks! dineshasanka. I was actually confused between shrink a log file and truncate a log file and I still wonder is it the same thing or different. I mean to truncate a log file we need to shrink it right? moreover i did not find the DBCC command to know the amount of space used by all the log files. could anybody please guide me to a link? Thanks in advance. Durgesh. its dbcc sqlperf(logspace) , it will show you log space information for all databases. Hemantgiri S. Goswami ghemant@gmail.com "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami
Hi Durgesh, <br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by durgesh</i><br /><br />Thanks! a lot Hemantgiri.<br /><br />Durgesh.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Most welcome [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br />[<img src='/community/emoticons/emotion-1.gif' alt='' />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami<br />
DBCC SHRINKFILE applies to the files in the current database. Switch context to the database to issue a DBCC SHRINKFILE statement referencing a file in that particular database. quote:Originally posted by durgesh Thanks! satya. One more question, Is it neccesary to write USE %DBName% before we truncate the log of that database for example..... USE TTEST1 BACKUP LOG TTEST1 WITH TRUNCATE_ONLY DBCC SHRINKFILE ('TTEST1_log') Will this work? (Default database selected is master) BACKUP LOG TTEST1 WITH TRUNCATE_ONLY DBCC SHRINKFILE ('TTEST1_log') This doesnt work actually .........we need to select the database..... Durgesh. 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.
Hi Durgesh I am not pretty clear about your 1st question, but yes there is a command to know as to how much space the log files are using in your server. Try this: DBCC SQLPERF (LOGSPACE) It will display a result as a table in which it will display the database, its log size in mb and also its logspace used in %. Hope this solves part of your problem. Thanks & Regards Amer Mohammed Jamal SQL DBA, Wipro Technologies, Hyderabad, India email: amer.jamal@wipro.com
