SharePoint Geek

How To Find The Size Of The Data Files In An SQL Server 2008 Database Instance

Stewart Schatz — Tue, 24 Aug 2010 14:31:23 +0000

Here is an SQL that you can run to find the total used size of an SQL Server 2008 database instance:

CREATE TABLE #t (name SYSNAME, rows CHAR(11), reserved VARCHAR(18),    data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))   exec sp_MSforeachdb '      use [+];       if (select DB_NAME())<> ''tempdb''          begin             EXEC sp_msforeachtable ''INSERT INTO #t EXEC sp_spaceused [?]''          end       ', '+'    SELECT SUM(CONVERT(INT, SUBSTRING(data, 1, LEN(data)-3))) as sizeKB FROM #t     DROP TABLE #t

SQL Server Versions List

Stewart Schatz — Mon, 19 Jul 2010 18:25:41 +0000

Bill Graziano at SQLTeam.com has a listing of all the SQL Server versions that come in very handy:

http://www.sqlteam.com/article/sql-server-versions

Tags: sql, sql server, sql server 2008

SharePoint 2010 Administration Toolkit Released

Stewart Schatz — Thu, 15 Jul 2010 22:30:49 +0000

The SharePoint 2010 Administration Toolkit has been released.

More info: Microsoft SharePoint Team Blog
Download: Microsoft
Documentation

Tags: Administration Toolkit, SharePoint 2010, SharePoint Foundation 2010, SharePoint Server 2010

How To Delete An SSP (Shared Services Provider) In Sharepoint

Stewart Schatz — Thu, 15 Jul 2010 18:16:07 +0000

Sharepoint Shared Services Providers (SSPs) can be forced to delete using the following stsadm command:

stsadm -o deletessp -title "SSPNAME" -force

If you also want to delete the SSP database also use the following stsadm command:

stsadm -o deletessp -title "SSPNAME" -deletedatabases –force

Tags: Shared Services Provider - SSP, stsadm

Sharepoint WSS Search Server Stuck On “Pausing”

Stewart Schatz — Wed, 07 Jul 2010 12:14:04 +0000

I ran across an issue this morning that had the Sharepoint WSS Search Server in a “Pausing” state. I had to restart the Office SharePoint Server Search service in order to resolve the issue.

Now to see if it will complete successfully.

Tags: search, search services, Windows Server, Windows Service, wss

Host Header On SSL Sites

Stewart Schatz — Thu, 24 Jun 2010 17:18:00 +0000

This is an update to our previous post (Enable Host Headers On An SSL/HTTPS Site). Which used the following command to enable host headers on SSL sites:

cscript.exe adsutil.vbs set /w3svc//SecureBindings ":443:"

I ran into some issues with using SecureBindings with the Sharepoint Central Administration sites so I thought I would post some of the other commands that I used:

To find out what bindings are on a site use the following command:

cscript.exe adsutil.vbs get w3svc//securebindings

To remove the host header from an SSL site use the following command:

cscript.exe adsutil.vbs set /w3svc//SecureBindings ":443:"

Tags: IIS, ssl

Shrink All Log Files Within A MS SQL 2008 Instance

Stewart Schatz — Fri, 18 Jun 2010 17:57:49 +0000

This is an update to a previous post (Shrink All Log Files Within A MS SQL Instance) that has been edited to work in SQL Server 2008.


<br />
declare @ssql nvarchar(4000)<br />
SET @ssql= '

    if ''?'' not in (''tempdb'',''master'',''model'',''msdb'')

        begin

            use [?]

            declare @tsql nvarchar(4000) set @tsql = ''''

            declare @recmodel nvarchar(10)

            declare @iLogFile int

            declare LogFiles cursor for

            select fileid from sysfiles where  status & 0x40 = 0x40

            open LogFiles

            fetch next from LogFiles into @iLogFile

            while @@fetch_status = 0

                begin

                    select @recmodel = (SELECT recovery_model_desc FROM sys.databases WHERE name = ''?'' )

                    if @recmodel = ''FULL''

                        begin

                            set @tsql = @tsql + ''use [?] ''

                            set @tsql = @tsql + ''ALTER DATABASE [?] SET RECOVERY SIMPLE ''

                            set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''

                            set @tsql = @tsql + ''ALTER DATABASE [?] SET RECOVERY FULL ''

                        end

                    else

                        set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''

                    fetch next from LogFiles into @iLogFile

            end

            --set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql

            --print @tsql

            exec(@tsql)

            close LogFiles

            DEALLOCATE LogFiles

        end'<br />
exec sp_msforeachdb @ssql<br />

Tags: sql, sql server, sql server 2008, transaction logs

Delete An Extended Web Application

Stewart Schatz — Thu, 27 May 2010 17:07:00 +0000

Extended web applications are not listed in the web application list and cannot be deleted using the Delete Web Application link in Central Administration.

Use the following steps to delete extended web applications:

Start Central Administration
Go to Application Management
Click Remove SharePoint from IIS Web Site to get a page with a form similar to the following.
Select the web application that the extended web app was extended from
Select which extention/zone to delete
Select whether to keep or delete the IIS Web site
Finally click OK

Tags: Central Administration, extended web application, IIS, MOSS, web application, Windows SharePoint Services Web Application, wss

IIS Admin Error: Handle Is Invalid – Permissions SSL Certificate MachineKeys

Stewart Schatz — Wed, 05 May 2010 18:32:18 +0000

Well, I’ve been trying to setup the External Collaboration Toolkit. All of the instructions tell how to do it as a standalone SharePoint install, but I am trying to get it setup in a SharePoint farm. One of the steps in the instructions is to setup ADAM to act as the authentication for external users. The communications with ADAM need to be SSL encrypted. So, I set it up the way the instructions say and went through the portion that ensures that the UserID that runs the ADAM service has access to the SSL Certificates.

That’s when I must have went wrong. Somehow, I removed access to the SSL Certificates from the service that runs the IIS Admin service. So, when I rebooted, IIS would not start. The only thing that was in the event log was:

Handle is invalid

Real helpful, huh?

Luckily, I was able to find Microsoft KB Article #278381. The basic thing is that I needed to reset the security on the SSL Certificates. Below is an explaination:

The MachineKeys are at the following location:
All Users Profile\Application Data\Microsoft\Crypto\RSA

The following settings are the default permissions for the MachineKeys folder:

Administrator	(Full Control)	This folder only
Everyone	(Special)	This folder, subfolders, and files
SYSTEM	(Full Control)	This folder, subfolders, and files

To view the special permissions for the Everyone group, right-click the MachineKeys folder, click Advanced on the Security tab, and then click View/Edit. The permissions consist of the following permissions:

List Folder/Read Data
Read Attributes
Read Extended Attributes
Create Files/Write Data
Create Folders/Append Data
Write Attributes
Write Extended Attributes
Read Permissions

Select the Reset Permissions on all Child objects and enable propagation of inheritable permissions check box.

Tags: adam, External Collaboration Tool-Kit, IIS, ssl

Shrink All Log Files Within A MS SQL Instance

Stewart Schatz — Wed, 07 Apr 2010 11:58:09 +0000

This little SQL script is used to to shrink all the log files in a Microsoft SQL server instance. It uses the following function to get the job done: DBCC SHRINKFILE.


declare @ssql nvarchar(4000)<br />
SET @ssql= '

        if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin

                        use [?]

                        declare @tsql nvarchar(4000) set @tsql = ''''

                        declare @iLogFile int

                        declare LogFiles cursor for

                        select fileid from sysfiles where  status & 0x40 = 0x40

                        open LogFiles

                        fetch next from LogFiles into @iLogFile

                        while @@fetch_status = 0

                        begin

                                set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''

                                fetch next from LogFiles into @iLogFile

                        end

                        set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql

                        --print @tsql

                        exec(@tsql)

                        close LogFiles

                        DEALLOCATE LogFiles

        end'<br />
 exec sp_msforeachdb @ssql<br />

via CodeSnippets

Tags: logs, sql, sql server

SharePoint Geek

How To Find The Size Of The Data Files In An SQL Server 2008 Database Instance

Related posts

SQL Server Versions List

Related posts

SharePoint 2010 Administration Toolkit Released

Related posts

How To Delete An SSP (Shared Services Provider) In Sharepoint

Related posts

Sharepoint WSS Search Server Stuck On “Pausing”

Related posts

Host Header On SSL Sites

Related posts

Shrink All Log Files Within A MS SQL 2008 Instance

Related posts

Delete An Extended Web Application

Related posts

IIS Admin Error: Handle Is Invalid – Permissions SSL Certificate MachineKeys

Related posts

Shrink All Log Files Within A MS SQL Instance

Related posts