November 9, 2010 at 10:22 am
We have a database which is just a backup/snapshot of some data. The log grew very large while loading the data (about 4.2 gig), and will only shrink to 562 MB. I cannot get it to shrink any smaller, needing it to be minimized as there will no more updates/inserts. I have researched many, many internet articles and forums, and tried everything suggested.
The recovery model is Simple. I just now even tried running a database backup, since none had been done yet.
This is the output from DBCC LOGINFO:
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
2 294846464 8192 72 0 64 0
2 294846464 294854656 73 2 64 0
As you can see, the status of the 2nd 'file' is 2, which apparently is keeping the log from truncating. There are no transactions running, and I have manually run "checkpoint" several times.
I even, per a suggestion, created a table, and have run a script to update it 100,000 times several times, and 1,000,000 times once, to no avail. The output from DBCC LOGINFO doesn't change.
HELP!
I have a couple of other similar databases needing this shrinking.
November 9, 2010 at 10:36 am
It's possible that your VLFs are blocking the shrink. It's possible (I think) that a sector in use near the end of the file will prevent shrinking and you need to have it reorganize the file first. Also, if I'm not mistaken, the file can't shrink to smaller than its "original size" (the size it was created at).
You could keep hunting around for ways to shrink it, possibly with a re-org. Another possibility is to take the database offline, move the log file, and then use "create database from file" to create a new, smaller log file. Take a look at the syntax for "create database" to see how that works.
If you decide to try that, make sure you have a valid backup first, just in case.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2010 at 1:38 pm
Check Log_Reuse_Wait_Desc from the folowing query
SELECT [name], recovery_model_desc, log_reuse_wait_desc
FROM sys.databases;
Also try .........
SSMS --> Rt Click on the DB --> Tasks --> Shrink --> Files --> File Type = Log --> Select "Reorganize pages before releasing unused space " and Shrink file to 50 MB.
Please note that this reorganises the data in the file and may have severe user impact.
Thank You,
Best Regards,
SQLBuddy
November 9, 2010 at 2:43 pm
GSquared (11/9/2010)
It's possible that your VLFs are blocking the shrink. It's possible (I think) that a sector in use near the end of the file will prevent shrinking and you need to have it reorganize the file first. Also, if I'm not mistaken, the file can't shrink to smaller than its "original size" (the size it was created at).
DBCC shrinkfile will allow you to shrink to below the original created size, shrinkdatabase will not. This looks to me like a VLF issue, you appear to only have two large vlfs, a result of the sizings used when the log was first created. The only way to get the active VLF moved to the first one is to wrap it round by running a script inserting into a dummy table until the active vlf is full and constantly attempting shrinks. sounds like you have already tried this without success (row sizes not big enough?). Anyways you will only get this log down to about 220MB so is it worth it, especially if disk space not an issue?
Another possibility is to take the database offline, move the log file, and then use "create database from file" to create a new, smaller log file. Take a look at the syntax for "create database" to see how that works.
If you decide to try that, make sure you have a valid backup first, just in case.
This works ONLY if the database is in simple mode - offline the database, move the log file somewhere, online the database. SQL will create a log file for you. Any other mode database would become suspect. As GUS said, do not do this without a backup available and not a recommended fix.
As per other post, worth a check of log_reuse_wait_desc first, especially if source database was replicated
---------------------------------------------------------------------
November 9, 2010 at 3:19 pm
Follow this steps:
1.enable truncate log on checkpoint in sp_configure.
2. Change recovery model to bulk-logged.
3. have regular log backup based on how busy your server.
4. Shrinking log is not good.
If you in simple you can not to do any thing when it grown to certain level and have an open tran running.
November 9, 2010 at 4:48 pm
pavan_srirangam (11/9/2010)
Follow this steps:1.enable truncate log on checkpoint in sp_configure.
2. Change recovery model to bulk-logged.
contradictory advice. trunc log on chkpoint is SQL 7 version of simple mode.
simple you can not to do any thing when it grown to certain level and have an open tran running.
what level? Simple mode does not make the issue of an open tran any different.
---------------------------------------------------------------------
November 9, 2010 at 5:06 pm
pavan_srirangam (11/9/2010)
Follow this steps:
1.enable truncate log on checkpoint in sp_configure.
2. Change recovery model to bulk-logged.
contradictory advice. trunc log on chkpoint is SQL 7 version of simple mode.
simple you can not to do any thing when it grown to certain level and have an open tran running.
what level? Simple mode does not make the issue of an open tran any different.
---------------------------------------------------------------------
proud member of the 'et al' fraternity.
I'm sorry instead sp_dboption I specified sp_configure
But its not only sql 7 it also works in till today version.
I faced that problem when I have my db in simple.
The log file went on growing and unable to change recovery model at that point in time because it won't let you to take log backup when its in simple.
Also, shriking always is not good and it won't work alway as far as I know.
Shrinking can occur only while the database is online and, also, while at least one virtual
log file is free. In some cases, shrinking the log may not be possible until after the next
log truncation.
Reference:here
November 9, 2010 at 5:11 pm
The main thing is that you probably have a VLF at the end of the log file. What you need to try to move the log. I'd think that your table probably did that, but maybe not.
What I've used in the past is this script: http://www.sqlservercentral.com/scripts/Maintenance+and+Management/30026/
November 10, 2010 at 3:07 pm
Finally -- success!
Thanks to Steve Jones. He wrote me "What about detach, rename log, attach with rebuild?"
So, I researched this for a few minutes, having not heard of it.
Then, here is what worked.
Checkpoint (the database recovery model is Simple)
Detached the database.
renamed the physical log file so that the attach would not find it.
Then,
CREATE DATABASE [ABC_2] ON
( FILENAME = N'O:\SQLData\ABC_2_data.MDF' )
FOR ATTACH_REBUILD_LOG
It created a log file of only 1MB (actually 504 KB on disk).
Thanks to everyone who tried to help.
November 10, 2010 at 3:57 pm
November 11, 2010 at 4:07 am
offline,rename,online would have been quicker and the better solution in this case. 😉
---------------------------------------------------------------------
November 11, 2010 at 6:34 am
George,
You're right!
For some reason, the first time I tried to offline it, that process just hung, and wouldn't even come back with an error except to say it timed out, so I abandoned that. Do you know what would prevent taking a database offline?
Offline / rename or move / online, is the simpler solution.
November 11, 2010 at 6:55 am
tim.steorts (11/11/2010)
George,You're right!
For some reason, the first time I tried to offline it, that process just hung, and wouldn't even come back with an error except to say it timed out, so I abandoned that. Do you know what would prevent taking a database offline?
Offline / rename or move / online, is the simpler solution.
probably someone connected to it
---------------------------------------------------------------------
January 17, 2020 at 3:11 pm
So, I am discovering this article after 10 years. It was still immensely helpful.
I've modified SQLBuddy123's query somewhat to include info about Change Data Capture (for you fans of Oracle Golden Gate).
Seems I can't shrink several of my debug database logfiles because the original production copies they came from had CDC/GG
associated. Still trying to figure out how to correct that since cdc doesn't appear to really be present in debug.
SELECT [name], recovery_model_desc, log_reuse_wait_desc, is_cdc_enabled
FROM sys.databases
order by log_reuse_wait_desc desc
I also found a Paul Randle article to be helpful ... leads me to think CDC on production died when the backup I took was running?
https://www.sqlskills.com/blogs/paul/replication-preventing-log-reuse-but-no-replication-configured/
A colleague found additional info from MVP Gail Shaw that says Replication was taken down improperly.
She recommends setting up replication again on that server, and creating a publication in the Db having the issue. Publish any table in that,and then completely remove the replication again.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply