dinsdag 25 november 2008

My Script -- DeltaProject

ReadMe:
======
Voor het creëren en registreren van de uitgevoerde scripts in de databases Volg de onderste stappen:

creeër eerst de benodigde tabel en stored procedures:
----------------------------------------------------

1): Executeur eerst de 'S:\DataManagement\MS-SQL Beheer\Changes\DeltaProject\dam_delta_log_01.sql' Script.

2): Run daarna de 'S:\DataManagement\MS-SQL Beheer\Changes\DeltaProject\sp_StartScript_02.sql' Script
en de 'S:\DataManagement\MS-SQL Beheer\Changes\DeltaProject\sp_EndScript_03.sql' Script.


Voor de registratie van de Script in de 'dam_delta_log' tabel ,voer de volgende uit:
-----------------------------------------------------------------------------------

#): Run de 'Uw_Script.sql' Script en vul de benodigde parameters in:

Script_Nummer:
------------- De Nummer van de door u gemaakte script (of door RedGate).

Database_Naam:
------------- De naam van de database waarin de script uitgevoerd moet worden.

Script_Omschrijving:
------------------- De Script omschrijving.

Test:
=====

/*** --------------Test --------------------------------------------------------------

USE [Price]
GO

SET NOCOUNT ON

DECLARE @Script_Nummer varchar(50)
DECLARE @Script_Omschrijving varchar(50)
DECLARE @Database_Naam varchar(50)

set @Script_Nummer = 'Script_Nummer';
set @Script_Omschrijving = 'Omschrijving van de script';
set @Database_Naam = db_name();
exec BeheerDB..sp_StartScript @Script_Nummer,@Database_Naam,@Script_Omschrijving
select * from BeheerDB..dam_delta_log
GO
--Execute uw sscript------------------
--------------------------------------
exec BeheerDB..sp_EndScript @Script_Nummer
select * from BeheerDB..dam_delta_log
GO
-----------------------------------------------------------------------------------
USE [BeheerDB]
GO
/****** Object: Table [dbo].[dam_delta_log] Script Date: 11/21/2008 14:15:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dam_delta_log](
[id] [int] IDENTITY(1,1) NOT NULL,
[delta] [varchar](50) NOT NULL,
[Omschrijving] [varchar](2000) NULL,
[Server] [varchar](100) NOT NULL CONSTRAINT [DF_ServerNaam] DEFAULT (@@servername),
[databasenaam] [varchar](100) NOT NULL CONSTRAINT [DF_DatabaseNaam] DEFAULT (db_name()),
[Gebruikernaam] [varchar](100) NOT NULL CONSTRAINT [DF_Gebruikernaam] DEFAULT (SYSTEM_USER),
[osuser] [varchar](100) NOT NULL CONSTRAINT [DF_Osuser] DEFAULT (suser_sname()),
[Hostnaam] [varchar](100) NOT NULL CONSTRAINT [DF_HostNaam] DEFAULT (host_name()),
[starttijd] [varchar](100) NOT NULL CONSTRAINT [DF_GetDate] DEFAULT (getdate()),
[eindtijd] [varchar](100) NULL,
CONSTRAINT [PK_dam_delta_log.dam_delta_log] PRIMARY KEY CLUSTERED
(
[id] ASC
)
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

--drop table dbo.dam_delta_log
------------------------------------------------------------------------------------
/*****************************************************************/
/* Script naam : sp_StartScript */
/* */
/* Server naam : <'Server_Naam'> */
/* */
/* Beschrijving :Dit Script wordt gemaakt voor het creëren van */
/* sp_StartScript' Stored Procedure in de 'BeheerDB'*/
/* database In Elke Server die een script moet */
/* uitgevoerd worden. */
/* */
/* */
/* File : S:\DataManagement\MS-SQL Beheer\Changes\ */
/* DeltaProject\sp_StartScript.sql */
/* */
/* Datum : 21-11-2008 */
/* Autore : Bahaa fadam */
/* Versie : */
/* Geupdated : */
/*****************************************************************/



--===============================================================
--# Script om Stored Procedure sp_StartScript te creëren =
--===============================================================

USE [BeheerDB]
GO
/****** Object: StoredProcedure [dbo].[sp_StartScript] Script Date: 11/24/2008 11:06:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sp_StartScript] (@Delta varchar(10),@databasenaam varchar(100),@Omschrijven varchar(2000))
AS

Declare @SQL as VARCHAR(2000)
Declare @RunDate as varchar(100)
Declare @i int
BEGIN TRANSACTION
SET NOCOUNT ON


SELECT @i=1
FROM master.dbo.sysdatabases
WHERE UPPER(Name) = UPPER(CONVERT(varchar(100),@databasenaam))

--zet tijd in variable om ook seconden te zien
SELECT @RunDate=convert(varchar, getdate(), 9)


If @i=1

INSERT INTO dbo.dam_delta_log (delta,Omschrijving,databasenaam,Gebruikernaam,Starttijd)
VALUES(CONVERT(varchar(100), @delta), CONVERT(varchar(100), @Omschrijven),@databasenaam,USER_NAME(),@RunDate )

print(@SQL)
EXEC(@SQL)
COMMIT TRANSACTION
------------------------------------------------------------------------------------
/*****************************************************************/
/* Script naam : sp_EndScript.sql */
/* */
/* Server naam : <'Server_Naam'> */
/* */
/* Beschrijving :Dit Script wordt gemaakt voor het creëren van */
/* 'sp_EndScript' Stored Procedure in de 'BeheerDB'*/
/* database In Elke Server die een script moet */
/* uitgevoerd worden. */
/* */
/* */
/* File : S:\DataManagement\MS-SQL Beheer\Changes\ */
/* DeltaProject\sp_EndScript.sql */
/* */
/* Datum : 21-11-2008 */
/* Autore : Bahaa fadam */
/* Versie : */
/* Geupdated : */
/*****************************************************************/



--===============================================================
--# Script om Stored Procedure sp_EndScript te creëren =
--===============================================================

USE [BeheerDB]
GO
/****** Object: StoredProcedure [dbo].[sp_EndScript] Script Date: 11/24/2008 11:38:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_EndScript]
@delta varchar(10)

AS

update BeheerDB..dam_delta_log
set eindtijd = convert(varchar, getdate(), 9)
where delta = @delta
and eindtijd is null
and starttijd = (select MAX(starttijd)
from BeheerDB..dam_delta_log
where delta = @delta
and eindtijd is null

)


GO
-----------------------------------------------------------------------------
DECLARE @Script_Nummer varchar(50)
DECLARE @Script_Omschrijving varchar(50)
DECLARE @Database_Naam varchar(50)

set @Script_Nummer = 'Script_Nummer';
set @Script_Omschrijving = 'Omschrijving van de script';
set @Database_Naam = db_name();

exec BeheerDB..sp_StartScript @Script_Nummer,@Database_Naam,@Script_Omschrijving
select * from BeheerDB..dam_delta_log
GO

--Execute uw script------------------
--
--------------------------------------

exec BeheerDB..sp_EndScript @Script_Nummer
select * from BeheerDB..dam_delta_log
GO

usp_FindTableUsage

USE [DBeheer]
GO
/****** Object: StoredProcedure [dbo].[usp_FindTableUsage] Script Date: 11/25/2008 13:58:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[usp_FindTableUsage]

AS

SET NOCOUNT ON

DECLARE @vcTableList VARCHAR(8000)
SET @vcTableList = ''

SELECT @vcTableList = COALESCE(@vcTableList+ ', ', '') + name
from sysobjects where type='U'


--Create table to hold table names
DECLARE @tblTableArray TABLE
(
TableName varchar(40)
)

-- load table names into array table
INSERT INTO @tblTableArray
SELECT Element FROM
dbo.split(@vcTableList, ',')


PRINT ''
PRINT 'REPORT FOR TABLE DEPENDENCIES for TABLES:'
PRINT '-----------------------------------------'
PRINT CHAR(9)+CHAR(9)+ REPLACE(@vcTableList,',',CHAR(13)+CHAR(10)+CHAR(9)+CHAR(9))


PRINT ''
PRINT ''
PRINT 'STORED PROCEDURES:'
PRINT ''
SELECT DISTINCT t.TableName , SUBSTRING(o.NAME,1,60) AS [Procedure Name]
FROM sysobjects o
INNER JOIN syscomments c
ON o.ID = c.ID
INNER JOIN @tblTableArray t
ON c.Text LIKE '%[ ,=]' + t.TableName + '[ .,]%'
WHERE o.XTYPE = 'P'
AND o.NAME <> 'usp_FindTableUsage'


ORDER BY t.TableName, [Procedure Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent stored procedures'

PRINT''
PRINT''
PRINT 'VIEWS:'
PRINT''
SELECT DISTINCT t.TableName , SUBSTRING(o.NAME,1,60) AS [View Name]
FROM sysobjects o
INNER JOIN syscomments c
ON o.ID = c.ID
INNER JOIN @tblTableArray t
ON c.Text LIKE '%[ ,=]' + t.TableName + '[ .,]%'
WHERE o.XTYPE = 'V'

ORDER BY t.TableName, [View Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent views'

PRINT''
PRINT''
PRINT 'FUNCTIONS:'
PRINT''
SELECT DISTINCT t.TableName , SUBSTRING(o.NAME,1,60) AS [Function Name]
FROM sysobjects o
INNER JOIN syscomments c
ON o.ID = c.ID
INNER JOIN @tblTableArray t
ON c.Text LIKE '%[ ,=]' + t.TableName + '[ .,]%'
WHERE o.XTYPE IN ('FN','IF','TF')

ORDER BY t.TableName, [Function Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent functions'

PRINT''
PRINT''
PRINT 'TRIGGERS:'
PRINT''
SELECT DISTINCT t.TableName , SUBSTRING(o.NAME,1,60) AS [Trigger Name]
FROM sysobjects o
INNER JOIN syscomments c
ON o.ID = c.ID
INNER JOIN @tblTableArray t
ON c.Text LIKE '%[ ,=]' + t.TableName + '[ .,]%'
WHERE o.XTYPE = 'TR'

ORDER BY t.TableName, [Trigger Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent triggers'


PRINT''
PRINT''
PRINT 'JOBS:'
PRINT''
DECLARE @table_name SYSNAME;
SELECT @table_name=Element FROM
dbo.split(@vcTableList, ',');

SELECT
j.name,
s.step_name,
s.command
FROM
msdb.dbo.sysjobs j
INNER JOIN
msdb.dbo.sysjobsteps s
ON
j.job_id = s.job_id
WHERE
s.command LIKE '%' + @table_name + '%';


PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent jobs'


RETURN (0)

Error_Handler:
RETURN(-1)

-------------------------------------------------------------
USE [DBeheer]
GO
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 11/25/2008 14:03:17 ******/
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Split] ( @vcDelimitedString varchar(8000),
@vcDelimiter varchar

(100) )

RETURNS @tblArray

TABLE

(
ElementID

smallint IDENTITY(1,1), --Array index
Element varchar

(1000) --Array element contents

)
AS

BEGIN


DECLARE
@siIndex smallint,
@siStart smallint,
@siDelSize smallint



SET @siDelSize = LEN(@vcDelimiter)

--loop through source string and add elements to destination table array

WHILE LEN(@vcDelimitedString) > 0

BEGIN

SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)

IF @siIndex = 0

BEGIN

INSERT INTO @tblArray VALUES(@vcDelimitedString)

BREAK

END

ELSE

BEGIN

INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))

SET @siStart = @siIndex + @siDelSize

SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)

END

END


RETURN
END



-------------------------------------------------------------

exec usp_FindTableUsage

how to check if linked server exists

how to check if linked server exists in SQL Server 2000:
--------------------------------------------------------
Select 1 Where Exists (Select [SRVID] From master..sysservers Where [srvName]='PSQLWEB1')


how to check if linked server exists in SQL Server 2005:
--------------------------------------------------------

Select 1 Where Exists (Select [SERVER_ID] From sys.servers Where [Name]='PSQLWEB1')

Shrink TempDB database

http://dimantdatabasesolutions.blogspot.com/search?updated-min=2007-01-01T00%3A00%3A00-08%3A00&updated-max=2008-01-01T00%3A00%3A00-08%3A00&max-results=44

------------------------------------------------------------------------
Shrink TempDB database
I have been recently worked for some client who has huge tempdb size (SQL Server 2000). As you probably know, you cannot reduce physical size of tempdb without restart MSSQLServices. Using the command DBCC SHRINKFILE to shrink the individual tempdb files you must run DBCC SHRINKFILE command while no other activity occurs in the tempdb database. To make sure that other processes cannot use tempdb while DBCC SHRINKFILE executes, you must restart SQL Server in the single user
mode.
So I would like to show some stepS we ended up. The client completely understands the problem cause tempdb database to be growing and I hope it is not short time solution.

1) Stop SQL Server. Open a command prompt, and then start SQL Server by
typing the following command:

"sqlservr -c -f" (remove the quotation)

