Trying to figure out why I have multiple SQL log.LDF files?

Hi all,

Been finding information on this web site for a while now, but finally had to make a post Of course I am running out of space on my SQL 2005 server and trying to narrow down the problem to make the proper backup and move on. The issue is that I have multiple log.LDF files for one database and one of them is 25GB! Creating a backup of the database and transaction log does not seem to be shrinking this log file. I do keep the backups on another disk. So my question is why is the backup not shrinking this log file as it should?

I did try to Shrink the file in the Object Explorer by right clicking on the database SharePoint_Config -> Tasks -> Shrink -> Files. It says that there is currently 2.50MB of space allocated to the Log file of SharePoint_Config_log.LDF. But as you will see below, this file does not have the same sequence of numbers and letters after the file name. Shrinking the file using any of the available Shrink options is not working. Its still at 25GB.

Here are the names of the multiple log.LDF files, files sizes of log and corresponding .mdf, and last modified from explorer. In the Object Explorer within SQL Server Management Studio, the name of this database is SharePoint_Config. Under the properties of the database the File Name is just SharePoint_Config.mdf and SharePoint_Config_log.LDF so I am not sure where all the extra numbers and letters are coming from? There is only 1 file Group.

SharePoint_Config_60b51b5e-69f3-4c04-a11d-ee11b4205cfe_log.LDF
Log file is 25GB and was last modified today
Correspondng mdf is only 8MB and was modified about a month ago

SharPoint_Config_9d4f17ce-2105-46c0-8ace-52f62616dcba_log.LDF
Log file is 34.8MB and last modified in 2007
Correspondng mdf is only 5MB and was modified in 2007

SharePoint_Config_cc0076f-c48f-47a8-8ad7-c0cf2c62ae41_log.LDF
Log file is 3MB and was last modified in 2007
Correspondng mdf is only 5MB and was modified in 2007

I am really stumped, any help is greatly appreciated, and I am standing by to try all suggestions. Thanks!

Welcome to forums.
I haven't worked with Sharepoint from database management point of view, to answer why it was implemented that way, but I take it as any other database.

• As you figured out, taking a log backup does not automatically shrink the file. You need to perform Shrink database or shrink file. When you select Shrink file option, By default it will show the mdf file. You need to select Log file as the filetype and then select the currect file under file name. DId you do that?
• You mentioned earlier, that one database is having multiple log files.Then you are saying that I dont know from where the other files are coming from. It is abit confusing.
• You also mentioned that the log file name is Sharepoint_Config_log.LDF. But none of the files listed by you have that name. Is something missing here?
• You can also try by selecting Shrink database.
• Actually You need only one log file per database as log entries are written serially, You can safely remove the other log files even if they are connected to any database (Do it after a backup and do another backup after deleting the additional log files.

Hope this helps, IF not please explain further on your question.

Hi preethi, and thank you for your reply.

On your first point. Yes I did try to do that. After I select log, the only file name available from the drop down menu is SharePoint_Config_log. And its location is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SharePoint_Config_log.LDF. I just noticed that this does not point to the same name or location as the log files above( especially the 25GB log file that concerns me. The log files I have listed above are all located in C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\DATA. So I checked all the mdf and log files for each database I have. None of them have mdf or log files located at C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\DATA? Strange thing is that the 25GB log file that concerns me shows that it was modified today. So I bet I could shrink the log file if I could access it from one of my databases within the object explorer.

On your second point. Yes, that does seem a bit confusing. Let me clear this up. Using the Object Explorer to view the databases properties, I can see the location and names of the mdf and ldf files associated with the database named SharePoint_Config. Here are the location and files names that point to the mdf and ldf's.
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SharePoint_Config.mdf
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SharePoint_Config_log.LDF

However, I found similar mdf and ldf files in another location. But I can't seem to find a database that is using these files. In addition, the first .LDF shows its been modified today, but how?
C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\Data\SharePoint_Config_60b51b5e-69f3-4c04-a11d-ee11b4205cfe_log.LDF (This one is the 25GB log file.)
C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\Data\SharePoint_Config_60b51b5e-69f3-4c04-a11d-ee11b4205cfe.mdf
C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\Data\SharePoint_Config_9d4f17ce-2105-46c0-8ace-52f62616dcba_log.LDF
C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\Data\SharePoint_Config_9d4f17ce-2105-46c0-8ace-52f62616dcba.mdf
C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\Data\SharePoint_Config_cc0076f-c48f-47a8-8ad7-c0cf2c62ae41_log.LDF
C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\Data\SharePoint_Config_cc0076f-c48f-47a8-8ad7-c0cf2c62ae41.mdf

On your Third point. I did not leave out any information, but I also did not install this server. I am guessing that someone made a copy of the folder at one point. But these different, yet similar file names seem generated. Maybe they were used at some point and not discarded. I do not see the log files just above connected to a database. But that does not explain how the 25GB log file was modified today. I am viewing the modified data through windows explorer.

On your Fourth point. All the mdf and ldf files for the databases I can view through the object explorer seem to have reasonable file sizes, a couple 25-50 MB databases, and the rest are 8 MB or less. None of the log files are out of control either. None of the database mdf or ldf files need to be shrank that I can see through the object explorer.

On your Fifth point. None of the mdf's or ldf's related to the SharePoint_Config database seem to me connected to the files in the C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\Data directory. So I went ahead and tried to manually delete the 25 GB log file 'SharePoint_Config_60b51b5e-69f3-4c04-a11d-ee11b4205cfe_log.LDF'. Windows reports an "Error Deleting File or Folder". It says "Cannot delete SharePoint_Config_60b51b5e-69f3-4c04-a11d-ee11b4205cfe_log: It is being used by another person or program. This leads me to believe that some database is using this log file, but I can't seem to find which one? Is there another way to find out?

I hope this cleared things up if not I'm here, I'll keep poking around. Thanks again for your reply.

Hi,
Thanks for the detailed explanation
Just check that you do not have another instance of SQL Server in you computer.
From the error you have received, it seems like it is a valid log file. That means you need to remove it from the database, filecannot be deleted by windows DEL command.

This script may help you to get the database

Code:

exec  sp_MsForeachDB 'select ''?'', * from ?.sys.sysfiles Where FileName = ''C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\Data\SharePoint_Config_60b51b5e-69f3-4c04-a11d-ee11b4205cfe_log.LDF'''

BTW, the path you specified is in C: Drive right? (Some people give some file names due to security reasons)
Please check whether it is part of a shared storage and another Clustered instance is not pointing to this drive.

On a side note, it is not a good practice to have the data files/log files in your default windows folder. when these files grow you may eat the space windows needs, and it may lead to windows crashing.

Hi Guys, and thank you for your replies. It took me a few days, but I went through your responses and resources thoroughly to try and solve my problem.

Preethi
I tried a to run your code, but I continued to get an error. I googled syntax errors to try and solve it, but nothing I tried worked. I think your code might be the key to finding the database.

Code:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.

Yes the path I specified is in the C: Drive. No security issues here. I do have one other server in this domain that I also did not setup. I checked all of its databases as well to make sure none of them had a database or transaction logs on this server. Nor did it have any references to the 25GB log file.

Code:

C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\Data\SharePoint_Config_60b51b5e-69f3-4c04-a11d-ee11b4205cfe_log.LDF

Thank you for the side note, I agree that I do need to move these files. I have been swamped at work. We do not have a db administrator, and that is not my job title. But, I was asked to keep this server in good shape to the best of my abilities, and I am trying to do so. I will make time to put those log files somewhere else!

Satya
Thanks for your reply. Through MS SQL Management Studio I ran that command on each database available. MS SQL Management Studio reported that there were no open transactions on any database. Just to be sure, I also ran the command on each database on the other server in the domain. There were no open transactions there as well.

Thank you for the resource you posted. I went through that page completely, and even tried to find information through the "Concept" links as well. But I could not find any information that would help me track down the database that is using this log file.

I did find a nifty program along the way called "Transaction Log Shrink Wizard". Although this program cannot help me because I cannot locate the database using the 25GB log file. It did help me look through the databases a bit faster. It will connect to your server, then allows you to select your databases. When you select a database, it simply shows you the log file name, size, autogrow properties, log max size, and log location.
http://www.codeproject.com/KB/database/ShrinkingSQLServerTransLo.aspx

Preethi, if you can advise me on how to correct the syntax I might be able to make some progress. Thanks for sticking with me guys.

Sorry, I am unable to recreate this error. Did you copy and paste my code?
Since you tried with other tools and it has not produced any positive results, I am pessimistic about my code too.

I am basically searching on the server on each database for that file.

From the command prompt can you get the attributes of the file:
attrib "C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\Data\SharePoint_Config_60b51b5e-69f3-4c04-a11d-ee11b4205cfe_log.LDF"


You can also right click on windows and check the properties.
You may also need to check the permissions on this file (under security tab)

Thanks again Preethi,

Yes, I did copy and paste the code. Yes, I did understand the logic of the statement and I knew it would be useful. But I still get the error.

I tried to get the attributes through the command prompt. Strangely, the command prompt says that the file is not found. I can however, get the attributes through windows. It is NOT set for Read-only, nor is it Hidden.

Under Security, three groups have permissions on this file. The Administrators, NETWORK SERVICE, and a group with a really long name. SQLServer2005MSSQLUser$PINNACLE-SRV$MICROSOFT\#\#SSEE (MYSERVER\SQLServer2005MSSQLUser$MYSERVER$MICROSOFT##SSEE)

Just to try to poke around, I connected to PINNACLE-SRV through MSQL Server Management Studeio, but all the databases in there are the same. None have a reference to this log file.

What can looking at the permissions tell you?

Ok, after Googling I found some valuable information. I found out that this ##SSEE is an embedded SQL 2005 data service that can only be run by some Windows services. So I tried to connect to it, but I got an error. So I turned to Google and found this exerpt from experts-exchange.com (Have to scroll to the bottom to see it)
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23235559.html

In addition to this I had to enable the Named Pipes and TCP/IP protocols. Then I just used the pipe to connect to the database using windows authentication. Pipe = \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

Now that I am connected I have found the database that is using this log file. It was hidden before so I was having trouble locating it.

Now I just have to find some way to dig 25GB out of 5GB on my secondary drive to back this log up and shrink it. I'll post an update!

Hey that's a great news. You have made some remarkable progress.
Thank you for posting issue, the reason and the link related to this.
I learnt something new. Please do keep posting on the progress.

I found this interesting thread. Hope it helps.
http://social.msdn.microsoft.com/fo.../thread/2d8dfefb-6928-4a18-8b30-420bd6fde9f9/

Thanks for posting the resource Preethi,

Thank you for your encouragement. So on to backup and shrinking this massive log. Within the properties of the database I changed the autogrowth to only 500MB , and took its size down to 1MB.

Then I went into the backup properties of the database and setup a location and file name for the backup ON ANOTHER DRIVE.

Pretty standard proceedure, however; for future people searching this thread, these databases that were automatically created have hyphens. So you'll have to enclose the the database and log names in brackets as shown below or you'll get syntax errors.

Code:

use [SharePoint_Config_60b51b5e-69f3-4c04-a11d-ee11b4205cfe]

backup log [SharePoint_Config_60b51b5e-69f3-4c04-a11d-ee11b4205cfe]
with truncate_only

dbcc shrinkfile ([SharePoint_Config_60b51b5e-69f3-4c04-a11d-ee11b4205cfe_log], 1)

I used this query because I was unable to shrink the log without backing up all 25GB through the 'Back Up' menu under the database in Server Management Studio.

Now, I never want to run into this again! So I need to figure out how to automate this backup process so this transaction log file never gets out of hand. I'll post a solution to wrap this thread up.

After Googling for a bit and reading some information on scheduleing backup operations within SQL Managment Studio 2005, I've got a question. I thought about posting this in another thread, but I think someone reading this thread would want to know the complete solution of keeping themselves from getting into the situation of having an overgrown log file again.

So anywayz I read how to create the automatic backups here.
http://support.microsoft.com/kb/930615

But I know that the database backup will not keep a log file from growing. A log backup contains all transactions generated since the previous log backup, regardless of what full or differential backups are taken. So this either means your log or your backup is going to keep growing right? So how I see it, on step 1 in the new backup job, after the BACKUP DATABASE command, I can insert the same backup log command on the previous post.

Code:

backup log [SharePoint_Config_60b51b5e-69f3-4c04-a11d-ee11b4205cfe]
with truncate_only

dbcc shrinkfile ([SharePoint_Config_60b51b5e-69f3-4c04-a11d-ee11b4205cfe_log], 1)

But what I am unsure of, is if this log is going to contain enough information to restore the database if something really bad happens? Could someone verify if this is a good solution to keeping the log file at bay?

As this database is used by WSUS, you need little control over the database. (your only worry was to keep the log file to its minimum size) I am wondering whether you need to keep the database in full/bulklog recovery model. If you keep it in simple recovery model, you can control the log file easily.
The benefit of full recovery model is you can restore at a point in time. Do you need that?
If you feel that, you need to keep the log files, I prefer you to take a log backup (to a device) and then issue a truncate statement.
Hope this helps.

We don't use Share Point anymore. I've actually migrated Share Point to Microsoft Business Online Productivity Suite. But my superiors do not want to delete it from the server.

I do not need the benefits of restoration at any point in time. I think for this database a simple recovery model would be great. Let me ready up on how to put this database in the simple recovery model and the implications. If you have a good resource could you please post it.

Thanks

I haven't tried this on Embedded edition, but this should work

Code:

ALTER DATABASE [SharePoint_Config_60b51b5e-69f3-4c04-a11d-ee11b4205cfe] SET RECOVERY SIMPLE