Microsoft SQL Server forum - Problem In Doing Shrink Database

Author

Problem In Doing Shrink Database

Tommy.Vincent@gmail.com

2005-06-23, 7:23 am

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(datab
asename),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

2005-06-23, 8:23 pm

(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(datab
asename),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/techin.../2000/books.asp

Greg

2005-06-23, 8:23 pm

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]

2005-06-26, 9:23 am

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:Xns967EEED03EB6
AYazorman@127.0.0.1...
> (Tommy.Vincent@gmail.com) writes:
>
> 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.
>
>
> 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.
>
>
> 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/techin.../2000/books.asp

Erland Sommarskog

2005-06-26, 1:23 pm

Paul S Randal [MS] (prandal@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, esquel@sommarskog.se

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

Sponsored Links