SQL Server

• Why it is important to re-create a database as it was originally created if you have to restore it from backup. (6.5, 6.0, 4.2 - not 7.0)
• EMail - Stored Proc to notify you via e-mail every time the SQL Server is started. Schedule it to run by executing 'sp_makestartup sp_emailaboutstartup'
• Standby SQL Server help.
• Let SQL Server add new login ids by itself. Administrator Helper!!!
• Upgrade from 6.0 to 6.5 Log. A diary of how things went for me one time.
• This script helps you to create devices and databases

create proc sp_emailaboutstartup
as
declare @messgg varchar(80)
select @messgg = @@servername + ' started on ' + convert(varchar(30),getdate(),109)
exec xp_sendmail @recipients='youremailaddress',@subject=@messgg

• Device Usage - run this to get usage information about your devices

select Dbase = db_name(dbid),MB=size/512,DeviceName=dv.name,dbid,segmap,lstart,vstart
from sysusages us, sysdevices dv
where us.vstart >= dv.low and us.vstart < dv.high
and status <> 16
order by dbid, lstart

• Use this stored procedure to find duplicate rows in your tables

create proc sp_finddups
@colnames char(40),
@tablename char(30)
as
declare @fullsql char(255)
select @fullsql = 'select ' + @colnames +
', NumOfDups = count(*) from ' + @tablename +
' group by ' + @colnames +
' having count(*) > 1'
exec (@fullsql)
go
exec sp_finddups 'au_id', 'pubs..authors'

• Use this stored procedure to list all of your primary key/foreign key relationships

select object_name(fkeyid), co.name,
object_name(rkeyid), co2.name
from sysreferences re,
syscolumns co,
syscolumns co2
where re.fkeyid = co.id and re.fkey1 = co.colid
and re.rkeyid = co2.id and re.rkey1 = co2.colid

• I recommend running the following every time you change a DB or device and saving it to a file:

set nocount on
use master
exec sp_configure 'show advanced options',1
reconfigure
go
select 'Configuration of Server Named: ' +@@servername+ ' on '
+ convert(char(10),getdate(),101) + ' ' +convert(char(12),getdate(),114)
exec sp_configure
go
select * from sysdevices
select * from sysusages
select * from sysdatabases

• Finally, here is my regular maintenance checklist:

Every Time a configuration option is changed on SQL Server:
run sp_configure and save the results to
\\e$\sql\<servername>\srvrcfg.rpt

Every Time a database device is created or made larger
or every time a database or log size is changed/created:
run select * from sysusages
select * from sysdevices
select * from sysdatabases
and save results to
\\e$\sql\<servername>\devusgdb.rpt
also alter the script in the following
\\e$\sql\<servername>\CreateDB.sql

Every time a db object is created or altered generate sql scripts
for the database of everything(except logins) and save to
\\e$\sql\<servername>\<dbname>.sql

Daily Maintenance Tasks:
<Production Primary Machine>
Backup all application transaction logs, then databases
Backup Master, then MSDB
Run DBCC CheckCatalog on all application databases
Run DBCC CheckCatalog on all master and msdb
Run DBCC NewAlloc on all application databases
Run DBCC NewAlloc on master and msdb
Run DBCC CheckDB on master and msdb
Copy DumpData .Dat files after backup to Standby Server
<Standby Secondary Machine>
Backup Master, then MSDB
Run DBCC CheckCatalog on all master and msdb
Restore application databases from Primary backups
Run DBCC NewAlloc on all application databases after LOAD
Run DBCC NewAlloc on master and msdb
Run DBCC CheckDB on master and msdb
Weekly Maintenance Tasks:
<Production Primary Machine>
Run DBCC CheckDB on all application databases
Drop and recreate indexes on application databases(on segments)