January 26, 2011 at 2:43 am
I have an log file that I try to shrink, but it doesn't seem to work. The file is currently 5gb, and I would like to reduce it to 500mb. I've performed an log backup and truncated the log. Afterward I run the commando dbcc SHRINKFILE (logfile,500), but nothing happens. The strange thing is that it works on the other databases on the same server.
January 26, 2011 at 2:47 am
Why do you want to shrink it?
How full is the log? (DBCC SQLPERF(LogSpace)
Check in sys.databases and see what the current value is for log_reuse_wait_desc for this database.
Full recovery I assume. What frequency do your log backups run at?
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
January 26, 2011 at 3:21 am
Log Space used is 50%.
/Andy
January 26, 2011 at 3:23 am
[font="Tahoma"]
With the log just 50% filled I suggest you not to go with the shrinking anytime now (However the trigger value is defined by the space requirements provided ur environment is not very critical....since shrinking results in fragmentation...best to avoid in place)
Having said that if you still wanted to go with shrinking...
Have you tried changing the recovery model of the database to simple and shrinking it(U can change back to full/diff later once u r done with the shrinking)
Even if this doesn't work, the last option could be issuing the command DBCC shrinkfile ('DB_file',500) with TRUNCATE_ONLY
(Remember that this option comes at a toll price of log chain being broke with this operation, you will have to take a full back up right after issuing this command)
Let us know at what point you were able to achieve ur task. 😛 [/font]
January 26, 2011 at 3:36 am
andy.l (1/26/2011)
Log Space used is 50%.
Which means that the log needs to be at least 2.5 GB in size. Shrink it below that and it will just grow again.
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
January 26, 2011 at 3:37 am
anilkullam (1/26/2011)
Even if this doesn't work, the last option could be issuing the command DBCC shrinkfile ('DB_file',500) with TRUNCATE_ONLY
Truncate_only is ignored when shrinking log files. It has no effect whatsoever. It's an option only for when shrinking data files.
(Remember that this option comes at a toll price of log chain being broke with this operation, you will have to take a full back up right after issuing this command)
Shrinkfile does not truncate the log. Ever.
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
January 27, 2011 at 1:50 pm
1.Properties window of the DataBase
2."Option" tab.
3.Set "Recovery Model" to "Simple".;-)
January 27, 2011 at 1:54 pm
soj2008 (1/27/2011)
1.Properties window of the DataBase2."Option" tab.
3.Set "Recovery Model" to "Simple".;-)
And say goodbye to the ability to restore to point of failure, or any other time than the last full backup.
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
January 27, 2011 at 1:57 pm
Shrinking DB or Restoring DB!!!
two way??!!
January 27, 2011 at 2:02 pm
What?
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
January 27, 2011 at 2:04 pm
Nothing.
Forget it.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply