Database With Fun (While 1 = 1)

When executing this script, it will create the stored procedure: usp_DiskSpaceAlert Now you can create little jobs to check the available diskspace for 1 drive (so by adding more steps in the job, you can check all disks depending other parameters). Variables: - MinMBFree = triggeramount of free disk space - Drive = diskdrive to check - RCPT = emailaddresses of recipients For example: EXEC dbo.usp_DiskSpaceAlert @MinMBFree= 3072, @Drive='C', @RCPT=' someone@mail.com ' GO USE [master]GO/****** Object: StoredProcedure [dbo].[usp_DiskSpaceAlert] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[usp_DiskSpaceAlert] @MinMBFree int, @Drive char(1), @RCPT VARCHAR(500) AS/*****************************************************    Object: dbo.usp_DiskSpaceAlert (DiskSpaceAlert per diskdrive)*    Dependent Objects: master.sys.xp_fixeddrives*    Version: 1.0*    Script Date: 3/10/2011*    Author: Sven Goossens*    Purpose: Validat...

Part 1:- Configure the Distributor, Publisher and enable the database to synchronize from backup ( to make sure I would not need to generate a full snapshot to initialize, as the business requirement was to do this task with least overhead on the live systems) Create an empty publication with no articles. (again I could add all the articles but I choose not to, so I can have control over which article I would like to replicate across) Backup database to be migrated across Verify the backup taken above is valid Copy the backup across the network to the destination server ( scripted Robocopy and used multi threading (MT: 100), flows like a breeze on a dedicated network ) Restore the database on the destination server. Create a push subscription The script creates a log file for each task organizing it in the folder structure with ServerName and a subfolder with DatabaseName then creates a txt log file with results of each step. The script then prompts the user to check th...

SET NOCOUNT ON; DECLARE @v_cmd nvarchar(255)         ,@v_drive char(99)         ,@v_sql nvarchar(255)         ,@i int SELECT @v_cmd = 'fsutil volume diskfree %d%' SET @i = 1 CREATE TABLE #drives(iddrive smallint ,drive char(99)) CREATE TABLE #t(drive char(99),shellCmd nvarchar(500)); CREATE TABLE #total(drive char(99),freespace decimal(9,2), totalspace decimal(9,2)); -- Use mountvol command to INSERT #drives (drive) EXEC master..xp_cmdshell 'mountvol' DELETE #drives WHERE drive not like '%:\%' or drive is null WHILE (@i <= (SELECT count(drive) FROM #drives)) BEGIN      UPDATE #drives  SET iddrive=@i  WHERE drive = (SELECT TOP 1 drive FROM #drives WHERE iddrive IS NULL)  SELECT @v_sql = REPLACE(@v_cmd,'%d%',LTRIM(RTRIM(drive))) from #drives where iddrive=@i  INSERT #t(shellCmd)  EXEC master..xp_cmdshell @v_sql  UPDATE #t  SET #t.drive = d.drive  FROM #drives d  WHERE #t.drive IS NULL and iddrive=@i  ...

What do you need to do? Give the Job a name, a schedule, and the TSQL command that you want to run. That simple! After the job runs, it will self-destruct. Error Handling: Since the Job is deleting it self before it finish executing, there will be an entry on your SQL Agent log that would say: [180] Job SelfDeletingJob was deleted while it was executing: the outcome was (Unknown) BEGIN TRANSACTION USE [msdb] GO DECLARE @JOBNAME nvarchar(10)  DECLARE @SQLCMD nvarchar(100) DECLARE @STARTDATE nvarchar(8) DECLARE @STARTTIME nvarchar(6) DECLARE @SQLCMD2 nvarchar(100) SET @JOBNAME= 'SelfDeletingJob' -- Name your job appropiately SET @STARTDATE= '201112311' -- Date format:  YYYYMMDD SET @STARTTIME= '120000' -- Time format:  hhmmss (military time) -- Below Enter the command that you want to schedule. In this case, it is removing a login from the sysadmin role SET @SQLCMD='EXEC master..sp_dropsrvro...

EXEC sp_SysMon will print out below columns: StatDate, SQLServerName, MachineName, SQLServerCPU, ServerCPU, PageLifeExpectency, PLEThreshold, PLE%, BufferCacheHitRatio, AvailablePhysicalMemoryMB, TotalPhysicalMemoryMB, TotalServerMemoryMB, TargetServerMemoryMB, ConnectionMemoryMB, GrantedWorkspaceMemoryMB, LockMemoryMB, MaximumWorkspaceMemoryMB, OptimizerMemoryMB, SQLCacheMemoryMB, MemoryGrantsOutstanding, MemoryGrantsPending, BatchRequestsPerSecond, CompilationsPerSecond, ReCompilationsPerSecond, LockWaitsPerSecond, PageSplitsPerSecond, CheckpointPagesPerSecond, LockBlocks, LockBlocksAllocated, LockOwnerBlocks, LockOwnerBlocksAllocated, Processesblocked, BlockingSessions, BlockedSessions, UserConnections, Sessions, ServiceAccountSessions, UserAccountSessions, DormantSessions, RunningSessions, BackgroundSessions, RollbackSessions, PendingSessions, RunnableSessions, SpinloopSessions, SuspendedSessions, ServerStartTime, ServerUpTime USE [master] GO IF OBJECT_ID('...