(To***********@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,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp