SQL 2008 standard x64 help - unable to truncate / re-create 2 log files

Question

SQL 2008 standard x64 help - unable to truncate / re-create 2 log files

Alex V

SSC-Addicted

Points: 462
More actions
March 31, 2010 at 12:03 am

#386611

Gentlemen,
Need help.
Here is a problem I have on my hands: I have a database (75mb) with two log files. They came from 2005 express box with some data corruption, rebuild on the original box, but growing a huge log. Original log is now 25gb. Second one is 700mb.
Any attempt to dbcc shrinkfile or anything else I found online did not work. Backup does not truncate the logs. Again, these files came from a machine with data loss.
I have tried to detach and re-attach the DB without log files, it fails, stating that it can't re-create a new log file since there were 2 present.
Attempts to truncate fail. Attempt to zero out second file by dumping data into first one fail. Supposedly there are some unreplicated transactions, which I don't care much for.
At this point, all I would like to do is to get rid of the corrupted logs and start fresh, setting the new log never to grow out of a certain size.
This DB does not have a lot of transactions, so even truncating logs daily or after each transaction would be OK.
Any help / idea / suggestion / laugh on my account is greatly appreciated, since I'm sadly cornered here with little to none support from a software vendor.
Thanks again,
Alex

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135510 More actions · Answer 1

1) keep your copy of the original files on a safe zone (if you haven't, start with a full backup of your current database )

2) you managed to actually attach the db (with its 2 log files) in the sql2008 instance ?

(you'll not be able to use these attached files again for any lower version of sqlserver !)

3) What kind of error messages did you receive ?

4) are there any other connections to this database ?

5) Which recovery model is the db using ?

6) chances are - because of how sqlservers log files are working - you need to repeat your shrinkfile sequence a number of times before your log file has been rolled over and can be shrinked down to the desired size. (check BOL "Transaction Log Management")

BTW:

- Gail published a very nice article on transaction logs at SSC: http://www.sqlservercentral.com/articles/64582/

- Also check : http://msdn.microsoft.com/en-us/library/ms189493.aspx ( paragraph on Shrinking a Log File )

This is the script I use whenever I really have to shrink a log file.

THIS SCRIPT IS NOT INTENDED FOR SCHEDULED USAGE !! READ BOL and the urls !!

Play it safe : start with a full db backup and make another one after the operation.

--INF: How to Shrink the SQL Server 7.0 Transaction Log

-- SQL7 http://support.microsoft.com/support/kb/articles/q256/6/50.asp?id=256650&SD

-- SQL7 http://www.support.microsoft.com/kb/256650

-- SQL2000 http://support.microsoft.com/kb/272318/en-us

-- SQL2005 http://support.microsoft.com/kb/907511/en-us

-- SQL2008 http://msdn.microsoft.com/en-us/library/ms189493.aspx

-- ----- 'BACKUP LOG ... WITH TRUNCATE_ONLY' no longer supported (sql2008)

-- select db_name()

-- select * from sysfiles

-- THIS SCRIPT IS NOT INTENDED FOR SCHEDULED USAGE !! READ BOL and the urls !!

Declare @OriginalRecoveryMode varchar(50)

Select @OriginalRecoveryMode = convert(varchar(50), DATABASEPROPERTYEX(DB_NAME(),'Recovery'))

Print 'OriginalRecoveryMode: ' + @OriginalRecoveryMode

Declare @AlterDb varchar(1000)

If @OriginalRecoveryMode <> 'simple'

begin

select @AlterDb = 'alter database [' + DB_NAME() + '] set recovery simple;

print ''Recovery model altered for shrink;'''

exec (@AlterDb)

end

SET NOCOUNT ON

DECLARE @LogicalFileName sysname,

@MaxMinutes INT,

@NewSize INT

-- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. ***

SELECT @LogicalFileName = 'Praktijk_Log2', -- Use sp_helpfile to identify the logical file name that you want to shrink.

@MaxMinutes = 10, -- Limit on time allowed to wrap log.

@NewSize = 250 -- 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(7000)

SELECT @StartTime = GETDATE(),

-- @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

@TruncLog = 'use [' + db_name() + ']

DBCC SHRINKFILE (' + @LogicalFileName + ', ' + convert(varchar(15),@NewSize) + ' , TRUNCATEONLY) WITH NO_INFOMSGS'

-- 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

If @OriginalRecoveryMode <> 'simple'

begin

select @AlterDb = 'alter database [' + DB_NAME() + '] set recovery ' + @OriginalRecoveryMode + ' ;

print ''Recovery model restored to [' + @OriginalRecoveryMode + '] after shrink;'''

exec (@AlterDb)

end

PRINT '*** Perform a full database backup ***'

SET NOCOUNT OFF

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Alex V (3/31/2010)
Gentlemen,

I notice this is not addressed to me, but...

What is the value of log_reuse_wait_desc in sys.databases for this database?

What does DBCC OPENTRAN return?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alex V SSC-Addicted Points: 462 More actions · Answer 3

Sorry, Gail, I didn't mean to be gender specific. Here in AL a good half of the ladies would get offended: "who exactly are you calling a lady?" LOL.

Here is the dbcc opentran:

Replicated Transaction Information:

Oldest distributed LSN : (0:0:0)

Oldest non-distributed LSN : (62563:25:1)

Not sure how to check on log_reuse_wait_desc.

Alex V SSC-Addicted Points: 462 More actions · Answer 4

ALZDBA,

1. The DB I have here is a copy of a live DB on a production box, which had been a subject to a power failure and data corruption on OS level. It still runs, but some of the instrumentation is out.

Production box is running SQL express 2005.

We're replacing it with a new box with SQL standard 2008.

