Monday, July 28, 2008

Cannot delete a job which is related to MP?

Hi folks
If you are using SQL Server 2005 (SP) and used to build Maintanace Plans especially with adding subplans so you are probable seen the folowing error message
/*
Drop failed for Job ‘jobname’. (Microsoft.SqlServer.Smo)

The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘job_id’.
The statement has been terminated. (Microsoft SQL Server, Error: 547)
*/

So if you create a MP,SQL Server will create a job and SSIS which is refernced to the subplan as well as inserts the data into system tables in msdb database. (sysmaintplan_subplans,sysjobs_view,sysjobschedules).

Intresting is that if you execute a job it makes more insert into log table called sysmaintplan_log. All of these tables are linked through FK and PK relationships. The problem is when you try to delete a job it gives a Foreign Key errors until you manually remove those entries by the SQL tables.

Please see the link written by Jonas Kempas http://gudenas.com/2007/04/20/sql-server-2005-delete-maintenance-plan-error/ explains step by step how to delete not associated jobs.

I tried it and it worked just fine.

Wednesday, July 2, 2008

How do we open a large table?

This question raised when we worked at the client's side by one of developers. They use SQL Server 2005 (SP2) and remembered that in SQL Server 2000 we can open the table via EM and then specify (All rows , Top rows...) do you remember?

I rarely use SSMS to open/edit tables data, and it seems that MS just removed this option. In SQL Server 2005 we have TABLESAMPLE clause that used to open a table with lots of rows. So we only needed to see what kind of data this psecific table has without open entire table or using TOP clause.

This example returns an approximate percentage of rows and generates a random value for each physical 8-KB page in the table.

SELECT * FROM table
TABLESAMPLE system(5 PERCENT)

I'd really advise you to read BOL about this great feature
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8868e8fd-6c42-4171-9eab-a0e38cb1bfd3.htm