I need to restore a DB but it was prevented by a background process of "Ghost Cleanup".
server is SQL2000 ENT. sp4.
It could not be killed, neither it was stoped after restart the server. Is there a way to change its running schedule and/or to kill it when I need to restore the db?
I have problem with 'Ghost cleanup' system process. It is locking up my tables and user transactions are keep getting wait status. So is there any way to disable or change the schedule of ghost cleaner? Thanks in advance..
I noticed that several connections were blocked by something called Ghost Cleanup (or something like that). I know what the cleanup does, but it often causes blocking for quite a while . . .
I have a test environment that we rebuild servers on a regular basis.To streamline the process we use ghost. We will be installing SQL onthe servers and want to build a ghost image with that build. We havetested it by doing the build, loading the data, and then stopping allthe services and setting them to manual. After we Ghost the machine,we start up the services and reset them to Automatic. Seems to work.My question is:Are there any risks? Should I expect any adverse affects?Thanks for all your help!
Hello all, Where do I start? My son and I wrote a small ASP.net 2.0 website on XP IIS 5. Works ok with the standard login controls. I FTP'ed the files up to a Windows server2003. I installed the SQL publishing wizard. I created a .sql file of the data base. I ran the file in SQL2005 manager to build the data base on SQL express running on the Windows 2003 server. It installed, no errors. great! The website would not run with Data base connection errors , like files or data base was read only, duplicate database , can not run mydatabase.create. something like that. I created a new app pool and made sure network service was the account. Gave network server permissions to the folders, it started working great. Now sit down. I created a few accounts using the website ok, no errors, Great! Then check the database to see if the passwords were encrypted. The new accounts were not there. But the accounts went somewhere???? Must be two databases???? One hidden? I detached the database I created with the .sql script file.... to see if I would get an error on the browser. nope still worked. Hmmmm other hidden database still working.... I stopped database server in the management tool, It still worked....Ok, I'll get the house check for poltergiests. I went to SERVICES. The service was stopped(SQLEXPRESS).. I changed auto start to manual. Browsed back to the site again, finally an error. I restarted the service, website worked again, but the database, the only user database, is still detached. Created more user accounts with no error.... Seached the hard drives for .MDF files. Found the file I detached and also the ASPNETDB.MDF that I orginally FTP'ed to the server earlier in the APP_DATA folder of the website. I renamed the MDF file to see if then I would get an error, yep I did. It then automatcly re-created the MDF file exactly the same size. Two files in the directory now and now a new error in the browser about a database mismatch. Ok that makes sense. But it will not allow me to rename the new file so I can rename the original. Question is how does SQLEXPRESS run the ASPNETDB.MDF file in the web folder which is not attached to the database engine? Why use a SQL pub wizard if ASP.NET creates the database automaticly? Or just "Runs" it? AM I HIGH? Thanks in advance, before I take drugs and sit in a dark closet. -Wade
I just now had the strangest error, for some reason a table in my DB was locked by two processes, there is no use with BEGIN TRANSACTION in the queries that execute on the DB and while it suddnely happaned no heavy process was running... I was just browsing the table with the enterprise manager.
After viewing the processes with sp_lock I found the two processes that caused the locking and killed them, this fixed the problem, but I would like to know what caused it... When viewing the process info with sp_who the status was "sleeping" and in sp_lock in one process the type was "key" and the status was "wait" (mode "s").
I have problem with 'Ghost cleanup' system process. It is locking up my tables and user transactions are keep getting wait status. So is there any way to disable or change the schedule of ghost cleaner? Thanks in advance..
Hello,I have a table 'customers', with 2 records:SELECT * FROM customers;customerID | customerName------------------+-------------------------myFriend | myFriend's Nametest | testing user(2 rows)but when I'm asking about customerID column, I get the answer:SELECT customerID FROM customers;ERROR: column "customerid" does not existWhat happens? I'm using PostgreSQL 8.1.3
I have a VB program which uses ADODB.Connection to make connection to SQL Server DB. However, under some circumstances, orphan / ghost connections remain in DB even after the VB program closed.
Here are the facts...
(1) Windows Server 2000 + SQL Server 2000 - run the VB program and made connection to the DB, leave it for 3 minutes, the connection closes when the program closes. - run the VB program and made connection to the DB, leave it for 3 hours, the connection closes when the program closes.
(2) Windows Server 2003 + SQL Server 2000 - run the VB program and made connection to the DB, leave it for 3 minutes, the connection closes when the program closes. - run the VB program and made connection to the DB, leave it for 3 hours, the connection does not close even after the program is closed.
Does anyone know if there is any problem with Windows Server 2003 + SQL Server 2000 using ADODB.Connection? Or is there some kind of "timeout" parameter for which I can set to resolve such issue?
We have SQL SERVER 2000 Runnin on our server. We are trying to do a db cleanup, ie all tbls/views that were created earlier and are not needed any more, need to be deleted. However, is there a way, to do a cleanup in a better method, other than going thru the whole bunch of tbls/views manually,and determining which ones are needed or not and delete the ones that are not needed.
Which sp code part is efficient? 1st code from sp_MSdistribution_cleanup. Used for Replication Cleanup job. For 1500 rows, runs a day. Is something wrong with this code? 2nd code part is an alternative idea!. /************************************************** *****************/ delete MSsubscriber_status from MSsubscriber_status ss1 where publisher_id = @publisher_id and publisher_db = @publisher_db and subscriber_id = @subscriber_id and subscriber_db = @subscriber_db and job_id < @max_cleanup_job and job_id <> (select max(job_id) from MSsubscriber_status ss2 (index = ucMSsubscriber_status) where ss2.publisher_id = @publisher_id and ss2.publisher_db = @publisher_db and ss2.subscriber_id = @subscriber_id and ss2.subscriber_db = @subscriber_db) /************************************************** *******************/ select @maxCleanup_job = max(job_id) from MSsubscriber_status (index = ucMSsubscriber_status) where publisher_id = @publisher_id and publisher_db = @publisher_db and subscriber_id = @subscriber_id and subscriber_db = @subscriber_db delete MSsubscriber_status from MSsubscriber_status ss1 where publisher_id = @publisher_id and publisher_db = @publisher_db and subscriber_id = @subscriber_id and subscriber_db = @subscriber_db and job_id < @max_cleanup_job and job_id <> @maxCleanup_job
I'm writing a small script to clean up our database. We have a couple of databases which contain many gigabytes of data.
The script fills a few temp tables, with price-id's which can be deleted (based on a few rules). Then it deletes all related data first, before actually deleting the price records themselves.
This works fine, except for performance issues. After 12 hours, I had to cancel the running script as it was taking too many resources.
My question is, how can I increase performance? Should I add 'commit' after deleting data in each table? Would it help to make it a stored procedure?
Script looks kinda like this:
SELECT priceId INTO #tobedeleted FROM prices WHERE ... (few rules)
DELETE FROM price_product WHERE priceId IN ... #tobedeleted .... ....
I would love to be able to run the distribution cleanup job with a switch that says cleanup all distributed transactions.
Because when I use peer to peer replication the @allow_initialize_from_backup publication property is set to true which is good. But it has the down side that transactions are stored the max retention period in the distribution database. I want to use the deafault 72 hours for my retention period so that the subscritions don't get deactivated but in a system with a high transaction rate there wil be a lot of transactions in 72 hours. This means that the cleanup job will have a tough time to figure out which transactions to delete so the cleanup job will run for a long time not a very big problem but the problem is that the cleanup job will keep the log reader agent from delivering trtansactions to the distribution database and the subscribers won't get their data in time.
Could Microsoft please give me a switch so I can choose when I want to save my transaction and when I want to delete them as soon as they have been delivered to all subscribers?
Is this a feature in SQL Server 2008? Could it be released in SP3 for SQL Server 2005. (The SP 2 cleanup job has a bug so I have to use the SP 1 verison of the cleanup job)
Ques; can "both" files the db backup (.bak) file "and" the (.txt)report file in a maint plan object be cleaned up at same time?
The object is working ok but trying to setup and match backup text reports to the db, I have way more .bak files than text files.
Created from/after db maint plan (which is working ok) from/in the object Maintenance Cleanup Task and the object selections are
Delete files of the following type: _backup files _maintenance plan text reports
File location: _delte specific file File name: __ _search folder and delete files based on an extension Folder:__ File extension:__ File age: _Delete files based on the age of the file at task run time... delete files oder than the following. . . .
I've noticed that wheter if you put the condition on files' age or not it generates exactly the same T-SQL statement, it says that the actual one could be different if you set conditions on the task, but if it is so why they work the saem way?
sysmail_mailitems table in msdb stores data for emails sent using database mail. The DB was growing and I found sysmail_mailitems table is taking almost 85% space. What is the procedure to clean up data in this table. I did dirty way of removing FK's for the table, truncating data and re-establishing FK relationships.
Is there some way or settings somewhere to schedule cleanup on this table?
Using SQL Server 2005. Replication working fine except the distribution table is continuely growing. Started to configure maintenance job (catagory: REPL-Distribution Cleanup) Any suggested steps that will not break the replication. Is there a SP available that will address my problem.
I am using Ola Hallegren's scripts to do backups. He uses @Cleanup Time to delete backups older than a certain number of hours. My situation is I do a full backup of a database on Sunday and then I have a few Differentials and then log backups for the rest of the week. When Sunday rolls around again and my full backup is finished, I would like to delete all the differential backups and log backups. Any way that I could accomplish this using Ola's scripts?
I need to setup a Server Audit in sql 2012 standard edition. The goal is to identify all procs, views, tables, functions that are not being used anymore. What is the best way to setup a server audit to accomplish this? What events need to be captured at a minimum so I can delete all the unused crap in this database. This is a smaller, growing company.
At a client site we just had a customer away on holidays, came back and went to replicate and received the error. I understand why this would happen after the default 14 days, and the obvious fix to prevent it from happening in the future is to extend the period, however I am hoping someone can offer me assistance on the best way to correct the issue now
Error is:
The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Publisher for changes not yet sent to the Subscriber. You must reinitialize the subscription (without upload).
The problem is we cannot lose the data that has been entered on this machine since the last replication. Writing scripts to manually save and pump this data will take days if not weeks (complex database). Is there any way to issue a command to cause a manual upload of the new information that is at the subscriber. Then I could just delete and recreate replication?
I have a 3 tier replication with one master server on tier1, 3 servers on tier 2 and up to 20 remote servers (tier 3) on each of the tier 2 servers. I recently had to delete the subscriptions to each of the tier 3 servers from the tier 2 servers and then recreate the subscription. All went well for most of the subscriptions, but for 3 subscriptions the subscription was dropped at the tier 2 level but failed to drop at the subscriber due to a time out. I went to the tier 3 servers in question and issued the sp_removedbreplication command that supposedly executed properly. On one of these 3 servers everything worked fine and I was able to re-subscribe successfuly and have everything continue to work properly. On the other 2 it appeared the re-subscription worked properly until users began to try to update the database through adds and updates (all controlled through stored procedures). Users receive an error message claiming an invalid object name ctsv_ followed by guid looking string of characters. I suspect it has to do with all of the stored procedures that were created by merge replication when the subscription was set up and initialized (no sync). These include numerous sp_ins_, sp_sel_, and sp_upd_ ... named procedures and a bunch of dt_ ... stored procedures as well.
How do I clean this up so that I can have users using their database again and then re-subscribe again short of rebuilding the database (a very time consuming process)?
After cloning our production SQL 2012 server to make DEV environment, there is a bunch of databases in "Suspect" mode. This is because the DB's and Logs have got out of sync.I can clean up each DB with this script:
ALTER DATABASE DB_NAME SET EMERGENCY GO DBCC CHECKDB (DB_NAME) GO ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO DBCC CHECKDB (DB_NAME, REPAIR_ALLOW_DATA_LOSS) GO ALTER DATABASE DB_NAME SET MULTI_USER GO
but this takes a long time replacing DB_NAME and executing for each DB.I tried putting it all into a script with variables, but I have done something wrong and it does not work. Also is there a way to just apply it to the suspect DB's?
USE master Go DECLARE @dbname sysname, @cmd varchar(1000), @cmd1 varchar(1000), @cmd2 varchar(1000), @cmd3 varchar(1000), @cmd4 varchar(1000) DECLARE db_recovery_cursor CURSOR FOR SELECT name from sysdatabases OPEN db_recovery_cursor
I added a maintenance CleanUp task pointing to the right folder I checked the include first-level subfolder box, and delete files based on the age of the file at task run time
I specified "delete files older than " 1 day
But the cleanup task is not working, I have to manually go a delete the files myself.
Am I missing something? should I run the maintenance task first? and then the backup task?
I am running a couple of sql 2000 SP3a servers with merge and snapshot replication. One server acting as publisher and distributor and the rest subscribers. On one of the server I have got the error below and have tried most of the suggestions by msdn. This server has not crashed ever before or any hardware problems. It has been running for a couple of months and no problems. This has not happened no any of the other servers. Any suggestions would be greatly appreciated as the only resolution I have left is to bring up a new instance, setup replication and see if this would resolve the issue. Stopping and starting of agents don't work.
Server: EASTSRV3 DBMS: Microsoft SQL Server Version: 08.00.0760 user name: dbo API conformance: 2 SQL conformance: 1 transaction capable: 2 read only: N identifier quote char: " non_nullable_columns: 1 owner usage: 31 max table name len: 128 max column name len: 128 need long data len: Y max columns in table: 1024 max columns in index: 16 max char literal len: 524288 max statement len: 524288 max row size: 524288
[4/18/2005 11:59:27 AM]EASTSRV3.ICASData: {call sp_MSgetversion } Percent Complete: 2 Connecting to Subscriber 'EASTSRV3' Percent Complete: 3 Retrieving publication information Percent Complete: 4 Retrieving subscription information Percent Complete: 4 The merge process is cleaning up meta data in database 'HO_Master'. Percent Complete: 4 The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in MSmerge_contents, and 0 row(s) in MSmerge_tombstone. Percent Complete: 4 The merge process is cleaning up meta data in database 'ICASData'. The merge process could not perform retention-based meta data cleanup in database 'ICASData'. Percent Complete: 0 The merge process could not perform retention-based meta data cleanup in database 'ICASData'. Percent Complete: 0 Category:NULL Source: Merge Replication Provider Number: -2147199467 Message: The merge process could not perform retention-based meta data cleanup in database 'ICASData'. Percent Complete: 0 Category:COMMAND Source: Failed Command Number: 0 Message: {call sp_mergemetadataretentioncleanup(?, ?, ?)} Percent Complete: 0 Category:SQLSERVER Source: EASTSRV3 Number: 11 Message: General network error. Check your network documentation.