DBCC CHECKDB: mdf

Showing posts with label mdf. Show all posts

Monday, March 19, 2012

db restore question

Hi all, is it possible to restore a database to another server with only the
..mdf & the .ldf files? Unfortunately I dont have a backup file, only the
data files itself. Also another thing is the data files are from sql2k and
the new server is sql2k sp3a. Am i SOL?
TIA!
If you are lucky, or you explicitly detached then first, you can use sp_attach_db to attach the
database files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RP" <rp@.nospam.com> wrote in message news:ue9YVIXqEHA.708@.tk2msftngp13.phx.gbl...
> Hi all, is it possible to restore a database to another server with only the
> .mdf & the .ldf files? Unfortunately I dont have a backup file, only the
> data files itself. Also another thing is the data files are from sql2k and
> the new server is sql2k sp3a. Am i SOL?
> TIA!
>
|||You can try to use sp_attach_db to attach the files but this might not work
if the database wasn't cleanly detached using sp_detach_db. See the Books
Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"RP" <rp@.nospam.com> wrote in message
news:ue9YVIXqEHA.708@.tk2msftngp13.phx.gbl...
> Hi all, is it possible to restore a database to another server with only
> the
> .mdf & the .ldf files? Unfortunately I dont have a backup file, only the
> data files itself. Also another thing is the data files are from sql2k and
> the new server is sql2k sp3a. Am i SOL?
> TIA!
>

db restore question

Hi all, is it possible to restore a database to another server with only the
.mdf & the .ldf files? Unfortunately I dont have a backup file, only the
data files itself. Also another thing is the data files are from sql2k and
the new server is sql2k sp3a. Am i SOL?
TIA!If you are lucky, or you explicitly detached then first, you can use sp_attach_db to attach the
database files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RP" <rp@.nospam.com> wrote in message news:ue9YVIXqEHA.708@.tk2msftngp13.phx.gbl...
> Hi all, is it possible to restore a database to another server with only the
> .mdf & the .ldf files? Unfortunately I dont have a backup file, only the
> data files itself. Also another thing is the data files are from sql2k and
> the new server is sql2k sp3a. Am i SOL?
> TIA!
>|||You can try to use sp_attach_db to attach the files but this might not work
if the database wasn't cleanly detached using sp_detach_db. See the Books
Online for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"RP" <rp@.nospam.com> wrote in message
news:ue9YVIXqEHA.708@.tk2msftngp13.phx.gbl...
> Hi all, is it possible to restore a database to another server with only
> the
> .mdf & the .ldf files? Unfortunately I dont have a backup file, only the
> data files itself. Also another thing is the data files are from sql2k and
> the new server is sql2k sp3a. Am i SOL?
> TIA!
>|||Hi!
As for your last problem. That will be no problem.
As for attach/detach procedure:
You will get the database with all data but you will get no users that reside in master. You have to create them and mapp them using following script:
--Run this in the faulty database
--Username is the databaseusername
--Loginname is the user in Security
sp_change_users_login 'Update_one', '<username>','<Loginna
me>'
If you database wasn=B4t cleanly closed you can not attach it directly. But that doesn=B4t make the database lost forever.
Do the following steps and you are homefree:
1. Reconfigure server to allow ad-hoc updates.
2. Create the database and set the physical names as those in the one you are restoring. 3. Close SQL-server and delete the .ldf file
4. Start SQL-server, the database is now in suspect mode.
5. Set the database to emergencymode
6. Activate traceflag 3604
7. Run DBCC Rebuild_log
8. Reset the database to normal operations.
9. Run DBCC Checkdb and correct any errornes indexes.
If you need further help drop me a mail.
Jesper Kr=E5khede, Capgemini
>--Original Message--
>Hi all, is it possible to restore a database to another server with only the
>..mdf & the .ldf files? Unfortunately I dont have a backup file, only the
>data files itself. Also another thing is the data files are from sql2k and
>the new server is sql2k sp3a. Am i SOL?
>TIA!
>
>.
>|||Note that rebuilding the log will compromise both logical and physical
database integrity. If RP can't successfully attach the database and the
data are important, I suggest he or she contact Microsoft PSS.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jesper Kråkhede Capgemini" <Jesper.krakhede@.capgemini.com> wrote in message
news:2fde01c4a979$588ed870$a401280a@.phx.gbl...
Hi!
As for your last problem. That will be no problem.
As for attach/detach procedure:
You will get the database with all data but you will get
no users that reside in master. You have to create them
and mapp them using following script:
--Run this in the faulty database
--Username is the databaseusername
--Loginname is the user in Security
sp_change_users_login 'Update_one', '<username>','<Loginna
me>'
If you database wasn´t cleanly closed you can not attach
it directly. But that doesn´t make the database lost
forever.
Do the following steps and you are homefree:
1. Reconfigure server to allow ad-hoc updates.
2. Create the database and set the physical names as
those in the one you are restoring.
3. Close SQL-server and delete the .ldf file
4. Start SQL-server, the database is now in suspect mode.
5. Set the database to emergencymode
6. Activate traceflag 3604
7. Run DBCC Rebuild_log
8. Reset the database to normal operations.
9. Run DBCC Checkdb and correct any errornes indexes.
If you need further help drop me a mail.
Jesper Kråkhede, Capgemini
>--Original Message--
>Hi all, is it possible to restore a database to another
server with only the
>..mdf & the .ldf files? Unfortunately I dont have a
backup file, only the
>data files itself. Also another thing is the data files
are from sql2k and
>the new server is sql2k sp3a. Am i SOL?
>TIA!
>
>.
>|||Thats correct. I did that once and this was the solution they gave me. Worked
as a charm but running DBCC CheckDB was needed afterwards.
J
"Dan Guzman" wrote:
> Note that rebuilding the log will compromise both logical and physical
> database integrity. If RP can't successfully attach the database and the
> data are important, I suggest he or she contact Microsoft PSS.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jesper KrÃ¥khede Capgemini" <Jesper.krakhede@.capgemini.com> wrote in message
> news:2fde01c4a979$588ed870$a401280a@.phx.gbl...
> Hi!
> As for your last problem. That will be no problem.
> As for attach/detach procedure:
> You will get the database with all data but you will get
> no users that reside in master. You have to create them
> and mapp them using following script:
> --Run this in the faulty database
> --Username is the databaseusername
> --Loginname is the user in Security
> sp_change_users_login 'Update_one', '<username>','<Loginna
> me>'
> If you database wasnÂ´t cleanly closed you can not attach
> it directly. But that doesnÂ´t make the database lost
> forever.
> Do the following steps and you are homefree:
> 1. Reconfigure server to allow ad-hoc updates.
> 2. Create the database and set the physical names as
> those in the one you are restoring.
> 3. Close SQL-server and delete the .ldf file
> 4. Start SQL-server, the database is now in suspect mode.
> 5. Set the database to emergencymode
> 6. Activate traceflag 3604
> 7. Run DBCC Rebuild_log
> 8. Reset the database to normal operations.
> 9. Run DBCC Checkdb and correct any errornes indexes.
> If you need further help drop me a mail.
> Jesper KrÃ¥khede, Capgemini
>
> >--Original Message--
> >Hi all, is it possible to restore a database to another
> server with only the
> >..mdf & the .ldf files? Unfortunately I dont have a
> backup file, only the
> >data files itself. Also another thing is the data files
> are from sql2k and
> >the new server is sql2k sp3a. Am i SOL?
> >
> >TIA!
> >
> >
> >.
> >
>
>

Sunday, March 11, 2012

db recovery

hi,
i have a failure on a server controller and i have lost some volumes where
logs and index files of a db so now i have only the db (.mdf file) and none
backup.
SQL versione 7.0 sp3
Anyone can tell me if is it possible to do something to recover the db?
thanks
VTOf course, the first response is to use your backups. (If only the world
were so predictible.)
The following may 'save' the database (at least what is in the mdf file),
but it may be in a severe inconsistant state. And I understand that it may
be all you have. (Make a copy of the mdf file before doing anything.)
If you have only the mdf file, you should be able to detach the db, and then
re-attach the db. If you don't provide a log file, you will get a warning
that a new log file will be created. (Caveat: I can't remember how it works
in SQL v7, but these steps should work in SQL v2000.)
If this successfully attaches, you should rebuild the indexes. And then
thoroughly investigate the state of your data. You will have lost all data
changes that were in the log file but not yet written to disk. You will have
lost all incomplete transaction activity.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"VT" <VT@.discussions.microsoft.com> wrote in message
news:04F7C77A-5D3F-4019-A067-A3C852B8C468@.microsoft.com...
> hi,
> i have a failure on a server controller and i have lost some volumes where
> logs and index files of a db so now i have only the db (.mdf file) and
> none
> backup.
> SQL versione 7.0 sp3
> Anyone can tell me if is it possible to do something to recover the db?
> thanks
> VT|||And if you are very, very lucky, you will not have lost your job for not
having current backups. ;-)
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OHvVsnsqGHA.5108@.TK2MSFTNGP05.phx.gbl...
> Of course, the first response is to use your backups. (If only the world
> were so predictible.)
> The following may 'save' the database (at least what is in the mdf file),
> but it may be in a severe inconsistant state. And I understand that it may
> be all you have. (Make a copy of the mdf file before doing anything.)
> If you have only the mdf file, you should be able to detach the db, and
> then re-attach the db. If you don't provide a log file, you will get a
> warning that a new log file will be created. (Caveat: I can't remember how
> it works in SQL v7, but these steps should work in SQL v2000.)
> If this successfully attaches, you should rebuild the indexes. And then
> thoroughly investigate the state of your data. You will have lost all data
> changes that were in the log file but not yet written to disk. You will
> have lost all incomplete transaction activity.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "VT" <VT@.discussions.microsoft.com> wrote in message
> news:04F7C77A-5D3F-4019-A067-A3C852B8C468@.microsoft.com...
>> hi,
>> i have a failure on a server controller and i have lost some volumes
>> where
>> logs and index files of a db so now i have only the db (.mdf file) and
>> none
>> backup.
>> SQL versione 7.0 sp3
>> Anyone can tell me if is it possible to do something to recover the db?
>> thanks
>> VT
>|||don't warry for my job... server is of a customer...
i don't know how was configured the db when it was ok.
i tried to attach the db using thhe only file i have available:
EXEC sp_attach_db @.dbname = 'test-prod', @.filename1 ='w:\databases\JDE_PRODUCTION.mdf'
server response was:
Changed language setting to us_english.
Server: Msg 5105, Level 16, State 5, Line 1
Device activation error. The physical file name
'd:\databases\JDE_PRODUCTION_INDICI.NDF' may be incorrect.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name
'd:\databases\JDE_PRODUCTION_INDICI2.NDF' may be incorrect.
Server: Msg 945, Level 14, State 1, Line 1
Database 'test-prod' cannot be opened because some of the files could not be
activated.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'test-prod'. CREATE DATABASE is aborted.
any good idea?
"Arnie Rowland" wrote:
> And if you are very, very lucky, you will not have lost your job for not
> having current backups. ;-)
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OHvVsnsqGHA.5108@.TK2MSFTNGP05.phx.gbl...
> > Of course, the first response is to use your backups. (If only the world
> > were so predictible.)
> >
> > The following may 'save' the database (at least what is in the mdf file),
> > but it may be in a severe inconsistant state. And I understand that it may
> > be all you have. (Make a copy of the mdf file before doing anything.)
> >
> > If you have only the mdf file, you should be able to detach the db, and
> > then re-attach the db. If you don't provide a log file, you will get a
> > warning that a new log file will be created. (Caveat: I can't remember how
> > it works in SQL v7, but these steps should work in SQL v2000.)
> >
> > If this successfully attaches, you should rebuild the indexes. And then
> > thoroughly investigate the state of your data. You will have lost all data
> > changes that were in the log file but not yet written to disk. You will
> > have lost all incomplete transaction activity.
> >
> > --
> > Arnie Rowland
> > Most good judgment comes from experience.
> > Most experience comes from bad judgment.
> > - Anonymous
> >
> >
> > "VT" <VT@.discussions.microsoft.com> wrote in message
> > news:04F7C77A-5D3F-4019-A067-A3C852B8C468@.microsoft.com...
> >> hi,
> >> i have a failure on a server controller and i have lost some volumes
> >> where
> >> logs and index files of a db so now i have only the db (.mdf file) and
> >> none
> >> backup.
> >> SQL versione 7.0 sp3
> >>
> >> Anyone can tell me if is it possible to do something to recover the db?
> >> thanks
> >> VT
> >
> >
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0759_01C6AA94.780541D0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
Do you have the ndf files? If you don't have the ndf files, you may have =lost significant amounts of data.
This link may provide some useful information.
http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.=
html
Always make backup copies of your files before attempting any recovery =techniques whether documented or undocumented.
If that doesn't help, you may need to open a case with PSS.
-- Arnie Rowland
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"VT" <VT@.discussions.microsoft.com> wrote in message =news:E0A8B29D-227F-4782-8ED3-1884DBDB737D@.microsoft.com...
> don't warry for my job... server is of a customer...
> > i don't know how was configured the db when it was ok.
> > i tried to attach the db using thhe only file i have available:
> EXEC sp_attach_db @.dbname =3D 'test-prod', @.filename1 =3D > 'w:\databases\JDE_PRODUCTION.mdf'
> > server response was:
> Changed language setting to us_english.
> Server: Msg 5105, Level 16, State 5, Line 1
> Device activation error. The physical file name > 'd:\databases\JDE_PRODUCTION_INDICI.NDF' may be incorrect.
> Server: Msg 5105, Level 16, State 1, Line 1
> Device activation error. The physical file name > 'd:\databases\JDE_PRODUCTION_INDICI2.NDF' may be incorrect.
> Server: Msg 945, Level 14, State 1, Line 1
> Database 'test-prod' cannot be opened because some of the files could =not be > activated.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'test-prod'. CREATE DATABASE is aborted.
> > any good idea?
> > > > > "Arnie Rowland" wrote:
> >> And if you are very, very lucky, you will not have lost your job for =not >> having current backups. ;-)
>> >> -- >> Arnie Rowland
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>> >> >> "Arnie Rowland" <arnie@.1568.com> wrote in message >> news:OHvVsnsqGHA.5108@.TK2MSFTNGP05.phx.gbl...
>> > Of course, the first response is to use your backups. (If only the =world >> > were so predictible.)
>> >
>> > The following may 'save' the database (at least what is in the mdf =file), >> > but it may be in a severe inconsistant state. And I understand that =it may >> > be all you have. (Make a copy of the mdf file before doing =anything.)
>> >
>> > If you have only the mdf file, you should be able to detach the db, =and >> > then re-attach the db. If you don't provide a log file, you will =get a >> > warning that a new log file will be created. (Caveat: I can't =remember how >> > it works in SQL v7, but these steps should work in SQL v2000.)
>> >
>> > If this successfully attaches, you should rebuild the indexes. And =then >> > thoroughly investigate the state of your data. You will have lost =all data >> > changes that were in the log file but not yet written to disk. You =will >> > have lost all incomplete transaction activity.
>> >
>> > -- >> > Arnie Rowland
>> > Most good judgment comes from experience.
>> > Most experience comes from bad judgment.
>> > - Anonymous
>> >
>> >
>> > "VT" <VT@.discussions.microsoft.com> wrote in message >> > news:04F7C77A-5D3F-4019-A067-A3C852B8C468@.microsoft.com...
>> >> hi,
>> >> i have a failure on a server controller and i have lost some =volumes >> >> where
>> >> logs and index files of a db so now i have only the db (.mdf =file) and >> >> none
>> >> backup.
>> >> SQL versione 7.0 sp3
>> >>
>> >> Anyone can tell me if is it possible to do something to recover =the db?
>> >> thanks
>> >> VT
>> >
>> > >> >> --=_NextPart_000_0759_01C6AA94.780541D0
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Do you have the ndf files? If you don't =have the ndf files, you may have lost significant amounts of data.
This link may provide some useful =information.
http://www.spaceprogram.com/knowledge/sqlserver_recover_fro=m_deleted_log.html
Always make backup copies of your files =before attempting any recovery techniques whether documented or undocumented.
If that doesn't help, you may need to =open a case with PSS.
-- Arnie RowlandMost good =judgment comes from experience. Most experience comes from bad judgment. =- Anonymous
"VT" wrote in message news:E0A8B29D-227F-4782-8ED3-1884DBDB737D@.microsoft.com...> =don't warry for my job... server is of a customer...> > i don't know how =was configured the db when it was ok.> > i tried to attach the =db using thhe only file i have available:> EXEC sp_attach_db @.dbname ==3D 'test-prod', @.filename1 =3D > ='w:\databases\JDE_PRODUCTION.mdf'> > server response was:> Changed language setting to us_english.> Server: Msg 5105, Level 16, State 5, Line 1> =Device activation error. The physical file name > 'd:\databases\JDE_PRODUCTION_INDICI.NDF' may be incorrect.> =Server: Msg 5105, Level 16, State 1, Line 1> Device activation error. The =physical file name > 'd:\databases\JDE_PRODUCTION_INDICI2.NDF' may be incorrect.> Server: Msg 945, Level 14, State 1, Line 1> =Database 'test-prod' cannot be opened because some of the files could not be => activated.> Server: Msg 1813, Level 16, State 1, Line 1> =Could not open new database 'test-prod'. CREATE DATABASE is aborted.> => any good idea?> > > > > "Arnie Rowland" wrote:> > And if you are very, very lucky, you will =not have lost your job for not > having current backups. =;-)> > -- > Arnie Rowland> Most good =judgment comes from experience.> Most experience comes from bad judgment.> - Anonymous> > > ="Arnie Rowland" wrote =in message > news:OHvVsnsqGHA.5108@.TK2MSFTNGP05.phx.gbl...> > Of course, the first response =is to use your backups. (If only the world > > were so predictible.)> >> > The following may 'save' =the database (at least what is in the mdf file), > > but it =may be in a severe inconsistant state. And I understand that it may > => be all you have. (Make a copy of the mdf file before doing =anything.)> >> > If you have only the mdf file, you should be able =to detach the db, and > > then re-attach the db. If you don't =provide a log file, you will get a > > warning that a new log file =will be created. (Caveat: I can't remember how > > it works in SQL =v7, but these steps should work in SQL v2000.)> >> > =If this successfully attaches, you should rebuild the indexes. And then => > thoroughly investigate the state of your data. You will have lost =all data > > changes that were in the log file but not yet written =to disk. You will > > have lost all incomplete transaction activity.> >> > -- > > Arnie Rowland> > Most good judgment comes from =experience.> > Most experience comes from bad judgment.> > - Anonymous> >> >> > "VT" = wrote in message > > news:04F7C77A-5D3F-4019-A067-A3C852B8C468@.microsoft.com...> >> hi,> >> =i have a failure on a server controller and i have lost some volumes > =>> where> >> logs and index files of a db so now =i have only the db (.mdf file) and > >> none> =>> backup.> >> SQL versione 7.0 sp3> >>> >> Anyone can tell me if is it possible to do =something to recover the db?> >> thanks> =>> VT> >> > > > >

--=_NextPart_000_0759_01C6AA94.780541D0--

Friday, February 17, 2012

DB Last Access Times

Hi All,
Would it be safe to use the last modified date of a .MDF file within Windows
Explorer to view the last time anyone utilised a database? I'm trying to
clear unused DB's from a server and am wondering if this is a reliable
method of finding unused data?
Many thanks in advance,
Orb.Hi
I'd run SQL Server Profiler to collect the info per database and then make
a conclusions. BTW , what version are you using?
"Orbital" <sian.clarke@.newhamhealth.nhs.uk> wrote in message
news:O60yZ6IXGHA.4148@.TK2MSFTNGP03.phx.gbl...
> Hi All,
> Would it be safe to use the last modified date of a .MDF file within
> Windows Explorer to view the last time anyone utilised a database? I'm
> trying to clear unused DB's from a server and am wondering if this is a
> reliable method of finding unused data?
>
> Many thanks in advance,
> Orb.
>|||Hi Uri,
Thanks for your rapid response!
I'm on SQL2000 right now.
The SQL Server Profiler you mention, is this an MS tool? If you would
kindly point me to any documentation/ resources I'd be really grateful!
Many Thanks in advance,
Orb.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ePtdY8IXGHA.2356@.TK2MSFTNGP03.phx.gbl...
> Hi
> I'd run SQL Server Profiler to collect the info per database and then
> make a conclusions. BTW , what version are you using?
>
>
> "Orbital" <sian.clarke@.newhamhealth.nhs.uk> wrote in message
> news:O60yZ6IXGHA.4148@.TK2MSFTNGP03.phx.gbl...
>> Hi All,
>> Would it be safe to use the last modified date of a .MDF file within
>> Windows Explorer to view the last time anyone utilised a database? I'm
>> trying to clear unused DB's from a server and am wondering if this is a
>> reliable method of finding unused data?
>>
>> Many thanks in advance,
>> Orb.
>|||> The SQL Server Profiler you mention, is this an MS tool?
Yes ,it is
> kindly point me to any documentation/ resources I'd be really grateful!
Actually , take a look at BOL for some explanations as well as visiting at
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
"Orbital" <sian.clarke@.newhamhealth.nhs.uk> wrote in message
news:ecJRPAJXGHA.3760@.TK2MSFTNGP02.phx.gbl...
> Hi Uri,
> Thanks for your rapid response!
> I'm on SQL2000 right now.
> The SQL Server Profiler you mention, is this an MS tool? If you would
> kindly point me to any documentation/ resources I'd be really grateful!
>
> Many Thanks in advance,
> Orb.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ePtdY8IXGHA.2356@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> I'd run SQL Server Profiler to collect the info per database and then
>> make a conclusions. BTW , what version are you using?
>>
>>
>> "Orbital" <sian.clarke@.newhamhealth.nhs.uk> wrote in message
>> news:O60yZ6IXGHA.4148@.TK2MSFTNGP03.phx.gbl...
>> Hi All,
>> Would it be safe to use the last modified date of a .MDF file within
>> Windows Explorer to view the last time anyone utilised a database? I'm
>> trying to clear unused DB's from a server and am wondering if this is a
>> reliable method of finding unused data?
>>
>> Many thanks in advance,
>> Orb.
>>
>|||"Orbital" <sian.clarke@.newhamhealth.nhs.uk> wrote in message
news:O60yZ6IXGHA.4148@.TK2MSFTNGP03.phx.gbl...
> Hi All,
> Would it be safe to use the last modified date of a .MDF file within
Windows
> Explorer to view the last time anyone utilised a database? I'm trying to
> clear unused DB's from a server and am wondering if this is a reliable
> method of finding unused data?
No, absolutely not.
In general the last modified date only shows the last time the server
started up or shut down or the size changed.
While in use, it generally doesn't change.
Now, you can try renaming the file, and it will fail if in use, but that's
not an entirely safe way to do things.
>
> Many thanks in advance,
> Orb.
>|||Uri,
This is superb, thanks for making me aware of this utility.
Many Thanks,
Orb.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23dgh4OJXGHA.4924@.TK2MSFTNGP05.phx.gbl...
>> The SQL Server Profiler you mention, is this an MS tool?
> Yes ,it is
>> kindly point me to any documentation/ resources I'd be really grateful!
> Actually , take a look at BOL for some explanations as well as visiting
> at
> http://www.sql-server-performance.com/sql_server_profiler_tips.asp
>
>
>
> "Orbital" <sian.clarke@.newhamhealth.nhs.uk> wrote in message
> news:ecJRPAJXGHA.3760@.TK2MSFTNGP02.phx.gbl...
>> Hi Uri,
>> Thanks for your rapid response!
>> I'm on SQL2000 right now.
>> The SQL Server Profiler you mention, is this an MS tool? If you would
>> kindly point me to any documentation/ resources I'd be really grateful!
>>
>> Many Thanks in advance,
>> Orb.
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:ePtdY8IXGHA.2356@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> I'd run SQL Server Profiler to collect the info per database and then
>> make a conclusions. BTW , what version are you using?
>>
>>
>> "Orbital" <sian.clarke@.newhamhealth.nhs.uk> wrote in message
>> news:O60yZ6IXGHA.4148@.TK2MSFTNGP03.phx.gbl...
>> Hi All,
>> Would it be safe to use the last modified date of a .MDF file within
>> Windows Explorer to view the last time anyone utilised a database? I'm
>> trying to clear unused DB's from a server and am wondering if this is a
>> reliable method of finding unused data?
>>
>> Many thanks in advance,
>> Orb.
>>
>>
>|||Hello Orbital,
I had exactly the same issue -- multiple databases and many of them
suspected of no usage.
I use Profiler, and monitor locks on the suspected databases. The idea behid
this is actually every access to Db is some kind of lock.
"Greg D. Moore (Strider)" wrote:
> "Orbital" <sian.clarke@.newhamhealth.nhs.uk> wrote in message
> news:O60yZ6IXGHA.4148@.TK2MSFTNGP03.phx.gbl...
> > Hi All,
> >
> > Would it be safe to use the last modified date of a .MDF file within
> Windows
> > Explorer to view the last time anyone utilised a database? I'm trying to
> > clear unused DB's from a server and am wondering if this is a reliable
> > method of finding unused data?
> No, absolutely not.
> In general the last modified date only shows the last time the server
> started up or shut down or the size changed.
> While in use, it generally doesn't change.
> Now, you can try renaming the file, and it will fail if in use, but that's
> not an entirely safe way to do things.
>
> >
> >
> > Many thanks in advance,
> > Orb.
> >
> >
>
>|||Thanks for your reply Strider. I've had a go at renaming what look to be
old databases - I think October 2003 datestamps should be safe!- and will
reinstate if anyone screams!
Thanks for you help,
Orb.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23p9L0iJXGHA.1192@.TK2MSFTNGP03.phx.gbl...
> "Orbital" <sian.clarke@.newhamhealth.nhs.uk> wrote in message
> news:O60yZ6IXGHA.4148@.TK2MSFTNGP03.phx.gbl...
>> Hi All,
>> Would it be safe to use the last modified date of a .MDF file within
> Windows
>> Explorer to view the last time anyone utilised a database? I'm trying to
>> clear unused DB's from a server and am wondering if this is a reliable
>> method of finding unused data?
> No, absolutely not.
> In general the last modified date only shows the last time the server
> started up or shut down or the size changed.
> While in use, it generally doesn't change.
> Now, you can try renaming the file, and it will fail if in use, but that's
> not an entirely safe way to do things.
>
>>
>> Many thanks in advance,
>> Orb.
>>
>|||Hi Gary,
Thanks for your reply. I've just ran a quick trace and this is just what
I'm looking for!. My only problem now is relating the DatabaseID to the
actual DB I see in my SQL Enterprise Manager, as the DatabaseName column
somehow remains empty? Does anyone know how I match the two together?
Many Thanks,
Orb.
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:EDF55C4E-9EDE-4D7E-86E2-8BFCD26806AE@.microsoft.com...
> Hello Orbital,
> I had exactly the same issue -- multiple databases and many of them
> suspected of no usage.
> I use Profiler, and monitor locks on the suspected databases. The idea
> behid
> this is actually every access to Db is some kind of lock.
> "Greg D. Moore (Strider)" wrote:
>> "Orbital" <sian.clarke@.newhamhealth.nhs.uk> wrote in message
>> news:O60yZ6IXGHA.4148@.TK2MSFTNGP03.phx.gbl...
>> > Hi All,
>> >
>> > Would it be safe to use the last modified date of a .MDF file within
>> Windows
>> > Explorer to view the last time anyone utilised a database? I'm trying
>> > to
>> > clear unused DB's from a server and am wondering if this is a reliable
>> > method of finding unused data?
>> No, absolutely not.
>> In general the last modified date only shows the last time the server
>> started up or shut down or the size changed.
>> While in use, it generally doesn't change.
>> Now, you can try renaming the file, and it will fail if in use, but
>> that's
>> not an entirely safe way to do things.
>>
>> >
>> >
>> > Many thanks in advance,
>> > Orb.
>> >
>> >
>>|||select name from master..sysdatabases where dbid = '
--
HTH. Ryan
"Orbital" <sian.clarke@.newhamhealth.nhs.uk> wrote in message
news:uicVQbKXGHA.5096@.TK2MSFTNGP03.phx.gbl...
> Hi Gary,
> Thanks for your reply. I've just ran a quick trace and this is just what
> I'm looking for!. My only problem now is relating the DatabaseID to the
> actual DB I see in my SQL Enterprise Manager, as the DatabaseName column
> somehow remains empty? Does anyone know how I match the two together?
>
> Many Thanks,
> Orb.
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:EDF55C4E-9EDE-4D7E-86E2-8BFCD26806AE@.microsoft.com...
>> Hello Orbital,
>> I had exactly the same issue -- multiple databases and many of them
>> suspected of no usage.
>> I use Profiler, and monitor locks on the suspected databases. The idea
>> behid
>> this is actually every access to Db is some kind of lock.
>> "Greg D. Moore (Strider)" wrote:
>>
>> "Orbital" <sian.clarke@.newhamhealth.nhs.uk> wrote in message
>> news:O60yZ6IXGHA.4148@.TK2MSFTNGP03.phx.gbl...
>> > Hi All,
>> >
>> > Would it be safe to use the last modified date of a .MDF file within
>> Windows
>> > Explorer to view the last time anyone utilised a database? I'm trying
>> > to
>> > clear unused DB's from a server and am wondering if this is a reliable
>> > method of finding unused data?
>> No, absolutely not.
>> In general the last modified date only shows the last time the server
>> started up or shut down or the size changed.
>> While in use, it generally doesn't change.
>> Now, you can try renaming the file, and it will fail if in use, but
>> that's
>> not an entirely safe way to do things.
>>
>> >
>> >
>> > Many thanks in advance,
>> > Orb.
>> >
>> >
>>
>|||Thanks Ryan :o)
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23IU6ngKXGHA.1228@.TK2MSFTNGP02.phx.gbl...
> select name from master..sysdatabases where dbid = '
> --
> HTH. Ryan
>
> "Orbital" <sian.clarke@.newhamhealth.nhs.uk> wrote in message
> news:uicVQbKXGHA.5096@.TK2MSFTNGP03.phx.gbl...
>> Hi Gary,
>> Thanks for your reply. I've just ran a quick trace and this is just what
>> I'm looking for!. My only problem now is relating the DatabaseID to the
>> actual DB I see in my SQL Enterprise Manager, as the DatabaseName column
>> somehow remains empty? Does anyone know how I match the two together?
>>
>> Many Thanks,
>> Orb.
>> "Gary" <Gary@.discussions.microsoft.com> wrote in message
>> news:EDF55C4E-9EDE-4D7E-86E2-8BFCD26806AE@.microsoft.com...
>> Hello Orbital,
>> I had exactly the same issue -- multiple databases and many of them
>> suspected of no usage.
>> I use Profiler, and monitor locks on the suspected databases. The idea
>> behid
>> this is actually every access to Db is some kind of lock.
>> "Greg D. Moore (Strider)" wrote:
>>
>> "Orbital" <sian.clarke@.newhamhealth.nhs.uk> wrote in message
>> news:O60yZ6IXGHA.4148@.TK2MSFTNGP03.phx.gbl...
>> > Hi All,
>> >
>> > Would it be safe to use the last modified date of a .MDF file within
>> Windows
>> > Explorer to view the last time anyone utilised a database? I'm
>> > trying to
>> > clear unused DB's from a server and am wondering if this is a
>> > reliable
>> > method of finding unused data?
>> No, absolutely not.
>> In general the last modified date only shows the last time the server
>> started up or shut down or the size changed.
>> While in use, it generally doesn't change.
>> Now, you can try renaming the file, and it will fail if in use, but
>> that's
>> not an entirely safe way to do things.
>>
>> >
>> >
>> > Many thanks in advance,
>> > Orb.
>> >
>> >
>>
>>
>|||"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23p9L0iJXGHA.1192@.TK2MSFTNGP03.phx.gbl...
> "Orbital" <sian.clarke@.newhamhealth.nhs.uk> wrote in message
> news:O60yZ6IXGHA.4148@.TK2MSFTNGP03.phx.gbl...
>> Hi All,
>> Would it be safe to use the last modified date of a .MDF file within
> Windows
>> Explorer to view the last time anyone utilised a database? I'm trying to
>> clear unused DB's from a server and am wondering if this is a reliable
>> method of finding unused data?
> No, absolutely not.
> In general the last modified date only shows the last time the server
> started up or shut down or the size changed.
> While in use, it generally doesn't change.
> Now, you can try renaming the file, and it will fail if in use, but that's
> not an entirely safe way to do things.
If you set the database to autoclose, the time on the file will indeed
change even though the server was not re-started.
sp_dboption FooDb , autoclose, false

DB increased the size

Hello...Can someone help me please?

I had a mdf file with 48 Mb...and suddenly, the size of it increased
to 690 Mb (without any action or movement of data)...

Why could it be done?...is there any solution for thar?

Thank you.

Tony"Tony" <toni.gomez@.aidico.es> wrote in message
news:177dfda5.0405110012.199c71d0@.posting.google.c om...
> Hello...Can someone help me please?
> I had a mdf file with 48 Mb...and suddenly, the size of it increased
> to 690 Mb (without any action or movement of data)...
> Why could it be done?...is there any solution for thar?
> Thank you.
> Tony

Is it possible that someone else increased the file size manually? Or if the
file is set to autogrow, could someone have INSERTed then DELETed a large
amount of data? Or did someone create a very large number of indexes
(unlikely)? It can be normal that the .ldf file grows a lot in some
situations, but the .mdf should not unless there really are extra data or
objects in the database.

Simon

Monday, March 19, 2012

db restore question

db restore question

Sunday, March 11, 2012

db recovery

db recovery

Friday, February 17, 2012

DB Last Access Times

DB Last Access Times

DB increased the size

DBCC CHECKDB

Blog Archive

About Me