July 11, 2005 at 7:35 pm
Hello All,
I have db 15 GB and transaction log is 13GB.
how can i reduce the Transaction log size.
i'm doing everynight full backup and every 15 min transaction log backup.
i'm new Sql DBA.
i'm confused abt my backup every 15 min is also transaction log backup
and when i go database properties i see transaction log size is 13 GB.
i'm confused b/w this transaction log and 15 min backup transaction log.why this T-Log is growing 13GB and why it does truncate after full backup.
Can any one explain first whats big Tlog like 13 GB and other transaction log backup.
other thing i know how to truncate log
backup log ... truncate_only or noTruncate
but if i truncate my log,should i able to restore to the point after truncating log.
Please explain...
Thanks
July 11, 2005 at 8:00 pm
Dear Andy,
Are you use SQL Server 2000? If yes, you can change the option recovery model in your SQL to Simple (Not Full), but this is can be apply if you don't need to save the Transaction Log File.
First time you detach your database and then delete the Log File, after that attach the database with the previous database file, by this way you can have a new Log File, and then you can change the recovery model to simple.
July 12, 2005 at 7:29 am
i don't want to change to simple recovery mode.all i want if i truncate my log
backup log .. truncate_only.
and take a backup.
if anything happened after couple of day,can i able to recover my database up to latest point.
would i loose any data if i truncate my log,coz stilli have transaction log backup every 15 min.
July 12, 2005 at 11:43 pm
I think you should use shrinking mechanism of transaction log. YOu can refer to SQL BOL about DBCC SHRINKFILE and this command you can schedule in a job or automate it when there are few or 0 user connections.
Regards,
Dilip
July 13, 2005 at 12:52 am
Other option is,
you can use the Option 'Auto Shrink' in the "Database->Properties->Options"
But this will affect the performance.
Second option is, use
dbcc shrinkdatabase (databasename,truncateonly)
this will shrink only logs, and return back the space to OS. You can schedule this process in your jobs
-Johnson
July 13, 2005 at 2:17 am
Hi Grasshopper
Simple solution for your problem is shrink the log file for all databases.
Use the following link supplies by David Bird in this forum
"Shrink Log file for all Databases"
http://qa.sqlservercentral.com/scripts/contributions/1533.asp
use master
DECLARE @Statement varchar (2000)
SELECT @Statement = @Statement + 'USE ?; '
SELECT @Statement = @Statement + 'SELECT ''?''; '
SELECT @Statement = @Statement + 'DECLARE @Log_Logical_FileName varchar (30); '
SELECT @Statement = @Statement + 'SELECT @Log_Logical_FileName = rtrim(name) FROM dbo.sysfiles WHERE (status & 0x40) <> 0; '
SELECT @Statement = @Statement + 'dbcc shrinkfile (@Log_Logical_FileName, 30,truncateonly); '
SELECT @Statement = @Statement + 'SELECT fileid, name, filename, size, growth, status, maxsize FROM dbo.sysfiles WHERE (status & 0x40) <> 0; '
SELECT @Statement
GO
July 21, 2005 at 7:52 pm
You could possibly look at a hung transaction preventing the log from shrinking below a certain point. Basically it's a transaction that, as far as the system is concerned, is still in progress, even though the program that performed the action is long past closed.
There are numerous articles on how to find these and clean them up.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply