Solved

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

Posted on 2012-12-23
5
1,117 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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In case you ever have to remove a faulty web part from a page , add the following to the end of the page url ?contents=1
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

630 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