Windows Powershell

Last few days I spent most of the time on my personal development to learn and understand about Windows Powershell; Microsoft new scripting language. I’ve not being used any scripting language extensively. But now I understand how much power will bring to a DBA by learning this kind of a scripting language. 

You may be thinking why do you need a scripting language while having most powerful user friendly GUIs in Windows. Ok. I also thought the same way before I learning Powershell.

There are couple of reasons why you should use Powershell. I’m sure there are many more;

Configure Windows Firewall for SQL Server 2008

You will find this article very useful if your planing to install SQL Server 2008 on Windows 2008 server. Once you have installed SQL Server and if you try to log into the server remotely, it will fail. I too have faced this issue recently. Configuration of Windows Firewall comes into action at this time. 

How to retrieve server information?

How do you get information (HW/SW) in a server? Have you ever wanted to get that information? Ok. You might say you can use, “dxdiag” in command prompt and it will give you ample of information. Of course you can use “dxdiag” but I’m going to present you a different way of getting that information.

How to create a procedure in all databases in a server?

I had a situation where I wanted to create a stored procedure in all databases for a specified SQL server.

I created a below code for that purpose.

SQL Azure Database

SQL Azure Database is Microsoft's cloud-based relational database system which is based on Microsoft SQL Server. Earlier it was called SQL Data Services. SQL Azure CTP 2 is now available for MSDN subscribers.


SQL Azure FAQ

SQL Azure Team Blog

View Jobs Schedule Information

The code portion given below is useful when you need to get the schedule information of SQL Server jobs. There are several system tables exist in msdb database to retrieve jobs related information. But in this scenario I wanted to see the jobs schedule description which displays in jobs properties UI.

The description is not stored in any system tables as is rather it will do a manipulation by using other values. 


It will use the system SP, sp_get_schedule_description   to prepare description. 



create table #temp_jobschedule
(
job_id uniqueidentifier
,schedule_id int
,schedule_name sysname
,[enabled] int
,freq_type int
,freq_interval int
,freq_subday_type int
,freq_subday_interval int
,freq_relative_interval int
,freq_recurrence_factor int
,active_start_date int
,active_end_date int
,active_start_time int
,active_end_time int
,date_created datetime
,schedule_description nvarchar(4000)
,next_run_date int
,next_run_time int
,schedule_uid uniqueidentifier
,job_count int

)




declare @Job_Id uniqueidentifier
declare @Last_Job_Id uniqueidentifier


select top 1 @Job_Id=J.job_id
from msdb..sysjobs j
where j.enabled=1
and j.category_id not in (10,15,13)
order by J.job_id


select top 1 @Last_Job_Id=J.job_id
from msdb..sysjobs j
where j.enabled=1
and j.category_id not in (10,15,13)
order by J.job_id desc


while @Job_Id is not null
begin


insert into #temp_jobschedule (schedule_id,schedule_name,[enabled],freq_type,freq_interval,freq_subday_type,freq_subday_interval,freq_relative_interval,freq_recurrence_factor
,active_start_date,active_end_date,active_start_time,active_end_time,date_created,schedule_description,next_run_date,next_run_time,schedule_uid,job_count)
exec sp_executesql N'exec msdb..sp_help_jobschedule @job_id=@JOB_ID,@include_description=1',N'@JOB_ID uniqueidentifier', @JOB_ID=@Job_Id

select top 1 @Job_Id=J.job_id
from msdb..sysjobs j
where j.[enabled]=1
and j.category_id not in (10,15,13)
and J.job_id>@Job_Id
order by J.job_id

if @Job_Id=@Last_Job_Id
set @Job_Id=null


end


update #temp_jobschedule set job_id=jsch.job_id
from msdb.dbo.sysjobschedules jsch
where #temp_jobschedule.schedule_id=jsch.schedule_id


select j.name,schedule_description,jsrv.last_run_duration
from #temp_jobschedule jsch
inner join msdb.dbo.sysjobs j
on jsch.job_id=j.job_id
inner join msdb.dbo.sysjobservers jsrv
on jsch.job_id=jsrv.job_id

Undocumented DBCC Commands

I found this article while searching about DBCC commands. This is really interesting one specially for the people who wants to know SQL Server internals. This article applies for SQL Server 7 and 2000 but I have used some DBCC commands in SQL Server 2005 too. (E.g: DBCC IND, DBCC PAGE)


Since these are undocumented onces you have to use at your risk and no support available from Microsoft.


Useful Undocumented DBCC Commands