SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Shrink


Database Shrink

Author
Message
thinknight
thinknight
SSC Eights!
SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)

Group: General Forum Members
Points: 882 Visits: 272
While shrinking database files, choices are
a) Compress pages and then truncate free space from the file
and
b) Truncate free space from the end of the file
and
c) Shrink file to 9999 MB

If i try to select first or second one, have i get data loss.

Or what are differences between three one.

The second question is, Is it necessary to shrink by above method.

Does it solve my space problem (shrink database step) without choosing shrink file.
MANU-J.
MANU-J.
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15078 Visits: 8766
You shouldn't shrink your database as it creates frgamentation and consumes costly server resources like cpu etc.

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

Read about the three options on books online and you will understand it.

MJ
GilaMonster
GilaMonster
SSC Guru
SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)

Group: General Forum Members
Points: 470222 Visits: 47370
A shrink will never lose data. Why do you want to shrink your database?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


The_SQL_DBA
The_SQL_DBA
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5002 Visits: 935
shrinking operation disregards indexing..which degrades the performance. What calls for you to shrink the database.

If its only the log file that has grown too big then try thi

use UrDbName
Backup Log UrDbName WITH TRUNCATE_ONLY
DBCC SHRINKFILE (logical name of Log File)

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
SQLBOT
SQLBOT
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3784 Visits: 836
Besides not doing that at all... my only advice would be that if you did... take a full backup afterward to start a new recovery chain.

I'm shocked at the number of times I've seen people suggest truncating a log file as if it were something trivial... I'm also shocked at the number of people who post about not having a good backup and needing emergency help in a crisis... probably the same people

Hehe


What you are doing by shrinking files with the GUI is running a DBCC Shrinkdatabase or Shrinkfile.
Technical details are below.

Shrinkdatabase
Shrinkfile


Many people will caution against running these because they cause file fragmentation and consume massive amounts of resources (except the shrinkfile with truncateonly).

Shrinking logfiles is a better bet to free up disk space than shrinking a database. The SQL logfile is structured in a different way where it isn't affected (as much) by fragmentation. The only caveat is that if your logfile is ballooning all the time it probably wants to be that size... in that case I suggest more frequent log backups (and quickly getting those files to a remote archive) to keep disk use to a minimum.


Cheers!

~BOT

Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
kwilt
kwilt
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1225 Visits: 394
I have been reading the forum looking for advice on shrinking database and log files as well. I'd like to post a follow-up question to this thread if I may.

We have a database that is loaded daily with transactions from a core system and the database is then used for reporting. The database's was created several years ago by someone no longer with the company and I have inherited the responsibility for it's maintenance and health.

The .mdf file size is 172 GB. To make matters worse, there are several versions of this same database used for development and testing purposes, so we have four separate databases of this same size to manage. When using the GUI to shrink the database, the screen indicates that there is 88% free space.

Our server admin performs nightly and weekly tape backups of these files for disaster recovery purposes, and frequenly complains about the length of time the tape backups take because of these large databases. My question is, if there is so much free space available within the database, is this not a good candidate for shrinking the database file to make it more manageable?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)

Group: General Forum Members
Points: 470222 Visits: 47370
kwilthew (3/13/2009)
My question is, if there is so much free space available within the database, is this not a good candidate for shrinking the database file to make it more manageable?


As a once-off operation with an index rebuild after, sure.

An unrelated comment: Backing up the files of a SQL database while it's active is not a good way to back the database up. It's pot luck if files copied in that manner are usable. Back the database up using SQL native backups (BACKUP DATABASE ...) and then copy that backup file to tape. Added bonus, the backup file won't back up empty space and hence will be smaller than the database files are.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


kwilt
kwilt
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1225 Visits: 394
"Backing up the files of a SQL database while it's active is not a good way to back the database up."

If you're referring to the tape backups, those are done at 3:00 am when users are off the system. We are not a 24/7 shop (fortunately!)

Thanks so much for the response. If I schedule a one-time maintenance job to shrink the database and then rebuild indexes, does that sound reasonable?

I really appreciate the information on these forums; it's probably the best training I'm going to find anywhere. BOL is a great resource, but it doesn't cover many of the real-world problems I'm finding that keep me awake at night.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)SSC Guru (470K reputation)

Group: General Forum Members
Points: 470222 Visits: 47370
kwilthew (3/13/2009)
"Backing up the files of a SQL database while it's active is not a good way to back the database up."

If you're referring to the tape backups, those are done at 3:00 am when users are off the system. We are not a 24/7 shop (fortunately!)


Doesn't matter. There being no users doesn't mean that the database IO is quiesced. That is a BAD way to do backups and it's possible that those files will be useless if you need to recover with them. I've seen cases where 'backups' like that are used and either the files won't attach at all or they attach and the database is suspect.
There are tools which allow that but they specifically request SQL to quiesce the database before copying the files

Backup the SQL databases using SQL's native backup before 3am and let the file-system backup copy those to tape, not the mdf and ldf files.

Thanks so much for the response. If I schedule a one-time maintenance job to shrink the database and then rebuild indexes, does that sound reasonable?

Sounds fine.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


kwilt
kwilt
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1225 Visits: 394
"Backup the SQL databases using SQL's native backup before 3am and let the file-system backup copy those to tape, not the mdf and ldf files."

My apologies, I wasn't clear on our backup strategy. Your suggestion is exactly what we do; in fact, we keep several days' worth of the actual backup files on the server itself. The tape backup only picks up the backup files. Your statement verifies that this is a sound approach, and that's good to know.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search