sql script for regular jobs

Script to get data files size, used space and free space

select

        DBName,

        name,

        [filename],

        size as 'Size(MB)',

        usedspace as 'UsedSpace(MB)',

        (size - usedspace) as 'AvailableFreeSpace(MB)'

from       

(  

SELECT

db_name(s.database_id) as DBName,

s.name AS [Name],

s.physical_name AS [FileName],

(s.size * CONVERT(float,8))/1024 AS [Size],

(CAST(CASE s.type WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8) END AS float))/1024 AS [UsedSpace],

s.file_id AS [ID]

FROM

sys.filegroups AS g

INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)

) DBFileSizeInfo

Script to get the SQL Server Installation Date and Authentication Mode

select createdate as InstallationDate ,

CASE SERVERPROPERTY('IsIntegratedSecurityOnly')  

WHEN 1 THEN 'Windows Authentication' 

WHEN 0 THEN 'Windows and SQL Server Authentication' 

END as [AuthenticationMode],

SERVERPROPERTY('servername') as svrName

from master..syslogins where name like 'NT AUTHORITY\SYSTEM'

Query to check the SQL Evaluation Version Expire Date

SELECT create_date as 'SQL Server Installation Date',

DATEADD(dd,180,create_date) as 'Expiration Date'

FROM sys.server_principals WHERE name='NT AUTHORITY\SYSTEM'

Script to find out Stored Procedures which are Using most resources

SELECT DB_NAME(database_id) AS DatabaseName

      ,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]

      ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]

      ,cached_time

      ,last_execution_time

      ,execution_count

      ,total_worker_time / execution_count AS Average_CPU

      ,total_elapsed_time / execution_count AS Average_Elapsed_Time

      ,total_logical_reads / execution_count AS Average_Logical_Reads

      ,total_logical_writes / execution_count AS Average_Logical_Writes

      ,total_physical_reads  / execution_count AS Average_Physical_Reads

FROM sys.dm_exec_procedure_stats

where database_id <> 32767

ORDER BY Average_Logical_Reads DESC

Script to get the database backup history

SELECT

   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

   msdb.dbo.backupset.database_name, 

   msdb.dbo.backupset.backup_start_date, 

   msdb.dbo.backupset.backup_finish_date,

   msdb.dbo.backupset.expiration_date,

   CASE msdb..backupset.type 

       WHEN 'D' THEN 'Database'

       WHEN 'L' THEN 'Log'

   END AS backup_type, 

   msdb.dbo.backupmediafamily.logical_device_name, 

   msdb.dbo.backupmediafamily.physical_device_name,  

   msdb.dbo.backupset.name AS backupset_name,

   msdb.dbo.backupset.description

FROM   msdb.dbo.backupmediafamily 

   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE

(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= '2013-01-08 00:04:49.000')  and (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) <= '2013-01-08 23:59:59') 

and  msdb.dbo.backupset.database_name = 'Mention the database name'

ORDER BY

   msdb.dbo.backupset.backup_finish_date desc

SA account property/password change

USE Master

GO

SELECT [name], sid, create_date, modify_date

FROM sys.sql_logins

WHERE [name] = 'sa'

GO

Get the List of DBCC Commands & Syntax

If you don’t remember all the DBCC commands “NO Issue” execute the DBCC HELPcommand to get the list of all the DBCC.

DBCC HELP('?')

Script to check – Login Name and Password are same

It is always a risk if the user name & password is equal. You can run the below script to find out the list of User/Login name & password which are equal.

select

cast(@@SERVERNAME as varchar(150)) as SQLInstanceName

,name as [LoginName]

,'Password is same as Login Name' [Description]

from sys.syslogins

WHERE PWDCOMPARE (name,password) = 1

Script to get the SQL Server Properties

create table #server(ID int,  Name  sysname null, Internal_Value int null, Value nvarchar(512) null)

insert #server exec master.dbo.xp_msver

declare @RegRootDir nvarchar(512)

exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @RegRootDir OUTPUT

SELECT

