#sql server agent

Learn how to retrieve SQL Server Agent job schedule details with a powerful query. Perfect for auditing, troubleshooting, or documenting your SQL environment. Check it out and simplify your DBA tasks!

Retrieving SQL Agent Job Schedule Details in SQL Server If you’re working with SQL Server Agent jobs, it’s often useful to have a quick way to retrieve schedule details for all jobs in your system. Whether you’re auditing schedules, troubleshooting overlapping jobs, or simply documenting your environment, having this query on hand can save you a lot of time. Below is a SQL query that returns…

Scheduled administrative tasks, referred to in SQL Server as jobs, are carried out by SQL Server Agent which is a crucial component of SQL Server and is a Microsoft Windows service. Using SQL Server Agent, we can schedule and automate database and server management activities. The following query will assist in determining which jobs were generated and scheduled for automatic execution by SQL…

New Post has been published on http://www.varindersandhu.in/2014/03/07/sql-server-2012-get-backup-history-database/

SQL Server 2012 - Get Backup History of the database

The msdb database is system database used by SQL Server Agent for scheduling alerts and jobs.

msdb.dbo.backupset – Contain the information concerning the most-granular details of the backup process msdb.dbo.backupmediafamily – Contains the metadata for the physical backup files as they relate to backup sets

<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->

Using the above mentioned tables, we will find the backup history of database

Script:

SELECT s.database_name, m.physical_device_name, CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS Backup_Size, CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken, s.backup_start_date, CASE s.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END AS BackupType, s.server_name, s.recovery_model FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id WHERE s.database_name = DB_NAME() -- Remove this where clause for all database ORDER BY backup_start_date DESC, backup_finish_date GO

New Post has been published on http://www.varindersandhu.in/2011/08/12/sql-serevr-replication-setup-and-configure-subscription-on-sql-2008r2-part-4/

SQL Serevr Replication - Setup and configure Subscription on SQL 2008R2 - Part 4 of 4

Setup and configure Subscription by www.varindersandhu.in

<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->

New Subscription wizard as below

Setup and configure Subscription by www.varindersandhu.in

Setup and configure Subscription by www.varindersandhu.in

Select the publication and then press “Next”

Next you have choose the subscriber and subscription database

Setup and configure Subscription by www.varindersandhu.in

Here we are choosing the Push subscription

Select Push subscription (First one) then press “Next”

Setup and configure Subscription by www.varindersandhu.in

Select Agent Schedule “Run Continuously” the press “Next”

Setup and configure Subscription by www.varindersandhu.in

Select Initialize when as “At first Synchronization”

Setup and configure Subscription by www.varindersandhu.in

Select option “Create the subscription”

Setup and configure Subscription by www.varindersandhu.in

Wait for the subscription process to finish.

Setup and configure Subscription by www.varindersandhu

Finally subscriber has been created.

New Post has been published on http://www.varindersandhu.in/2011/08/12/sql-serever-replication-setup-and-configure-the-publication-on-sql-server-2000-part-3/

SQL Serever Replication - Setup and configure the Publication on SQL Server 2000 - Part 3 of 4

In part 2, we have configured a SQL Server as a main publishing server, we have to create a publication of databases which the clients can subscribe.

Setup and configure the Publication by www.varindersandhu.in

Click on New Publication

Setup and configure the Publication by www.varindersandhu.in

“Create Publication Wizard” starts, check the “Show advanced options in this Wizard” checkbox and press “Next”

After please select your database and press “Next”

<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->

Setup and configure the Publication by www.varindersandhu.in

In the “Select Publication Type” screen select “Transactional publication” (second option) en press “Next”

Setup and configure the Publication by www.varindersandhu.in

Setup and configure the Publication by www.varindersandhu.in

On the “Specify Articles” Screen check all the checkboxes on the left, selecting all tables, and views as articles and press “Next”

Setup and configure the Publication by www.varindersandhu.in

Warning screen appears

Setup and configure the Publication by www.varindersandhu.in

Give the Publication a Name then Press “Next”

Setup and configure the Publication by www.varindersandhu.in

 Customize the properties of this publication, select “Yes” and press “Next”.

Setup and configure the Publication by www.varindersandhu.in

In the “filter data” screen we leave both specific record or column filtering should be applied on the replication data.

Setup and configure the Publication by www.varindersandhu.in

 In the “Allow Anonymous Subscriptions” choose “Yes” and press “Next”

Setup and configure the Publication by www.varindersandhu.in

 The “Set Snapshot Agent Schedule” screen defines a schedule how often and when initial snapshots (initial copies of the main database) should be created in the replication file share.

Check “Create the first Snapshot immediately” and press “Next”

Setup and configure the Publication by www.varindersandhu.in

 Wait for the publication process to finish.