Backup and Shrinking of Database

All replies

• 

  

  1

  Sign in to vote

  Hey,

  1. It depends. If you are "losing" space. If your transaction logs are eating your disk space then backing them up will help you free some space. 

  2. and 3. It is hard to tell, because this depends on the type of backup and also the amount of data you have in the DBs and transaction logs. Maybe someone else can try and give an estimation here?

  What I will recommend you is to take a look at the following articles:

  What SQL maintenance should I perform on my SCOM 2012 databases?

  Great article, when it comes down to basic SCOM DB administration. Another "must read" on teh topic is provided by Bob Cornelissen:

  Case of the fast growing SCOM datawarehouse db and logs

  In addition take also a look here:

  Choosing the Recovery Model for a Databaseand here

  Complete and Incremental Backups in Operations Manager

  Hope this helps. Regards,

  ---

  Stoyan (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!)

  Thursday, September 29, 2016 9:21 AM
• 

  

  0

  Sign in to vote

  Thanks for your reply and sharing links.

  Can you share your experience.  Generally, how much you have seen it decrease keeping in mind Simple Database Recovery Model and Size of Databases that I mentioned above?

  Thursday, September 29, 2016 8:51 PM
• 

  

  1

  Sign in to vote

  Hi Sir,

  >>So, even if I continue to do shrinking it will not give me much space and I will be shrinking it every so often. 

  "Please keep in mind that we don’t support/recommend EVER shrinking a DB file for
  OpsMgr. It causes fragmentation issues. "

  https://blogs.technet.microsoft.com/stefan_stranger/2009/08/15/everything-you-wanted-to-know-about-opsmgr-data-warehouse-grooming-but-were-afraid-to-ask/

  >>We have so far not taken backup of any Database (OpsMgr, DW or ACS).

  >>What would be the size of backup of OpsMgr (150 GB), DW (1 TB) and ACS (1 TB)?

  Have you counted the size of log file ?

  http://www.bictt.com/blogs/bictt.php/2014/10/10/case-of-the-fast-growing

   

  Best Regards,

  Elton

  ---

  Please remember to mark the replies as answers if they help and unmark them if they provide no help.
  If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

  Friday, September 30, 2016 2:25 PM
• 

  

  0

  Sign in to vote

  Thanks for the reply; appreciate it.

  I am aware that shrinking on any of the database of SCOM is not recommended.  But, I do not have any disks available currently hence resorting to shrinking.

  For the size of the DB and Logs.  My DW DB size is around 1 TB with TL being around 500 MB.  I am using simple model.  This is the case with all DBs with very little TL and very big DB Size.

  Manual shirking has not done much for me.  It was used to free 50 GB or something, but now that has reduced to few GBs now.

  My question is how to get out of this.  If somehow I am able to take the backup of all the databases, would it reduce the size of the Databases?  If not, how can I reduce the size of the database?  

  Monday, October 3, 2016 1:39 PM
• 

  

  1

  Sign in to vote

  Hmmm,

  a tricky one. What I would do if I were you is:

  - First I will make sure that my data retention settings are OK and suited to my reporting requirements. 

  Understanding and modifying Data Warehouse retention and grooming

  This is MUST read in your case.

  - Then I will go and lower the retention settings for the DW.

  I would also not recommend shrinking...It could cause nasty consequences to your DB. Actually Kevin Holman made a pretty remembering comment on the topic (comment section of the same article):

  "You should NEVER EVER EVER "shrink" a database file that is hosting a SCOM database.  This will cause MAJOR performance issues.  Forcing a SQL shrink operation causes the DB file to shink in size and recover free space in the database, reducing the file size and freeing up space on disk.  While this sounds good – it is BAD.  It causes fregmentation of the DB that cannot be resolved by a simple reindex operation.
  
  Again – NEVER shrink an OpsMgr database.  Whatever size it has grown to – leave it that size.  Free space in a DB file is a GOOD thing – backups only back up used space – this doesnt hurt anything.  If you MUST recover the used DB file space, then get with a SQL expert who understand how to create a new file on a new disk and move ALL the data from one DB file to a new DB file, to keep the DB from fragmenting." 

  So at the end, after modifying the grooming settings you will just have more space in the DB file.

  Now, I know this is not the thing you wanted to hear, but it is the way it is. SO If I were you and I had to definitely recover the used space from the DB file I would seek the help of and SQL expert to help me with moving the data to a new DB file. 

  Hope I could help. Regards,

  
  

  ---

  Stoyan (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!)

  Monday, October 3, 2016 1:54 PM
