Dbcc shrinkfile log file 2008

It takes a while to complete but it gets our database tidy and fresh before the regular business starts in the morning. It has been working fine for us. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Asked 10 years, 4 months ago. Active 6 months ago. Viewed k times. If DBCC printed error messages, contact your system administrator.

I can only get the log file down to 62 GB. I'm not sure what I'm doing wrong and what I should try next. Improve this question. Greg Sansom Ed Sinek Ed Sinek 4, 10 10 gold badges 51 51 silver badges 80 80 bronze badges. This probably won't be very many people's problem, but it might help a few. Add a comment. Active Oldest Votes. Improve this answer. Salman A k 78 78 gold badges silver badges bronze badges. Radderz Radderz 2, 4 4 gold badges 25 25 silver badges 38 38 bronze badges.

Worked like a charm. This should be marked as the correct answer. I appreciate the background info. I'm still stuck, though, because I think your solution boils down to the solution that the original poster tried and couldn't get to work.

I've tried several times without success, and it seems like the OP tried it also without success. Just wanting to clarify to see if I'm missing something in your answer. Probably a small edge case. The reason I think it is intended , is that the shrink only reduces the size of the log file to the size of the 'used pages' since the last backup; probably to minimize the need to grow the log file, useful for production environment where regular load and backups occur.

For example, if you create a 5-MB file and then shrink the file to 3 MB while the file is still empty, the default file size is set to 3 MB. This applies only to empty files that have never contained data. Used pages in the file's area to be freed are moved to free space in the file's kept areas. The free space at the file's end isn't returned to the operating system, and the file's physical size does not change. The log files are not affected.

The data file is shrunk only to the last allocated extent. Other users can work in the database during file shrinking - the database doesn't have to be in single-user mode. You don't have to run the instance of SQL Server in single-user mode to shrink the system databases. The whole log's target size is then translated to each log file's target size.

However, if part of the logical log resides in the virtual logs beyond the target size, the Database Engine frees as much space as possible, and then issues an informational message. The message describes what actions are required to move the logical log out of the virtual logs at the end of the file.

Because a log file can only be shrunk to a virtual log file boundary, shrinking a log file to a size smaller than the size of a virtual log file might not be possible, even if it isn't being used. The Database Engine dynamically chooses the virtual file log size when log files are created or extended.

A shrink operation is most effective after an operation that creates a large amount of unused space, such as a truncate table or a drop table operation. Most databases require some available free space for regular day-to-day operations.

If you shrink a database repeatedly and its size grows again, then it's likely that regular operations require the shrunk space. All the settings are verified and the application also working fine for a week. Then we noticed a problem with the log file Size Size is increasing and reached around 42 GB and not able to execute any of the basic query and everything break :.

Also please let me know if you need any other information. I am not sure why you say that its due to Azure.. I have gone through same issue. I used simple above solution and its working good for me. Hello Pinal Dave. Would you go out with me? I think your site is amazing. I have a huge poster of you hanging above my bed so I can gaze into your eyes while I str. I would like to know if there is any difference between shrink file through GUI and through script because, I scripted out the shrink file and executed and space was not reclaimed but then again I ran the shrinkfile through GUI and it worked helping me reclaiming a lot of space.

Note: This was a one time thing and the DB was in full recovery mode. He holds a Masters of Science degree and numerous database certifications. Pinal is an experienced and dedicated professional with a deep commitment to flawless customer service. Nupur Dave is a social media enthusiast and an independent consultant. She primarily focuses on the database domain, helping clients build short and long term multi-channel campaigns to drive leads for their sales pipeline.

Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check , we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours. Once you learn my business secrets, you will fix the majority of problems in the future. There are many issues included here, but I would list two major issues: 1 From the setting database to simple recovery, shrinking the file and once again setting in full recovery, you are in fact losing your valuable log data and will be not able to restore point in time.

Core 2 Duo. Leave new baskarlak. Wyatt Wong. What should I do? Luiz Mercante. Need your help at the earliest as I am in a very critical situation.