CHECKDB REPAIR_REBUILD

use master
go

DBCC CHECKDB (thedb) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO

ALTER DATABASE thedb
   SET AUTO_UPDATE_STATISTICS_ASYNC OFF;
GO

ALTER DATABASE thedb
   SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

DBCC CHECKDB (thedb, REPAIR_REBUILD) WITH ALL_ERRORMSGS;
GO

--DBCC CHECKDB (thedb, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
--GO

ALTER DATABASE thedb
   SET MULTI_USER;
GO

CHECKPOINT;

logical page errors

--Msg 605, Level 21, State 3, Line 13
--Attempt to fetch logical page (1:3418) in database 16 failed. It belongs to allocation unit 25896092997713920 not to 72057594982891520.


dbcc traceon(3604) --3604 = redirect error output to client instead of log
dbcc page(16,1,3418,1)  --dbid, fileid, pageid, level 0-3
dbcc traceoff(3604)

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]