January 17, 2011 at 9:38 am
It sounds very difficult to identify if the document is still available. How would anyone be able to access & read it at this point ?
January 17, 2011 at 10:13 am
If the sectors have not been overwritten, then someone could read the disks. Arguably not easy, but possible.
January 18, 2011 at 8:45 am
How anyone would be able to read the file is beyond me. The security here is uber restrictive. I think this is a case of making upper management 'feel' more secure. My opinion is that the technical people should make the technical decisions of how to get things done but in this case the guy said 'shrink the db' so everyone here jumped and said "We have to shrink the database!" before any research could be done into the implications of doing that. Turns out we are using SAN storage so thankfully this hasn't prgoressed as quickly as it might have.
I appreciate all the comments on this. Not having a fulltime DBA has been made alot easier by your willingness to share your knowledge and experience.
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 18, 2011 at 9:12 am
Glad to help and I suspect you are correct. It's not likely anyone would read the files, but there have been cases of this happening.
I think you have to interpret what upper management says and understand what they want to happen and don't get caught up in their terminology matching yours. Often there is a different meaning between what they want, and what the process you call by the same name will do.
Make your best guess about what they mean, and make that happen.
January 19, 2011 at 5:33 pm
Hi MothInTheMachine
Echoing the sentiment of some of the other replies, I'm interested to know exactly what your CIO actually wants to achive. Does he/she really care about the size of the file and want you to make it smaller, or does he/she want you to purge any trace of the confidential document from the database?
DBCC Shrinkfile will make the physical file smaller if there is currently free space, but this is no guarantee that all traces of the document will be removed from the database.
Also, if your CIO is doing this to prevent access to the confidential document, I'd also have to ask what confidence you have in your SQL Server and SharePoint security. What scenario are you trying to avoid?
Lastly....given the size of your content database, you should definately be looking to split out your different SharePoint site collections into their own databases - I'm hoping your content isn't one enormous site collection :-).
Cheers,
Dwayne
EDIT: Hmmm, would help if I read page two before replying 😉
January 19, 2011 at 8:03 pm
Steve Jones - SSC Editor (1/18/2011)
Make your best guess about what they mean, and make that happen.
Or, if possible, go back to them, and clarify the objective, not the method. And tell them you will meet the objective..... such as "make the document unavailable to anyone"
March 18, 2011 at 7:50 pm
Well, quite some time has passed since I posed my initial questions and got such good feedback from folks. I was able to have a backup of the 800Gb Content DB restored to an instance of SQL Server 2005 that was pretty much isolated on it's own machine. This afternoon I began a shrink on it. I used the GUI rather than a DBCC. It took several hours before ending with:
------------------------------
Shrink failed for Database 'Content01'. (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Lock request time out period exceeded.
Could not adjust the space allocation for file 'WSS_Content_01_5'.
Lock request time out period exceeded.
Could not adjust the space allocation for file 'WSS_Content_01_6'.
Lock request time out period exceeded.
Could not adjust the space allocation for file 'WSS_Content_01_8'.
DBCC SHRINKDATABASE: File ID 1 of database ID 5 was skipped because the file does not have enough free space to reclaim.
Cannot shrink log file 2 (WSS_Content_Primary_log) because all logical log files are in use.
DBCC execution completed. (Microsoft SQL Server, Error: 1222)
It looks like a lock timeout caused the failure of 3, 1 was skipped and finally one was in use..which I don't get because there is no site collection hooked up to it. Also, someone asked earlier if we could split this up into different content dbs,..This DB is in fact a single site collection. This is what happens when people adopt SharePoint, fall in love with it, but don't know how to manage it!
I'm wondering if I extend the lock timeout period if this shrink would be successful? BTW, I have a hex editor to seek out the file in question but have not tried to use it. Would it be possible to just put in the name of the file in question and click a button or is this something I'd have to manually click through 800Gb of data looking for? This sounds like a really dumb question..i'm pretty sure they don't work like that but..I never used me one before so ..thought I'd ask it anyway.
Cheers!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
March 19, 2011 at 2:05 am
Each of those is a separate mdf or ndf file. Query sys.database_files, it'll show you the logical name (WSS_Content_01_5) and what the physical file name is.
What are you planning on using a hex editor for? You won't be able to open the file while SQL is running and tampering in any way with the database files is a really bad idea.
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 19, 2011 at 11:11 am
Is this with shrinkdatabase or shrinkfile?
March 19, 2011 at 4:12 pm
I am working on a backup and not on the production db. The use of a hex editor was suggested as a way to determine if a classified document was in the db. If I could prove that it was not there I could avoid shrinking it.
This is a database shrink. I opened SSMS and right clicked the database and selected shrink.
Is there a way to extend the timeout period for the lock during this process? No users are using this copy of the db so I'm confused as to why it said a file was in use. Should I first put it into single user mode?
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
March 19, 2011 at 4:20 pm
MothInTheMachine (3/19/2011)
Is there a way to extend the timeout period for the lock during this process?
Yes, write the query, don't use the GUI. Query windows have timeout of 0 (forever)
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 19, 2011 at 7:10 pm
Thanks Gail. It completed. It took just under one hour using:
DBCC SHRINKDATABASE ( 5, NOTRUNCATE) . I used NOTRUNCATE to prevent the space from being released to the OS, which an earlier reply had suggested.
Results:
dbidfieldcurrentsizeminsizeusedpagesestimatedpages
53146841282561468316814683168
54131028802561310145613101456
55147104002561470678414706784
56130995282561309447213094472
571310720064000001310428013104280
581310720064000001309483213094832
591310429664000001310056013100560
5101310604864000001310061613100616
52269417663269417656
When I ran it via the interface it took a little more than 2 hours before it returned the timeout error.
One question, even though the previous 2 attempts to shrink the db failed, is it possible that some shrink took place?
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;
March 20, 2011 at 3:34 am
Could well have.
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 23, 2011 at 1:39 pm
And indeed, after comparing screenshots of the mdf/ndf files from before and after there was some shrinkage. I went and ran dbcc shrinkdatabase again and specified 10% freespace. After it executed (took a couple of minutes) there was no change, viewing the files in Windows Explorer, of the mdf and ndf files or in the log files.
The command I used was DBCC shrinkdatabase('db_name', 10). I restarted the services too. Does it need a reboot before it actually releases the freed space back to the OS?
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
March 23, 2011 at 1:52 pm
No.
Is there 10% free space?
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 - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply