In order to delete the many Content Deployment Timer Job records that are created during the automated content deployment process follow the steps below:

1. Run the following SQL statement on your SharePoint Config Database to retrieve the unique IDs (GUIDs) of all the Content Deployment Timer Jobs:

    select * from Sharepoint_Config.dbo.[Objects] REM where [Name] like 'ContentDeploymentJobDefinition%'

2. Then, use those unique IDs (GUIDs) to build a batch file to run the following STSADM command:

   STSADM -o deleteconfigurationobject -id "<object id>"

Greg Robidoux has a very helpful post “SQL Server Find and Replace Values in All Tables and All Text Columns”, where he presents the following SQL:

SET NOCOUNT ON
DECLARE @stringToFind VARCHAR(100)
DECLARE @stringToReplace VARCHAR(100)
DECLARE @schema sysname
DECLARE @table sysname
DECLARE @count INT
DECLARE @sqlCommand VARCHAR(8000)
DECLARE @where VARCHAR(8000)
DECLARE @columnName sysname
DECLARE @object_id INT

SET @stringToFind = ‘Smith’
SET @stringToReplace = ‘Jones’

DECLARE TAB_CURSOR

CURSOR FOR SELECT B.NAME AS SCHEMANAME, A.NAME AS TABLENAME, A.OBJECT_ID
FROM sys.objects A
INNER JOIN sys.schemas B ON A.SCHEMA_ID = B.SCHEMA_ID
WHERE TYPE = ‘U’
ORDER BY 1

OPEN TAB_CURSOR FETCH NEXT
FROM TAB_CURSOR INTO @schema, @table, @object_id WHILE @@FETCH_STATUS = 0

BEGIN
DECLARE COL_CURSOR CURSOR FOR
SELECT A.NAME FROM sys.columns A
INNER JOIN sys.types B ON A.SYSTEM_TYPE_ID = B.SYSTEM_TYPE_ID
WHERE OBJECT_ID = @object_id AND IS_COMPUTED = 0
AND B.NAME IN (‘char’,’nchar’,’nvarchar’,’varchar’,’text’,’ntext’)

OPEN COL_CURSOR FETCH NEXT
FROM COL_CURSOR INTO @columnName WHILE @@FETCH_STATUS = 0

BEGIN
SET @sqlCommand = ‘UPDATE ‘ + @schema + ‘.’ + @table + ‘ SET [' + @columnName + '] =
REPLACE(convert(nvarchar(max),[' + @columnName + ']),”’ + @stringToFind + ”’,”’ +
@stringToReplace + ”’)’ SET @where = ‘ WHERE [' + @columnName + '] LIKE ”%’ +
@stringToFind + ‘%”’

EXEC( @sqlCommand + @where)

SET @count = @@ROWCOUNT IF @count > 0

BEGIN
PRINT @sqlCommand + @where
PRINT ‘Updated: ‘ + CONVERT(VARCHAR(10),@count)
PRINT ‘—————————————————-’

END

FETCH NEXT FROM COL_CURSOR INTO @columnName END

CLOSE COL_CURSOR DEALLOCATE COL_CURSOR

FETCH NEXT FROM TAB_CURSOR INTO @schema, @table, @object_id END

CLOSE TAB_CURSOR DEALLOCATE TAB_CURSOR

Use the following SQL to find the sites that have missing assemblies that are found in the Review Problems and Solutions Central Admin page:

SELECT w.FullURL, w.Author, w.Title,w.[Description]
  FROM [dbo].[EventReceivers] with (nolock)
  inner join dbo.Webs w with (nolock) on WebId = w.Id
  where [Assembly] like '%<MISSING ASSEMBLY NAME>%'

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  

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

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= '<br />
    if ''?'' not in (''tempdb'',''master'',''model'',''msdb'')<br />
        begin<br />
            use [?]<br />
            declare @tsql nvarchar(4000) set @tsql = ''''<br />
            declare @recmodel nvarchar(10)<br />
            declare @iLogFile int<br />
            declare LogFiles cursor for<br />
            select fileid from sysfiles where  status &amp; 0x40 = 0x40<br />
            open LogFiles<br />
            fetch next from LogFiles into @iLogFile<br />
            while @@fetch_status = 0<br />
                begin<br />
                    select @recmodel = (SELECT recovery_model_desc FROM sys.databases WHERE name = ''?'' )<br />
                    if @recmodel = ''FULL''<br />
                        begin<br />
                            set @tsql = @tsql + ''use [?] ''<br />
                            set @tsql = @tsql + ''ALTER DATABASE [?] SET RECOVERY SIMPLE ''<br />
                            set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''<br />
                            set @tsql = @tsql + ''ALTER DATABASE [?] SET RECOVERY FULL ''<br />
                        end<br />
                    else<br />
                        set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''<br />
                    fetch next from LogFiles into @iLogFile<br />
            end<br />
            --set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql<br />
            --print @tsql<br />
            exec(@tsql)<br />
            close LogFiles<br />
            DEALLOCATE LogFiles<br />
        end'<br />
exec sp_msforeachdb @ssql<br />

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= '<br />
        if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin<br />
			use [?]<br />
			declare @tsql nvarchar(4000) set @tsql = ''''<br />
			declare @iLogFile int<br />
			declare LogFiles cursor for<br />
			select fileid from sysfiles where  status &amp; 0x40 = 0x40<br />
			open LogFiles<br />
			fetch next from LogFiles into @iLogFile<br />
			while @@fetch_status = 0<br />
			begin<br />
				set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''<br />
				fetch next from LogFiles into @iLogFile<br />
			end<br />
			set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql<br />
			--print @tsql<br />
			exec(@tsql)<br />
			close LogFiles<br />
			DEALLOCATE LogFiles<br />
        end'<br />
 exec sp_msforeachdb @ssql<br />
 

via CodeSnippets

I was trying to export a site that had been moved from a MOSS farm to a WSS farm and was getting an error that a list did not exist.  Well, I’ve fought with this particular web application for quite a while and was sick of getting stupid little errors because of the move from MOSS to WSS.  So, I was able to find a SQL Stored Procedure that I could use to delete all references to the “orphaned” list.

I found this Stored Procedure on Vyas Kondreddi’s website.

CREATE PROC SearchAllTables
(
	@SearchStr nvarchar(100)
)
AS
BEGIN

	-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
	-- Purpose: To search all columns of all tables for a given search string
	-- Written by: Narayana Vyas Kondreddi
	-- Site: http://vyaskn.tripod.com
	-- Tested on: SQL Server 7.0 and SQL Server 2000
	-- Date modified: 28th July 2002 22:50 GMT

	CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

	SET NOCOUNT ON

	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
	SET  @TableName = ''
	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

	WHILE @TableName IS NOT NULL
	BEGIN
		SET @ColumnName = ''
		SET @TableName =
		(
			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			FROM 	INFORMATION_SCHEMA.TABLES
			WHERE 		TABLE_TYPE = 'BASE TABLE'
				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				AND	OBJECTPROPERTY(
						OBJECT_ID(
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							 ), 'IsMSShipped'
						       ) = 0
		)

		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		BEGIN
			SET @ColumnName =
			(
				SELECT MIN(QUOTENAME(COLUMN_NAME))
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
					AND	QUOTENAME(COLUMN_NAME) > @ColumnName
			)

			IF @ColumnName IS NOT NULL
			BEGIN
				INSERT INTO #Results
				EXEC
				(
					'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
					FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				)
			END
		END
	END

	SELECT ColumnName, ColumnValue FROM #Results
END

Here is the t-sql statement used to shrink the SQL transaction logs:

Use
Go
Backup Log  With Truncate_Only
DBCC SHRINKFILE(, 1)
Go

I came across this script to backup all SQL Server databases:

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'E:Backups'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
       BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor