dbcc traceon/traceoff

dbcc traceon(302) redirects output to client rather than logs.  dbcc traceon(302) is often used in conjunction with dbcc traceon(310), which provides more detail on the optimizer’s join order decisions and final cost estimates. dbcc traceon(310) also prints a “Final plan” block at the end of query optimization. To enable this trace option also, use:

dbcc traceon(3604, 302, 310)

To turn off the output, use:

dbcc traceoff(3604, 302, 310)

DBCC CHECKDB()

exec sp_MSforeachDB 'DBCC CHECKDB (?) WITH ALL_ERRORMSGS, NO_INFOMSGS'

Connect to SQL Server When System Administrators Are Locked Out

http://msdn.microsoft.com/en-us/library/dd207004(v=sql.105).aspx


How to: Change Server Authentication Mode

http://msdn.microsoft.com/en-us/library/ms188670(v=SQL.105).aspx


these articles came in handy today...

Rowcount all tables

declare @tabs TABLE ( id int identity(1,1), tabname sysname)
declare @res TABLE ( tabname sysname, numrows int )

declare @id int, @numrows int, @tabname sysname, @sql nvarchar(4000)

insert into @tabs (tabname)
select name from sysobjects where type = 'U' order by 1 asc

select @id = max(id) from @tabs

while @id > 0
 begin
   select @tabname = tabname from @tabs where id = @id
   set @sql = 'select ''' + @tabname + ''' "tabname", count(1) "numrows" from [' + @tabname + ']'

   insert into @res ( tabname, numrows )
   exec sp_executesql @sql

   set @id = @id - 1
 end

 select tabname, numrows from @res where numrows > 0 order by tabname

remap db user to server login

When you restore a 2005+ database from one instance to another, the database users are preserved in the database, but now they are linked to the GUID of the login on the originating instance.   The login may exist by the same name on the target server, but since the principal GUIDs are different the user is not linked to that login.  One possibility is to drop and recreate the user and/or login, but this is very destructive in the case that there are complex permissions involved for the user in the DB.

   DECLARE @tab TABLE ( id int identity(1,1), uname sysname )

   DECLARE @id int
         , @uname sysname
         , @sql nvarchar(4000)

     INSERT INTO @tab ( uname )
          SELECT name
            FROM sysusers 
           WHERE issqluser = 1 
             AND hasdbaccess = 1 
             AND name != 'dbo'

          SELECT @id = max(id) from @tab

   WHILE @id > 0
    BEGIN
          SELECT @uname = uname 
            FROM @tab 
           WHERE id = @id

      EXEC sp_change_users_login @action = 'Update_One', @UserNamePattern = @uname, @LoginName = @uname

      SET @id = @id - 1
    END

 Used to use sp_change_users_login, but since that is now deprecated, it is recommended to use the following:

alter user [joeuser] with login = [joeuser]

Truncation error in job

Had a real winner this week, had some stored procs that would run fine in SSMS, but would return error 8152 String or binary  data would be truncated when run as a job.
Turns out the table had a field for updated by that was defaulted to suser_name() but was only 20 chars long.   The user name the job ran as was almost 40 chars.
Wont say how much time was spent tracking this one down :),

Error: The maximum string content length quota (8192) has been exceeded while reading XML data. Solution: Adjust buffer, message, and string content length in client's app config or web config.

<configuration>

...

 <system .servicemodel=".servicemodel">
  <bindings>
    <basichttpbinding>
       <binding ...="..." maxbuffersize="20481000" maxreceivedmessagesize="20481000" name="WSSoap">
         <readerquotas ...="..." maxstringcontentlength="20481000">
         </readerquotas>
       </binding>
    </basichttpbinding>
  </bindings>
 </system>
</configuration>