July 2, 2010 at 10:41 am
Hi,
I have a doubt in shrinking the log file..
I found that some transaction is causing the log file and it is still running..
So my question is what will happen to the transaction that is running if I srink the log file.. Will it rollback the transaction?
Ram...
🙂
July 2, 2010 at 10:53 am
Why do you want to shrink the log file?
How much free space is in it? (DBCC SQLPERF(LogSpace))
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
July 2, 2010 at 11:03 am
Log file size is around 10GB.. and still it is growing..
(Server we are using for development purpose. So developers always executing scripts that causes the problem.. )
🙂
July 2, 2010 at 11:04 am
One day I have seen that only 10mb available space in my 250 gb Hard disk
🙂
July 2, 2010 at 11:09 am
Ok, so you need to identify why the log is growing and resolve that, not just blindly shrink something that's trying to grow. Have you checked how much (if any) free space is in the log file (not on your disk)? If the log is growing, I'd bet it's close to 0, which means shrinking will do absolutely nothing.
Forget about shrinking, start thinking about finding out why it's growing. The log_reuse_wait_desc column in sys.databases is the place to start.
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
July 2, 2010 at 1:34 pm
Is the database in "full" recovery, but you are not taking frequent trans log backups ?
July 2, 2010 at 1:46 pm
It is Simple backup model.. As it is developers server we are not taking regular transactional backups..
When ever a transactions getting slow are getting locks on that we will get informed.
🙂
July 2, 2010 at 7:18 pm
Being a development box, I can imagine the sort of things that gets thrown at it 🙂
See the following links on how to find out how much of the transaction log is used, what's the oldest active transaction, how to truncate the log, shrink the log etc etc.
http://www.sqlbackuprestore.com/transactionlog.htm
http://www.sqlbackuprestore.com/truncatingshrinking.htm
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply