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

Using Backslash (“\”) in SSIS Expressions

I wanted build an expression to make a folder name dynamically. (Basically expressions are anyway dynamic in nature)

So I simply used the “\” as end of my expression as stated below;

@[User::Differential_backup_path] + @[User::database_name] + "Differential\"

It gave an error;

Then I tried various methods including CHAR(47) which is an ASCII value for the backslash.

After spending some time on research in google, I found that you need to use two backslashes. (“\\”)

So the correct expression would be;

@[User::Differential_backup_path] + @[User::database_name] + "Differential\\"

Identify out of synch subscriptions

One of the main tasks of DBA is to monitor replication status. There may be situations where subscriptions may out of synch due to various reasons. If this happened, DEV and QA will face issues and they will start communicate with DBAs to verify the replication status. If you’ve not setup proper monitoring mechanism to see replication staus this is the usual story.


However if you have already setup monitoring mechanism the DBAs will identify the issues upfront without making panic on DEVs and QAs.

This blog is on how to identify out of synch subscriptions especially in transactional replication.