Large Distribution Database With Few Row-count?

Feb 19, 2012

I have a distribution database that has grown over 50g. Distribution cleanup takes over 15 hours to run and there are always very few rows in msrepl_Commands. Usually only a few hundred. Everything is being replicated fine, but I fear a storm coming. why distribution would be so large with so few rows? Also, Ghost Cleanup has been running all week, so I'm thinking maybe the records marked for deletion aren't being deleted?

View 1 Replies


ADVERTISEMENT

Transact SQL :: How To Get Count From A Large Table

Jun 1, 2015

I have a table with a couple hundred billion records (sql server 2005). When I do a select count(*) from tblx -- it takes this side of forever. Is it possible to count partitions and then add them up to make it faster?  

How I could improve the performance for count(*) of this huge table.  Note:  if the partition idea sounds viable -- what would that look like? 

View 11 Replies View Related

Count Distinct In Large Table With 30M Records?

Jul 7, 2015

I have a task to count distinct records in a big table with roughly 30M records, performance is an important factor. Query is to be written to calculate weekly stats, weekly record number could be as high as 1M.

The actual result is like:
ID Policy
350235744Credit Cards
350235744PCI
350235744PCI Audit

So the final number for this particular Policy is 3

I can write the query like:

select count(distinct Incident_id) policy_name
from Reporting_DailyDlpDetail
Where (year(INSERT_DETECT_TS)=2015) and (month(INSERT_DETECT_TS) =6) and (day(INSERT_DETECT_TS) between 2 and 9)
This returns 526254 and costs 11 seconds to complete

or a query like:
Select distinct Incident_id, policy_name
from Reporting_DailyDlpDetail
Where (year(INSERT_DETECT_TS)=2015) and (month(INSERT_DETECT_TS) =6) and (day(INSERT_DETECT_TS) between 2 and 9)
This returns 749687 and costs roughly 1 minute to complete.

Result is different from the two queries, I believe the later gives correct number. How can I count the distinct based on a combo?Considering the size of data, what is the best and most efficient way to run the stats calculation against over 30 different scenarios (different policies and alert types) and not timeout?

View 9 Replies View Related

Fulltext Large (500.000) Count Query Performace Too Slow

Feb 14, 2008

Hi,

I am with the response time for a simple count on a fulltext search that is too slow.

Even using the most simple query on a good server (64 bit Dual Opteron 4GB Ram with high speed 16 raid disk storage)):

select count(*) from content_books where contains(searchData,'"english"')
Takes 4 seconds to count the avg 500.000 resultsI have removed all the joins with real table data so that the query is only inside the fulltext engine..

I would expect this to be down to 4 milli seconds. Isn't it just getting the size of the "english" word result index?

It seems the engine is going through all the results because if a do a more complex search that returns less results the performance is better.

Any clues of how to do this faster? I never read the thousands of records BUT i need to count them...

Thank you very much.

View 2 Replies View Related

Distribution Clean Up: Distribution Job Not Removing Snapshots

Jul 6, 2007

On SQL Server 2005 SP2 for Publisher and Distributor on the same instance, my old snapshots are not being cleaned up.



The following error is in the agent history:

Executed as user: DomainMyUser. Could not remove directory '\vmsql01ReplDatauncPublication_TRANSACTIONAL20070702104416'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed.



xp_cmdshell is enabled and I can run commands like :

exec master.dbo.xp_cmdshell ' md c:TestFolder'

The permissions to the snapshot share and file system are that DomainMyUser has full control.

I have logged into the machine as this user and can remove snapshots so it does not seem to be a permission issue.





On other machines I do not get any errors but the snapshot folder still is not cleaned up.

Any suggestion as to what the problem could be?



Thanks,

Amy

View 3 Replies View Related

Distribution Database

Jan 25, 1999

Hi,

I'm new to working with replication & I'm just starting to test some implementations. We're still using the beta, v7.00.517. and... basically nothing works, I can't even open any of the replication management features in Enterprise Manager. As soon as I select any item from the replication menu I get:

Error 208: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'msdb.dbo.MSdistributiondbs'

It's either:

A: a bug, B: something screwed up in my system databases, or C: I'm missing something really obvious.

Any insight would be much appreciated.

Gordo

View 2 Replies View Related

Dropping Distribution Database

May 2, 2000

i would like to drop a distribution database after disabling both susbscriber and publisher .but i get a message saying distribution database is still in use .and as far as i can tell its there is no one using it .also stopped sql agent but no luck . and when i do select @@ servername i get servername is null how do i correct it .

thanks

View 1 Replies View Related

Distribution Database For Replication

Mar 9, 1999

When setting up Replication in v. 6.5, a 'distribution' database is created, containing these tables:

MSjobs
MSjob_commands
MSjob_subscriptions
MSsubscriber_info
MSsubscriber_jobs
MSsubscriber_status

However, they cannot be accessed via the SQL Server Enterprise Manager interface like tables in other databases, at least I can't get to them. Is this a problem or a feature? I can query them to get their contents, but I'm wondering if there might be something wrong with my installation of SQL Server.

Any knowledge shared would be greatly appreciated.

Regards,

Karl Simanonok

View 1 Replies View Related

Quantum Distribution Database

Apr 5, 2012

I can't use the distribution database because it doesn't exist, and I can't create the distribution database because it already exists.

A failed replication set up had left a fat distribution database that caused the server CPU to peg 100% when I tried to set up a new Transactional replication (halfway through the wizard).

I removed all replication objects from SMSS, but the only thing that fixed it was deleting the distribution database with an ALTER to OFFLINE and a DROP, and restarting SQL.

Now, I'm unable to set up replication.

When I try, I get the error "Database 'distribution' does not exist. Make sure that the name is entered correctly."

In Distributor Properties, the Publisher is shown with a distribution database of 'distribution'. If I untick that, SQL asks if I want to disable the Publisher. I say yes & get the error "Database 'distribution' does not exist.'

Ok, so let's create it. In the General tab of that window I create a new Distribution database and call it 'distribution'. Then I get the error 'Could not add the distribution database 'distribution'. This distribution database already exists.

exec sp_helpdistributor shows distribution database as 'distribution'
SMSS doesn't show the distribution database at all.

How I can proceed?

View 14 Replies View Related

Suspected Distribution Database

Apr 8, 2004

We have a production SQL2K DB with publication.
I found replication stopped, distribution database was marked suspect
and log file is gowning.
I have tried using sp_resetstatus and restart server but recovery
said some IO errors reading distribution's datafile, so I guessed distribution database was totally dead.
I have handful documents telling me how to remove replication and
I am going to take that ride.
Before I do anything real,
is there any warning from you Prof people?
is there any trap I may encounter into?
is that OK to rebuild replication after I remove the dead replication?
highly appreciate for any help and comments.
thanks in advance!
-DW-

View 3 Replies View Related

Distribution Database 9GBs

Aug 2, 2007

What job cleans out the distribution database?

Cna you perform maintenance to keep the size down?

Thanks,
Thomas


ThomBeaux

View 5 Replies View Related

Moving Distribution Database - How?

Aug 8, 2007

Hello,

I have my distribution database at C drive, I have to move this database to another drive D.

May anybody tell me how do this secure, please.

This production environment so I can't make any mistakes.

--
Regards

View 4 Replies View Related

Moving The Distribution Database

Aug 3, 2005

Hi all,Is it possible to move the distribution database to a new folder/drivewithout removing replication? I am attempting to do it the same way youwould move tempdb:ALTER DATABASE distribution MODIFY FILE(name = distmodel, filename = 'C:DISTMOVEDdistribution.MDF')ALTER DATABASE distribution MODIFY FILE(name = distmodel_log, filename = 'C:DISTMOVEDdistribution.LDF')But I get -Server: Msg 5037, Level 16, State 1, Line 1MODIFY FILE failed. Do not specify physical name.Thanks,Josh

