March 31, 2009 at 4:31 am
Hello!
Is there a way for the SQL Server to delete a particular db after a limited period of time?
I have created a db and I want it to run for say, 10 days, after which, I want it to be deleted automatically unless I intervene.
Is there a way to set this up?
scorp
March 31, 2009 at 5:00 am
Hi,
Based upon your requirement, incase if you do not want to have a particular database older than 10 days...You can create a job with the following condition and schedule it to run daily.
IF (select DATEDIFF(day, crdate, getdate()) FROM master..sysdatabases where name = 'dbname') > 10
BEGIN
drop database 'dbname'
ELSE
PRINT 'Database not older than 10 days'
At any point if you do not want to drop the database, just go ahead and disable this job. This is one of the option I can see, not sure how well it suits your requirement.
-Rajini
March 31, 2009 at 5:06 am
Small correction in the query:
if (SELECT DATEDIFF(day, crdate, getdate()) FROM master..sysdatabases where name = 'dbname') > 10
BEGIN
drop database 'dbname'
END
Else
PRINT 'Database not older than 10 days'
March 31, 2009 at 5:11 am
I have many databases, but I want this to be setup on 1 particular db only.
Looking at the structure of the query, I think this is exactly what I want, except the printing part, which I'll leave out.
I'll try this out and see if this works.
Thanks tons!
scorp
March 31, 2009 at 5:44 am
It may not work and might give you the error message like "You can not drop the database XXXX as user are connected to the database."
You need to kill the connected session to the specific database XXXX before executing drop command.
Also you can set job to delete automatically after the successful execution.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 31, 2009 at 8:13 am
rajiniforu (3/31/2009)
Small correction in the query:if (SELECT DATEDIFF(day, crdate, getdate()) FROM master..sysdatabases where name = 'dbname') > 10
BEGIN
drop database 'dbname'
END
Else
PRINT 'Database not older than 10 days'
That was a good one but before dong this, you need to make sure that the database is in single user mode.
March 31, 2009 at 4:06 pm
Use the below mentioned script as first step of your job:
Kill DB Users before restoration
USE master
DECLARE @DB_Name varchar(50),
@SPID smallint,
@msg varchar(255),
@DBID int
SET @DB_Name = 'DBName Here'
SET @DBID = (SELECT dbid FROM sysdatabases WHERE name = @DB_name)
-----------------------------------------------------------------------------------------
DECLARE db_users INSENSITIVE cursor for
SELECT spid
FROM sysprocesses
WHERE dbid = @DBID
OPEN db_users
FETCH NEXT FROM db_users INTO @SPID
WHILE @@fetch_status = 0
BEGIN SELECT @msg = 'kill '+ CONVERT(char(5), @SPID)
PRINT @msg -- don't really need to print it out
EXEC (@msg)
FETCH NEXT FROM db_users INTO @SPID
END
CLOSE db_users
DEALLOCATE db_users
GO
MJ
April 1, 2009 at 1:04 am
Some application connections are persistent in nature, so if you simply kill them they will come again in no time.
alter database [your_db_name] set offline with rollback immediate
Then drop the database
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply