August 20, 2008 at 8:20 pm
HI,
One of our database' log file grows to 250 Gb resulting in almost out of disk space, so i took the backup of transaction log. Then i go to the shrink option and it was showing that 99% of log is free, so i start shrinking the file with the option of "release unused space", it was successfully completed. But after this process when i saw the size of Log file it was still 250 Gb. Any idea?
did i miss some thing?do i have to explicitly run DBCC CHECKPOINT before taking the transaction log backup?or do i have to do something else to release the free space?
Thanks,
Usman
August 20, 2008 at 8:31 pm
Use the "reorganize" option.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 21, 2008 at 1:07 am
See if this document helps.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 21, 2008 at 7:17 am
1.Shrink the log using DBCC Shrinkfile
2.Backup log with truncate only option.
August 21, 2008 at 7:21 am
Khwaja Arshaduddin (8/21/2008)
1.Shrink the log using DBCC Shrinkfile2.Backup log with truncate only option.
Actually, if this is the path you want to take, this is the proper order:
1) Backup log with truncate only option.
2) Shrink the log using DBCC Shrinkfile
3) Full backup of the database. This is critical as #1 has broken your log chain.
And this should not be considered routine maintenance by you. Setup your log backups on a more frequent schedule to keep the log size to a minimum and/or get more disk space so you're not faced with this scenario on a regular basis.
-- You can't be late until you show up.
August 21, 2008 at 7:43 am
1) Backup log with truncate only option.
2) Shrink the log using DBCC Shrinkfile
I exactly follow these steps, but my problem is file size is not reduced even after these steps.
Is there any other that i missed?
August 21, 2008 at 7:55 am
Is replication running? Execute DBCC OPENTRAN and post the results.
Edit - Did you read the link the Ray Mond posted? If may give a clearer picture of what's actually occurring.
-- You can't be late until you show up.
August 21, 2008 at 7:58 am
NO replication, no open transaction.
I tried shrinking from database option, and also from dbcc shrinkfile. But when i tried using dbcc shrinkfile it gave me error "Cannot shrink log file 2 (CRM_StagingArea_Log) because all logical log files are in use"..... i checked using dbcc opentran but there are no open transactions.
August 21, 2008 at 8:05 am
Check out Paul Randal's blog on this:
-- You can't be late until you show up.
August 21, 2008 at 8:08 am
Excellent information on Ray Mond's link. I guess he has pointed to the problem very precisely. But one thing that i couldnt understand in that article is "active log record is in the second to last virtual log file"......what does this means?
August 21, 2008 at 9:04 am
active log record is in the second to last virtual log file
If you look at the screenshot before the paragraph that contained that statement, you will see that there were 8 virtual log files in the transaction log. Only the second to last virtual log file was in use (row 7, status = 2). Let's call this virtual log file 7.
When I attempted to shrink the log down to 2 MB, it failed because virtual log file 7 was in use. Only the last virtual log file (virtual log file 8) was removed because it was not used. Thus, the transaction log could only be shrunk from ~6 MB to ~4.7 MB. At the same time, SQL Server padded the transaction log with dummy transactions, so that the last active transaction now lies in virtual log file 2.
When I then backed up the transaction log, virtual log files 7 and 1 are truncated. Attempting to shrink the transaction log down to 2 MB now succeeds, because the virtual log files that needed to be removed to attain that size were no longer in use (virtual log files 6 and 7).
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 21, 2008 at 9:09 am
Thanks alot all, specially Ray, hope this will solve the problem. I ll try this tonight can't take the backup right now coz of production environment.
Thanks,
usman
August 21, 2008 at 2:20 pm
All of the stuff everyone else has posted is spot on, but I've never gotten it to work to my satisfaction. Here's what I do.
1) Take a full backup of the database
2) Change the Database Recovery Model to Simple - This will effectively shrink the T-log to nothing
3) Change the Database Recovery Model back to Full (of Bulk Logged)
4) Set my growth parameters for the DB growth
5) Determine how frequently I need to backup the T-log to ensure that it does not grow to humongous proportions and setup a Maint Plan to handle that
Granted, this is not the best plan to perform in the middle of the business day when all your users are in the data, but it has worked effectively for me.
Regards, Irish
August 21, 2008 at 2:25 pm
I have used this technique as well, and yes you are right it always worked for me as well. But i ll try Ray's technique tonite. Ray one thing i was wondering is which TLog backup should be we keep then, the first backup that removed only the last logical file, or the second backup that shrinks the size??
August 21, 2008 at 4:24 pm
DBCC LOGINFO should show the status of the log file.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply