Detach then attach db to drop log file

[mflancour]

I had a huge log file that i had not real use for since my data was static. So I detached it. When i did this an error occured saying i needed to back it up first and make teh log file smaller.....ok problem is it still detached, and now it wont re-attach
"Could not open database 'db1'. Create Database is aborted. Device activation error. The physical file name may be incorect.

I have a backup copy of this specific database on Symantec exec. But...and don't laugh at me..I've never actually had a database fail before, so I don't even know for sure how to restore it.

 

[ptheriault]

you should be able to re-attach it without the log. It should create a new one.

- Paul
- Database performance looks fine, it must be the Network!

 

[mflancour]

That's what I thought, but it's giving me that error.

 

[ptheriault]

When you attach it does it find the log file? if it does, delete or move the log and try it again.

- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

Or, tell it to look in a different place for the log in the Attach dialog box, which should fix the problem.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mflancour]

Well, I couldn't get it to attach again with that file, so I restored it from backup and now have teh database again. Problem now is I still have that huge log file. so, my next stupid question is; how exactly does the backup and trunkate thing work? Is it possible to just truncate without backing it up? I ask because we truely have no need for the log files for that database. so much so, that if possible i'll be investigating how to totally get rid of them to avoid future growth problems, but that's a different issue.

 

[ptheriault]

There are a few things you can do here.
1 set the db recovery model to simple. this will cause the log to truncate on check point. If you do not need to recover this db to a point in time this is the a great option. This option will prevent your log file from growing to a size that is now.

2. Backup your TLOG and then run DBCC SHRINKFILE(filename, size)

3 Backup your TLog and Run this script. It will run DBCC SHRINKFILE You will need to set @NewSize and @LogicalFileName variables.

--select * from sysfiles
--drop table DummyTrans
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT

-- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. ***
--USE FDMQA -- This is the name of the database for which the log will be shrunk.
SELECT @LogicalFileName = 'Avenue_log', -- Use sp_helpfile to identify the logical file name that you want to shrink.
@MaxMinutes = 2, -- Limit on time allowed to wrap log.
@NewSize = 10 -- in MB

-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)


-- Wrap log and truncate it.
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) -- the log has not shrunk
AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END -- update
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF


- Paul
- Database performance looks fine, it must be the Network!

 

[mflancour]

Thanks...
So, no way to trunkate without backing up?

 

[ptheriault]

yes, you have to set the recovery model to simple. But most likely you will have to enter some bogus transactions to get the db to checkpoint. You can set it back to full if you need to.

Change the recovery model from the options page of db properties.

- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

Try this:

BACKUP LOG <MyDB> WITH TRUNCATE_ONLY

Per Books Online:

NO_LOG | TRUNCATE_ONLY

Removes the inactive part of the log without making a backup copy of it and truncates the log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.

After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE.

NO_TRUNCATE

Allows backing up the log in situations where the database is damaged.

Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

Oh, FYI, that is from BOL in SQL 2000. SQL Server 2005 shortly will not support the No_Log and Truncate_Only option(s) for log backups.

NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and truncates the log by discarding all but the active log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.

Note:
This option will be removed in a future version of SQL Server. Avoid using it in new development work, and plan to modify applications that currently use it.

Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"