April 9, 2008 at 4:06 pm
Hello-
I have a database (simple recovery mode) that is about 30 GB in size; the log, however, is 460 GB! This is a development server, so no Production data is at risk. I have tried shrinking both the database and the log file to free up some space, and tried backing up the log with TRUNCATE_ONLY. Any suggestions on how I can get it smaller?
Thanks!
April 9, 2008 at 4:32 pm
What command(s) did you use to try and shrink the log?
You might want to have a look at this: BOL: Shrinking the Transaction Log.
April 9, 2008 at 8:27 pm
A) do you have replicaiton on this database?
B) any open transactions on this database?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 10, 2008 at 12:07 am
What does the following query return?
select name, recovery_model_desc, log_reuse_wait_desc
from sys.databases where name = <Name of offending database>
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
April 10, 2008 at 2:28 am
Backup with truncate_only restarts the log file, but does not shrink it.
Huge log file means there are huge table transactions and/or transactions over huge blobs. So the source of problem is probably the application.
It can also be result of periodic maintenance tasks.
April 10, 2008 at 9:45 am
Thanks all, turns out one of the DBA's who left was doing some replication tests. Problem resolved.
April 10, 2008 at 12:24 pm
Just fire backup command with truncate_only and then resize the log files
April 10, 2008 at 10:49 pm
narendra.ree (4/10/2008)
Just fire backup command with truncate_only and then resize the log files
That won't fix it if it's being caused by replication.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 11, 2008 at 3:50 am
Run the following script -
EXEC sp_dboption 'DATABASE_NAME','trunc. log on chkpt.','true'
CHECKPOINT
Then shrink the log file upto the min size specified.
Your log file will shrink. Any other method will take huge time.
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
April 11, 2008 at 5:02 am
If the database is in simple recovery mode (which it is) that option will have no effect. It's a older way of doing exactly what simple recovery mode does.
Also, that option is deprecated and should not be used.
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
April 15, 2008 at 2:55 am
an might I know why we shouldn't use it?
It is the straightforward and most easiest way to empty the log file. If not, can you suggest any other method which will shrink the log file size from 460GB faster?
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply