DBA WORLD: Log Shipping Tables and Stored Procedures

DBA WORLD: Log Shipping Tables and Stored Procedures

Log Shipping Tables and Stored Procedures

Primary Server Tables

---

Table	Description
log_shipping_monitor_alert	Stores alert job ID. This table is only used on the primary server if a remote monitor server has not been configured.
log_shipping_monitor_error_detail	Stores error detail for log shipping jobs associated with this primary server.
log_shipping_monitor_history_detail	Stores history detail for log shipping jobs associated with this primary server.
log_shipping_monitor_primary	Stores one monitor record for this primary database.
log_shipping_primary_databases	Contains configuration information for primary databases on a given server. Stores one row per primary database.
log_shipping_primary_secondaries	Maps primary databases to secondary databases.

Primary Server Stored Procedures

---

Stored Procedure	Description
sp_add_log_shipping_primary_database	Sets up the primary database for a log shipping configuration, including the backup job, local monitor record, and remote monitor record.
sp_add_log_shipping_primary_secondary	Adds a secondary database name to an existing primary database.
sp_change_log_shipping_primary_database	Changes primary database settings including local and remote monitor record.
sp_cleanup_log_shipping_history	Cleans up history locally and on the monitor based on retention period.
sp_delete_log_shipping_primary_database	Removes log shipping of primary database including backup job as well as local and remote history.
sp_delete_log_shipping_primary_secondary	Removes a secondary database name from a primary database.
sp_help_log_shipping_primary_database	Retrieves primary database settings and displays the values from the log_shipping_primary_databases and log_shipping_monitor_primary tables.
sp_help_log_shipping_primary_secondary	Retrieves secondary database names for a primary database.
sp_refresh_log_shipping_monitor	Refreshes the monitor with the latest information for the specified log shipping agent.

Secondary Server Tables

---

Table	Description
log_shipping_monitor_alert	Stores alert job ID. This table is only used on the secondary server if a remote monitor server has not been configured.
log_shipping_monitor_error_detail	Stores error detail for log shipping jobs associated with this secondary server.
log_shipping_monitor_history_detail	Stores history detail for log shipping jobs associated with this secondary server.
log_shipping_monitor_secondary	Stores one monitor record per secondary database associated with this secondary server.
log_shipping_secondary	Contains configuration information for the secondary databases on a given server. Stores one row per secondary ID.
log_shipping_secondary_databases	Stores configuration information for a given secondary database. Stores one row per secondary database.

Note
Secondary databases on the same secondary server for a given primary database share the settings in the log_shipping_secondary table. If a shared setting is altered for one secondary database, the setting is altered for all of them.

Secondary Server Stored Procedures

---

Stored Procedure	Description
sp_add_log_shipping_secondary_database	Sets up a secondary database for log shipping.
sp_add_log_shipping_secondary_primary	Sets up the primary information, adds local and remote monitor links, and creates copy and restore jobs on the secondary server for the specified primary database.
sp_change_log_shipping_secondary_database	Changes secondary database settings including local and remote monitor records.
sp_change_log_shipping_secondary_primary	Changes secondary database settings such as source and destination directory, and file retention period.
sp_cleanup_log_shipping_history	Cleans up history locally and on the monitor based on retention period.
sp_delete_log_shipping_secondary_database	Removes a secondary database and the local history and remote history.
sp_delete_log_shipping_secondary_primary	Removes the information about the specified primary server from the secondary server.
sp_help_log_shipping_secondary_database	Retrieves secondary database settings from the log_shipping_secondary, log_shipping_secondary_databases, and log_shipping_monitor_secondary tables.
sp_help_log_shipping_secondary_primary	This stored procedure retrieves the settings for a given primary database on the secondary server.
sp_refresh_log_shipping_monitor	Refreshes the monitor with the latest information for the specified log shipping agent.

Monitor Server Tables

---

Table	Description
log_shipping_monitor_alert	Stores alert job ID.
log_shipping_monitor_error_detail	Stores error detail for log shipping jobs.
log_shipping_monitor_history_detail	Stores history detail for log shipping jobs.
log_shipping_monitor_primary	Stores one monitor record per primary database associated with this monitor server.
log_shipping_monitor_secondary	Stores one monitor record per secondary database associated with this monitor server.

Monitor Server Stored Procedures

---

Stored Procedure	Description
sp_add_log_shipping_alert_job	Creates a log shipping alert job if one has not already been created.
sp_delete_log_shipping_alert_job	Removes a log shipping alert job if there are no associated primary databases.
sp_help_log_shipping_alert_job	Returns the job ID of the alert job.
sp_help_log_shipping_monitor_primary	Returns monitor records for the specified primary database from the log_shipping_monitor_primary table.
sp_help_log_shipping_monitor_secondary	Returns monitor records for the specified secondary database from the log_shipping_monitor_secondary table.

2 JoBS - Replication

Distribution clean up

If a subscription is not synchronized within the maximum distribution retention period (default of 72 hours) and there are changes in the distribution database that have not been delivered to the Subscriber, the subscription will be marked deactivated by the Distribution clean up job that runs on the Distributor. The subscription must be reinitialized.

Expired subscription clean up

If a subscription is not synchronized within the publication retention period (default of 336 hours), the subscription will expire and be dropped by the Expired subscription clean up job that runs on the Publisher. The subscription must be recreated and synchronized.

If a push subscription expires, it is completely removed, but pull subscriptions are not. You must clean up pull subscriptions at the Subscriber.

Rolling Back TRUNCATE TABLE

TSQL Tuesday #11:Rolling Back TRUNCATE TABLE

“It ain't what people don't know that hurts them it's what they know that ain't so”
-- commonly attributed to Mark Twain

SQL Server has been around for a long time now, and people who learn one version well sometimes have monumental task when a new version comes out. They need to figure out what changed. The "What's New" documentation usually gives a good idea of the major new features, but sometimes when a more minor feature just change a bit, it's may get overlooked in all the hoopla of learning about the BIG new stuff.

One change that occurred very long ago that many people still haven't hooked into is that fact that a TRUNCATE TABLE operation can be rolled back! Even in my advanced classes, with some of the best DBAs out there, when I start talking about TRUNCATE TABLE, there are frequently one or two people who think it is unlogged and thus can't be rolled back.

Let me state now… THIS IS NOT TRUE.

And in fact, it is very easy to prove that it is not true… but if you don't suspect that it is not true, why would you even test it?

TRUNCATE TABLE is logged, but SQL Server doesn't log every single row as it TRUNCATEs the table, so you might think that this command falls into the category of commands we call 'minimally logged'. But although indeed minimal information is logged for this operation, it is not technically in that category. The official 'minimally logged' operations also cause a bit to be set in a big bit map, so that when you are running in BULK_LOGGED recovery model and make a log backup, SQL Server can capture all the data involved in 'minimally logged' operations into the transaction log. This allows the data to be recovered when restoring from backup.

But for TRUNCATE TABLE, SQL Server only logs the fact that the operation happened, along with information about the pages and extents that were deallocated. So that's minimal information. And by recording which pages and extents were deallocated, there's enough information to roll back, by just REallocating those pages. A log backup only needs the information that the TRUNCATE TABLE occurred. And to restore the TRUNCATE TABLE, the operation is just reapplied. The data involved is not needed during RESTORE (like it would be for a true 'minimally logged' operation like a BULK INSERT).

So what if the transaction in which the TRUNCATE TABLE occurs stay open for a long time? How does SQL Server know not to reuse the pages that belonged to the table? It turns out the pages and/or extents involved are locked with an eXclusive lock, and just like all X locks, they are held until the end of the transaction. And as long as the pages or extents are locked, they can't be deallocated, and certainly cannot be reused.

So here's some code to see for yourself. I'll make a copy of a table from the AdventureWorks database:

USE AdventureWorks
GO

IF EXISTS (SELECT * FROM sys.tables
WHERE name = 'Products'
AND schema_id = 1)
DROP TABLE dbo.Products;
GO

SELECT * INTO dbo.Products
FROM Production.Product;
GO

Now we'll look at the count of rows, and the pages that belong to the table.

SELECT COUNT(*) FROM dbo.Products;
GO
DBCC IND('AdventureWorks', 'Products', -1);
GO

We get a count of 504 rows and the following pages:

File Page
----- -------
1 23027
1 23029
1 23030
1 26992
1 26993
1 33352
1 33353
1 33354
1 33355
1 33356
1 33357
1 42486
1 42487
1 42488
1 42489

Next, start a transaction and truncate the table. Verify that DBCC IND shows no pages belonging to the table and the count is 0. Then, look at the locks.

BEGIN TRAN
TRUNCATE TABLE dbo.Products;
SELECT COUNT(*) FROM dbo.Products;

DBCC IND('AdventureWorks', 'Products', -1);
DBCC EXTENTINFO('AdventureWorks', 'Products', -1);

SELECT resource_type, resource_description,
request_mode FROM sys.dm_tran_locks
WHERE resource_type IN ('EXTENT', 'PAGE')
AND resource_database_id = DB_ID('AdventureWorks');

You should see no rows from DBCC IND, and 0 rows from count(*). But the locks info should return something like:

resource_type resource_description request_mode
------------- -------------------- ------------
EXTENT 1:33352 X
PAGE 1:42486 X
EXTENT 1:42488 X
PAGE 1:42487 X
PAGE 1:42488 X
PAGE 1:42489 X
PAGE 1:23027 X
PAGE 1:23030 X
PAGE 1:23029 X
PAGE 1:26992 X
PAGE 1:26993 X

So the extent and page locks include all the pages that we saw in the DBCC IND output. (Remember, an extent is 8 contiguous pages, starting at the one that describes the extent.)

Only after you ROLLBACK the transaction will the locks be released, and you should see all the rows and pages back in the table again.

ROLLBACK TRAN;
GO
SELECT COUNT(*) FROM dbo.Products;
DBCC IND('AdventureWorks', 'Products', -1);
GO

So now you know!

~Kalen

Posted Tuesday, October 12, 2010 2:02 PM by Kalen Delaney | 9 Comments Filed under: internals, Transactions, locking, T-SQL Tuesday

Peer 2 Peer Replication - Step By Step

Download the corresponding Document from the below link :)

http://www.megaupload.com/?d=J4V98265

PaisaLive

Earn upto Rs. 9,000 pm checking Emails. Join now!

Install SQL from Script

Install SQL from Script


Script 1:-

http://www.megaupload.com/?d=AN5UQOGN


Script 2:-

http://www.megaupload.com/?d=F3LQIVNZ


Thanks To :- http://www.databasejournal.com/features/mssql/article.php/3672926/Scripting-the-Installation-of-SQL-Server-2005.htm