.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

clean up system tables like sysmergepublications , sysmergesubscriptions...

Posted By:      Posted Date: September 18, 2010    Points: 0   Category :Sql Server
Hi ALL,            How to clean up system tables like sysmergepublications , sysmergesubscriptions... Bcoz  in my subscriber database i still see the previosous publihser details in these system tables Same thing in publisher database....how to remove all these data .....when i reinitialize database at subscriber ..and if i see the local subscriptions in ssms there are subscriptions from publishers to which it is subscribed before..and i cant delete this....

View Complete Post

More Related Resource Links

merge replication system tables fragmentation issue

I have Merge replication going on in my environment. The problem is that system tables like MSmerge_content, MS merge _tombstone and MSmerge_genhistory are getting fragmented. We are rebuilding indexes time to time daily. But we want some good fix. Just send some good idea guys?

Minimum Permissions Needed To See Cdc tables In System Tables Folder

What are the minimum permissions needed for a user to see the cdc tables in the System Tables folder in SQL Management Studio?DJ Baby Anne's Biggest Fan................

Getting server non system databases, getting database tables, getting table columns


I use next code to get server databases

string connectionString = "Data Source=DENIS\\SQLEXPRESS;Integrated Security=SSPI;";
DataTable tables = new DataTable("Tables");
using (SqlConnection connection =
    new SqlConnection(connectionString))
  SqlCommand command = connection.CreateCommand();
  command.CommandText = "sp_databases"; 
  command.CommandType = CommandType.StoredProcedure;


this.dataGridView1.DataSource = tables;

I've got many databases :

- model ( i guess it is system )

- master ( i guess it is system )

- tempdb ( i guess it is system )

- db1 ( my table )

and etc.

How to determinate what table is system.



FROM information_schema.Tables

So how to determinate what table is system or not.

Can I get tables of database

SSIS : Pulling large number of tables from Source system



we are migrating a datawarehouse application from asp - sql 2000 to dotnet2.0 - sql 2005
currently we are in the process of coming up with an optimum solution for backend design using sql server 2005.
This application pulls data from 3 different source systems (oracle,sql server and mainframe db2).

we have 3 different staging databases corresponding to each of the source application database.
For eg : RetailGarments application is an oltp application with backend oracle database .
we have a staging SQL server database called Retailgarments_stg for our datawarehouse application.
similarly OnlineTravelBooking is an OLTP application with DB2 database .we have corresponding
OnlineTravelBooking_Staging database for staging this data.

Each of these source systems have more than 100 tables and we are currently pulling most of the
tables to our side from the source .

These staging data from different datasources are accumulated in to a cleansed Schema database
which is used for Reporting and other OLAP requirements.

Our question is related to data pull from the source system.
Current SQL Server database pulls these data from sorce system using Stored proc dynamic queries containing
link server openquery.

We would like to improve the performance as part of sql server 2000 to

user access to system tables



I've some developers who want to be able to select from some system tables. I'm reluctant to give this in case we get any locking issues. As an alternative I'm thinking of creating views with nolock hints, and granting them access to this. e.g.

CREATE VIEW vw_sysjobs
select * FROM sysjobs WITH(NOLOCK)		

Is there a better way of doing this ? Am I right to asume that locking would be a potential problem on these tables ?

(SQL Server 2005)






SQL Server 2005 SP4 Install Fails - error log references replication system tables


The OS is Windows Server 2003 R2 SP2 (32 bit).  Trying to install SQL Server 2005 SP4 (current version is 9.00.4035).  All SQL products update without error, except for the Database engine.  The error listed in the summary.txt file in C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix is:

Product                   : Database Services (MSSQLSERVER)
Product Version (Previous): 5000
Product Version (Final)   :
Status                    : Failure
Log File                  : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB2463332_sqlrun_sql.msp.log
Error Number              : 29537
Error Description         : MSP Error: 29537  SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]Update or insert of view or function 'dbo.syspublications' failed because it contains a derived or constant field.. To continue, correct the problem, and then run SQL Serv

Is there no need to manually clean up merge replication system table?



In my previous post, Hilary replied that the size of MSmerge_Contents doesn't really matter, but index fragmentation does. However, referring to BOL, it says, "Additionally, you should minimize the size of the merge system tables (specifically MSmerge_history) by using sp_mergecleanupmetadata." Is it still necessary to do that in SQL Server 2005? 

Also, when running select metadatacleanuptime from sysmergesubscriptions, I found that the cleanup time is just about a week before. However, my merge interval is only 5 minutes, and I remembered during last week I did a manual sp_mergemetadataretentioncleanup although paused in half way. How to check the clean up interval?

Last but not least, I have already did index rebuild on MSmerge_Contents, MSmerge_henhistory, and MSmerge_tombstone, and found that the average fragmentation dropped from above 90% (MSmerge_Contents) to below 1%, but the overall performance did not improve obviously. I remembered when merge replication was set up at the begining, th

On rebuilding indexes of MSmerge_Contents and other replication system tables



In my previous question leads me to the index rebuild of MSmerge_Contents system table. However, I am still not sure what the explicit and correct way to rebuild index is. 

Refering to examples in BOL, the syntax should be something like the following:

ALTER INDEX ALL ON msmerge_contents

After checking system.indexes, I found that the fill_factor of 4 indexes of msmerge_contents are all 0. Does this mean I do not need to specify the value of FILLFACTOR? How about another 2 parameters, i.e. SORT_IN_TEMPDB and STATISTICS_NORECOMPUTE?

Do I need to specify ot

Clean Web.Config Files (VS 2010 and .NET 4.0 Series)

.NET 4 includes a new version of the CLR, and a new .NET 4 specific machine.config file (which is installed side-by-side with the one used by .NET 2, .NET 3 and .NET 3.5).

The new .NET 4 machine.config file now automatically registers all of the ASP.NET tag sections, handlers and modules that we've added over the years, including the functionality for:

.ASP.NET Dynamic Data
.ASP.NET Routing (which can now be used for both ASP.NET WebForms and ASP.NET MVC)
.ASP.NET Chart Control (which now ships built-into ASP.NET V4)
What this means is that when you create a new "Empty ASP.NET application" project in VS 2010, you'll find that the new default application-level web.config file is now clean and simple:

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

System Development Life Cycle

The Systems Development Life Cycle (SDLC) is a conceptual model used in project management that describes the stages involved in an information system development project from an initial feasibility study through maintenance of the completed application. Various SDLC methodologies have been developed to guide the processes involved including the waterfall model (the original SDLC method), rapid application development (RAD), joint application development (JAD), the fountain model and the spiral model. Mostly, several models are combined into some sort of hybrid methodology

The Agile System Development Life Cycle (SDLC)

The goal of this article is to describe the agile system development life cycle (SDLC), putting it in context from what you may have heard about within the agile community and more importantly within the context of your overall IT efforts.

Performance Tests: Precise Run Time Measurements with System.Diagnostics.Stopwatch

Everybody who does performance optimization stumbles sooner or later over the Stopwatch class in the System.Diagnostics namespace. And everybody has noticed that the measurements of the same function on the same computer can differ 25% -30% in run time. This article shows how single threaded test programs must be designed to get an accuracy of 0.1% - 0.2% out of the Stopwatch class. With this accuracy, algorithms can be tested and compared.

Temporary Tables - MS SQL Server

Usage of temporary tables in MS SQL Server is more developer friendly and they are widely used in development. Local temporary tables are visible only in current session while global temporary tables are visible across all sessions.

Temporary tables in SQL Server vs. table variables

When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables.



hello i have the following problem

i have upload my content to hosting server but i get the following error

Security Exception

Description: The application attempted to perform an operation not allowed by the security policy.  To grant this application the required permission please contact your system administrator or change the application's trust level in the configuration file.

Exception Details: System.Security.SecurityException: Request for the permission of type 'System.Web.AspNetHostingPermission, System, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SecurityException: Request for the permission of typ

Problem upgrading to MVC3 - System.Web.Routing



I'm trying to upgrade to .NET 4 and MVC 3.

When trying to compile my project, I get the error:

Error 1 The type or namespace name 'Routing' does not exist in the namespace 'System.Web' (are you missing an assembly reference?) C:\Users\spencer\Desktop\weblauncher\Common\MVCConfig.cs 4 18 Common

The type or namespace name 'Routing' does not exist in the namespace 'System.Web' (are you missing an assembly reference?)

I do have the System.Web.Routing in my references for the project.

BUT - when I try to view System.Web.Routing in the OBJECT BROWSER, nothing is inside!  The System.Web.Routing namespace is not in there - the entire thing is empty!  Could it that the dll is corrupted?  I tried to reinstall the .NET 4 and MVC 3 stuff, but its still empty!

The dll is coming from here:

C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\System.Web.Routing.dll

and the runti

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend