Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Database Shrink Expand / Collapse
Author
Message
Posted Tuesday, November 11, 2008 8:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 22, 2011 7:03 AM
Points: 104, 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.

Post #600729
Posted Tuesday, November 11, 2008 9:15 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 22, 2014 7:04 AM
Points: 1,688, Visits: 8,766
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
Post #600747
Posted Tuesday, November 11, 2008 9:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 40,168, Visits: 36,554
A shrink will never lose data. Why do you want to shrink your database?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #600771
Posted Tuesday, November 11, 2008 6:42 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:15 PM
Points: 802, 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."
Post #601047
Posted Tuesday, November 11, 2008 7:42 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:59 AM
Points: 626, 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




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
Post #601065
Posted Friday, March 13, 2009 8:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 17, 2011 10:05 AM
Points: 85, 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?
Post #675225
Posted Friday, March 13, 2009 8:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 40,168, Visits: 36,554
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 2008, MVP
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

Post #675235
Posted Friday, March 13, 2009 8:45 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 17, 2011 10:05 AM
Points: 85, 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.
Post #675245
Posted Friday, March 13, 2009 8:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 40,168, Visits: 36,554
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 2008, MVP
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

Post #675271
Posted Friday, March 13, 2009 9:14 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 17, 2011 10:05 AM
Points: 85, 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.
Post #675309
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse