SQL Server Performance Forum – Threads Archive
Shrinkfile
I have used dbcc shrinkfile on a server which worked well to reduce the Transaction log to the desired size. We have specified values to set tlog size to 1024MB and weekend job is failing by stating space used for Tlog is 97% full. I increaesed the size to 2gb. Surprisingly when I look at database properties for Tlog size it shows as 45kb!!Previously i changed value to 1024Mb and removed Can somebody help????
Have you enabled AUTO_SHRINK option for that database? _________
Satya SKJ
Thanks webmaster, apologies for posting in incorrect forum. Satya i confirm auto shrink is false. Any ideas???
I guess I don’t fully understand your problem. Are you saying that you have specified in a job to shring the transaction log file to 1024MB, but instead of this working as you expect, it is shrinking it to 45K? On another issue, why are you shrinking the transaction logs on a regular basis? In most general use, you don’t really want to do this as additional space is needed for the logs, the logs have to grow, which takes extra server resources. The same applies when you shring the log. Personally, I only shrink longs that have accidently become very large because of a poorly-designed query that expanded in way past its normal size. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Thanks brad.
A while ago I have increase transaction log to 4gb and finished the job which was successful.
Then executed dbcc shrinkfile to reduce the size to 1GB which was successful. We do maintain a standard of not leaving AUTO SIZING for database. For which I restricted the filesize to 1GB for transaction log. Surprisingly it reverts to 45KB showing under EM for this database, every 1st we need to run a report job which should no more take transaction log to 1000mb. But when due to this changes on tlog file to 45kb and AUTO GROW is disabled the job is failing. I executed dbcc shrinkfile only twice which was 2 months ago, and now also the file is showing as 45kb. Any help??? Thanks for your time.
Thanks for the additional information. Unfortunately, I don’t have an answer. I have never seen this before. If I was facing this problem, I might consider the following, which is from a tip on my website. Assuming that maybe there is a problem with the log, the following action should correct it. Tip: According to Microsoft, the way to shrink a log file is to use DBCC SHRINKFILE. This process is painfully slow and inefficient. Even after running the procedure numerous times, you may not get much free space. Here’s another way to shrink your logs that is guaranteed to work every time. First, back up the database and then detach (sp_detach_db) it (you will have to bring the database down to do this). Next, delete the log file and then re-attach (sp_attach_db) the database, not providing the old log file location from within the sp_attach_db command. This will create a new log file with the old log file name in the old location with default size i.e. 512 KB. To make sure there is no problem during the operation, the old log file can be renamed and kept until the database is reattached successfully. This provides a backup plan if for some reason SQL server fails to attach the database without the old log file. This trick won#%92t work if the database has more than one log file, but if you need to, you can alter the database so that it only has a single log file, while will allow you to perform the above steps. After carrying out the detach and attach database activity, the database can be again altered to add more log files. [7.0, 2000] Added 2-24-2003 Contributed by Gaurav Bindlish
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
]]>