Prev: BUY FAKE PASSPORTS OF AUSTRALIA,FAKE AUSTRALIAN PASSPORTS FOR SALE
Next: ROLLBACK on error
From: Bob Barrows on 19 May 2010 09:31
sChapman wrote:
> How can you find out if a query has been run on a database? The reason
> is we would like to see if a database has been accessed in the last
> three months and if not, we will archive the database and remove it
> from the server to create space. Thanks.

I saw in your other reply that you are using SQL 2005, so my immediate
thought is to run the Index Usage Statistics report (right-click the
database in SSMS, then Reports>Standard Reports) against a database you
suspect is not being used. Scroll through the user scan and update
columns to see if any recent activity shows up.

Or, once you realize that the index usage report uses the
sys.dm_db_index_usage_stats dynamic view, you can write a scriptlike
this:

declare @datelimit datetime;
set @datelimit=dateadd(m,-1,current_timestamp);
select distinct db_name(database_id) from
sys.dm_db_index_usage_stats i
where NOT EXISTS (
select * from sys.dm_db_index_usage_stats
where database_id=i.database_id
and (last_user_seek>@datelimit
or last_user_scan >@datelimit
or last_user_lookup>@datelimit));

or create a folded view of sys.dm_db_index_usage_stats like this:

create foldedview as
select database_id, last_user_seek last_used
from sys.dm_db_index_usage_stats
union all
select database_id, last_user_scan
from sys.dm_db_index_usage_stats
etc.

so you can do this

select db_name(database_id), max(last_used)
from foldedview
group by database_id
order by max(last_used)

Of course, if the databases are badly designed and have no indexes, or
the indexes that do exist never get used, you might get some false
positives ... but that's not very likely, is it.



--
HTH,
Bob Barrows


From: sChapman on 19 May 2010 11:30
Thanks a lot. Your solution will work for us. Much appreciated.
From: Cleary on 20 May 2010 20:05
On May 18, 1:54 pm, "TheSQLGuru" <kgbo...(a)earthlink.net> wrote:

> There are some very expensive third party products that could help I think,
> that do things like network packet sniffing to find all queries sent to a
> server.


One such third party product is Appfluent, although I'm not sure if
they are supporting SQL Server 2005. (Disclaimer -- I used to work
for Appfluent, building the logic that answered such questions :-)
From: TheSQLGuru on 20 May 2010 20:51
If you need some help with things drop me a private message. I have a
client I am the dba for where I manage over 7000 databases on a single
server. So I KNOW your pain points.

If your SLA allows it, just generate scripts for detach/attach of all
databases. Start picking 100 at a time or so and detach them. When you get
a support call, 10 seconds later the database can be ready to go when you
execute the attach script for the appropriate database. Many people will
laugh at this, but it is actually is a viable strategy for some entities. I
will note that before I do that I would use the index usage stats DMV to
narrow down the hit-list. :-)



--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"sChapman" <sumanthcp(a)googlemail.com> wrote in message
news:6c0e3ebd-3791-431e-a8a0-ed304cf7d227(a)m33g2000vbi.googlegroups.com...
On 18 May, 18:55, "TheSQLGuru" <kgbo...(a)earthlink.net> wrote:
> Just a general question: why don't you jus add more space? :-)

That is not possible in our environment, we have 5.6TB capacity and we
have around 2000 databases! we run out of space quite regularly.
Adding more space increases costs (impacts backup capcity etc.) and
makes it harder to manage the huge size/number.

We are using Sql Server 2005. We can not move to 2008 as yet as the
software we use does not support 2008.


From: Bob Barrows on 24 May 2010 08:57
Ah, the old delete-it-and-see-who-complains strategem :-)

TheSQLGuru wrote:
>
> If your SLA allows it, just generate scripts for detach/attach of all
> databases. Start picking 100 at a time or so and detach them. When
> you get a support call, 10 seconds later the database can be ready to
> go when you execute the attach script for the appropriate database.
> Many people will laugh at this, but it is actually is a viable
> strategy for some entities. I will note that before I do that I
> would use the index usage stats DMV to narrow down the hit-list. :-)
>
>
>
>
> "sChapman" <sumanthcp(a)googlemail.com> wrote in message
>
news:6c0e3ebd-3791-431e-a8a0-ed304cf7d227(a)m33g2000vbi.googlegroups.com...
> On 18 May, 18:55, "TheSQLGuru" <kgbo...(a)earthlink.net> wrote:
>> Just a general question: why don't you jus add more space? :-)
>
> That is not possible in our environment, we have 5.6TB capacity and we
> have around 2000 databases! we run out of space quite regularly.
> Adding more space increases costs (impacts backup capcity etc.) and
> makes it harder to manage the huge size/number.
>
> We are using Sql Server 2005. We can not move to 2008 as yet as the
> software we use does not support 2008.

--
HTH,
Bob Barrows


First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: BUY FAKE PASSPORTS OF AUSTRALIA,FAKE AUSTRALIAN PASSPORTS FOR SALE
Next: ROLLBACK on error