Google Custom Search

Saturday, March 28, 2009

Database Size Info

declare @dbname sysname

set nocount on

if @dbname is not null and @dbname not in (select name from sysdatabases (nolock))

begin

raiserror('Incorrect database name. ', 16, 1)

end

create table #datafilestats

(    dbname varchar(25),

    flag bit default 0,

    Fileid tinyint,

    [FileGroup] tinyint,

    TotalExtents dec (8, 1),

    UsedExtents dec (8, 1),

    [Name] varchar(50),

    [FileName] sysname )

declare @string sysname, @dbname1 sysname

set @string = ''

if @dbname is not null

    set @dbname1 = @dbname

else

    set @dbname1 = ''

set @dbname = ''

while 1=1

    begin

     select top 1 @dbname = name from master..sysdatabases where name > @dbname --order by name asc     

     if @@rowcount = 0

     break

     set @string = 'use ' + @dbname + ' DBCC SHOWFILESTATS with no_infomsgs'

    insert into #datafilestats (Fileid, [FileGroup] , TotalExtents , UsedExtents , [Name] , [FileName]) exec (@string)

update #datafilestats set dbname = @dbname, flag = 1 where flag = 0

    update #datafilestats set TotalExtents = (select sum(TotalExtents)*8*8192.0/1048576.0 from #datafilestats where dbname = @dbname)

        where flag = 1 and Fileid = 1 and FileGroup = 1 and dbname = @dbname

    update #datafilestats set UsedExtents = (select sum(UsedExtents)*8*8192.0/1048576.0 from #datafilestats where dbname = @dbname)

        where flag = 1 and Fileid = 1 and FileGroup = 1 and dbname = @dbname

    end

create table #sizeinfo 

( db_name varchar(30) not null primary key clustered,

total dec (7, 1),

data dec (7, 1),

data_used dec (7, 1),

[data (%)] dec (7, 1),

data_free dec (7, 1),

[data_free (%)] dec (7, 1),

log dec (7, 1),

log_used dec (7, 1),

[log (%)] dec (7, 1),

log_free dec (7, 1),

[log_free (%)] dec (7, 1),

status dec (7, 1) )    

insert #sizeinfo (db_name, log, [log (%)], status ) exec ('dbcc sqlperf(logspace) with no_infomsgs')

update #sizeinfo set

data = d.TotalExtents from #datafilestats d join #sizeinfo s on d.dbname = s.db_name where d.flag = 1 and d.Fileid = 1 and d.FileGroup = 1

update #sizeinfo set

data_used = d.UsedExtents from #datafilestats d join #sizeinfo s on d.dbname = s.db_name where d.flag = 1 and d.Fileid = 1 and d.FileGroup = 1

update #sizeinfo set

total = (data + log)

update #sizeinfo set

    [data (%)] = (data_used * 100.0 / data)

update #sizeinfo set

     data_free = (data - data_used)

update #sizeinfo set

     [data_free (%)] = (100 - [data (%)])

update #sizeinfo set

    log_used = (log * [log (%)] / 100.0)

update #sizeinfo set

    log_free = (log - log_used)

update #sizeinfo set

    [log_free (%)] = (log_free * 100.0 / log)

print ''

if @dbname1 = ''

begin

print 'Database size report on ' + @@servername + ' as of ' + convert(varchar(30), getdate(), 100) + char(10)

    select db_name,

total,

data,

data_used,

[data (%)],

data_free,

[data_free (%)],

log,

log_used,

[log (%)],

log_free,

[log_free (%)]

from #sizeinfo order by db_name asc compute sum(total)    

end

else

begin

print 'Database size report on ' + @@servername + '.' + @dbname1 + ' as of ' + convert(varchar(30), getdate(), 100) + char(10)

    select db_name,

total,

data,

data_used,

[data (%)],

data_free,

[data_free (%)],

log,

log_used,

[log (%)],

log_free,

[log_free (%)]

from #sizeinfo where db_name = @dbname1    

end

go

drop table #datafilestats 

drop table #sizeinfo

Thursday, March 26, 2009

Job Schedule Information for SQL Server 2005

SELECT
   @@servername as [Server], msdb.dbo.sysjobs.name as [Name]
  , CASE
       WHEN msdb.dbo.sysjobs.enabled = 0 THEN 'Disabled'
       WHEN msdb.dbo.sysjobs.job_id IS NULL THEN 'Unscheduled'
       WHEN msdb.dbo.sysschedules.freq_type = 0x1 -- OneTime
           THEN
               'Once on '
             + CONVERT(
                          CHAR(10)
                        , CAST( CAST( msdb.dbo.sysschedules.active_start_date AS VARCHAR ) AS DATETIME )
                        , 102 -- yyyy.mm.dd
                      )
       WHEN msdb.dbo.sysschedules.freq_type = 0x4 -- Daily
           THEN 'Daily'
       WHEN msdb.dbo.sysschedules.freq_type = 0x8 -- weekly
           THEN
               CASE
                   WHEN msdb.dbo.sysschedules.freq_recurrence_factor = 1
                       THEN 'Weekly on '
                   WHEN msdb.dbo.sysschedules.freq_recurrence_factor > 1
                       THEN 'Every '
                          + CAST( msdb.dbo.sysschedules.freq_recurrence_factor AS VARCHAR )
                          + ' weeks on '
               END
             + LEFT(
                         CASE WHEN msdb.dbo.sysschedules.freq_interval &  1 =  1 THEN 'Sunday, '    ELSE '' END
                       + CASE WHEN msdb.dbo.sysschedules.freq_interval &  2 =  2 THEN 'Monday, '    ELSE '' END
                       + CASE WHEN msdb.dbo.sysschedules.freq_interval &  4 =  4 THEN 'Tuesday, '   ELSE '' END
                       + CASE WHEN msdb.dbo.sysschedules.freq_interval &  8 =  8 THEN 'Wednesday, ' ELSE '' END
                       + CASE WHEN msdb.dbo.sysschedules.freq_interval & 16 = 16 THEN 'Thursday, '  ELSE '' END
                       + CASE WHEN msdb.dbo.sysschedules.freq_interval & 32 = 32 THEN 'Friday, '    ELSE '' END
                       + CASE WHEN msdb.dbo.sysschedules.freq_interval & 64 = 64 THEN 'Saturday, '  ELSE '' END
                     , LEN(
                                CASE WHEN msdb.dbo.sysschedules.freq_interval &  1 =  1 THEN 'Sunday, '    ELSE '' END
                              + CASE WHEN msdb.dbo.sysschedules.freq_interval &  2 =  2 THEN 'Monday, '    ELSE '' END
                              + CASE WHEN msdb.dbo.sysschedules.freq_interval &  4 =  4 THEN 'Tuesday, '   ELSE '' END
                              + CASE WHEN msdb.dbo.sysschedules.freq_interval &  8 =  8 THEN 'Wednesday, ' ELSE '' END
                              + CASE WHEN msdb.dbo.sysschedules.freq_interval & 16 = 16 THEN 'Thursday, '  ELSE '' END
                              + CASE WHEN msdb.dbo.sysschedules.freq_interval & 32 = 32 THEN 'Friday, '    ELSE '' END
                              + CASE WHEN msdb.dbo.sysschedules.freq_interval & 64 = 64 THEN 'Saturday, '  ELSE '' END
                          ) - 1  -- LEN() ignores trailing spaces
                   )
       WHEN msdb.dbo.sysschedules.freq_type = 0x10 -- monthly
           THEN
               CASE
                   WHEN msdb.dbo.sysschedules.freq_recurrence_factor = 1
                       THEN 'Monthly on the '
                   WHEN msdb.dbo.sysschedules.freq_recurrence_factor > 1
                       THEN 'Every '
                          + CAST( msdb.dbo.sysschedules.freq_recurrence_factor AS VARCHAR )
                          + ' months on the '
               END
             + CAST( msdb.dbo.sysschedules.freq_interval AS VARCHAR )
             + CASE
                   WHEN msdb.dbo.sysschedules.freq_interval IN ( 1, 21, 31 ) THEN 'st'
                   WHEN msdb.dbo.sysschedules.freq_interval IN ( 2, 22     ) THEN 'nd'
                   WHEN msdb.dbo.sysschedules.freq_interval IN ( 3, 23     ) THEN 'rd'
                   ELSE 'th'
               END
       WHEN msdb.dbo.sysschedules.freq_type = 0x20 -- monthly relative
           THEN
               CASE
                   WHEN msdb.dbo.sysschedules.freq_recurrence_factor = 1
                       THEN 'Monthly on the '
                   WHEN msdb.dbo.sysschedules.freq_recurrence_factor > 1
                       THEN 'Every '
                          + CAST( msdb.dbo.sysschedules.freq_recurrence_factor AS VARCHAR )
                          + ' months on the '
               END
             + CASE msdb.dbo.sysschedules.freq_relative_interval
                   WHEN 0x01 THEN 'first '
                   WHEN 0x02 THEN 'second '
                   WHEN 0x04 THEN 'third '
                   WHEN 0x08 THEN 'fourth '
                   WHEN 0x10 THEN 'last '
               END
             + CASE msdb.dbo.sysschedules.freq_interval
                   WHEN  1 THEN 'Sunday'
                   WHEN  2 THEN 'Monday'
                   WHEN  3 THEN 'Tuesday'
                   WHEN  4 THEN 'Wednesday'
                   WHEN  5 THEN 'Thursday'
                   WHEN  6 THEN 'Friday'
                   WHEN  7 THEN 'Saturday'
                   WHEN  8 THEN 'day'
                   WHEN  9 THEN 'week day'
                   WHEN 10 THEN 'weekend day'
               END
       WHEN msdb.dbo.sysschedules.freq_type = 0x40
           THEN 'Automatically starts when SQLServerAgent starts.'
       WHEN msdb.dbo.sysschedules.freq_type = 0x80
           THEN 'Starts whenever the CPUs become idle'
       ELSE ''
   END
 + CASE
       WHEN msdb.dbo.sysjobs.enabled = 0 THEN ''
       WHEN msdb.dbo.sysjobs.job_id IS NULL THEN ''
       WHEN msdb.dbo.sysschedules.freq_subday_type = 0x1 OR msdb.dbo.sysschedules.freq_type = 0x1
           THEN ' at '
             + CASE
                   WHEN msdb.dbo.sysschedules.active_start_time =      0 THEN '12:00'
                   WHEN msdb.dbo.sysschedules.active_start_time = 120000 THEN '12:00'
                   WHEN msdb.dbo.sysschedules.active_start_time <>
                       THEN STUFF(
                                     LEFT( CAST ( msdb.dbo.sysschedules.active_start_time AS VARCHAR ), 3 )
                                   , 2
                                   , 0
                                   , ':'
                                 )
                   WHEN msdb.dbo.sysschedules.active_start_time <>
                       THEN STUFF(
                                     LEFT( CAST ( msdb.dbo.sysschedules.active_start_time AS VARCHAR ), 4 )
                                   , 3
                                   , 0
                                   , ':'
                                 )
                   WHEN msdb.dbo.sysschedules.active_start_time <>
                       THEN STUFF(
                                     LEFT( CAST ( msdb.dbo.sysschedules.active_start_time - 120000 AS VARCHAR ), 3 )
                                   , 2
                                   , 0
                                   , ':'
                                 )
                   ELSE STUFF(
                                 LEFT( CAST ( msdb.dbo.sysschedules.active_start_time - 120000 AS VARCHAR ), 4 )
                               , 3
                               , 0
                               , ':'
                             )
               END
             + CASE
                   WHEN msdb.dbo.sysschedules.active_start_time <>
                   ELSE ' PM'
               END
       WHEN msdb.dbo.sysschedules.freq_subday_type IN ( 0x2, 0x4, 0x8 )
           THEN ' every '
             + CAST( msdb.dbo.sysschedules.freq_subday_interval AS VARCHAR )
             + CASE freq_subday_type
                   WHEN 0x2 THEN ' second'
                   WHEN 0x4 THEN ' minute'
                   WHEN 0x8 THEN ' hour'
               END
             + CASE
                   WHEN msdb.dbo.sysschedules.freq_subday_interval > 1 THEN 's'
               END
       ELSE ''
   END
 + CASE
       WHEN msdb.dbo.sysjobs.enabled = 0 THEN ''
       WHEN msdb.dbo.sysjobs.job_id IS NULL THEN ''
       WHEN msdb.dbo.sysschedules.freq_subday_type IN ( 0x2, 0x4, 0x8 )
           THEN ' between '
             + CASE
                   WHEN msdb.dbo.sysschedules.active_start_time =      0 THEN '12:00'
                   WHEN msdb.dbo.sysschedules.active_start_time = 120000 THEN '12:00'
                   WHEN msdb.dbo.sysschedules.active_start_time <>
                       THEN STUFF(
                                     LEFT( CAST ( msdb.dbo.sysschedules.active_start_time AS VARCHAR ), 3 )
                                   , 2
                                   , 0
                                   , ':'
                                 )
                   WHEN msdb.dbo.sysschedules.active_start_time <>
                       THEN STUFF(
                                     LEFT( CAST ( msdb.dbo.sysschedules.active_start_time AS VARCHAR ), 4 )
                                   , 3
                                   , 0
                                   , ':'
                                 )
                   WHEN msdb.dbo.sysschedules.active_start_time <>
                       THEN STUFF(
                                     LEFT( CAST ( msdb.dbo.sysschedules.active_start_time - 120000 AS VARCHAR ), 3 )
                                   , 2
                                   , 0
                                   , ':'
                                 )
                   ELSE STUFF(
                                 LEFT( CAST ( msdb.dbo.sysschedules.active_start_time - 120000 AS VARCHAR ), 4 )
                               , 3
                               , 0
                               , ':'
                             )
               END
             + CASE
                   WHEN msdb.dbo.sysschedules.active_start_time <>
                   ELSE ' PM'
               END
             + ' and '
             + CASE
                   WHEN msdb.dbo.sysschedules.active_end_time =      0 THEN '12:00'
                   WHEN msdb.dbo.sysschedules.active_end_time = 120000 THEN '12:00'
                   WHEN msdb.dbo.sysschedules.active_end_time <>
                       THEN STUFF(
                                     LEFT( CAST ( msdb.dbo.sysschedules.active_end_time AS VARCHAR ), 3 )
                                   , 2
                                   , 0
                                   , ':'
                                 )
                   WHEN msdb.dbo.sysschedules.active_end_time <>
                       THEN STUFF(
                                     LEFT( CAST ( msdb.dbo.sysschedules.active_end_time AS VARCHAR ), 4 )
                                   , 3
                                   , 0
                                   , ':'
                                 )
                   WHEN msdb.dbo.sysschedules.active_end_time <>
                       THEN STUFF(
                                     LEFT( CAST ( msdb.dbo.sysschedules.active_end_time - 120000 AS VARCHAR ), 3 )
                                   , 2
                                   , 0
                                   , ':'
                                 )
                   ELSE STUFF(
                                 LEFT( CAST ( msdb.dbo.sysschedules.active_end_time - 120000 AS VARCHAR ), 4 )
                               , 3
                               , 0
                               , ':'
                             )
               END
             + CASE
                   WHEN msdb.dbo.sysschedules.active_end_time <>
                   ELSE ' PM'
               END

       ELSE ''
   END AS Schedule

FROM         msdb.dbo.sysjobs INNER JOIN
                      msdb.dbo.sysjobschedules ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobschedules.job_id INNER JOIN
                      msdb.dbo.sysschedules ON msdb.dbo.sysjobschedules.schedule_id = msdb.dbo.sysschedules.schedule_id

Wednesday, March 25, 2009

Database Details

create table #dbdetails(name varchar(30), fileid int,filename varchar(1000), filegroup varchar(50), size varchar(30), maxsize varchar(30), growth varchar(30), usage varchar(30))

insert into #dbdetails

EXEC sp_MSforeachdb 'USE [?]; EXEC sp_helpfile;'

select * from #dbdetails

drop table #dbdetails

Tuesday, March 24, 2009

Job Status Script

select b.name,a.step_name,

c.message,

case  c.run_status when 0 then 'Failed'

when 1 then 'Succeeded'

when 2 then 'Retry'

when 3 then 'Canceled'

when 4 then 'In progress'

else 'NO STATUS' END as status

,substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),c.run_time), 6), 1, 2)

            +    ':'

            + substring (

            right (stuff (' ', 1, 1, '000000') + convert(varchar(6), c.run_time), 6) ,3 ,2)

            +    ':'

            + substring (

            right (stuff (' ', 1, 1, '000000') + convert(varchar(6),c.run_time), 6) ,5 ,2) as run_time

,substring (right (stuff (' ', 1, 1, '00000000') + convert(varchar(8),c.run_date), 8), 1, 4)

            +    '/'

            + substring (

            right (stuff (' ', 1, 1, '00000000') + convert(varchar(8), c.run_date), 8) ,5 ,2)

            +    '/'

            + substring (

            right (stuff (' ', 1, 1, '00000000') + convert(varchar(8),c.run_date), 8) ,7 ,2) as run_date

from msdb..sysjobsteps a, msdb..sysjobhistory c,msdb..sysjobs b

where a.step_id=c.step_id and a.job_id=c.job_id

and

a.last_run_date=c.run_date

and

a.last_run_time=c.run_time

and a.job_id=b.job_id

Monday, March 23, 2009

Script to Validate Subscribers for all articles and publication

declare @pubdb nvarchar(50)
declare @artobj nvarchar(100)
declare @owner nvarchar(50)
declare @params nvarchar(100)
declare @cnt1 nvarchar(50)
declare @cmd nvarchar(1000)
declare @cmd1 nvarchar(1000)
DECLARE pub_validate CURSOR FOR
select publisher_db,source_owner,source_object from distribution.dbo.MSarticles
open pub_validate
fetch next from pub_validate into @pubdb,@owner,@artobj
while @@fetch_status=0
begin
select @cmd = 'select @cnt=count(*) from '+@pubdb+'.'+@owner+'.'+@artobj
select @params=N'@cnt varchar(100) OUTPUT'
exec sp_executesql @cmd,@params,@cnt=@cnt1 output
--select @cnt1
select @cmd1='use '+@pubdb+CHAR(10)+'go'+CHAR(10)+'sp_table_validation @table='+@artobj+',@expected_rowcount='+@cnt1
select @cmd1
--exec sp_executesql @cmd1
fetch next from pub_validate into @pubdb,@owner,@artobj
end
close pub_validate
deallocate pub_validate

Cross-Domain Windows Authentication for SQL Server databases

There are many acquisitions these days and the DBA and sysadmins have a great tasks of doing a cross-domain authentication to their servers and databases. System admins have various options to connect interdomain where as DBA has to look up for it. Here is a solution that one can try using to access databases in cross-domain platform with windows authentication.
Consider one of the servers is in new york domain and Second is in boston domain. newyork domain can't talk to boston domain and vice versa..The idea is to make the windows user in newyork domain access database in boston domain using the same windows credentials. This can be achieved by following steps below.
1. Create a SQL login named sqluser in the secondary server and copy the sid of the login.
2. Create a login in the primary named sqluser with sid captured.
CREATE LOGIN sqluser with PASSWORD='sqluser1', SID = 0xB0B50710A1D1394D8E1B26CFA25AA3BE
--- copied from secondary server.
Assign the datareader permissions to primary server.
3. This user will be replicated to the secondary readonly db through log backup, being sid matches between the user and login named sqluser, it works.
It’s simple, no downtime required.
Windows Authentication: (user named newyork\sqluser)
It’s the same but we may have to work thru some tricky steps. Reason behind was that Windows domain account will have a unique SID value.
1. Create a windows login named newyork\sqluser in the secondary server. Copy the sid.
2. Create a login in the primary named sqluser with sid captured
CREATE LOGIN sqluser with PASSWORD='sqluser123', SID = 0x01050000000000051500000071416F0BD9618A1FC22E626EC6190003
it results with the below error..
Msg 15419, Level 16, State 1, Line 1
Supplied parameter sid should be binary(16).
3. Basic and logic helps here. Create a login named sqluser in the Primary with the datareader permissions.

CREATE LOGIN sqluser with PASSWORD='sqluser123'
4. Now the user named sqluser is replicated to the secondary readonly db but stands as a orphaned user. It’s not in sync with the windows login(newyork\sqluser).
5. whats the next option.. how do we bring the sid in sync now.
Use SQLCMD with DAC session on primary server
c:\> SQLCMD -A -d master
-A - options opens DAC(admin session)
SQLCMD>
Enable the allow updates option using sp_configure and update the sid of the sqluser login in sys.sysusers table with sid of windows login(newyork\sqluser)
This query results with an error because updates can be happened only when db is in single user mode with sql 2005.
C:\>sqlcmd -A
1> sp_configure 'allow updates'
2> go
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
allow updates 0 1 1 1
1> update sys.sysusers set sid = 0xB0B50710A1D1394D8E1B26CFA25AA3BE where name like 'sqluser'
2> Go
Msg 259, Level 16, State 1, Server servername, Line 1
Ad hoc updates to system catalogs are not allowed.
1>exit
sid can be captured in sys.sysxlgns table also which are similar to sysxlogins in SQL 2000. It can be viewed only when you open a session using DAC. In order to update the system tables we have to bring sql server in single user mode.

sqlservr -m -- helps you to bring sql server in single user mode.
Repeat step 5 but sql server should be started with single user mode.
C:\>sqlcmd -A
1> use demo;
2> go
Changed database context to 'Demo'.
1> update sys.sysusers set sid = 0xD67EDAA0627B944F997EB9C552DEE47A where name like 'sqluser';
2> go
(1 rows affected)
Warning: System table ID 27 has been updated directly in database ID 6 and cache coherence may not have been maintained. SQL Server should be restarted.
Now it works. recycle the SQL.Log backup of primary database will update the sid of the sys.sysusers in the secondary db. Now the windows user will have the datareader permissions on the table..
Now newyork\sqluser will have readonly access to secondary db

Wednesday, March 18, 2009

Validating Replication Subscriber using TSQL

Last post from me on SQL Server replication was on How to validate subscribers in SQL Server Replication. This article shows how the same can be done using TSQL.

sp_publication_validation:

    This system stored procedure does a validation for each and all articles in the specified publication in transactional replication. This stored procedure is executed against the publication database. The following is the syntax for this stored procedure.

sp_publication_validation [ @publication = ] 'publication'

[ , [ @rowcount_only = ] type_of_check_requested ]

[ , [ @full_or_fast = ] full_or_fast ]

[ , [ @shutdown_agent = ] shutdown_agent ]

[ , [ @publisher = ] 'publisher' ]

@publication is the name of the publication.

@rowcount_only is whether to return only the row count for the table.

    0 for perform a SQL Server 7.0 compatible check sum

    1 for perform rowcount only

    2 for perform a row count and binary check sum.

@full_or_fast is used to calculate the row count.

    0 does full count using count(*)

    1 does a count using rows column in sysindexes table. Since this table is not updated frequently this might be wrong sometimes.

    2 does a count using rows column in sysindexes table if there is a difference then it does a full count.

@shutdown_agent Specifies whether to shutdown distribution agent immediately after completing the validation.

@publisher specifies a non-sql server publisher.

You need to have sysadmin or db_owner role in the publication database to execute stored procedure.

sp_article_validation:

    This system stored procedure does a validation for each article individually in the specified publication in transactional replication. This stored procedure is executed against the publication database. The following is the syntax for this stored procedure.

sp_article_validation [ @publication = ] 'publication'

[ , [ @article = ] 'article' ]

[ , [ @rowcount_only = ] type_of_check_requested ]

[ , [ @full_or_fast = ] full_or_fast ]

[ , [ @shutdown_agent = ] shutdown_agent ]

[ , [ @subscription_level = ] subscription_level ]

[ , [ @reserved = ] reserved ]

[ , [ @publisher = ] 'publisher' ]

@publication is the name of the publication in which the article exists.

@article is the name of the article to validate.

@rowcount_only specifies if only the rowcount for the table is returned.

    0 for perform a rowcount and a Microsoft SQL Server 7.0 compatible checksum.

    1 for perform a rowcount check only.

    2 for perform a rowcount and binary checksum.

@full_or_fast is the method used to calculate the rowcount.

    0 does full count using count(*)

    1 does a count using rows column in sysindexes table. Since this table is not updated frequently this might be wrong sometimes.

    2 does a count using rows column in sysindexes table if there is a difference then it does a full count.

@shutdown_agent Specifies whether to shutdown distribution agent immediately after completing the validation.

@subscription_level specifies whether or not the validation is picked up by a set of subscribers. If 0,    validation is applied to all Subscribers. If 1, validation is only applied to a subset of the Subscribers specified by calls to    sp_marksubscriptionvalidation in the current open transaction.

@reserved identified for informational purposes only.

@publisher specifies a non-Microsoft SQL Server Publisher.

You need to have SELECT ALL permissions on the source table for the article being validated

sp_table_validation:

    This system stored procedure does a validation for each table individually in the specified publication in transactional replication. This stored procedure is executed against the publication database. The following is the syntax for this stored procedure.

sp_table_validation [ @table = ] 'table'

[ , [ @expected_rowcount = ] type_of_check_requested OUTPUT]

[ , [ @expected_checksum = ] expected_checksum OUTPUT]

[ , [ @rowcount_only = ] rowcount_only ]

[ , [ @owner = ] 'owner' ]

[ , [ @full_or_fast = ] full_or_fast ]

[ , [ @shutdown_agent = ] shutdown_agent ]

[ , [ @table_name = ] table_name ]

[ , [ @column_list = ] 'column_list' ]

@table is the name of the table to be validated.

@expected_rowcount specifies whether to return the expected number of rows in the table. If NULL, the actual rowcount is returned as an output parameter. If a value is provided, that value is checked against the actual rowcount to identify any differences.

@expected_checksum specifies whether to return the expected checksum for the table. If NULL, the actual checksum is returned as an output parameter. If a value is provided, that value is checked against the actual checksum to identify any differences.

@rowcount_only specifies what type of checksum or rowcount to perform.

    0 for perform a rowcount and a Microsoft SQL Server 7.0 compatible checksum.

    1 for perform a rowcount check only.

    2 for perform a rowcount and binary checksum.

@owner is the name of the owner of the table.

@full_or_fast is the method used to calculate the rowcount.

    0 does full count using count(*)

    1 does a count using rows column in sysindexes table. Since this table is not updated frequently this might be wrong sometimes.

    2 does a count using rows column in sysindexes table if there is a difference then it does a full count.

@shutdown_agent Specifies whether to shutdown distribution agent immediately after completing the validation.

@table_name is the table name of the view used for output messages.

@column_list is the list of columns that should be used in the checksum function.

Monday, March 16, 2009

My Interview in MVP Summit

Mike Nash from c-sharpconsulting interviewed me for a short duration of 2 minutes during the MVP summit. The link for the video is given below.

http://www.c-sharpcorner.com/UploadFile/mahesh/Vs03122009175026PM/Vs.aspx

How to validate subscribers in SQL Server Replication

    There have been many SQL Server DBAs who would like to compare the row count between tables in replication Publisher and Subscriber. SQL Server provides an easy way of doing this and is known as validation subscribers in replication which involves doing a row count between the table that is published and the ones that are subscribed to it.

    To Validate subscribers, you need to do the following.

  1. Launch replication monitor.
  2. Right click the publisher for which you want to validate the row count and click validate subscriptions.
  3. Choose the subscribers that you want to validate, by default it chooses all subscribers for that publication.
  4. You will be provided with the validation options.
  5. Select the validation option that best suits you. It is advisable to choose, "Compute a fast row count: If differences are found, compute an actual row count"
  6. Click ok.
  7. Click ok.

    Now you will be able to see the row count match are mismatch between the publisher and the subscriber, when you double click the subscriber. The next article would be how to perform the same validation using TSQL.

Sunday, March 15, 2009

tablediff utility

            Tablediff is an inbuilt SQL Server utility that is used to compare data between two tables across same or different servers. This utility is invoked from command prompt and is usually found in C:\Program Files\Microsoft SQL Server\100\COM folder. The best use of this utility is exhibited when you use it to compare tables that are involved in replication. The following tasks can be performed with the utility.

  • A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.
  • Perform a fast comparison by only comparing row counts and schema. 
  • Perform column-level comparisons. 
  • Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence. 
  • Log results to an output file or into a table in the destination database.

Tablediff has various arguments that can be passed to it to get the desired results.

The parameters are discussed as follows.

-sourceserver source_server_name[\instance_name]

The name of the source server. You need to specify the source instance name to this argument.

-sourcedatabase source_database

The name of the source database.

-sourcetable source_table_name

The name of the source table being compared.

-sourceschema source_schema_name

The schema owner of the source table. By default, the table owner is dbo.

-sourcepassword source_password

The password for the login used to connect to the source server using SQL Server Authentication.

-sourceuser source_login

The login used to connect to the source server using SQL Server Authentication. If source_login is not supplied, the utility uses Windows Authentication to connect to the source server. Whenever possible, use Windows Authentication.

-sourcelocked

The source table is locked during the comparison using the TABLOCK and HOLDLOCK table hints.

-destinationserver destination_server_name[\instance_name]

The name of the destination server. You need to specify the source instance name to this argument.

-destinationdatabase subscription_database

The name of the destination database.

-destinationtable destination_table

The name of the destination table.

-destinationschema destination_schema_name

The schema owner of the destination table. By default, the table owner is dbo.

-destinationpassword destination_password

The password for the login used to connect to the destination server using SQL Server Authentication.

-destinationuser destination_login

The login used to connect to the destination server using SQL Server Authentication. If destination_login is not supplied, the utility uses Windows Authentication to the server. When possible, use Windows Authentication.

-destinationlocked

The destination table is locked during the comparison using the TABLOCK and HOLDLOCK table hints.

-b large_object_bytes

            The number of bytes to compare for large object data type columns, which includes: text, ntext, image, varchar(max), nvarchar(max) andvarbinary(max). large_object_bytes defaults to the size of the column. Any data above large_object_bytes will not be compared.

-bf number_of_statements

The number of Transact-SQL statements to write to the current Transact-SQL script file when the -f option is used. When the number of Transact-SQL statements exceeds number_of_statements, a new Transact-SQL script file is created.

-c

Compare column-level differences.

-dt

Drop the result table specified by table_name, if the table already exists.

-et table_name

Specifies the name of the result table to create. If this table already exists, -DT must be used or the operation will fail.

-f [ file_name ]

Generates a Transact-SQL script to synchronize table that are being compared. You can optionally specify a name and path for the generated Transact-SQL script file.

-o output_file_name

The full name and path of the output file.

-q

Perform a fast comparison by only comparing row counts and schema.

-rc number_of_retries

Number of times that the utility retries a failed operation.

-ri retry_interval

Interval, in seconds, to wait between retries.

-strict

Source and destination schema are strictly compared.

-t connection_timeouts

Is the connection timeout period, in seconds, for connections to the source server and destination server.

Restrictions:

            The source table in the comparison must contain at least one primary key, identity, or ROWGUID column. When you use the -strict option, the destination table must also have a primary key, identity, or ROWGUID column.

The Transact-SQL script generated to bring the destination table into convergence does not include the following data types:

  • varchar(max) 
  • nvarchar(max) 
  • varbinary(max) 
  • timestamp 
  • xml 
  • text 
  • ntext 
  • image 

Permissions Needed:

1.      You need select permissions on the tables being compared.

2.     To use the -et option, you must be a member of the db_owner fixed database role, or at least have CREATE TABLE permission in the subscription database and ALTER permission on the destination owner schema at the destination server.

3.     To use the -dt option, you must be a member of the db_owner fixed database role, or at least have ALTER permission on the destination owner schema at the destination server.

4.     To use the -o or -f options, you must have write permissions to the specified file directory location.

Thursday, March 5, 2009

Disabling Windows Authentication in SQL Server????????

I have seen lot of people asking in forums "Can I disable windows authentication in SQL Server and have only SQL Authentication?". The obvious answer that he/she is going to get is "NO". For SQL server experts, they know the reason why it cannot be disabled but when a person asks this kind of question we have to understand that he/she is in infact stage of learning SQL Server. The reason why Windows Authentication cannot be disabled is that SQL Server runs as a SERVICE in Windows Operating System and the service runs under a Windows Local/Domain account.

Monday, March 2, 2009

MVP Summit 2009 Photos

I have posted the few photos that we took during the MVP Summit in my picasa. Please have a look of those photos.

MVP Summit 2009

This is my first MVP summit and it has been till now the most exciting event that i have attended on my professional journey. The event started with the registration, went on with keynotes from Toby Richards, Rich Kaplan from Microsoft and then a great networking with fellow MVPs around the globe. I have not dreamt of meeting people like Steve Jones, Andy Warren, Itzik Ben and have a discussion on the SQL Server and activities that we do to make it a great technology.
The scecond day of the event is now starting with the sessions on SQL Server and I am excited in attending all the session on SQL Server. Let me keep you updated as the event goes on. Happy Reading.