(select Value from #server where Name = N'ProductName') AS [Product],

SERVERPROPERTY(N'ProductVersion') AS [VersionString],

(select Value from #server where Name = N'Language') AS [Language],

(select Value from #server where Name = N'Platform') AS [Platform],

CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],

(select Internal_Value from #server where Name = N'ProcessorCount') AS [Processors],

(select Value from #server where Name = N'WindowsVersion') AS [OSVersion],

(select Internal_Value from #server where Name = N'PhysicalMemory') AS [PhysicalMemory],

CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],

@RegRootDir AS [RootDirectory],

convert(sysname, serverproperty(N'collation')) AS [Collation]

drop table #server

T-SQL Script to identify tables without Primary Key

Use <Database Name>

SELECT SCHEMA_NAME(schema_id) AS [Schema Name], name AS [Table Name]

FROM sys.tables

WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0

Order by name

GO

T-SQL Script to identify the data, log and backup drive

select

@@SERVERNAME as svrName,

drivename,

drivedescription

from

(

select distinct  SUBSTRING(filename,1,3) as drivename, 'data Drive' as drivedescription from master..sysaltfiles where filename like '%.mdf'

union

select distinct  SUBSTRING(filename,1,3) as drivename, 'Log Drive' as drivedescription from master..sysaltfiles where filename like '%.ldf'

union

select distinct  SUBSTRING(physical_device_name,1,3) as drivename, 'Backup Drive' as drivedescription from msdb.dbo.backupmediafamily where physical_device_name like '%.bak'

) tab1

Rename the database

EXEC sp_renamedb N'olddb', N'newdb';

Query to check the state of the DB Mirroring

SELECT state_desc FROM sys.database_mirroring_endpoints

GO

Query to check the associated ports with DB Mirroring

GO

SELECT type_desc, port FROM sys.tcp_endpoints;

GO

How to Check When SQL Server was installed?

SELECT createdate AS sql_server_install_date FROM   sys.syslogins WHERE  sid = 0x010100000000000512000000

SELECT * FROM   sys.syslogins WHERE  sid = 0x010100000000000512000000

Scripts which make you Database Hero

– Create databsae SQLDBPool
CREATE DATABASE [sqldbpool] ON PRIMARY
( NAME = N’sqldbpool’, FILENAME = N’C:\sqldbpool.mdf’ , SIZE = 2048KB ,MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’sqldbpool_log’, FILENAME = N’C:\sqldbpool_log.ldf’ , SIZE = 1024KB, MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

–Script to create schema
USE [sqldbpool]
GO
CREATE SCHEMA [mySQLDBPool] AUTHORIZATION [dbo]

– Script to create table with constraints
create table mySQLDBPool.Emp
(
EmpID int Primary key identity(100,1),
EmpName Varchar(20) Constraint UK1 Unique,
DOB datetime Not Null,
JoinDate datetime default getdate(),
Age int Constraint Ck1 Check (Age > 18)
)

– Script to change the recovery model of the databsae
USE [master]
GO
ALTER DATABASE [SQLDBPool] SET RECOVERY FULL WITH NO_WAIT
GO

ALTER DATABASE [SQLDBPool] SET RECOVERY FULL
GO

– Script to take the full backup of database
BACKUP DATABASE [SQLDBPool] TO DISK = N’D:\SQLDBPool.bak’
WITH NOFORMAT, INIT, NAME = N’SQLDBPool-Full Database Backup’,
NOREWIND, SKIP, NOUNLOAD, STATS = 10
GO

–Script to take the Differential Database backup
BACKUP DATABASE [SQLDBPool] TO DISK = N’D:\SQLDBPool.diff.bak’
WITH DIFFERENTIAL , NOFORMAT, INIT, NAME = N’SQLDBPool-Diff Backup’,
NOREWIND, SKIP, NOUNLOAD, STATS = 10
GO

–Script to take the Transaction Log backup that truncates the log
BACKUP LOG [SQLDBPool] TO DISK = N’D:\SQLDBPoolTlog.trn’
WITH NOFORMAT, INIT, NAME = N’SQLDBPool-Transaction Log Backup’,SKIP, NOREWIND,NOUNLOAD, STATS = 10
GO

– Backup the tail of the log (not normal procedure)
BACKUP LOG [SQLDBPool] TO DISK = N’D:\SQLDBPoolLog.tailLog.trn’
WITH NO_TRUNCATE , NOFORMAT, INIT, NAME = N’SQLDBPool-Transaction Log Backup’,NOREWIND,SKIP, NOUNLOAD, NORECOVERY , STATS = 10
GO

– Script to Get the backup file properties
RESTORE FILELISTONLY FROM DISK = ‘D:\SQLDBPool.bak’

– Script to Restore Full Database Backup
RESTORE DATABASE [SQLDBPool1] FROM DISK = N’D:\SQLDBPool.bak’
WITH FILE = 1, MOVE N’sqldbpool’ TO N’D:\SQLDBPooldata.mdf’,
MOVE N’sqldbpool_log’ TO N’D:\SQLDBPoollog_1.ldf’,
NOUNLOAD, STATS = 10
GO

– Script to delete the backup history of the specific databsae
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N’SQLDBPool1′
GO

– Full restore with no recovery (status will be Restoring)
RESTORE DATABASE [SQLDBPool1] FROM DISK = N’D:\SQLDBPool.bak’
WITH FILE = 1, MOVE N’SQLDBPool’ TO N’D:\SQLDBPooldata.mdf’,
MOVE N’SQLDBPool_Log’ TO N’D:\SQLDBPoolLog_1.ldf’,
NORECOVERY, NOUNLOAD, STATS = 10
GO

– Restore transaction log with recovery
RESTORE LOG [SQLDBPool1] FROM DISK = N’D:\SQLDBPoolLog.trn’
WITH FILE = 1, NOUNLOAD, RECOVERY STATS = 10
GO

–Script to bring the database online without restoring log backup
restore database sqldbpool with recovery

–Script to detach database
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N’SQLDBPool’
GO

– Script to get the database information
sp_helpdb ‘SQLDBPOOL’

–to Attach database
USE [master]
GO

CREATE DATABASE [SQLDBPool1] ON
( FILENAME = N’C:\SQLDBPool.mdf’ ),
( FILENAME = N’C:\SQLDBPool_Log.ldf’ )
FOR ATTACH
GO

USE SQLDBPool
GO

– Get Fragmentation info for each non heap table in SQLDBPool database
– Avg frag.in percent is External Fragmentation (above 10% is bad)
– Avg page space used in percent is Internal Fragmention (below 75% is bad)

SELECT OBJECT_NAME(dt.object_id) AS ‘Table Name’ , si.name AS ‘Index Name’,
dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent
FROM
(SELECT object_id, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(‘SQLDBPool’), NULL, NULL, NULL, ‘DETAILED’)
WHERE index_id <> 0) AS dt
INNER JOIN sys.indexes AS si
ON si.object_id = dt.object_id
AND si.index_id = dt.index_id
ORDER BY OBJECT_NAME(dt.object_id)

– Script to Get Fragmention information for a single table
SELECT TableName = object_name(object_id), database_id, index_id, index_type_desc,avg_fragmentation_in_percent, fragment_count, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(N’SQLDBPool’),OBJECT_ID(N’mySQLDBPool.Emp’), NULL, NULL , ‘LIMITED’);

–script to get the index information
exec sp_helpindex [mySQLDBPool.Emp]

–Script to Reorganize an index
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE
GO

– Rebuild an index (offline mode)
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,STATISTICS_NORECOMPUTE = ON);

–Script to find which columns don’t have statistics
SELECT c.name AS ‘Column Name’
FROM sys.columns AS c
LEFT OUTER JOIN sys.stats_columns AS sc
ON sc.[object_id] = c.[object_id]
AND sc.column_id = c.column_id
WHERE c.[object_id] = OBJECT_ID(‘mySQLDBPool.Emp’)
AND sc.column_id IS NULL
ORDER BY c.column_id

– Create Statistics on DOB column
CREATE STATISTICS st_BirthDate
ON mySQLDBPool.Emp(DOB)
WITH FULLSCAN

– When were statistics on indexes last updated
SELECT ‘Index Name’ = i.name, ‘Statistics Date’ = STATS_DATE(i.object_id, i.index_id)
FROM sys.objects AS o WITH (NOLOCK)
JOIN sys.indexes AS i WITH (NOLOCK)
ON o.name = ‘Emp’
AND o.object_id = i.object_id
ORDER BY STATS_DATE(i.object_id, i.index_id);

– Update statistics on all indexes in the table
UPDATE STATISTICS mySQLDBPool.Emp
WITH FULLSCAN

– Script to shrink database
DBCC SHRINKDATABASE(N’SQLDBPool’ )
GO

– Shrink data file (truncate only)
DBCC SHRINKFILE (N’SQLDBPool_Data’ , 0, TRUNCATEONLY)
GO

– Script to shrink Shrink data file – Very Slow and Enhances the fragmentation
DBCC SHRINKFILE (N’SQLDBPool_Data’ , 10)
GO
– Script Shrink transaction log file
DBCC SHRINKFILE (N’SQLDBPool_Log’ , 0, TRUNCATEONLY)
GO

– Script to create view
CREATE VIEW emp_view
AS
SELECT *
FROM mySQLDBPool.emp

–rename table name Old Table Name and New Table Name
EXEC sys.Sp_rename
‘empl’,
‘empInfo’