View 2 Replies View Related

Creating Distribution Database

Mar 14, 2008

I would like to use SQL Server Express with Advanced Services for both a centralized publication/distribution database and a local subscribing database for offline.

I need to configure the distribution database but do not see the "configure publishing and distribution" option while right-clicking on the Replication folder in SQL Server Management Studio Express.

How do I get that wizard to show? I do see the "New" -> "Subscription" wizard.

I am fairly new, any help would be greatly appreciated with configuring my centralized database. Thanks in advance!

View 9 Replies View Related

Help Regarding Backup Distribution Database

Oct 24, 2007



Hi all,

I have set up sql 2005 transactional replication .The environment here cannot accept latency so I guess I will have to go with sync db option at the distribution db.My question is what should be my distribution db back up strategy.Should it be scheduled after the backup of the publication db.I am confused.Somebody please help me regarding scheduling it.

Thanks,
SATYA

View 1 Replies View Related

Free Distribution Of Database Engine

May 3, 2006

Hi:
I'm an asp.net programmer and my database is in access format.My server is Windows Server 2003 Enterprise Edition Service Pack 1.I do not need microsoft access installed on my server to access my database through asp.net.
I want migrate my database to sql server 2005
Could I access my database in sql server 2005 format (mdf) through asp.net without sql server 2005 installed on the server?
could I buy to microsoft only "Database Engine"? orIs there a free distribution of "Database Engine"?
Thanks!!

View 2 Replies View Related

Newbie : Dump The Log On Distribution Database ?

Apr 12, 2001

Hi there,

I'm a newbie. I've installed a replication between 2 NT4 servers, with SQL 6.5. My logreader tasks has crashed "Can't allocate space for objects 'syslogs' in database 'distribution' etc...". With a DBCC SQLPERF(LOGSPACE) I can see that the log is 100% full, but I can't dump it : when I use DUMP TRAN DISTRIBUTION WITH NO_LOG, my distribution log is still 100% full. So how can I do to dump it and what can I do to prevent it from filling up ? Do I need to dump it like I do for my database but how could it work if a DUMP TRAN doesn't work ?

Thanks in advance.

Sorry for the English, it's not my native language.

View 2 Replies View Related

Size Of Distribution Database-Replication

Feb 5, 2004

Hello,

We are setting up Merge replication and size of the database is
85 GB. How much disk size is feasible to keep for distribution Database.
Is there any % basis (SIZE) for Distribution DB according to the size of the database?
Is it feasible to keep seperate server for Distribution Server or keeping
Publisher and distributor on same same.

Can any one help me!

Thanks!

View 3 Replies View Related

What Is The Use Of Resource And Distribution System Database

Dec 10, 2007

Hi All,Can anyone explain me, why we have Resource and Distribution SystemDatabase in SQL Server 2005. Whether they were also available inprevious versions also.Thanks in Advance.Regards,Om

View 1 Replies View Related

Move Distribution Database To Another Drive?

May 18, 2007

Is it possible to move a distribution database to another drive without removing replication? I have done some research but I getting mixed answers from Google searches.

Thanks in advance
Dave

View 3 Replies View Related

Distribution Database Log File Growth

Jan 11, 2007

SQL Server 2000 | Transactional Replication

Suspected Problem: Distribution Database Transaction Log Not Checkpointing

I have a distributor with a distribution database that keeps growing and growing (About 40 GB in 7 days). The database is using the SIMPLE recovery model but the log continues to accumulate data. I have spent time looking at articles such as: "Factors that keep log records alive" (http://msdn2.microsoft.com/en-us/library/ms345414.aspx) and the one thing that stands out is the Checkpoint. I noticed that I can run a manual checkpoint and clear the log. If the log records were still active, the checkpoint would not allow the log to be truncated. This leads me to believe that the server is not properly initiating checkpoints in the Distribution database even though Recovery Model = SIMPLE and the server Recovery Interval = 0.

I found this: "FIX: Automatic checkpoints on some SQL Server 2000 databases do not run as expected" (http://support.microsoft.com/kb/909369/en-us) but I suspect this is a followup to a problem that may have been introduced with SP4 (since SP4 is a requirement for the hotfix). I am running SP3a (Microsoft SQL Server 2000 - 8.00.850) so I don't think that is the issue. I have several other nearly identical servers with the same version and configuration that have properly maintained log files.

SP4 is not a good option for me at this point - the next upgrade will be to SQL 2K5.

Any thoughts?

Jeff

View 1 Replies View Related

SQL 2012 :: Move Distribution Database To A Different Server?

Sep 9, 2015

Is there a way to move the Distribution database (that is currently on the publisher server) to a different server without having to reync the subscriptions.

View 0 Replies View Related

Configure Distribution Database Wizard Fails

Mar 5, 2007

Ryan J. Boyle writes "I'm trying to set up repliction on my SQL 2005 instance. When I run through the "Configure Distribution Wizard" the wizard errors out, here is the following error message that I'm reciving. I can not find any information based on that SQL Server Error number (there is none in the help file go figure)

ERROR MESSAGE:

SQL Server could not configure <servername> as a Distributor

Additional Information:

An exception occurred while executing a Transact-SQL statement or batch Microsoft.SqlServer.ConnectionInfo

Only replication jobs or job schedules can be added, modiifed, dropped or viewed through replication stored procedures. Changed database context to 'master'(Microsoft SQL Server Error 22538)

Any help in troubleshooting what this issue could be that would be great.

Thanks in advance.

Ryan J. Boyle"

View 2 Replies View Related

How Do I Drop A Restored Database Named Distribution?

Sep 20, 2007



I restored a backup of a replication database named "distribution" to a non-replicated development instance (ss2000). Well, when I try to drop the database I am told I can not because it is used for replication. It isn't. But you now how Microsoft is . . . . So the question is: how do I drop the database without rebuilding the server, or jumping through firery hoops????

Thanks!

Scorched

View 1 Replies View Related

Replication :: Distribution Database Is Huge In Size

Jul 14, 2015

I have transnational replication setup on two environments, on one server distribution database is tiny, but on the second server the distribution database is 5 times bigger, and taking up lot of space, both environments have almost same size of data.

View 15 Replies View Related

Invalid Destination Path When Trying To Create Distribution Database

Jun 6, 2007

Hello. I am attempting to add a distribution database to an instance of SQL Server on my local PC. I am using SQL 2005 Developer Edition. I am able to set the instance up as a distributor using the following command:



exec sp_adddistributor @distributor = N'computernameinstancename'



I use the following command to create the distribution database:



exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLData',

@data_file_size = 4, @log_folder = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLData',



I get the following error when executing that command:



Msg 14430, Level 16, State 1, Procedure sp_adddistributiondb, Line 214

Invalid destination path C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLData.



It's not due to permissions on that particular folder. I have tried using other folders, all with the same results. Also, I am an Administrator on the PC, so I have full permissions. I have tried running the SQL Server services as myself and Local System. Same results both times. Co-workers with apparently the same setup are able to create the distribution database without a problem.



Has anybody had this type of problem?



Thanks,

Evan

View 5 Replies View Related

SQL 2012 :: Replication - Distribution Database Doesn't Appear Under System Databases

Oct 30, 2014

I just finished installing SQL Server 2012 with the SQL Server Replication feature component checked. On configuring transaction replication, i notice the distribution database wasn't there. I ran the below query

EXEC sp_get_distributor And the value for distribution_db_installed is 0 as shown below

installed distribution server distribution db installed is distribution publisher has remote distribution publisher
0NULL000

View 1 Replies View Related

SQL Server 2008 :: Backup Of Distribution Database Hangs On Stored Procedure

Nov 29, 2014

Have a server running 14 production databases, 12 of which are replicated (transactional replication) to a second server for reporting. Distributor on same machine as publishers. All set to 'SyncWithBackup' = true, distribution set to Full recovery mode, log backups every 30 minutes, full backup nightly. This generally runs just fine.

Occasionally, the process 'hangs' indefinitely (has gone 12 hours or more before being caught) and I need to stop the backup job, stop one or more of the log reader agents, and restart everything, and it proceeds just fine. Annoying, but not fatal, and not very often.

This time, no matter what, the backup job hangs when it runs. This is true whether it is the FULL backup or just a Transaction Log backup. It hangs on the stored procedure sp_msrepl_backup_start, at the point where it is attempting to update the table 'MSrepl_backup_lsns'. When it is hung like this, all of the log reader agent jobs are also hung, blocked by this stored proc. I've tried stopping ALL of the log reader agents prior to starting the backup, but the backup process still hangs up at the same spot and never ends.

I can run the select statement in that SP that gathers the data for the databases 'manually' in a query window and it finishes in about 10 seconds. It actually seems to be hung up on the 'UPDATE' statement. When it is hung, I cannot SELECT from MSrepl_backup_lsns unless I append WITH (NOLOCK) to the statement. Nothing else I can find indicates that there is anything else locking that table. DBCC OPENTRAN shows that there is a lock on that table held by that stored proc -- but I can't see any reason why it won't update the table (17 total records) and move on.

As I said, normally this runs just fine. Totally baffled by what may be causing this at this time.

View 1 Replies View Related

Question About Move Large Amount Of Data From Database To Database

Apr 23, 2007

guys,I have a project need to move more than 100,000 records from onedatabase table to another database table every week. Currently, usersinput date range from web UI, my store procedure will take those dateranges to INSERT records to a table in another database, then deletethe records, but it will take really long time to finish this action(up to 1 or 2 hours).My question is if there is some other way I should do to speed up theaction, I am thinking about use bcp to copy those records to datafileand then use bcp to insert it into SQL Server table. Is this the rightway to do it or should I consider other solution (then, what is thesolution.)Thanks a lot!

View 8 Replies View Related

Large DataBase

Jul 23, 2007

What happens if you have a website and the hard drive on your server is say 250GB. Then the database exceeds that and the database is 300GB in size.How would you spread your database into two different hard drives?Thanks

View 2 Replies View Related

Large Database

Apr 17, 2003

Hello,

I would like to know whether MS SQL Server 2000 Can handle Large Database size 28 gig, currently we are using Sybase but we want migrate to SQL Server 2000. Are there any performance issues?

Any help is appreciated.

Thanks

Sejal

View 1 Replies View Related

Database Is Is Too Large Need Help

Aug 17, 2006

We have many installations of our shopping cart database. One specific database is huge, now about 25 GIG, compared to the others that range from 20 to 75 MEG. The server this one resides on has three other instances of the same database that are normal size.

In a particular table in the large database there are 9700 rows taking 380MB, the same table in a normal db has 162000 rows and takes 6.MB. The tables are identical and the indexes are the same.

Any ideas out there?

View 6 Replies View Related

Database Has Gotten Relatively Large

Sep 12, 2006

I upgraded a database from SQL2000 to SQL2005 and it went pretty smooth. After the transition was made, I backed up the DB. The size of the database was as expected, about 5 GB. About 5 hours later, a maintenance plan executed a few optimization jobs in the following order: Reorganize Index, Rebuild Index, Shrink Database, Update Statistics. Soon after that, another job backed up the database and it was then 32 GB. During the time after the first backup, no one was really using the database.

I've been trying to track this down for several days. Does anyone have any ideas for me?

Thanks,
Bobby Crawford

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved