January 11, 2011 at 8:27 pm
We have been asked by our CIO to shrink the datafiles of our 800 Gb database.
Someone mentioned that shrinking the datafiles is one of the tasks that is performed with a Full backup. Our databases use Full Backup but this doesn't sound right to me. I don't recall ever reading this. Does anyone know if that is the case? I know that truncating the T logs is an option of the Full backup but ... the data files ??
We are using SQL Server 2005 Enterprise
Thanks
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
January 11, 2011 at 8:46 pm
No shrinking is not at all part of a full backup by default.
Neither it is an option in backup.
Shrinking of a database actually is a totally different process in which you actually try to reclaim the unused\ unallocated part of your data files to OS.
To shrink a data file is not recommended because it can cause fragmentation because it involves moving your data\index pages here and there physically to reduce the size of the datafile.
And also Full backup never truncates the Transaction log. only the Log backup will truncate your transaction log again and only the inactive portion of the log.
I hope i have answered your query.
pls. reply in case you need further clarification on that.
January 11, 2011 at 8:47 pm
The above answer is correct. Shrink is not part of backup, and you should not shrink to a point below that which you need for maintenance operations.
How much data do you have, and to what level do you plan to shrink? And why were you asked?
January 11, 2011 at 10:58 pm
MothInTheMachine (1/11/2011)
Someone mentioned that shrinking the datafiles is one of the tasks that is performed with a Full backup. Our databases use Full Backup but this doesn't sound right to me. I don't recall ever reading this.
It's not. Backup just backs up the data and part of the log. Nothing else
I know that truncating the T logs is an option of the Full backup but
It's not. Truncating the log (marking log space as reusable) is only done by log backups in full recovery, not by full backups and there's no option to make it so.
Do note that shrinking is usually a very bad idea, especially repeated shrinking. Databases tend to grow as more data gets put in them. It's in their nature.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
http://brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/
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
January 12, 2011 at 4:46 am
You're also only going to be able to shrink as far as you have space available. Unless you created an 800gb database and only put 400gb of data in it, I would assume that you probably don't have much in the way of reclaimable space.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 12, 2011 at 8:36 am
Funny you should mention an 800Gb database Grant..that is how big this database is!. It is a SharePoint 2007 Content Database. The recommendation is that they don't go over 100Gb but this is what it got to prior to bringing us in.
I have just gotten access to the box so I haven't had an opportunity to look at the db. Our CIO said he wanted us to shrink the database because a confidential document had been put onto a public SharePoint site. As part of our cleanup we deleted the document and ran an incremental search which in Sharepoint removes it from the search results. I have a feeling this isn't going to do exactly what he thinks it will do but I suppose it does mark the space as reclaimable to the OS so at some point it will be overwritten.
Thanks for clearing up the confusion about the Shrinking on Full Backup. Someone here asked if that might have been a feature in an earlier version of SQL Server?
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
January 12, 2011 at 8:43 am
Make sure you dig in and understand what happens.
Also realize that when the CIO says "shrink", he doesn't necessarily mean "run dbcc shrinkfile". What he means could be "make sure we remove this from the server" or "get space back", which may not be the same thing.
January 12, 2011 at 9:32 am
MothInTheMachine (1/12/2011)
Our CIO said he wanted us to shrink the database because a confidential document had been put onto a public SharePoint site. As part of our cleanup we deleted the document and ran an incremental search which in Sharepoint removes it from the search results. I have a feeling this isn't going to do exactly what he thinks it will do but I suppose it does mark the space as reclaimable to the OS so at some point it will be overwritten.
Maybe, maybe not, and it may still sit on the drive in unallocated sectors for years. If you want to be sure, you need to:
Back the database up
Check that there is no trace of the document in the backup file (hex editor)
Detach the database
Run a secure deletion program to delete the database files securely.
Restore the database from backup
Someone here asked if that might have been a feature in an earlier version of SQL Server?
Nope.
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
January 12, 2011 at 12:57 pm
Do you know how much space is used/unused in the database? This may be useful information to pass on to the CIO. If there is only 20% or less I would most definitely leave the database alone. The following statement will give you that information
select [FileSizeMB] = convert(numeric(10,2)
, round(a.size/128.,2))
, [UsedSpaceMB] = convert(numeric(10,2)
, round(fileproperty( a.name,'SpaceUsed')/128.,2))
, [UnusedSpaceMB] = convert(numeric(10,2)
, round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))
, [DBFileName] = a.name
from sysfiles a
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
January 12, 2011 at 3:28 pm
hi Chris,
Thanks for sending that query.
So the used space across 8 files adds up to 814151.4 Mb. The unused Space is at 52.13 Mb.
Given this situation if I try to shrink a backup (on my test server) you think the shrink will fail ?
I may have to do some looking at GilaMonsters PlanB suggestion that involves a hex editor to read the backup. Is there some more informaiton about this procedure at SQLServerCentral? Would using a hex editor involve scrolling through the files manually, thus taking me a month, or is there a search feature that can be used?
Thank you!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
January 12, 2011 at 3:48 pm
MothInTheMachine (1/12/2011)
Would using a hex editor involve scrolling through the files manually, thus taking me a month, or is there a search feature that can be used?
I suggest that you use google, locate a hex editor that you can download, then read through its manual/help file and see what and how. They're like text editors, just for binary files, and as varied in features as notepad vs UltraEdit.
p.s. My suggestion was not intended as a Plan B. Shrinking the file in no way guarantees that the confidential data will be overwritten. If you need to be sure that no one can read the remains of the file with tools that directly read hard drive sectors shrinking is not good enough. I supposed it depends how certain you have to be that it's gone and not accessible to anyone with any tool.
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
January 12, 2011 at 6:51 pm
Thanks Gila. I will definitely check into that. It is critical that the document be completely unreadable. We have already destroyed our tape backups and taken some other steps to deal with it.
Thanks!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
January 17, 2011 at 3:15 am
Run a secure deletion program to delete the database files securely.
You need to know how your disk subsystem works before using a program of this type. If your data is stored on a SAN, then where possible always use the SAN vendor's secure delete program.
Some SANs and network storage devices work on the principle that a track of data is only ever written or read, but never updated. In this model, any update causes a track to be pulled from the free track pool and the new data written to it. The old track then gets put back into the free track pool (unless it is needed for any active SAN-level snapshot). If you use a secure delete program that assumes that tracks will get updated, all this achieves is cycling a number of tracks in and out of the free track pool, and never actually overwrites the sensitive data. That is why the vendor secure delete is needed, as this works with the SAN in doing the secure delete.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 17, 2011 at 3:21 am
I suppose it does mark the space as reclaimable to the OS so at some point it will be overwritten.
As GilaMonster says, if it is in the OS free space pool it may remain unallocated for years. It depends on how volatile your free space is on the database disks.
The space is probably more likely to get overwritten if it remains within the database file. If you do tasks such as index rebuilds then the free space in your database files is almost certainly more volatile than your OS free space.
But also see my previous post about secure deletes, because if your storage is track vector based then you as a DBA will never know when a given track has been overwritten - this information is only available to the storage administrator, and typically they need to have purchased troubleshooting software from the storage vendor to get this level of detail.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 17, 2011 at 3:28 am
EdVassie (1/17/2011)
You need to know how your disk subsystem works before using a program of this type. If your data is stored on a SAN, then where possible always use the SAN vendor's secure delete program.
Good point. Thank you for that.
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
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply