April 29, 2010 at 12:00 pm
Hi,
I took the transactional log backup of the database then I shrink the file but the size of the file is not reduced.
I used the following command to find out any active transaction in log file as
select log_reuse_wait_desc from sys.databases where name='dbname'
It is returning log_backup.
Even i took the transactional log backup but I am unable to shrink file.
Please suggest me on this . How can i reduce the log file size .
April 29, 2010 at 12:06 pm
First, why are you trying to shrink the transaction log? If you shrink it, it will just have to grow again as needed until the transaction log backup.
April 29, 2010 at 8:51 pm
No need to shrink the t-log. The space inside the file is available for re-use once the t-log backup completes successfully.
Shrinking the file just releases the space to the OS. This causes undue IO and possibly pressure on your system. It is an expensive (in terms of resources) action / event.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 30, 2010 at 12:13 am
What is the outpur of desc column in log_reuse_wait_desc. If any Replication is setup? If Script out the subscription, drop the subscriber, back up the log, shrink it
to something smaller and then recreate the subscription.
You need check if there is any open transaction, which is preventing you to shrink the log run the following script to find if there is any open transactions dbcc opentran().
Also check the output dbcc loginfo(dbname) and send the detail
"More Green More Oxygen !! Plant a tree today"
April 30, 2010 at 1:07 pm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply