January 2, 2003 at 9:01 am
Hi
Transaction logs increased up to 16 GB. How to shrink transaction logs? I would like to restrict transaction logs growing more than 1 GB. How can I achieve this?
Request for information.
Thanks in advance,
Chandu Cheeti
Ireland
January 2, 2003 at 9:31 am
Right click the database in Enterprise Manager, select Shrink. choose the "Files" button and select your log file(s) and shrink them.
Or look in our script library. There is a script I submitted (but did not write) that allows you to shrink a log by writing dummy transactions and then shrinking the log.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 2, 2003 at 9:39 am
Hi Steve
Thanks for the prompt response.
I would be thankful if you could clarify the following:
1) Will log file size reduce after shrinking the log file?
2) What are the implications during and after shrinkng the log file.
3) What do you recommend for the size of transactions log and is there any way to monitor?
This is production database and we take backup everyday night.
Thanks once again,
January 2, 2003 at 9:48 am
Hi
It is mentioned in the SQL Server Book Online that "You should truncate the log file prior to shrinking the file to reduce the size of the logical log and mark as inactive virual logs that do not hold any part of the logical...."
Regards
Chandu
January 2, 2003 at 10:44 am
The following statement did not succeed:
Dbcc shrinkfile (helpdesk_log, 5000)
Output:
DBID 7
FileID 2
Currentsize 2068464
Minimum size 96000
Usedpages 2068464
Esttimatedpages 96000
The Database size is 600 MB while Transaction logs grown up to 16 GB. How do I truncate the log files?
Can we reduce the transaction file size?
Thanks in advance,
Chandu
January 2, 2003 at 1:05 pm
Ref: Books OnLine (BOL), go to index tab, type in Transaction Log. Then review the following sub-sections: backing up, shrinking, truncating.
If you aren't doing Transaction Log backups, then run the following command to truncate the log.
BACKUP LOG mydatabasename
WITH TRUNCATE_ONLY
-SQLBill
January 2, 2003 at 1:46 pm
You may have virtual log segments that are not empty and preventing the shrinking. Use this script to help:
http://qa.sqlservercentral.com/scripts/contributions/26.asp
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 6, 2003 at 7:22 am
Chandu, here is a script that you can run to compact all the databases and their log files in one go.
*****************************
declare db_cur cursor
for
select name from master.dbo.sysdatabases order by name
declare @S varchar(255)
declare @db varchar(255)
open db_cur
fetch next from db_cur into @db
while @@fetch_status=0
begin
print 'Shrinking '+@db+' ...'
set @S='dbcc shrinkdatabase ('+@db+')'
exec(@s)
set @S='backup log '+@db+' with truncate_only'
exec(@s)
fetch next from db_cur into @db
end
close db_cur
deallocate db_cur
*********************************
Run it atleast twice as I have seen that SQL server does not compact some databases to the maximum extent in single execution of this script.
January 6, 2003 at 8:58 am
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 ct_vaconsult with TRUNCATE_ONLY
dbcc shrinkfile(2, 0)
go
=======================================
-- Chandra Cheedella
January 6, 2003 at 9:32 am
Chandu,
If your database is 600mb and your transaction log has grown to 16gb, and you are taking nightly backups, I assume that you are only taking full database backups. In this case, the transaction log will continue to grow, because old transactions are never being removed from the log. The advice you have received about reducing the size of the log is good. You must first truncate the log (remove old transactions), then shrink the log (reduce the file size). You may then set a maximum size for the log from the database properties dialog in Enterprise Manager. However, you must realize that if you don't do something to ensure that old transactions are removed, your database will crash when it reaches that limit. You have a couple of choices on how to remove the old transactions. The first is to take regular transaction log backups. Part of this process is to truncate the log. The other method is to set the database to "truncate log on checkpoint" in SQL Server 7.0, or "Simple Recovery Mode" in SQL Server 2000. These are the same thing, just referenced differently in the two versions. Recoverability is reduced with this method, but if you are not taking transaction log backups anyway, you won't be affected by that. If you don't plan to take advantage of the added recoverability provided by transaction log backups, "truncate log on checkpoint" or "Simple Recovery Mode" is a good choice. I would recommend that you read up on transaction logs and backups in Books Online to help you decide the best course of action.
Good luck!
Steve Phelps
Steve Phelps
SQL Server DBA
American Fidelity Group
January 9, 2003 at 7:14 am
I think Steve's hit it right on the money, good explanation.
Got the following script from the MS support site & have used it to good effect. Follow the instructions in the comments. Ensure that no user is in the system while you run this.
>>>
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE RMS -- This is the name of the database
-- for which the log will be shrunk.
SELECT @LogicalFileName = 'RMS_Log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 20 -- in MB
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) -- the log has not shrunk
AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END -- update
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
>>>
Good luck.
January 9, 2003 at 10:14 am
Vhanda's script from MS support is pretty nifty.
It appears that one of the things the script does is attempt to put the active virtual file at the beginning of the physical log by inserting a bunch of bogus data in the db.
Because of the max runtime limit in the proc, which defaults to 10 minutes, it may or may not work depending upon virtual file usage within the log and the actual allocated space of the log. Also consider general performance and workload of the machine. So, if you have a big log on a slow machine and the active virtual log is somewhere near the middle third, there is a good possibility that it won't get you all the space back.
Since there is a timeout restriction in the script, it might not actually shrink the log.
One other thing... It can certainly put an additional strain on a busy server because of all the inserts that can get generated.
A method that has been fool proof for me is to detach the db and reattach single file. This method is very quick and will allow you to rebuild a trans log of minimal size. Of course you must have exclusive access to do it.
See: sp_detach_db and sp_attach_single_file
Tim
January 18, 2003 at 2:23 am
Chandu????
Is the problem solved? If yes what method works???
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
January 20, 2003 at 6:32 am
Hi
Thanks for checking.
It is a still an issue.
I am confused with several suggestions made. What to implement?
I would appreciate if u have any suggestions.
What are the options available to reduce the size of the transaction logs data file size after shrinking? I cannot afford to keep transaction log file size around 17 GB when the database is much lesser.
Thanks in advance,
Chandu
January 20, 2003 at 6:56 am
Wanted to know what options you tried...
run this line and see if space get freed or not if space is freed then use shrinkdb
BACKUP LOG mydatabasename
WITH TRUNCATE_ONLY
have you backed up the database restore it with other name and see what is the log file size and then truncate log for this database and see
Other thing is dbcc checkdb with repair database option may work
third is in properties--> Option tab make logging as simple type and see if it works...
Let's know if any of this work....
revert in case need any clarification????
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply