InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 20:48:52 GMT20http://www.sqlservercentral.com/Forums/Topic1382366-391-1.aspxYeah they both do, and I only suggested it on the basis that the log file was at 50GB (from a standard 4GB), and therefore log shipping and log backups were unlikely to be taken, at least with any regularity.The better option is to set up maintenance routines on your logs to prevent them getting to that size, but in the event you're not maintaining logs or are out of space, then it is an option to consider.Thu, 08 Nov 2012 04:36:53 GMTMcSQLhttp://www.sqlservercentral.com/Forums/Topic1382366-391-1.aspx[quote][b]sanket kokane (11/8/2012)[/b][hr][quote][b]McSQL (11/8/2012)[/b][hr]Ah apologies, yes you're right, you would have to set RECOVERY to SIMPLE and then run the shrink commands in SQL 2008.Forgot that this was a discountinued command in 2008 :-P[/quote]be aware about this . this can break LSN chain in log file[/quote]So does TRUNCATE_ONLY, should only ever use it if you dont care about recovery and if you dont care about recovery need to step back and decide if you actually need to be in the full or bulk logged recovery model.Thu, 08 Nov 2012 04:31:27 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1382366-391-1.aspx[quote][b]McSQL (11/8/2012)[/b][hr]Ah apologies, yes you're right, you would have to set RECOVERY to SIMPLE and then run the shrink commands in SQL 2008.Forgot that this was a discountinued command in 2008 :-P[/quote]be aware about this . this can break LSN chain in log fileThu, 08 Nov 2012 04:29:07 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1382366-391-1.aspxAh apologies, yes you're right, you would have to set RECOVERY to SIMPLE and then run the shrink commands in SQL 2008.Forgot that this was a discountinued command in 2008 :-PThu, 08 Nov 2012 04:24:09 GMTMcSQLhttp://www.sqlservercentral.com/Forums/Topic1382366-391-1.aspxsorry truncate_only present in sql server 2005 .Just verified :-DThu, 08 Nov 2012 04:16:49 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1382366-391-1.aspx[quote][b]anthony.green (11/8/2012)[/b][hr]Probably as I stated on my post above, the active portion of the log was at the end and it needed the second backup to move the active portion back to the beginning of the file allowing you to shrink the file.[/quote]Microsoft removed Truncate_only option from sql server 2005 onwards.you should refer links provided by Anthony to manage your log fileThu, 08 Nov 2012 04:14:22 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1382366-391-1.aspxTRUNCATE_ONLY has been removed in SQL 2008Thu, 08 Nov 2012 04:12:41 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1382366-391-1.aspxThanks for your reply.I've now solved the problem, but when I tried your suggestion of using the TRUNCATE_ONLY option on the log backup I received the following error:[code="plain"]Msg 155, Level 15, State 1, Line 3'TRUNCATE_ONLY' is not a recognized BACKUP option.[/code]Thu, 08 Nov 2012 04:11:02 GMTraotorhttp://www.sqlservercentral.com/Forums/Topic1382366-391-1.aspxProbably as I stated on my post above, the active portion of the log was at the end and it needed the second backup to move the active portion back to the beginning of the file allowing you to shrink the file.Thu, 08 Nov 2012 04:08:53 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1382366-391-1.aspxI ran the two commands you suggested. However, the second one told me that "LOG BACKUP" was needed. So, I performed another log backup and then did a SHRINKFILE on the log and Voila!I now have a 4Gb log file once more! :)What puzzles me though is the fact that after doing my first log backup that this didn't solve the problem. It seems doing the second one was needed, but I don't know why.Thu, 08 Nov 2012 04:06:55 GMTraotorhttp://www.sqlservercentral.com/Forums/Topic1382366-391-1.aspxManaging 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]Some good links above on transaction log management.As you have run shrinkdatabase I would strongly recommend checking for index fragmentation and rebuilding any indexes which need rebuilding.As for the 50GB log file, what caused the log to get that big? One off data import? Poor transaction log management? Day to day activity?Also remember that the log can only be shrunk to the most active point as the log is cyclical, so if the active portion of the log is marked at 49.5GB and you shrink it, it can only shrink it to 49.5GB, you will need to wait till the active portion of the log has looped round to the beginning of the file before you can shrink it down fully.Thu, 08 Nov 2012 03:43:14 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1382366-391-1.aspxPersonally, I wouldn't shrink the database if you don't HAVE to.It tends to hit a specific size for a reason, and you'll be hit in performance when autogrowth takes place to expand it back to it's natural size.As for the logs, if it is a problem with size and you don't have log shipping set up or a regular log backup, you can try the "WITH TRUNCATE_ONLY" option in the backup step and then try the shrinkfile.I normally run;shrinkfilelog backupshrinkfileIt should work after this,Thu, 08 Nov 2012 03:36:39 GMTMcSQLhttp://www.sqlservercentral.com/Forums/Topic1382366-391-1.aspxwhat is the output of the [code="other"]dbcc loginfo( YourDBName)[/code]also[code="other"]select log_reuse_wait_desc from sys.databases where name = 'YourDbName'[/code]Thu, 08 Nov 2012 03:36:33 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1382366-391-1.aspxHello,I know this question has probably been asked a thousand times, so I apologise in advance, but I appear to be unable to reduce the physical file size of a log file I have on a database using the Full recovery model.So far, I have done:* Backed up the transaction log* Perform full backup of the database* Checked via DBCC OPENTRAN for open or active transaction - there are none* DBCC SHRINKDATABASE After using DBCC SHRINKDATABASE and even DBCC SHRINKFILE using the log file ID, I can see that using DBCC SQLPERF(LOGSPACE) that less than 1% of the 50Gb log file is in use.Initially the log file was created with a 4Gb file size, so I expected that the file would return to this size after the usual steps, but this is not so.I imagine I've missed something simple, so would be most grateful for any advice to get my log file back to its 4Gb original empty size.RegardsSteveThu, 08 Nov 2012 03:27:10 GMTraotor