Category: SQL

SQL : Unlock Account

Use the following SQL to unlock an account in SQL 2005/2008, simply change the account name in square brackets:

ALTER LOGIN [sa] WITH CHECK_POLICY = OFF;
ALTER LOGIN [sa] WITH CHECK_POLICY = ON;
 

SQL Database Replication : Repairing

I recently came across an issue where SQL database replication was failing between two nodes. The replication queue was full of un-replicated transactions,I could verify this looking at the following tables:

• MSrepl_queuedtraninfo
• MSreplication_queue

In this case there was over 8 million unreplicated transactions. Due to the shear number of unreplicated transactions I decided that it would probably be best to simply re-seed the database on the remote node. The process below outlines how this was achieved.

Please note that this process requires permissions to be reset on the subscriber once completed.

1. Perform backup of publisher (COMMS-S01) and subscriber (COMMS-M01) database.
2. Stop OTS (stop OTS service on COMMS-TRANS01/02 and kill any OTS.exe processes via task manager)
3. Stop the log reader and distribution replication agents.
4. Use the following commands to flush the replication queue: [less than one minute to run]
a. EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
b. EXEC sp_replflush
5. Verify that the queue is now empty on the subscriber: [less than one minute to run]
a. EXEC sp_replshowcmds
b. DBCC opentrans
6. Now un-publish and truncate the transaction log on the publisher (comms-s01):
a. BACKUP LOG OTSTrack WITH TRUNCATE_ONLY
b. DBCC SHRINKFILE (OTSTrackLog, NOTRUNCATE)
c. DBCC SHRINKFILE (OTSTrackLog, TRUNCATEONLY)
7. Finally shrink the database on both comms-s01:
a. DBCC SHRINKDATABASE(OTSTrack, 10)
8. Drop the OTSTrack database on COMMS-M01 (standby)- this will be recreated during replication setup.
9. Check the rowcount of the following tables in the distribution database (should not be 8 million):
a. MSrepl_queuedtraninfo
b. MSreplication_queue
10. Re-republish the database and configure sunscriptions as per GL documentation.
11. Check that replication agents are started, if not start log reader first then distribution agent.
12. Re-create user accounts on COMMS-M01 using attached ‘script.’
13. Run Stored Procedure Permissions Script on subscriber (comms-s01)
14. Start OTS (start OTS service on COMMS-TRANS01/02)

SQL : Table Two-Way Column Comparison

This article was originally posted here: http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx I have found it useful so thought I would repost it here.

The following SQL will create a stored procedure that will perform a two-way comparison of columns in different tables.

CREATE PROCEDURE CompareTables(@table1 varchar(100),
 @table2 Varchar(100), @T1ColumnList varchar(1000),
 @T2ColumnList varchar(1000) = ”)
AS
 
— Table1, Table2 are the tables or views to compare.
— T1ColumnList is the list of columns to compare, from table1.
— Just list them comma-separated, like in a GROUP BY clause.
— If T2ColumnList is not specified, it is assumed to be the same
— as T1ColumnList.  Otherwise, list the columns of Table2 in
— the same order as the columns in table1 that you wish to compare.
—
— The result is all rows from either table that do NOT match
— the other table in all columns specified, along with which table that
— row is from.
 
declare @SQL varchar(8000);
IF @t2ColumnList = ” SET @T2ColumnList = @T1ColumnList
set @SQL = ‘SELECT ”’ + @table1 + ”’ AS TableName, ‘ + @t1ColumnList +
 ‘ FROM ‘ + @Table1 + ‘ UNION ALL SELECT ”’ + @table2 + ”’ As TableName, ‘ +
 @t2ColumnList + ‘ FROM ‘ + @Table2
set @SQL = ‘SELECT Max(TableName) as TableName, ‘ + @t1ColumnList +
 ‘ FROM (‘ + @SQL + ‘) A GROUP BY ‘ + @t1ColumnList +
 ‘ HAVING COUNT(*) = 1’
exec ( @SQL)

You can execute the query using the command:

DECLARE    @return_value int
EXEC    @return_value = [dbo].[CompareTables]
        @table1 = N’otProductSiteInclusion’,
        @table2 = N’otDealers’,
        @T1ColumnList = N’dealerid’,
        @T2ColumnList = N’dealerid’
SELECT    ‘Return Value’ = @return_value
GO

SQL : Table Column Comparison

The following SQL will perform a one way comparison of a column from one table with another. Any listed items do not exist in the second table:

— Identify if values in Table1 exist in Table2
Select DealerID
FROM Table1
WHERE NOT EXISTS
(SELECT 1 FROM Table2
WHERE Table1.DealerID = Table2.DealerID

This can be extended to multiple columns:

Select DealerID, Name
FROM TABLE1
WHERE NOT EXISTS
(SELECT 1 FROM TABLE2
WHERE Table1.DealerID = Table2.DealerID
AND Table1.Name = Table2.Name
)

SQL : Bulk Delete in Batches

The following query will delete rows from a table in batches of 200, this can help reduce locks during the delete process on tables with a large row count. You can increase the rowcount by changing the number in red;

SET ROWCOUNT 200
WHILE 1 = 1
BEGIN
  DELETE FROM messages

  IF @@ROWCOUNT = 0
  BREAK
END

SQL : View Table Indexes

The following SQL will show index related information for a specific table within the local database context, this data will contain the index name and indexed fields:

EXEC sp_helpindex ‘messages’

You can view index fragmentation data on a per table basis using the command:

DBCC SHOWCONTIG tablename

For a more easy-to-read fragmentation report using the following query which will display indexes fragmented more than 40%, if you wish to modify the display criteria change the line highlighted in red (WHERE LogicalFrag > 40):

SET NOCOUNT ON

print ‘Cleaning up old temp tables’
IF object_id(‘tempdb..#spt_space’) IS NOT NULL
    BEGIN
       DROP TABLE #spt_space
    END

IF object_id(‘tempdb..#bigtables’) IS NOT NULL
    BEGIN
       DROP TABLE #bigtables
    END

IF object_id(‘tempdb..#fraglist’) IS NOT NULL
    BEGIN
       DROP TABLE #fraglist
    END
    
declare @tablename varchar (128)
declare @id int
declare @Cnt smallint
declare @nrc_mask int
declare @dbname varchar (200)

CREATE TABLE #spt_space
    (    aidi    int,
        [rows]        int null,
        reserved    dec(15) null,
        AUTOSTATS      char(2) null,
        [Last Updated] datetime
    )
    
CREATE TABLE #fraglist (
   ObjectName CHAR (255), ObjectId INT,
   IndexName CHAR (255), IndexId INT,
   Lvl INT, CountPages INT,
   CountRows INT, MinRecSize INT,
   MaxRecSize INT, AvgRecSize INT,
   ForRecCount INT, Extents INT,
   ExtentSwitches INT, AvgFreeBytes INT,
   AvgPageDensity INT, ScanDensity DECIMAL,
   BestCount INT, ActualCount INT,
   LogicalFrag DECIMAL, ExtentFrag DECIMAL
    )
    
CREATE TABLE #bigtables (
    i int NOT NULL IDENTITY(1,1),
    tablename varchar(128)
    )

/* Create cursor for per-table analysis and analyse tables for INDEX and AUTOSTATS data */
print ‘Creating cursor or per-table analysis’
declare tables cursor for
   select TABLE_SCHEMA + ‘.’ + TABLE_NAME
   from INFORMATION_SCHEMA.TABLES
   where TABLE_TYPE = ‘BASE TABLE’

print ‘Executing tables cursor’
open tables
    /*
    ** Set NORECOMPUTE mask
    */
    set @nrc_mask = 16777216

fetch next
   from tables
   into @tablename
while @@fetch_status = 0
begin

    select @id = object_id(@tablename)

    insert into #spt_space (aidi, reserved)
        select @id, sum(reserved)
            from sysindexes
                where indid in (0, 1, 255)
                    and id = @id
    update #spt_space set     [rows] = i.[rows]
            from #spt_space inner join sysindexes i
            on #spt_space.aidi = i.id
                where i.indid                     and i.id = @id
    update #spt_space set     [AUTOSTATS] =
                case (si.status & @nrc_mask)
                when @nrc_mask then ‘OFF’
                else ‘ON’
            end,
               [Last Updated] = stats_date(#spt_space.aidi, si.indid)
        from sysindexes si inner join #spt_space
            on #spt_space.aidi = si.id
        where si.id = @id            AND        — Table
              si.indid BETWEEN 1 AND 254            — Skip HEAP/TEXT index
   fetch next
      from tables
      into @tablename
end

print ‘Closing and deallocating tables cursor’
close tables
deallocate tables
    
INSERT #bigtables(tablename)
SELECT object_name(aidi) as tablename
FROM #spt_space
WHERE rows >= 100000

SELECT @Cnt = count(*) FROM #bigtables

DECLARE @DTime datetime
Set @DTime = GETDATE()

/* Obtain INDEX fragmentation data */
print ‘Updating Fragmentation temp table’
SET @id = 1
WHILE @id BEGIN
   SELECT @tablename = tablename FROM #bigtables WHERE i = @id
   INSERT INTO #fraglist
   EXEC (‘DBCC SHOWCONTIG (”’ + @tablename + ”’)
    WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS’)     
   SET @id = @id + 1
END

/* Display Fragmentation Data > 40% */
SELECT ObjectName, ObjectId, IndexName, IndexId, Lvl, CountPages,
           ExtentSwitches, ScanDensity, BestCount , ActualCount ,LogicalFrag  
FROM #fraglist
WHERE LogicalFrag > 40
ORDER BY LogicalFrag DESC
       
DROP TABLE #spt_space
DROP Table #bigtables
DROP TABLE #fraglist

SQL : View Table Size

The following SQL will provide size infomation (in KB) for each table in the local database contex in a single table:

  SET NOCOUNT ON
  DBCC UPDATEUSAGE(0)

  SELECT sysobjects.[name] AS [TableName],
    SUM(sysindexes.reserved) * 8 AS [Size(KB)],
    SUM(sysindexes.dpages) * 8 AS [Data(KB)],
    (SUM(sysindexes.used) – SUM(sysindexes.dpages)) * 8 AS [Indexes(KB)],
    (SUM(sysindexes.reserved) – SUM(sysindexes.dpages)) * 8 AS [Unused(KB)]
  FROM dbo.sysindexes AS sysindexes
    JOIN dbo.sysobjects AS sysobjects ON sysobjects.id = sysindexes.id
  WHERE sysobjects.[type] = ‘U’
  GROUP BY sysobjects.[name]
  ORDER BY [Size(KB)] DESC

 

 

SQL : View Table Row Count

The following SQL will provide table name and rowcount for every table in the local database context:
  SELECT object_name (i.id) TableName, rows as RowCnt
  FROM sysindexes i INNER JOIN sysObjects o ON (o.id = i.id AND o.xType = ‘U’)
  WHERE indid   ORDER BY RowCnt

The reults will be similar to the output below:

tableName	RowCnt
messages	6342
orders	3221
dealer	101

SQL : View Running Trace Information

The following SQL can be used to identify any traces that are active on a SQL instance.

Use the query below to see how many running traces there are on the instance:

SELECT count(*) FROM :: fn_trace_getinfo(default) WHERE property = 5 and value = 1

running

1

The next query will return more detailed information about the active traces:

SELECT * FROM :: fn_trace_getinfo(default)

traceid	property	value
1	1	0
1	2	c:\temp\TraceGlobal
1	3	5
1	4	29:27.5
1	5	1

To halt the running trace use the fllowing query:

EXEC sp_trace_setstatus  1, @status = 0

To delete the trace definition from the database:

EXEC sp_trace_setstatus  1, @status = 2

 

SQL 2000 : Enabling AWE on Windows Server

On an x86/i386 system it is possible to use PAE and AWE to allow SQL to use more than 2GB of RAM. Windows Server Advanced 2000 x86 allows for up to 8GB of RAM using PAE and AWE, Windows Server 2003 Enterprise allows for up to 16GB.Datacenter editions allow for even greater ammounts of PAE/AWE assigned RAM.

First configure the /PAE switch in the operating system boot.ini file. You can also use the /3GB switch if not configuring more than 16GB of RAM.

Next, run the following SQL to enabled the instance to use AWE, and therefore the newly available RAM.

sp_configure ‘show advanced options’, 1
RECONFIGURE
GO
sp_configure ‘awe enabled’, 1
RECONFIGURE
GO
sp_configure ‘max server memory’, 2048 — This Sets The Allocation To 2 Gigabyte
RECONFIGURE
GO 

You will have to restart the SQL instance for the change to become effective.

Considerations:

• The total sum of all SQL assignd RAM should not be greater than all of the memory in the server; you should removed 2GB from this total for the OS if not using the /3GB  switch, or 1GB if using the /3GB switch.
• You should configure the SQL service account to have ‘Lock Pages In Memory‘ permissions; this will prevent the AWE memory being paged to disk.
• On a failover cluster environment, the sum of ALL instance assigned AWE memory should be no greater than the total memory (taking the kernel reserved 2GB/1GB depending on /3GB switch) on a single node. If this is exceeded, any instance which starts on a node where all memory is assigned will start in dynamic mode with 128MB RAM, or my even fail to start.
• The ‘min server memory’ option is ignored when using AWE.
• You cannot monitor SQL server memory use when utilising AWE from Task Manager – this will simply show the SQL instance using the total amount of memory. The following SQL can be used to identify real memory usage:

select counter_name,cntr_value/1024 As MemoryUsedMB from master..sysperfinfo
where counter_name = ‘Total Server Memory (KB)’

• AWE is is an enabler allowing a 32-bit Operating System to address more than 4GB of physical memory.; there are obvious benifits however, there are performance considerations which should not be over looked when using AWE. For example, AWE memory cannot be swapped to the page file, therefore you should closely monitor application memory requirements after machine startup before allocating memory to SQL.