February 2, 2004 at 3:19 am
Quick one, I have ran out of disk space on my server but my transaction log is 20gb. I want to truncate my log but the command I use is 'DUMP TRAN MWR_LIVE_DB_INCR_TEST WITH TRUNCATE_ONLY'
February 2, 2004 at 3:23 am
sorry pressed enter half wat through writting this post. cont.... This command is saying cannot back up the log. But i do not want to back it up just truncate it can anyone give any help thanks Paul
February 2, 2004 at 7:08 am
Are you running SQL 2000 or 7.0? If 7.0, do you have "truncate log on checkpoint" on or off? If 2000, is your recovery model full or simple (or bulk logged)? It sounds like you have "truncate log on checkpoint" on or recovery set to simple. The command you entered "dump tran..." is a backup command, but you have set the parameters to simply dump without backup (truncate). Probably, if I'm correct about your settings, you just need to SHRINK the log, rather than truncate. Truncating simply removes data from the log. It doesn't de-allocate disk space. SHRINK will de-allocate unused space. Check Books Online for syntax.
February 2, 2004 at 10:47 am
try "truncate xxx with no_log", then shrink the log and run a backup.
February 2, 2004 at 11:14 am
I would go with
BACKUP LOG MWR_LIVE_DB_INCR_TEST WITH NO_LOG
GO
USE MWR_LIVE_DB_INCR_TEST
GO
DECLARE @FILENAME VARCHAR(200), @SQL VARCHAR(1000)
SELECT @FILENAME = RTRIM(NAME) FROM SYSFILES WHERE GROUPID = 0
SET @SQL = 'DBCC SHRINKFILE ('''+@FILENAME+''', 128, TRUNCATEONLY)'
EXEC (@SQL)
This will backup the database log and then shrink the log file. You could actually encapsulate this easily into a stored procedure where you just have to pass in the parameter of the database name.
February 2, 2004 at 11:21 am
Also if the shrinkfile doesn't work, try this:
http://www.sqlservercentral.com/scripts/contributions/26.asp
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply