Tag Archive: microsoft##ssee


Running the SBS 2008 BPA, you receive a warning item that states:

The log file for the Windows SharePoint Services configuration database is larger than 1 GB in size. For information about how to reduce the size of the log file, see the Knowledge Base at http://go.microsoft.com/fwlink/?LinkId=159745.

SQL Config Log Warning in BPA

SQL Config Log Warning in BPA

If you follow the link, Microsoft explains how to make a SQL script that will trim your database. My problem was that even after trimming the database, it was still over 1Gb in size- 1.4Gb actually. Let’s solve this.

This is not a permanent solution, but rather a solution to temporarily shrink the log files. They will eventually build back up- I plan to execute this plan when ever I get the warning.

Also note that doing this process will make the database temporarily “Full Recovery” only. which means that if you use differential backup and recovery, after this process takes place you can only do a full recovery untill the log files build back up over transactions.

Perform a full server backup.

Now check the size of the database.

Log into your Microsoft##SSEE Database (Windows Internal Database).

By entering \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query as your server name. Select Windows Authentication. Click Connect.

Connect to Microsoft##SSEE

Connect to Microsoft##SSEE

You will see several databases- we will be working with the one titled Sharepoint_Config_#########.

Databases

Databases

Right click on that database and select Properties. You will see two numbers for Size and Space Available. Look at the size. Mine was 1449 Mb. I ran the Microsoft suggested SQL script, and it was still the same size. So here is what to do.

Under the options tab of the database properties, change the Recovery Model dropdown from Full to Simple. This tells SQL to truncate the log files, meaning only a full recovery is available.

Recovery Model: Simple

Recovery Model: Simple

Click Ok.

Now Run the logshrink.sql to trim the database. You can do this through a command prompt by running command:

sqlcmd -S \\.\pipe\mssql$microsoft##ssee\sql\query -E -i
 
-or-
 
You can double-click logshrink.sql. It will ask you to log in, use the same information as above. Above the window that opens, click Execute.
Execute Script

Execute Script

 

Now go back into the Properties of the config database, click Options, and change Recovery Model back to Full.

Trimmed Database

Trimmed Database

You should see your file size WAY smaller now, happy shrinking.

So running the SBS 2008 BPA, I received a low disk space error, less than 15%. Now I get these errors often, and I have moved everything I could off of the drive, including Sharepoint Content, Redirected Folders, Exchange Databases, Installed Programs such as Microsoft Office.

BPA

BPA

Man, my space IS low, 9 Gb out of 60 Gb free. Let me clean out what I can. I empty all the temp folders, delete some empty folders, some setup logs… Wow, I actually recovered 10 Mb of space. Great.

Then I thought about Windows Server Update Services- I know they keep content somewhere (If you have them set to download and store content, which I do). Drats, that is on D:\ as well. (If you need to move WSUS CONTENT, this is not your post).

What about the database files themselves? I go to C:\WSUS\SUSDB\UpdateServicesDbFiles\ and there they are. Two files:

SUSDB.mdf

SUSDB_log.ldf

*** Stop Update Services Service in Services.mmc ***

Having worked with databases before, I know it is not as simple as dragging and dropping the files. So lets work in SQL.

Goto Start>All Programs>Microsoft SQL Server 2005> and select SQL Server Manager Express.

The program opens, and gives you the Object Explorer. You need to connect to a database instance to work with the database. WSUS uses Windows Internal Database, so let’s connect to that one. You can’t log in with sa, or with Windows Auth even if you are an admin- so enter this in the server name:

\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

Leave it on Windows Auth, and hit connect. Expand Databases, and your looking at quite a few of them, mostly related to Sharepoint. The one we want is SUSDB.

First, and always, BACK IT UP. This can be done by right clicking the SUSDB and selecting Tasks>Back Up. Now you will have to choose your location, medium type, and file name yourself. For me, I picked a removable HDD (A:/) and named the backup…

Backup

Backup

SUSDB.bak

Let that execute, it might take some time (mine took roughly 7 minutes). Once you get the success message, it’s time to move this DB. Since databases have active connections, and moving the files with these connections can break the the entire internet, lets DETACH the database before moving it.

On a side note this can all be done via command line and sqlcmd. I am not comfortable with the language so I just use the GUI.

Go back to SQLMSE and right click the SUSDB again. This time click Tasks>Detach.

Detach

Detach

You get a screen with one line and some check boxes. You can change some of the boxes depending upon your needs, but for this one, we will select Drop Connections and Keep Full Text Catalog, which is selected by default. As you can see the DB has current connections (such as SUS clients).

Drop Connections

Drop Connections

This will detach the DB. Go back to Windows Explorer, and navigate to the C: drive. Grab the entire WSUS folder, and move it to your target drive. I moved mine to D:\WSUS, which is where my catalog is as well. Might want to give it it’s own directory to be safe.

Now we need to re-attach the database. Go back to SQLSME and right-click on instance, in this case, \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query (SQL Server 9.0.4035 – Domain\UserName).

Click Tasks>Attach.

Attach

Attach

Click Add, and navigate to your new database location.

Choose Location

Choose Location

Click ok. Let it work, it will gray out and take a bit.

Attaching

Attaching

Once it is complete, double-check the database file location by right-clicking SUSDB and selecting properties. Select Files on the left, and look at the Path. You should see your new database path listed there.

Path

Path

Your done. Close everything out, and double-check both the SBS Console and the WSUS Console to make sure everything is synchronized and working. That just recovered almost 4 Gb of space on my otherwise picked-clean C: drive.

*** Start Update Services Service in Services.mmc ***

Now this may be very simple for some, others might not know what to do about database issues- so Ill explain in a quick post.

I logged in this morning and noticed my RAM usage was very high, (91% on the resource monitor, compared to normal 71%). I hit Crtl-Shift-Esc and went to processess and my top two memory usage processes were SQL Server. The top one was using 1,540,736 K, with the second around 600 K. That is a lot.

Right click on the top one, and select Go To Service. This one is MICROSOFT##SSEE and the second is SBSMONITORING. I know from past experience that SBSMONITORING can get out of control, but in my opinion 600,000 K is not bad. There is a good post on running a script that will clean up and compact the SBSMONITORING database here (Smallbizserver.net- one of my favorite sites, but you will have to find the post yourself).

In this instance, I do not really care about SBSMONITORING. But the MICROSOFT##SSEE is really high, and I have never seen that before.

Now I am no DBA, but I DO know that limiting a database will affect performance. I also know that if the database was using that much memory, it probably had reason. It could be a memory leak, but I do not think so in this instance, because it is just running all of the default services. Do a Google search on SQL Server memory leaks for more information.

So in this case, I do not want to limit the database. Ill restart it and see what happens. Start>Administrative Tools>Services. Right click and restart Windows Internal Database. Voila! it is now hovering around 158,000 K. Thats a lot better than 10x that. And by only restarting it, I did not limit the database should it NEED much more ram, perhaps when it is synchronizing WSUS or something.

SQL Process

SQL Process

Might as well restart SBSMONITORING as well- yep, that knocked the RAM usage of that one down a few notches, though not as dramatically as the first.

So, I want to do this regularly, but I do not want to remember to restart these manually. They get restarted when the server reboots, but I TRY to minimize those as well. We can write it into a VERY simple batch script.

Open up Notepad. Enter the following:

net stop mssql$sbsmonitoring
net start mssql$sbsmonitoring
net stop mssql$microsoft##ssee
net start mssql$microsoft##ssee

Save the file as a text file on the root of some drive, or if you have a folder for scripts. I keep mine in D:\Scripts\.

I go to the new text file location, and change the .txt to .bat

Now we have a file that when it is run, stops both SQL database services and starts them one at a time. This will not cause system damage, nor damage the databases- as limiting the RAM might have. Let’s give the script a test run to make sure it works:

Restart Script

Restart Script

Navigate to the file location from a command prompt, and run the batch file. If the results look like this, then you are good. Now we need to automate this script. So, we will use scheduled tasks to enable this script to run once a week. Could be twice a week, but I think running this Monday morning will be nice.

Start>Administrative Tools>Task Scheduler.

Right click Task Scheduler Library, and click New Task.

Give the task a name and description. In my case I named is Restart SQL.

Select the radio button to run the task if no one is logged on.

General Tab

General Tab

 

You have the option to run this on any account you wish. If you have an account you use for DBA (or even a power user account), then select this account.

On the Triggers tab, we will select on a schedule. I set it to occur Weekly every Monday at 5AM. I know I have no other processes running at that time such as backups. I also put a random delay of 30 minutes on the task- this is not necessary in most cases.

Trigger Tab

Trigger Tab

 

On the Action tab, we will select New. Leave it on run a program. In the box under settings for Program/Script, we will select the script that we made, restart_sql.bat.

Action Tab

Action Tab

 

On the Conditions tab, pretty much leave stuff alone. Run if computer is idle for 10 minutes, wait for idle 1 hour. Uncheck stop if the computer ceases to be idle. Uncheck wake the computer to run this task- why would a server be in sleep mode?

Conditions Tab

Conditions Tab

 

On the settings tab, pretty much leave everything alone. It’s all self explanatory if need to change something to suit your needs, then do so.

Settings Tab

Settings Tab

 

Now, let the task run it’s course. You can check the task scheduler after Monday to see if the task ran- which it will.

This will keep my database memory usage down without me having to worry about it or by limiting the natural functions of said databases.

%d bloggers like this: