Archives For SQL Server 2008


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


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