Truncate SharePoint Log Files for All the database in SQL Server


Truncate Log Files for All the database in SQL Server

1. Perform full backup of all your database
2. Open your SQL Management Studio

3. Click on New Query
4. Insert the code and click Execute

clip_image002


SQL 2008

use master
DECLARE @Statement varchar (4000)
SELECT @Statement = ”
SELECT @Statement = @Statement + ‘USE [?];’
SELECT @Statement = @Statement + ‘DECLARE @Log_Logical_FileName varchar (100);’
SELECT @Statement = @Statement + ‘SELECT @Log_Logical_FileName = rtrim(name) FROM dbo.sysfiles WHERE fileid = 2; ‘
SELECT @Statement = @Statement + ‘ALTER DATABASE [?] SET RECOVERY SIMPLE WITH NO_WAIT;’
SELECT @Statement = @Statement + ‘DBCC SHRINKFILE(@Log_Logical_FileName,1);’
SELECT @Statement = @Statement + ‘ALTER DATABASE [?] SET RECOVERY FULL WITH NO_WAIT;’
EXEC sp_MSforeachdb @command1=@Statement
GO

SQL 2005

use master
DECLARE @Statement varchar (4000)
SELECT @Statement = ”
SELECT @Statement = @Statement + ‘USE [?]; ‘
SELECT @Statement = @Statement + ‘DECLARE @Log_Logical_FileName varchar (100); ‘
SELECT @Statement = @Statement + ‘SELECT @Log_Logical_FileName = rtrim(name) FROM dbo.sysfiles WHERE fileid = 2; ‘
SELECT @Statement = @Statement + ‘BACKUP LOG [?] WITH TRUNCATE_ONLY; ‘
SELECT @Statement = @Statement + ‘DBCC SHRINKFILE(@Log_Logical_FileName,1); ‘
EXEC sp_MSforeachdb @command1=@Statement
GO

Running this will truncate all your log file

Uninstall named instance SQL 2008


How do I remove a named instance of sql server 2008 on the same mssql server?
If you have two instance in SQL Server 2008 and what are are the steps to remove one instance from SQL Server?

To remove a named instance of sql server 2008, follow these steps

1. Navigate to Control Panel->Add/Remove Programs

2. Select Microsoft SQL Server 2008

3. Select Change/Remove

4. Select Remove

5. At Select Instance spefify the instance of SQl Server to modify

Select the instance of SQl Server to remove. To remove management tools and shared features only, select “Remove shared features only” and then click next

uninstall named instance sql 2008

uninstall named instance sql 2008

Removal progress will go through and remove named instance of sql server 2008 on the same mssql server

How to find SQL Server configuration files


How to find SQL Server configuration settings in SQL Server?

Configuration files can be found at :
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20140321_112944\ConfigurationFile.ini

SQLConfigurationPath

Microsoft Kerberos Configuration Manager for SQL Server


Source:http://blogs.msdn.com/b/farukcelik/archive/2013/05/21/new-tool-quot-microsoft-kerberos-configuration-manager-for-sql-server-quot-is-ready-to-resolve-your-kerberos-connectivity-issues.aspx

You can download “Microsoft Kerberos Configuration Manager for SQL Server” from here

Microsoft Kerberos Configuration Manager for SQL Server is a diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server.

Kerberos authentication provides a highly secure method to authenticate client and server entities (security principals) on a network.  To use Kerberos authentication with SQL Server, a Service Principal Name (SPN) must be registered with Active Directory, which plays the role of the Key Distribution Center in a Windows domain.  In addition, many customers also enable delegation for multi tier applications using SQL Server.  In such a setup, it may be difficult to troubleshoot the connectivity problems with SQL Server when Kerberos authentication fails.

The Kerberos Configuration Manager for SQL Server is a diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server.   It can perform the following functions:

  • Gather information on OS and Microsoft SQL Server instances installed on a server.
  • Report on all SPN and delegation configurations on the server.
  • Identify potential problems in SPNs and delegations.
  • Fix potential SPN problems.
Supported Operating System

Windows 7, Windows 8, Windows Server 2008 R2 SP1, Windows Server 2012

The following are required on the machine where the Kerberos Configuration Manager for SQL Server is launched:

    • .Net framework 4.0 or higher

To Install:

  1. Download the 32bit or 64bit version of the Kerberos Configuration Manager installer that matches your computer’s OS architecture.
  2. Click Open to start the installation immediately or click Save to save the installation .msi file to disk and install it later.
  3. Accept the license term of this tool.
  4. Click Next to complete the installation.

To Launch the Tool:

  1. After the installation is complete successfully, double click the KerberosConfigMgr.exe  to launch the application.

To Generate SPN List from Command Line:

  1. Go to command line.
  2. Switch to the folder where KerberosConfigMgr.exe is.
  3. Type KerberosConfigMgr.exe -q -l
  4. For more command line option, type KerberosConfigMgr.exe -h

To Save a Server’s Kerberos Configuration Information:

  1. Connect to the target windows server.
  2. Click on Save button on the toolbar
  3. Specify the location where you want the file to be saved at.  It can be on a local drive or network share.
  4. The file will be saved as .XML format.

To View a Server’s Kerberos Configuration Information from Saved File:

  1. Click on the Load button on the toolbar.
  2. Open the XML file generated by Kerberos Configuration Manager.

To Generate a Script to Fix SPN from Command Line:

  1. Click on the Generate button for the SPN entry.
  2. The generated script can be used by a user who has privilege to fix the SPN on the server.

To See the Log Files for this Tool:

  1. By default, one log file is generated in the user’s application data folder.

To Get Help:
Option 1: Hover the mouse cursor over the command for tooltip.
Option 2:  Run KerberosConfigMgr.exe –h from command line
Option 3: Click the Help button in the toolbar.

Install and Configure RBS (Remote Blob Storage) for SharePoint


Where I can download Remote Blob Storage installation files for SharePoint 2010?
You can download rbs_x64.msi file from : http://go.microsoft.com/fwlink/p/?LinkID=165839&clcid=0x409

How do I install and configure RBS for SharePoint?

1. Download Remote Blob Storage from above link
2. Before you install Remote Blob storage, make sure SQL server 2008 enabled FILESTREAM. To enable FILESTREAM

Start | All Program | Microsoft SQL Server 2008 R2 | Configuration Tools | Configuration Manager
Click on SQL Server Services | SQL Server | Properties

Enable FILESTREAM for BLOB Storage

3.  Enable following services

Enable File stream for Transact-SQL Access
Enable File stream for file I/O streaming access
Allow remote clients to have streaming access to Filestream data

Enable Filestream for SharePoint BLOB storage

4. Open SQL Management Studio and performing following task

use WSS_Content If not exists
(select * from sys.symmetric_keys where name = N‘##MS_DatabaseMasterKey##’)
create master key encryption by password = N‘Pa$$word’


image

5. Next we need to enable FileStreamProvider

Filestream feature is disabled, how to enable

use WSS_Content if not exists

(select groupname from sysfilegroups where groupname=N‘RBSFilestreamProvider’)

alter database WSS_Content add filegroup RBSFilestreamProvider contains filestream

If you get error message saying “ FILESTREAM Feature is Disabled” run the following query

FILESTREAM Feature is Disabled

Configuration option 'filestream access level'

6.  Next run the following query.  This will create a folder in E called Blobdata. This can be different in your case

use WSS_content alter database WSS_Content add file

(name = RBSFilestreamFile, filename = ‘E:\blobdata’)

to filegroup RBSFilestreamProvider


Create RBS File Stream File Folder

8. Now let’s go and install RBS. Best practice would be to install using command prompt  but it won’t give you any steps about what’s going on.
I had problem using Command and I installed using the GUI.  If you want to install using command here are the steps

(open command prompt as administrator).
I placed RBS installation file at C:\RBS folder Navigate to RBS folder using your Command Prompt

image

9.  Enter the following code

msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi TRUSTSERVERCERTIFICATE=true
FILEGROUP=PRIMARY DBNAME=”WSS_Content” DBINSTANCE=”YourServer”
FILESTREAMFILEGROUP=RBSFilestreamProviderFILESTREAMSTORENAME=FilestreamProvider_1

Installing RBS Using Command Prompt

10. It won’t give you any indication about the installation. To see if its successfully installed Blob storage, just go to the folder and look for the log file

11. Installing RBS using GUI.  Double click on RB_X64.msi

12.  Welcome to the installation wizard for Remote Blob Storage wizard pops up. Setup helps you install, modify or remote remote Blob storage.

To continue, click Next

installation wizard for Remote Blob Storage

12. Install SQL Remote Blob storage : Licence Agreement

Install SQL Remote Blob storage : Licence Agreement

13. Install SQL Remote Blob Storage: Registration Information, the following information will personalize your blob installation

SNAGHTMLe57dbb

14.  Install SQL Remote Blob Storage : Feature Selection

Select the program features you would like to install and select the installation path where you want to install the program

Install SQL Remote Blob Storage : Feature Selection

15. Blob Storage Database Connection. You can test your connection against the database
which your setting blob storage by hitting test connection.

Blob Storage Database Connection

16. SQL Remote Blob Storage: Database Configuration

Enter information to configure RBS database settings

Blob Storage Database Configuration

17. Filestream Blob Store: Enter Filestream blob store configuration Information

image

18. Maintainer Task. Enter information to optionally schedule the maintainer task

During installation, the windows task scheduler window will apper. Use this to set scheduled task properties.

image
19. Remote Blob Storage Client Configuration

Remote Blob Storage Client Configuration

20.  Install Remote Blob Storage.  The program features you selected are being installed.

Please wait while the installation wizard installs remote blob storage. This may take several minutes.

image

21.To ensure that the installation was successful, open up the Content DB | tables

Ensure the blob installation in SharePoint

22. Now open your SharePoint 2010 Management Shell and enter the following scripts
$cdb = Get-SPContentDatabase –WebApplication http://vsp2010be
$rbss = $cdb.RemoteBlobStorageSettings
$rbss.Installed() – This should result in True. Else the next command will fail.
$rbss.Enable()
$rbss.SetActiveProviderName($rbss.GetProviderNames()[0])
$rbss

 

image

23. Now set the minimum threshold so that only files larger than a 1 MB will be placed in our blobdata fodler

$rbss.MinimumBlobStorageSize=1048576

image

24. Go head and upload a document which is larger than 1 MB.  This document should end up in the blobdata folder

I uploaded 8 GB of PDF file and it end up in the blobdata folder

Files moved to Blobdata Folder