|
|
Posted by Paul S Randal [MS] on 06/26/05 17:11
One small clarification - although everything is logged during a shrink,
each page is moved inits own transaction internally so if the power cord is
pulled, only the last page move needs to be rolled-back. All previous work
is committed and is not lost.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns967EEED03EB6AYazorman@127.0.0.1...
> (Tommy.Vincent@gmail.com) writes:
>> This will be a easy question for all out here.
>> I have a database of 28GB.
>> having 3 Data Files 22 GB, 3.58 Gb and 2.70 GB respectively.
>> and a Transaction Log file of 156 mb.
>>
>> When i executed DBCC Shrinkdatabase(databasename),it reduced
>> size of datafile but the LOG file had gone up to 5 Gb from 156mb.
>
> Yes, shrinking a data file is a logged operation - so that the shrinking
> can be rolled back, if someone pulls the power chord while the shrinking
> is running. And since shrinking can require to move a whole lot around,
> you can bet that your log will grow.
>
>> I want to know why this happened and how should i shrink Log File or
>> any other option.
>
> You can use DBCC SHRINKFILE to shrink the log. If you are running in
> full or bulk-logged recovery, then your first need to backup the
> transaction log.
>
>> One more doubt how does Dbcc ShrinkDatabase help in performance.
>
> There is rarely any need to shrink databases. Do this, only if you
> have removed a lot of data, and you know that this amount will not
> come back. Else the database will have to grow again, and autogrow
> is a fairly expensive operation.
>
> What I would consider, though, is to run SHRINKFILE on the small files
> with the EMPTYFILE option, and then drop these with ALTER DATABASE
> REMOVE FILE.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
[Back to original message]
|