January 15, 2003 at 2:56 am
I've recently set up a DB and found out that the transaction log has grown massively. How do I reduce the log down to a size i want and keep it there?
I have just set up maintenance plans to backup trans log twice a day and backup once. This should keep the log down.
Thanks in advance
Scott
January 15, 2003 at 4:45 am
First you have to clear all transactions from the TL with a backup of the log.
Then you can use DBCC SHRINKFILE to shrink it in both 7/2000. But in some cases the 7 logs will not shrink, there is a script on this site under the title Force Shrink Log that will help break down the virutal logs in the TL nd shirnk it.
Next open EM and drill to the DB in question, right click and do properties on the DB. On The transaction log tab highlite your file and set the maximum growth size, make sure you give it plenty of room. I do suggest having it alert you some how if the TL is nearly full thou.
January 15, 2003 at 6:03 am
I have been working on a similar problem.
I tried to set up an alert on 'log file size used' to fire a job to backup the transaction log but the condition is never met even when the size is twice the threshold. It appears that the sql alert is not picking up the condition. Any help on this.
ps. I have used a work around in the meantime
January 15, 2003 at 6:36 am
I ran the Shrink File stored proc...
Named..SP_FORCE_SHRINK_ALL_LOGS
It hasn't worked...I still have an enormous file and can't seem to reduce it.
I'm running SQL 7.0 with 2000 Client Tools
Scott
January 15, 2003 at 7:15 am
also it doesn't matter about the transaction log as its currently a test db. If I can delete it all the better
Thanks
Scott
January 15, 2003 at 7:46 am
Detach the database.
delete the log !
Next attach the database with
sp_attach_singel_file_db !
January 15, 2003 at 8:43 am
Just wanted to make sure nothing went wrong if I did detatch and delete the log. Thats now perfect.
Thanks all for your help
Scott
January 16, 2003 at 12:09 am
remember you can only shrink your logfile at "the end". If that last section is in use, you cannot shrink. The "forced" shrinks only fillup the last section during a time and then try to shrink. This can take several runs.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 16, 2003 at 1:48 am
Use this script in query analyser on the database where you want to shrink your transaction log.
Change the @databasename to your database name
declare @fileid as int
declare @databasename as nvarchar(500)
set @databasename='MyDatabaseNAME'
Select @fileid=fileid from sysfiles WHERE filename like '%.LDF%'
BACKUP LOG @databasename WITH TRUNCATE_ONLY
dbcc shrinkfile (@fileid)
January 16, 2003 at 2:13 am
I believe this script came from the db journal website. First run the script for about 10-15 seconds and MANUALLY STOP IT. Then run the first of the 2 comments at the end (drop TABLE virtual_log_file_wrapper, dbcc shrinkfile (yardidev_Log, 200) AS SQL commants (that is, selected and without the "--" in front of them. I have had a lot of success with this, just haven't got around to automating it yet.
/********** Begin SQL *************/
USE MyDatabase
GO
CREATE TABLE virtual_log_file_wrapper (char1 char(4000))
GO
DECLARE @i INT
SELECT @i = 0
WHILE (1 = 1)
BEGIN
WHILE (@i < 100)
BEGIN
INSERT virtual_log_file_wrapper VALUES ('a')
SELECT @i = @i +1
END
TRUNCATE TABLE virtual_log_file_wrapper
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
/* Run the next to lines as commands after
stopping the above manually */
--drop TABLE virtual_log_file_wrapper
--dbcc shrinkfile (MyDatabase_Log, 200)
-- Manually stop this script.
-- Run sp_helpdb to verify the file shrunk.
-- Perform a full database backup so that transaction log backups can occur.
END
/********** End SQL **************/
G. Milner
January 16, 2003 at 2:13 am
I believe this script came from the db journal website. First run the script for about 10-15 seconds and MANUALLY STOP IT. Then run the first of the 2 comments at the end (drop TABLE virtual_log_file_wrapper, dbcc shrinkfile (yardidev_Log, 200) AS SQL commants (that is, selected and without the "--" in front of them. I have had a lot of success with this, just haven't got around to automating it yet.
/********** Begin SQL *************/
USE MyDatabase
GO
CREATE TABLE virtual_log_file_wrapper (char1 char(4000))
GO
DECLARE @i INT
SELECT @i = 0
WHILE (1 = 1)
BEGIN
WHILE (@i < 100)
BEGIN
INSERT virtual_log_file_wrapper VALUES ('a')
SELECT @i = @i +1
END
TRUNCATE TABLE virtual_log_file_wrapper
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
/* Run the next to lines as commands after
stopping the above manually */
--drop TABLE virtual_log_file_wrapper
--dbcc shrinkfile (MyDatabase_Log, 200)
-- Manually stop this script.
-- Run sp_helpdb to verify the file shrunk.
-- Perform a full database backup so that transaction log backups can occur.
END
/********** End SQL **************/
G. Milner
January 16, 2003 at 7:42 am
In regards to detaching the database and then deleting the log file and then attaching the database again, I always rename the logfile first before reattaching. That way if there are any problems reattaching and creating a new log file, you still have the original log file available to reattach with. Just a little safety procedure as I've been burned more than once after deleting the log file before reattaching. Once you've reattached and the new log file has been created and you are sure everything is OK, you can then delete the original log file.
January 16, 2003 at 7:53 am
Thanks everyone for your comments...
and a little extra precaution doesn't go amiss when deleting log files..
Ta
Scott
January 22, 2003 at 7:43 pm
See if this helps.
http://www.sqlservercentral.com/faq/viewfaqanswer.asp?categoryid=2&faqid=154
Soln1 - Have you tried running DBCC SHRINKFILE with first the NOTRUNCATE option to move all the data to
the front of the file followed by running it again with TRUNCATEONLY to cause the unused space to be released?
Soln2 - I have encounter this problem before. Backup your db first, then turn on following database option
auto close auto shrink truncate log on checkpoint
paul
paul
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply