Solved

sharepoint 2010 database - data file shrink for service application databases.

Posted on 2012-12-23
5
1,103 Views
Last Modified: 2012-12-31
Hi Friends,

I get an message on Central Admin of SharePoint 2010 farm as a part of health reports:

1.Title - Database has large amounts of unused space
2.Explaination -
Following databases have large amounts of space allocated on the disk but not in use. This may be due to recent deletion of data form the database, or because the database has been pre-grown to a larger size. This database will take up a larger amount of space on the file system unless it is shrunk down to a smaller size. DSI_Search_Service_PropertyStoreDB on <db instance name>.

3. Remedy -

The database can be shrunk in size using the DBCC ShrinkDatabase command or the Shrink Database wizard in SQL Server Management Studio. For more information about this rule, see "http://go.microsoft.com/fwlink/?LinkID=167144".

4. Failing Services -

SPTimerService (SPTimerV4)
I am pretty much aware about shrinking of content databases.
I want to know how much risky is the shrinking of service app databases or any other sp system database is?

From your ie expert point of view.

This db .mdf is 3 gb & not a problem at all.

started concerning after i saw this -

http://technet.microsoft.com/en-us/library/cc262731(v=office.14).aspx#DBMaintenanceForSPS2010_ShrinkingDataFiles
0
Comment
Question by:Minesh Shah
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 29

Accepted Solution

by:
QPR earned 300 total points
ID: 38717959
Is a bit of a silly message that one. Database contents will grow but when content is deleted they don't shrink. This is not an issue unless you are low on disk space. If you shrink it then the database will need to "ask" the operating system for more disk space next time it needs to grow to accommodate more content. This is a CPU intensive process and can have performance impacts if done too often.
Some time later when content is deleted the central admin message will reappear.
Personally I'd ignore it. If you'd rather not see the message then you can edit the monitoring rule to not show
0
 
LVL 38

Assisted Solution

by:Justin Smith
Justin Smith earned 50 total points
ID: 38718443
Agreed with above.  Disable the rule, it's irrelevant.
0
 
LVL 6

Assisted Solution

by:Sushanta Sahu
Sushanta Sahu earned 150 total points
ID: 38720974
With SharePoint 2010 became more database intensive as compared to it's predecessor, it's important that the health of the databases are maintained. SharePoint manages the databases on it's own and only warns you about any possible issues like the one you have mentioned.

The specific database you have mentioned is expected to grow significantly as a search database. It completely depends on the actual contents. Ideally a data file of with high empty space is not recommended just because the underlying SQL tier should be stable. However these warnings are not critical and gives you an opportunity to review the capacity of the databases and analyse the situation. If you expect the property database to grow significantly due to the content growth, you should not take any action. Also the log file size should also need to be taken care of. Ideally the log file size should be 30% of the datafile (mdf) size.

If you do not expect any growth, then you should shrink the DBs so that the SQL tier remains stable. This is worth to note that these are all prevention to keep the platform healthy and a good platform rely on proactiveness rather than reactive fix.

Let us know how it goes.
0
 
LVL 10

Assisted Solution

by:Minesh Shah
Minesh Shah earned 0 total points
ID: 38722829
I am not going for this, ignoring this.
Not worth trying this since space is not anyways in any kind of alarming level.
If something has held up more space than should have been. i am okay for now.

I see Microsoft has document below in this regard.

1.      Only perform shrink operations rarely, and only after you perform an operation that removes a lot of data from a database, and then only when you do not expect to use that free space again. Data file shrink operations cause heavy index fragmentation and are extremely resource-intensive.

2.      Shrink only content databases. Users and administrators do not delete enough data from the configuration database, Central Administration content database, and various service application databases to contain significant free space.

Link –
http://technet.microsoft.com/en-us/library/cc262731(v=office.14).aspx#DBMaintenanceForSPS2010_ShrinkingDataFiles
0
 
LVL 10

Author Closing Comment

by:Minesh Shah
ID: 38732221
ok
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question