Transcript Database Mirroring: Maximizing Availability Through Automation

Do as I Say, Not as I Do
Robert L Davis
Who am I?
No one of consequence.
Time is short!
Please hold all
questions until the next
person’s session.
xp_createsubdir
Exec xp_createsubdir 'c:\bak';
•
•
•
•
Creates the specified directory path
Doesn’t overwrite existing directories or files
No error if directories already exist
Error only returned if insufficient permissions
xp_fileexist
Exec xp_fileexist 'c:\bak';
Exec xp_fileexist 'c:\bak\db.bak';
• Checks for existence of file or folder
• File Exists = 1 if a file and exists
• File is a Directory = 1 if a folder and exists
xp_instance_regxxx
xp_instance_regread;
xp_instance_regenumvalues;
xp_instance_regenumkeys;
xp_instance_regwrite;
xp_instance_regaddmultistring;
xp_instance_regremovemultistring;
xp_instance_regdeletevalue;
xp_instance_regdeletekey;
Declare
@BakDir nvarchar(4000),
@DefBakDir nvarchar(4000);
Set @Instance = IsNull('\' + Cast(ServerProperty('InstanceName') as sysname), '');
Set @DefBakDir = 'E:\mssql\bak' + @Instance;
-- Check default backup location
Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory',
@BakDir output,
'no_output‘;
-- If default setting null or <> to desired path, set
If IsNull(@BakDir, '') <> @DefBakDir
Begin
Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory',
REG_SZ,
@DefBakDir;
End
-- Make sure path exists (DUH!)
Exec xp_create_subdir @BakDir;
xp_enumerrorlogs
xp_readerrorlogs [#]
xp_dirtree 'c:'
xp_subdirs 'c:\bak'
xp_fixeddrives
Trace flag 3604
DBCC TraceOn(3604);
• Redirects output from the error log to
the console
• Use with DBCC PAGE
DBCC Ind()
DBCC Ind ('DB', 'Table', Index ID);
• Returns internal info about an index
DBCC Ind
('AdventureWorksDW2008R2',
'DimAccount',
1);
DBCC Page()
DBCC Page ('DB', File #, Page #, Option #);
• Returns internal info about a page
• Use with trace flag 3604
• Use with DBCC Ind
• PageFID = File number
• PagePID = Page #
DBCC Page
('AdventureWorksDW2008R2', 1, 3458, 3);
DBCC Page ('DB', File #, Page #, Option #);
DBCC TraceOn (3604);
-- PageFID = File Number, PagePID = Page Number
DBCC Page ('AdventureWorksDW2008R2', 1, 3458, 3) ;
Do as I Say, Not as I Do
Q&A
Do as I Say, Not as I Do
Thank You!
The PowerPoint slide-deck will be available on
my website:
http://www.sqlsoldier.com
Microsoft Certified Master