Tuesday 26 March 2013

SQL Server Backup Status (Full & Transaction)

SQL Server Database Backup Status

Using below scripts we can find if a disk level backup has happend for a DB in last 7 days or not and we can check the status Transaction Log backup and we will get an entry if the Transaction Log is more than 10 GB.

For SQL Server 2000

select distinct @@servername [Server Name],bs.database_name Bkup_DB_Name,db.name [DB_Name],DATABASEPROPERTYEX(db.name,'Recovery') recovery_model_desc,DATABASEPROPERTYEX(db.name,'Recovery') log_reuse_wait_desc, case when bs.backup_finish_date is Null then 'No Latest Full Backup' when bs.backup_finish_date>=(getdate()-7) then 'Latest Backup' when bs.backup_finish_date<(getdate()-7) then 'Not Latest Backup' else 'Check Last Backup of the Database' end [Backup Status], case when bs.database_name is NULL then 'Take a full backup of '+db.name+' Database and check in Maintenance Plan' else 'We have a full backup, check latest or not' end Message from (msdb..backupmediafamily bm join msdb.dbo.backupset bs on bs.media_set_id=bm.media_set_id and device_type=2 and backup_finish_date>=(getdate()-7) ) right outer join master.dbo.sysdatabases db on bs.database_name=db.name where db.name<>'tempdb' and DATABASEPROPERTYEX(db.name,'status')='ONLINE' and (bs.database_name is null) union select @@servername [Server Name],instance_name Perf_DB_Name,db.name [DB_Name],DATABASEPROPERTYEX(instance_name,'Recovery') recovery_model_desc,cntr_value, case when cntr_value>=80 then 'Transaction Log usage is more than 80%' when cntr_value<79 then 'Transaction Log usage is less than 80%' else 'check' end [Backup sattus], case when cntr_value>=80 then 'Take a Transaction Log Backup of '+db.name+' Database and check in Maintenance Plan' else 'Check Database' end [Message] from master.dbo.sysperfinfo sp join master.dbo.sysdatabases db on db.name=sp.instance_name and COUNTER_NAME LIKE '%Percent Log Used%' AND INSTANCE_NAME NOT IN ('_Total','mssqlsystemresource') and cntr_value>=80 union select @@servername [Server Name],instance_name Perf_DB_Name,db.name [DB_Name],DATABASEPROPERTYEX(instance_name,'Recovery') recovery_model_desc,cntr_value/1024/1024 Counter_value, case when (cntr_value/1024/1024)>=10 then 'Transaction Log usage is more than 10 GB' when (cntr_value/1024/1024)<10 then 'Transaction Log usage is less than 10 GB' else 'check Database' end [Backup sattus], case when (cntr_value/1024/1024)>=10 then 'Take a Transaction Log Backup of '+db.name+' Database and check in Maintenance Plan' else 'Check Database' end [Message] from master.dbo.sysperfinfo sp join master.dbo.sysdatabases db on db.name=sp.instance_name and COUNTER_NAME LIKE '%Log File(s) Size (KB) %' AND INSTANCE_NAME NOT IN ('_Total','mssqlsystemresource') and (cntr_value/1024/1024)>=10


For SQL Server 2005 and 2008


select distinct @@servername [Server Name],bs.database_name Bkup_DB_Name,db.name [DB_Name],db.recovery_model_desc,db.log_reuse_wait_desc, case when bs.backup_finish_date is Null then 'No Latest Full Backup' when bs.backup_finish_date>=(getdate()-7) then 'Latest Backup' when bs.backup_finish_date<(getdate()-7) then 'Not Latest Backup' else 'Check Last Backup of the Database' end [Backup Status], case when bs.database_name is NULL then 'Take a full backup of '+db.name+' Database and check in Maintenance Plan' else 'We have a full backup, check latest or not' end Message from (msdb..backupmediafamily bm join msdb.dbo.backupset bs on bs.media_set_id=bm.media_set_id and device_type=2 and backup_finish_date>=(getdate()-7) ) right outer join master.sys.databases db on bs.database_name=db.name where db.name<>'tempdb' and db.state_desc='ONLINE' and (bs.database_name is null) union select @@servername,name,name,recovery_model_desc,log_reuse_wait_desc,case when log_reuse_wait_desc='LOG_BACKUP' then 'Trans Log getting Full' else log_reuse_wait_desc end,case when log_reuse_wait_desc='LOG_BACKUP' then 'Take a Transaction Log Backup of '+name+' Database and check in Maintenance Plan' else 'Check Database' end Message from master.sys.databases where log_reuse_wait_desc='LOG_BACKUP'

Finding and dropping Orphan Users in a DB

Below script can be used to find and drop the orphan users in a Database

 
 
Below script will list all the Orphan Users in a DB, we can select the output of [Script_To_Drop_User] column of the table variable and execute the same to Drop the user.
 
 -- Change the DB as per your DB Name
 

Use [PubDB]
declare @tbl_orpusers table (name varchar(260),[sid] varchar(100))

insert @tbl_orpusers
exec sp_change_users_login 'report'

select name [Orphan User],'DROP USER ['+name+']' [Script_To_Drop_User]from @tbl_orpusers



Checking the Orphan Users:



 
 
 
 
 
 
 
Below Link can be used to work on Orphan users in a DB,
 
 

 
 

Script to Disable Multiple Jobs in a SQL Server

Using below script we can generate the SP scripts to disable multiple jobs on a SQL Server


Method 1:
Below script will list all the Active Jobs configured on the SQL Server, we can select the output of [Script_To_Disable_Job] column of the jobs of our interest (which we wanted to disable) and execute the same to disable the job.
 
select
'EXEC msdb.dbo.sp_update_job @job_name='''+name+''''+', @enabled=0' [Script_To_Disable_Job]
,name,[enabled]
from msdb.dbo.sysjobs
where [enabled]=1

Below screen shots shows how we disabled few jobs using above script.






Method 2:

We can disable all the Active Jobs on a SQL Server by executing the below query on that server.

Note: Cross verify before executing the script as it disables all the Active Jobs in single shot.
 
use msdb
set nocount on

declare @maxjobcnt int,@jobname2dis varchar(260),@wcnt int=1
select @maxjobcnt=count(*) from msdb.dbo.sysjobs
where [enabled]=1
 
declare @tbl_disablejobs table(cnt int identity(1,1),
name varchar(260),[enabled] tinyint)

insert @tbl_disablejobs (name,[enabled])
select name,[enabled]
from msdb.dbo.sysjobs
where [enabled]=1
--select * from @tbl_disablejobs
 
while (@wcnt<=@maxjobcnt)

Begin
select @jobname2dis=name from @tbl_disablejobs
where cnt=@wcnt
--select @jobname2dis
EXEC msdb.dbo.sp_update_job @job_name=@jobname2dis, @enabled=0
Print '['+@jobname2dis+'] Job is Disabled'
set @wcnt=@wcnt+1
End

set nocount off


 

Tuesday 12 February 2013

Get Mail Alert when SQL Server Restarts

Using below query we can configure a alert mail whenever a SQL Server gets restarted


--- Checking startup stored procedures configuration status

SELECT VALUE, VALUE_IN_USE, DESCRIPTION
FROM SYS.CONFIGURATIONS
WHERE NAME = 'scan for startup procs'
GO

--- Create table in one of the User Database

CREATE TABLE ECSDBAMonitor.dbo.server_restart_info
(ServerName varchar(500),ReportTime datetime,LogDate datetime,LastText varchar(1000))

--- Create Procedure in Master Database

create PROCEDURE usp_server_restart_log
As
Begin
SET NOCOUNT ON
Create table #errorlog (logdate datetime,processinfo varchar(500),lasttext varchar(1000))

declare @startdate datetime,@enddate datetime

set @enddate=getdate()
set @startdate=dateadd(mi,-90,getdate())
--select @enddate,@startdate
insert #errorlog
Exec xp_readerrorlog 1,1,NULL,NULL,@startdate,@enddate,'desc'

declare @logdate datetime,@lasttext varchar(1000)

select top 1 @logdate=logdate,@lasttext=lasttext from #errorlog
--select @logdate,@lasttext
delete  ECSDBAMonitor.dbo.server_restart_info  where ReportTime<(getdate()-180)
insert into ECSDBAMonitor.dbo.server_restart_info values
(@@servername,getdate(),@logdate,@lasttext)

Drop table #errorlog

End
Go


--- Configuring startup stored procedures for the server

EXEC sp_procoption @ProcName = 'usp_server_restart_log'
    , @OptionName =  'startup'
    , @OptionValue = 'on';


--- Checking startup stored procedures configuration status

SELECT VALUE, VALUE_IN_USE, DESCRIPTION
FROM SYS.CONFIGURATIONS
WHERE NAME = 'scan for startup procs'
GO


--- Creating a Trigger on User Table with which we would be getting an alert mail when SQl Server
--- Restart and adds a row to user table. Chnage the Mail Id's, Profile Information
--- as per the environment


create TRIGGER tr_server_restart_info_mail
ON server_restart_info
AFTER INSERT
AS

declare @servername varchar(500),@reporttime datetime,@logdate datetime,@lasttext varchar(1000)

select @servername=ServerName,@reporttime=ReportTime
,@logdate=LogDate,@lasttext=LastText
     from inserted

declare @body varchar(2000)

select @body=N'<H1><font size="2" face="cambria">Hi Team,<br><br>'
select @body=@body +N' Below SQL Server was restarted at below mentioned time, please check if the server
was restarted unexpectedly. Please ignore the mail if the server was restarted for
maintenance activity<br><br></H1>'

Select @body = @body+
    N'<H1><font size="2" face="cambria">SQL Server Restart Information</font></H1>' +
    N'<table border="1">' +
    N'<tr><th><font size="2" face="cambria">ServerName</th><th>
<font size="2" face="cambria">ReportTime</th><th>
<font size="2"    face="cambria">LogDate</th><th>
<font size="2"   face="cambria">LastText</th>'


 select @body = @body + '<tr><th><font size="2" color="forestgreen" face="cambria">'+isnull(convert(varchar(500),@servername),' ')+
'</font></th><th><font size="2"  color="forestgreen" face="cambria">'+isnull(convert(varchar(500),@reporttime),' ')+
'</font></th><th><font size="2"  color="forestgreen" face="cambria">'+isnull(convert(varchar(500),@logdate),' ')+
'</font></th><th><font size="2"  color="forestgreen" face="cambria">'+isnull(convert(varchar(1000),@lasttext),' ')+
'</font></th>'


 select @body = @body + '</tr></table>'

declare @subject varchar(500)
set @subject='SQL Server Restart Info of '+@servername

 EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'pawankalyan@power.com',
    --@copy_recipients=' pawankalyan@power.com',
    @subject =@subject,
    @body =@body,
    @profile_name='Mail Alerts',
    @body_format = 'HTML' ;
Go

Friday 7 December 2012

Users and permissions in Database


Below Script is used to find the Users in Database and their permissions


SQL Server 2000

use [master]
select db_name(0)AS DBName, a.name, CASE WHEN a.isntgroup =1 AND a.isntuser=0 THEN 'Windows Group'
    WHEN a.isntgroup =0 AND a.isntuser=1 THEN 'Windows Login'
    WHEN a.issqlrole=1 THEN 'Database Role'
    ELSE 'SQL Login' END AS 'Login Type',USER_NAME(b.groupuid) AS 'AssociatedRole' ,sl.name 'Login_ID'
from  master..syslogins sl join dbo.sysusers a  on a.sid=sl.sid   LEFT OUTER JOIN dbo.sysmembers b ON a.uid=b.memberuid where a.altuid<>1 and a.uid not in (1,2) AND 'DBName' NOT IN ('master','msdb','model','tempdb') ORDER BY a.Name


SQL Server 2008

use [master]
Select db_name(0) As DBName,a.name 'User_Name',
CASE WHEN a.isntgroup =1 AND a.isntuser=0 THEN 'Windows Group'
    WHEN a.isntgroup =0 AND a.isntuser=1 THEN 'Windows Login'
    WHEN a.issqlrole=1 THEN 'Database Role'
    ELSE 'SQL Login' END AS 'Login Type',USER_NAME(b.groupuid) AS 'AssociatedRole' ,sl.name 'Login_ID'
from master..syslogins sl join sysusers a on sl.sid=a.sid
LEFT OUTER JOIN sysmembers b ON a.uid=b.memberuid
--where a.altuid<>1

SQL Server 2008

use master
Select db_name(0) As DBName,a.name 'User_Name',
CASE WHEN a.isntgroup =1 AND a.isntuser=0 THEN 'Windows Group'
    WHEN a.isntgroup =0 AND a.isntuser=1 THEN 'Windows Login'
    WHEN a.issqlrole=1 THEN 'Database Role'
    ELSE 'SQL Login' END AS 'Login Type',USER_NAME(b.groupuid) AS 'AssociatedRole' ,sl.name 'Login_ID',
    default_schema_name
from master..syslogins sl join sysusers a on sl.sid=a.sid
LEFT OUTER JOIN sysmembers b ON a.uid=b.memberuid
left join sys.database_principals dp on dp.principal_id=a.uid
--where a.altuid<>1

Script to find User Permissions on all DB's in a SQL Server 


create table #DBuserperm (DBName varchar(100),User_Name varchar(100),Login_Type varchar(100)
,Associated_Role varchar(100),LoginID varchar(100),Default_Schema_Name varchar(100),)
declare @dbname varchar(100)
declare @sqlQuery nvarchar(4000)

declare dbcursor CURSOR for
select name from sys.databases
where name not in ('master','model','msdb','tempdb')

OPEN dbcursor
FETCH NEXT FROM dbcursor
into @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

set @sqlQuery='Use ['+@dbname+']'
      set @sqlQuery =@sqlQuery+ 'Select db_name(0) As DBName,a.name '''+'User_Name'+''',
CASE WHEN a.isntgroup =1 AND a.isntuser=0 THEN '''+'Windows Group'+'''
    WHEN a.isntgroup =0 AND a.isntuser=1 THEN '''+'Windows Login'+'''
    WHEN a.issqlrole=1 THEN '''+'Database Role'+'''
    ELSE '''+'SQL Login'+''' END AS '''+'Login Type'+''',USER_NAME(b.groupuid) AS '''+'AssociatedRole'+''' ,
    sl.name '''+'Login_ID'+''',
    default_schema_name
from master..syslogins sl join sysusers a on sl.sid=a.sid
LEFT OUTER JOIN sysmembers b ON a.uid=b.memberuid
left join sys.database_principals dp on dp.principal_id=a.uid'

insert into #DBuserperm
      exec sp_executesql @sqlQuery 

FETCH NEXT FROM dbcursor
into @dbname
END

select * from #DBuserperm
drop table #DBuserperm
CLOSE dbcursor
Deallocate dbcursor