Marc Lognoul's IT Infrastructure Blog

Cloudy with a Chance of On-Prem

SharePoint 2007: Relocating data and log files

Leave a comment

I usually work in environment where WSS/MOSS is deployed with a remote SQL server. While reading the NG’s, my attention was caught by a person complaining about the lack of disk space on system partition after a few weeks of WSS usage. This reminded me that during the setup of WSS v3, if you chose for a “Basic” installation, the setup process will install and automatically configure Microsoft SQL Embedded Edition (MSEE) as database and set the default path for databases to %SystemRoot%SYSMSISSEEMSSQL.2005MSSQLData, which is obviously not ideal in the long run.

Since I don’t like to work the half, you’ll find hereunder method to relocate all WSS-related files that may grow with time and become therefore problematic on a system partition:

  • All SharePoint databases
  • The SQL “tempdb” (trust me, it may sometimes become really problematic, isn’t it G. N.?)
  • The IIS log files
  • The WSS Search Index files (and related)
  • The trace log files

Now let’s say you’re running WSS with the default configuration, everything on the system partition, here is how to do the relocation job:

0. Download & Install the necessary software if they are not from the page http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en, download the software named “Microsoft SQL Server Native Client” and “Microsoft SQL Server 2005 Command Line Query Utility” and install them on your server. Make sure you chose the appropriate version (x86, x64…)

1. Create data folders on another partition such as
D:SQL_DATA
D:WSS_DATA
D:IIS_LOGS
D:WSS_INDEX
D:WSS_TRACE_LOGS

2. List the SharePoint databases and keep the results at hand. You can simply use the dir command such as:
dir /b “%SystemRoot%SYSMSISSEEMSSQL.2005MSSQLDataWSS_*.*”
dir /b “%SystemRoot%SYSMSISSEEMSSQL.2005MSSQLDataSharePoint_*.*”

3. Stop all WSS-related services. Using command-line, it would give something like:
net stop “Windows SharePoint Services Search”
net stop “Windows SharePoint Services Timer”
net stop “Windows SharePoint Services Tracing”
iisreset /stop

4. Detach databases and physically move the files to the new location
Open a command prompt and set its path to %PROGRAMFILES%Microsoft SQL Server80ToolsBinn
Execute the command sqlcmd -S
\.pipemssql$microsoft##sseesqlquery -E. This will start the SQL command-line utility
For each databases noted at step 2, execute the commands hereunder taking care not writing the extension (.mdf or .mdf) or the name of the log file (_log.ldf):
EXEC sp_detach_db WSS_Content
GO

This should normally look like:
EXEC sp_detach_db WSS_Content
GO
EXEC sp_detach_db WSS_Search_SRV01
GO
EXEC sp_detach_db ‘SharePoint_AdminContent_8c523776-d06c-4460-b7ec-f321aa2032ba’
GO
EXEC sp_detach_db ‘SharePoint_Config_9aae4bcb-37e4-4a88-b98b-40fc180095f0’
GO
Open another command-prompt and execute the command below to move the files:
move %SystemRoot%SYSMSISSEEMSSQL.2005MSSQLDataWSS_*.* D:WSS_DATA
move %SystemRoot%SYSMSISSEEMSSQL.2005MSSQLDataSharePoint_*.* D:WSS_DATA

Return to the SQL command-line utility and execute the instructions hereunder to re-attach database. Make sure you repeat it for each database.
EXEC sp_attach_db @dbname = N’DBNAME’, @filename1 = N’D:WSS_DataDBNAME.mdf’, @filename2 = N’D:WSS_DataDBNAME_log.LDF’
GO

