January 20, 2003 at 7:36 am
Hi Prakash
What a simple solution!
back log helpdesk with truncate_only worked. I can see now my transaction logs shrinked to 51 MB.
Next problem is how to reduce the size of the transaction logs from 18 GB.
If I make the recovery model to simple, will it delete the transaction logs?
Thanks again for your help.
Regards
Chandu
January 20, 2003 at 10:08 am
Hi Chandu,
I think you have not paid any attention to my earlier reponse to you ( dated 01/06 on this topic ). Read it one more time and I hope you should be able to solve your problem in an easy way.
Pasted it below again for you
**********************************
1. Login to Query Analyzer as sa.
2. Change the DB name where you need to shrink the files.
3. Take a good backup of your DB and the transaction Log first. Then run the commands below. The shinking works fine for me most of the times.
=================================
backup log DB_NAME with TRUNCATE_ONLY
dbcc shrinkfile(2, 0)
go
=======================================
-- Chandra Cheedella
January 20, 2003 at 10:43 am
Hi Chandra
Thanks for information. I am sorry that I could not proceed with the suggestion u made earlier.
I got confused with several updates and was not sure of using the parameters in your update. Also, I was away for the last two weeks. So I did not get a chance to check further.
I would be thankful if you could suggest how to reduce the transaction log data files. Presently around 18 GB is allocated to these data files. How do I resize these data files.
Also, can we schedule a job to shrink these transaction logs regularly?
Thanks once again,
Chandu
January 21, 2003 at 1:08 pm
use DB detach and then DB attach to free up the log enteries in the trans log. Take a full backup before and after this.
January 21, 2003 at 1:23 pm
QUestion: If one is using full or bulk log with full backup once a day and multi log dumps, in theory a log shrink should not be needed? If the log keep on growing would'nt a reboot help to force any open transactions closed / cleared?
January 21, 2003 at 1:50 pm
First off there's a lot of good advice that was given, and I don't hold a candle to Steven Jones, but that said it looks like you need a lesson SQL ADmin 101 but not now, so there's a clearer way through the forest. I would never do it this way, but it works and it's safe.
If you still have 500mg open on the database server do the following
On the same server create a blank database
Allocate 400 for the data
and 100 for the log
DTS everything from your existing database to the new one.
Now go home and have a Guinness
If the DTS package is done
Rename the existing production database to something old
Rename the new database to the original production name.
Have everyone test it.
Write click on the original now old database and select delete.
Verify that all the LDF and MDF files are removed.
Use the database planner, be sure to include transaction log backup in the plan.
If this doesn't - My daily rate is reasonable, send a ticket and cover my expenses for couple of days, we'll have a couple of pints and play some snooker.
Cheers,
Three for Barksdale, Case, and Jobs under the sky,
Seven for the states in their halls of stone,
Nine for the Supreme Court Justices doomed to die,
One for the Bill Gates on his dark throne
In the Land of Micorsoft where the Shadows lie.
One Window to rule them all, One Window to find them,
One Window to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
John Zacharkan
John Zacharkan
January 21, 2003 at 1:51 pm
First off there's a lot of good advice that was given, and I don't hold a candle to Steven Jones, but that said it looks like you need a lesson SQL ADmin 101 but not now, so there's a clearer way through the forest. I would never do it this way, but it works and it's safe.
If you still have 500mg open on the database server do the following
On the same server create a blank database
Allocate 400 for the data
and 100 for the log
DTS everything from your existing database to the new one.
Now go home and have a Guinness
If the DTS package is done
Rename the existing production database to something old
Rename the new database to the original production name.
Have everyone test it.
Write click on the original now old database and select delete.
Verify that all the LDF and MDF files are removed.
Use the database planner, be sure to include transaction log backup in the plan.
If this doesn't - My daily rate is reasonable, send a ticket and cover my expenses for couple of days, we'll have a couple of pints and play some snooker.
Cheers,
Three for Barksdale, Case, and Jobs under the sky,
Seven for the states in their halls of stone,
Nine for the Supreme Court Justices doomed to die,
One for the Bill Gates on his dark throne
In the Land of Micorsoft where the Shadows lie.
One Window to rule them all, One Window to find them,
One Window to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
John Zacharkan
John Zacharkan
January 21, 2003 at 6:15 pm
Hi Chandu,
I had the same problems. But I had the liberty of taking the database offline using sp_detach_db (I had only one single tran log file) and then shut down the server and agin bring it up and thn I copied the original tran log to some other location and deleted it from original location.
I used sp_attach_db and the db came up with out any problems and with a new min size tran log. However if you have multiple log files, you might want to try Backup log method as one of the gentleman was suggesting before.
But I don't know what mode your db is in ie full/simple/bulk?
As a good practice, take full backup regularly and also apply backup log statements after that and then shrinking the log should help in your case.
Hope this helps.
Cheers
Gopi
January 21, 2003 at 6:49 pm
There is a great procedure sp_force_shrink_log written by Andrew Zanevsky. Download it at http://www.pinnaclepublishing.com/sq/SQmag.nsf/Index/F36C151BD80FAD8F852568D0007799BE?opendocument
January 22, 2003 at 4:06 pm
Can some of you clarify something for me. Is this still an issue with SQL 2000. I have come across it many times with SQL 7 but I haven't (yet) had the problem of a transaction log refusing to shrink in 2000. I thought Microsoft had fixed the problem by allowing the unused segments to be deleted even if their were a number of used segments near the end of the file.
Oh and just to repeat DBCC loginfo will tell you which segments are being used in your log file.
Nigel Moore
======================
January 23, 2003 at 10:29 am
It happens. Try do a bcp from a text file to a table in a dabatase for more than 1 million records. Then check the size of trans log, remember to refresh. Using sp_force_shrink_log works for me. I use it to shrink the transac log first. Then use the Enterprise Manager to reduce the file size (right click the database name, click shrink database, click files, select dbname_log, select shrink file, type in the minimum size, click OK). I usually end up reducing the size of the database as well by selecting dbname_Data instead of dbname_log after the log file has been reduced in size. It will take a while for a big file, to do either of them, but a window will pop up to announce that the job has been completed.
January 23, 2003 at 12:02 pm
Chandu,
I'm wondering if we are missing the point of your question. It appears you have now tried most of the suggestions and are still not happy. I'm thinking that you need to check one more place.
In Enterprise Manager, expand your server, expand Databases, and right-click on your database and select Properties. Go to the Transaction Log tab. In the window there is a column for 'Space Allocated (MB)'. Check to see what that says and make sure it has the amount of space that your log should INITIALLY hold. This is the amount that is allocated when the log is first created. Your log can NEVER get smaller than this amount, unless you change this number. Next thing to check on the same tab, check how the log expands (File Growth). Do you have it expanding by percent or MB? How much does it expand by? This is the amount it increases whenever a transaction happens AND the allocated space gets full.
For example: Transaction Log Space Allocated is set for 1 GB. File Growth set for 1 GB. Now, the TLog is initally 1 GB, as transactions happen, this space gets full. As it nears full it has to expand, so it expands 1 GB, then transactions continue and it nears full again...and expands another 1 GB and so on.... When the TLog is backed up, shrunk, whatever, this file can only shrink to 1 GB at a minimum. If 1.3 GB of data remains, then it can only shrink to 1.3 GB.
SO, I think at this point it's a matter of checking what initial allocation size and file growth size you have the transaction log set for.
-SQLBill
January 28, 2003 at 11:44 am
Hello,
As all the contributers are suggesting, you can right click on the database or from enterprise manager utility select the database and choose task pad and go to the discription and choose the database to shrink you can also specify at this level which files you want to shrink either log or data files. Then after you perform this task you can run your integrity and optimization jobs. You will see the files shrink after the maintanence jobs run.
Hope this helps.
Thanks,Rearasi
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply