SharePoint 2010 Database

As a developer, I always less focus on the database. But the database configuration is the key to the performance of SharePoint.

Here is the some key points:

DBCC

run the DBCC CHECKTABLE on the tables that you find having some sort of fragmentation in all the the SharePoint Databases. You can schedule an SQL Agent Job to run the command

The DBCC CHECKDB command should be run on SharePoint DBs once a week.

Here is a good blog http://programming4.us/database/2924.aspx and http://www.sharepointboris.net/2008/10/sharepoint-config-database-log-file-too-big-reduce-it/

Initial Size for primary data file,transaction log

There are a few performance concerns with these Files settings. The Initial Size value is small; the Autogrowth value is small; and the Path of the files is pointing to the default directory on drive C.

For instance, if you were to upload a 10 MB file into this database using the default Initial Size and Autogrowth settings, SQL Server would have to lock the database 8 to 10 times to grow the data file in 1-MB increments until there was enough room to accept the 10-MB file you wanted to upload. Furthermore, because the log file Initial Size is small and its Autogrowth setting is at 10 percent increments, this file would also have to grow to accept the file being uploaded. Also, each time these files are enlarged in 1-MB increments, it causes fragmentation of your hard drive. As you can imagine, this can have an enormous impact on your SharePoint performance.

This is why it is important that you carefully consider how much information will be contained within most of your SharePoint databases, as well as how much information will be added, modified, or deleted, before you modify the Initial Size setting in the Model database. After you make the change, all new databases created using the Model database will begin with that Initial Size value, which will eliminate—or at least reduce—the need for Autogrowth to occur. There is no magic number that is best for the Initial Size setting of the content databases; you must perform a careful analysis to make that determination yourself. However, the best practice is that the size of your content databases should not exceed 100 GB. This is a soft limit that will increase the chances of performing a recovery in less than four hours.

---

Note:

Your database transaction log initial size is normally 25 percent of the size of the associated data file.

	SharePoint 2010 (recommended max)
Items per view	5000
Documents per library	10 million
Database size	200GB (up to 1TB for workloads)
Simultaneous Doc Editors	10 (max at 99)
Column	New Row Wrapping (8,000 bytes)
Content Databases per Web App	300
App Pools per web server	10
Indexed (Crawl Count)	100 Million per search Application
Site Collections per Web App	500,000

 

Microsoft has an good article on SQL Capacity planning and configuration. and Joe has a good one for SharePoint 2010 Capacity Planning

An exception occurred while enqueueing a message in the target queue. Error: 15404, State: 19. Could not obtain information about Windows NT group/user ‘DOMAIN\ppsservice’, error code 0×5.

The problem I found was the SQL Server Service was running on a invalid admin account.

Once I changed the account, no errors anymore

Generate insert script from existing data table

You can generate a create table script from SQL 2005 but there is no oob to generate an insert script.

Here is the link to create a sp which will generate script for you.
http://vyaskn.tripod.com/code/generate_inserts_2005.txt.

Check out the My code library to see any script can be used in your project.

How To Connect mdf in app_data

Sometime people would like to put mdf in APP_Data so they can zip the database with the code.

the connection string will look like

That’s fine for SQL Express installed but if you are running SQL 2005, you may get error to connect the database.
After a while try, I found you can see the mdf from Server Explorer -> Data Connections. Try to connect the server and have a look the property. you will see the connection string like

Data Source=.\SQLEXPRESS;AttachDbFilename=”C:\VS2008 Projects\Ajax2008\Ajax2008\App_Data\Northwind.mdf”;Integrated Security=True;User Instance=True

So after I added the User Instance=True, it works.

SQL Server 2008 RTM IS Ready

http://msdn.microsoft.com/en-us/subscriptions/downloads/default.aspx Has Ent, Dev, Std, Web and Workgroup for subscriber downloads, can’t see Express or Ent Trial yet on the non subscriber pages.