For a company like an airline that runs 24/7 and the database is always
being accessed and they can't shut down, how do they do maintenance on their
databases? How do they do things like defrag or rebuild indexes or shrink
files?
Do they have two databases and they switch back and forth?
Thanks,
Dan
> For a company like an airline that runs 24/7 and the database is always
> being accessed and they can't shut down, how do they do maintenance on
their
> databases?
Clustering, perhaps? We use it here...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||Generally they use dbcc indexdefrag instead of dbcc dbreindex, you just need to be more diligent with its application.
It also becomes critical to size the database correctly so that it does not go offline for database growths/shrinks.
|||They do what they have to based on their specific requirements. Almost no
one that is in a 24 x 7 will be doing any shrinking of files and it should
rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
option and INDEDXDEFRAG might be the way to go. It is impossableto go into
enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
have this type of requirement and are asking these questions I would highly
recommend you hire a qualified consultant.
Andrew J. Kelly SQL MVP
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> For a company like an airline that runs 24/7 and the database is always
> being accessed and they can't shut down, how do they do maintenance on
their
> databases? How do they do things like defrag or rebuild indexes or shrink
> files?
> Do they have two databases and they switch back and forth?
> Thanks,
> Dan
>
|||Good points. I was thinking more along the lines of applying security
fixes, windows update, etc.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:#Qh#aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> They do what they have to based on their specific requirements. Almost
no
> one that is in a 24 x 7 will be doing any shrinking of files and it should
> rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
> option and INDEDXDEFRAG might be the way to go. It is impossableto go
into
> enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
> have this type of requirement and are asking these questions I would
highly
> recommend you hire a qualified consultant.
|||Ken and Andrew,
So, even though INDEXDEFRAG isn't as efficient as DBREINDEX, I guess it is
sufficient. Isn't there fragmentation that also happens to the data on the
disk as well as to the indexes? How would you handle that kind of
fragmentation?
Dan
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:%23Qh%23aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> They do what they have to based on their specific requirements. Almost
no
> one that is in a 24 x 7 will be doing any shrinking of files and it should
> rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
> option and INDEDXDEFRAG might be the way to go. It is impossableto go
into
> enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
> have this type of requirement and are asking these questions I would
highly[vbcol=seagreen]
> recommend you hire a qualified consultant.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> their
shrink
>
|||Depending on what you mean by 'as efficient' (disk space, log space, cpu
usage, IOs, elapsed time), I didn't design INDEXDEFRAG to be more efficient
that DBREINDEX - it's meant to be an online alternative to rebuilding
indexes. You should read the whitepaper below for more details.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:#FdM04bPEHA.556@.tk2msftngp13.phx.gbl...
> Ken and Andrew,
> So, even though INDEXDEFRAG isn't as efficient as DBREINDEX, I guess it
is[vbcol=seagreen]
> sufficient. Isn't there fragmentation that also happens to the data on the
> disk as well as to the indexes? How would you handle that kind of
> fragmentation?
> Dan
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:%23Qh%23aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> no
should[vbcol=seagreen]
an[vbcol=seagreen]
> into
you[vbcol=seagreen]
> highly
always
> shrink
>
|||Thanks. I will.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uRVJRJcPEHA.252@.TK2MSFTNGP10.phx.gbl...
> Depending on what you mean by 'as efficient' (disk space, log space, cpu
> usage, IOs, elapsed time), I didn't design INDEXDEFRAG to be more
efficient
> that DBREINDEX - it's meant to be an online alternative to rebuilding
> indexes. You should read the whitepaper below for more details.
>
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:#FdM04bPEHA.556@.tk2msftngp13.phx.gbl...
> is
the[vbcol=seagreen]
Almost[vbcol=seagreen]
> should
> an
> you
> always
on
>
Showing posts with label accessed. Show all posts
Showing posts with label accessed. Show all posts
Wednesday, March 7, 2012
database maintenance for companies that run 24/7
For a company like an airline that runs 24/7 and the database is always
being accessed and they can't shut down, how do they do maintenance on their
databases? How do they do things like defrag or rebuild indexes or shrink
files?
Do they have two databases and they switch back and forth?
Thanks,
Dan> For a company like an airline that runs 24/7 and the database is always
> being accessed and they can't shut down, how do they do maintenance on
their
> databases?
Clustering, perhaps? We use it here...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Generally they use dbcc indexdefrag instead of dbcc dbreindex, you just need to be more diligent with its application
It also becomes critical to size the database correctly so that it does not go offline for database growths/shrinks.|||They do what they have to based on their specific requirements. Almost no
one that is in a 24 x 7 will be doing any shrinking of files and it should
rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
option and INDEDXDEFRAG might be the way to go. It is impossableto go into
enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
have this type of requirement and are asking these questions I would highly
recommend you hire a qualified consultant.
--
Andrew J. Kelly SQL MVP
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> For a company like an airline that runs 24/7 and the database is always
> being accessed and they can't shut down, how do they do maintenance on
their
> databases? How do they do things like defrag or rebuild indexes or shrink
> files?
> Do they have two databases and they switch back and forth?
> Thanks,
> Dan
>|||Good points. I was thinking more along the lines of applying security
fixes, windows update, etc.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:#Qh#aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> They do what they have to based on their specific requirements. Almost
no
> one that is in a 24 x 7 will be doing any shrinking of files and it should
> rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
> option and INDEDXDEFRAG might be the way to go. It is impossableto go
into
> enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
> have this type of requirement and are asking these questions I would
highly
> recommend you hire a qualified consultant.|||Ken and Andrew,
So, even though INDEXDEFRAG isn't as efficient as DBREINDEX, I guess it is
sufficient. Isn't there fragmentation that also happens to the data on the
disk as well as to the indexes? How would you handle that kind of
fragmentation?
Dan
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:%23Qh%23aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> They do what they have to based on their specific requirements. Almost
no
> one that is in a 24 x 7 will be doing any shrinking of files and it should
> rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
> option and INDEDXDEFRAG might be the way to go. It is impossableto go
into
> enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
> have this type of requirement and are asking these questions I would
highly
> recommend you hire a qualified consultant.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> > For a company like an airline that runs 24/7 and the database is always
> > being accessed and they can't shut down, how do they do maintenance on
> their
> > databases? How do they do things like defrag or rebuild indexes or
shrink
> > files?
> >
> > Do they have two databases and they switch back and forth?
> >
> > Thanks,
> >
> > Dan
> >
> >
>|||Depending on what you mean by 'as efficient' (disk space, log space, cpu
usage, IOs, elapsed time), I didn't design INDEXDEFRAG to be more efficient
that DBREINDEX - it's meant to be an online alternative to rebuilding
indexes. You should read the whitepaper below for more details.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:#FdM04bPEHA.556@.tk2msftngp13.phx.gbl...
> Ken and Andrew,
> So, even though INDEXDEFRAG isn't as efficient as DBREINDEX, I guess it
is
> sufficient. Isn't there fragmentation that also happens to the data on the
> disk as well as to the indexes? How would you handle that kind of
> fragmentation?
> Dan
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:%23Qh%23aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> > They do what they have to based on their specific requirements. Almost
> no
> > one that is in a 24 x 7 will be doing any shrinking of files and it
should
> > rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be
an
> > option and INDEDXDEFRAG might be the way to go. It is impossableto go
> into
> > enough detail of how to maintain a 24 x 7 operation in a newsgroup. If
you
> > have this type of requirement and are asking these questions I would
> highly
> > recommend you hire a qualified consultant.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Dan" <ddonahue@.archermalmo.com> wrote in message
> > news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> > > For a company like an airline that runs 24/7 and the database is
always
> > > being accessed and they can't shut down, how do they do maintenance on
> > their
> > > databases? How do they do things like defrag or rebuild indexes or
> shrink
> > > files?
> > >
> > > Do they have two databases and they switch back and forth?
> > >
> > > Thanks,
> > >
> > > Dan
> > >
> > >
> >
> >
>|||Thanks. I will.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uRVJRJcPEHA.252@.TK2MSFTNGP10.phx.gbl...
> Depending on what you mean by 'as efficient' (disk space, log space, cpu
> usage, IOs, elapsed time), I didn't design INDEXDEFRAG to be more
efficient
> that DBREINDEX - it's meant to be an online alternative to rebuilding
> indexes. You should read the whitepaper below for more details.
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:#FdM04bPEHA.556@.tk2msftngp13.phx.gbl...
> > Ken and Andrew,
> >
> > So, even though INDEXDEFRAG isn't as efficient as DBREINDEX, I guess it
> is
> > sufficient. Isn't there fragmentation that also happens to the data on
the
> > disk as well as to the indexes? How would you handle that kind of
> > fragmentation?
> >
> > Dan
> >
> > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > news:%23Qh%23aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> > > They do what they have to based on their specific requirements.
Almost
> > no
> > > one that is in a 24 x 7 will be doing any shrinking of files and it
> should
> > > rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be
> an
> > > option and INDEDXDEFRAG might be the way to go. It is impossableto go
> > into
> > > enough detail of how to maintain a 24 x 7 operation in a newsgroup. If
> you
> > > have this type of requirement and are asking these questions I would
> > highly
> > > recommend you hire a qualified consultant.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Dan" <ddonahue@.archermalmo.com> wrote in message
> > > news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> > > > For a company like an airline that runs 24/7 and the database is
> always
> > > > being accessed and they can't shut down, how do they do maintenance
on
> > > their
> > > > databases? How do they do things like defrag or rebuild indexes or
> > shrink
> > > > files?
> > > >
> > > > Do they have two databases and they switch back and forth?
> > > >
> > > > Thanks,
> > > >
> > > > Dan
> > > >
> > > >
> > >
> > >
> >
> >
>
being accessed and they can't shut down, how do they do maintenance on their
databases? How do they do things like defrag or rebuild indexes or shrink
files?
Do they have two databases and they switch back and forth?
Thanks,
Dan> For a company like an airline that runs 24/7 and the database is always
> being accessed and they can't shut down, how do they do maintenance on
their
> databases?
Clustering, perhaps? We use it here...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Generally they use dbcc indexdefrag instead of dbcc dbreindex, you just need to be more diligent with its application
It also becomes critical to size the database correctly so that it does not go offline for database growths/shrinks.|||They do what they have to based on their specific requirements. Almost no
one that is in a 24 x 7 will be doing any shrinking of files and it should
rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
option and INDEDXDEFRAG might be the way to go. It is impossableto go into
enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
have this type of requirement and are asking these questions I would highly
recommend you hire a qualified consultant.
--
Andrew J. Kelly SQL MVP
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> For a company like an airline that runs 24/7 and the database is always
> being accessed and they can't shut down, how do they do maintenance on
their
> databases? How do they do things like defrag or rebuild indexes or shrink
> files?
> Do they have two databases and they switch back and forth?
> Thanks,
> Dan
>|||Good points. I was thinking more along the lines of applying security
fixes, windows update, etc.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:#Qh#aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> They do what they have to based on their specific requirements. Almost
no
> one that is in a 24 x 7 will be doing any shrinking of files and it should
> rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
> option and INDEDXDEFRAG might be the way to go. It is impossableto go
into
> enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
> have this type of requirement and are asking these questions I would
highly
> recommend you hire a qualified consultant.|||Ken and Andrew,
So, even though INDEXDEFRAG isn't as efficient as DBREINDEX, I guess it is
sufficient. Isn't there fragmentation that also happens to the data on the
disk as well as to the indexes? How would you handle that kind of
fragmentation?
Dan
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:%23Qh%23aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> They do what they have to based on their specific requirements. Almost
no
> one that is in a 24 x 7 will be doing any shrinking of files and it should
> rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
> option and INDEDXDEFRAG might be the way to go. It is impossableto go
into
> enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
> have this type of requirement and are asking these questions I would
highly
> recommend you hire a qualified consultant.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> > For a company like an airline that runs 24/7 and the database is always
> > being accessed and they can't shut down, how do they do maintenance on
> their
> > databases? How do they do things like defrag or rebuild indexes or
shrink
> > files?
> >
> > Do they have two databases and they switch back and forth?
> >
> > Thanks,
> >
> > Dan
> >
> >
>|||Depending on what you mean by 'as efficient' (disk space, log space, cpu
usage, IOs, elapsed time), I didn't design INDEXDEFRAG to be more efficient
that DBREINDEX - it's meant to be an online alternative to rebuilding
indexes. You should read the whitepaper below for more details.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:#FdM04bPEHA.556@.tk2msftngp13.phx.gbl...
> Ken and Andrew,
> So, even though INDEXDEFRAG isn't as efficient as DBREINDEX, I guess it
is
> sufficient. Isn't there fragmentation that also happens to the data on the
> disk as well as to the indexes? How would you handle that kind of
> fragmentation?
> Dan
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:%23Qh%23aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> > They do what they have to based on their specific requirements. Almost
> no
> > one that is in a 24 x 7 will be doing any shrinking of files and it
should
> > rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be
an
> > option and INDEDXDEFRAG might be the way to go. It is impossableto go
> into
> > enough detail of how to maintain a 24 x 7 operation in a newsgroup. If
you
> > have this type of requirement and are asking these questions I would
> highly
> > recommend you hire a qualified consultant.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Dan" <ddonahue@.archermalmo.com> wrote in message
> > news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> > > For a company like an airline that runs 24/7 and the database is
always
> > > being accessed and they can't shut down, how do they do maintenance on
> > their
> > > databases? How do they do things like defrag or rebuild indexes or
> shrink
> > > files?
> > >
> > > Do they have two databases and they switch back and forth?
> > >
> > > Thanks,
> > >
> > > Dan
> > >
> > >
> >
> >
>|||Thanks. I will.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uRVJRJcPEHA.252@.TK2MSFTNGP10.phx.gbl...
> Depending on what you mean by 'as efficient' (disk space, log space, cpu
> usage, IOs, elapsed time), I didn't design INDEXDEFRAG to be more
efficient
> that DBREINDEX - it's meant to be an online alternative to rebuilding
> indexes. You should read the whitepaper below for more details.
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:#FdM04bPEHA.556@.tk2msftngp13.phx.gbl...
> > Ken and Andrew,
> >
> > So, even though INDEXDEFRAG isn't as efficient as DBREINDEX, I guess it
> is
> > sufficient. Isn't there fragmentation that also happens to the data on
the
> > disk as well as to the indexes? How would you handle that kind of
> > fragmentation?
> >
> > Dan
> >
> > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > news:%23Qh%23aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> > > They do what they have to based on their specific requirements.
Almost
> > no
> > > one that is in a 24 x 7 will be doing any shrinking of files and it
> should
> > > rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be
> an
> > > option and INDEDXDEFRAG might be the way to go. It is impossableto go
> > into
> > > enough detail of how to maintain a 24 x 7 operation in a newsgroup. If
> you
> > > have this type of requirement and are asking these questions I would
> > highly
> > > recommend you hire a qualified consultant.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Dan" <ddonahue@.archermalmo.com> wrote in message
> > > news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> > > > For a company like an airline that runs 24/7 and the database is
> always
> > > > being accessed and they can't shut down, how do they do maintenance
on
> > > their
> > > > databases? How do they do things like defrag or rebuild indexes or
> > shrink
> > > > files?
> > > >
> > > > Do they have two databases and they switch back and forth?
> > > >
> > > > Thanks,
> > > >
> > > > Dan
> > > >
> > > >
> > >
> > >
> >
> >
>
Tuesday, February 14, 2012
Database last accessed
We have a Windows 2000 server with SQL 2000 on it and a ton of databases
(300+). Is there a way to find out which databases are still being used
(last time accessed) so I can archive some of them an only have the databases
that are actually being used.No, SQL Server doesn't log all access to a database...
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"spinkid" <spinkid@.discussions.microsoft.com> wrote in message
news:C8A5C8AE-8671-41D7-A4B0-5FF6B3D1EBD3@.microsoft.com...
> We have a Windows 2000 server with SQL 2000 on it and a ton of databases
> (300+). Is there a way to find out which databases are still being used
> (last time accessed) so I can archive some of them an only have the
> databases
> that are actually being used.|||Too bad you aren't on sql2005. You could possibly use
sys.dm_db_index_usage_stats, or login triggers for your needs.
--
TheSQLGuru
President
Indicium Resources, Inc.
"spinkid" <spinkid@.discussions.microsoft.com> wrote in message
news:C8A5C8AE-8671-41D7-A4B0-5FF6B3D1EBD3@.microsoft.com...
> We have a Windows 2000 server with SQL 2000 on it and a ton of databases
> (300+). Is there a way to find out which databases are still being used
> (last time accessed) so I can archive some of them an only have the
> databases
> that are actually being used.|||> Too bad you aren't on sql2005. You could possibly use
> sys.dm_db_index_usage_stats, or login triggers for your needs.
For logon triggers, that won't capture things like logging into database A
(or the default database) and running SELECT foo FROM databaseB.dbo.table;|||Hi
In this case you may try to run SQL Server Profiler , so you can order by
database , see BOL for details
"spinkid" <spinkid@.discussions.microsoft.com> wrote in message
news:C8A5C8AE-8671-41D7-A4B0-5FF6B3D1EBD3@.microsoft.com...
> We have a Windows 2000 server with SQL 2000 on it and a ton of databases
> (300+). Is there a way to find out which databases are still being used
> (last time accessed) so I can archive some of them an only have the
> databases
> that are actually being used.
(300+). Is there a way to find out which databases are still being used
(last time accessed) so I can archive some of them an only have the databases
that are actually being used.No, SQL Server doesn't log all access to a database...
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"spinkid" <spinkid@.discussions.microsoft.com> wrote in message
news:C8A5C8AE-8671-41D7-A4B0-5FF6B3D1EBD3@.microsoft.com...
> We have a Windows 2000 server with SQL 2000 on it and a ton of databases
> (300+). Is there a way to find out which databases are still being used
> (last time accessed) so I can archive some of them an only have the
> databases
> that are actually being used.|||Too bad you aren't on sql2005. You could possibly use
sys.dm_db_index_usage_stats, or login triggers for your needs.
--
TheSQLGuru
President
Indicium Resources, Inc.
"spinkid" <spinkid@.discussions.microsoft.com> wrote in message
news:C8A5C8AE-8671-41D7-A4B0-5FF6B3D1EBD3@.microsoft.com...
> We have a Windows 2000 server with SQL 2000 on it and a ton of databases
> (300+). Is there a way to find out which databases are still being used
> (last time accessed) so I can archive some of them an only have the
> databases
> that are actually being used.|||> Too bad you aren't on sql2005. You could possibly use
> sys.dm_db_index_usage_stats, or login triggers for your needs.
For logon triggers, that won't capture things like logging into database A
(or the default database) and running SELECT foo FROM databaseB.dbo.table;|||Hi
In this case you may try to run SQL Server Profiler , so you can order by
database , see BOL for details
"spinkid" <spinkid@.discussions.microsoft.com> wrote in message
news:C8A5C8AE-8671-41D7-A4B0-5FF6B3D1EBD3@.microsoft.com...
> We have a Windows 2000 server with SQL 2000 on it and a ton of databases
> (300+). Is there a way to find out which databases are still being used
> (last time accessed) so I can archive some of them an only have the
> databases
> that are actually being used.
Database last accessed
Hello All,
Is there a way to figure out/track when was the last time/date a
particular database was accessed by users? I am trying to determine
which databases haven't been used/accessed by users in a while so I can
find out if they are still needed or not. My environment is SQL 2000
SP3 with Windows 2000 as the OS. Any help will be appreciated.
Thanks,
Raziq.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi,
First option will be enable Profiler , otherwise write triggers to identify
the last accessed database.
Thanks
Hari
MCDBA
"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:eiZlyPr2DHA.1700@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> Is there a way to figure out/track when was the last time/date a
> particular database was accessed by users? I am trying to determine
> which databases haven't been used/accessed by users in a while so I can
> find out if they are still needed or not. My environment is SQL 2000
> SP3 with Windows 2000 as the OS. Any help will be appreciated.
> Thanks,
> Raziq.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
Is there a way to figure out/track when was the last time/date a
particular database was accessed by users? I am trying to determine
which databases haven't been used/accessed by users in a while so I can
find out if they are still needed or not. My environment is SQL 2000
SP3 with Windows 2000 as the OS. Any help will be appreciated.
Thanks,
Raziq.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi,
First option will be enable Profiler , otherwise write triggers to identify
the last accessed database.
Thanks
Hari
MCDBA
"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:eiZlyPr2DHA.1700@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> Is there a way to figure out/track when was the last time/date a
> particular database was accessed by users? I am trying to determine
> which databases haven't been used/accessed by users in a while so I can
> find out if they are still needed or not. My environment is SQL 2000
> SP3 with Windows 2000 as the OS. Any help will be appreciated.
> Thanks,
> Raziq.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
Database last accessed
We have a Windows 2000 server with SQL 2000 on it and a ton of databases
(300+). Is there a way to find out which databases are still being used
(last time accessed) so I can archive some of them an only have the databases
that are actually being used.
No, SQL Server doesn't log all access to a database...
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"spinkid" <spinkid@.discussions.microsoft.com> wrote in message
news:C8A5C8AE-8671-41D7-A4B0-5FF6B3D1EBD3@.microsoft.com...
> We have a Windows 2000 server with SQL 2000 on it and a ton of databases
> (300+). Is there a way to find out which databases are still being used
> (last time accessed) so I can archive some of them an only have the
> databases
> that are actually being used.
|||Too bad you aren't on sql2005. You could possibly use
sys.dm_db_index_usage_stats, or login triggers for your needs.
TheSQLGuru
President
Indicium Resources, Inc.
"spinkid" <spinkid@.discussions.microsoft.com> wrote in message
news:C8A5C8AE-8671-41D7-A4B0-5FF6B3D1EBD3@.microsoft.com...
> We have a Windows 2000 server with SQL 2000 on it and a ton of databases
> (300+). Is there a way to find out which databases are still being used
> (last time accessed) so I can archive some of them an only have the
> databases
> that are actually being used.
|||> Too bad you aren't on sql2005. You could possibly use
> sys.dm_db_index_usage_stats, or login triggers for your needs.
For logon triggers, that won't capture things like logging into database A
(or the default database) and running SELECT foo FROM databaseB.dbo.table;
|||Hi
In this case you may try to run SQL Server Profiler , so you can order by
database , see BOL for details
"spinkid" <spinkid@.discussions.microsoft.com> wrote in message
news:C8A5C8AE-8671-41D7-A4B0-5FF6B3D1EBD3@.microsoft.com...
> We have a Windows 2000 server with SQL 2000 on it and a ton of databases
> (300+). Is there a way to find out which databases are still being used
> (last time accessed) so I can archive some of them an only have the
> databases
> that are actually being used.
(300+). Is there a way to find out which databases are still being used
(last time accessed) so I can archive some of them an only have the databases
that are actually being used.
No, SQL Server doesn't log all access to a database...
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"spinkid" <spinkid@.discussions.microsoft.com> wrote in message
news:C8A5C8AE-8671-41D7-A4B0-5FF6B3D1EBD3@.microsoft.com...
> We have a Windows 2000 server with SQL 2000 on it and a ton of databases
> (300+). Is there a way to find out which databases are still being used
> (last time accessed) so I can archive some of them an only have the
> databases
> that are actually being used.
|||Too bad you aren't on sql2005. You could possibly use
sys.dm_db_index_usage_stats, or login triggers for your needs.
TheSQLGuru
President
Indicium Resources, Inc.
"spinkid" <spinkid@.discussions.microsoft.com> wrote in message
news:C8A5C8AE-8671-41D7-A4B0-5FF6B3D1EBD3@.microsoft.com...
> We have a Windows 2000 server with SQL 2000 on it and a ton of databases
> (300+). Is there a way to find out which databases are still being used
> (last time accessed) so I can archive some of them an only have the
> databases
> that are actually being used.
|||> Too bad you aren't on sql2005. You could possibly use
> sys.dm_db_index_usage_stats, or login triggers for your needs.
For logon triggers, that won't capture things like logging into database A
(or the default database) and running SELECT foo FROM databaseB.dbo.table;
|||Hi
In this case you may try to run SQL Server Profiler , so you can order by
database , see BOL for details
"spinkid" <spinkid@.discussions.microsoft.com> wrote in message
news:C8A5C8AE-8671-41D7-A4B0-5FF6B3D1EBD3@.microsoft.com...
> We have a Windows 2000 server with SQL 2000 on it and a ton of databases
> (300+). Is there a way to find out which databases are still being used
> (last time accessed) so I can archive some of them an only have the
> databases
> that are actually being used.
Database last accessed
Hello All,
Is there a way to figure out/track when was the last time/date a
particular database was accessed by users? I am trying to determine
which databases haven't been used/accessed by users in a while so I can
find out if they are still needed or not. My environment is SQL 2000
SP3 with Windows 2000 as the OS. Any help will be appreciated.
Thanks,
Raziq.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi,
First option will be enable Profiler , otherwise write triggers to identify
the last accessed database.
Thanks
Hari
MCDBA
"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:eiZlyPr2DHA.1700@.TK2MSFTNGP12.phx.gbl...
that database or not. Isn't there a systable/sysdatabase where this
information is recorded?
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||are you saying where the data collected by profiler is stored? When you run
profiler you can tell it where to save the data, or you can run and save
later.
"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:uvPkRRs2DHA.3416@.tk2msftngp13.phx.gbl...
for a few days to see if anyone had connected to a particular database?
What I am trying to find is a way to figure out when was the last time
someone had accessed this database. It could have been couple of months
ago that someone may have accessed it. It could have been last week,
and they may not access it again for another week. As far as the
profiler is concerned, I'll have to keep it running for a couple of
weeks to see if anyone logged on to a particular database while the
profiler was running. I hope this clarifies what I am trying to do.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!
Is there a way to figure out/track when was the last time/date a
particular database was accessed by users? I am trying to determine
which databases haven't been used/accessed by users in a while so I can
find out if they are still needed or not. My environment is SQL 2000
SP3 with Windows 2000 as the OS. Any help will be appreciated.
Thanks,
Raziq.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi,
First option will be enable Profiler , otherwise write triggers to identify
the last accessed database.
Thanks
Hari
MCDBA
"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:eiZlyPr2DHA.1700@.TK2MSFTNGP12.phx.gbl...
quote:|||I'll have to run profiler for quite some time to see if anyone is using
> Hello All,
> Is there a way to figure out/track when was the last time/date a
> particular database was accessed by users? I am trying to determine
> which databases haven't been used/accessed by users in a while so I can
> find out if they are still needed or not. My environment is SQL 2000
> SP3 with Windows 2000 as the OS. Any help will be appreciated.
> Thanks,
> Raziq.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
that database or not. Isn't there a systable/sysdatabase where this
information is recorded?
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||are you saying where the data collected by profiler is stored? When you run
profiler you can tell it where to save the data, or you can run and save
later.
"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:uvPkRRs2DHA.3416@.tk2msftngp13.phx.gbl...
quote:|||No, what I am saying is that won't I have to keep the profiler running
> I'll have to run profiler for quite some time to see if anyone is using
> that database or not. Isn't there a systable/sysdatabase where this
> information is recorded?
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
for a few days to see if anyone had connected to a particular database?
What I am trying to find is a way to figure out when was the last time
someone had accessed this database. It could have been couple of months
ago that someone may have accessed it. It could have been last week,
and they may not access it again for another week. As far as the
profiler is concerned, I'll have to keep it running for a couple of
weeks to see if anyone logged on to a particular database while the
profiler was running. I hope this clarifies what I am trying to do.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!
Database last accessed
We have a Windows 2000 server with SQL 2000 on it and a ton of databases
(300+). Is there a way to find out which databases are still being used
(last time accessed) so I can archive some of them an only have the database
s
that are actually being used.No, SQL Server doesn't log all access to a database...
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"spinkid" <spinkid@.discussions.microsoft.com> wrote in message
news:C8A5C8AE-8671-41D7-A4B0-5FF6B3D1EBD3@.microsoft.com...
> We have a Windows 2000 server with SQL 2000 on it and a ton of databases
> (300+). Is there a way to find out which databases are still being used
> (last time accessed) so I can archive some of them an only have the
> databases
> that are actually being used.|||Too bad you aren't on sql2005. You could possibly use
sys.dm_db_index_usage_stats, or login triggers for your needs.
TheSQLGuru
President
Indicium Resources, Inc.
"spinkid" <spinkid@.discussions.microsoft.com> wrote in message
news:C8A5C8AE-8671-41D7-A4B0-5FF6B3D1EBD3@.microsoft.com...
> We have a Windows 2000 server with SQL 2000 on it and a ton of databases
> (300+). Is there a way to find out which databases are still being used
> (last time accessed) so I can archive some of them an only have the
> databases
> that are actually being used.|||> Too bad you aren't on sql2005. You could possibly use
> sys.dm_db_index_usage_stats, or login triggers for your needs.
For logon triggers, that won't capture things like logging into database A
(or the default database) and running SELECT foo FROM databaseB.dbo.table;|||Hi
In this case you may try to run SQL Server Profiler , so you can order by
database , see BOL for details
"spinkid" <spinkid@.discussions.microsoft.com> wrote in message
news:C8A5C8AE-8671-41D7-A4B0-5FF6B3D1EBD3@.microsoft.com...
> We have a Windows 2000 server with SQL 2000 on it and a ton of databases
> (300+). Is there a way to find out which databases are still being used
> (last time accessed) so I can archive some of them an only have the
> databases
> that are actually being used.
(300+). Is there a way to find out which databases are still being used
(last time accessed) so I can archive some of them an only have the database
s
that are actually being used.No, SQL Server doesn't log all access to a database...
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"spinkid" <spinkid@.discussions.microsoft.com> wrote in message
news:C8A5C8AE-8671-41D7-A4B0-5FF6B3D1EBD3@.microsoft.com...
> We have a Windows 2000 server with SQL 2000 on it and a ton of databases
> (300+). Is there a way to find out which databases are still being used
> (last time accessed) so I can archive some of them an only have the
> databases
> that are actually being used.|||Too bad you aren't on sql2005. You could possibly use
sys.dm_db_index_usage_stats, or login triggers for your needs.
TheSQLGuru
President
Indicium Resources, Inc.
"spinkid" <spinkid@.discussions.microsoft.com> wrote in message
news:C8A5C8AE-8671-41D7-A4B0-5FF6B3D1EBD3@.microsoft.com...
> We have a Windows 2000 server with SQL 2000 on it and a ton of databases
> (300+). Is there a way to find out which databases are still being used
> (last time accessed) so I can archive some of them an only have the
> databases
> that are actually being used.|||> Too bad you aren't on sql2005. You could possibly use
> sys.dm_db_index_usage_stats, or login triggers for your needs.
For logon triggers, that won't capture things like logging into database A
(or the default database) and running SELECT foo FROM databaseB.dbo.table;|||Hi
In this case you may try to run SQL Server Profiler , so you can order by
database , see BOL for details
"spinkid" <spinkid@.discussions.microsoft.com> wrote in message
news:C8A5C8AE-8671-41D7-A4B0-5FF6B3D1EBD3@.microsoft.com...
> We have a Windows 2000 server with SQL 2000 on it and a ton of databases
> (300+). Is there a way to find out which databases are still being used
> (last time accessed) so I can archive some of them an only have the
> databases
> that are actually being used.
Subscribe to:
Posts (Atom)