space used by tables

;WITH
space_cte AS
(
          SELECT t.NAME "TableName"
               , p.rows "RowCounts"
               , SUM(a.total_pages) * 8 "TotalSpaceKB"
               , SUM(a.used_pages) * 8 "UsedSpaceKB"
               , (SUM(a.total_pages) - SUM(a.used_pages)) * 8 "UnusedSpaceKB"
            FROM sys.tables t
      INNER JOIN sys.indexes i 
              ON t.OBJECT_ID = i.object_id
      INNER JOIN sys.partitions p 
              ON i.object_id = p.OBJECT_ID 
             AND i.index_id = p.index_id
      INNER JOIN sys.allocation_units a 
              ON p.partition_id = a.container_id
           WHERE t.NAME NOT LIKE 'dt%' 
             AND t.is_ms_shipped = 0
             AND i.OBJECT_ID > 255 
        GROUP BY t.Name
               , p.Rows
)
          SELECT A.TableName
               , A.RowCounts
               , A.TotalSpaceKB
               , ( case when A.UsedSpaceKB > 1000000 then cast(A.UsedSpaceKB / 1000000 As varchar(30)) + ' GB'
                        when A.UsedSpaceKB > 1000 then cast(A.UsedSpaceKB / 1000 As varchar(30)) + ' MB'
                        else cast(A.UsedSpaceKB as varchar(30)) + ' KB' end ) "UsedSpace"
               , A.UnusedSpaceKB
            FROM space_cte A
        ORDER BY A.UsedSpaceKB desc

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