March 11, 2009 at 4:32 am
Hi,
I have 3 database on one of the SQL server(2000) which are identical copies but used for different purpose. Data files size is about 17GB, Log file size is about 10GB .
I have investigated through dbcc log info, all log files have most VLF's(about 400) with status 0, only couple has value 2. Its size should come down to about 500MB but it does not as we are taking log backup every hour.
I have tried shrinking log files few days ago, its size came down to about 500MB and after two days it went back to 10GB.
Question is if we shrink data files lets say size of 20GB. it will log all page movement in log file so i need disk space in log file.correct me if i am wrong. what happen when shrinking log files? where does it log changes?
What could be done to shrinking log files?
Thanks
March 11, 2009 at 6:01 am
Does your database have lot of transaction?
If your database needs that much log then even if you shrink the log file it will come to that value which it want for the smooth operation of database.
March 11, 2009 at 6:12 am
As i said all 3 databases are indentical. One out of 3 has more transaction as its live database. Another two used by hardly one or two users. so apart from one database transactions are not many
March 11, 2009 at 6:32 am
The best way to shrink log file is to take a transaction log backup
then issue checkpoint and run the dbcc shrinkfile command
March 11, 2009 at 6:33 am
CrazyDBA (3/11/2009)
I have investigated through dbcc log info, all log files have most VLF's(about 400) with status 0, only couple has value 2. Its size should come down to about 500MB but it does not as we are taking log backup every hour.
Log backups don't shrink the log. They just make the space inside available for reuse
I have tried shrinking log files few days ago, its size came down to about 500MB and after two days it went back to 10GB.
Because you're doing enough operations that the log needs to be 10GB. Probably as a result of index rebuilds
Question is if we shrink data files lets say size of 20GB.
Why do you want to shrink the data files
What could be done to shrinking log files?
Don't understand the question.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2009 at 10:33 am
Is this about a space issue or just wanating to keep the logs small?
If space issue, consider moving the logs to another disk on yout server.
If just trying to keep logs small, dont bother. We have some logs which continually run upto 3* the size of the database (due to daily data loads, statistical update and re-index).
Adam Zacks-------------------------------------------Be Nice, Or Leave
March 11, 2009 at 11:02 am
CrazyDBA (3/11/2009)
As i said all 3 databases are indentical. One out of 3 has more transaction as its live database. Another two used by hardly one or two users. so apart from one database transactions are not many
it's hard to say why the tlog's are becoming huge. As advised this might be due to the operations you perform against the database you are talking about. What tasks are you performing against this? Can you give us an idea?
March 16, 2009 at 5:47 pm
Do you need to restor to a point in time or would daily backups and changing the recovery mode to simple be suufficient. This would then get round Ever increasing log files.
Ells
🙂
March 17, 2009 at 12:31 am
whatever i know is that when you shrink log file it will remove all uncommited transaction from transactional log file ....
so whenever you want to shrink log file first keep backup of logfile...
you should follow particular backup cycle...take full backup diff backup and transactional log file backup ... and when second time full backup done removing old backup periodically ...this method useful to maintain disk space...
following query will be shrink your logfile perfectly....
USE DatabaseName
GO
DBCC SHRINKFILE( transactional log name, 1)
BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE(transactional log name , 1)
GO
Raj Acharya
March 17, 2009 at 1:04 am
raj acharya (3/17/2009)
whatever i know is that when you shrink log file it will remove all uncommited transaction from transactional log file ....
Absolutely not.
Shrink just releases unused space to the operating system. It does not affect what's in the log file.
Truncate discards all inactive transactions, ones that are committed and have had their changed written to disk.
Nothing removes uncommitted transaction from the log. Those entries are still needed for rolling the transaction back, should it be necessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 17, 2009 at 3:03 am
sory i should use word inactive instead of uncommited...but if you are telling that it's not see whatever in log file then how can it see that this is inactive or not ...
Raj Acharya
March 17, 2009 at 3:10 am
raj acharya (3/17/2009)
sory i should use word inactive instead of uncommited...
Indeed, because they have a completely different meaning
but if you are telling that it's not see whatever in log file then how can it see that this is inactive or not ...
Not sure what you're asking.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 17, 2009 at 4:08 am
hi ...pls check my topic " trigger issue" in administering forum.....
if you can help than i will be very thankful to you
Raj Acharya
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply