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

String manipulation (extracting)

I probably should create some group of articles for string manuipulation.At this time I'd like to show you an example to extract portion of text prior/after to some charcter/symbol.An idea is simple to iterate through string and return the text prior (in my example) to 'N' appearance of specified symbol.


CREATE FUNCTION dbo.udf_Extract(
@target varchar(8000),
@string varchar(8000),
@i int
) RETURNS varchar(8000) AS BEGIN
DECLARE @pos int
SET @pos = 0
WHILE @i > 0 BEGIN
SET @pos = CHARINDEX(@target,@string,@pos+1)
SET @i = @i - 1
IF @pos = 0 RETURN '0'
END
RETURN SUBSTRING (@string,1,@pos-1)
END
GO

--Usage
SELECT dbo.udf_Extract('@','hjdhjdhj@hjjsd@hjdfhdfj@jjj,3)

Which tables have a trigger/s?

I have been recently helping my fried on very large database ,which has hundred od tables to determine which table has a trigger. So I came out with the below script that may help others to identify triggers define on the tables.

--SQL Server 2000(SP3)

SELECT
name
, CASE WHEN instrig <> 0 then OBJECT_NAME (instrig) ELSE 'None' END as
'Insert Trigger'
, CASE WHEN updtrig <> 0 then OBJECT_NAME (updtrig) ELSE 'None' END as
'Update Trigger'
, CASE WHEN deltrig <> 0 then OBJECT_NAME (deltrig) ELSE 'None' END as
'Delete Trigger'
FROM
sysobjects
WHERE
type = 'U'
AND
( instrig <> 0
OR
updtrig <> 0
OR
deltrig <> 0
)

What's the version of SQL Server?

I have seen this question (see post's subject) so many times in newsgroup asking people, so I decided to post some usefull info about SQL Server by using SERVERPROPERTY command.

SELECT @@VERSION AS [Server Information]

SELECT SERVERPROPERTY('ServerName') AS [ServerName]
SELECT SERVERPROPERTY('MachineName') AS [MachineName]
SELECT SERVERPROPERTY('InstanceName') AS [InstanceName]

SELECT SERVERPROPERTY('productversion') AS [ProductVersion]
SELECT SERVERPROPERTY('ProductLevel') AS [ProductLevel]
SELECT SERVERPROPERTY('Edition') AS [Edition]

SELECT SERVERPROPERTY('Collation') AS [Collation]
SELECT SERVERPROPERTY('IsClustered') AS [IsClustered]
SELECT SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled]
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS
[IsIntegratedSecurityOnly]
SELECT SERVERPROPERTY('LicenseType') AS [LicenseType]
SELECT SERVERPROPERTY('NumLicenses') AS [NumLicenses]

Mixed or Windows Authentication Only

Ok, at this time I would to discuss about the one but realy painful issue we have to face where we use SQL Authentication (Mixed).
Consider the following situation. You are using SQL Server (I speak for SQL Server 2000 SP4) Authentication(Mixed).
You also have a database and user within which is mapped to the SQL login. Now , you have been told to move the database on the new server. So you created a backup of the database , copied it to the new server as well as create a SQL Login with the same name as you had on the old one. Ok, now it is time to perform restore database, so everything went just fine but when theh users tried to connnect to the database (through application) on the new server they get the error. What happened? The thing is that SQL Server is 'losing' SIDs that mapped between a login and user when you created a new SQL Login on the new server (master..syslogins) and a database user (dbname..sysuser)that you brought within a database. So we have sp_change_users_login system stored procedure that takes care of re-mapping SID's.

However, that DOES NOT happen if you use Windows Authentication Only.That means SQL Server keeps SID's realtionship between Windows Login and User database although you move the database on the new server.

Just one more argument to use Windows Authentication Only.

In SQL Server 2005 SP2 MS has introduced some greate features on subject.
http://blogs.msdn.com/lcris/archive/2007/02/19/sql-server-2005-some-new-security-features-in-sp2.aspx.

Drop all indexes

Well, sometimes we have to do that. A large INSERT statement may run faster for instance. The below script just generate DROP command of all indexes in current dataabse. You can add a filter to specify a table name for example.(SQL Server 2000)

SELECT
'DROP INDEX ' +
QUOTENAME(USER_NAME(o.uid)) +
'.' +
QUOTENAME(o.name) +
'.' +
QUOTENAME(i.name)
FROM sysobjects o
JOIN sysindexes i ON
i.id = o.id
WHERE i.indid BETWEEN 1 AND 254 AND
INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 0 AND
INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 0 AND
OBJECTPROPERTY(o.id, 'IsMSShipped') = 0

Some third parties break DIFFERENTIAL backup chains

I'd like to share some things I have seen on my client's machine. He has a full backup database set up once a night and every four hours differential back during the work day. But at the same time ( it should be done at night) the network administrator has ran tird party software backup to copy .MDF/LDF files. When we started to restore database it throws the error that SQL Server cannot apply this bakup file to the full backup file created at night. We did lots of testing on the developing machine and did not het the error. And only when we asked the net.admin not to run this software then we were able to restore the database succefully.

Note, I have not seen that doing LOG files backup does break the chains.

So please do lots of testing before you find your self without properly created disaster recovery strategy.


I found that MS has this KB explaining the behaviour.
http://support.microsoft.com/kb/903643.