SQLSERVERBASE.COM Run with SQL Server DBA and BI

DMV is dynamic management view. It gives us an insight of SOL server. Its a collection of system tables name start with dm_.

I will have detailed explanation of DMV in subsequent article.

Hi All,

This is my performance counter test blog

Your

Query to enable/disable sql jobs

-->> Query to enable/disable sql jobs

exec msdb..sp_update_job@job_id = 'PassJobIDHere' , @enabled = 0  

exec msdb..sp_update_job@job_name = 'PassJobNameHere' , @enabled = 1

Query to find all the Log Shipping copy job which is disable, to verify

-->> Query to find all the Log Shipping copy job which is disable, to verify

select * from msdb..sysjobs  

where name like '%LSCopy%TESTDB%' and enabled=1

Query to find all the Log Shipping restore job which is enable/disable, to verify

-->> Query to find all the Log Shipping restore job which is enable/disable, to verify

select * from msdb..sysjobs 

where name like '%LSrestore%TESTDB%' and enabled=1

Query to find all the Log Shipping backup job which is disable, to verify

-->> Query to find all the Log Shipping backup job which is disable, to verify

select * from msdb..sysjobs 

where name like '%LSBackup_TESTDB%' and enabled=0

Dynamic Query to generate script to disable autogrowth of all the files of specific drive

-->> Dynamic Query to generate script to disable autogrowth of all the files of specific drive

select ' alter database ' + DB_NAME() + ' MODIFY FILE ( NAME = ''' + df.name + ''' , FILEGROWTH = 0 )'

FROM sys.database_files df LEFT OUTER JOIN sys.data_spaces ds 

ON df.data_space_id = ds.data_space_id

where physical_name  like 'K%' --and physical_name not like '%mdf%' and physical_name not like '%ldf%'

and growth <>0

-->> Dynamic Query to generate script of Add Datafile of all the datafile of specific drive

select ' alter database '+DB_NAME()+' ADD FILE ( NAME = '''+ df.name +'_1 '' , FILENAME = '''+  replace(REPLACE( physical_name ,'.ndf','_1.ndf'),'K:','I:')+''' , SIZE = 4096KB , FILEGROWTH = 1024KB ) TO FILEGROUP ['+ds.name +']'

FROM sys.database_files df LEFT OUTER JOIN sys.data_spaces ds  ON df.data_space_id = ds.data_space_id

where physical_name  like 'K%'--and physical_name not like '%mdf%' and physical_name not like '%ldf%'

and growth <> 0

select*fromsys.dm_os_cluster_nodes

selectserverproperty('ComputerNamePhysicalNetBIOS')

Query to get Drive Total and Free Space

-->> Query to get Drive Total and Free Space

SELECT f.database_id,DB_NAME(f.database_id),f.file_id,f.growth,f.name, f.physical_name,x.volume_mount_point, x.total_bytes/1024/1024/1024 as TotalSpaceGB, x.available_bytes/1024/1024/1024 as AvailableSpaceGB FROM sys.master_files AS f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) x where (DB_NAME(f.database_id) like 'TESTDB%' or SUBSTRING(f.physical_name,1,3) in(select SUBSTRING(physical_name,1,3) from sys.master_files where DB_NAME(database_id) like 'TESTDB%') ) and growth <> 0 order by 2

Query to find total log fie size

-->> query to find total log fie size

select db_name(database_id), name, sum(size)/128 from sys.master_files where db_name(database_id) like 'TESTDB%' and type=1 group by db_name(database_id), name

select db_name(database_id), name, size/128, physical_name from sys.master_files where db_name(database_id) like 'TESTDB%' and type=1

Below is the script to add free space in datafile or logfile. You just need to pass database name, datafile name, log file name, space to add, and total space. 

------------------>>>>>>>>>>>>>>>>>>>>>>>>>>>>

declare @databaes varchar(100), @datafile varchar(250), @Current_size int, @SpaceBunch int, @sql varchar(500)

declare @TotalSpaceToAdd int

set @databaes='TESTDB' -->> Pass Database name here

set @datafile= 'TESTDB_Log_2'   -->> Pass Logical Name of data/log file

set @SpaceBunch=20   -->> It will add THIS much of space of chunck

set @TotalSpaceToAdd=100 -->> It will add space till THIS much, and will starts from current size+@SpaceBunch

select @Current_size=size/128 from sys.master_files where db_name(database_id)=@databaes and name=@datafile

print 'Space Before Addition ' + convert(varchar,@Current_size/1024) -->> It will print current size in GB

while @Current_size/1024 <@TotalSpaceToAdd

begin

 set @sql='ALTER DATABASE [' + @databaes + '] MODIFY FILE ( NAME = N''' + @datafile + ''''+ ', SIZE = ' + convert(varchar,@Current_size+ @SpaceBunch*1024) + 'MB )'

 print @sql

 exec(@sql)

 select @Current_size=size/128 from sys.master_files where db_name(database_id)=@databaes and name=@datafile

 print 'Space After Addition ' + convert(varchar,@Current_size/1024)  -->> It will print new current size in GB after space addition

end

------------------>>>>>>>>>>>>>>>>>>>>>>>>>>>>

create procedure sp_READONLY_READWRITE @dbname varchar(50) --NULL=all DBs other than mast, pass database name for sigle,
, @RW int -- 0=READONLY and 1=READWRITE
as
BEGIN
 declare @name varchar(50)
 declare @sql varchar(250)
 select @sql= 'ALTER DATABASE ' + @name + ' SET READ_ONLY WITH NO_WAIT'
-- print @dbname
 if @dbname is null
  DECLARE db_cursor CURSOR

Below query fetch all the sql jobs and its schedule:

SELECT j.name as jobname, js.next_run_date as ScheduleDate, js.next_run_time as ScheduleTime

FROM msdb.dbo.sysjobs AS j INNER JOIN -- sysjobs talbe whch hold job details

msdb.dbo.sysjobschedules AS js ON j.job_id=js.job_id -- sysjobschedules table to fech Schedule details

WHERE j.enabled= 1 -- All enabled Job Only

ORDER BY js.next_run_date desc

There are two way to rename SQL Server database

1. Using sp_renameDB stored procedure and

2. by Using ALTER DATABASE command 

1. sp_renamed

EXEC sp_renameDB 'oldDBName','newDBName'

2. Alter database

--Rename the Database <OldDBName> to <NewDBName>
ALTER DATABASE <OldDBName> MODIFY NAME = <NewDBName>
GO

How to start SQL Server in single user mode in SQL Server 2008.

Step 1: Open SQL Server configuration manager.

Step2: On Left pan, point to “SQL Server Services”

Step3: Then, In right pan select “SQL Server(servername)”.

C:\>net user /DOMAIN <username>

C:\>net user <username> /DOMAIN

This command will list you complete information of a user like:

User name                    <username>

Full Name                   
Comment
User's comment
Country/region code          000 (System Default)
Account active               Yes
Account expires              Never

SQL is 5th generation language.