Please visit my New Blog http://www.sqlserverdba.co.cc/

Steve Jones is Maintaining the build list of SQL 2005. You can verify and validate your version by running @@Version Global variable and compare it with sqlservercentral.com’s build list @ http://www.sqlservercentral.com/articles/Administration/2960/

SQL Server 2005 offers an undocumented system stored procedure sp_readerrorlog which calls the extended stored procedure xp_readerrorlog.
This procedure takes four parameters:

1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…
2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1: String one you want to search for
4. Search string 2: String two you want to search for to further refine the results

If you do not pass any parameters this will return the contents of the current error log.

Read more @ http://www.mssqltips.com/tip.asp?tip=1476

You can execute the SP by executing its name like

EXEC DBO.SPGetForeignKeyInfo
IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N’[dbo].[SPGetForeignKeyInfo]‘)
AND OBJECTPROPERTY(id, N’IsProcedure’) = 1)
DROP PROCEDURE dbo.SPGetForeignKeyInfo
GO
CREATE PROCEDURE DBO.SPGetForeignKeyInfo
AS
/*
Author : Seenivasan
This procedure is used for Generating Foreign Key script.
*/
SET NOCOUNT ON
DECLARE @FKName NVARCHAR(128)
DECLARE @FKColumnName NVARCHAR(128)
DECLARE @PKColumnName NVARCHAR(128)
DECLARE @fTableName NVARCHAR(128)
DECLARE @fUpdateRule INT
DECLARE @fDeleteRule INT
DECLARE @FieldNames NVARCHAR(500)
CREATE TABLE #Temp(
PKTABLE_QUALIFIER NVARCHAR(128),
PKTABLE_OWNER NVARCHAR(128),
PKTABLE_NAME NVARCHAR(128),
PKCOLUMN_NAME NVARCHAR(128),
FKTABLE_QUALIFIER NVARCHAR(128),
FKTABLE_OWNER NVARCHAR(128),
FKTABLE_NAME NVARCHAR(128),
FKCOLUMN_NAME NVARCHAR(128),
KEY_SEQ INT,
UPDATE_RULE INT,
DELETE_RULE INT,
FK_NAME NVARCHAR(128),
PK_NAME NVARCHAR(128),
DEFERRABILITY INT)
DECLARE TTableNames CURSOR FOR
SELECT name
FROM sysobjects
WHERE xtype = ‘U’
OPEN TTableNames
FETCH NEXT
FROM TTableNames
INTO @fTableName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #Temp
EXEC dbo.sp_fkeys @fTableName
FETCH NEXT
FROM TTableNames
INTO @fTableName
END
CLOSE TTableNames
DEALLOCATE TTableNames
SET @FieldNames = ”
SET @fTableName = ”
SELECT DISTINCT FK_NAME AS FKName,FKTABLE_NAME AS FTName,
@FieldNames AS FTFields,PKTABLE_NAME AS STName,
@FieldNames AS STFields,@FieldNames AS FKType
INTO #Temp1
FROM #Temp
ORDER BY FK_NAME,FKTABLE_NAME,PKTABLE_NAME
DECLARE FK_CUSROR CURSOR FOR
SELECT FKName
FROM #Temp1
OPEN FK_CUSROR
FETCH
FROM FK_CUSROR INTO @FKName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE FK_FIELDS_CUSROR CURSOR FOR
SELECT FKCOLUMN_NAME,PKCOLUMN_NAME,UPDATE_RULE,DELETE_RULE
FROM #TEMP
WHERE FK_NAME = @FKName
ORDER BY KEY_SEQ
OPEN FK_FIELDS_CUSROR
FETCH
FROM FK_FIELDS_CUSROR INTO @FKColumnName,@PKColumnName,
@fUpdateRule,@fDeleteRule
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #Temp1 SET FTFields = CASE WHEN LEN(FTFields)
= 0 THEN ‘['+@FKColumnName+']‘
ELSE FTFields
+‘,['+@FKColumnName+']‘ END
WHERE FKName = @FKName
UPDATE #Temp1 SET STFields = CASE WHEN LEN(STFields)
= 0 THEN ‘['+@PKColumnName+']‘
ELSE STFields
+‘,['+@PKColumnName+']‘ END
WHERE FKName = @FKName
FETCH NEXT
FROM FK_FIELDS_CUSROR INTO @FKColumnName,@PKColumnName,
@fUpdateRule,@fDeleteRule
END
UPDATE #Temp1 SET FKType = CASE WHEN @fUpdateRule = 0
THEN FKType + ‘ ON UPDATE CASCADE’
ELSE FKType END
WHERE FKName = @FKName
UPDATE #Temp1 SET FKType = CASE WHEN @fDeleteRule = 0
THEN FKType + ‘ ON DELETE CASCADE’
ELSE FKType END
WHERE FKName = @FKName
CLOSE FK_FIELDS_CUSROR
DEALLOCATE FK_FIELDS_CUSROR
FETCH next
FROM FK_CUSROR INTO @FKName
END
CLOSE FK_CUSROR
DEALLOCATE FK_CUSROR
SELECT ‘ALTER TABLE [dbo].['+FTName+'] ADD
CONSTRAINT ['+FKName+'] FOREIGN KEY (’+FTFields+‘)
REFERENCES ['+STName+'] (’+STFields+‘) ’+FKType
FROM #Temp1
SET NOCOUNT OFF
RETURN
GO

Read more @ http://blog.sqlauthority.com/2008/04/18/sql-server-generate-foreign-key-scripts-for-database/

USE master
GO
ALTER DATABASE TempDB MODIFY FILE (NAME=tempdev, FILENAME = ‘d:\data\tempdb.mdf’)
GO
ALTER DATABASE TempDB MODIFY FILE (NAME=templog, FILENAME = ‘d:\data\templog.ldf’)
GO

Now you can restart the server and notice that log and data files are created in the folder mentioned in T-SQL.

Note:
If log are left to un-restricted growth they will grow bigger than hard disk space available it will crash SQL Server completely.The best way to handle SQL Server logs are to have un-restricted grown with frequent back up of log. This will also add security to your system when it fails you can restore using logs (conditionally you have full backup to begin with). Simple recovery mode will truncate logs but it is not advisable.

Reference: Pinal Dave - SQL Authority.com

Mr.Hallengren made a solution for backup, integrity check and index optimization in SQL Server 2005. The solution is based on stored procedures, functions, sqlcmd and SQL Server Agent jobs.

Dynamic selection of databases, e.g. USER_DATABASES.
Database state check.
Robust error handling and logging.
Database backup features.

Download and install the solution in one script.

Click here to browse the article