Archive

Archive for the ‘SQL 2000’ Category

How to find out what is causing page splits?

March 30, 2012 2 comments

Client was noticing high number of Page Splits/sec in relation to Batch Request/sec; however could not figure out why.  They were not have performance issue but still was curious what was generating it?

So how do we track it?  Well short of the answer is there is no easy way to do this.   Even though perfmon has ability to tell you page splits are taking place by Page Splits/Sec in SQLServer: Access Methods; it cannot tell you where.

We can use the fn_dblog function to read the transaction log file in SQL Server 2000/2005 and in SQL Server 2008 we can rely on extended events.

  SELECT   Operation
, AllocUnitName
		 , COUNT(*) AS NumberofIncidents
    FROM sys.fn_dblog(null, null)
   WHERE Operation = N'LOP_DELETE_SPLIT'
GROUP BY Operation, AllocUnitName

Example from #1.

I am not going to repeat the work done by others here, the code or examples are from reference links below if you need additional details.

  1. Daniel Adeniji’s – Learning in the Open. Microsoft – SQL Server – Page Splits. Link.
  2. Microsoft Connect. SQL Server Extended Events Page/Splits Event Additions. Link.
  3. SQL Server Pro. Evaluating the Page Splits/sec Value. Link.
  4. SQLBlog.Com. Michael Zilberstein (Hopefully soon to be a SQL MVP ;-)). Monitoring page splits with Extended Events. Link.

Edit Notes

  • March 30, 2012: I had it wrong that Michael is not a SQL MVP yet, so here to hoping he gets MVP :). Thanks for correction mate.

OSQL and Code Page Settings

March 6, 2009 Leave a comment

Credit: Carl B. (SQL Server Central, Link)

In SQL Server 2000, when using OSQL it is difficult to work with import files that are in different encoding. Because when importing OSQL tends to take the local code page settings and applies it to input file. Only way I found to get around that issue was to save the file as unicode and then import it. Which works; but when working on large import files this turns out to be a headach.

Carl found following registery settings can be changed to achive same affect; as recommend by him becareful about changing them though :)>. I would change them back after doing the work.

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage\]
-> OEMCP: Changing its value from 850 to 1252.

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage\]
-> 850: Changing it’s value from c_850.nls to c_1252.nls.

Interseting bit of info .. as Carl warns use it carefully, we wouldn’t want something else to blow up :).

Categories: SQL 2000, Tools Tags:

Error 14274: Cannot add, update, or delete a job (or its steps or schedule) that originated from an MSX server.

January 20, 2009 Leave a comment

You can get this error when trying to modify the properties of a Job after the SQL Server has been renamed or cloned into another computer. When we create a job on SQL Server it creates an entry in sysjobs table logging the job and server originated from. We can fix this issue by updating the sysjobs table:

USE [msdb]
GO

UPDATE sysjobs
   SET originating_server = 'NewServerName'
GO

In addition to jobs not working now the @@ServerName variable can also return invalid information, old server name. Because the @@ServerName gets the server information from the sysservers system table. We can fix this issue with following script:

USE [master]
GO

sp_dropserver 'OldServerName'
GO

sp_addserver 'NewServerName', 'local'
GO

You will have to restart the SQL Server to see the new changes.

Categories: SQL 2000, SQL Errors Tags:

Granting Execute Permissions

November 10, 2008 Leave a comment

Following code can be used to Grant Execute Permissions to a Database Role, in a database. This is for SQL Server 2000; because in 2000 you can’t just Grant Execute on a Schema or Database like we can in SQL Server 2005/2008.

EXEC dbo.sp_addrole @rolename = N'udbr_SPPermissions'
GO

EXEC dbo.sp_addrolemember N'udbr_SPPermissions', N'DOMAIN\user'
GO

 SELECT 'GRANT EXECUTE ON ' + name + ' TO udbr_SPPermissions' AS SQLStatement
   INTO #NewPermissions
   FROM sysobjects
  WHERE xtype = 'P'
    AND ( name LIKE 'sp[_]%' OR
          name LIKE 'up[_]%')
ORDER BY name
GO

DECLARE @SQLStatement VARCHAR(512)

DECLARE SPPermissions CURSOR FOR
SELECT SQLStatement
  FROM #NewPermissions

OPEN SPPermissions

  FETCH NEXT
   FROM SPPermissions
   INTO @SQLStatement

  WHILE @@FETCH_STATUS = 0
  BEGIN

     PRINT 'Executing: ' + @SQLStatement
     EXEC (@SQLStatement)

     FETCH NEXT
      FROM SPPermissions
      INTO @SQLStatement

  END

CLOSE SPPermissions
DEALLOCATE SPPermissions
GO 

Status Column in Sysusers

October 3, 2008 Leave a comment

I have encountered issues after adding logins to the SQL Server 2000 databases where even though the servers show up in the user list they still cannot access the database. It turns out I only granted the user permissions to the database and not actually added them; so it failed. In doing that found following states:

State Description

4

User has permissions to the database (objects) but user doesn’t belong to the database (Windows NT Group).

6

User has permissions to the database (objects) and user belong to the database (Windows NT Group).

12

User has permissions to the database (objects) but user doesn’t belong to the database (Windows NT User).

14

User has permissions to the database (objects) and user belong to the database (Windows NT User).

I wouldn’t recommend making any coding on this; but it is a good reference. And I don’t know if these hold true for SQL Server 2005 or 2008 yet.

Categories: Security, SQL 2000
%d bloggers like this: