February 6, 2006 at 9:25 am
Hey all,
I have a SQL Server 7 DB that has the "Truncate log on Checkpoint" and "Select into/ bulk copy" options set, however, the log file is not going away. it is currently at 7595 MB used, and I really do not care about this log file, however, it will not go away. I have tried all sorts of shrinks, backups, checkpoints, and nothing. Any help would be, as usual, greatly appreciated.
Cory
-- Cory
February 6, 2006 at 3:08 pm
Sometimes, as a last resort, I have detached the database, renamed the offending LDF file, and then reattached the database... omitting the LDF in the attach command. This builds a new LDF with the same name as previous. If it works ok then delete the 7gb LDF.
nb. this works in SQL2000 but not having a SQL7 environment or books on hand I'm just guessing it may work.
Cheers,
- Mark
February 6, 2006 at 3:17 pm
I just answered this question on SQL Server 2005/Administration forum for the topic on DBCC Shrinkfile.
see
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=146&messageid=256050
Detaching /attaching to get a nice new log worked in SQL Server 7 for me with sp_attach_single _file_db when DBCC Shrinkfile did not work.
Regards,Yelena Varsha
February 7, 2006 at 2:05 am
Once the log has grown, checkpointing won't shrink it physically, the inactive portion still occupies space at the filesystem level. Use dbcc shrinkfile.
I would issue a 'backup log with truncate_only' for this database then 'dbcc shrinkfile'. detach/attach seems a very heavy handed approach.
February 7, 2006 at 8:02 am
There is another work around that's been out there for a while; you can run a dummy update statement against a non-production table. Here's an excerpt:
Run:
DBCC SHRINKFILE ( <logfile> , TRUNCATEONLY )
BACKUP LOG <database> WITH TRUNCATE_ONLY
Create a dummy table and insert a record to ReduceLogFile
CREATE TABLE ReduceLogFile (
MyField VARCHAR(10)
, PK INT )
INSERT ReduceLogFile (PK) VALUES (1)
GO
Run the following script against ReduceLogFile
SET NOCOUNT ON
DECLARE @Index INT
SELECT @Index = 0
WHILE (@Index < 30000)
BEGIN
UPDATE ReduceLogFile
SET MyField = MyField
WHERE PK = 1 /* Some criteria to restrict to one row. */
SELECT @Index = @Index + 1
END
SET NOCOUNT OFF
Once the log has been truncated, the earlier portions of the log can be reused. So, once you start running transactions, SQL Server will use space at the beginning of the file, rather than growing the physical file.
This causes the MinLSN with a staus = 2 to wrap around to the unused portion of the physical log file. Virtual log files are then marked as unused and will be deleted when a DBCC SHRINKFILE and BACKUP LOG are run. The deletion of the virtual log files is what causes the reduction of the physical log file size. You can adjust the number of times the loop runs if 30000 doesn’t reduce the log enough.
Run the following commands again:
DBCC SHRINKFILE ( <logfile> , truncateonly )
BACKUP LOG <database> WITH TRUNCATE_ONLY
Try this approach and see what happens. You may have to run the update statement more than once to get the MinLSN to wrap around to the unused portion of the log. This has worked for me in the past.
HTH
Mark
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply