Hello,
Is the procedure to change database recovery model from full to simple?Is ot
only change in properties or we need do something more.I would like to have
very small transaction log after this operation.
Art

Arti (arti69WYTNIJTO (AT) interia (DOT) pl) writes:
If you all you want to achieve is to switch to simple recovery, all you
need is

ALTER DATBASE db SET RECOVERY SIMPLE

(Unless you are into replication.)

If you also want to reduce the size of the log file, you also need to
run DBCC SHRINKFILE on the log file. Try to estimate what size you wil
need, and use this as target size when shrinking the file. It's useless
to shrink the file to just a few megabytes if it will grow again. Keep
in mind, that even with simple recovery, the log is still written to, and
if you run large mass-updates, you may still need some log size.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

What to do in order to all transaction were in database (mdf)
A


But my question is what about transactions which are not yet in mdf file.
Art

Arti (arti69WYTNIJTO (AT) interia (DOT) pl) writes:
SQL Server never truncates the log past active transactions, or transactions
that have not been written to the MDF yet. If you want to force this,
you can use the CHECKPOINT command.

If you are shrinking your log 2-3-4 times, you are shrinking it 2-3-4
times too many. Shrinking the log (or the database file) should be an
exceptional event, and nothing you do as matter of routine.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

0.Do something on databest fer example Insert 1000 rows and
1.Backup Log + shrink log file
2.Check size of file
After 3-4 times doing point 1 and 2 file will be smaller .
Explaination is easy: VLF 's with status active after backup log is moving
to the begining of file.
A

"Arti" <arti69WYTNIJTO (AT) interia (DOT) pl> wrote

As Erland stated you should be using CHECKPOINT to clear down committed
transactions in the log, this way the VLFs are cleared and so you should
only need one shrink.

Between step 0 and step 1 you should be issuing the CHECKPOINT - if you
don't do this then the transactions may still be uncommitted to the MDF
which is why step 1 isn't shrinking the log.

You're sort of right, but then Erland was too. You just didn't read his
whole reply, and instead focussed on one part.

--
Dan