Conflict Table Cleanup Interval
Nov 9, 2006What replication parameter sets the interval in which data in the conflict tables is deleted in SQL Server 2000 SP4?
View 5 Replies
ADVERTISEMENT
SQL 2012 :: 15 Minute Interval Report Between Two Dates With Total For Each Interval
Jul 21, 2014I'm trying to create a report which will give me a break down of how many unique vehicles have been seen between two dates via a 15 minute interval and what Lane they were seen. My current script looks like this
SELECT l.Name [Name], count(l.Name) Total, p.Created
FROM PlateReads p
inner join Lanes l on p.Lane_ID = l.ID
where LicencePlate in (Select Plate from LPRnet_MelAir_C.dbo.TempVehiclePlates)
group by Name
Name being the Lane they were in and the Total being the amount of times a unique vehicle has been seen and p.Created being the date they were seen (thats what I need the interval powered off)
Ideally the output would look like this
16/03/201408:00 to 08:15Bus Lane 15
16/03/201408:00 to 08:15Elevated Road150
16/03/201408:00 to 08:15Public Pickup75
16/03/201408:15 to 08:30Bus Lane 13
16/03/201408:15 to 08:30Elevated Road120
16/03/201408:15 to 08:30Public Pickup55
All the way to 12/04/2014
I’ve got it so it says Lane and Count just can’t get the interval part
Sysmail_mailitems Table Cleanup
Aug 22, 2007sysmail_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?
Thanks,
Distribution Table Cleanup
Sep 15, 2006Using 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.
Thanks,
Run A SP With A Datetime Interval From A Table
Nov 9, 2007Hello,
I have something i called a deletelist, inside this list is items that is waiting to be deleted.
The deletelist looks like this:username nvarchar(20),itemtype int,deletedate datetime
The SP that runs once everyday and deletes post is called Data_DeleteFromDeleteList.
Inside of this SP i have afew delete functions like this one:DELETE FROM reg_user WHERE (username = (SELECT username FROM data_deletelist WHERE (deletedate < getdate() AND itemtype = 0)))
Now i want to add a new delete function to this list, this function is to execute a SP when the itemtype occurs, like this:SELECT EXEC [dbo].[Data_CompletlyDeleteAUser] @UserName = username, @QResult = @QResult OUTPUT FROM data_deletelist WHERE (username = (SELECT username FROM data_deletelist WHERE (deletedate < getdate() AND itemtype = 2)))
any idea of how i can rewrite this function so it works??in this SP im not using the @QResult for anything, thats in another SP
At the end of this SP, i just delete all the old records from the deletelist like this,
DELETE FROM data_deletelist WHERE (deletedate < getdate())
Delete Interval Of Values Of A Certain Field From A Table...
Dec 4, 2007
How do i use delete to perform a delete action of a value from a table using an interval of values?
--
delete [table] from [field]='[value]' to '[value]'
-- or
delete [table] where [field]='[value]' to '[value]'
--
i can't delete an interval of values (from...to) from a field of a certain table...
What is the right expression to do this?
How Rows Get Deleted Auomatically In Sql Table After Specific Interval Of Time
May 17, 2008hi there
i am using .net framework 1.1 with SQL 2000 .
i want the data in table to get deleted automatically after 30 days of inserting data.
so how do i achieve this?
Select Multiple Rows Based On Date Interval From Table
Apr 7, 2015I have a table in which each record has a initial date and a final date. I would like to create I query that gives me one row for each month between the initial date and the final date. It would be something like this:
Original:
Product|price|initial_date|final_date
A|20.50|2014-08-10|2014-10-01
B|50|2015-01-15|2015-02-20
Resulting view:
A|20.5|2014-08-01
A|20.5|2014-09-01
A|20.5|2014-10-01
B|50|2015-01-01
B|50|2015-02-01
I would like to do that, because these dates correspond to the time in which the products are in possession of sellers, so I would to use this resulting query to generate a pivot chart in Excel to illustrate the value of the goods that are with our sellers in each month.
Is it possible to do it? I think I could do that direct in VBA, but I think that maybe it would be faster if I could do it directly in SQL.
By the way, I am using MS SQL Server and SQL Server Manegement Studio 2012.
Conflict Table Problem
May 24, 2007Hi All,
I Want to know the following
1) when are the conflict tables in Merge replication created and are they dropped automatically after certain duration or do they exists once created????
2) data for how many days exist on the conflict tables.
Lindly reply even for one question its very urgent for me
Thanks in advance to all u helpfull people out there
Problem With Conflict Table For Merge Replication
Sep 3, 2007Hi
I faced with strange problem.
From time to time, during syncronization I get the following errors at the subscriber:
ERROR: -2147200992
SOURCE: Merge Replication Provider
TEXT: The merge process could not store conflict information for article 'Products'. Check the publication properties to determine where conflicts records are stored. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
ERROR: 2812
SOURCE: OFF1SQLEXPRESS
TEXT: Could not find stored procedure 'MSmerge_cft_sp_49D28CEAFF8A4ACC5C5462B5027D4E4D'.
ERROR: -2147199481
SOURCE: Merge Replication Provider
TEXT: The Merge Agent was unable to update information about the last synchronization at the Subscriber. Ensure that the subscription exists at the Subscriber, and restart the Merge Agent.
After I refresh the snaphot at the publisher, these errors disappear and the syncronization is completed without any problem. And I can sync easy again week or two..
What is the problem?
What is the right solution?
Yep, I can continue to refresh the snapshot each time when the error appears, but..
maybe there is another approach?
Thanks in advance!
Paul
SQL DB Cleanup
Oct 12, 2007We 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.
View 5 Replies View RelatedReplication Cleanup Sp.
Feb 9, 1999Which 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
Ghost Cleanup....
Mar 28, 2008Hi:
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?
thanks
David
Cleanup The Server Log?
May 14, 2007Hi,
Just wanted to know how can I delete those old server/error/agent log?
Thanks!
Deletescript For Cleanup
Oct 26, 2007
Hi everyone,
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
....
....
Distribution Cleanup
Jun 17, 2007I 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)
How To Cleanup All Database？
Mar 6, 2006hello，if I want to cleanup all the data that in a database,how to do?
thanks!
History Cleanup Task
Feb 11, 2008I have set up a history cleanup task to be performed once per week, however it doesn't seem to delete the old files, what am I doing wrong?
View 8 Replies View RelatedMaint Cleanup Task
Mar 19, 2007Ques; 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. . . .
Cleanup Task Deleting Everything - T-SQL
Apr 24, 2007Hi all,
I created a maintenance plan with a cleanup task to delete bak files older than 4 days, but it keeps deleting everything but the current day files.
This is the generated T-SQL:
EXECUTE master.dbo.xp_delete_file 0,N'F:SQLDBBACKUP',N'BAK',N'2007-04-20T11:59:30',1
Someone can tell what's wrong with it?
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?
Thanks!
Cheers,
Giovanni
Problem With Ghost Cleanup
Nov 12, 2007 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..
Maintenance Cleanup Task……
Sep 14, 2007
SQL Server 2005 Standard Edition
Windows Server 2003.
I have created an €˜Backup Database Task€™ to create the database backups and checked the option €œCreate a subdirectory for each database€?.
When I add the €œMaintenance Cleanup Task€? I am not getting the similar option to clean the backup files in the subdirectory.
Any suggestion on how to solve this?
I can always uncheck the €œCreate a subdirectory for each database€? option to create the backup files in one location.
Thank you,
Smith
Blocking On Something Like Ghost Cleanup?
Aug 23, 2007I 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 . . .
Anything I can do about it? SS2000.
Thanks,
Michael
Calculating Interval
Mar 16, 2006If two records containing datetime stamps are SELECTed, how can the interval between them be quickly calculated?
View 8 Replies View RelatedSQL Interval Count
Feb 1, 2008Does anybody know what's the best way to do a count with a 1 day interval? This to me seems like the best approach, my data looks like this
work order, analyst, analysis, dateanalyzed
080154705, ZT, Special, 1/31/2008 9:35:00 PM
080154706, ZT, 8260B, 1/31/2008 10:07:00 PM
080154706, ZT, Special, 1/31/2008 10:07:00 PM
080154706, ZT, Special, 1/31/2008 10:39:00 PM
080154706, ZT, Special, 1/31/2008 11:11:00 PM
080154706, ZT Special, 1/31/2008 11:43:00 PM
080165001, ZT, 624, 2/1/2008 4:30:00 AM
080165001, ZT, 624, 2/1/2008 5:02:00 AM
080166901, ZT, 624, 2/1/2008 12:50:00 PM
080166801, ZT, 624, 2/1/2008 2:51:00 PM
080154711, ZT, Special, 2/1/2008 10:39:00 PM
080124711, ZT, Special, 2/1/2008 10:11:00 PM
What I would like is to have my data displayed as follows:
Analysis, myCount, dateanalyzed
Special, 5, 1/31/08
Special, 2, 2/1/08
624, 4, 2/1/08
8260B, 1, 1/31/08
As you can see, I will need to count the number of occurences for analysis for each day, such as Special has 5 and 2 for those two days, we can have difference analysis done each day done. A Union Join will not work because the table is huge and I will need to do at least 30 days. Is interval the best approach, I plan to use Access but can do it in SQL Server if necessary.
Polling Interval
Mar 27, 2007Hello
I was wondering where I can change the polling interval settings in sql server management studio..
It appears to be different from the one in Enterprise Manager.
very little info can be found on the Internet
Thanks!
Worf
SQL 2012 :: Cleanup Of Differential And Log Backups
Oct 15, 2014I 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?
View 1 Replies View RelatedHow To Identify Unused DB Objects For Cleanup
Aug 28, 2013I 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.
View 2 Replies View RelatedMerge Replication / Metadata Cleanup / Help!
Jan 8, 2007At 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?
Any help or advice would be appreciated.
Subscriber Cleanup After Deleting Subscription
May 15, 2006I 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)?
Transact SQL :: Cleanup Suspect Databases
Sep 26, 2015After 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
[code]....
I Hate The Maintenance CleanUp Task :=)
May 4, 2007I have a backup database task that runs at 4 am.
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?
How To Group By Time Interval
Oct 1, 2007Hello ,I have a stored procedure that group a series of event by day. My table Tevent has a timestamp column for every event.Tevent structure:idevent| event| DatetimeEvent
I just found out that I need to group the result by production shift of the day , therefore I have to group the result for everyday from 8 AM until 2 AM the next day.Any idea of how to group by time interval?Thanks