May 26, 2008 at 10:17 pm
I have a test database (ID 2) that is 13 G and has a transaction log of 5 G.
It was in Simple recovery mode.
It now is in Full recovery mode in order to try to shrink the log.
The live database us 14G and less than 1G transaction log.
It is in simple mode.
Both databases receive data from replication servers.
I have tried manual log backups and maintenance plans and:
DBCC SHRINKFILE(2, 1)
BACKUP LOG [test] WITH TRUNCATE_ONLY
DBCC SHRINKFILE(2, 1)
Nothing appears to shrink it.
Any suggestions out there?
Thanks
Graham
Graham Okely B App Sc
Senior Database Administrator
May 27, 2008 at 12:00 am
Assuming 2005, query the sys.database view and check the value of the column log_reuse_wait_desc (or something similar). That tells you the reason that the log space cannot be reused.
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
May 27, 2008 at 12:04 am
SQL 2000
😀
Graham Okely B App Sc
Senior Database Administrator
May 27, 2008 at 12:09 am
Then rather post in the SQL 2000 forum please. Posting in the 2005 forum, you will get just that, a useless suggestion that wasted both out times.
Now... Is the database in question published? Was it ever published?
What does DBCC OPENTRAN return?
What does DBCC SQLPERF(logspace) 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
May 27, 2008 at 12:17 am
opps sorry, I did not notice that it was 2005 forum, forgive me.
The test db subscribes to another database.
I do not know if it was ever published, it is not published now.
DBCC SQLPERF(logspace) give 95% of logspace used for db in question.
DBCC OPENTRAN
Gives
Replicated Transaction Information:
Oldest distributed LSN : (32471:27046:1)
Oldest non-distributed LSN : (32471:29355:1)
Thank you
Graham
Graham Okely B App Sc
Senior Database Administrator
May 27, 2008 at 12:25 am
Firstly, you can set the DB back to simple recovery mode. You can shrink the logs insimple, you just can't back them up.
The open tran info shows that the DB thinks it's published.
(Oldest distributed LSN : (32471:27046:1)
Oldest non-distributed LSN : (32471:29355:1))
That refers to the log entries that have not been passed on to the distributor. Was it restored from a backup of a published DB? Sometimes the published status partially stays with a restored database. Enough to stuff the logs up anyway.
Go to query analyser and run the following (only if you are very sure this DB is not supposed to be published!!!!!!!)
exec sp_repldone NULL -- mark all log entries as distributed
checkpoint -- auto-truncate the log if in simple recovery
Now run DBCC SQLPERF (logspace) and see if the usage has dropped.
The permainent fix is to set up a publication on that DB (it doesn't matter of what) then drop the publication. That should clear out the reminents of replication. A check of DBCC OPENTRAN after that should have no references to replicated LSNs
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
May 27, 2008 at 1:11 am
Gail
Many thanks, that advice has resolved the difficulty.
It turns out the database was a restore form a published database.
Plan
?Backed up the test db
?Placed it into Simple recovery model
?Published a small random table
?Deleted that publication
?Ran DBCC SHRINKFILE(FILE_ID, 1)
?Ran BACKUP LOG [test db] WITH TRUNCATE_ONLY
?Ran DBCC SHRINKFILE(FILE_ID, 1)
?DBCC OPENTRAN gave clear results
The log came from 5GB to 50MB!
Regards
Graham Okely B App Sc
Senior Database Administrator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply