Hi,
After disabling publishing on my server, there were
numerous merge replication related objects.
How do I clean them up. I tried to drop them, but I get a
message saying I am trying to drop system objects, and the
effort fails.
Thanks,
Sang
Sang,
try sp_removedbreplication (assuming the database is no longer contains any
publications/subscriptions).
Hilary Cotter sent me a link to a script he created at http://www.ava.co.uk
(technical resouces section) that you might want to look at, if the above
stored proc doesn't remove all the objects.
HTH,
Paul Ibison
Showing posts with label clean. Show all posts
Showing posts with label clean. Show all posts
Sunday, March 25, 2012
cleaning up duplicates
I have a table with column a, b, c, and d
column b has several duplicates and I want to clean this up.
a b c d
=============
1 name1 aa gg
2 name2 bb hh
3 name3 cc ii
4 name3 dd jj
5 name4 ee kk
6 name5 ff ll
etc.
I'm guessing I would need to select all the unique rows from the table,
put them into temp_table1, select the rows with the duplicates as
single rows into temp_table2, and join those two back into the original
table.
would this be the right idea? and if so,
exactly how would I accomplish this?
any help would be greatly appreciated!create table #test
(
col1 int not null primary key,
col2 varchar(10) not null,
col3 char(1) not null
)
insert into #test values (1,'name1','a')
insert into #test values (2,'name2','b')
insert into #test values (3,'name2','y')
insert into #test values (4,'name3','c')
insert into #test values (5,'name4','f')
select * from #test
where col1=(select max(col1) from #test t where t.col2=#test.col2)
order by col1
<guilesf2@.hotmail.com> wrote in message
news:1141542444.740452.81000@.e56g2000cwe.googlegroups.com...
>I have a table with column a, b, c, and d
> column b has several duplicates and I want to clean this up.
> a b c d
> =============
> 1 name1 aa gg
> 2 name2 bb hh
> 3 name3 cc ii
> 4 name3 dd jj
> 5 name4 ee kk
> 6 name5 ff ll
> etc.
>
> I'm guessing I would need to select all the unique rows from the table,
> put them into temp_table1, select the rows with the duplicates as
> single rows into temp_table2, and join those two back into the original
> table.
> would this be the right idea? and if so,
> exactly how would I accomplish this?
> any help would be greatly appreciated!
>|||thanks a lot! just for my understanding, what would be the reason the
table
needs that alias-type thing in order to work? just curious and trying
to learn.
again, thanks a lot|||If your goal is to cleanup only column b then you probably need to
normalize the table further. You will need to store unique names in a
separate table called NAMES (id, name) and store the id in column b.sqlsql
column b has several duplicates and I want to clean this up.
a b c d
=============
1 name1 aa gg
2 name2 bb hh
3 name3 cc ii
4 name3 dd jj
5 name4 ee kk
6 name5 ff ll
etc.
I'm guessing I would need to select all the unique rows from the table,
put them into temp_table1, select the rows with the duplicates as
single rows into temp_table2, and join those two back into the original
table.
would this be the right idea? and if so,
exactly how would I accomplish this?
any help would be greatly appreciated!create table #test
(
col1 int not null primary key,
col2 varchar(10) not null,
col3 char(1) not null
)
insert into #test values (1,'name1','a')
insert into #test values (2,'name2','b')
insert into #test values (3,'name2','y')
insert into #test values (4,'name3','c')
insert into #test values (5,'name4','f')
select * from #test
where col1=(select max(col1) from #test t where t.col2=#test.col2)
order by col1
<guilesf2@.hotmail.com> wrote in message
news:1141542444.740452.81000@.e56g2000cwe.googlegroups.com...
>I have a table with column a, b, c, and d
> column b has several duplicates and I want to clean this up.
> a b c d
> =============
> 1 name1 aa gg
> 2 name2 bb hh
> 3 name3 cc ii
> 4 name3 dd jj
> 5 name4 ee kk
> 6 name5 ff ll
> etc.
>
> I'm guessing I would need to select all the unique rows from the table,
> put them into temp_table1, select the rows with the duplicates as
> single rows into temp_table2, and join those two back into the original
> table.
> would this be the right idea? and if so,
> exactly how would I accomplish this?
> any help would be greatly appreciated!
>|||thanks a lot! just for my understanding, what would be the reason the
table
needs that alias-type thing in order to work? just curious and trying
to learn.
again, thanks a lot|||If your goal is to cleanup only column b then you probably need to
normalize the table further. You will need to store unique names in a
separate table called NAMES (id, name) and store the id in column b.sqlsql
Cleaning out Log file
Can someone give me some advise on how to clean out my log file?
Thanks :confused:1. Don't
2. Stop SQL Server, delete the files in the log-dir, start SQL Server.
3. erhm...|||So do I just want to delete the ldf file attached to the database? If so will it just create a new ldf file?|||oh no! I misunderstood! please don't delete the ldf file!|||Sometimes I execute the following:
USE MONKEY
DBCC OPENTRAN ('MONKEY')
BACKUP LOG MONKEY WITH TRUNCATE_ONLY
CHECKPOINT
BACKUP LOG MONKEY WITH TRUNCATE_ONLY
CHECKPOINT
BACKUP LOG MONKEY WITH TRUNCATE_ONLY
CHECKPOINT
BACKUP LOG MONKEY WITH TRUNCATE_ONLY
CHECKPOINT
DBCC SHRINKDATABASE (MONKEY)
DBCC SHRINKDATABASE (MONKEY, TRUNCATEONLY)
Works for me, I'm sure there's a saver or better way to have a log file shrunk. Make sure though there are no transactions open on that database, also, I'd like to pick a time when there's no user activity on that db. The DBCC SHINK can take a while though.
Thanks :confused:1. Don't
2. Stop SQL Server, delete the files in the log-dir, start SQL Server.
3. erhm...|||So do I just want to delete the ldf file attached to the database? If so will it just create a new ldf file?|||oh no! I misunderstood! please don't delete the ldf file!|||Sometimes I execute the following:
USE MONKEY
DBCC OPENTRAN ('MONKEY')
BACKUP LOG MONKEY WITH TRUNCATE_ONLY
CHECKPOINT
BACKUP LOG MONKEY WITH TRUNCATE_ONLY
CHECKPOINT
BACKUP LOG MONKEY WITH TRUNCATE_ONLY
CHECKPOINT
BACKUP LOG MONKEY WITH TRUNCATE_ONLY
CHECKPOINT
DBCC SHRINKDATABASE (MONKEY)
DBCC SHRINKDATABASE (MONKEY, TRUNCATEONLY)
Works for me, I'm sure there's a saver or better way to have a log file shrunk. Make sure though there are no transactions open on that database, also, I'd like to pick a time when there's no user activity on that db. The DBCC SHINK can take a while though.
Clean up replication data
I have a develop machine for our web site. On this machine I have a copy of a database from the production site. The database on the production site is being replicated. When I copied the database to the develop machine and started testing some new developments I got the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sysmergearticles'
This tells me that some replication data is also copied to the develop database.
Can anyone tell me how I can clean this data, or if I should copy the databse in a different way. (Now I create a new databse and do an import of the tables and stored procedures)You could try to remove replication by going under Tools(in EM)-->Replication-->Disable publishing,Distri...If it allows you to do so then it's fine.Or else you could try to first set the replication on your Test system and then remove ot by using the Tools-->Replication.
Or you may try the script below:
USE [DBNAME]
GO
-- Drop all replication triggers from the database
PRINT 'Drop all replication triggers from the database'
SELECT trigs.name AS TriggerName,
trigs.id AS TriggerID,
tables.name AS TableName
INTO #Triggers
FROM sysobjects trigs
INNER JOIN sysobjects tables
ON trigs.parent_obj=tables.id
where trigs.category=2 and trigs.xtype='TR'
DECLARE @.TriggerName varchar(100), @.TriggerID INT, @.TableName varchar(100)
DECLARE cur CURSOR for SELECT * FROM #Triggers
OPEN cur
FETCH NEXT FROM cur INTO @.TriggerName, @.TriggerID, @.TableName
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP TRIGGER ' + @.TriggerName)
FETCH NEXT FROM cur INTO @.TriggerName, @.TriggerID, @.TableName
END
CLOSE cur
DEALLOCATE cur
GO
-- Drop all replication constraints from the database
PRINT 'Drop all replication constraints from the database'
DECLARE @.ConstName varchar(100), @.ConstID INT, @.TableName varchar(100)
SELECT CONST.name AS ConstName,
CONST.id AS ConstID,
tables.name AS TableName
INTO #Constraints
FROM sysobjects CONST
INNER JOIN sysobjects tables
ON CONST.parent_obj=tables.id
where CONST.xtype='C'
DECLARE cur CURSOR for SELECT * FROM #Constraints
OPEN cur
FETCH NEXT FROM cur INTO @.ConstName, @.ConstID, @.TableName
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('ALTER Table ' + @.TableName + ' DROP CONSTRAINT ' + @.ConstName)
FETCH NEXT FROM cur INTO @.ConstName, @.ConstID, @.TableName
END
CLOSE cur
DEALLOCATE cur
GO
-- Drop all replication User tables
PRINT 'Drop all replication User tables'
DECLARE @.TableName varchar(100), @.TableID INT
SELECT Tables.name AS ConstName,
Tables.id AS ConstID
INTO #Tables
FROM sysobjects Tables
where Tables.xtype='U' AND Status < 0 AND category=2050
DECLARE cur CURSOR for SELECT * FROM #Tables
OPEN cur
FETCH NEXT FROM cur INTO @.TableName, @.TableID
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP Table ' + @.TableName)
FETCH NEXT FROM cur INTO @.TableName, @.TableID
END
CLOSE cur
DEALLOCATE cur
GO
-- Drop all replication User procedures
PRINT 'Drop all replication User procedures'
DECLARE @.ProcName varchar(100), @.ProcID INT
SELECT Procs.name AS ConstName,
Procs.id AS ConstID
INTO #Procedures
FROM sysobjects Procs
where procs.xtype='P' AND Status < 0
DECLARE cur CURSOR for SELECT * FROM #Procedures
OPEN cur
FETCH NEXT FROM cur INTO @.ProcName, @.ProcID
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP Procedure ' + @.ProcName)
FETCH NEXT FROM cur INTO @.ProcName, @.ProcID
END
CLOSE cur
DEALLOCATE cur
GO
-- Drop all replication User Views
PRINT 'Drop all replication User Views'
DECLARE @.ViewName varchar(100), @.ViewID INT
SELECT MyViews.name AS ConstName,
MyViews.id AS ConstID
INTO #Views
FROM sysobjects MyViews
where MyViews.xtype='V' AND Status < 0 AND Name NOT LIKE 'sys%'
DECLARE cur CURSOR for SELECT * FROM #Views
OPEN cur
FETCH NEXT FROM cur INTO @.ViewName, @.ViewID
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP View ' + @.ViewName)
FETCH NEXT FROM cur INTO @.ViewName, @.ViewID
END
CLOSE cur
DEALLOCATE cur
GO
-- Drop all replication rowguids, defaults and indexes
PRINT 'Drop all replication rowguids, defaults and indexes'
SELECT tables.name as TableName,
defaults.name AS DefaultName,
Indexes.IndexName,
cols.name AS ColumnName
INTO #Defaults
FROM sysobjects defaults
INNER JOIN syscolumns cols
ON defaults.ID=cols.cdefault
INNER JOIN sysobjects tables
ON tables.id=cols.id
INNER JOIN
(select sysindexes.name AS IndexName,
Tables.Name AS TableName,
Tables.id as TableID,
cols.Name AS ColumnName
from sysindexes
INNER JOIN sysobjects Tables
ON sysindexes.id=tables.id
INNER JOIN sysindexkeys k
on sysindexes.id=k.id
AND sysindexes.indid=k.indid
INNER JOIN syscolumns cols
ON k.id=cols.id
AND k.colid=cols.colid
where cols.name='rowguid') Indexes
ON Indexes.TableID=tables.id
where cols.name='rowguid'
DECLARE @.DefaultName varchar(100), @.IndexName varchar(100), @.TableName varchar(100), @.ColName varchar(50)
DECLARE cur CURSOR for SELECT * FROM #Defaults
OPEN cur
FETCH NEXT FROM cur INTO @.TableName, @.DefaultName, @.IndexName, @.ColName
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('ALTER TABLE ' + @.TableName + ' DROP CONSTRAINT ' + @.DefaultName)
EXECUTE ('DROP INDEX ' + @.TableName + '.' + @.IndexName)
EXECUTE ('ALTER TABLE ' + @.TableName + ' DROP COLUMN rowguid')
FETCH NEXT FROM cur INTO @.TableName, @.DefaultName, @.IndexName, @.ColName
END
CLOSE cur
DEALLOCATE cur
GO|||I've been trying to delete the conflict tables left over after EXEC sp_removereplication
Your script did it!
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sysmergearticles'
This tells me that some replication data is also copied to the develop database.
Can anyone tell me how I can clean this data, or if I should copy the databse in a different way. (Now I create a new databse and do an import of the tables and stored procedures)You could try to remove replication by going under Tools(in EM)-->Replication-->Disable publishing,Distri...If it allows you to do so then it's fine.Or else you could try to first set the replication on your Test system and then remove ot by using the Tools-->Replication.
Or you may try the script below:
USE [DBNAME]
GO
-- Drop all replication triggers from the database
PRINT 'Drop all replication triggers from the database'
SELECT trigs.name AS TriggerName,
trigs.id AS TriggerID,
tables.name AS TableName
INTO #Triggers
FROM sysobjects trigs
INNER JOIN sysobjects tables
ON trigs.parent_obj=tables.id
where trigs.category=2 and trigs.xtype='TR'
DECLARE @.TriggerName varchar(100), @.TriggerID INT, @.TableName varchar(100)
DECLARE cur CURSOR for SELECT * FROM #Triggers
OPEN cur
FETCH NEXT FROM cur INTO @.TriggerName, @.TriggerID, @.TableName
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP TRIGGER ' + @.TriggerName)
FETCH NEXT FROM cur INTO @.TriggerName, @.TriggerID, @.TableName
END
CLOSE cur
DEALLOCATE cur
GO
-- Drop all replication constraints from the database
PRINT 'Drop all replication constraints from the database'
DECLARE @.ConstName varchar(100), @.ConstID INT, @.TableName varchar(100)
SELECT CONST.name AS ConstName,
CONST.id AS ConstID,
tables.name AS TableName
INTO #Constraints
FROM sysobjects CONST
INNER JOIN sysobjects tables
ON CONST.parent_obj=tables.id
where CONST.xtype='C'
DECLARE cur CURSOR for SELECT * FROM #Constraints
OPEN cur
FETCH NEXT FROM cur INTO @.ConstName, @.ConstID, @.TableName
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('ALTER Table ' + @.TableName + ' DROP CONSTRAINT ' + @.ConstName)
FETCH NEXT FROM cur INTO @.ConstName, @.ConstID, @.TableName
END
CLOSE cur
DEALLOCATE cur
GO
-- Drop all replication User tables
PRINT 'Drop all replication User tables'
DECLARE @.TableName varchar(100), @.TableID INT
SELECT Tables.name AS ConstName,
Tables.id AS ConstID
INTO #Tables
FROM sysobjects Tables
where Tables.xtype='U' AND Status < 0 AND category=2050
DECLARE cur CURSOR for SELECT * FROM #Tables
OPEN cur
FETCH NEXT FROM cur INTO @.TableName, @.TableID
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP Table ' + @.TableName)
FETCH NEXT FROM cur INTO @.TableName, @.TableID
END
CLOSE cur
DEALLOCATE cur
GO
-- Drop all replication User procedures
PRINT 'Drop all replication User procedures'
DECLARE @.ProcName varchar(100), @.ProcID INT
SELECT Procs.name AS ConstName,
Procs.id AS ConstID
INTO #Procedures
FROM sysobjects Procs
where procs.xtype='P' AND Status < 0
DECLARE cur CURSOR for SELECT * FROM #Procedures
OPEN cur
FETCH NEXT FROM cur INTO @.ProcName, @.ProcID
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP Procedure ' + @.ProcName)
FETCH NEXT FROM cur INTO @.ProcName, @.ProcID
END
CLOSE cur
DEALLOCATE cur
GO
-- Drop all replication User Views
PRINT 'Drop all replication User Views'
DECLARE @.ViewName varchar(100), @.ViewID INT
SELECT MyViews.name AS ConstName,
MyViews.id AS ConstID
INTO #Views
FROM sysobjects MyViews
where MyViews.xtype='V' AND Status < 0 AND Name NOT LIKE 'sys%'
DECLARE cur CURSOR for SELECT * FROM #Views
OPEN cur
FETCH NEXT FROM cur INTO @.ViewName, @.ViewID
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP View ' + @.ViewName)
FETCH NEXT FROM cur INTO @.ViewName, @.ViewID
END
CLOSE cur
DEALLOCATE cur
GO
-- Drop all replication rowguids, defaults and indexes
PRINT 'Drop all replication rowguids, defaults and indexes'
SELECT tables.name as TableName,
defaults.name AS DefaultName,
Indexes.IndexName,
cols.name AS ColumnName
INTO #Defaults
FROM sysobjects defaults
INNER JOIN syscolumns cols
ON defaults.ID=cols.cdefault
INNER JOIN sysobjects tables
ON tables.id=cols.id
INNER JOIN
(select sysindexes.name AS IndexName,
Tables.Name AS TableName,
Tables.id as TableID,
cols.Name AS ColumnName
from sysindexes
INNER JOIN sysobjects Tables
ON sysindexes.id=tables.id
INNER JOIN sysindexkeys k
on sysindexes.id=k.id
AND sysindexes.indid=k.indid
INNER JOIN syscolumns cols
ON k.id=cols.id
AND k.colid=cols.colid
where cols.name='rowguid') Indexes
ON Indexes.TableID=tables.id
where cols.name='rowguid'
DECLARE @.DefaultName varchar(100), @.IndexName varchar(100), @.TableName varchar(100), @.ColName varchar(50)
DECLARE cur CURSOR for SELECT * FROM #Defaults
OPEN cur
FETCH NEXT FROM cur INTO @.TableName, @.DefaultName, @.IndexName, @.ColName
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('ALTER TABLE ' + @.TableName + ' DROP CONSTRAINT ' + @.DefaultName)
EXECUTE ('DROP INDEX ' + @.TableName + '.' + @.IndexName)
EXECUTE ('ALTER TABLE ' + @.TableName + ' DROP COLUMN rowguid')
FETCH NEXT FROM cur INTO @.TableName, @.DefaultName, @.IndexName, @.ColName
END
CLOSE cur
DEALLOCATE cur
GO|||I've been trying to delete the conflict tables left over after EXEC sp_removereplication
Your script did it!
Clean up old stored procedures
Is there a way to find out if a stored procedure has not been run in a
while?
Mike W wrote:
> Is there a way to find out if a stored procedure has not been run in a
> while?
Not directly. The best you can do is create a server-side trace that
traps the SP:Starting event. You do not need to bind the TextData, only
the ObjectID, DatabaseID. Let it run until you feel you'll get the best
collection of procedures. You can resolve the object names against the
sysobjects table in each database or use object_name() for the currently
selected database.
I would obviously test the removal in the deve environment before
removing any procedures from production.
David Gugick
Imceda Software
www.imceda.com
while?
Mike W wrote:
> Is there a way to find out if a stored procedure has not been run in a
> while?
Not directly. The best you can do is create a server-side trace that
traps the SP:Starting event. You do not need to bind the TextData, only
the ObjectID, DatabaseID. Let it run until you feel you'll get the best
collection of procedures. You can resolve the object names against the
sysobjects table in each database or use object_name() for the currently
selected database.
I would obviously test the removal in the deve environment before
removing any procedures from production.
David Gugick
Imceda Software
www.imceda.com
Clean up old stored procedures
Is there a way to find out if a stored procedure has not been run in a
while?Mike W wrote:
> Is there a way to find out if a stored procedure has not been run in a
> while?
Not directly. The best you can do is create a server-side trace that
traps the SP:Starting event. You do not need to bind the TextData, only
the ObjectID, DatabaseID. Let it run until you feel you'll get the best
collection of procedures. You can resolve the object names against the
sysobjects table in each database or use object_name() for the currently
selected database.
I would obviously test the removal in the deve environment before
removing any procedures from production.
David Gugick
Imceda Software
www.imceda.comsqlsql
while?Mike W wrote:
> Is there a way to find out if a stored procedure has not been run in a
> while?
Not directly. The best you can do is create a server-side trace that
traps the SP:Starting event. You do not need to bind the TextData, only
the ObjectID, DatabaseID. Let it run until you feel you'll get the best
collection of procedures. You can resolve the object names against the
sysobjects table in each database or use object_name() for the currently
selected database.
I would obviously test the removal in the deve environment before
removing any procedures from production.
David Gugick
Imceda Software
www.imceda.comsqlsql
Clean up old stored procedures
Is there a way to find out if a stored procedure has not been run in a
while?Mike W wrote:
> Is there a way to find out if a stored procedure has not been run in a
> while?
Not directly. The best you can do is create a server-side trace that
traps the SP:Starting event. You do not need to bind the TextData, only
the ObjectID, DatabaseID. Let it run until you feel you'll get the best
collection of procedures. You can resolve the object names against the
sysobjects table in each database or use object_name() for the currently
selected database.
I would obviously test the removal in the deve environment before
removing any procedures from production.
David Gugick
Imceda Software
www.imceda.com
while?Mike W wrote:
> Is there a way to find out if a stored procedure has not been run in a
> while?
Not directly. The best you can do is create a server-side trace that
traps the SP:Starting event. You do not need to bind the TextData, only
the ObjectID, DatabaseID. Let it run until you feel you'll get the best
collection of procedures. You can resolve the object names against the
sysobjects table in each database or use object_name() for the currently
selected database.
I would obviously test the removal in the deve environment before
removing any procedures from production.
David Gugick
Imceda Software
www.imceda.com
clean up data - when process runs next time
In a integration project I am moving data from A to B.
First time is fine - since table B is empty.
However next time I run the process, I would like to delete all records in B before I run the project again.
What is the best way to delete / clean up data when you re run the process ?
Cheers, T
Issue a DELETE or TRUNCATE from an Execute SQL Task.
-Jamie
clean up bak files on file server
I have a maintanence plan that does a backup of all my databases and stores
the databases on a nas device. The problem is that the clean plan I have is
not removing the .bak files that are older then 4 days. How can I get my
Clean up plan be able to delete the .bak files that are 4 days old? This is
only happening with my SQL 2005 servers, my sql 2000 servers are working
correctly.
Mike,
In your SQL Server 2005 maintenance plan, you need to add a Maintenance
Cleanup Task. The expiry date on backups only marks when they can be
overwritten. The cleanup task is what actually deletes files.
RLF
"Mike" <Mike@.Ihatespam.net> wrote in message
news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>I have a maintanence plan that does a backup of all my databases and stores
>the databases on a nas device. The problem is that the clean plan I have is
>not removing the .bak files that are older then 4 days. How can I get my
>Clean up plan be able to delete the .bak files that are 4 days old? This is
>only happening with my SQL 2005 servers, my sql 2000 servers are working
>correctly.
>
>
|||I have a Clean up History plan, is that the same as 'clean up task'?
If so then that isn't deleting the files from my nas location
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
> Mike,
> In your SQL Server 2005 maintenance plan, you need to add a Maintenance
> Cleanup Task. The expiry date on backups only marks when they can be
> overwritten. The cleanup task is what actually deletes files.
> RLF
> "Mike" <Mike@.Ihatespam.net> wrote in message
> news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>
|||Its called "Maintenance Cleanup Task"
- - - - - - - - -
Thanks
Yogish
"Mike" wrote:
> I have a Clean up History plan, is that the same as 'clean up task'?
> If so then that isn't deleting the files from my nas location
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
>
>
|||Mike,
No, it is not the same. The Clean up History plan has the following
purpose:
The History Cleanup task deletes historical data about Backup and Restore,
SQL Server Agent, and Maintenance Plan operations. This wizard allows you
to specify the type and age of the data to be deleted.
In case that is not clear (and I can understand why it might not be) it is
not deleting backups, but is deleting the history records that track your
backups, restores, and so forth. It is cleaning up tables in msdb.
RLF
"Mike" <Mike@.Ihatespam.net> wrote in message
news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>I have a Clean up History plan, is that the same as 'clean up task'?
> If so then that isn't deleting the files from my nas location
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
>
|||Where can i find the clean up task at?
Sorry for the questions but I'm a developer that is now doing DBA work, so
I'm learning as I go and so far so good, except for a few minor things.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uKQV6dheIHA.6092@.TK2MSFTNGP06.phx.gbl...
> Mike,
> No, it is not the same. The Clean up History plan has the following
> purpose:
> The History Cleanup task deletes historical data about Backup and Restore,
> SQL Server Agent, and Maintenance Plan operations. This wizard allows you
> to specify the type and age of the data to be deleted.
> In case that is not clear (and I can understand why it might not be) it is
> not deleting backups, but is deleting the history records that track your
> backups, restores, and so forth. It is cleaning up tables in msdb.
> RLF
> "Mike" <Mike@.Ihatespam.net> wrote in message
> news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>
|||I found it.
"Mike" <Mike@.Ihatespam.net> wrote in message
news:urmeVgheIHA.536@.TK2MSFTNGP06.phx.gbl...
> Where can i find the clean up task at?
>
> Sorry for the questions but I'm a developer that is now doing DBA work, so
> I'm learning as I go and so far so good, except for a few minor things.
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uKQV6dheIHA.6092@.TK2MSFTNGP06.phx.gbl...
>
|||I created a clean up task and its still not working. I have all of my .bak
files in sub folders with the db name, and I noticed that the task is only
looking in a root directory with the .bak files, is there a way to have it
search all sub folders within a directory?
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uKQV6dheIHA.6092@.TK2MSFTNGP06.phx.gbl...
> Mike,
> No, it is not the same. The Clean up History plan has the following
> purpose:
> The History Cleanup task deletes historical data about Backup and Restore,
> SQL Server Agent, and Maintenance Plan operations. This wizard allows you
> to specify the type and age of the data to be deleted.
> In case that is not clear (and I can understand why it might not be) it is
> not deleting backups, but is deleting the history records that track your
> backups, restores, and so forth. It is cleaning up tables in msdb.
> RLF
> "Mike" <Mike@.Ihatespam.net> wrote in message
> news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>
the databases on a nas device. The problem is that the clean plan I have is
not removing the .bak files that are older then 4 days. How can I get my
Clean up plan be able to delete the .bak files that are 4 days old? This is
only happening with my SQL 2005 servers, my sql 2000 servers are working
correctly.
Mike,
In your SQL Server 2005 maintenance plan, you need to add a Maintenance
Cleanup Task. The expiry date on backups only marks when they can be
overwritten. The cleanup task is what actually deletes files.
RLF
"Mike" <Mike@.Ihatespam.net> wrote in message
news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>I have a maintanence plan that does a backup of all my databases and stores
>the databases on a nas device. The problem is that the clean plan I have is
>not removing the .bak files that are older then 4 days. How can I get my
>Clean up plan be able to delete the .bak files that are 4 days old? This is
>only happening with my SQL 2005 servers, my sql 2000 servers are working
>correctly.
>
>
|||I have a Clean up History plan, is that the same as 'clean up task'?
If so then that isn't deleting the files from my nas location
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
> Mike,
> In your SQL Server 2005 maintenance plan, you need to add a Maintenance
> Cleanup Task. The expiry date on backups only marks when they can be
> overwritten. The cleanup task is what actually deletes files.
> RLF
> "Mike" <Mike@.Ihatespam.net> wrote in message
> news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>
|||Its called "Maintenance Cleanup Task"
- - - - - - - - -
Thanks
Yogish
"Mike" wrote:
> I have a Clean up History plan, is that the same as 'clean up task'?
> If so then that isn't deleting the files from my nas location
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
>
>
|||Mike,
No, it is not the same. The Clean up History plan has the following
purpose:
The History Cleanup task deletes historical data about Backup and Restore,
SQL Server Agent, and Maintenance Plan operations. This wizard allows you
to specify the type and age of the data to be deleted.
In case that is not clear (and I can understand why it might not be) it is
not deleting backups, but is deleting the history records that track your
backups, restores, and so forth. It is cleaning up tables in msdb.
RLF
"Mike" <Mike@.Ihatespam.net> wrote in message
news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>I have a Clean up History plan, is that the same as 'clean up task'?
> If so then that isn't deleting the files from my nas location
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
>
|||Where can i find the clean up task at?
Sorry for the questions but I'm a developer that is now doing DBA work, so
I'm learning as I go and so far so good, except for a few minor things.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uKQV6dheIHA.6092@.TK2MSFTNGP06.phx.gbl...
> Mike,
> No, it is not the same. The Clean up History plan has the following
> purpose:
> The History Cleanup task deletes historical data about Backup and Restore,
> SQL Server Agent, and Maintenance Plan operations. This wizard allows you
> to specify the type and age of the data to be deleted.
> In case that is not clear (and I can understand why it might not be) it is
> not deleting backups, but is deleting the history records that track your
> backups, restores, and so forth. It is cleaning up tables in msdb.
> RLF
> "Mike" <Mike@.Ihatespam.net> wrote in message
> news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>
|||I found it.
"Mike" <Mike@.Ihatespam.net> wrote in message
news:urmeVgheIHA.536@.TK2MSFTNGP06.phx.gbl...
> Where can i find the clean up task at?
>
> Sorry for the questions but I'm a developer that is now doing DBA work, so
> I'm learning as I go and so far so good, except for a few minor things.
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uKQV6dheIHA.6092@.TK2MSFTNGP06.phx.gbl...
>
|||I created a clean up task and its still not working. I have all of my .bak
files in sub folders with the db name, and I noticed that the task is only
looking in a root directory with the .bak files, is there a way to have it
search all sub folders within a directory?
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uKQV6dheIHA.6092@.TK2MSFTNGP06.phx.gbl...
> Mike,
> No, it is not the same. The Clean up History plan has the following
> purpose:
> The History Cleanup task deletes historical data about Backup and Restore,
> SQL Server Agent, and Maintenance Plan operations. This wizard allows you
> to specify the type and age of the data to be deleted.
> In case that is not clear (and I can understand why it might not be) it is
> not deleting backups, but is deleting the history records that track your
> backups, restores, and so forth. It is cleaning up tables in msdb.
> RLF
> "Mike" <Mike@.Ihatespam.net> wrote in message
> news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>
clean up bak files on file server
I have a maintanence plan that does a backup of all my databases and stores
the databases on a nas device. The problem is that the clean plan I have is
not removing the .bak files that are older then 4 days. How can I get my
Clean up plan be able to delete the .bak files that are 4 days old? This is
only happening with my SQL 2005 servers, my sql 2000 servers are working
correctly.Mike,
In your SQL Server 2005 maintenance plan, you need to add a Maintenance
Cleanup Task. The expiry date on backups only marks when they can be
overwritten. The cleanup task is what actually deletes files.
RLF
"Mike" <Mike@.Ihatespam.net> wrote in message
news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>I have a maintanence plan that does a backup of all my databases and stores
>the databases on a nas device. The problem is that the clean plan I have is
>not removing the .bak files that are older then 4 days. How can I get my
>Clean up plan be able to delete the .bak files that are 4 days old? This is
>only happening with my SQL 2005 servers, my sql 2000 servers are working
>correctly.
>
>|||I have a Clean up History plan, is that the same as 'clean up task'?
If so then that isn't deleting the files from my nas location
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
> Mike,
> In your SQL Server 2005 maintenance plan, you need to add a Maintenance
> Cleanup Task. The expiry date on backups only marks when they can be
> overwritten. The cleanup task is what actually deletes files.
> RLF
> "Mike" <Mike@.Ihatespam.net> wrote in message
> news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>>I have a maintanence plan that does a backup of all my databases and
>>stores the databases on a nas device. The problem is that the clean plan I
>>have is not removing the .bak files that are older then 4 days. How can I
>>get my Clean up plan be able to delete the .bak files that are 4 days old?
>>This is only happening with my SQL 2005 servers, my sql 2000 servers are
>>working correctly.
>>
>|||Its called "Maintenance Cleanup Task"
--
- - - - - - - - -
Thanks
Yogish
"Mike" wrote:
> I have a Clean up History plan, is that the same as 'clean up task'?
> If so then that isn't deleting the files from my nas location
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
> > Mike,
> >
> > In your SQL Server 2005 maintenance plan, you need to add a Maintenance
> > Cleanup Task. The expiry date on backups only marks when they can be
> > overwritten. The cleanup task is what actually deletes files.
> >
> > RLF
> >
> > "Mike" <Mike@.Ihatespam.net> wrote in message
> > news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
> >>I have a maintanence plan that does a backup of all my databases and
> >>stores the databases on a nas device. The problem is that the clean plan I
> >>have is not removing the .bak files that are older then 4 days. How can I
> >>get my Clean up plan be able to delete the .bak files that are 4 days old?
> >>This is only happening with my SQL 2005 servers, my sql 2000 servers are
> >>working correctly.
> >>
> >>
> >>
> >
> >
>
>|||Mike,
No, it is not the same. The Clean up History plan has the following
purpose:
The History Cleanup task deletes historical data about Backup and Restore,
SQL Server Agent, and Maintenance Plan operations. This wizard allows you
to specify the type and age of the data to be deleted.
In case that is not clear (and I can understand why it might not be) it is
not deleting backups, but is deleting the history records that track your
backups, restores, and so forth. It is cleaning up tables in msdb.
RLF
"Mike" <Mike@.Ihatespam.net> wrote in message
news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>I have a Clean up History plan, is that the same as 'clean up task'?
> If so then that isn't deleting the files from my nas location
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Mike,
>> In your SQL Server 2005 maintenance plan, you need to add a Maintenance
>> Cleanup Task. The expiry date on backups only marks when they can be
>> overwritten. The cleanup task is what actually deletes files.
>> RLF
>> "Mike" <Mike@.Ihatespam.net> wrote in message
>> news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>>I have a maintanence plan that does a backup of all my databases and
>>stores the databases on a nas device. The problem is that the clean plan
>>I have is not removing the .bak files that are older then 4 days. How can
>>I get my Clean up plan be able to delete the .bak files that are 4 days
>>old? This is only happening with my SQL 2005 servers, my sql 2000 servers
>>are working correctly.
>>
>>
>|||Where can i find the clean up task at?
Sorry for the questions but I'm a developer that is now doing DBA work, so
I'm learning as I go and so far so good, except for a few minor things.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uKQV6dheIHA.6092@.TK2MSFTNGP06.phx.gbl...
> Mike,
> No, it is not the same. The Clean up History plan has the following
> purpose:
> The History Cleanup task deletes historical data about Backup and Restore,
> SQL Server Agent, and Maintenance Plan operations. This wizard allows you
> to specify the type and age of the data to be deleted.
> In case that is not clear (and I can understand why it might not be) it is
> not deleting backups, but is deleting the history records that track your
> backups, restores, and so forth. It is cleaning up tables in msdb.
> RLF
> "Mike" <Mike@.Ihatespam.net> wrote in message
> news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>>I have a Clean up History plan, is that the same as 'clean up task'?
>> If so then that isn't deleting the files from my nas location
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Mike,
>> In your SQL Server 2005 maintenance plan, you need to add a Maintenance
>> Cleanup Task. The expiry date on backups only marks when they can be
>> overwritten. The cleanup task is what actually deletes files.
>> RLF
>> "Mike" <Mike@.Ihatespam.net> wrote in message
>> news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>>I have a maintanence plan that does a backup of all my databases and
>>stores the databases on a nas device. The problem is that the clean plan
>>I have is not removing the .bak files that are older then 4 days. How
>>can I get my Clean up plan be able to delete the .bak files that are 4
>>days old? This is only happening with my SQL 2005 servers, my sql 2000
>>servers are working correctly.
>>
>>
>>
>|||I found it.
"Mike" <Mike@.Ihatespam.net> wrote in message
news:urmeVgheIHA.536@.TK2MSFTNGP06.phx.gbl...
> Where can i find the clean up task at?
>
> Sorry for the questions but I'm a developer that is now doing DBA work, so
> I'm learning as I go and so far so good, except for a few minor things.
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uKQV6dheIHA.6092@.TK2MSFTNGP06.phx.gbl...
>> Mike,
>> No, it is not the same. The Clean up History plan has the following
>> purpose:
>> The History Cleanup task deletes historical data about Backup and
>> Restore, SQL Server Agent, and Maintenance Plan operations. This wizard
>> allows you to specify the type and age of the data to be deleted.
>> In case that is not clear (and I can understand why it might not be) it
>> is not deleting backups, but is deleting the history records that track
>> your backups, restores, and so forth. It is cleaning up tables in msdb.
>> RLF
>> "Mike" <Mike@.Ihatespam.net> wrote in message
>> news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>>I have a Clean up History plan, is that the same as 'clean up task'?
>> If so then that isn't deleting the files from my nas location
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Mike,
>> In your SQL Server 2005 maintenance plan, you need to add a Maintenance
>> Cleanup Task. The expiry date on backups only marks when they can be
>> overwritten. The cleanup task is what actually deletes files.
>> RLF
>> "Mike" <Mike@.Ihatespam.net> wrote in message
>> news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>>I have a maintanence plan that does a backup of all my databases and
>>stores the databases on a nas device. The problem is that the clean
>>plan I have is not removing the .bak files that are older then 4 days.
>>How can I get my Clean up plan be able to delete the .bak files that
>>are 4 days old? This is only happening with my SQL 2005 servers, my sql
>>2000 servers are working correctly.
>>
>>
>>
>>
>|||I created a clean up task and its still not working. I have all of my .bak
files in sub folders with the db name, and I noticed that the task is only
looking in a root directory with the .bak files, is there a way to have it
search all sub folders within a directory?
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uKQV6dheIHA.6092@.TK2MSFTNGP06.phx.gbl...
> Mike,
> No, it is not the same. The Clean up History plan has the following
> purpose:
> The History Cleanup task deletes historical data about Backup and Restore,
> SQL Server Agent, and Maintenance Plan operations. This wizard allows you
> to specify the type and age of the data to be deleted.
> In case that is not clear (and I can understand why it might not be) it is
> not deleting backups, but is deleting the history records that track your
> backups, restores, and so forth. It is cleaning up tables in msdb.
> RLF
> "Mike" <Mike@.Ihatespam.net> wrote in message
> news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>>I have a Clean up History plan, is that the same as 'clean up task'?
>> If so then that isn't deleting the files from my nas location
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Mike,
>> In your SQL Server 2005 maintenance plan, you need to add a Maintenance
>> Cleanup Task. The expiry date on backups only marks when they can be
>> overwritten. The cleanup task is what actually deletes files.
>> RLF
>> "Mike" <Mike@.Ihatespam.net> wrote in message
>> news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>>I have a maintanence plan that does a backup of all my databases and
>>stores the databases on a nas device. The problem is that the clean plan
>>I have is not removing the .bak files that are older then 4 days. How
>>can I get my Clean up plan be able to delete the .bak files that are 4
>>days old? This is only happening with my SQL 2005 servers, my sql 2000
>>servers are working correctly.
>>
>>
>>
>|||As of sp1, the cleanup task has an option to traverse subdirectories.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mike" <Mike@.Ihatespam.net> wrote in message news:uuFilmheIHA.4144@.TK2MSFTNGP05.phx.gbl...
>I created a clean up task and its still not working. I have all of my .bak files in sub folders
>with the db name, and I noticed that the task is only looking in a root directory with the .bak
>files, is there a way to have it search all sub folders within a directory?
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uKQV6dheIHA.6092@.TK2MSFTNGP06.phx.gbl...
>> Mike,
>> No, it is not the same. The Clean up History plan has the following purpose:
>> The History Cleanup task deletes historical data about Backup and Restore, SQL Server Agent, and
>> Maintenance Plan operations. This wizard allows you to specify the type and age of the data to
>> be deleted.
>> In case that is not clear (and I can understand why it might not be) it is not deleting backups,
>> but is deleting the history records that track your backups, restores, and so forth. It is
>> cleaning up tables in msdb.
>> RLF
>> "Mike" <Mike@.Ihatespam.net> wrote in message news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>>I have a Clean up History plan, is that the same as 'clean up task'?
>> If so then that isn't deleting the files from my nas location
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Mike,
>> In your SQL Server 2005 maintenance plan, you need to add a Maintenance Cleanup Task. The
>> expiry date on backups only marks when they can be overwritten. The cleanup task is what
>> actually deletes files.
>> RLF
>> "Mike" <Mike@.Ihatespam.net> wrote in message news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>>I have a maintanence plan that does a backup of all my databases and stores the databases on a
>>nas device. The problem is that the clean plan I have is not removing the .bak files that are
>>older then 4 days. How can I get my Clean up plan be able to delete the .bak files that are 4
>>days old? This is only happening with my SQL 2005 servers, my sql 2000 servers are working
>>correctly.
>>
>>
>>
>>
>
the databases on a nas device. The problem is that the clean plan I have is
not removing the .bak files that are older then 4 days. How can I get my
Clean up plan be able to delete the .bak files that are 4 days old? This is
only happening with my SQL 2005 servers, my sql 2000 servers are working
correctly.Mike,
In your SQL Server 2005 maintenance plan, you need to add a Maintenance
Cleanup Task. The expiry date on backups only marks when they can be
overwritten. The cleanup task is what actually deletes files.
RLF
"Mike" <Mike@.Ihatespam.net> wrote in message
news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>I have a maintanence plan that does a backup of all my databases and stores
>the databases on a nas device. The problem is that the clean plan I have is
>not removing the .bak files that are older then 4 days. How can I get my
>Clean up plan be able to delete the .bak files that are 4 days old? This is
>only happening with my SQL 2005 servers, my sql 2000 servers are working
>correctly.
>
>|||I have a Clean up History plan, is that the same as 'clean up task'?
If so then that isn't deleting the files from my nas location
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
> Mike,
> In your SQL Server 2005 maintenance plan, you need to add a Maintenance
> Cleanup Task. The expiry date on backups only marks when they can be
> overwritten. The cleanup task is what actually deletes files.
> RLF
> "Mike" <Mike@.Ihatespam.net> wrote in message
> news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>>I have a maintanence plan that does a backup of all my databases and
>>stores the databases on a nas device. The problem is that the clean plan I
>>have is not removing the .bak files that are older then 4 days. How can I
>>get my Clean up plan be able to delete the .bak files that are 4 days old?
>>This is only happening with my SQL 2005 servers, my sql 2000 servers are
>>working correctly.
>>
>|||Its called "Maintenance Cleanup Task"
--
- - - - - - - - -
Thanks
Yogish
"Mike" wrote:
> I have a Clean up History plan, is that the same as 'clean up task'?
> If so then that isn't deleting the files from my nas location
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
> > Mike,
> >
> > In your SQL Server 2005 maintenance plan, you need to add a Maintenance
> > Cleanup Task. The expiry date on backups only marks when they can be
> > overwritten. The cleanup task is what actually deletes files.
> >
> > RLF
> >
> > "Mike" <Mike@.Ihatespam.net> wrote in message
> > news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
> >>I have a maintanence plan that does a backup of all my databases and
> >>stores the databases on a nas device. The problem is that the clean plan I
> >>have is not removing the .bak files that are older then 4 days. How can I
> >>get my Clean up plan be able to delete the .bak files that are 4 days old?
> >>This is only happening with my SQL 2005 servers, my sql 2000 servers are
> >>working correctly.
> >>
> >>
> >>
> >
> >
>
>|||Mike,
No, it is not the same. The Clean up History plan has the following
purpose:
The History Cleanup task deletes historical data about Backup and Restore,
SQL Server Agent, and Maintenance Plan operations. This wizard allows you
to specify the type and age of the data to be deleted.
In case that is not clear (and I can understand why it might not be) it is
not deleting backups, but is deleting the history records that track your
backups, restores, and so forth. It is cleaning up tables in msdb.
RLF
"Mike" <Mike@.Ihatespam.net> wrote in message
news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>I have a Clean up History plan, is that the same as 'clean up task'?
> If so then that isn't deleting the files from my nas location
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Mike,
>> In your SQL Server 2005 maintenance plan, you need to add a Maintenance
>> Cleanup Task. The expiry date on backups only marks when they can be
>> overwritten. The cleanup task is what actually deletes files.
>> RLF
>> "Mike" <Mike@.Ihatespam.net> wrote in message
>> news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>>I have a maintanence plan that does a backup of all my databases and
>>stores the databases on a nas device. The problem is that the clean plan
>>I have is not removing the .bak files that are older then 4 days. How can
>>I get my Clean up plan be able to delete the .bak files that are 4 days
>>old? This is only happening with my SQL 2005 servers, my sql 2000 servers
>>are working correctly.
>>
>>
>|||Where can i find the clean up task at?
Sorry for the questions but I'm a developer that is now doing DBA work, so
I'm learning as I go and so far so good, except for a few minor things.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uKQV6dheIHA.6092@.TK2MSFTNGP06.phx.gbl...
> Mike,
> No, it is not the same. The Clean up History plan has the following
> purpose:
> The History Cleanup task deletes historical data about Backup and Restore,
> SQL Server Agent, and Maintenance Plan operations. This wizard allows you
> to specify the type and age of the data to be deleted.
> In case that is not clear (and I can understand why it might not be) it is
> not deleting backups, but is deleting the history records that track your
> backups, restores, and so forth. It is cleaning up tables in msdb.
> RLF
> "Mike" <Mike@.Ihatespam.net> wrote in message
> news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>>I have a Clean up History plan, is that the same as 'clean up task'?
>> If so then that isn't deleting the files from my nas location
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Mike,
>> In your SQL Server 2005 maintenance plan, you need to add a Maintenance
>> Cleanup Task. The expiry date on backups only marks when they can be
>> overwritten. The cleanup task is what actually deletes files.
>> RLF
>> "Mike" <Mike@.Ihatespam.net> wrote in message
>> news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>>I have a maintanence plan that does a backup of all my databases and
>>stores the databases on a nas device. The problem is that the clean plan
>>I have is not removing the .bak files that are older then 4 days. How
>>can I get my Clean up plan be able to delete the .bak files that are 4
>>days old? This is only happening with my SQL 2005 servers, my sql 2000
>>servers are working correctly.
>>
>>
>>
>|||I found it.
"Mike" <Mike@.Ihatespam.net> wrote in message
news:urmeVgheIHA.536@.TK2MSFTNGP06.phx.gbl...
> Where can i find the clean up task at?
>
> Sorry for the questions but I'm a developer that is now doing DBA work, so
> I'm learning as I go and so far so good, except for a few minor things.
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uKQV6dheIHA.6092@.TK2MSFTNGP06.phx.gbl...
>> Mike,
>> No, it is not the same. The Clean up History plan has the following
>> purpose:
>> The History Cleanup task deletes historical data about Backup and
>> Restore, SQL Server Agent, and Maintenance Plan operations. This wizard
>> allows you to specify the type and age of the data to be deleted.
>> In case that is not clear (and I can understand why it might not be) it
>> is not deleting backups, but is deleting the history records that track
>> your backups, restores, and so forth. It is cleaning up tables in msdb.
>> RLF
>> "Mike" <Mike@.Ihatespam.net> wrote in message
>> news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>>I have a Clean up History plan, is that the same as 'clean up task'?
>> If so then that isn't deleting the files from my nas location
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Mike,
>> In your SQL Server 2005 maintenance plan, you need to add a Maintenance
>> Cleanup Task. The expiry date on backups only marks when they can be
>> overwritten. The cleanup task is what actually deletes files.
>> RLF
>> "Mike" <Mike@.Ihatespam.net> wrote in message
>> news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>>I have a maintanence plan that does a backup of all my databases and
>>stores the databases on a nas device. The problem is that the clean
>>plan I have is not removing the .bak files that are older then 4 days.
>>How can I get my Clean up plan be able to delete the .bak files that
>>are 4 days old? This is only happening with my SQL 2005 servers, my sql
>>2000 servers are working correctly.
>>
>>
>>
>>
>|||I created a clean up task and its still not working. I have all of my .bak
files in sub folders with the db name, and I noticed that the task is only
looking in a root directory with the .bak files, is there a way to have it
search all sub folders within a directory?
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uKQV6dheIHA.6092@.TK2MSFTNGP06.phx.gbl...
> Mike,
> No, it is not the same. The Clean up History plan has the following
> purpose:
> The History Cleanup task deletes historical data about Backup and Restore,
> SQL Server Agent, and Maintenance Plan operations. This wizard allows you
> to specify the type and age of the data to be deleted.
> In case that is not clear (and I can understand why it might not be) it is
> not deleting backups, but is deleting the history records that track your
> backups, restores, and so forth. It is cleaning up tables in msdb.
> RLF
> "Mike" <Mike@.Ihatespam.net> wrote in message
> news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>>I have a Clean up History plan, is that the same as 'clean up task'?
>> If so then that isn't deleting the files from my nas location
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Mike,
>> In your SQL Server 2005 maintenance plan, you need to add a Maintenance
>> Cleanup Task. The expiry date on backups only marks when they can be
>> overwritten. The cleanup task is what actually deletes files.
>> RLF
>> "Mike" <Mike@.Ihatespam.net> wrote in message
>> news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>>I have a maintanence plan that does a backup of all my databases and
>>stores the databases on a nas device. The problem is that the clean plan
>>I have is not removing the .bak files that are older then 4 days. How
>>can I get my Clean up plan be able to delete the .bak files that are 4
>>days old? This is only happening with my SQL 2005 servers, my sql 2000
>>servers are working correctly.
>>
>>
>>
>|||As of sp1, the cleanup task has an option to traverse subdirectories.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mike" <Mike@.Ihatespam.net> wrote in message news:uuFilmheIHA.4144@.TK2MSFTNGP05.phx.gbl...
>I created a clean up task and its still not working. I have all of my .bak files in sub folders
>with the db name, and I noticed that the task is only looking in a root directory with the .bak
>files, is there a way to have it search all sub folders within a directory?
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uKQV6dheIHA.6092@.TK2MSFTNGP06.phx.gbl...
>> Mike,
>> No, it is not the same. The Clean up History plan has the following purpose:
>> The History Cleanup task deletes historical data about Backup and Restore, SQL Server Agent, and
>> Maintenance Plan operations. This wizard allows you to specify the type and age of the data to
>> be deleted.
>> In case that is not clear (and I can understand why it might not be) it is not deleting backups,
>> but is deleting the history records that track your backups, restores, and so forth. It is
>> cleaning up tables in msdb.
>> RLF
>> "Mike" <Mike@.Ihatespam.net> wrote in message news:uS02iPheIHA.5208@.TK2MSFTNGP04.phx.gbl...
>>I have a Clean up History plan, is that the same as 'clean up task'?
>> If so then that isn't deleting the files from my nas location
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:%23%23qqdEheIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Mike,
>> In your SQL Server 2005 maintenance plan, you need to add a Maintenance Cleanup Task. The
>> expiry date on backups only marks when they can be overwritten. The cleanup task is what
>> actually deletes files.
>> RLF
>> "Mike" <Mike@.Ihatespam.net> wrote in message news:ul47jBheIHA.4696@.TK2MSFTNGP05.phx.gbl...
>>I have a maintanence plan that does a backup of all my databases and stores the databases on a
>>nas device. The problem is that the clean plan I have is not removing the .bak files that are
>>older then 4 days. How can I get my Clean up plan be able to delete the .bak files that are 4
>>days old? This is only happening with my SQL 2005 servers, my sql 2000 servers are working
>>correctly.
>>
>>
>>
>>
>
Clean up agent
is there a way to tell the status of a clean up agent?
Nothing is in the history for him , but when I right click on him my
option is "stop agent".. start agent is grey'd out which tells me he is
running. I do a sp_who2 on the dist. and don't see anything about him
there.
What else can i look for.
Thanks,
-comb
What about if you run sp_help_job and look at 'current_execution_status'?
Rgds,
Paul Ibison
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.replication:63692
In article <OsgLtBJXFHA.2420@.TK2MSFTNGP12.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> What about if you run sp_help_job and look at 'current_execution_status'?
> Rgds,
> Paul Ibison
>
>
yea i just did that and it does tell me that much.
thanks though paul. I appreciate your help as always.
-comb
sqlsql
Nothing is in the history for him , but when I right click on him my
option is "stop agent".. start agent is grey'd out which tells me he is
running. I do a sp_who2 on the dist. and don't see anything about him
there.
What else can i look for.
Thanks,
-comb
What about if you run sp_help_job and look at 'current_execution_status'?
Rgds,
Paul Ibison
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.replication:63692
In article <OsgLtBJXFHA.2420@.TK2MSFTNGP12.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> What about if you run sp_help_job and look at 'current_execution_status'?
> Rgds,
> Paul Ibison
>
>
yea i just did that and it does tell me that much.
thanks though paul. I appreciate your help as always.
-comb
sqlsql
Clean up a table & save to another table
I'm trying to filter and update the table AllAgreements (below) by
moving parts of each row into appropriate tables and deleting redundant
rows. This table is just a scratch table used while importing old data
into a new DB.
The situation: A company supplies credits to its customers (other
companies [construction] called Developers) that the customers
(Developers) can use to reduce any bills sent to them by the company.
Each time the credits are supplied they are referred to as a Credit
Agreement. The developers can transfer those credits, or parts of those
credits, to other developers. When a transfer is made they are creating
a new agreement. The Agreement identifier, in correspondence, uses a
user constructed identifier (ID #). The company keeps the original
credit agreement identifier & just adds a sequential letter suffix
representing each iteration of a new agreement off the original (because
the original agreement has one cut-off date that is enforced for all
subsequent agreements that use credits from the original credit agreement).
E.g.:
1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
3,'FIF2 GRNBR 001A','JUDY',20000.00
5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
4,'FIF2 GRNBR 001B','SUSIE',10000.00
6,'TIF1 ACSPA 001','WILLY',30000.00 <- original agreement
7,'TIF1 ACSPA 002','WILLY',45000.00 <- original agreement
8,'TIF1 MSSH 001','FRED',25000.00 <- original agreement
9,'TIF1 MSSH 001A','FRED',-10000.00 <- Fred transferred $10K to Harry
10,'TIF1 MSSH 001A','HARRY',10000.00
11,'TIF1 MSSH 001B','HARRY',-100000.00 <- Harry transferred $10K to Joe
12,'TIF1 MSSH 001B','JOE',10000.00
I need to generate a new row in the table Transactions (below) for both
the debit on the original credit agreement and the transfer/credit for
the new credit agreement.
/* BEGIN DDL */
set nocount on
CREATE TABLE AllAgreements ( -- scratch table - no PK
ca_id INTEGER NOT NULL UNIQUE,
"ID #" VARCHAR(20) NOT NULL,
Developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
issued_date DATETIME NOT NULL,
amt decimal(9,2) NOT NULL
)
CREATE TABLE Transactions (
ca_id INTEGER NOT NULL REFERENCES AllAgreements,
developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
transaction_type INTEGER NOT NULL
CHECK (transaction_type BETWEEN 1 AND 5),
applied_date DATETIME NOT NULL,
amount DECIMAL(9,2) NOT NULL,
CONSTRAINT PK_Transactions
PRIMARY KEY (ca_id, developer, transaction_type, applied_date)
)
INSERT INTO AllAgreements
VALUES (1,'FIF2 GRNBR 001','LUCY','20050115',55000.00)
INSERT INTO AllAgreements
VALUES (2,'FIF2 GRNBR 001A','LUCY','20050122',-20000.00)
INSERT INTO AllAgreements
VALUES (3,'FIF2 GRNBR 001A','JUDY','20050122',20000.00)
INSERT INTO AllAgreements
VALUES (4,'FIF2 GRNBR 001B','SUSIE','20050122',10000.00)
INSERT INTO AllAgreements
VALUES (5,'FIF2 GRNBR 001B','JUDY','20050122',-10000.00)
INSERT INTO AllAgreements
VALUES (6,'TIF1 ACSPA 001','WILLY','20050211',30000.00)
INSERT INTO AllAgreements
VALUES (7,'TIF1 ACSPA 002','WILLY','20050211',45000.00)
INSERT INTO AllAgreements
VALUES (8,'TIF1 MSSH 001','FRED','20050212',25000.00)
INSERT INTO AllAgreements
VALUES (9,'TIF1 MSSH 001A','FRED','20050212',-10000.00)
INSERT INTO AllAgreements
VALUES (10,'TIF1 MSSH 001A','HARRY','20050212',10000.00)
INSERT INTO AllAgreements
VALUES (11,'TIF1 MSSH 01B','HARRY','20050225',-100000.00)
INSERT INTO AllAgreements
VALUES (12,'TIF1 MSSH 001B','JOE','20050225',10000.00)
/* desired results in table AllAgreements (I'll remove the Amt column
after the clean up & the values are in the Transactions table. Didn't
show the date in order to display row on one line).
ca_id ID # Developer amt
1 FIF2 GRNBR 001 LUCY $55,000.00
3 FIF2 GRNBR 001A JUDY $20,000.00 <- Lucy transferred to Judy
4 FIF2 GRNBR 001B SUSIE $10,000.00 <- Judy transferred to Susie
6 TIF1 ACSPA 001 WILLY $30,000.00
7 TIF1 ACSPA 002 WILLY $45,000.00
8 TIF1 MSSH 001 FRED $25,000.00
10 TIF1 MSSH 001A HARRY $10,000.00 <- Fred transferred to Harry
12 TIF1 MSSH 001B JOE $10,000.00 <- Harry transferred to Joe
*/
/* desired results in table Transactions:
(1 = Original credit agreement; 2 = transfer of CR agreement)
E.g.: below the 1st 2 LUCY lines show the original credit
(trans_type=1) and the debit (trans_type=2) on the ID # "FIF2 GRNBR 001"
caused by the transfer to JUDY (line 3). The JUDY line's ca_id, 3,
points to the new credit agreement "FIF2 GRNBR 001A" row in the
AllAgreements table.
ca_id developer transaction_type applied_date amount
-- -- -- -- --
1 LUCY 1 20050115 55000.00
1 LUCY 2 20050122 -20000.00
3 JUDY 2 20050122 20000.00
4 SUSIE 2 20050122 10000.00
3 JUDY 2 20050122 -10000.00
6 WILLY 1 20050211 30000.00
7 WILLY 1 20050211 45000.00
8 FRED 1 20050212 25000.00
8 FRED 2 20050212 -10000.00
10 HARRY 2 20050212 10000.00
10 HARRY 2 20050225 -100000.00
12 JOE 2 20050225 10000.00
*/
DROP TABLE AllAgreements, Transactions
/* END */
QUESTION: What append command will move the amounts to the Transactions
table w/ the correct ca_id value.
Thanks for your help.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)Hi
See inline:
"MGFoster" <mgf00@.earthlink.net> wrote in message
news:8waoe.562$HM.259@.newsread1.news.pas.earthlink.net...
> I'm trying to filter and update the table AllAgreements (below) by moving
> parts of each row into appropriate tables and deleting redundant rows.
> This table is just a scratch table used while importing old data into a
> new DB.
> The situation: A company supplies credits to its customers (other
> companies [construction] called Developers) that the customers
> (Developers) can use to reduce any bills sent to them by the company. Each
> time the credits are supplied they are referred to as a Credit Agreement.
> The developers can transfer those credits, or parts of those credits, to
> other developers. When a transfer is made they are creating a new
> agreement. The Agreement identifier, in correspondence, uses a user
> constructed identifier (ID #). The company keeps the original credit
> agreement identifier & just adds a sequential letter suffix representing
> each iteration of a new agreement off the original (because the original
> agreement has one cut-off date that is enforced for all subsequent
> agreements that use credits from the original credit agreement).
> E.g.:
> 1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
> 2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
> 3,'FIF2 GRNBR 001A','JUDY',20000.00
> 5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
> 4,'FIF2 GRNBR 001B','SUSIE',10000.00
> 6,'TIF1 ACSPA 001','WILLY',30000.00 <- original agreement
> 7,'TIF1 ACSPA 002','WILLY',45000.00 <- original agreement
> 8,'TIF1 MSSH 001','FRED',25000.00 <- original agreement
> 9,'TIF1 MSSH 001A','FRED',-10000.00 <- Fred transferred $10K to Harry
> 10,'TIF1 MSSH 001A','HARRY',10000.00
> 11,'TIF1 MSSH 001B','HARRY',-100000.00 <- Harry transferred $10K to Joe
> 12,'TIF1 MSSH 001B','JOE',10000.00
> I need to generate a new row in the table Transactions (below) for both
> the debit on the original credit agreement and the transfer/credit for the
> new credit agreement.
> /* BEGIN DDL */
> set nocount on
> CREATE TABLE AllAgreements ( -- scratch table - no PK
> ca_id INTEGER NOT NULL UNIQUE,
> "ID #" VARCHAR(20) NOT NULL,
> Developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
> issued_date DATETIME NOT NULL,
> amt decimal(9,2) NOT NULL
> )
> CREATE TABLE Transactions (
> ca_id INTEGER NOT NULL REFERENCES AllAgreements,
> developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
> transaction_type INTEGER NOT NULL
> CHECK (transaction_type BETWEEN 1 AND 5),
> applied_date DATETIME NOT NULL,
> amount DECIMAL(9,2) NOT NULL,
> CONSTRAINT PK_Transactions
> PRIMARY KEY (ca_id, developer, transaction_type, applied_date)
> )
> INSERT INTO AllAgreements
> VALUES (1,'FIF2 GRNBR 001','LUCY','20050115',55000.00)
> INSERT INTO AllAgreements
> VALUES (2,'FIF2 GRNBR 001A','LUCY','20050122',-20000.00)
> INSERT INTO AllAgreements
> VALUES (3,'FIF2 GRNBR 001A','JUDY','20050122',20000.00)
> INSERT INTO AllAgreements
> VALUES (4,'FIF2 GRNBR 001B','SUSIE','20050122',10000.00)
> INSERT INTO AllAgreements
> VALUES (5,'FIF2 GRNBR 001B','JUDY','20050122',-10000.00)
> INSERT INTO AllAgreements
> VALUES (6,'TIF1 ACSPA 001','WILLY','20050211',30000.00)
> INSERT INTO AllAgreements
> VALUES (7,'TIF1 ACSPA 002','WILLY','20050211',45000.00)
> INSERT INTO AllAgreements
> VALUES (8,'TIF1 MSSH 001','FRED','20050212',25000.00)
> INSERT INTO AllAgreements
> VALUES (9,'TIF1 MSSH 001A','FRED','20050212',-10000.00)
> INSERT INTO AllAgreements
> VALUES (10,'TIF1 MSSH 001A','HARRY','20050212',10000.00)
> INSERT INTO AllAgreements
> VALUES (11,'TIF1 MSSH 01B','HARRY','20050225',-100000.00)
> INSERT INTO AllAgreements
> VALUES (12,'TIF1 MSSH 001B','JOE','20050225',10000.00)
> /* desired results in table AllAgreements (I'll remove the Amt column
> after the clean up & the values are in the Transactions table. Didn't
> show the date in order to display row on one line).
> ca_id ID # Developer amt
> 1 FIF2 GRNBR 001 LUCY $55,000.00
> 3 FIF2 GRNBR 001A JUDY $20,000.00 <- Lucy transferred to Judy
> 4 FIF2 GRNBR 001B SUSIE $10,000.00 <- Judy transferred to Susie
> 6 TIF1 ACSPA 001 WILLY $30,000.00
> 7 TIF1 ACSPA 002 WILLY $45,000.00
> 8 TIF1 MSSH 001 FRED $25,000.00
> 10 TIF1 MSSH 001A HARRY $10,000.00 <- Fred transferred to Harry
> 12 TIF1 MSSH 001B JOE $10,000.00 <- Harry transferred to Joe
> */
This seems to be
SELECT A.ca_id,A.[ID #],A.Developer,
CONVERT(char(10),A.issued_date,112) As Applied_date,
A.Amt FROM AllAgreements A
WHERE A.Amt > 0
order by A.ca_id
> /* desired results in table Transactions:
> (1 = Original credit agreement; 2 = transfer of CR agreement)
> E.g.: below the 1st 2 LUCY lines show the original credit (trans_type=1)
> and the debit (trans_type=2) on the ID # "FIF2 GRNBR 001" caused by the
> transfer to JUDY (line 3). The JUDY line's ca_id, 3, points to the new
> credit agreement "FIF2 GRNBR 001A" row in the AllAgreements table.
> ca_id developer transaction_type applied_date amount
> -- -- -- -- --
> 1 LUCY 1 20050115 55000.00
> 1 LUCY 2 20050122 -20000.00
> 3 JUDY 2 20050122 20000.00
> 4 SUSIE 2 20050122 10000.00
> 3 JUDY 2 20050122 -10000.00
> 6 WILLY 1 20050211 30000.00
> 7 WILLY 1 20050211 45000.00
> 8 FRED 1 20050212 25000.00
> 8 FRED 2 20050212 -10000.00
> 10 HARRY 2 20050212 10000.00
> 10 HARRY 2 20050225 -100000.00
> 12 JOE 2 20050225 10000.00
> */
>
I can't see the login in the ca_id column as they do not relate to the
transaction concerned! But this is almost what you wanted:
SELECT A.ca_id,--A.[ID #],
B.Developer,
2 Transaction_Type,
CONVERT(char(10),A.issued_date,112) As Applied_date,
B.AMT AS AMT
FROM AllAgreements A
JOIN AllAgreements B ON A.ca_id <> B.ca_id
AND A.[ID #] = B.[ID #]
AND A.Developer <> B.Developer
AND A.issued_date = B.issued_date
AND A.AMT = -1 * B.AMT
WHERE A.AMT > 0
UNION ALL
SELECT A.ca_id,--A.[ID #],
A.Developer AS Developer,
CASE WHEN B.ca_id IS NULL THEN 1 ELSE 2 END AS Transaction_Type,
CONVERT(char(10),A.issued_date,112) As Applied_date,
A.AMT AS AMT
FROM AllAgreements A
LEFT JOIN AllAgreements B ON A.ca_id <> B.ca_id
AND A.[ID #] = B.[ID #]
AND A.Developer <> B.Developer
AND A.issued_date = B.issued_date
AND A.AMT = -1 * B.AMT
WHERE A.AMT > 0
ORDER BY A.CA_ID, AMT
> DROP TABLE AllAgreements, Transactions
> /* END */
>
> QUESTION: What append command will move the amounts to the Transactions
> table w/ the correct ca_id value.
>
You will have to decide what the criteria is for the ca_id.
John
> Thanks for your help.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)|||John Bell wrote:
> I can't see the login [do you mean logic?] in the ca_id column as they do
not relate to the
> transaction concerned! But this is almost what you wanted:
> SELECT A.ca_id,--A.[ID #],
> B.Developer,
> 2 Transaction_Type,
> CONVERT(char(10),A.issued_date,112) As Applied_date,
> B.AMT AS AMT
> FROM AllAgreements A
> JOIN AllAgreements B ON A.ca_id <> B.ca_id
> AND A.[ID #] = B.[ID #]
> AND A.Developer <> B.Developer
> AND A.issued_date = B.issued_date
> AND A.AMT = -1 * B.AMT
> WHERE A.AMT > 0
> UNION ALL
> SELECT A.ca_id,--A.[ID #],
> A.Developer AS Developer,
> CASE WHEN B.ca_id IS NULL THEN 1 ELSE 2 END AS Transaction_Type,
> CONVERT(char(10),A.issued_date,112) As Applied_date,
> A.AMT AS AMT
> FROM AllAgreements A
> LEFT JOIN AllAgreements B ON A.ca_id <> B.ca_id
> AND A.[ID #] = B.[ID #]
> AND A.Developer <> B.Developer
> AND A.issued_date = B.issued_date
> AND A.AMT = -1 * B.AMT
> WHERE A.AMT > 0
> ORDER BY A.CA_ID, AMT
>
> You will have to decide what the criteria is for the ca_id.
>
Ah, but that's just what I need the query to do - figure out which ca_id
goes w/ each transfer's credit transaction.
Think of it this way: Lucy transfers $20K from agreement FIF2 GRNBR 001
(ca_id 1) to Judy, which makes a new agreement FIF2 GRNBR 001A (ca_id 2)
for Judy. I need to show in the Transactions table a debit of $20K from
ca_id 1 and a credit to ca_id 2 (easy to do) of $20K.
The following DML command will get the credits:
INSERT INTO Transactions (ca_id, developer, transaction_type,
applied_date, amount)
SELECT ca_id, developer, 2 As trans_type, issued_date, amt
FROM AllAgreements
WHERE amt > 0
It's the DML command that puts the debits, w/ the proper ca_id, that's
the problem.
Ex. 1:
ca_id, "ID #", developer, amt
1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
The 2nd row (ca_id 2) needs to have an entry in Transactions of:
ca_id developer, trans_type, amount
--
1 Lucy 2 -20000.00
Since it is a deduction from agreement 'FIF2 GRNBR 001.'
Ex. 2:
3,'FIF2 GRNBR 001A','JUDY',20000.00
5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
4,'FIF2 GRNBR 001B','SUSIE',10000.00
The above row needs to have an entry in Transactions of:
ca_id developer, trans_type, amount
--
3 Judy 2 -10000.00
Since it is a deduction from agreement 'FIF2 GRNBR 001A.'
IOW, the credits ($) are deducted from the prior agreement:
001A deducts from 001
001B deducts from 001A
I believe I need to add a character "@." to the 001 so the query can
"see" the previous agreement as
Right([current ID #],1) > Right([other ID #],1)
This sorta works:
SELECT C.ca_id, c.[ID #], d.developer, d.transaction_type,
d.issued_date, d.amt
FROM AllAgreements As C,
(SELECT ca_id,[ID #], Developer, 2 AS Transaction_Type,
issued_date, amt
FROM AllAgreements
WHERE amt<0 ) As D
WHERE LEFT(C.[id #],LEN(c.[id #])-1)=LEFT(d.[id #],LEN(d.[id #])-1)
AND ASC(RIGHT(C.[ID #],1)) = ASC(RIGHT(D.[ID #],1))-1
ORDER BY 1, 2
Data from above query (removed the amt & date cols so could display on
one line):
ca_id ID # developer amt
1 FIF2 GRNBR 001@. LUCY ($20,000.00) <- correct
2 FIF2 GRNBR 001A JUDY ($10,000.00) <- incorrect
3 FIF2 GRNBR 001A JUDY ($10,000.00) <- correct
8 TIF1 MSSH 001@. FRED ($10,000.00) <- correct
9 TIF1 MSSH 001A HARRY ($10,000.00) <- incorrect
10 TIF1 MSSH 001A HARRY ($10,000.00) <- correct
Any refinements possible?
Rgds,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
moving parts of each row into appropriate tables and deleting redundant
rows. This table is just a scratch table used while importing old data
into a new DB.
The situation: A company supplies credits to its customers (other
companies [construction] called Developers) that the customers
(Developers) can use to reduce any bills sent to them by the company.
Each time the credits are supplied they are referred to as a Credit
Agreement. The developers can transfer those credits, or parts of those
credits, to other developers. When a transfer is made they are creating
a new agreement. The Agreement identifier, in correspondence, uses a
user constructed identifier (ID #). The company keeps the original
credit agreement identifier & just adds a sequential letter suffix
representing each iteration of a new agreement off the original (because
the original agreement has one cut-off date that is enforced for all
subsequent agreements that use credits from the original credit agreement).
E.g.:
1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
3,'FIF2 GRNBR 001A','JUDY',20000.00
5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
4,'FIF2 GRNBR 001B','SUSIE',10000.00
6,'TIF1 ACSPA 001','WILLY',30000.00 <- original agreement
7,'TIF1 ACSPA 002','WILLY',45000.00 <- original agreement
8,'TIF1 MSSH 001','FRED',25000.00 <- original agreement
9,'TIF1 MSSH 001A','FRED',-10000.00 <- Fred transferred $10K to Harry
10,'TIF1 MSSH 001A','HARRY',10000.00
11,'TIF1 MSSH 001B','HARRY',-100000.00 <- Harry transferred $10K to Joe
12,'TIF1 MSSH 001B','JOE',10000.00
I need to generate a new row in the table Transactions (below) for both
the debit on the original credit agreement and the transfer/credit for
the new credit agreement.
/* BEGIN DDL */
set nocount on
CREATE TABLE AllAgreements ( -- scratch table - no PK
ca_id INTEGER NOT NULL UNIQUE,
"ID #" VARCHAR(20) NOT NULL,
Developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
issued_date DATETIME NOT NULL,
amt decimal(9,2) NOT NULL
)
CREATE TABLE Transactions (
ca_id INTEGER NOT NULL REFERENCES AllAgreements,
developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
transaction_type INTEGER NOT NULL
CHECK (transaction_type BETWEEN 1 AND 5),
applied_date DATETIME NOT NULL,
amount DECIMAL(9,2) NOT NULL,
CONSTRAINT PK_Transactions
PRIMARY KEY (ca_id, developer, transaction_type, applied_date)
)
INSERT INTO AllAgreements
VALUES (1,'FIF2 GRNBR 001','LUCY','20050115',55000.00)
INSERT INTO AllAgreements
VALUES (2,'FIF2 GRNBR 001A','LUCY','20050122',-20000.00)
INSERT INTO AllAgreements
VALUES (3,'FIF2 GRNBR 001A','JUDY','20050122',20000.00)
INSERT INTO AllAgreements
VALUES (4,'FIF2 GRNBR 001B','SUSIE','20050122',10000.00)
INSERT INTO AllAgreements
VALUES (5,'FIF2 GRNBR 001B','JUDY','20050122',-10000.00)
INSERT INTO AllAgreements
VALUES (6,'TIF1 ACSPA 001','WILLY','20050211',30000.00)
INSERT INTO AllAgreements
VALUES (7,'TIF1 ACSPA 002','WILLY','20050211',45000.00)
INSERT INTO AllAgreements
VALUES (8,'TIF1 MSSH 001','FRED','20050212',25000.00)
INSERT INTO AllAgreements
VALUES (9,'TIF1 MSSH 001A','FRED','20050212',-10000.00)
INSERT INTO AllAgreements
VALUES (10,'TIF1 MSSH 001A','HARRY','20050212',10000.00)
INSERT INTO AllAgreements
VALUES (11,'TIF1 MSSH 01B','HARRY','20050225',-100000.00)
INSERT INTO AllAgreements
VALUES (12,'TIF1 MSSH 001B','JOE','20050225',10000.00)
/* desired results in table AllAgreements (I'll remove the Amt column
after the clean up & the values are in the Transactions table. Didn't
show the date in order to display row on one line).
ca_id ID # Developer amt
1 FIF2 GRNBR 001 LUCY $55,000.00
3 FIF2 GRNBR 001A JUDY $20,000.00 <- Lucy transferred to Judy
4 FIF2 GRNBR 001B SUSIE $10,000.00 <- Judy transferred to Susie
6 TIF1 ACSPA 001 WILLY $30,000.00
7 TIF1 ACSPA 002 WILLY $45,000.00
8 TIF1 MSSH 001 FRED $25,000.00
10 TIF1 MSSH 001A HARRY $10,000.00 <- Fred transferred to Harry
12 TIF1 MSSH 001B JOE $10,000.00 <- Harry transferred to Joe
*/
/* desired results in table Transactions:
(1 = Original credit agreement; 2 = transfer of CR agreement)
E.g.: below the 1st 2 LUCY lines show the original credit
(trans_type=1) and the debit (trans_type=2) on the ID # "FIF2 GRNBR 001"
caused by the transfer to JUDY (line 3). The JUDY line's ca_id, 3,
points to the new credit agreement "FIF2 GRNBR 001A" row in the
AllAgreements table.
ca_id developer transaction_type applied_date amount
-- -- -- -- --
1 LUCY 1 20050115 55000.00
1 LUCY 2 20050122 -20000.00
3 JUDY 2 20050122 20000.00
4 SUSIE 2 20050122 10000.00
3 JUDY 2 20050122 -10000.00
6 WILLY 1 20050211 30000.00
7 WILLY 1 20050211 45000.00
8 FRED 1 20050212 25000.00
8 FRED 2 20050212 -10000.00
10 HARRY 2 20050212 10000.00
10 HARRY 2 20050225 -100000.00
12 JOE 2 20050225 10000.00
*/
DROP TABLE AllAgreements, Transactions
/* END */
QUESTION: What append command will move the amounts to the Transactions
table w/ the correct ca_id value.
Thanks for your help.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)Hi
See inline:
"MGFoster" <mgf00@.earthlink.net> wrote in message
news:8waoe.562$HM.259@.newsread1.news.pas.earthlink.net...
> I'm trying to filter and update the table AllAgreements (below) by moving
> parts of each row into appropriate tables and deleting redundant rows.
> This table is just a scratch table used while importing old data into a
> new DB.
> The situation: A company supplies credits to its customers (other
> companies [construction] called Developers) that the customers
> (Developers) can use to reduce any bills sent to them by the company. Each
> time the credits are supplied they are referred to as a Credit Agreement.
> The developers can transfer those credits, or parts of those credits, to
> other developers. When a transfer is made they are creating a new
> agreement. The Agreement identifier, in correspondence, uses a user
> constructed identifier (ID #). The company keeps the original credit
> agreement identifier & just adds a sequential letter suffix representing
> each iteration of a new agreement off the original (because the original
> agreement has one cut-off date that is enforced for all subsequent
> agreements that use credits from the original credit agreement).
> E.g.:
> 1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
> 2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
> 3,'FIF2 GRNBR 001A','JUDY',20000.00
> 5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
> 4,'FIF2 GRNBR 001B','SUSIE',10000.00
> 6,'TIF1 ACSPA 001','WILLY',30000.00 <- original agreement
> 7,'TIF1 ACSPA 002','WILLY',45000.00 <- original agreement
> 8,'TIF1 MSSH 001','FRED',25000.00 <- original agreement
> 9,'TIF1 MSSH 001A','FRED',-10000.00 <- Fred transferred $10K to Harry
> 10,'TIF1 MSSH 001A','HARRY',10000.00
> 11,'TIF1 MSSH 001B','HARRY',-100000.00 <- Harry transferred $10K to Joe
> 12,'TIF1 MSSH 001B','JOE',10000.00
> I need to generate a new row in the table Transactions (below) for both
> the debit on the original credit agreement and the transfer/credit for the
> new credit agreement.
> /* BEGIN DDL */
> set nocount on
> CREATE TABLE AllAgreements ( -- scratch table - no PK
> ca_id INTEGER NOT NULL UNIQUE,
> "ID #" VARCHAR(20) NOT NULL,
> Developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
> issued_date DATETIME NOT NULL,
> amt decimal(9,2) NOT NULL
> )
> CREATE TABLE Transactions (
> ca_id INTEGER NOT NULL REFERENCES AllAgreements,
> developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
> transaction_type INTEGER NOT NULL
> CHECK (transaction_type BETWEEN 1 AND 5),
> applied_date DATETIME NOT NULL,
> amount DECIMAL(9,2) NOT NULL,
> CONSTRAINT PK_Transactions
> PRIMARY KEY (ca_id, developer, transaction_type, applied_date)
> )
> INSERT INTO AllAgreements
> VALUES (1,'FIF2 GRNBR 001','LUCY','20050115',55000.00)
> INSERT INTO AllAgreements
> VALUES (2,'FIF2 GRNBR 001A','LUCY','20050122',-20000.00)
> INSERT INTO AllAgreements
> VALUES (3,'FIF2 GRNBR 001A','JUDY','20050122',20000.00)
> INSERT INTO AllAgreements
> VALUES (4,'FIF2 GRNBR 001B','SUSIE','20050122',10000.00)
> INSERT INTO AllAgreements
> VALUES (5,'FIF2 GRNBR 001B','JUDY','20050122',-10000.00)
> INSERT INTO AllAgreements
> VALUES (6,'TIF1 ACSPA 001','WILLY','20050211',30000.00)
> INSERT INTO AllAgreements
> VALUES (7,'TIF1 ACSPA 002','WILLY','20050211',45000.00)
> INSERT INTO AllAgreements
> VALUES (8,'TIF1 MSSH 001','FRED','20050212',25000.00)
> INSERT INTO AllAgreements
> VALUES (9,'TIF1 MSSH 001A','FRED','20050212',-10000.00)
> INSERT INTO AllAgreements
> VALUES (10,'TIF1 MSSH 001A','HARRY','20050212',10000.00)
> INSERT INTO AllAgreements
> VALUES (11,'TIF1 MSSH 01B','HARRY','20050225',-100000.00)
> INSERT INTO AllAgreements
> VALUES (12,'TIF1 MSSH 001B','JOE','20050225',10000.00)
> /* desired results in table AllAgreements (I'll remove the Amt column
> after the clean up & the values are in the Transactions table. Didn't
> show the date in order to display row on one line).
> ca_id ID # Developer amt
> 1 FIF2 GRNBR 001 LUCY $55,000.00
> 3 FIF2 GRNBR 001A JUDY $20,000.00 <- Lucy transferred to Judy
> 4 FIF2 GRNBR 001B SUSIE $10,000.00 <- Judy transferred to Susie
> 6 TIF1 ACSPA 001 WILLY $30,000.00
> 7 TIF1 ACSPA 002 WILLY $45,000.00
> 8 TIF1 MSSH 001 FRED $25,000.00
> 10 TIF1 MSSH 001A HARRY $10,000.00 <- Fred transferred to Harry
> 12 TIF1 MSSH 001B JOE $10,000.00 <- Harry transferred to Joe
> */
This seems to be
SELECT A.ca_id,A.[ID #],A.Developer,
CONVERT(char(10),A.issued_date,112) As Applied_date,
A.Amt FROM AllAgreements A
WHERE A.Amt > 0
order by A.ca_id
> /* desired results in table Transactions:
> (1 = Original credit agreement; 2 = transfer of CR agreement)
> E.g.: below the 1st 2 LUCY lines show the original credit (trans_type=1)
> and the debit (trans_type=2) on the ID # "FIF2 GRNBR 001" caused by the
> transfer to JUDY (line 3). The JUDY line's ca_id, 3, points to the new
> credit agreement "FIF2 GRNBR 001A" row in the AllAgreements table.
> ca_id developer transaction_type applied_date amount
> -- -- -- -- --
> 1 LUCY 1 20050115 55000.00
> 1 LUCY 2 20050122 -20000.00
> 3 JUDY 2 20050122 20000.00
> 4 SUSIE 2 20050122 10000.00
> 3 JUDY 2 20050122 -10000.00
> 6 WILLY 1 20050211 30000.00
> 7 WILLY 1 20050211 45000.00
> 8 FRED 1 20050212 25000.00
> 8 FRED 2 20050212 -10000.00
> 10 HARRY 2 20050212 10000.00
> 10 HARRY 2 20050225 -100000.00
> 12 JOE 2 20050225 10000.00
> */
>
I can't see the login in the ca_id column as they do not relate to the
transaction concerned! But this is almost what you wanted:
SELECT A.ca_id,--A.[ID #],
B.Developer,
2 Transaction_Type,
CONVERT(char(10),A.issued_date,112) As Applied_date,
B.AMT AS AMT
FROM AllAgreements A
JOIN AllAgreements B ON A.ca_id <> B.ca_id
AND A.[ID #] = B.[ID #]
AND A.Developer <> B.Developer
AND A.issued_date = B.issued_date
AND A.AMT = -1 * B.AMT
WHERE A.AMT > 0
UNION ALL
SELECT A.ca_id,--A.[ID #],
A.Developer AS Developer,
CASE WHEN B.ca_id IS NULL THEN 1 ELSE 2 END AS Transaction_Type,
CONVERT(char(10),A.issued_date,112) As Applied_date,
A.AMT AS AMT
FROM AllAgreements A
LEFT JOIN AllAgreements B ON A.ca_id <> B.ca_id
AND A.[ID #] = B.[ID #]
AND A.Developer <> B.Developer
AND A.issued_date = B.issued_date
AND A.AMT = -1 * B.AMT
WHERE A.AMT > 0
ORDER BY A.CA_ID, AMT
> DROP TABLE AllAgreements, Transactions
> /* END */
>
> QUESTION: What append command will move the amounts to the Transactions
> table w/ the correct ca_id value.
>
You will have to decide what the criteria is for the ca_id.
John
> Thanks for your help.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)|||John Bell wrote:
> I can't see the login [do you mean logic?] in the ca_id column as they do
not relate to the
> transaction concerned! But this is almost what you wanted:
> SELECT A.ca_id,--A.[ID #],
> B.Developer,
> 2 Transaction_Type,
> CONVERT(char(10),A.issued_date,112) As Applied_date,
> B.AMT AS AMT
> FROM AllAgreements A
> JOIN AllAgreements B ON A.ca_id <> B.ca_id
> AND A.[ID #] = B.[ID #]
> AND A.Developer <> B.Developer
> AND A.issued_date = B.issued_date
> AND A.AMT = -1 * B.AMT
> WHERE A.AMT > 0
> UNION ALL
> SELECT A.ca_id,--A.[ID #],
> A.Developer AS Developer,
> CASE WHEN B.ca_id IS NULL THEN 1 ELSE 2 END AS Transaction_Type,
> CONVERT(char(10),A.issued_date,112) As Applied_date,
> A.AMT AS AMT
> FROM AllAgreements A
> LEFT JOIN AllAgreements B ON A.ca_id <> B.ca_id
> AND A.[ID #] = B.[ID #]
> AND A.Developer <> B.Developer
> AND A.issued_date = B.issued_date
> AND A.AMT = -1 * B.AMT
> WHERE A.AMT > 0
> ORDER BY A.CA_ID, AMT
>
> You will have to decide what the criteria is for the ca_id.
>
Ah, but that's just what I need the query to do - figure out which ca_id
goes w/ each transfer's credit transaction.
Think of it this way: Lucy transfers $20K from agreement FIF2 GRNBR 001
(ca_id 1) to Judy, which makes a new agreement FIF2 GRNBR 001A (ca_id 2)
for Judy. I need to show in the Transactions table a debit of $20K from
ca_id 1 and a credit to ca_id 2 (easy to do) of $20K.
The following DML command will get the credits:
INSERT INTO Transactions (ca_id, developer, transaction_type,
applied_date, amount)
SELECT ca_id, developer, 2 As trans_type, issued_date, amt
FROM AllAgreements
WHERE amt > 0
It's the DML command that puts the debits, w/ the proper ca_id, that's
the problem.
Ex. 1:
ca_id, "ID #", developer, amt
1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
The 2nd row (ca_id 2) needs to have an entry in Transactions of:
ca_id developer, trans_type, amount
--
1 Lucy 2 -20000.00
Since it is a deduction from agreement 'FIF2 GRNBR 001.'
Ex. 2:
3,'FIF2 GRNBR 001A','JUDY',20000.00
5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
4,'FIF2 GRNBR 001B','SUSIE',10000.00
The above row needs to have an entry in Transactions of:
ca_id developer, trans_type, amount
--
3 Judy 2 -10000.00
Since it is a deduction from agreement 'FIF2 GRNBR 001A.'
IOW, the credits ($) are deducted from the prior agreement:
001A deducts from 001
001B deducts from 001A
I believe I need to add a character "@." to the 001 so the query can
"see" the previous agreement as
Right([current ID #],1) > Right([other ID #],1)
This sorta works:
SELECT C.ca_id, c.[ID #], d.developer, d.transaction_type,
d.issued_date, d.amt
FROM AllAgreements As C,
(SELECT ca_id,[ID #], Developer, 2 AS Transaction_Type,
issued_date, amt
FROM AllAgreements
WHERE amt<0 ) As D
WHERE LEFT(C.[id #],LEN(c.[id #])-1)=LEFT(d.[id #],LEN(d.[id #])-1)
AND ASC(RIGHT(C.[ID #],1)) = ASC(RIGHT(D.[ID #],1))-1
ORDER BY 1, 2
Data from above query (removed the amt & date cols so could display on
one line):
ca_id ID # developer amt
1 FIF2 GRNBR 001@. LUCY ($20,000.00) <- correct
2 FIF2 GRNBR 001A JUDY ($10,000.00) <- incorrect
3 FIF2 GRNBR 001A JUDY ($10,000.00) <- correct
8 TIF1 MSSH 001@. FRED ($10,000.00) <- correct
9 TIF1 MSSH 001A HARRY ($10,000.00) <- incorrect
10 TIF1 MSSH 001A HARRY ($10,000.00) <- correct
Any refinements possible?
Rgds,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
clean server cache
Hi,
I am testing .net app. I restored sql server databases with overwrite the
original database.
I re-launch the .net app, I got this error says. Could not connect to the
database DBID = 15...
After I reboot the sql server, I was able to connect again,.
My question is that how do I fix (clean the cache) without rebooting the
server.
thnaks
Hi
I am not sure how this could happen, if you restored the database nothing
should have been connected, and subsequent connections should have connected
ok, providing permissions were in place. Does your .NET application have
connections to other databases? Did you check nothing was connected while you
did the restore?
John
"mecn" wrote:
> Hi,
> I am testing .net app. I restored sql server databases with overwrite the
> original database.
> I re-launch the .net app, I got this error says. Could not connect to the
> database DBID = 15...
> After I reboot the sql server, I was able to connect again,.
> My question is that how do I fix (clean the cache) without rebooting the
> server.
> thnaks
>
>
|||It's very rare, first time for us.
..net application tries to connect dbid = 15 which is the previous db that I
restored with overwrite.
I don't know why the .net application still looking for the old db. not the
db name taht specified in connection string.
After re-boot server(Sql server and iis server in one), everything is ok.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:73E30669-7FFD-41BB-BE63-C565B0B083E4@.microsoft.com...[vbcol=seagreen]
> Hi
> I am not sure how this could happen, if you restored the database nothing
> should have been connected, and subsequent connections should have
> connected
> ok, providing permissions were in place. Does your .NET application have
> connections to other databases? Did you check nothing was connected while
> you
> did the restore?
> John
> "mecn" wrote:
|||Sorry forgot to mention it. Yes The application is connecting to
multi-databaseses.
Is this the problems?
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:73E30669-7FFD-41BB-BE63-C565B0B083E4@.microsoft.com...[vbcol=seagreen]
> Hi
> I am not sure how this could happen, if you restored the database nothing
> should have been connected, and subsequent connections should have
> connected
> ok, providing permissions were in place. Does your .NET application have
> connections to other databases? Did you check nothing was connected while
> you
> did the restore?
> John
> "mecn" wrote:
|||Hi
I think it could possibly be the cause of the problem, do they remain
connected to the other database while you are restoring? If so try setting
these other databases to single user (and killing the connections) while the
restore is in progress.
John
"mecn" wrote:
> Sorry forgot to mention it. Yes The application is connecting to
> multi-databaseses.
> Is this the problems?
> Thanks
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:73E30669-7FFD-41BB-BE63-C565B0B083E4@.microsoft.com...
>
>
I am testing .net app. I restored sql server databases with overwrite the
original database.
I re-launch the .net app, I got this error says. Could not connect to the
database DBID = 15...
After I reboot the sql server, I was able to connect again,.
My question is that how do I fix (clean the cache) without rebooting the
server.
thnaks
Hi
I am not sure how this could happen, if you restored the database nothing
should have been connected, and subsequent connections should have connected
ok, providing permissions were in place. Does your .NET application have
connections to other databases? Did you check nothing was connected while you
did the restore?
John
"mecn" wrote:
> Hi,
> I am testing .net app. I restored sql server databases with overwrite the
> original database.
> I re-launch the .net app, I got this error says. Could not connect to the
> database DBID = 15...
> After I reboot the sql server, I was able to connect again,.
> My question is that how do I fix (clean the cache) without rebooting the
> server.
> thnaks
>
>
|||It's very rare, first time for us.
..net application tries to connect dbid = 15 which is the previous db that I
restored with overwrite.
I don't know why the .net application still looking for the old db. not the
db name taht specified in connection string.
After re-boot server(Sql server and iis server in one), everything is ok.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:73E30669-7FFD-41BB-BE63-C565B0B083E4@.microsoft.com...[vbcol=seagreen]
> Hi
> I am not sure how this could happen, if you restored the database nothing
> should have been connected, and subsequent connections should have
> connected
> ok, providing permissions were in place. Does your .NET application have
> connections to other databases? Did you check nothing was connected while
> you
> did the restore?
> John
> "mecn" wrote:
|||Sorry forgot to mention it. Yes The application is connecting to
multi-databaseses.
Is this the problems?
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:73E30669-7FFD-41BB-BE63-C565B0B083E4@.microsoft.com...[vbcol=seagreen]
> Hi
> I am not sure how this could happen, if you restored the database nothing
> should have been connected, and subsequent connections should have
> connected
> ok, providing permissions were in place. Does your .NET application have
> connections to other databases? Did you check nothing was connected while
> you
> did the restore?
> John
> "mecn" wrote:
|||Hi
I think it could possibly be the cause of the problem, do they remain
connected to the other database while you are restoring? If so try setting
these other databases to single user (and killing the connections) while the
restore is in progress.
John
"mecn" wrote:
> Sorry forgot to mention it. Yes The application is connecting to
> multi-databaseses.
> Is this the problems?
> Thanks
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:73E30669-7FFD-41BB-BE63-C565B0B083E4@.microsoft.com...
>
>
clean server cache
Hi,
I am testing .net app. I restored sql server databases with overwrite the
original database.
I re-launch the .net app, I got this error says. Could not connect to the
database DBID = 15...
After I reboot the sql server, I was able to connect again,.
My question is that how do I fix (clean the cache) without rebooting the
server.
thnaksHi
I am not sure how this could happen, if you restored the database nothing
should have been connected, and subsequent connections should have connected
ok, providing permissions were in place. Does your .NET application have
connections to other databases? Did you check nothing was connected while yo
u
did the restore?
John
"mecn" wrote:
> Hi,
> I am testing .net app. I restored sql server databases with overwrite the
> original database.
> I re-launch the .net app, I got this error says. Could not connect to the
> database DBID = 15...
> After I reboot the sql server, I was able to connect again,.
> My question is that how do I fix (clean the cache) without rebooting the
> server.
> thnaks
>
>|||It's very rare, first time for us.
.net application tries to connect dbid = 15 which is the previous db that I
restored with overwrite.
I don't know why the .net application still looking for the old db. not the
db name taht specified in connection string.
After re-boot server(Sql server and iis server in one), everything is ok.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:73E30669-7FFD-41BB-BE63-C565B0B083E4@.microsoft.com...[vbcol=seagreen]
> Hi
> I am not sure how this could happen, if you restored the database nothing
> should have been connected, and subsequent connections should have
> connected
> ok, providing permissions were in place. Does your .NET application have
> connections to other databases? Did you check nothing was connected while
> you
> did the restore?
> John
> "mecn" wrote:
>|||Sorry forgot to mention it. Yes The application is connecting to
multi-databaseses.
Is this the problems?
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:73E30669-7FFD-41BB-BE63-C565B0B083E4@.microsoft.com...[vbcol=seagreen]
> Hi
> I am not sure how this could happen, if you restored the database nothing
> should have been connected, and subsequent connections should have
> connected
> ok, providing permissions were in place. Does your .NET application have
> connections to other databases? Did you check nothing was connected while
> you
> did the restore?
> John
> "mecn" wrote:
>|||Hi
I think it could possibly be the cause of the problem, do they remain
connected to the other database while you are restoring? If so try setting
these other databases to single user (and killing the connections) while the
restore is in progress.
John
"mecn" wrote:
> Sorry forgot to mention it. Yes The application is connecting to
> multi-databaseses.
> Is this the problems?
> Thanks
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:73E30669-7FFD-41BB-BE63-C565B0B083E4@.microsoft.com...
>
>
I am testing .net app. I restored sql server databases with overwrite the
original database.
I re-launch the .net app, I got this error says. Could not connect to the
database DBID = 15...
After I reboot the sql server, I was able to connect again,.
My question is that how do I fix (clean the cache) without rebooting the
server.
thnaksHi
I am not sure how this could happen, if you restored the database nothing
should have been connected, and subsequent connections should have connected
ok, providing permissions were in place. Does your .NET application have
connections to other databases? Did you check nothing was connected while yo
u
did the restore?
John
"mecn" wrote:
> Hi,
> I am testing .net app. I restored sql server databases with overwrite the
> original database.
> I re-launch the .net app, I got this error says. Could not connect to the
> database DBID = 15...
> After I reboot the sql server, I was able to connect again,.
> My question is that how do I fix (clean the cache) without rebooting the
> server.
> thnaks
>
>|||It's very rare, first time for us.
.net application tries to connect dbid = 15 which is the previous db that I
restored with overwrite.
I don't know why the .net application still looking for the old db. not the
db name taht specified in connection string.
After re-boot server(Sql server and iis server in one), everything is ok.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:73E30669-7FFD-41BB-BE63-C565B0B083E4@.microsoft.com...[vbcol=seagreen]
> Hi
> I am not sure how this could happen, if you restored the database nothing
> should have been connected, and subsequent connections should have
> connected
> ok, providing permissions were in place. Does your .NET application have
> connections to other databases? Did you check nothing was connected while
> you
> did the restore?
> John
> "mecn" wrote:
>|||Sorry forgot to mention it. Yes The application is connecting to
multi-databaseses.
Is this the problems?
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:73E30669-7FFD-41BB-BE63-C565B0B083E4@.microsoft.com...[vbcol=seagreen]
> Hi
> I am not sure how this could happen, if you restored the database nothing
> should have been connected, and subsequent connections should have
> connected
> ok, providing permissions were in place. Does your .NET application have
> connections to other databases? Did you check nothing was connected while
> you
> did the restore?
> John
> "mecn" wrote:
>|||Hi
I think it could possibly be the cause of the problem, do they remain
connected to the other database while you are restoring? If so try setting
these other databases to single user (and killing the connections) while the
restore is in progress.
John
"mecn" wrote:
> Sorry forgot to mention it. Yes The application is connecting to
> multi-databaseses.
> Is this the problems?
> Thanks
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:73E30669-7FFD-41BB-BE63-C565B0B083E4@.microsoft.com...
>
>
clean server cache
Hi,
I am testing .net app. I restored sql server databases with overwrite the
original database.
I re-launch the .net app, I got this error says. Could not connect to the
database DBID = 15...
After I reboot the sql server, I was able to connect again,.
My question is that how do I fix (clean the cache) without rebooting the
server.
thnaksHi
I am not sure how this could happen, if you restored the database nothing
should have been connected, and subsequent connections should have connected
ok, providing permissions were in place. Does your .NET application have
connections to other databases? Did you check nothing was connected while you
did the restore?
John
"mecn" wrote:
> Hi,
> I am testing .net app. I restored sql server databases with overwrite the
> original database.
> I re-launch the .net app, I got this error says. Could not connect to the
> database DBID = 15...
> After I reboot the sql server, I was able to connect again,.
> My question is that how do I fix (clean the cache) without rebooting the
> server.
> thnaks
>
>|||It's very rare, first time for us.
.net application tries to connect dbid = 15 which is the previous db that I
restored with overwrite.
I don't know why the .net application still looking for the old db. not the
db name taht specified in connection string.
After re-boot server(Sql server and iis server in one), everything is ok.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:73E30669-7FFD-41BB-BE63-C565B0B083E4@.microsoft.com...
> Hi
> I am not sure how this could happen, if you restored the database nothing
> should have been connected, and subsequent connections should have
> connected
> ok, providing permissions were in place. Does your .NET application have
> connections to other databases? Did you check nothing was connected while
> you
> did the restore?
> John
> "mecn" wrote:
>> Hi,
>> I am testing .net app. I restored sql server databases with overwrite the
>> original database.
>> I re-launch the .net app, I got this error says. Could not connect to the
>> database DBID = 15...
>> After I reboot the sql server, I was able to connect again,.
>> My question is that how do I fix (clean the cache) without rebooting the
>> server.
>> thnaks
>>|||Sorry forgot to mention it. Yes The application is connecting to
multi-databaseses.
Is this the problems?
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:73E30669-7FFD-41BB-BE63-C565B0B083E4@.microsoft.com...
> Hi
> I am not sure how this could happen, if you restored the database nothing
> should have been connected, and subsequent connections should have
> connected
> ok, providing permissions were in place. Does your .NET application have
> connections to other databases? Did you check nothing was connected while
> you
> did the restore?
> John
> "mecn" wrote:
>> Hi,
>> I am testing .net app. I restored sql server databases with overwrite the
>> original database.
>> I re-launch the .net app, I got this error says. Could not connect to the
>> database DBID = 15...
>> After I reboot the sql server, I was able to connect again,.
>> My question is that how do I fix (clean the cache) without rebooting the
>> server.
>> thnaks
>>|||Hi
I think it could possibly be the cause of the problem, do they remain
connected to the other database while you are restoring? If so try setting
these other databases to single user (and killing the connections) while the
restore is in progress.
John
"mecn" wrote:
> Sorry forgot to mention it. Yes The application is connecting to
> multi-databaseses.
> Is this the problems?
> Thanks
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:73E30669-7FFD-41BB-BE63-C565B0B083E4@.microsoft.com...
> > Hi
> >
> > I am not sure how this could happen, if you restored the database nothing
> > should have been connected, and subsequent connections should have
> > connected
> > ok, providing permissions were in place. Does your .NET application have
> > connections to other databases? Did you check nothing was connected while
> > you
> > did the restore?
> >
> > John
> >
> > "mecn" wrote:
> >
> >> Hi,
> >> I am testing .net app. I restored sql server databases with overwrite the
> >> original database.
> >> I re-launch the .net app, I got this error says. Could not connect to the
> >> database DBID = 15...
> >>
> >> After I reboot the sql server, I was able to connect again,.
> >>
> >> My question is that how do I fix (clean the cache) without rebooting the
> >> server.
> >>
> >> thnaks
> >>
> >>
> >>
>
>
I am testing .net app. I restored sql server databases with overwrite the
original database.
I re-launch the .net app, I got this error says. Could not connect to the
database DBID = 15...
After I reboot the sql server, I was able to connect again,.
My question is that how do I fix (clean the cache) without rebooting the
server.
thnaksHi
I am not sure how this could happen, if you restored the database nothing
should have been connected, and subsequent connections should have connected
ok, providing permissions were in place. Does your .NET application have
connections to other databases? Did you check nothing was connected while you
did the restore?
John
"mecn" wrote:
> Hi,
> I am testing .net app. I restored sql server databases with overwrite the
> original database.
> I re-launch the .net app, I got this error says. Could not connect to the
> database DBID = 15...
> After I reboot the sql server, I was able to connect again,.
> My question is that how do I fix (clean the cache) without rebooting the
> server.
> thnaks
>
>|||It's very rare, first time for us.
.net application tries to connect dbid = 15 which is the previous db that I
restored with overwrite.
I don't know why the .net application still looking for the old db. not the
db name taht specified in connection string.
After re-boot server(Sql server and iis server in one), everything is ok.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:73E30669-7FFD-41BB-BE63-C565B0B083E4@.microsoft.com...
> Hi
> I am not sure how this could happen, if you restored the database nothing
> should have been connected, and subsequent connections should have
> connected
> ok, providing permissions were in place. Does your .NET application have
> connections to other databases? Did you check nothing was connected while
> you
> did the restore?
> John
> "mecn" wrote:
>> Hi,
>> I am testing .net app. I restored sql server databases with overwrite the
>> original database.
>> I re-launch the .net app, I got this error says. Could not connect to the
>> database DBID = 15...
>> After I reboot the sql server, I was able to connect again,.
>> My question is that how do I fix (clean the cache) without rebooting the
>> server.
>> thnaks
>>|||Sorry forgot to mention it. Yes The application is connecting to
multi-databaseses.
Is this the problems?
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:73E30669-7FFD-41BB-BE63-C565B0B083E4@.microsoft.com...
> Hi
> I am not sure how this could happen, if you restored the database nothing
> should have been connected, and subsequent connections should have
> connected
> ok, providing permissions were in place. Does your .NET application have
> connections to other databases? Did you check nothing was connected while
> you
> did the restore?
> John
> "mecn" wrote:
>> Hi,
>> I am testing .net app. I restored sql server databases with overwrite the
>> original database.
>> I re-launch the .net app, I got this error says. Could not connect to the
>> database DBID = 15...
>> After I reboot the sql server, I was able to connect again,.
>> My question is that how do I fix (clean the cache) without rebooting the
>> server.
>> thnaks
>>|||Hi
I think it could possibly be the cause of the problem, do they remain
connected to the other database while you are restoring? If so try setting
these other databases to single user (and killing the connections) while the
restore is in progress.
John
"mecn" wrote:
> Sorry forgot to mention it. Yes The application is connecting to
> multi-databaseses.
> Is this the problems?
> Thanks
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:73E30669-7FFD-41BB-BE63-C565B0B083E4@.microsoft.com...
> > Hi
> >
> > I am not sure how this could happen, if you restored the database nothing
> > should have been connected, and subsequent connections should have
> > connected
> > ok, providing permissions were in place. Does your .NET application have
> > connections to other databases? Did you check nothing was connected while
> > you
> > did the restore?
> >
> > John
> >
> > "mecn" wrote:
> >
> >> Hi,
> >> I am testing .net app. I restored sql server databases with overwrite the
> >> original database.
> >> I re-launch the .net app, I got this error says. Could not connect to the
> >> database DBID = 15...
> >>
> >> After I reboot the sql server, I was able to connect again,.
> >>
> >> My question is that how do I fix (clean the cache) without rebooting the
> >> server.
> >>
> >> thnaks
> >>
> >>
> >>
>
>
Clean out (Reinitialize) SQL Server Database?
It there an easy way to 'clean out' a SQL Server database? I need to get a
database of an unknown state back to a known state of 'empty'. Is there an
easy way to drop ALL structures; TABLES, VIEWS, SPROCS, FUNCTIONS, INDEXES,
TRIGGERS, CONSTRAINTS, etc. (Did I miss anything?)
(I don't want to rely on Dropping the Database and recreating it entirely as
a new database, since this requires a priviledge escalation as compared to
the priviledges required for working on structures inside of the database.)
Thanks for your help!
- Joseph Geretz -
You could easily write a proc with a little cursor to do this using
2005:
select name, type
from sys.objects
2000
select name, xtype
from sysobjects
Then just use the type of proc to determine what drop statement to use. I
think this will do it, even considering constraints (sometimes you have to
drop the constraint before the columns,) I think if you limit this to:
sys.objects.type in ('P', 'FN', 'U', 'IF', 'TF', 'V') --pretty much the same
in 2000
--procedure, scalar function, user table, inline function, table value
function
You can do it. You would just have to run the cursor multiple times to
eliminate dependencies, or you could add foreign keys if you want to using
the information_schema,table_constraints for that.
I might also suggest you could just write a sql agent job that could be
executed to clean out the database pretty easily by killing any users,
dropping the database and recreating it, if you really want to just start
over.
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:ezib0ueHGHA.2040@.TK2MSFTNGP14.phx.gbl...
> It there an easy way to 'clean out' a SQL Server database? I need to get a
> database of an unknown state back to a known state of 'empty'. Is there an
> easy way to drop ALL structures; TABLES, VIEWS, SPROCS, FUNCTIONS,
> INDEXES, TRIGGERS, CONSTRAINTS, etc. (Did I miss anything?)
> (I don't want to rely on Dropping the Database and recreating it entirely
> as a new database, since this requires a priviledge escalation as compared
> to the priviledges required for working on structures inside of the
> database.)
> Thanks for your help!
> - Joseph Geretz -
>
|||Here's a link to a script to drop all user objects using the basic technique
Louis suggested. You can tweak for SQL 2005, although the need to do this
might be mitigated if you can do DROP/CREATE DATABASE instead.
http://tinyurl.com/9t9m6
Hope this helps.
Dan Guzman
SQL Server MVP
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:ezib0ueHGHA.2040@.TK2MSFTNGP14.phx.gbl...
> It there an easy way to 'clean out' a SQL Server database? I need to get a
> database of an unknown state back to a known state of 'empty'. Is there an
> easy way to drop ALL structures; TABLES, VIEWS, SPROCS, FUNCTIONS,
> INDEXES, TRIGGERS, CONSTRAINTS, etc. (Did I miss anything?)
> (I don't want to rely on Dropping the Database and recreating it entirely
> as a new database, since this requires a priviledge escalation as compared
> to the priviledges required for working on structures inside of the
> database.)
> Thanks for your help!
> - Joseph Geretz -
>
|||Joseph
you can back up an empty database, then restore the backup against the
database you wish to clean up
HIH
|||Thanks Dan!
Your script works perfectly for us.
- Joe Geretz -
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uhxmpBrHGHA.1088@.tk2msftngp13.phx.gbl...
> Here's a link to a script to drop all user objects using the basic
> technique Louis suggested. You can tweak for SQL 2005, although the need
> to do this might be mitigated if you can do DROP/CREATE DATABASE instead.
> http://tinyurl.com/9t9m6
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:ezib0ueHGHA.2040@.TK2MSFTNGP14.phx.gbl...
>
|||Nice
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uhxmpBrHGHA.1088@.tk2msftngp13.phx.gbl...
> Here's a link to a script to drop all user objects using the basic
> technique Louis suggested. You can tweak for SQL 2005, although the need
> to do this might be mitigated if you can do DROP/CREATE DATABASE instead.
> http://tinyurl.com/9t9m6
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:ezib0ueHGHA.2040@.TK2MSFTNGP14.phx.gbl...
>
sqlsql
database of an unknown state back to a known state of 'empty'. Is there an
easy way to drop ALL structures; TABLES, VIEWS, SPROCS, FUNCTIONS, INDEXES,
TRIGGERS, CONSTRAINTS, etc. (Did I miss anything?)
(I don't want to rely on Dropping the Database and recreating it entirely as
a new database, since this requires a priviledge escalation as compared to
the priviledges required for working on structures inside of the database.)
Thanks for your help!
- Joseph Geretz -
You could easily write a proc with a little cursor to do this using
2005:
select name, type
from sys.objects
2000
select name, xtype
from sysobjects
Then just use the type of proc to determine what drop statement to use. I
think this will do it, even considering constraints (sometimes you have to
drop the constraint before the columns,) I think if you limit this to:
sys.objects.type in ('P', 'FN', 'U', 'IF', 'TF', 'V') --pretty much the same
in 2000
--procedure, scalar function, user table, inline function, table value
function
You can do it. You would just have to run the cursor multiple times to
eliminate dependencies, or you could add foreign keys if you want to using
the information_schema,table_constraints for that.
I might also suggest you could just write a sql agent job that could be
executed to clean out the database pretty easily by killing any users,
dropping the database and recreating it, if you really want to just start
over.
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:ezib0ueHGHA.2040@.TK2MSFTNGP14.phx.gbl...
> It there an easy way to 'clean out' a SQL Server database? I need to get a
> database of an unknown state back to a known state of 'empty'. Is there an
> easy way to drop ALL structures; TABLES, VIEWS, SPROCS, FUNCTIONS,
> INDEXES, TRIGGERS, CONSTRAINTS, etc. (Did I miss anything?)
> (I don't want to rely on Dropping the Database and recreating it entirely
> as a new database, since this requires a priviledge escalation as compared
> to the priviledges required for working on structures inside of the
> database.)
> Thanks for your help!
> - Joseph Geretz -
>
|||Here's a link to a script to drop all user objects using the basic technique
Louis suggested. You can tweak for SQL 2005, although the need to do this
might be mitigated if you can do DROP/CREATE DATABASE instead.
http://tinyurl.com/9t9m6
Hope this helps.
Dan Guzman
SQL Server MVP
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:ezib0ueHGHA.2040@.TK2MSFTNGP14.phx.gbl...
> It there an easy way to 'clean out' a SQL Server database? I need to get a
> database of an unknown state back to a known state of 'empty'. Is there an
> easy way to drop ALL structures; TABLES, VIEWS, SPROCS, FUNCTIONS,
> INDEXES, TRIGGERS, CONSTRAINTS, etc. (Did I miss anything?)
> (I don't want to rely on Dropping the Database and recreating it entirely
> as a new database, since this requires a priviledge escalation as compared
> to the priviledges required for working on structures inside of the
> database.)
> Thanks for your help!
> - Joseph Geretz -
>
|||Joseph
you can back up an empty database, then restore the backup against the
database you wish to clean up
HIH
|||Thanks Dan!
Your script works perfectly for us.
- Joe Geretz -
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uhxmpBrHGHA.1088@.tk2msftngp13.phx.gbl...
> Here's a link to a script to drop all user objects using the basic
> technique Louis suggested. You can tweak for SQL 2005, although the need
> to do this might be mitigated if you can do DROP/CREATE DATABASE instead.
> http://tinyurl.com/9t9m6
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:ezib0ueHGHA.2040@.TK2MSFTNGP14.phx.gbl...
>
|||Nice
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uhxmpBrHGHA.1088@.tk2msftngp13.phx.gbl...
> Here's a link to a script to drop all user objects using the basic
> technique Louis suggested. You can tweak for SQL 2005, although the need
> to do this might be mitigated if you can do DROP/CREATE DATABASE instead.
> http://tinyurl.com/9t9m6
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:ezib0ueHGHA.2040@.TK2MSFTNGP14.phx.gbl...
>
sqlsql
Clean out (Reinitialize) SQL Server Database?
It there an easy way to 'clean out' a SQL Server database? I need to get a
database of an unknown state back to a known state of 'empty'. Is there an
easy way to drop ALL structures; TABLES, VIEWS, SPROCS, FUNCTIONS, INDEXES,
TRIGGERS, CONSTRAINTS, etc. (Did I miss anything?)
(I don't want to rely on Dropping the Database and recreating it entirely as
a new database, since this requires a priviledge escalation as compared to
the priviledges required for working on structures inside of the database.)
Thanks for your help!
- Joseph Geretz -What are you actually trying to accomplish here? If you drop all of the
database objects, what are you trying to preserve for future use?
========================================
==========
People who want to share their religious views with you almost never
want you to share yours with them. -Dave Barry, author and columnist
(1947- )
*** Sent via Developersdex http://www.examnotes.net ***|||You could easily write a proc with a little cursor to do this using
2005:
select name, type
from sys.objects
2000
select name, xtype
from sysobjects
Then just use the type of proc to determine what drop statement to use. I
think this will do it, even considering constraints (sometimes you have to
drop the constraint before the columns,) I think if you limit this to:
sys.objects.type in ('P', 'FN', 'U', 'IF', 'TF', 'V') --pretty much the same
in 2000
--procedure, scalar function, user table, inline function, table value
function
You can do it. You would just have to run the cursor multiple times to
eliminate dependencies, or you could add foreign keys if you want to using
the information_schema,table_constraints for that.
I might also suggest you could just write a sql agent job that could be
executed to clean out the database pretty easily by killing any users,
dropping the database and recreating it, if you really want to just start
over.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:ezib0ueHGHA.2040@.TK2MSFTNGP14.phx.gbl...
> It there an easy way to 'clean out' a SQL Server database? I need to get a
> database of an unknown state back to a known state of 'empty'. Is there an
> easy way to drop ALL structures; TABLES, VIEWS, SPROCS, FUNCTIONS,
> INDEXES, TRIGGERS, CONSTRAINTS, etc. (Did I miss anything?)
> (I don't want to rely on Dropping the Database and recreating it entirely
> as a new database, since this requires a priviledge escalation as compared
> to the priviledges required for working on structures inside of the
> database.)
> Thanks for your help!
> - Joseph Geretz -
>|||Here's a link to a script to drop all user objects using the basic technique
Louis suggested. You can tweak for SQL 2005, although the need to do this
might be mitigated if you can do DROP/CREATE DATABASE instead.
http://tinyurl.com/9t9m6
Hope this helps.
Dan Guzman
SQL Server MVP
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:ezib0ueHGHA.2040@.TK2MSFTNGP14.phx.gbl...
> It there an easy way to 'clean out' a SQL Server database? I need to get a
> database of an unknown state back to a known state of 'empty'. Is there an
> easy way to drop ALL structures; TABLES, VIEWS, SPROCS, FUNCTIONS,
> INDEXES, TRIGGERS, CONSTRAINTS, etc. (Did I miss anything?)
> (I don't want to rely on Dropping the Database and recreating it entirely
> as a new database, since this requires a priviledge escalation as compared
> to the priviledges required for working on structures inside of the
> database.)
> Thanks for your help!
> - Joseph Geretz -
>|||Joseph
you can back up an empty database, then restore the backup against the
database you wish to clean up
HIH|||> What are you actually trying to accomplish here? If you drop all of the
> database objects, what are you trying to preserve for future use?
I can't speak for the OP but I've done this in SQL 2000 as an alternative to
DROP/CREATE DATABASE. This is significantly faster when the database is
large. However, in SQL 2005, extent formatting is deferred so that reason
doesn't apply. In SQL 2005, DROP/CREATE is better/faster as long as the
user has CREATE DATABASE permissions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stephen Hendricks" <shendricks@.datalabs.com> wrote in message
news:uIUs5efHGHA.916@.TK2MSFTNGP10.phx.gbl...
> What are you actually trying to accomplish here? If you drop all of the
> database objects, what are you trying to preserve for future use?
> ========================================
==========
> People who want to share their religious views with you almost never
> want you to share yours with them. -Dave Barry, author and columnist
> (1947- )
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks Dan!
Your script works perfectly for us.
- Joe Geretz -
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uhxmpBrHGHA.1088@.tk2msftngp13.phx.gbl...
> Here's a link to a script to drop all user objects using the basic
> technique Louis suggested. You can tweak for SQL 2005, although the need
> to do this might be mitigated if you can do DROP/CREATE DATABASE instead.
> http://tinyurl.com/9t9m6
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:ezib0ueHGHA.2040@.TK2MSFTNGP14.phx.gbl...
>|||Nice :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uhxmpBrHGHA.1088@.tk2msftngp13.phx.gbl...
> Here's a link to a script to drop all user objects using the basic
> technique Louis suggested. You can tweak for SQL 2005, although the need
> to do this might be mitigated if you can do DROP/CREATE DATABASE instead.
> http://tinyurl.com/9t9m6
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:ezib0ueHGHA.2040@.TK2MSFTNGP14.phx.gbl...
>
database of an unknown state back to a known state of 'empty'. Is there an
easy way to drop ALL structures; TABLES, VIEWS, SPROCS, FUNCTIONS, INDEXES,
TRIGGERS, CONSTRAINTS, etc. (Did I miss anything?)
(I don't want to rely on Dropping the Database and recreating it entirely as
a new database, since this requires a priviledge escalation as compared to
the priviledges required for working on structures inside of the database.)
Thanks for your help!
- Joseph Geretz -What are you actually trying to accomplish here? If you drop all of the
database objects, what are you trying to preserve for future use?
========================================
==========
People who want to share their religious views with you almost never
want you to share yours with them. -Dave Barry, author and columnist
(1947- )
*** Sent via Developersdex http://www.examnotes.net ***|||You could easily write a proc with a little cursor to do this using
2005:
select name, type
from sys.objects
2000
select name, xtype
from sysobjects
Then just use the type of proc to determine what drop statement to use. I
think this will do it, even considering constraints (sometimes you have to
drop the constraint before the columns,) I think if you limit this to:
sys.objects.type in ('P', 'FN', 'U', 'IF', 'TF', 'V') --pretty much the same
in 2000
--procedure, scalar function, user table, inline function, table value
function
You can do it. You would just have to run the cursor multiple times to
eliminate dependencies, or you could add foreign keys if you want to using
the information_schema,table_constraints for that.
I might also suggest you could just write a sql agent job that could be
executed to clean out the database pretty easily by killing any users,
dropping the database and recreating it, if you really want to just start
over.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:ezib0ueHGHA.2040@.TK2MSFTNGP14.phx.gbl...
> It there an easy way to 'clean out' a SQL Server database? I need to get a
> database of an unknown state back to a known state of 'empty'. Is there an
> easy way to drop ALL structures; TABLES, VIEWS, SPROCS, FUNCTIONS,
> INDEXES, TRIGGERS, CONSTRAINTS, etc. (Did I miss anything?)
> (I don't want to rely on Dropping the Database and recreating it entirely
> as a new database, since this requires a priviledge escalation as compared
> to the priviledges required for working on structures inside of the
> database.)
> Thanks for your help!
> - Joseph Geretz -
>|||Here's a link to a script to drop all user objects using the basic technique
Louis suggested. You can tweak for SQL 2005, although the need to do this
might be mitigated if you can do DROP/CREATE DATABASE instead.
http://tinyurl.com/9t9m6
Hope this helps.
Dan Guzman
SQL Server MVP
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:ezib0ueHGHA.2040@.TK2MSFTNGP14.phx.gbl...
> It there an easy way to 'clean out' a SQL Server database? I need to get a
> database of an unknown state back to a known state of 'empty'. Is there an
> easy way to drop ALL structures; TABLES, VIEWS, SPROCS, FUNCTIONS,
> INDEXES, TRIGGERS, CONSTRAINTS, etc. (Did I miss anything?)
> (I don't want to rely on Dropping the Database and recreating it entirely
> as a new database, since this requires a priviledge escalation as compared
> to the priviledges required for working on structures inside of the
> database.)
> Thanks for your help!
> - Joseph Geretz -
>|||Joseph
you can back up an empty database, then restore the backup against the
database you wish to clean up
HIH|||> What are you actually trying to accomplish here? If you drop all of the
> database objects, what are you trying to preserve for future use?
I can't speak for the OP but I've done this in SQL 2000 as an alternative to
DROP/CREATE DATABASE. This is significantly faster when the database is
large. However, in SQL 2005, extent formatting is deferred so that reason
doesn't apply. In SQL 2005, DROP/CREATE is better/faster as long as the
user has CREATE DATABASE permissions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stephen Hendricks" <shendricks@.datalabs.com> wrote in message
news:uIUs5efHGHA.916@.TK2MSFTNGP10.phx.gbl...
> What are you actually trying to accomplish here? If you drop all of the
> database objects, what are you trying to preserve for future use?
> ========================================
==========
> People who want to share their religious views with you almost never
> want you to share yours with them. -Dave Barry, author and columnist
> (1947- )
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks Dan!
Your script works perfectly for us.
- Joe Geretz -
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uhxmpBrHGHA.1088@.tk2msftngp13.phx.gbl...
> Here's a link to a script to drop all user objects using the basic
> technique Louis suggested. You can tweak for SQL 2005, although the need
> to do this might be mitigated if you can do DROP/CREATE DATABASE instead.
> http://tinyurl.com/9t9m6
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:ezib0ueHGHA.2040@.TK2MSFTNGP14.phx.gbl...
>|||Nice :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uhxmpBrHGHA.1088@.tk2msftngp13.phx.gbl...
> Here's a link to a script to drop all user objects using the basic
> technique Louis suggested. You can tweak for SQL 2005, although the need
> to do this might be mitigated if you can do DROP/CREATE DATABASE instead.
> http://tinyurl.com/9t9m6
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:ezib0ueHGHA.2040@.TK2MSFTNGP14.phx.gbl...
>
Clean old backup with retaindays
Hi,
i have 3 sql server, one with sql2000 and other with sql2005.
on each server i have a maintenance plan. this plan save all base to a
network share on another server.
all plans have the option retaindays at 2.
the sql2000 server clean correctly the old backup. but the sql2005 servers
don' t do it.
all the sql service are running under the same account.
i applied patch on a sql2005 at 3152, the other is always at 3042. but
nothing change.
everydays i muste clean manually the backup...retaindays don't work!
someone have an idea about this bug ?
Ty
ChristopheChristophe,
It's not a bug, it's a feature. :) I assume that you are using the
Maintenance Plans. One thing to know is that the option RETAINDAYS does not
actually delete anything, it is just marking the file to tell SQL Server not
to overwrite this file before the retain time is up.
If you are using SQL 2000 Legacy Maintenance Plans, check the Complete
Backup and Transaction Log Backup tabs and make sure that "Remove files
older than" is set.
If you are using SQL 2005 Maintenance plans, you also need to include the
Maintenance Cleanup task to specify file deletions.
RLF
"Christophe" <Christophe@.discussions.microsoft.com> wrote in message
news:C14216B5-32AA-4D94-972C-122D864DEA51@.microsoft.com...
> Hi,
> i have 3 sql server, one with sql2000 and other with sql2005.
> on each server i have a maintenance plan. this plan save all base to a
> network share on another server.
> all plans have the option retaindays at 2.
> the sql2000 server clean correctly the old backup. but the sql2005 servers
> don' t do it.
> all the sql service are running under the same account.
> i applied patch on a sql2005 at 3152, the other is always at 3042. but
> nothing change.
> everydays i muste clean manually the backup...retaindays don't work!
> someone have an idea about this bug ?
> Ty
> Christophe|||ty very much ! ;)
"Russell Fields" wrote:
> Christophe,
> It's not a bug, it's a feature. :) I assume that you are using the
> Maintenance Plans. One thing to know is that the option RETAINDAYS does not
> actually delete anything, it is just marking the file to tell SQL Server not
> to overwrite this file before the retain time is up.
> If you are using SQL 2000 Legacy Maintenance Plans, check the Complete
> Backup and Transaction Log Backup tabs and make sure that "Remove files
> older than" is set.
> If you are using SQL 2005 Maintenance plans, you also need to include the
> Maintenance Cleanup task to specify file deletions.
> RLF
>
> "Christophe" <Christophe@.discussions.microsoft.com> wrote in message
> news:C14216B5-32AA-4D94-972C-122D864DEA51@.microsoft.com...
> > Hi,
> >
> > i have 3 sql server, one with sql2000 and other with sql2005.
> > on each server i have a maintenance plan. this plan save all base to a
> > network share on another server.
> > all plans have the option retaindays at 2.
> > the sql2000 server clean correctly the old backup. but the sql2005 servers
> > don' t do it.
> > all the sql service are running under the same account.
> > i applied patch on a sql2005 at 3152, the other is always at 3042. but
> > nothing change.
> >
> > everydays i muste clean manually the backup...retaindays don't work!
> > someone have an idea about this bug ?
> >
> > Ty
> > Christophe
>
>
i have 3 sql server, one with sql2000 and other with sql2005.
on each server i have a maintenance plan. this plan save all base to a
network share on another server.
all plans have the option retaindays at 2.
the sql2000 server clean correctly the old backup. but the sql2005 servers
don' t do it.
all the sql service are running under the same account.
i applied patch on a sql2005 at 3152, the other is always at 3042. but
nothing change.
everydays i muste clean manually the backup...retaindays don't work!
someone have an idea about this bug ?
Ty
ChristopheChristophe,
It's not a bug, it's a feature. :) I assume that you are using the
Maintenance Plans. One thing to know is that the option RETAINDAYS does not
actually delete anything, it is just marking the file to tell SQL Server not
to overwrite this file before the retain time is up.
If you are using SQL 2000 Legacy Maintenance Plans, check the Complete
Backup and Transaction Log Backup tabs and make sure that "Remove files
older than" is set.
If you are using SQL 2005 Maintenance plans, you also need to include the
Maintenance Cleanup task to specify file deletions.
RLF
"Christophe" <Christophe@.discussions.microsoft.com> wrote in message
news:C14216B5-32AA-4D94-972C-122D864DEA51@.microsoft.com...
> Hi,
> i have 3 sql server, one with sql2000 and other with sql2005.
> on each server i have a maintenance plan. this plan save all base to a
> network share on another server.
> all plans have the option retaindays at 2.
> the sql2000 server clean correctly the old backup. but the sql2005 servers
> don' t do it.
> all the sql service are running under the same account.
> i applied patch on a sql2005 at 3152, the other is always at 3042. but
> nothing change.
> everydays i muste clean manually the backup...retaindays don't work!
> someone have an idea about this bug ?
> Ty
> Christophe|||ty very much ! ;)
"Russell Fields" wrote:
> Christophe,
> It's not a bug, it's a feature. :) I assume that you are using the
> Maintenance Plans. One thing to know is that the option RETAINDAYS does not
> actually delete anything, it is just marking the file to tell SQL Server not
> to overwrite this file before the retain time is up.
> If you are using SQL 2000 Legacy Maintenance Plans, check the Complete
> Backup and Transaction Log Backup tabs and make sure that "Remove files
> older than" is set.
> If you are using SQL 2005 Maintenance plans, you also need to include the
> Maintenance Cleanup task to specify file deletions.
> RLF
>
> "Christophe" <Christophe@.discussions.microsoft.com> wrote in message
> news:C14216B5-32AA-4D94-972C-122D864DEA51@.microsoft.com...
> > Hi,
> >
> > i have 3 sql server, one with sql2000 and other with sql2005.
> > on each server i have a maintenance plan. this plan save all base to a
> > network share on another server.
> > all plans have the option retaindays at 2.
> > the sql2000 server clean correctly the old backup. but the sql2005 servers
> > don' t do it.
> > all the sql service are running under the same account.
> > i applied patch on a sql2005 at 3152, the other is always at 3042. but
> > nothing change.
> >
> > everydays i muste clean manually the backup...retaindays don't work!
> > someone have an idea about this bug ?
> >
> > Ty
> > Christophe
>
>
Clean Log files
Hi All,
I have problem with my logs files, they are going so much especially when I
import data.
The best way that I found to clean the log files is:
1. Detach database
2. Delete the log file from the data folder
3. Attach database
Obviously I can't do this during the day until nobody is using the database.
I have 16 databases.
Can I do this using an script?
Any help will be appreciate... tks in advance.
JFB> The best way that I found to clean the log files is:
> 1. Detach database
> 2. Delete the log file from the data folder
> 3. Attach database
That may well be the best way to corrupt your data. The best way to
control log size is to choose the right recovery model, allocate a
sensible size to start with and then take regular Log and Database
backups as appropriate. If you are still in development then perhaps
database backups are sufficienct in which case choose the Simple
Recovery model.
David Portas
SQL Server MVP
--
http://support.microsoft.com/?kbid=873235|||Much of http://www.aspfaq.com/2446 applies to all databases, not just
tempdb.
Also see http://www.aspfaq.com/2471
"JFB" <help@.jfb.com> wrote in message
news:Od$WBFTjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have problem with my logs files, they are going so much especially when
> I import data.
> The best way that I found to clean the log files is:
> 1. Detach database
> 2. Delete the log file from the data folder
> 3. Attach database
> Obviously I can't do this during the day until nobody is using the
> database. I have 16 databases.
> Can I do this using an script?
> Any help will be appreciate... tks in advance.
> JFB
>|||By "cleaning" do you mean truncating the log or reducing the physical size?
If so, first check the recovery model set for your database.
If logs are not required to be maintained, then you can simply issue a
BACKUP LOG statement with NO_LOG option. To reduce the physical file size,
use DBCC SHRINKFILE. Details of these commands with examples can be found in
SQL Server Books Online.
Anith|||If you are not using BCP.EXE or the BULK INSERT statement to import your
data, then you should consider this. In SQL Server Books Online, read the
article titled "Logged and Minimally Logged Bulk Copy Operations".
Basically, the requirements for non-logged bulk copy operations are:
- The recovery model is simple or bulk-logged.
- The target table is not being replicated.
- The target table does not have any triggers.
- The target table has either 0 rows or no indexes.
- The TABLOCK hint is specified.
If this is an OLTP database, you can use the ALTER DATABASE.. statment to
switch between different recovery models as needed.
You may also want to consider dropping indexes (especially the clustered
index) before the import and re-creating them again afterward. This will
also have the affect of preventing index fragmentation and increasing the
load performance.
If this database is a 24x7 transactional system with many users, then you
may want to re-consider the process by which you import new data and avoid
massive import operations all together.
"JFB" <help@.jfb.com> wrote in message
news:Od$WBFTjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have problem with my logs files, they are going so much especially when
> I import data.
> The best way that I found to clean the log files is:
> 1. Detach database
> 2. Delete the log file from the data folder
> 3. Attach database
> Obviously I can't do this during the day until nobody is using the
> database. I have 16 databases.
> Can I do this using an script?
> Any help will be appreciate... tks in advance.
> JFB
>|||Hi,
Looks like you do not want the Transaction log backup. There are 2 ways to
clear the Log file online.
1. Take the transaction log backup and then shrink the LDF file.
Script:
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
use dbname
go
dbcc shrinkfile('logical_ldf_name',size_to_sh
rink_in_MB)
2. Truncate the Transaction log and Shrink.
Script:
Backup log <dbname> with Truncate_only
go
use dbname
go
dbcc shrinkfile('logical_ldf_name',size_to_sh
rink_in_MB)
Thanks
Hari
SQL Server MVP
"JFB" <help@.jfb.com> wrote in message
news:Od$WBFTjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have problem with my logs files, they are going so much especially when
> I import data.
> The best way that I found to clean the log files is:
> 1. Detach database
> 2. Delete the log file from the data folder
> 3. Attach database
> Obviously I can't do this during the day until nobody is using the
> database. I have 16 databases.
> Can I do this using an script?
> Any help will be appreciate... tks in advance.
> JFB
>
I have problem with my logs files, they are going so much especially when I
import data.
The best way that I found to clean the log files is:
1. Detach database
2. Delete the log file from the data folder
3. Attach database
Obviously I can't do this during the day until nobody is using the database.
I have 16 databases.
Can I do this using an script?
Any help will be appreciate... tks in advance.
JFB> The best way that I found to clean the log files is:
> 1. Detach database
> 2. Delete the log file from the data folder
> 3. Attach database
That may well be the best way to corrupt your data. The best way to
control log size is to choose the right recovery model, allocate a
sensible size to start with and then take regular Log and Database
backups as appropriate. If you are still in development then perhaps
database backups are sufficienct in which case choose the Simple
Recovery model.
David Portas
SQL Server MVP
--
http://support.microsoft.com/?kbid=873235|||Much of http://www.aspfaq.com/2446 applies to all databases, not just
tempdb.
Also see http://www.aspfaq.com/2471
"JFB" <help@.jfb.com> wrote in message
news:Od$WBFTjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have problem with my logs files, they are going so much especially when
> I import data.
> The best way that I found to clean the log files is:
> 1. Detach database
> 2. Delete the log file from the data folder
> 3. Attach database
> Obviously I can't do this during the day until nobody is using the
> database. I have 16 databases.
> Can I do this using an script?
> Any help will be appreciate... tks in advance.
> JFB
>|||By "cleaning" do you mean truncating the log or reducing the physical size?
If so, first check the recovery model set for your database.
If logs are not required to be maintained, then you can simply issue a
BACKUP LOG statement with NO_LOG option. To reduce the physical file size,
use DBCC SHRINKFILE. Details of these commands with examples can be found in
SQL Server Books Online.
Anith|||If you are not using BCP.EXE or the BULK INSERT statement to import your
data, then you should consider this. In SQL Server Books Online, read the
article titled "Logged and Minimally Logged Bulk Copy Operations".
Basically, the requirements for non-logged bulk copy operations are:
- The recovery model is simple or bulk-logged.
- The target table is not being replicated.
- The target table does not have any triggers.
- The target table has either 0 rows or no indexes.
- The TABLOCK hint is specified.
If this is an OLTP database, you can use the ALTER DATABASE.. statment to
switch between different recovery models as needed.
You may also want to consider dropping indexes (especially the clustered
index) before the import and re-creating them again afterward. This will
also have the affect of preventing index fragmentation and increasing the
load performance.
If this database is a 24x7 transactional system with many users, then you
may want to re-consider the process by which you import new data and avoid
massive import operations all together.
"JFB" <help@.jfb.com> wrote in message
news:Od$WBFTjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have problem with my logs files, they are going so much especially when
> I import data.
> The best way that I found to clean the log files is:
> 1. Detach database
> 2. Delete the log file from the data folder
> 3. Attach database
> Obviously I can't do this during the day until nobody is using the
> database. I have 16 databases.
> Can I do this using an script?
> Any help will be appreciate... tks in advance.
> JFB
>|||Hi,
Looks like you do not want the Transaction log backup. There are 2 ways to
clear the Log file online.
1. Take the transaction log backup and then shrink the LDF file.
Script:
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
use dbname
go
dbcc shrinkfile('logical_ldf_name',size_to_sh
rink_in_MB)
2. Truncate the Transaction log and Shrink.
Script:
Backup log <dbname> with Truncate_only
go
use dbname
go
dbcc shrinkfile('logical_ldf_name',size_to_sh
rink_in_MB)
Thanks
Hari
SQL Server MVP
"JFB" <help@.jfb.com> wrote in message
news:Od$WBFTjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have problem with my logs files, they are going so much especially when
> I import data.
> The best way that I found to clean the log files is:
> 1. Detach database
> 2. Delete the log file from the data folder
> 3. Attach database
> Obviously I can't do this during the day until nobody is using the
> database. I have 16 databases.
> Can I do this using an script?
> Any help will be appreciate... tks in advance.
> JFB
>
Clean Install Windows Auth Error
Hi,
I cannot log in to SQL Server 2005 Dev Edition in my local machine using Windows Authentication. The server returned "Login failed..." when connecting with SQL Server Management Studio.
I have not change anything since installation of this server.
This problem happens in RTM and SP1 versions, both running on Windows Vista RTM.
Anyone having this kind of problem too? Any solution? I'm guessing it's Vista-related.
True, this is something of Vista. http://www.microsoft.com/sql/howtobuy/windowsvistasupport.mspx
Sorry for not reading it first.
Subscribe to:
Posts (Atom)