How to find backup status of databases which are online

We need to find the backup status of all database which are currently in online state . This script with not include database that is offline or in recovery state.

DECLARE @Baksql VARCHAR(8000) DECLARE @BackupFolder VARCHAR(100) DECLARE @BackupFile VARCHAR(100) DECLARE @BAK_PATH VARCHAR(4000) DEclare @BackupDate varchar(100)  
-- Setting value of  backup date and folder of the backup
SET @BackupDate =  REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') -- 20110517_182551 SET @BackupFolder = 'D:\DBBackups\' SET @Baksql = ''  
-- Declaring cursor
DECLARE c_bakup CURSOR FAST_FORWARD READ_ONLY FOR   SELECT NAME FROM SYS.DATABASES  WHERE state_desc = 'ONLINE'
-- Consider databases which are online
AND database_id > 4
-- Exluding system databases    
-- Opening and fetching next values from sursor
OPEN c_bakup  FETCH NEXT FROM c_bakup INTO @BackupFile    WHILE @@FETCH_STATUS = 0 BEGIN   SET @BAK_PATH = @BackupFolder + @BackupFile  
-- Creating dynamic script for every databases backup
SET @Baksql = 'BACKUP DATABASE ['+@BackupFile+'] TO DISK = '''+@BAK_PATH+'_FullBackup_'+@BackupDate+'.bak'' WITH INIT;'  
-- Executing dynamic query
PRINT (@Baksql) EXEC(@Baksql)  
-- Opening and fetching next values from sursor
FETCH NEXT FROM c_bakup INTO @BackupFile  END  
-- Closing and Deallocating cursor
CLOSE c_bakup DEALLOCATE c_bakup

How to check status of SQL services using TSQL

To check the SQL Server services or any other service status using SSMS you can execute the extended stored procedure xp_servicecontrol. 

EXEC xp_servicecontrol N'Querystat|Start|Stop’,N'Service Name' 

E.g :- 

EXEC xp_servicecontrol N'querystate',N'MSSQLServer'

EXEC xp_servicecontrol N'querystate',N'SQLServerAGENT' 

How to find the owner of the maintenance plans in SQL SERVER 2000/2005/2008/2008R2

Run the below script and it will tell you the owner of the maintenance plan.

For SQL 2000.

select plan_name,owner from dbo.sysdbmaintplans

For SQL 2005/2008/2008R2

select * from dbo.sysmaintplan_plans

If you want to change the maintenance plan owner then please use the below command.

For SQL 2000

update dbo.sysdbmaintplans set owner ='sa' where owner ='LoginName'

For SQL 2005


UPDATE
msdb.dbo.sysdtspackages90
SET
OWNERSID = SUSER_SID('YOUR_DOMAIN\APPROPRIATE_ACCOUNT')
WHERE
OWNERSID = SUSER_SID('YOUR_DOMAIN\OLD_ACCOUNT')

For SQL 2008/2008R2


update msdb.dbo.sysssispackages set [ownersid] = suser_sid('sa') where [name] = 'MaintenancePlan'

How to change the collation of the server.

Run the below command in CMD and it will change the collation of the server. Master database will be rebuild.

setup.exe /Quiet /ACTION=REBUILDDATABASE /INSTANCENAME=Instance2 /SQLSYSADMINACCOUNTS=server\Administrator /SAPWD=sa123 /SQLCOLLATION=SQL_Latin1_General_CP1_CS_AS 

Script to list all indexes in the databases.

Run the below script on the database and it will list down all the indexes present inside the database.

SELECT   SchemaName = schema_name(schema_id),

         TableName = object_name(t.object_id),

         ColumnName = c.name,

         [Unique] = CASE

                      WHEN i.is_unique = 1 THEN 'Yes'

                      ELSE 'No'

                    END,

         [Clustered] = CASE

                         WHEN i.index_id = 1 THEN 'C'

                         ELSE 'NC'

                       END,

         Ordinal = key_ordinal,

         IndexName = i.Name

FROM     sys.indexes i

         INNER JOIN sys.tables t

           ON i.object_id = t.object_id

         INNER JOIN sys.index_columns ic

           ON ic.object_id = t.object_id

              AND ic.index_id = i.index_id

         INNER JOIN sys.columns c

           ON c.object_id = t.object_id

              AND ic.column_id = c.column_id

ORDER BY SchemaName,

         TableName,

         IndexName,

         key_ordinal

Issue 3 :-TSQL command to check if the Login has access to server

Issue :-

When job fails and job owner is a domain login which do not have access to server 


Cause :-

SQL agent services will not be running with a domain account thus it is not able to verify the domain login. Also the account doesn't exist on domain any more.

Remediation Steps :-

1. Xp_logininfo (‘account name’). Run the command and if it return NULL that means the server is not able to verify the domain account.

2. Change the job owner to SA and re-run the job. It will run successfully.

Syntax to create Database Snapshot using T-SQL

Syntax :-

create database SnapshotName  on ( Name = Logical file name of DB , filename = 'C:\snapshotname.ss' ) as snapshot of Database name

Example :-

create database  test_snapshot on ( Name = test_new , filename = 'C:\test_new_snapshot.ss' ) as snapshot of test_new

How to find the CPU busy stats for SQL SERVER.

This statement will give the value that will show how much the SQL SERVER CPU is busy.

DECLARE @CPU_BUSY int, @IDLE int

SELECT @CPU_BUSY = @@CPU_BUSY, @IDLE = @@IDLE

WAITFOR DELAY '000:00:01'

SELECT (@@CPU_BUSY - @CPU_BUSY)/((@@IDLE - @IDLE + @@CPU_BUSY - @CPU_BUSY) * 1.00) *100 AS CPUBusyPct

How to find the percentage completion of backup and restore operation

Run the below script and it will give the percentage of backup and restore operation.

Find the session id that is doing the backup or restore from sys.processess.

SELECT percent_complete , (estimated_completion_time/1000)/60 Estimated_completion_time_Mins ,

(total_elapsed_time/1000)/60 Total_Elapsed_Time_Mins ,DB_NAME(Database_id) DBName ,*FROM sys.dm_exec_requests WHERE session_id='id which is doing the operation'

Script to list all users having access on extended stored procedure in SQL SERVER.

The script will list all the users that are having the access on the extended stored procedure.

SELECT     SystemObject.name AS [Extended storedProcedure] , USER_NAME(SystemPermissionObject.grantee) AS [Granted to]

FROM sys.all_objects AS SystemObject INNER JOIN master.dbo.syspermissions AS SystemPermissionObject ON SystemObject.object_id = SystemPermissionObject.id

WHERE   (SystemObject.type = 'X')ORDER BY SystemObject.name

How to generate a list of extended stored procedure in SQL SERVER.

Run the below command to get a list of all extended stored procedure.

SELECT * FROM sys.all_objects WHERE  [type] = 'X' ORDER BY [name]

Script to change the autogrowth setting of all database by 100 MB.

Below is the script that will change the autogrowth  setting of all database except master and msdb database by 100 MB.

IF EXISTS(SELECT name FROM sys.sysobjects WHERE name = N'ConfigAutoGrowth' AND xtype='U') 

    DROP TABLE ConfigAutoGrowth 

GO   

CREATE TABLE DBO.ConfigAutoGrowth 

( 

iDBID       INT, 

sDBName     SYSNAME, 

vFileName   VARCHAR(max), 

vGrowthOption   VARCHAR(12) 

) 

PRINT 'Table ConfigAutoGrowth Created' 

GO 

-- Inserting data into staging table 

INSERT INTO DBO.ConfigAutoGrowth 

SELECT  

    SD.database_id,  

    SD.name, 

    SF.name, 

    --sf.fileid,  

    --SUSER_NAME(owner_sid), 

    --recovery_model_desc,  

    CASE SF.status & 0x100000 

    WHEN 1048576 THEN 'Percentage' 

    WHEN 0 THEN 'MB' 

    END AS 'GROWTH Option' 

FROM SYS.SYSALTFILES SF 

JOIN  

SYS.DATABASES SD 

ON  

SD.database_id = SF.dbid 

GO 

 

-- Dynamically alters the file to set auto growth option to fixed mb  

DECLARE @name VARCHAR ( max ) -- Database Name 

DECLARE @dbid INT -- DBID 

DECLARE @vFileName VARCHAR ( max ) -- Logical file name 

DECLARE @vGrowthOption VARCHAR ( max ) -- Growth option 

DECLARE @Query VARCHAR(max) -- Variable to store dynamic sql 

 

 

DECLARE db_cursor CURSOR FOR 

SELECT  

idbid,sdbname,vfilename,vgrowthoption 

FROM configautogrowth 

WHERE sdbname NOT IN ( 'master' ,'msdb' )  

--AND vGrowthOption  = 'Percentage' or 'Mb'

 

OPEN db_cursor 

FETCH NEXT FROM db_cursor INTO @dbid,@name,@vfilename,@vgrowthoption   

WHILE @@FETCH_STATUS = 0 

BEGIN 

    PRINT 'Changing AutoGrowth option for database:- '+ UPPER(@name) 

    SET @Query  = 'ALTER DATABASE '+ @name +' MODIFY FILE (NAME = '+@vFileName+',FILEGROWTH = 100MB)' 

    EXECUTE(@Query) 

 

FETCH NEXT FROM db_cursor INTO @dbid,@name,@vfilename,@vgrowthoption   

END 

CLOSE db_cursor -- Closing the curson 

DEALLOCATE db_cursor  -- deallocating the cursor 

 

GO 

-- Querying system views to see if the changes are applied 

  DECLARE @SQL VARCHAR(8000), @sname VARCHAR(3)

SET @SQL=' USE ?

SELECT ''?'' [Dbname]

,[name] [Filename]

,CASE is_percent_growth

WHEN 1 THEN CONVERT(VARCHAR(5),growth)+''%''

ELSE CONVERT(VARCHAR(20),(growth/128))+'' MB''

END [Autogrow_Value]

,CASE max_size

WHEN -1 THEN CASE growth

WHEN 0 THEN CONVERT(VARCHAR(30),''Restricted'')

ELSE CONVERT(VARCHAR(30),''Unlimited'') END

ELSE CONVERT(VARCHAR(25),max_size/128)

END [Max_Size]

FROM ?.sys.database_files'

IF EXISTS(SELECT 1 FROM tempdb..sysobjects WHERE name='##Fdetails')

DROP TABLE ##Fdetails

CREATE TABLE  ##Fdetails (Dbname VARCHAR(50),Filename VARCHAR(50),Autogrow_Value VARCHAR(15),Max_Size VARCHAR(30))

INSERT INTO ##Fdetails

EXEC sp_msforeachdb @SQL

SELECT * FROM ##Fdetails  ORDER BY Dbname

 

--Dropping the staging table 

DROP TABLE ConfigAutoGrowth  

GO

Script to find the list of database name whose backup is not happened for the past one day

Run the below command and it will list out all the database name that have not been backed up since yesterday.

use msdb;

SELECT  backupset.database_name,backupset.backup_start_date,backupmediafamily.physical_device_name,(backupset.backup_size/1024)/1000 as size

FROM   backupset,backupmediafamily where backupset.media_set_id = backupmediafamily.media_set_id

and datediff(dd,backupset.backup_start_date,getdate()) < 1 and database_name like '%%' order by backupset.database_name,backupset.backup_start_date desc

How to set recovery model of all database to simple by using TSQL

Run the below command on master database and it will change the recovery model of all the databases in SQL SERVER to simple.

DECLARE @command varchar(1000)

SELECT @command = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'')

BEGIN USE ? EXEC(''ALTER DATABASE ? set recovery simple  '') END '

EXEC sp_MSforeachdb @command

For changing the recovery model to FULL use the below command.

DECLARE @command varchar(1000)

SELECT @command = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'')

BEGIN USE ? EXEC(''ALTER DATABASE ? set recovery FULL  '') END '

EXEC sp_MSforeachdb @command

For changing the recovery model to BULK_LOGGED use the below command.

DECLARE @command varchar(1000)

SELECT @command = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'')

BEGIN USE ? EXEC(''ALTER DATABASE ? set recovery bulk_logged  '') END '

EXEC sp_MSforeachdb @command

How to check trustworthy property of the databases

The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the contents within it.

select  name,is_trustworthy_on from sys.databases

This will let you know if trustworthy property is enabled/disabled for all database.

If you want to set trustworthy property On the use the below command on the required database.

ALTER DATABASE DatabaseName set TRUSTWORTHY ON

If you want to set trustworthy property ON for all database in SQL SERVER except the system databases then please use the below command.

DECLARE @command varchar(1000)

SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? EXEC(''ALTER DATABASE ? set TRUSTWORTHY ON '') END '

EXEC sp_MSforeachdb @command

How to check the page_verify value of all database in SQL SERVER.

The PAGE_VERIFY option discovers database pages which have been damaged by disk errors; normally caused by hardware failure or power outage. If SQL Server finds a page which it suspects is damaged, error number 824 will be raised. Depending on the value of the PAGE_VERIFY setting, the message will report the type of suspected condition (torn page or checksum error). Normally such errors are raised during database recovery at SQL Server startup. SQL Server will write the error to its error log as well as to the application event log and retry reading the damaged page up to four times. If any of these attempts are successful the read operation will move on to the next data page, otherwise the read operation will fail. Any time error 824 is raised, SQL Server will write a record to suspect_pages system table in msdb database, however, the pages might still be readable even if they're suspected. If the read operation does fail, you'll have to attempt fixing the database using the DBCC CHECKDB statement. If only index pages are damaged, you can usually resolve the problem by rebuilding the index

There are three kind of values that we can set for page_verify in a database. For checking the value of  page_verify we can use the below command.

Values that can be set for page_verify :-
1. Checksum
2. Torn_page_detection
3. None

Command 1 :-

SELECT NAME , page_verify_option_desc  FROM sys.databases

Output 1:-

It will list the page_verify value that is currently set for all database.

Command 2 :-

SELECT NAME  FROM sys.databases  WHERE page_verify_option_desc <> 'CHECKSUM'

Output 2:- It will list all database whose page_verify value is set to checksum. 

How to check the date when the password of SQL logins will expire.

Select name

         ,create_date

         ,modify_date

         ,default_database_name

         ,is_policy_checked

         ,is_expiration_checked

         ,loginproperty(name, 'DaysUntilExpiration') as 'Number of days to expire'

         ,loginproperty(name, 'PasswordLastSetTime') As 'Last Time password is set'

  From sys.sql_logins;

How to check password policy status of all SQL logins.

Run the below command and you will get the status of password policy of all SQL Logins inside a SQL server.

select * from sys.sql_logins

Issue 2 :- How can you kill negative SPID or ORPHANED DISTIBUTION TRANSACTION

If you want to kill negative SPID or orphaned distribution transaction then follow the below steps.


1. Find the negative SPID number.

2. For e.g. is SPID number is -2 then run the below T-SQL

use master
go
select req_transactionUOW from syslockinfo where req_spid = -2
go

3. You will get a hexadecimal value with the above syntax and then run the below command.

KILL 'FE4A57F2-28C5-44F9-8416-B08760DFE7E9'

By this process you will be able to kill negative SPID.

Issue 1 :- Not able to edit the maintenance plan and getting error.

Error :- Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)

Solution :- Register the DTS.dll (using regsvr32) in \Program Files\Microsoft SQL Server\100\DTS\Binn and restarted SSMS and the problem has gone away.

Script to find all objects dependent on view

EXEC sp_depends @objname = N'View Name'

Granting execute permission on a stored procedure to a login

Grant Execute on store_procedure to loginname

To grant execute permission on all Stored procedure inside a database

Use databasename

select 'Grant Execute on ' + name + ' to loginame'

from sysobjects where xtype in ('P')

How to find the start scan procedure enabled in SQL 2005/2008

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

How to find the start scan procedure that are enabled in SQL 2000

SELECT @@Servername

USE MASTER

GO

SELECT VALUE,comment

FROM sysconfigures

WHERE COMMENT = 'scan for startup stored procedures'

GO

How to find the current isolation level.

DBCC useroptions

To find SQL SERVER settings that have deviated from default settings.

DECLARE @config_defaults TABLE (

    name nvarchar(35),

    default_value sql_variant

)

INSERT INTO @config_defaults (name, default_value) VALUES

('access check cache bucket count',0),

('access check cache quota',0),

('Ad Hoc Distributed Queries',0),

('affinity I/O mask',0),

('affinity mask',0),

('affinity64 I/O mask',0),

('affinity64 mask',0),

('Agent XPs',1),

('allow updates',0),

('awe enabled',0),

('backup compression default',0),

('blocked process threshold (s)',0),

('c2 audit mode',0),

('clr enabled',0),

('common criteria compliance enabled',0),

('cost threshold for parallelism',5),

('cross db ownership chaining',0),

('cursor threshold',-1),

('Database Mail XPs',0),

('default full-text language',1033),

('default language',0),

('default trace enabled',1),

('disallow results from triggers',0),

('EKM provider enabled',0),

('filestream access level',0),

('fill factor (%)',0),

('ft crawl bandwidth (max)',100),

('ft crawl bandwidth (min)',0),

('ft notify bandwidth (max)',100),

('ft notify bandwidth (min)',0),

('index create memory (KB)',0),

('in-doubt xact resolution',0),

('lightweight pooling',0),

('locks',0),

('max degree of parallelism',0),

('max full-text crawl range',4),

('max server memory (MB)',2147483647),

('max text repl size (B)',65536),

('max worker threads',0),

('media retention',0),

('min memory per query (KB)',1024),

-- NOTE: SQL Server may change the min server

--   memory value 'in flight' in some environments

--    so it may commonly show up as being 'non default'

('min server memory (MB)',0),

('nested triggers',1),

('network packet size (B)',4096),

('Ole Automation Procedures',0),

('open objects',0),

('optimize for ad hoc workloads',0),

('PH timeout (s)',60),

('precompute rank',0),

('priority boost',0),

('query governor cost limit',0),

('query wait (s)',-1),

('recovery interval (min)',0),

('remote access',1),

('remote admin connections',0),

('remote login timeout (s)',20),

('remote proc trans',0),

('remote query timeout (s)',600),

('Replication XPs',0),

('scan for startup procs',0),

('server trigger recursion',1),

('set working set size',0),

('show advanced options',0),

('SMO and DMO XPs',1),

('SQL Mail XPs',0),

('transform noise words',0),

('two digit year cutoff',2049),

('user connections',0),

('user options',0),

('xp_cmdshell',0)

SELECT c.name, value, value_in_use, d.default_value

from sys.configurations c

INNER JOIN @config_defaults d ON c.name = d.name

where

    c.value != c.value_in_use

    OR c.value_in_use != d.default_value

Go