InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 14:51:53 GMT20http://www.sqlservercentral.com/Forums/Topic1391864-391-1.aspx[quote][b]arunyadav007 (12/3/2012)[/b][hr]If not, you can try thisUSE MASTERGODBCC FREESESSIONCACHE WITH NO_INFOMSGSGODBCC FREESYSTEMCACHE 'ALL'GOUSE (YOURDBNAME)GODBCC SHRINKFILE (N'LOGFILENAME', 0, TRUNCATEONLY)GODBCC SHRINKFILE (N'LOGFILENAME' , 1024)GO[/quote]Well, I suppose that you can try that, but since none of those will help in the slightest with a full transaction log and may in fact make the scenario worse, I personally would recommend that you don't try any of those commands.Tue, 04 Dec 2012 07:10:32 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1391864-391-1.aspx@OP ,please do check Gail's post for managing log file[url]http://www.sqlservercentral.com/articles/Administration/64582/[/url]Tue, 04 Dec 2012 07:00:27 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1391864-391-1.aspx[quote][b]arunyadav007 (12/3/2012)[/b][hr]USE MASTERGODBCC FREESESSIONCACHE WITH NO_INFOMSGSGODBCC FREESYSTEMCACHE 'ALL'GO[/quote]and how does it will help to manage the log space ?you are just adding trouble to existing issuesTue, 04 Dec 2012 06:57:53 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1391864-391-1.aspx[quote][b]ravi@sql (12/3/2012)[/b][hr]ok cant i point my log file path any other drive ? if yes how can i do that ? pls share the query ?ThanksRavi@sql[/quote]If you want to move the existing t-log file to different location then, follow the below steps:1. Run ALTER DATABASE command and change location of ldf file2. Take database offline3. Move the physical file from old location to new location.3. Bring database onlineYou can also try to add second log file to your databaseUSE [master]GOALTER DATABASE [DB_NAME] ADD LOG FILE ( NAME = N'second_log_file', FILENAME = N'D:\LOG_FILES\second_log_file.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)GOMon, 03 Dec 2012 06:11:45 GMTarunyadav007http://www.sqlservercentral.com/Forums/Topic1391864-391-1.aspxThank you all .Regards,Ravi@sqlMon, 03 Dec 2012 06:09:08 GMTravi@sqlhttp://www.sqlservercentral.com/Forums/Topic1391864-391-1.aspx[quote][b]ravi@sql (12/3/2012)[/b][hr]ya maxsize is given for autogrowth[/quote]What are the size details for the log currently?Do you have any long running transactions?Mon, 03 Dec 2012 06:03:44 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1391864-391-1.aspxYou can try the below options:Increase the size of 'MAX GROWTH' (If ample space is available in the drive hosting t-log)If not, you can try thisUSE MASTERGODBCC FREESESSIONCACHE WITH NO_INFOMSGSGODBCC FREESYSTEMCACHE 'ALL'GOUSE (YOURDBNAME)GODBCC SHRINKFILE (N'LOGFILENAME', 0, TRUNCATEONLY)GODBCC SHRINKFILE (N'LOGFILENAME' , 1024)GOMon, 03 Dec 2012 06:01:05 GMTarunyadav007http://www.sqlservercentral.com/Forums/Topic1391864-391-1.aspxok cant i point my log file path any other drive ? if yes how can i do that ? pls share the query ?ThanksRavi@sqlMon, 03 Dec 2012 05:56:02 GMTravi@sqlhttp://www.sqlservercentral.com/Forums/Topic1391864-391-1.aspxya maxsize is given for autogrowthMon, 03 Dec 2012 05:54:00 GMTravi@sqlhttp://www.sqlservercentral.com/Forums/Topic1391864-391-1.aspxSpace in t-log is made reusable in SIMPLE recovery model as soon as the transaction is Committed/ written to the database. Have you restricted your t-log growth? If you don't have enough space in the drive hosting t-logs consider using smaller transactions and frequent COMMITs.Mon, 03 Dec 2012 05:51:53 GMTarunyadav007http://www.sqlservercentral.com/Forums/Topic1391864-391-1.aspx[quote][b]ravi@sql (12/3/2012)[/b][hr]recovery_model_desc log_reuse_wait_descSIMPLE NOTHING[/quote]What size is the transaction log?Does it have any growth configured?Mon, 03 Dec 2012 05:50:35 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1391864-391-1.aspxif your database is using the full or bulk logged recovery model take a transaction log backup using the following[code="SQL"]BACKUP LOG [MYDB] TO DISK = 'some drive\some path\mydb.trn' [/code]Mon, 03 Dec 2012 05:48:02 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1391864-391-1.aspxrecovery_model_desc log_reuse_wait_descSIMPLE NOTHINGMon, 03 Dec 2012 05:47:02 GMTravi@sqlhttp://www.sqlservercentral.com/Forums/Topic1391864-391-1.aspxSounds like you need to do some transaction log management.What is the output of this query[code="sql"]select recovery_model_desc, log_reuse_wait_desc from sys.databases where name = 'testdb'[/code]And some reading materialManaging Transaction Logs - [url]http://www.sqlservercentral.com/articles/Administration/64582/[/url]Why is my transaction log full - [url]http://www.sqlservercentral.com/articles/Transaction+Logs/72488/[/url]Stairway to Transaction Log Management - [url]http://www.sqlservercentral.com/stairway/73776/[/url]Accidental DBA Guide - Chapter 8 - [url]http://www.sqlservercentral.com/articles/books/76296/[/url]Mon, 03 Dec 2012 05:44:18 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1391864-391-1.aspxWhat is the recovery Model of the database?What is the size of t-log and growth options?Mon, 03 Dec 2012 05:43:31 GMTarunyadav007http://www.sqlservercentral.com/Forums/Topic1391864-391-1.aspxHi All, While running one sp am getting this below error'The transaction log for database 'testdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases' i tried to dbshrink using dbcc command still no use . i'll get small space but i.e not sufficient for my query or sp so i'll get the same error again. so i wanted to clear log file completely or i wanted to move it to any other path .Please provide quick solution for my problem.Thanks,Ravi@sqlMon, 03 Dec 2012 05:39:25 GMTravi@sql