• 

  

  0

  Sign in to vote

  Thanks for the reply.

  1.  Grooming settings for all databases are in place at much lower than what is generally recommended.  So, I have set it to as low as I can.  But, the DW Database is growing every minute.  In other words, even after change in grooming settings, I am not seeing any decrease in database size?  How can I reduce it now?

  2.  Secondly, if somehow I am able to take the backup of all the databases, would it reduce the size of the Databases?  If not, how can I reduce the size of the database?  

  Thanks in advance.

  Monday, October 3, 2016 2:21 PM
• 

  

  1

  Sign in to vote

  Hey,

  To 1: could it be that you are checking the size of the file (allocated space by the DB file, which would not change), instead of checking the free space of your DB file? How are you checking this? This could explain why you don't see any changes on physical level. Still your DB file will have more free space, which a is a good thing.

  To 2: If your TLs are small then even if you make backups of them, this would not be a game changer. I cannot think of other way of reducing the allocated space by the DB file, then creating a new DB and moving the data to it. 

  Hope this makes sense to you. Regards,

  ---

  Stoyan (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!)

  Monday, October 3, 2016 2:51 PM
• 

  

  0

  Sign in to vote

  Thanks for the reply.

  1.  I am checking on the File System i.e. the physical location of the DB and TL files for the instance where DBs are installed.  Am I doing it wrong?

  2.  Goodness gracious, so, backup in that case would not do much as it would not decrease the size of the DB only perhaps of TL which is not much to begin with.  Please, suggest how to manage it.  Do you have any link to article or blog which I can follow to create new DB and move data into it?

  3.  Would changing Recovery Model do anything to the database sizes?  From Simple to Full and back to SImple?

  Thanks in advance?

  Monday, October 3, 2016 8:26 PM
• 

  

  0

  Sign in to vote

  Hi,

  1. What you are checking is the disk space used, by the DB file itself. Still this is the space that has been allocated, but it could be that your actual data is a lot smaller then the file itself. You can take a look at the example with standard SQL reports here:

  SCOM – Check Size of Databases (SQL Standard Reports)

  or use one of the methods from this article:

  Display Data and Log Space Information for a Database

  I think when you see the data chart from the standard reports you will understand why I asked about the way you are checking the size.

  2. Hm...that is the hard part... I am not an SQL expert, but the right way would be the way Kevin suggested:

  "If you MUST recover the used DB file space, then get with a SQL expert who understand how to create a new file on a new disk and move ALL the data from one DB file to a new DB file, to keep the DB from fragmenting."

  It is not just moving a DB file, which is straight-forward. It is about creating new DB file and moving the data and this is pretty tricky.

  Hope this helped. Regards,

  ---

  Stoyan (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!)

  
  
  

  • Edited by Stoyan ChalakovMVP Tuesday, October 4, 2016 7:32 AM

  Tuesday, October 4, 2016 7:23 AM
• 

  

  0

  Sign in to vote

  Thanks for the reply; appreciate it.

  1.  I will check and let you know about it.  What if I change the size of the database manually through properties?  Would it work? Is it a good Idea?

  2.  Is there any article or blog you can share with me that I can read and understand it better plus probably do the same with little care.

  4.  You did not answer about the Recovery Model question.  Can changing it back and fourth reduce the size somewhat?

  Thanks in advance.

  Tuesday, October 4, 2016 7:58 PM
• 

  

  0

  Sign in to vote

  Hi,

  1. I think NOT. Please don't do this.  You can  use  DBCC_SHINKDATABASE 'OperationsManagerDW' to shrink the DB after you have configured the grooming settings:

  How to shrink OperationsManagerDW database file size

  That is the only way I think. Playing with "Auto-Shrink" is not a good idea. 

  2. How to shrink OperationsManagerDW database file size

  And also:

  Everything you wanted to know about OpsMgr Data Warehouse Grooming but were afraid to ask

  All of the articles posted in the thread so far are very very informative and must be read to get a better understanding of all the aspects.

  3. I doubt that changing the model will have any affect currently. Like suppose your problem isn't the data size, but the file size (allocated space on the disk). So playing with the backup recovery model won't matter here.

  Regards,

  ---

  Stoyan (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!)

  • Proposed as answer by Elton_Ji Wednesday, October 12, 2016 9:43 AM
  • Marked as answer by Elton_Ji Wednesday, October 12, 2016 3:57 PM

  Wednesday, October 5, 2016 7:29 AM
• 

  

  0

  Sign in to vote

  Thanks for the reply.

  I have already tried shrinking, but it only provides very little space after shrink operation.  How can DW DB take a TB of space even after Grooming Settings set to 60 days?  Should not it automatically change the size or purge old data?  If not, how can I achieve that?

  Wednesday, October 12, 2016 7:55 PM
• 

  

  0

  Sign in to vote

  gentle reminder.

  Monday, October 24, 2016 8:34 PM