November 11, 2008 at 8:56 am
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.
November 11, 2008 at 9:15 am
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
November 11, 2008 at 9:45 am
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
November 11, 2008 at 6:42 pm
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."
November 11, 2008 at 7:42 pm
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.
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
March 13, 2009 at 8:32 am
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?
March 13, 2009 at 8:39 am
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
March 13, 2009 at 8:45 am
"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.
March 13, 2009 at 8:57 am
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
March 13, 2009 at 9:14 am
"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.
March 13, 2009 at 9:20 am
kwilthew (3/13/2009)
The tape backup only picks up the backup files.
In that case, your admin shouldn't be complaining about wasted space. SQL's backups only contain the used space so if a DB is 200GB but only has 20GB of data in it, the backup will be around 20GB, not 200
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
March 13, 2009 at 10:14 am
You're correct, even though the database itself is around 172 GB, the .bak files are running at about 20.5 GB. It may be the volume of files slowing down the tape backups rather than the size of the individual backup files. Since I'm relatively new I don't push back much yet, I just listen and try to absorb how my actions impact others here. At some point I should be able to identify areas where I'm not as efficient as I could be and start making improvements.
In any event, thanks for the "training" session; I certainly learn something more everytime I post a question. Very knowledgeable people supporting this site.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply