October 24, 2007 at 1:19 am
Hi friends,
I have a production sql server 2000, there're over 30 DBs on the box, mostly are website backend DB. Some of them may already retired, but they are getting regular backups everyday.
I was wondering if there're any methods to verify whether certain dbs are in use or not so i can del unused ones to free diskspace.
Thanks,
Jack
October 24, 2007 at 2:26 am
Hi,
May be you can try running a profiler with minimum required events and if you already have some databases in mind (unused) , use the DBID for filtering.
Renuka__
[font="Verdana"]Renuka__[/font]
October 24, 2007 at 2:51 am
Hi
U can also use sp_who to check whether any users are connected to a database. But i think running profiler for some amount of time is the better option.
"Keep Trying"
October 24, 2007 at 3:14 am
take the db offline. see if anyone complains.
or setup a job that snapshots sysprocesses for a certain amount of time and check the dbid column.
October 24, 2007 at 3:28 am
Try this I have used it and it does work, but I would suggest testing it first.
All credit should go to the author of this script
Robin Bäck (robin@robbac.com)
*****************************************************
DECLARE@dbVARCHAR(255),
@intervalINT,
@stopatDATETIME,
@totalusersINT,
@reportfileVARCHAR(500)
/*************************************************************************************
**PURPOSE: **
**Check if a database is beeing used or not **
** **
**COMPATIBLE WITH: **
**SQL 7.0 **
**SQL 8.0 **
** **
**VARIABLES: **
**NAMEDESCRIPTION **
**====================================================================== **
**@dbWhat database to check **
**cannot be NULL **
**@intervalHow many milliseconds between each check **
**minimum value: 1 **
**maximum value: 999 **
**default 1 (each millisecond) **
**@stopatWhen the schudeled task will end **
**default NULL (never stop) **
**FORMAT yyyy-mm-dd hh:mm:ss.mmm **
**@totalusersHow many users have to logon before reporting **
**default 1 **
**@reportfileFull local path and filename on the server for report **
**default NULL (do not create reportfile).rpt" **
**====================================================================== **
** **
**OBJECTS CREATED: **
**tempdb.dbo.database_ _usage_table **
**tempdb.dbo.database_ _usage_proc **
**A checkulded job named: **
**DATABASE USAGE CHECK - **
**@reportfile (if not NULL) **
** **
**WRITTEN BY: **
**Robin Bäck (robin@robbac.com) **
** **
**WRITTEN WHEN: **
**2003-07-14 **
*************************************************************************************/
SELECT @db = 'perflogs'
SELECT @interval = 10
SELECT @stopat = '2007-10-12'
SELECT @totalusers = 5
SELECT @reportfile = 'C:\MyReportFile.rpt'
SET NOCOUNT ON
DECLARE@jobnameVARCHAR(255),
@job_idUNIQUEIDENTIFIER,
@job_step_idUNIQUEIDENTIFIER,
@tempvarVARCHAR(255)
-- CHECK AND SET VARIABLES
IF ( @db IS NULL )
BEGIN
PRINT 'Variable @db has to be set'
GOTO the_end
END
IF NOT EXISTS ( SELECT * FROM master.dbo.sysdatabases WHERE name = @db )
BEGIN
PRINT 'DATABASE ' + @db + ' NOT FOUND ON THIS SERVER'
GOTO the_end
END
IF ( @interval IS NULL OR @interval 999 )
BEGIN
PRINT 'Variable @interval has to be an integer between 1 and 999'
GOTO the_end
END
IF ( @stopat < GETDATE() )
BEGIN
PRINT 'Variabel @stopat has to be after current time'
GOTO the_end
END
IF ( @stopat IS NULL )
SELECT @stopat = '9999-12-31'
IF ( @totalusers IS NULL )
SELECT @totalusers = 1
-- Create a temporary table in tempdb
IF EXISTS (
SELECT*
FROMdbo.sysobjects
WHEREname = 'database_' + @db + '_usage_table' AND
type = 'U' )
EXEC('DROP TABLE dbo.database_' + @db + '_usage_table')
EXEC ('
create table dbo.database_' + @db + '_usage_table (
checkdatesmalldatetimenull,
dbvarchar(50)null,
loginamevarchar(255)null,
nt_usernamenchar(128)null,
hostnamenchar(128)null
)
')
-- Create a proc to run in tempdb
IF EXISTS (
SELECT*
FROMdbo.sysobjects
WHEREname = 'database_' + @db + '_usage_proc' AND
type = 'P' )
EXEC('DROP PROCEDURE dbo.database_' + @db + '_usage_proc')
EXEC ('
CREATE PROC dbo.database_' + @db + '_usage_proc
@stopatDATETIME,
@intervalINT,
@totalusersINT
AS
SET NOCOUNT ON
IF ( @stopat < GETDATE() )
RAISERROR (''Variabel @stopat has to be after current time'', 16, 1)
DECLARE@waitfor DATETIME
SELECT@waitfor = GETDATE()
WHILE ( GETDATE() < @stopat )
BEGIN
-- Check if there are any users using the database
INSERT INTO dbo.database_' + @db + '_usage_table (
checkdate,
db,
loginame,
nt_username,
hostname
)
SELECTDISTINCT
GETDATE(),
DB_NAME(sp.dbid),
RTRIM(sp.loginame),
RTRIM(sp.nt_username),
RTRIM(sp.hostname)
FROMmaster.dbo.sysprocesses sp
LEFT OUTER JOIN dbo.database_' + @db + '_usage_table dut ON sp.loginame = dut.loginame
WHEREDB_NAME(sp.dbid) = ''' + @db + '''
AND dut.loginame IS NULL
-- Check if total users logged has been met
IF ( SELECT COUNT(*) FROM dbo.database_' + @db + '_usage_table ) >= @totalusers
GOTO out_of_loop
SELECT@waitfor = DATEADD(ms, @interval , GETDATE())
WAITFOR TIME @waitfor
END
out_of_loop:
SELECT*
FROMdbo.database_' + @db + '_usage_table
ORDER BY
checkdate
')
-- Create the sceduled job
SELECT@jobname = 'DATABASE USAGE CHECK - ' + @db
IF EXISTS (
SELECT*
FROMmsdb.dbo.sysjobs
WHEREname = @jobname )
BEGIN
PRINT 'The scheduled job ''' + @jobname + ''' already exists'
GOTO the_end
END
-- Add the job
EXECmsdb.dbo.sp_add_job
@job_name =@jobname,
@description ='Job for checking database usage',
@start_step_id =1,
@owner_login_name ='sa',
@notify_level_eventlog =2, -- on failure
@job_id =@job_id OUTPUT
-- Add the jobstep
SELECT@tempvar =
'EXEC dbo.database_' + @db + '_usage_proc
@stopat = ''' + CONVERT(VARCHAR, @stopat, 21) + ''', -- yyyy-mm-dd hh:mm:ss.mmm
@interval = ' + CONVERT(VARCHAR, @interval) + ', -- min 0, max 999
@totalusers = ' + CONVERT(VARCHAR, @totalusers) + ' -- min 1, max 2^31-1'
EXECmsdb.dbo.sp_add_jobstep
@job_id =@job_id,
@step_id = 1,
@step_name =@jobname,
@subsystem ='TSQL',
@command =@tempvar,
@cmdexec_success_code =0,
@on_success_action =1, -- quit with success
@on_fail_action =2, -- quit with failure
@database_name ='tempdb'
-- Shall there be a reportfile
IF ( @reportfile IS NOT NULL )
EXEC msdb.dbo.sp_update_jobstep
@job_id =@job_id,
@step_id =1,
@step_name =@jobname,
@output_file_name =@reportfile
-- Set target server to local server
EXEC msdb.dbo.sp_add_jobserver
@job_id = @job_id,
@server_name = '(local)'
-- Start the job
EXEC msdb.dbo.sp_start_job @job_id = @job_id
the_end:
GO
October 25, 2007 at 7:29 am
1) you can never know FOR CERTAIN that a database won't be used EVER again without searching every piece of code you have in existence - and even then you can't guard against someone wanting to do an ad hoc query on the database 2 years from now.
2) you can track sysprocesses.dbid over time and analyze if certain databases never get a hit there.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 25, 2007 at 10:02 am
If your intent is to find database(s) no longer in use in order to remove them there is a relatively simple and low impact way. Just take the database(s) offline !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 25, 2007 at 10:55 am
If you want to find out which databases are not used at THE CURRENT MOMENT use this query, since this is 2005 forum I wrote and tested against 2005, but for 2000 you probably have to remove dots:
select
d.name,
used = case count(p.spid)
when 0 then 'not used'
else 'used' end
from sys.databases d
left join sys.sysprocesses p
on d.database_id = p.dbid
group by d.name
October 26, 2007 at 3:36 am
This code is great for looking at if the databases are being used at that point in time, if iwant to archive or remove a database from a server I would like to base the decision on a longer period of time.
____
select
d.name,
used = case count(p.spid)
when 0 then 'not used'
else 'used' end
from sys.databases d
left join sys.sysprocesses p
on d.database_id = p.dbid
group by d.name
October 26, 2007 at 3:40 am
Just another thing this script also indicates system databases as not being in used. This can be dangerous if you allow your junior DBA to do database clean ups
October 26, 2007 at 8:19 am
Another option would be to turn the database "auto close" option on for each database you want to monitor. Then a "starting up database xxxxx' message will be logged in the SQL Server log each time the database is accessed.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply