Schedule Sql Server Restart

Job Step for Restarting Server >


(schedule as required)

Setting a stored procedure to run on server startup

exec sp_procoption N'spTest', 'startup', 'on'

TSQL : Searching Job Steps

Searches steps of jobs defined on server.
Use to locate sql code performing a certain function.

use msdb
go

select sysjobs.name, sysjobsteps.command
from sysjobs
inner join sysjobsteps
on sysjobs.job_id = sysjobsteps.job_id
where command like '%searchstring%'
order by name

Reports In Management Studio 2005

Reports In Management Studio 2005

Support
No support for SQL 2000 instances
No support for databases prior to Compatibility Mode 90 (Restores of SQL 2000 databases on SQL 2005)

Report Types
Standard Reports - Inbuilt, see screenshots below.
Custom Reports (SP2 onwards) - Run a .RDL file (Reporting Services Report) within the context of the current server in Management St

Accessing Reports
Right click a node in Object Explorer to get Reports Menu
Standard Reports, Custom Reports and shortcuts to the most recently accessed reports are the options.

The list of available Reports is context sensitive e.g 'Service Broker Statistics' only appears when selecting reports from the 'Service Broker' node.

Standard Reports

17 Database Reports >

Navigating to Reports : (Databases > [database name] > )


Disk Usage :


Disk Usage by Top Tables :


Disk Usage by Table :


Disk Usage by Partition :


Backup and Restore Events :



All Transactions :


Top Transactions by Age :


All Blocking Transactions :


Top Transactions by Age :


Top Transactions by Blocked Transactions Count :


Top Transactions by Locks Count :


Resource Locking Statistics by Objects :


Object Execute Statistics :


Database Consistency history :


Index Usage Statistics :


Index Physical Statistics :


Schema Change history :


User Statistics :


Databases > [database name] > Service Broker Node >

Service Broker Statistics :


Server Reports >


Security > Logins >

Login Statistics :


Login Failures :


Resource Locking Statistics by login :


Management >

Tasks :


Number of Errors :



Notification Services >

General :


SQL Server Agent >

Job Steps Execution History :


Top Jobs :

TSQL : Negate Time from DateTime column

update! doesn't incur an implicit conversion :

SELECT DATEADD(dd, DATEDIFF(dd, '1900-01-01', GETDATE()),'1900-01-01')

update! more efficient :

SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)

original version :

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

SQL 2000 ; Replication Clearup Script

Written by a colleague, this removes unnecessary constraints and columns when recovering a database previously replicated -

DECLARE @TabName varchar(255)
DECLARE @ConstName varchar(255)
DECLARE @SQL nvarchar(255)
DECLARE @Cnt as int
DECLARE @DBNAME as varchar(255)

SET @DBNAME = ''

use (databasename)

DECLARE Const_cursor CURSOR FOR



SELECT O_Table.[Name],
 O_Const.[Name]

FROM sysConstraints C

INNER JOIN sysObjects O_Table ON O_Table.[ID] = C.[ID]
INNER JOIN sysobjects O_Const ON O_Const.[ID] = C.ConstID

WHERE O_Const.[Name] LIKE 'DF__' + LEFT(O_Table.Name,9) + '__msrep__%'

ORDER BY 1,2

SET @Cnt = 0

OPEN Const_cursor

FETCH NEXT FROM Const_cursor INTO @TabName, @ConstName

WHILE @@FETCH_STATUS = 0
  BEGIN
PRINT 'Processing ' + @TabName
SET @Cnt = @Cnt + 1

--First we need to drop the constraint
SELECT @SQL = 'ALTER TABLE ' + @DBNAME + @TabName + ' DROP CONSTRAINT ' + @ConstName
exec sp_executesql @SQL
PRINT @SQL

--Now drop the unneeded column
SELECT @SQL = 'ALTER TABLE ' + @DBNAME + @TabName + ' DROP COLUMN msrepl_tran_version'
exec sp_executesql @SQL
PRINT @SQL

FETCH NEXT FROM Const_cursor INTO @TabName, @ConstName
  END

PRINT ''
PRINT cast(@Cnt as varchar(3)) + ' Tables Processed'

CLOSE Const_cursor
DEALLOCATE Const_cursor

GO

USP_FixUsers :: Fix All Orphanned Users

Reconnect matching user ids after restoring across servers

CREATE PROCEDURE dbo.USP_FixUsers
AS
/*
Based on the widely available sp_fixusers
*/
BEGIN
DECLARE @username varchar(25)
DECLARE fixusers CURSOR 
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END
CLOSE fixusers
DEALLOCATE fixusers
END
GO

SQL connectivity issues

Steps to troubleshoot SQL connectivity issues ! NEW 30/04/08 !
http://blogs.msdn.com/sql_protocols/archive/2008/04/30/steps-to-troubleshoot-connectivity-issues.aspx

Troubleshoot Connectivity Issues in SQL Server 2005 - Part I
http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

Troubleshoot Connectivity Issues in SQL Server 2005 - Part II
http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

Troubleshoot Connectivity Issues in SQL Server 2005 - Part III
http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx

Outlook 2003+ : Customizing Startup Folder

Commands to start Outlook up with various views -

START /MAX '' 'C:\Program files\Microsoft Office\OFFICE11\OUTLOOK.EXE'
START /MAX '' 'C:\Program files\Microsoft Office\OFFICE11\OUTLOOK.EXE' /select outlook:tasks
START /MAX '' 'C:\Program files\Microsoft Office\OFFICE11\OUTLOOK.EXE' /select outlook:calendar
START /MAX '' 'C:\Program files\Microsoft Office\OFFICE11\OUTLOOK.EXE' /select outlook:contacts
START /MAX '' 'C:\Program files\Microsoft Office\OFFICE11\OUTLOOK.EXE' /select outlook:notes

Put your preferred options in a batch file to use multiple instances of outook (useful as can switch between outlook functionality on the grouped taskbar in xp).

Security : Chaos Generator

Chaos Generator. A free random password generator tool.



Download from http://safechaos.com/

Dynamic SQL to set all databases to Simple recovery mode

-- Dynamic SQL to set all databases to Simple recovery mode

-- set all to simple
-- use this sql to generate to sql to perform this task.

use master
SELECT 'ALTER DATABASE [' + name + '] SET RECOVERY SIMPLE' from master..sysdatabases where name not in ('master','model','msdb','tempdb')
 

Good Practice - Procedure Commenting

Demonstrates use of XML style comments block -

CREATE PROCEDURE [dbo].[USP_ProcedureName]
/* 

  
   USP_ProcedureName
  
  
   Stored Procedure
  
  
   What the procedure does.
  
  
   
    1
    05/05/2006
    Joe Bloggs
    Original Version
   

   
    2
    23/06/2006
    Fred Bloggs
    Changed some code
   


   
    3
    01/08/2006
    Joe Bloggs
    Changed some more code
   


*/
AS
BEGIN
-- Do stuff here!!!
END