This should normally look like:
EXEC sp_attach_db @dbname = N’WSS_Content’, @filename1 = N’D:WSS_DataWSS_Content.mdf’, @filename2 = N’D:WSS_DataWSS_Content_log.LDF’
GO
EXEC sp_attach_db @dbname = N’WSS_Search_SRV01′, @filename1 = N’D:WSS_DataWSS_Search_SRV01.mdf’, @filename2 = N’D:WSS_DataWSS_Search_SRV01_log.LDF’
GO
EXEC sp_attach_db @dbname = N’SharePoint_AdminContent_8c523776-d06c-4460-b7ec-f321aa2032ba’, @filename1 = N’D:WSS_DataSharePoint_AdminContent_8c523776-d06c-4460-b7ec-f321aa2032ba.mdf’, @filename2 = N’D:WSS_DataSharePoint_AdminContent_8c523776-d06c-4460-b7ec-f321aa2032ba_log.LDF’
GO
EXEC sp_attach_db @dbname = N’SharePoint_Config_9aae4bcb-37e4-4a88-b98b-40fc180095f0′, @filename1 = N’D:WSS_DataSharePoint_Config_9aae4bcb-37e4-4a88-b98b-40fc180095f0.mdf’, @filename2 = N’D:WSS_DataSharePoint_Config_9aae4bcb-37e4-4a88-b98b-40fc180095f0_log.LDF’
GO

Now relocate the tempdb database by executing the following command in the SQL command-line utility:
USE master
GO
ALTER DATABASE tempdb modify file (name = tempdev, filename = ‘D:SQL_DATAtempdb.mdf’)
GO
ALTER DATABASE tempdb modify file (name = templog, filename = ‘D:SQL_DATAtemplog.ldf’)
GO

Exit SQL command-line utility by typing “Exit”
Restart the SQL Server Instance to apply change by executing the following commands:
net stop “SQL Server 2005 Embedded Edition (MICROSOFT##SSEE)”
net start “SQL Server 2005 Embedded Edition (MICROSOFT##SSEE)”

5. Reconfigure IIS and Move Logs
Return to the command-prompt and execute the command taking care of replacing MYWSSITEID with the actual site id of your WSS site. To find it out, you can use the IIS Console and click on “Web Sites”, the is will appear on the right pane under the column name “identified” (second one)
cscript.exe /nologo c:InetpubAdminScriptsadsutil.vbs Set W3SVC/MYWSSITEID/LogFileDirectory D:IIS_LOGS
Note: if you wish to use centralized logging and therefore log all web site activity to the same folder, execute the command hereunder:
cscript.exe /nologo c:InetpubAdminScriptsadsutil.vbs Set W3SVC/MYWSSITEID/LogFileDirectory D:IIS_LOGS
Move the file from %windir%system32logfiles  to D:IIS_LOGS
Restart IIS by executing
iisreset /start
Then time to restart the WSS services by executing
net start “Windows SharePoint Services Search”
net start “Windows SharePoint Services Timer”
net start “Windows SharePoint Services Tracing”

Optionally, look into the Application event logs for possible issues

6. Relocating the WSS Search Files
Return to the command-prompt, set the path to %programfiles%Common FilesMicrosoft Sharedweb server extensions12BIN and execute the command hereunder:
stsadm -o spsearch -indexlocation D:WSS_INDEX

7. Relocating the WSS Trace Logs
For this one, stsadm does not offer any command-line-based solution so we’ll need to go back to the GUI.
Open the SharePoint Central Administration Web Site
Click on the tab “Operations”
Under the section “Logging and Reporting”, click on the link “Diagnostic logging”
Scroll down to the section “Trace Log” and on the right, enter “D:WSS_TRACE_LOGS” as path then click “OK”
Optionally, move the files under %programfiles%Common FilesMicrosoft Sharedweb server extensions12LOGS to D:WSS_TRACE_LOGS

If you wish to the the Trace Log fiels location using STSADM anyway, you may consider using Gary Lapointe’s valuable extensions for STSADM: http://stsadm.blogspot.com/2008/06/trace-log-settings.html

 

Advertisements

Author: Marc Lognoul

Relentless cloud professional. Restless rider. Happy husband. Proud father. Opinions are my own.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s