Sharepoint Forum

Ask Question   UnAnswered
Home » Forum » Sharepoint       RSS Feeds

SQL Server 2005 transaction logs

  Asked By: Nadia    Date: Jul 12    Category: Sharepoint    Views: 870

We keep SQL Server 2005 databases and transaction logs on different
drives for performance reasons. When I create a new database from SQL
Server Management Studio, it puts the database on one drive and log on
other drive just like it should. However, when I add a content database
to a SharePoint Portal Server 2003 portal using Central Administration,
it puts both the database and log on the database drive. Is this a
glitch just with SharePoint? Has anyone else experienced this?



10 Answers Found

Answer #1    Answered By: Alisha Itagi     Answered On: Jul 12

Can you verify that both the configured values and running values have the
correct location for the transaction  logs? Has sql  been restarted since you
changed the values?

Answer #2    Answered By: Maricela Conway     Answered On: Jul 12

Configured and Running values:

Data: G:\MSSQL\Data

Log: F:\MSSQL\Logs

Servers have been restarted many times since we upgraded from SQL Server
2000 to SQL Server 2005 about two months ago.

Answer #3    Answered By: Vinay Thakur     Answered On: Jul 12

Same problem here....

We have a similar setup with our data and log files on seperate
drives and every time we create a new content db it puts both the
data and log files on the C: drive. We have to manually go in and
move the files to their appropriate drive letter.

We have the default settings in SQL 2005 for the location of the data
and log files setup, but SharePoint still puts the files on the C:
drive under Program Files/Microsoft SQL Server/MSSQL.1/....

We are running WSS 3.0.

Answer #4    Answered By: Caleb Gordon     Answered On: Jul 12

I believe that the default location for database/log placement is to create them
on the same drive that SQL installed to, so making the change to the default DB
path should fix the problem.

Answer #5    Answered By: Micheal Knight     Answered On: Jul 12

It appears that WSS 2.0 and WSS 3.0 (at least in Cody and my
environments) are ignoring default database and/or log locations when
using SQL Server 2005. I didn't have this issue with SQL Server 2000.
I'm going to call Microsoft PSS on this one.

Answer #6    Answered By: Judy Pittman     Answered On: Jul 12

Make sure and report back what you find. I have both WSS v2 and MOSS pointing to
SQL 2005 servers and they have behaved themselves in this regard. I'm curious
the difference between our two setups.

Answer #7    Answered By: Joanne Greer     Answered On: Jul 12

I'm curious also as we have no problem either with 2005. Could it
have to do with where the files for your system dbs (specifically
model) are located? Are they also on the split drives?

Answer #8    Answered By: Cathy Cameron     Answered On: Jul 12

That could be the answer. The model and master mdf files are still
located on the C: drive under the default install location for SQL. I
am going to get with my DBA to see if they can be moved without
killing SQL.

Answer #9    Answered By: Kerri Steele     Answered On: Jul 12

I have opened a case with Microsoft PSS on this issue. The SharePoint
tech support guy was stumped and handed it off to SQL tech support.
I'll let you know what I find out. BTW, you can move user database and
transaction logs  per KB article 224071.

Answer #10    Answered By: Alisha Itagi     Answered On: Jul 12

Microsoft PSS was able to solve this problem. The registry key
DefaultLog was missing from registry at location:

HKey_Local_Machine\Software\Microsoft\Microsoft SQL

The DefaultData key was there but not DefaultLog. I added the key on
both SQL cluster nodes and rebooted servers. All new WSS content
database transaction  logs are now being created in the correct location.

Didn't find what you were looking for? Find more on SQL Server 2005 transaction logs Or get search suggestion and latest updates.