Backup all file group

/*
Script  : Backup all file group
Version : September 2012
Author  : Sushil Rout
Web     : http://sushildbasupports.blogspot.com
*/

DECLARE @destinationpath NVARCHAR(1000)
DECLARE @beginbackuptimestamp VARCHAR(20)

SET @destinationpath = '\\SERVER-01\sqlbackupshare$\'
SET @beginbackuptimestamp =  REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30),GETDATE(),20),'-',''),':',''),' ','_')

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#backupcommand%')
   DROP TABLE #backupcommand
 
CREATE TABLE #backupcommand (id INT IDENTITY(1,1), command VARCHAR(1000))

INSERT INTO #backupcommand (command)
SELECT 'BACKUP DATABASE [' + DB_NAME()+'] FILEGROUP = ''' + name + ''' TO DISK = ''' + @destinationpath  + DB_NAME() +  '_' + @beginbackuptimestamp +  '_' + name +'.BAK'''
FROM sys.filegroups
--WHERE name <> 'NCIndexes'
ORDER BY data_space_id

DECLARE @intCounter INT
SET @intCounter = 0

DECLARE @intMaxId INT
SELECT @intMaxId = MAX(ID) FROM #backupcommand

DECLARE @CurrentCommand VARCHAR(1000)

 WHILE (@intCounter <= @intMaxId)

BEGIN
SET @intCounter = @intCounter + 1

  SELECT @CurrentCommand = command FROM #backupcommand
WHERE id = @intCounter
   
    -- display command being executed. using RAISERROR here to display immediately, unlike PRINT
    RAISERROR (@CurrentCommand, 10, 1) WITH NOWAIT
   
    -- do the backup
    EXEC  (@CurrentCommand)
       
END
GO

Script to grant exec permission to all sps in a db

USE [databaseName]
Go
---*********ADD EXEC PERMS TO ALL SPS******--------------
DECLARE @cmd varchar(8000)
DECLARE @objectCount int
DECLARE @OwnerName varchar(128)
DECLARE @ObjectName varchar(128)
DECLARE @user VARCHAR(100) = 'myuser';

CREATE TABLE #StoredProcedures
(OID int IDENTITY (1,1),
StoredProcOwner varchar(128) NOT NULL,
StoredProcName varchar(128) NOT NULL)


INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
SELECT
u.[Name],
o.[Name]
FROM
dbo.sysobjects o
INNER JOIN dbo.sysusers u
ON o.uid = u.uid
WHERE o.Type = 'P';


SELECT @objectCount = MAX(OID) FROM #StoredProcedures

WHILE @objectCount > 0
BEGIN


SELECT @OwnerName = StoredProcOwner,
@ObjectName = StoredProcName
FROM #StoredProcedures
WHERE OID = @objectCount


SELECT @cmd = 'GRANT EXEC ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO ' + @user

SELECT @cmd;

EXEC(@cmd);


SET @objectCount = @objectCount- 1;

END

DROP TABLE #StoredProcedures;

What got restored from where, by who and when

Working in a small environment where people have their fingers in many pies can sometimes be a pain.

The developers have full db_owner rights to the databases and can restore the DB to a previous version, this script helped my find who restored what, when they restored it and what backup / server was used as the source database.

Not that I like pointing fingers at who broke something, but could come in handy if someone rolled back the DB and something went missing.

SELECT
DatabaseRestoredTo = RH.destination_database_name,
TimeOfRestore = RH.restore_date,
UserImplimentingRestore = RH.user_name,
RestoreType = CASE RH.restore_type WHEN 'D' THEN 'Full DB Restore'
WHEN 'F' THEN 'File Restore'
WHEN 'G' THEN 'Filegroup Restore'
WHEN 'I' THEN 'Differential Restore'
WHEN 'L' THEN 'Log Restore'
WHEN 'V' THEN 'Verify Only'
END,
ServerWhereBackupTaken = BS.server_name,
UserWhoBackedUpTheDatabase = BS.user_name,
BackupOfDatabase = BS.database_name,
DateOfBackup = BS.backup_start_date,
RestoredFromPath = BMF.physical_device_name
FROM
msdb.dbo.restorehistory RH
INNER JOIN
msdb.dbo.backupset BS
ON
RH.backup_set_id = BS.backup_set_id
INNER JOIN
msdb.dbo.backupmediafamily BMF
ON
BS.media_set_id = BMF.media_set_id
ORDER BY
RH.restore_history_id

Check SQL Job timings

Open Management studio. Use msdb database and execute the script. The user should be sysadmin to execute this script.

Use msdb
go
SELECT name,
   step_id
  ,run_date
  ,count(*) howMany
  ,min((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) lowest_Min
  ,avg((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) average_Min
  ,max((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) highest_Min
  ,stdev((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) stdev_Min
 from sysJobHistory h
inner join sysjobs j  on
(h.job_id = j.job_id)
where name =''
group by name, step_id, run_date
order by run_date desc

All database files growth

Execute the given script which will provide growth details for each file of the databases.

select DB_NAME(database_id) as Database_name,Name,growth,
case is_percent_growth
when 0 then 'In MB'
when 1 then 'In Percent'
end as Typeofgrowth,
case max_size
when 0 then 'Restricted'
when -1 then 'Unrestricted'
when 268435456 then 'Unrestricted'
else convert(varchar(20),max_size/128)
end as Maxsize
 from sys.master_files

Find size of all tables in a database

Declare @v_TableName Varchar(100)
Declare @v_Table Table (Table_Name Varchar(100))
Declare @v_TableSize Table (
Table_Name Varchar(100),
rows BigInt,
Reserved Varchar(50),
ActualDataSize Varchar(50),
IndexSize Varchar(50),
Unused Varchar(50)
)
Insert Into @v_Table
Select Table_Name From Information_Schema.Tables Where Table_Type = 'BASE TABLE'
Select @v_TableName = Table_Name From @v_Table
While Exists (Select 1 From @v_Table)
Begin
Insert Into @v_TableSize exec sp_spaceused @v_TableName
Delete From @v_Table Where Table_Name = @v_TableName
Select @v_TableName= Table_Name From @v_Table
End
Select * From @v_TableSize Order By rows Desc
Go

How SPARSE column saves space in database table.

Example of how SPARSE column saves space in database table.

CREATE TABLE UnSparsed(ID INT IDENTITY(1,1),
FirstCol INT,
SecondCol VARCHAR(100),
ThirdCol SmallDateTime)
GO
CREATE TABLE Sparsed(ID INT IDENTITY(1,1),
FirstCol INT SPARSE,
SecondCol VARCHAR(100) SPARSE,
ThirdCol SmallDateTime SPARSE)
GO
DECLARE @idx INT = 0
WHILE @idx < 50000
BEGIN
INSERT INTO UnSparsed VALUES (NULL,NULL, NULL)
INSERT INTO Sparsed VALUES (NULL, NULL, NULL)
SET @idx+=1
END
GO
sp_spaceused 'UnSparsed'
GO
sp_spaceused 'Sparsed'
GO
DROP TABLE UnSparsed
GO
DROP TABLE Sparsed
GO

Where is the Object ? In which Database ?

I want to use one stored procedure or any other object name contains 'Blog' from current Server or Instance.

But, I don't know where is/are the procedure or object actually exists! or In which database ?

Is there any way to search an object in all the databases in current Instance ?
 
Use  Master 
Go
Declare @WhereIs Table
(DBName Varchar(100),
ObjectName Varchar(150),
ObjectType Varchar(150)
)
Insert @WhereIs
Exec sp_msforeachdb 'use [?]; select DB_NAME(), name, Type_desc from sys.objects where name like ''%Blog%'''

Select * from @WhereIs
Go

To capture current executing statements that are waiting on IO or just finished waiting on IO:

select r.session_id, s.login_name, s.program_name, r.start_time, r.status, r.command, r.wait_type, r.wait_time, r.last_wait_type, r.logical_reads, (r.logical_reads * 8192) as 'KB Read', r.writes, (r.writes * 8192) as 'KB Written', t.[text] from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(sql_handle) t inner join sys.dm_exec_sessions s on r.session_id = s.session_id where s.is_user_process = 1 and (r.wait_type like 'PAGEIOLATCH%' or r.last_wait_type like 'PAGEIOLATCH%') and r.session_id != @@SPID

Find Next Running Time of Scheduled Job Using T-SQL

USE msdb ;WITH CTE AS (SELECT schedule_id, job_id, RIGHT('0'+CAST(next_run_time AS VARCHAR(6)),6) AS next_run_time, next_run_date FROM sysjobschedules) SELECT A.name Job_Name, 'Will be running today at '+ SUBSTRING(CONVERT(VARCHAR(10), CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12 THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) -12 ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) END),1,2) +':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time),3,2) +':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2) 'Scheduled At' FROM sysjobs A ,CTE B WHERE A.job_id = B.job_id AND SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 5,2) +'/'+ SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 7,2) +'/'+ SUBSTRING(CONVERT(VARCHAR(10),next_run_date),1,4) = CONVERT(VARCHAR(10),GETDATE(),101) AND (SUBSTRING( CONVERT(VARCHAR(10), CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12 THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) -12 ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) END),1,2) +':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),3,2) +':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2)) > SUBSTRING (CONVERT( VARCHAR(30) , GETDATE(),9),13,7)