Author: sqldba

To move a table from one filegroup to another while the system is “live” and “online”, recreate its clustered index…  Be sure the target filegroup is already sized to handle the incoming data 🙂

ALTER DATABASE databaseName
MODIFY FILE
    (NAME = datafileLogicalName,
    SIZE = 2000MB);
GO

CREATE CLUSTERED INDEX [i_tableName_cu] ON [dbo].[tableName] 
(
[columName] ASC
)
WITH DROP_EXISTING
ON [filegroupName]
GO

To consolidate database files:

DBCC SHRINKFILE(‘logicalFileName’, EMPTYFILE);
ALTER DATABASE databaseName REMOVE FILE logicalFileName;

ALTER FUNCTION [dbo].[fnGetVal]( @s varchar(8000), @label varchar(1000) )

RETURNS varchar(1000)

AS

BEGIN

   DECLARE @value varchar(1000)

         , @value_start int

         , @value_end int
   SET @value_start = charindex(@label,@s)
   IF @value_start > 0

    BEGIN

      SET @value_start += len(@label) + 3

      SET @value_end = charindex(‘,’,@s,@value_start)
      IF @value_end > @value_start

       BEGIN

         SET @value = substring(@s,@value_start,@value_end-@value_start)

       END

      ELSE

       BEGIN

         SET @value = substring(@s,@value_start,len(@s)-@value_start+1)

       END

    END
 RETURN ltrim(rtrim(@value))

END

a good tool will make something you already do, easier

never get a tool expecting that the tool will make you do something you are not already doing…

it is similar to exercise equipment…

if you are not already doing push-ups, sit-ups, and jogging, a gym membership or purchasing gym equipment will not make you start exercising…

;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

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;

–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(3604, 302, 310)

dbcc traceoff(3604, 302, 310)

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…