July 1, 2010 at 5:22 am
i tried to truncate log file thgough dbcc comaand bt it is still not releasing space why??
pls help frns
July 1, 2010 at 5:32 am
Possibly because it's full. Shrinkfile just tries to release unused space. If there isn't any unused space, there's nothing to release.
What does DBCC SQLPERF(Logspace) return for this DB?
What is the value of log_reuse_wait_desc in sys.databases for this DB?
What's the recovery model of this DB?
If the recovery model is anything other than Simple, how often are you doing log backups?
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 1, 2010 at 6:07 am
If you recovery model is full try backing up your transaction log then run your dbcc command to shrink the transaction log to the size you choose. That should do it. I've been in that situation several times with boxes I've inherited.
lavigne (wcucomputernerd)
July 1, 2010 at 8:36 am
See this link on log truncation and shrinking.
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.
July 2, 2010 at 12:28 am
Do you have transaction log backup ? if not then schedule it depending on your environment.
Try to put the DB into single user mode , Truncate the log and the shrink it, before shrinking it check how much empty space is available? it will shrink only if there is any empty space available.
If still you are not able shrink then , would like to know if you have setup any replication. Because we have seen due to this tlog shrinking is impacted.
Let us know?
"More Green More Oxygen !! Plant a tree today"
July 8, 2010 at 5:22 am
Truncate log command will not work on SQL 2008.
I tried this option and worked for me DBCC Shrinkfile(logfile number,space in mb,truncateonly)
July 8, 2010 at 5:26 am
Rajesh M S (7/8/2010)
I tried this option and worked for me DBCC Shrinkfile(logfile number,space in mb,truncateonly)
The truncateonly option is ignored on log files, it only applies to data files. That statement will only work if there's free space in the transaction log
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 12, 2010 at 2:56 am
Hi,
If you don't want the transaction log, Then you can use by the following query
backup log databasename with truncate_only
In these query will truncate the log space. you can try this..
With thanks & regards
Balaji.G
July 12, 2010 at 3:12 am
balaji.ganga (7/12/2010)
backup log databasename with truncate_only
That does not work in SQL 2008.
Msg 155, Level 15, State 1, Line 1
'truncate_only' is not a recognized BACKUP option.
If you don't want point-in-time recovery, set the database to simple recovery, and leave the log alone. If you need it in full (or bulk-logged) for recovery purposes, back the log up on a regular basis.
Take a look through this article - http://qa.sqlservercentral.com/articles/64582/
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply