|
|
Posted by Erland Sommarskog on 10/01/15 11:19
(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
Navigation:
[Reply to this message]
|