The -c and -f parameters force SQL Server to start in a minimum
configuration mode with a tempdb size of 1 MB for the data file and 0.5 MB
for the log file.
Connect to SQL Server with Query Analyzer, and then issue the following
TSQL commands for the tempdb database files that you need to shrink

use tempdb
go

dbcc shrinkfile (tempdev, 'target size in MB') --for the primary data file
or
dbcc shrinkfile (templog, 'target size in MB')--for the log file

As MS says
"An advantage of DBCC SHRINKFILE is that it can reduce the size of a file to
a size smaller than its original size. A limitation of DBCC SHRINKFILE is that you cannot make the database smaller than the size of the model database."

A useful undocumented function: fn_dblog

A useful undocumented function: fn_dblog:
========================================
A useful undocumented function: fn_dblog
Posted by decipherinfosys on November 12, 2008

A friend of mine recently told me about this undocumented function: ::fn_dblog when I was talking to him about Lumigent’s Log Explorer product. He pointed me to this post by Andrew Novick which mentions about this function which has existed since quite some time. I have always been using DBCC SQLPERF(LogSpace) when I wanted to capture used and free space and at times DBCC LOGINFO with help from different sites out there to understand the output. I am a fan of Lumigent’s product and have used it quite a bit in our client engagements but the fact that something like this existed all along, was a welcome surprise to me so I thought that we would share this on the blog with our readers as well.

As the post from Andrew Novick points out, the syntax is :

::fn_dblog(@StartingLSN, @EndingLSN)And if you provide Null as the value for both the parameters, then you will get a running record of everything from the start to the end (not a good idea in a production environment but something to play around with in your development/test environment to get an understanding of the output). The post from Andrew goes into some of the details of what the different columns are for.

SQL server services didn't started after min & max server memory configuration

http://www.sqlservercentral.com/articles/Administering/2963/

The SQL Server tools are top notch & one of the tools is "sqlservr.exe" which starts, stops, pauses, and continues Database Engine from the command prompt. This article describes how to start an instance of the Database Engine.

Over-Committing Memory
I had an issue recently where SQL server services didn't started after min & max server memory configuration of SQL server was changed. The Server got configured for AWE along with min & max server memory configuration. The SQL Server didn't start after stopping the services. What I intend to show you here is how problem came to happen & how it got resolved. In this article I'd like to talk about how this over committed memory issue is been addressed when value defined for "max server memory" (RAM) is not physically present on the server. Below topic will take you from Server configuration to Boot.INI file & to enable AWE & configuring server memory using system store procedure sp_configure & finally sqlservr.exe coming in rescue to start SQL server.

My Server Configuration:
1> Windows 2000 Datacenter

2> Physical memory 36GB

3> SQL Server 2000 Enterprise Edition

Configuring AWE for SQL Server 2000

Use of the /PAE switch in the Windows Boot.INI configuration file provides access to physical memory above the 4 GB limit. This is a requirement for AWE memory access above 4 GB.

Any change to Boot.INI file requires server restart\reboot. Now is time to enable AWE on SQL Server 2000 by running "sp_configure" from query analyzer.

The LOCK PAGE IN MEMORY permission must be granted to the SQL Server account before enabling AWE (SQL SERVER 2005); this may be enabled if Windows 2003 is on SP1

(USE MASTER)
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
-- Note: - max server memory is in MB
sp_configure 'min server memory', 1024
RECONFIGURE
GO
-- 30GB is 30720MB but accidentally I typed 307200, which is 300GB
sp_configure 'max server memory', 307200
RECONFIGURE
GO
I stopped SQL Server & when I started SQL Server 2000, it didn't start, I tried other possibilities but that didn't help.

Solution
To work around the problem, start SQL Server 2000 in minimal configuration mode by using Sqlservr.exe with the -c -f option and reconfigure "max server memory"

For a SQL Server 2000 Default Instance: Navigate to the Binn folder where the SQL Server 2000 default instance is installed and run the following command:

sqlservr.exe -c -f
For a SQL Server 2000 Named Instance: Navigate to the Binn folder where the SQL Server named instance is installed and run the following command:

sqlservr.exe -c -f -s Instance_Name
Connect to SQL Server through Query Analyzer, and then run this code:

(USE MASTER)
sp_configure 'max server memory', 30720 --- (Which is now 30GB)
RECONFIGURE
GO
Navigate to the command prompt and then press CTRL+C. To shut down the SQL Server 2000 instance, type:

Y
After that when you start again SQL Server it will come up fine & your"max server memory" issue is been resolved.