Re: [OT] Shrinking LDF file

[罗格雷格博士]

To accommodate a transaction of a certain size, it had to grow. That's normal. 
It doesn't autoshrink back down as doing so is generally a really bad idea. 
There is an autoshrink option but I usually joke that it should be renamed 
"auto fragment my filesystem". You don't want files constantly growing and 
shrinking. Empty space in the DB is your friend.

Regards

Greg

Dr Greg Low
SQL Down Under
+61 419201410
1300SQLSQL (1300775775)

On 9 Feb 2016, at 6:53 PM, Greg Keogh 
> wrote:

I found it! ...

DBCC SHRINKFILE(mydatabase_log, 1)

This reduced the 140MB to 3MB, not quite as good as I hoped, but much better. I 
don't yet know what this command actually does. The advice in dozens of top 
search web articles is really verbose and misleading

Why on earth is the LDF so big anyway in simple mode?! Once a transaction is 
complete, isn't that it and it's forgotten? I know log/journal files are 
required for historical recovery, but how than that much space be used for 
anything useful?

 -- GK

On 9 February 2016 at 18:43, Greg Keogh 
> wrote:
Folks, I have been trying for 40 minutes to shrink the 140MB LDF file paired 
with its 15MB MDF file. I have been searching and searching and reading, but 
all the commands and suggestions I've found DON'T WORK. This is a test database 
and there is nothing in the LDF of interest to me. I want to deploy it to Azure 
for more testing, but I want to remove the 140MB of log garbage first.

The DB has recovery mode set to simple like everyone suggests. Does anyone 
actually, really know how to shrink the log back to as small as possible? Is it 
even possible? Do I misunderstand how this works?!

Greg K

Re: [OT] Shrinking LDF file

[Greg Keogh]

I found it! ...

DBCC SHRINKFILE(mydatabase_log, 1)

This reduced the 140MB to 3MB, not quite as good as I hoped, but much
better. I don't yet know what this command actually does. The advice in
dozens of top search web articles is really verbose and misleading

Why on earth is the LDF so big anyway in simple mode?! Once a transaction
is complete, isn't that it and it's forgotten? I know log/journal files are
required for historical recovery, but how than that much space be used for
anything useful?

 -- *GK*

On 9 February 2016 at 18:43, Greg Keogh  wrote:

> Folks, I have been trying for 40 minutes to shrink the 140MB LDF file
> paired with its 15MB MDF file. I have been searching and searching and
> reading, but all the commands and suggestions I've found *DON'T WORK*.
> This is a test database and there is nothing in the LDF of interest to me.
> I want to deploy it to Azure for more testing, but I want to remove the
> 140MB of log garbage first.
>
> The DB has recovery mode set to simple like everyone suggests. Does anyone
> actually, really know how to shrink the log back to as small as possible?
> Is it even possible? Do I misunderstand how this works?!
>
> *Greg K*
>