So, I need to find a way to migrate the DB to the new box without migrating the problem logs.

2. Yes, restored from the backup with no problems. Changed to SQL 2008 100% compatibility with no problems. Can detach and attach with no problems, as long as both logs are there.

3. attaching without logs:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Could not open new database 'DirectorySQL'. CREATE DATABASE is aborted.

File activation failure. The physical file name "D:\MSSQL\DATA\temp\DirectorySQL_Log.LDF" may be incorrect.

File activation failure. The physical file name "D:\MSSQL\DATA\temp\DirectorySQL.ldf" may be incorrect.

The log was not rebuilt because there is more than one log file. (Microsoft SQL Server, Error: 1813)

Trying to "remove" second file:

Drop failed for 'DirectorySQL_log2'

The file 'DirectorySQL_log2' cannot be removed because it is not empty. (Microsoft SQL Server, Error: 5042)

DBCC SHRINKFILE (on a second log):

Cannot shrink log file 3 (DirectorySQL_log2) because of minimum log space required.

curr size min size used pages estim. pages

5 3 84848 256 84848 256

4. None I'm aware of. Created a new blank DB and specified it for the appl. use. So, any manipulations with this one do not affect my appl., meaning there are no legit connections.

5. It was using simple. I changed it to full to run some commands, and switched it back.

6. I have tried running shrinkfile several times, as per MS article, but got various error trying to follow it. Will be happy to try it again.

Thanks,

Alex

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Alex V (3/31/2010)
Sorry, Gail, I didn't mean to be gender specific. Here in AL a good half of the ladies would get offended: "who exactly are you calling a lady?" LOL.

I can understand that, but I'm pretty suer they'd be equally offended by being called a gentleman....

Here is the dbcc opentran:
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (62563:25:1)

That's what I suspected. Is this database supposed to be replicated? Do you have any transactional replication publications created?

Not sure how to check on log_reuse_wait_desc.

Query the sys.databases view.

Don't try any more truncation or shrink tricks. They won't work, there's some bits of replication configured, once we get rid of those everything will be fine.

Do not attempt to delete the transaction log, that's the fastest way to really mess up a database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150468 More actions · Answer 6

If all you want to do is to get rid of the logs, the fastest way is to use the CREATE DATABASE...FOR ATTACH_REBUILD_LOG statement.

sp_attach_single_file_db is limited to databases that only had one log file. The above syntax can cope with multiple log files.

Note that sp_attach_single_file_db and sp_attach_db are deprecated, in part because of this.

See CREATE DATABASE for full syntax and additional information.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Alex V SSC-Addicted Points: 462 More actions · Answer 7

Gail,

After using Google to find the way to query sys.databases (you see, I'm just learning to steer this ship), here is what I see:

1.NOTHING

2. ACTIVE_TRANSACTION

3. NOTHING

4. NOTHING

5. REPLICATION

6. NOTHING

7. NOTHING

8. NOTHING

9. NOTHING

Where 2 is a newly created appl. database, and 5 is the one not working right.

If I understand it right, by default my DB should be set for active transactions (?), but it is set for replication.

I guess, it's like learning a foreign language...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Paul White NZ (3/31/2010)
If all you want to do is to get rid of the logs, the fastest way is to use the CREATE DATABASE...FOR ATTACH_REBUILD_LOG statement.

Yes (providing the database is shut down cleanly before the logs are deleted), however here it'll be a temporary fix. The log will simply grow again because SQL thinks that it's needed for replication.

Not that deleting the transaction log is ever a recommended course of action.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Alex V (3/31/2010)
If I understand it right, by default my DB should be set for active transactions (?), but it is set for replication.

Nope. There are various reasons why log space is not reusable. Active transactions is one (and it's a normal one), there are several others, including replication.

Does the database (the one with the big log file) have any transactional replication publications?

From management studio, connect object explorer to the server in question, expand out the replication folder and local publications. Are there any entries in there?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150468 More actions · Answer 10

GilaMonster (3/31/2010)
Paul White NZ (3/31/2010)
Yes (providing the database is shut down cleanly before the logs are deleted), however here it'll be a temporary fix. The log will simply grow again because SQL thinks that it's needed for replication.

Sure thing. Just making a general observation there. He will still need to run EXEC sys.sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1;

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Paul White NZ (3/31/2010)
He will still need to run EXEC sys.sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1;

If this is one of those half-configured replication problems (which I suspect it is), that still may not fix the problem permanently. I've seen situations where, OPENTRAN returns the distributed and non-distributed LSN info, but running sp_repldone returns an error saying that the DB is not replicated

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alex V SSC-Addicted Points: 462 More actions · Answer 12

Gail, there are no objects on either new or production servers under "replication" besides empty folders "Local Subscriprions" (on both) and "Local Publications" (only on a new 2008 server).

Paul White SSC Guru Points: 150468 More actions · Answer 13

GilaMonster (3/31/2010)
Paul White NZ (3/31/2010)
He will still need to run EXEC sys.sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1;
If this is one of those half-configured replication problems (which I suspect it is), that still may not fix the problem permanently.

Ok. You seem to be fully across this one...I'll leave you to it.

edit: to avoid posting again: The root cause is often down to not using the sync_with_backup option.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Paul White NZ (3/31/2010)
GilaMonster (3/31/2010)
Paul White NZ (3/31/2010)
He will still need to run EXEC sys.sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1;
If this is one of those half-configured replication problems (which I suspect it is), that still may not fix the problem permanently.
Ok. You seem to be fully across this one...I'll leave you to it.

I ran into this problem so frequently at the previous company that I had a saved script to do the fix. Never could dig up the root cause though

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL 2008 standard x64 help - unable to truncate / re-create 2 log files

Cookies on SQLServerCentral