SQL DBA Bookmarks

http://msmvps.com/blogs/omar/archive/2007/09/19/a-significant-part-of-sql-server-process-memory-has-been-paged-out-this-may-result-in-performance-degradation.aspx

From here you will come to know about Performance Counter Settings, CacheSet exe in Windows, Setting Working Set during Startup and Microsoft PSS blog link

In SQL 2000/2005 there is no direct feature for this. Below is the workaround.

INSERT INTO tblSuppServers (ServerName,Type)
(SELECT ‘LAB1′,’Dev’)
UNION
(SELECT ‘LAB2′,’Dev’)
UNION
(SELECT ‘LAB3′,’Dev’)
GO

For SQL 2008:
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (’First’,1),
(’Second’,2),
(’Third’,3),
(’Fourth’,4),
(’Fifth’,5)

Ref:
http://blog.sqlauthority.com/2008/07/02/sql-server-2008-insert-multiple-records-using-one-insert-statement-use-of-row-constructor/
http://www.daniweb.com/forums/thread34491.html

Have you used xp_getfiledetails extend stored procedure in SQL Server 2000? This undocumented sp used to get details of a given external files.

For example,
EXEC master.dbo.xp_getfiledetails 'c:\pagefile.sys'

will give you following details
1. Size of the file in Bytes
2.Creation Date
3.Creation Time
4. Last Written Date
5. Last Written Time
6.Last Accessed Date
7.Last Accessed Time
8.Attributes

Problem is this NOT AVAILABLE in SQL SERVER 2005. This is the problem with using undocumented sps. This is why many gurus are advise not to use undocumented sps.

Check out this blog on the updated script for sp_help_revlogin which can be used in SQL 2005 to transfer logins between DB. Also the link to SQL 2000 sp_help_revlogin is there in the blog…

SQL Server 2005: a proposed update of sp_help_revlogin

I’m planning to do Microsoft Certifications for DBA and after analysis i have found that
1) MCDBA for SQL 2000 may retire next year april (April 2009) so we have only one year left for this.
2) For SQL 2005 we have to do MCITP Database Administrator Course which i feel better since now industry demands SQL 2005.
3) For MCITP there is a pre-requiste to do MCTS so we can have two certifications.

Check out this link having comparison on the above certifications!

Also Check out this beautiful Roadmap for Ceritifications

Check this link for the Certifications for Technology

The following are the steps to perform Side by Side upgrade from SQL Server 7 or SQL Server 2000 to SQL Server 2005.

1) Take full backup of all the databases in SQL 7 or SQL 2000 for precautionary purpose
2) Script all the logins using http://support.microsoft.com/kb/246133 in SQL 7 or SQL 2000
3) Script all the jobs in SQL 7 or SQL 2000 using the below link http://msdn2.microsoft.com/en-us/library/aa177024(SQL.80).aspx
4) Install SQL Server 2005
5) Detach all the user databases in SQL 7 or SQL 2000 and copy the files
6) Attach all the user databases in SQL 2005
7) Run the script taken in Step 2 to recreate the logins
8) Run the script taken in Step 3 to recreate the jobs
9) Inorder to migrate DTS packages to SSIS you can refer this link, http://www.simple-talk.com/sql/sql-server-2005/dts-to-ssis-migration
10) Once everything is completed login and check the application functionality
The below link is helpful for getting started with upgrading to SQL Server 2005, http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en

Read Complete Article @ here….

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/

While executing the WAITFOR statement, the transaction is running and no other requests can run under the same transaction. If the server is busy, the thread may not be immediately scheduled; therefore, the time delay may be longer than the specified time. WAITFOR can be used with query but not with UDF or cursors. WAITFOR wait till TIMEOUT is reached.

--Delay for 20 seconds
WAITFOR DELAY ‘000:00:20′
SELECT ‘20 Second Delay’
GO
—Delay till 7 AM
WAITFOR TIME ‘7:00:00′
SELECT ‘Good Morning’
GO

To Read more Click the below link
http://blog.sqlauthority.com/2007/06/18/sql-server-delay-function-waitfor-clause-delay-execution-of-commands/