kelltek
asked on
Sharepoint Services v3 Config Database log file grown to 8GB!!! yet database is 9mb - Why???
Hi,
We have SPS v3 running well with various sites created (approx 6) today our server ran out of space on C:. I am researching how to relocate the default SQLEXPRESS database directorys onto a larger disk, but whilst looking around the server I found that the Sharepoint_Config LOG file was the main culprit as it was 6.6GB in size!!
Question is: Has anyone else noticed large log files in SPS v3, how can I delete it (if possible) and why might it be growing so much?
Thanks
We have SPS v3 running well with various sites created (approx 6) today our server ran out of space on C:. I am researching how to relocate the default SQLEXPRESS database directorys onto a larger disk, but whilst looking around the server I found that the Sharepoint_Config LOG file was the main culprit as it was 6.6GB in size!!
Question is: Has anyone else noticed large log files in SPS v3, how can I delete it (if possible) and why might it be growing so much?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Download the SQL Server Management Studio Express at http://go.microsoft.com/fwlink/?linkid=65110
Thanks,
Paul Liebrand
Thanks,
Paul Liebrand
ASKER
Sorry one other Q, untils I re-locate the DB files from C I have limited space to use, does the BACKUP LOG element use and diskspace, if so can I specify an alternate to C:?
I do not believe they use disk space, so you should be fine to run them.
ASKER
OK, I have connected to the SQL Express using:
sqlcmd -S \\.\pipe\mssql$microsoft##
when I try the USE command I get an error:
1> USE SharePoint_Config_40e02456
2> go
Msg 911, Level 16, State 1, Server SRVINF05\MICROSOFT##SSEE, Line 1
Could not locate entry in sysdatabases for database 'SharePoint_Config_40e0245
. No entry found with that name. Make sure that the name is entered correctly.
sqlcmd -S \\.\pipe\mssql$microsoft##
when I try the USE command I get an error:
1> USE SharePoint_Config_40e02456
2> go
Msg 911, Level 16, State 1, Server SRVINF05\MICROSOFT##SSEE, Line 1
Could not locate entry in sysdatabases for database 'SharePoint_Config_40e0245
. No entry found with that name. Make sure that the name is entered correctly.
ASKER
Can anyone recommend anything else?
Many thanks to liebrand but I cannot progress using his suggestion as the command fails.
Many thanks to liebrand but I cannot progress using his suggestion as the command fails.
Have you tried connecting to the SQL database using the SQL Express Studio Manager instead of the command line and executing the command?
ASKER
Hi liebrand,
Unfortunatly, I am not familar with the Studio Manager and cannot find where/how to enter your script and run it?
Unfortunatly, I am not familar with the Studio Manager and cannot find where/how to enter your script and run it?
Once you connect to the database, you will see a menu option titled "New Query". Clicking this will open a blank editor type screen on the right -- paste the script in there and click on the Execute script button.
ASKER
Hi liebrand,
Thanks for the pointer, I have pasted your code and executed, got the following errors:
Msg 911, Level 16, State 1, Line 3
Could not locate entry in sysdatabases for database 'SharePoint_Config'. No entry found with that name. Make sure that the name is entered correctly.
(1 row(s) affected)
I also tried the following code:
BACKUP LOG SharePoint_Config_40e02456
USE SharePoint_Config_40e02456
GO
DBCC SHRINKFILE (SharePoint_Config_40e0245
GO
And this gave the following errors:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 911, Level 16, State 1, Line 3
Could not locate entry in sysdatabases for database 'SharePoint_Config_40e0245
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
The latter code contains the full database name.
Any ideas?
Sean
Thanks for the pointer, I have pasted your code and executed, got the following errors:
Msg 911, Level 16, State 1, Line 3
Could not locate entry in sysdatabases for database 'SharePoint_Config'. No entry found with that name. Make sure that the name is entered correctly.
(1 row(s) affected)
I also tried the following code:
BACKUP LOG SharePoint_Config_40e02456
USE SharePoint_Config_40e02456
GO
DBCC SHRINKFILE (SharePoint_Config_40e0245
GO
And this gave the following errors:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 911, Level 16, State 1, Line 3
Could not locate entry in sysdatabases for database 'SharePoint_Config_40e0245
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
The latter code contains the full database name.
Any ideas?
Sean
ASKER
Hi,
I have found a solution as follows:
1) Backup the log file by right clicking on the database and selecting backup.
2) Set the Config database to simple recovery model (Right Click on database / properties / options)
3) Right click on database and select Tasks/Shrink/Files and select file type = log, action = release unused space.
Thansk for your help librand, it looks as though the scripting does not like the - character so I achieved the same using the GUI tools.
I have found a solution as follows:
1) Backup the log file by right clicking on the database and selecting backup.
2) Set the Config database to simple recovery model (Right Click on database / properties / options)
3) Right click on database and select Tasks/Shrink/Files and select file type = log, action = release unused space.
Thansk for your help librand, it looks as though the scripting does not like the - character so I achieved the same using the GUI tools.
We're running Windows Sharepoint Services V3 that came with Windows Server 2003 and the above solutions did not work for us. We received the same error messages and the Studio Manager also gave us access errors. I found the answer in this location http://dotnet.org.za/calmyourself/archive/2007/07/11/managing-your-sql-log-files.aspx
re: Managing your SQL Log files
@Diabolo:
Sharepoint 2007 creates its databases with 'full recovery' model. However, it has very basic backup support in which it only backups the databases, NOT the transactions logs.
Your logs will only grow, unless:
1. You use your own backup strategy in which you backup both the databases and the transaction logs yourself. This will however invalidate the 'restore' option in the Sharepoint Central Administration.
2. You let Sharepoint make the backups, but you will manually truncate the logs on scheduled intervals. This can be scripted using the following T-SQL commands:
(just an example truncating a config db logfile to 70 MB):
BACKUP LOG [Sharepoint_Config] TO DISK='drive:\path\Yourback
GO
BACKUP LOG [Sharepoint_Config] WITH TRUNCATE_ONLY
USE [SharePoint_Config]
GO
DBCC SHRINKFILE (N'SharePoint_Config_log' , 70) Note: the 70 is the 70MB.
GO
If you don't care about a point-in-time restore, you can skip the first backup statement.
For detailed information, see: support.microsoft.com/.../
Additional Info:
Use the Brackets [ ] and enter the whole log file name including the numbers after SharePoint_Config in the backup and use commands. Don't use the _log or file name extension in these sections.
Example: BACKUP LOG [SharePoint_Config_b1825e6
Then in the DBCC section, use the whole name with numbers and _log with no file name extension.
Example: DBCC SHRINKFILE (N'SharePoint_Config_b1825
re: Managing your SQL Log files
@Diabolo:
Sharepoint 2007 creates its databases with 'full recovery' model. However, it has very basic backup support in which it only backups the databases, NOT the transactions logs.
Your logs will only grow, unless:
1. You use your own backup strategy in which you backup both the databases and the transaction logs yourself. This will however invalidate the 'restore' option in the Sharepoint Central Administration.
2. You let Sharepoint make the backups, but you will manually truncate the logs on scheduled intervals. This can be scripted using the following T-SQL commands:
(just an example truncating a config db logfile to 70 MB):
BACKUP LOG [Sharepoint_Config] TO DISK='drive:\path\Yourback
GO
BACKUP LOG [Sharepoint_Config] WITH TRUNCATE_ONLY
USE [SharePoint_Config]
GO
DBCC SHRINKFILE (N'SharePoint_Config_log' , 70) Note: the 70 is the 70MB.
GO
If you don't care about a point-in-time restore, you can skip the first backup statement.
For detailed information, see: support.microsoft.com/.../
Additional Info:
Use the Brackets [ ] and enter the whole log file name including the numbers after SharePoint_Config in the backup and use commands. Don't use the _log or file name extension in these sections.
Example: BACKUP LOG [SharePoint_Config_b1825e6
Then in the DBCC section, use the whole name with numbers and _log with no file name extension.
Example: DBCC SHRINKFILE (N'SharePoint_Config_b1825
Is there a way to limit the size of this file?
ASKER
Quick Q prior to me doing this:
This is OK for Embedded SQL Express? If so what it is the command line for the user interface with SQL Express?