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"? or
Is there a free distribution of "Database Engine"?
Hi, what is the distribution fee for SQL Server 2005 Desktop Engine distribution? Where can I find this information? I don't need it for development only for distribution.
Today we received an issue on an application database on internal free space on the DB is 0% that was designed with as below
name fileid filename filegroup size maxsize growth usage XX 1 I:DataMSSQL.1MSSQLDataNew XX.mdf PRIMARY 68140032 KB Unlimited 0 KB data only XX_log 2 I:DataMSSQL.1MSSQLDataNew XX_log.LDF NULL 1050112 KB 2147483648 KB 102400 KB log only XX_2 3 I:DataMSSQL.1MSSQLDataNew XX_2.ndf PRIMARY 15458304 KB Unlimited 0 KB data only XX_3 4 I:DataMSSQL.1MSSQLDataNew XX_3.ndf PRIMARY 13186048 KB Unlimited 0 KB data only XX_4 5 I:DataMSSQL.1MSSQLDataNew XX_4.ndf PRIMARY 19570688 KB Unlimited 204800 KB data only XX_5 6 I:DataMSSQL.1MSSQLDataNew XX_5.ndf PRIMARY 19591168 KB Unlimited 204800 KB data only
2 of the secondary data files had its autogrowth enabled to unrestricted with 200MB and 3 of the data files including primary had its Autogowth turned OFF. Application use is complaining that there is no internal freespace on the DB.
What fails to understand us is that when the Auto growth was already TURNED OFF on 3 data files ( 1 primary and 2 secondary ) still why was the application trying to increase the space on the .mdf and .ndf files; as well when the Autogrowth is TURNED ON on 2 of the secondary data files, why was the DB not able to expand these file groups when the autogrowth is already turned off on 3 of its other files.
What more data i need to ensure i submit an analysis to this.
The space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
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.
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.
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 .
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.
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.
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-
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
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!
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.
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.
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?
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.
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
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.
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.
I inherited a SQL server (2005) that sits on a Windows 2003 box (upgrading to 2008 R2 and Win2008, ASAP) and on our SQL Data Drive (260GB - 22GB free) we have 16 databases (that are associated with our primary application). One database in particular is causing me quite a bit of pain - on Tuesdays. This table will consume all available free space (save 700kb) and place it within it's 'reserved' space. It's not being used and I can reclaim it by shrinking. I have done this for 3 consecutive weeks now and I have always thought that shrinking is not really a best practice.
Based on a job I scheduled to spit out various database stats I discovered the following:
Somewhere after 11pm on Monday (and on or before 11:30pm) the reserved space goes from 9GB down to 23 then 37MB on this particular database.
After 1:30am on Tuesday (and on or before 2:00am) the database consumes nearly all available disk space and thus expands its reserved space to 28GB
I believe it has something to do with the Indexes being rebuilt - per the Maintenance Plan the Index Rebuild is supposed to occur every Monday @ 11pm. On 6/18 it ran from 11pm - 11:44pm with no problems. Starting on 6/26 (and subsequently 7/2 and 7/9) they have started at 11 and ended anywhere between 2 - to 3 hours later. And with the following error:
Failed-1073548784) Executing the query "ALTER INDEX [PK_activityLog] ON [dbo].[activityLog] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF ) " failed with the following error: "Could not allocate a new page for database 'FA_PROD_SDDS' because of insufficient disk space in filegroup 'PRIMARY'.
Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The database 'FA_PROD_SDSS' is the one that I am having issues with.
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.
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.
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????
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.
Having just archived quite a bit of data from the main Production DB, I now have around 15% free, reclaimable space sat in the data file.
I'm reluctant to run DBCC SHRINKFILE as that apparently causes a lot of Index fragmentation which will cause issues for performance - how else can the space be allocated back to the OS?
The free one that i have downloaded doesn't seem to be able to view the database. Is there any free database which i can download? Please advise. Thanks.
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:
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.
