Problem In Doing Shrink Database

[Tommy.Vincent]

hi all,

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.

I want to know why this happened and how should i shrink Log File or
any other option.
One more doubt how does Dbcc ShrinkDatabase help in performance.

A kick to a right direction will be helpfull to me.

Thanks in advance
tv

ps
I also used DBCC SHRINKFILE(database_log)but their was no change
in size of log file.

[Erland Sommarskog]

(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

[Greg]

The logfile will shrink down to the virtual log file boundary.. Backup
the transaction log first then try shrinking the file..

greg

[Paul S Randal [MS]]

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" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

(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

[Erland Sommarskog]

Paul S Randal [MS] (pr*****@online.microsoft.com) writes:

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 for the correction, Paul!
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp