Log File in sql server 2008

Question

Post reply

Log File in sql server 2008

venkatreddy.bashi

SSC Veteran

Points: 231
More actions
April 15, 2010 at 10:28 pm

#219292

Hi All,
We are using sql server 2008 ,the log file of the one database is very large.
The size is more than data file of database.
The size of the log file is not reducing even I took the transactonal log backup.
I am sure the the log file has no active transactions, because when i tried this below command it is returning LogBackup.
select log_reuse_wait_desc from sys.databases where name=' databasename'.
Please suggest me on this.
Thanks
venkat

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 1

hi,

Use DBCC SHRINK command....

http://support.microsoft.com/kb/873235

regards,

Sasidhar Chowdary

Recognition is the greatest motivator.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

First verify the free space in the logfile. Then, if the logfile is largely made up of free space - you can shrink it. Use DBCC Shrinkfile.

Second, verify that you have your transaction logs scheduled to run regularly. Verify that those are completing successfully.

Third, find out what is causing your log file to grow so large. You can read this article to help with that - http://www.sqlservercentral.com/articles/Log+growth/69476/

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

Suresh B. SSC-Insane Points: 22986 More actions · Answer 3

venkatreddy.bashi (4/15/2010)
The size of the log file is not reducing even I took the transactonal log backup.
I am sure the the log file has no active transactions, because when i tried this below command it is returning LogBackup.
select log_reuse_wait_desc from sys.databases where name=' databasename'.
Please suggest me on this.

1) Just taking tlog backup is not enough. You need to shrink also.

2) Above query does not tell whether there is any active tran.

You need to run DBCC OPENTRAN

chanti2985 SSC-Addicted Points: 429 More actions · Answer 4

select name,recovery_model_desc,log_reuse_wait_dec from sysdatabases

log_reuse_wait_dec gives the status of perticular log:ACTIVE_transaction,logback_up,nothing ...etc

which database u have to find the status of logback_up immediately u take the log backup.

then u go for shrink...