MSSQL Maintanance stuff

[toast2250]

Backup all databases individually to path *.bak

Spoiler

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

 
-- specify database backup directory
SET @path = 'C:\Backup\'  

 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

 
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

 
WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  
       WITH CHECKSUM

 
       FETCH NEXT FROM db_cursor INTO @name   
END   

 
CLOSE db_cursor   
DEALLOCATE db_cursor

Delete Accounts that has no character created after 1 week of registration
http://forum.ragezone.com/f451/a-746094/#post6300946

Spoiler

/* If the account has no LoginTime created and is older than 1 week after registration it will be deleted */
USE [Account]
DECLARE @DateTreshold datetime;
SELECT @DateTreshold = DATEADD(ww, -1, GETDATE());

DELETE
FROM [dbo].[cabal_auth_table]
WHERE
[Login] = 0

AND [CreateDate] < @DateTreshold
AND [LoginTime] IS NULL
GO

To view and clear the SQL Server error log

Spoiler

In Object Explorer, expand a server, expand Management, and then expand SQL Server Logs.

Right-click a log and click View SQL Server Log.

[I]sp_cycle_errorlog[/I]

Shrink the database logs and set recovery model to simple, newer shrink the game DB!

Spoiler

USE Account
GO
ALTER DATABASE Account SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE('Account_log', EMPTYFILE)
GO

USE Authentication
GO
ALTER DATABASE Authentication SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE('Authentication_log', EMPTYFILE)
GO

USE CabalCash
GO
ALTER DATABASE CabalCash SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE('CabalCash_log', EMPTYFILE)
GO

USE CabalGuild
GO
ALTER DATABASE CabalGuild SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE('CabalGuild_log', EMPTYFILE)
GO

USE cabalmanager
GO
ALTER DATABASE cabalmanager SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE('cabal_managerDB_log', EMPTYFILE)
GO

USE EventData
GO
ALTER DATABASE EventData SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE('EventData_log', EMPTYFILE)
GO

USE NetcafeBilling
GO
ALTER DATABASE NetcafeBilling SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE('NetcafeBilling_log', EMPTYFILE)
GO

USE Server01
GO
ALTER DATABASE Server01 SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE('Server02_log', EMPTYFILE)

Show DB fragmentation

Spoiler

-- be safe for sys.indexes although BOL says "sys.dm_db_index_physical_stats requires only an Intent-Shared (IS)"
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

-- if object_id('tempdb.dbo.#IDXFRAG') is not null drop TABLE #IDXFRAG
if object_id('tempdb.dbo.#IDXFRAG') is not null
DROP table tempdb.dbo.#IDXFRAG

    CREATE TABLE #IDXFRAG
    (	DbName                          sysname,
        ObjName                         sysname,
        IdxName                         sysname     NULL,
    -- columns below are exactly as generated by SYS.DM_DB_INDEX_PHYSICAL_STATS function
        database_id                     smallint,
	    [object_id]                     int,
	    index_id                        int,
	    partition_number                int,
	    index_type_desc                 nvarchar(60) COLLATE Latin1_General_CI_AI NULL,
	    alloc_unit_type_desc            nvarchar(60) COLLATE Latin1_General_CI_AI NULL,
	    index_depth                     tinyint NULL,
	    index_level                     tinyint NULL,
	    avg_fragmentation_in_percent    float NULL,
	    fragment_count                  bigint NULL,
	    avg_fragment_size_in_pages      float NULL,
	    page_count                      bigint NULL,
	    avg_page_space_used_in_percent  float NULL,
	    record_count                    bigint NULL,
	    ghost_record_count              bigint NULL,
	    version_ghost_record_count      bigint NULL,
	    min_record_size_in_bytes        int NULL,
	    max_record_size_in_bytes        int NULL,
	    avg_record_size_in_bytes        float NULL,
	    forwarded_record_count          bigint NULL
   -- ,   primary key nonclustered (DbName, ObjName,[object_id],index_id,partition_number)   -- IdxName is NULL if HEAP (index_id=0)
    )
GO

IF NOT EXISTS (SELECT * FROM tempdb.sys.indexes WHERE object_id = OBJECT_ID(N'tempdb..#IDXFRAG') AND name = N'IDXFRAG_CI')
    create index IDXFRAG_CI on #IDXFRAG (DbName,ObjName,IdxName)

-- tip: you can re-run in SSMS for different db's by 
declare tblcur cursor for
    select  object_id, TblName=schema_name(schema_id)+'.'+name
    from    sys.tables where type = 'U'
    order by TblName 

declare @dbid smallint, @objid int, @TblName nvarchar(257)
select @dbid=db_id()    --, @objid=OBJECT_ID('SEDOL_PRICE')
--select @dbid, @objid
open tblcur
fetch next from tblcur into @objid, @TblName
while @@fetch_status=0
 begin
 -- print @TblName        -- DEBUG only
    delete from #IDXFRAG where DbName=db_name() and [object_id]=@objid      -- allow re-runs and multiple db's
    insert into #IDXFRAG
        select  DbName=db_name(), ObjName=@TblName, IdxName=I.name,
                database_id,s.[object_id],s.index_id, partition_number,
	            index_type_desc,alloc_unit_type_desc,
	            index_depth,index_level,
	            avg_fragmentation_in_percent,fragment_count,
	            avg_fragment_size_in_pages,page_count,
	            avg_page_space_used_in_percent,record_count,
	            ghost_record_count,	version_ghost_record_count,
	            min_record_size_in_bytes,max_record_size_in_bytes,
	            avg_record_size_in_bytes,forwarded_record_count 
        from    SYS.DM_DB_INDEX_PHYSICAL_STATS(@dbid,@objid,NULL,NULL,'SAMPLED')    S
        join    sys.indexes                                                         I on I.object_id= @objid and I.index_id=S.index_id 
        --where   S.avg_fragmentation_in_percent > 20 AND S.page_count > 8 
          
    fetch next from tblcur into @objid, @TblName
 end
close tblcur
deallocate tblcur
go

select  DbName, ObjName,IdxName, index_id, partition_number,index_type_desc, index_depth, index_level, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count, avg_page_space_used_in_percent, record_count, forwarded_record_count    --, ghost_record_count, version_ghost_record_count, min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes
from    #IDXFRAG
order by DbName,ObjName,IdxName    -- IDXFRAG_CI
go

Defrag all DB and tables

Spoiler

DECLARE @Database VARCHAR(255)  
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR  
SELECT name FROM MASTER.dbo.sysdatabases  
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')  
ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  
WHILE @@FETCH_STATUS = 0  
BEGIN  

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
  table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES
  WHERE table_type = ''BASE TABLE'''  

   -- create table cursor  
   EXEC (@cmd)  
   OPEN TableCursor  

   FETCH NEXT FROM TableCursor INTO @Table  
   WHILE @@FETCH_STATUS = 0  
   BEGIN  

       IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
       BEGIN
           -- SQL 2005 or higher command
           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
           EXEC (@cmd)
       END
       ELSE
       BEGIN
          -- SQL 2000 command
          DBCC DBREINDEX(@Table,' ',@fillfactor)  
       END

       FETCH NEXT FROM TableCursor INTO @Table  
   END  

   CLOSE TableCursor  
   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  
END  
CLOSE DatabaseCursor  
DEALLOCATE DatabaseCursor

NOTE: Stop the cabal server completely and restart the mssql service before editing anything. Never shrink the game DB except the logs!

Comments, improvements, feedback is welcome.

Credits: Google, xXxAxXx, Alphakillo23

 

[PwrDex]

I have never seen this topic, but now i realized how this useful topic! I recommend on every server which have 5k+ accounts. Very useful!