Trigger's definition

I have had a clinet who wanted to script out the source of triggers on database level, so I'm talking about SQL Server 2005. Well as you probably know you can easily script the objects by using Tasks ---> Generate Scripts.. and moreover if you installed SP2 you will be able to script the object per file.

I would like to show you more simple way. You join two system tables on object_id column where sys.sql_modules table has a definition column which my client wanted to get.The WHERE condition restricts the ouptupt for triggers that were designed on database level.

SELECT definition FROM sys.sql_modules sm
INNER JOIN sys.triggers st ON
sm.object_id=st.object_id
WHERE parent_class_desc ='DATABASE'

Is Linked Server available?

In SQL Server 2000 we have been checking an availability of linked server by using
xp_cmdshell along with 'ping' command.It worked fine but what does SQL Server 2005 propose to us?
There is a system stored procedure (sp_testlinkedserver )that accepts name of linked server as a parameter and returns the status of server.

Take a look at the script below.

DECLARE @srvr NVARCHAR(128), @statusval INT;
SET @srvr = 'Linked_Server';
BEGIN TRY
EXEC @statusval = sp_testlinkedserver @srvr;
END TRY
BEGIN CATCH
SET @statusval = SIGN(@@ERROR);
END CATCH;
IF @statusval <> 0
RAISERROR('Unable to connect to linked server. Try later!', 16, 2 );

Enabling AWE on Cluster environment

I know many people ask this question. Recently I had a client wants me to enable AWE on the cluster which has two nodes (Active/Passive). He had only one instance of SQL Server 2000 (SP4) and 8 GB RAM and /3GB in the Boot.INI.

1)
Add /PAE parameter to Boot.INI file. PAE is the added ability of the IA32 processor to address more than 4 GB of physical memory.
2)
You need to install hotfix on SQL Server instance from Microsoft (http://support.microsoft.com/kb/899761) that fixs the bug "Not all memory is available when AWE is enabled".
3)
Make sure that domain account SQL Server is running under is added to "Lock pages in memory" local group policy.
4)
Finally we configured 6 GB for SQL Server as it was business requirements.

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE

5)Restart OS

Note: we need to run those steps on Active node where the SQL Server instance is running on. When it failed over but the binaries are loaded on all
nodes.

Linked servers and an Identity property

Someone in sqlserver.programming group asked nice question about getting an IDENTITY from the linked server. I came up with the following solution. I assume that there is a table on the linked server that has one column defined as indentity property.

CREATE FUNCTION dbo.fn_get_remoteidentity()
RETURNS INT
AS
BEGIN
RETURN(
SELECT row_id
FROM OPENQUERY(
[put here your server name],
'SET NOCOUNT ON;
INSERT INTO dbname..usertable DEFAULT VALUES
ROLLBACK;
SELECT SCOPE_IDENTITY() AS row_id;') AS Der)
END
Another question was how to insert an INDENTITY property in the table on remote(linked) server?
Well in that case you will need to build dynamic SQL along with SET IDENTITY_INSERT
command.

DECLARE @sql NVARCHAR(400)
SELECT @sql = '
CREATE TABLE test(id INT IDENTITY(1,1))
SET IDENTITY_INSERT test ON
INSERT test DEFAULT VALUES
SET IDENTITY_INSERT test OFF
SELECT id FROM test'
--Usage
EXEC [LinkedServer].master.dbo.sp_executesql @sql

Global stored procedure

I have my doubts that many of us have been used this system stored procedure(sp_MS_marksystemobject) to resolve the problems. But it exists and gives you an ability to return the data in the scope of selected database. Well, across number of databases having one table with the same structure and being in the scope of desired database you'll return the data (by using the following stored procedure) from the table located in desired database.To make the stored procedure to recognize the 'desired' data we use sp_MS_marksystemobject system stored procedure. BTW, it works as well as on SQL Server 2000 and on SQL Server 2005(SP2).

USE master
CREATE TABLE test (c1 VARCHAR(50))
INSERT test VALUES('master')
go
CREATE PROC sp_test AS
SELECT * FROM test
GO
USE northwind
CREATE TABLE test (c1 VARCHAR(50))
INSERT test VALUES('northwind')
USE pubs
CREATE TABLE test(c1 VARCHAR(50))
INSERT test VALUES('pubs')
USE pubs
EXEC sp_test --returns 'master'
USE master
EXEC sp_MS_marksystemobject sp_test
USE pubs
EXEC sp_test --returns 'pubs'
USE northwind
EXEC sp_test --returns 'northwind'

Find specific data type of the column

Recently i was told to return tables which have FLOAT datatype defined on columns.The version of the product was SQL Server 2000 ,however it will work on SQL Server 2005 as well, so take a look at very very simple script.

SELECT columns.TABLE_NAME ,COLUMN_NAME
FROM information_schema.columns columns
JOIN information_schema.tables tables
ON tables.table_name = columns.table_name
WHERE tables.table_type = 'base table'
AND columns.data_type = 'float'
ORDER BY columns.table_name, columns.column_name

Can we look into LOG file?

In SQL Server 2005 with fn_dblog function you can get more info about what's happening in SQL Server. MS has added a new data like 'partitionid' column for example.

Let us do some testing

USE Demo
--create a table for testing
CREATE TABLE dbo.Test1 (c INT)
--populate the table with data

WITH numbers AS
(SELECT 1 as Num
UNION ALL
SELECT Num + 1 FROM numbers WHERE Num <10)
INSERT INTO dbo.Test1 SELECT Num FROM numbers OPTION(Maxrecursion 10)

--delete the data
DELETE FROM dbo.Test1

In order to identify how many 'DELETE' operation and when does it start? Run the followinq query.

SELECT [Transaction Name],[begin time FROM ::fn_dblog( NULL,NULL)WHERE [Transaction Name] ='DELETE'

Ok, the next step I'd like to know how long does DELETION take? So we have [end time column] for this purpose. We need to write the below query which gets little complicated.

WITH cte_delete
AS
(
SELECT [Transaction Name],[begin time],[end time]
FROM ::fn_dblog( NULL,NULL)d
WHERE [begin time] IS NOT NULL OR [end time]IS NOT NULL
) ,cte_d
AS
(
SELECT [Transaction Name],[begin time],(SELECT MIN([end time]) FROM cte_delete C
WHERE cte_delete.[begin time]<=C.[end time])AS [end time]
FROM cte_delete WHERE [Transaction Name] ='DELETE' OR
[Transaction Name] IS NULL
)
SELECT * FROM cte_d
WHERE [begin time] IS NOT NULL OR [end time]IS NOT NULL

Also take a look at 'Operation' column.

SELECT Operation,AllocUnitName,[Transaction Name],[begin time],[end time]
FROM ::fn_dblog( NULL,NULL)
WHERE Operation ='LOP_DELETE_ROWS' OR [Transaction Name]='DELETE'

To identify which tables are being hit by DELETE operation.

SELECT AllocUnitName, COUNT([Current LSN])
FROM ::fn_dblog(NULL,NULL )
WHERE Operation = N'LOP_DELETE_ROWS'
GROUP BY AllocUnitName