Database growing unexpectedly

Hi all,
Running sql2000 ee SP4 on win2003.
I have a small database which havent been inserted/updated since it was
created 6 months ago. No DDL changes. This database was about 1,5 Mb for
the first months, but have started to grow. The size is now 30 Mb and it
is growing with about 2 Mb a month. It is set to autogrow with 10%.
Can someone explain this growth?
name rows reserved
form_lang 1 56 KB
grid_expr 0 0 KB
GridViewCategory 0 0 KB
GridViewDefinition 0 0 KB
hierarchy_tables 0 0 KB
invitation 0 0 KB
iteration 1 24 KB
lookup 0 0 KB
lookup_lang 0 0 KB
PanelistCredit 0 0 KB
quota 1 56 KB
response_control 2 104 KB
response1 0 0 KB
SampleLog 0 0 KB
weightmodel 0 0 KB
weightmodelform 0 0 KBTry running DBCC UPDATEUSAGE(0) and report back the space used.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns977AEC59B7F29gurbaohotmailcom@.12
9.250.171.65...
Hi all,
Running sql2000 ee SP4 on win2003.
I have a small database which havent been inserted/updated since it was
created 6 months ago. No DDL changes. This database was about 1,5 Mb for
the first months, but have started to grow. The size is now 30 Mb and it
is growing with about 2 Mb a month. It is set to autogrow with 10%.
Can someone explain this growth?
name rows reserved
form_lang 1 56 KB
grid_expr 0 0 KB
GridViewCategory 0 0 KB
GridViewDefinition 0 0 KB
hierarchy_tables 0 0 KB
invitation 0 0 KB
iteration 1 24 KB
lookup 0 0 KB
lookup_lang 0 0 KB
PanelistCredit 0 0 KB
quota 1 56 KB
response_control 2 104 KB
response1 0 0 KB
SampleLog 0 0 KB
weightmodel 0 0 KB
weightmodelform 0 0 KB|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in news:#noh#6kPGHA.1532
@.TK2MSFTNGP12.phx.gbl:

> Try running DBCC UPDATEUSAGE(0) and report back the space used.
>
Hi,
seems the first output from
USE MyDb; EXEC sp_MSforeachtable 'EXEC sp_spaceused [?]'
was missing some tables...
I ran UpdateUsage and the sp_spaceused again and found the reason for the
growth.
Case closed :-)
Thanks,

Database growing unexpectedly

Hi all,
Running sql2000 ee SP4 on win2003.
I have a small database which havent been inserted/updated since it was
created 6 months ago. No DDL changes. This database was about 1,5 Mb for
the first months, but have started to grow. The size is now 30 Mb and it
is growing with about 2 Mb a month. It is set to autogrow with 10%.
Can someone explain this growth?
name rows reserved
form_lang 1 56 KB
grid_expr 0 0 KB
GridViewCategory 0 0 KB
GridViewDefinition 0 0 KB
hierarchy_tables 0 0 KB
invitation 0 0 KB
iteration 1 24 KB
lookup 0 0 KB
lookup_lang 0 0 KB
PanelistCredit 0 0 KB
quota 1 56 KB
response_control 2 104 KB
response1 0 0 KB
SampleLog 0 0 KB
weightmodel 0 0 KB
weightmodelform 0 0 KB
Try running DBCC UPDATEUSAGE(0) and report back the space used.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns977AEC59B7F29gurbaohotmailcom@.129.250.171. 65...
Hi all,
Running sql2000 ee SP4 on win2003.
I have a small database which havent been inserted/updated since it was
created 6 months ago. No DDL changes. This database was about 1,5 Mb for
the first months, but have started to grow. The size is now 30 Mb and it
is growing with about 2 Mb a month. It is set to autogrow with 10%.
Can someone explain this growth?
name rows reserved
form_lang 1 56 KB
grid_expr 0 0 KB
GridViewCategory 0 0 KB
GridViewDefinition 0 0 KB
hierarchy_tables 0 0 KB
invitation 0 0 KB
iteration 1 24 KB
lookup 0 0 KB
lookup_lang 0 0 KB
PanelistCredit 0 0 KB
quota 1 56 KB
response_control 2 104 KB
response1 0 0 KB
SampleLog 0 0 KB
weightmodel 0 0 KB
weightmodelform 0 0 KB
|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in news:#noh#6kPGHA.1532
@.TK2MSFTNGP12.phx.gbl:

> Try running DBCC UPDATEUSAGE(0) and report back the space used.
>
Hi,
seems the first output from
USE MyDb; EXEC sp_MSforeachtable 'EXEC sp_spaceused [?]'
was missing some tables...
I ran UpdateUsage and the sp_spaceused again and found the reason for the
growth.
Case closed :-)
Thanks,

Database growing unexpectedly

Hi all,
Running sql2000 ee SP4 on win2003.
I have a small database which havent been inserted/updated since it was
created 6 months ago. No DDL changes. This database was about 1,5 Mb for
the first months, but have started to grow. The size is now 30 Mb and it
is growing with about 2 Mb a month. It is set to autogrow with 10%.
Can someone explain this growth?
name rows reserved
form_lang 1 56 KB
grid_expr 0 0 KB
GridViewCategory 0 0 KB
GridViewDefinition 0 0 KB
hierarchy_tables 0 0 KB
invitation 0 0 KB
iteration 1 24 KB
lookup 0 0 KB
lookup_lang 0 0 KB
PanelistCredit 0 0 KB
quota 1 56 KB
response_control 2 104 KB
response1 0 0 KB
SampleLog 0 0 KB
weightmodel 0 0 KB
weightmodelform 0 0 KBTry running DBCC UPDATEUSAGE(0) and report back the space used.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns977AEC59B7F29gurbaohotmailcom@.129.250.171.65...
Hi all,
Running sql2000 ee SP4 on win2003.
I have a small database which havent been inserted/updated since it was
created 6 months ago. No DDL changes. This database was about 1,5 Mb for
the first months, but have started to grow. The size is now 30 Mb and it
is growing with about 2 Mb a month. It is set to autogrow with 10%.
Can someone explain this growth?
name rows reserved
form_lang 1 56 KB
grid_expr 0 0 KB
GridViewCategory 0 0 KB
GridViewDefinition 0 0 KB
hierarchy_tables 0 0 KB
invitation 0 0 KB
iteration 1 24 KB
lookup 0 0 KB
lookup_lang 0 0 KB
PanelistCredit 0 0 KB
quota 1 56 KB
response_control 2 104 KB
response1 0 0 KB
SampleLog 0 0 KB
weightmodel 0 0 KB
weightmodelform 0 0 KB|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in news:#noh#6kPGHA.1532
@.TK2MSFTNGP12.phx.gbl:
> Try running DBCC UPDATEUSAGE(0) and report back the space used.
>
Hi,
seems the first output from
USE MyDb; EXEC sp_MSforeachtable 'EXEC sp_spaceused [?]'
was missing some tables...
I ran UpdateUsage and the sp_spaceused again and found the reason for the
growth.
Case closed :-)
Thanks,sql

Database Error during backup

We are currently using SQL 2000 SP4. When I try to backup any of our
Databases I receive the error message below, any suggestions that would fix
this problem for me. I would also like to make note that I viewed the Error
Log and could not identify any possible causes to this error, I have also
checked the Event Log and nothing logged there either.
--
Microsoft SQL-DMO (ODBC SQLState: 01000)
--
Error 926: Database 'msdb' cannot be opened. It has been marked SUSPECT by
recovery. See the SQL Server errorlog for more information.
--
OK
--
Regards,
JohnCheck BOL for DBCC CHECKDB
"John" <John@.discussions.microsoft.com> wrote in message
news:10ECD5A7-3C9A-427C-BD9B-F0DAA3F1FBD9@.microsoft.com...
> We are currently using SQL 2000 SP4. When I try to backup any of our
> Databases I receive the error message below, any suggestions that would
> fix
> this problem for me. I would also like to make note that I viewed the
> Error
> Log and could not identify any possible causes to this error, I have also
> checked the Event Log and nothing logged there either.
> --
> Microsoft SQL-DMO (ODBC SQLState: 01000)
> --
> Error 926: Database 'msdb' cannot be opened. It has been marked SUSPECT by
> recovery. See the SQL Server errorlog for more information.
> --
> OK
> --
> Regards,
> John
>|||... and be prepared for the worst: to restore your msdb database from a clean backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:u46ACdaEIHA.2004@.TK2MSFTNGP06.phx.gbl...
> Check BOL for DBCC CHECKDB
> "John" <John@.discussions.microsoft.com> wrote in message
> news:10ECD5A7-3C9A-427C-BD9B-F0DAA3F1FBD9@.microsoft.com...
>> We are currently using SQL 2000 SP4. When I try to backup any of our
>> Databases I receive the error message below, any suggestions that would
>> fix
>> this problem for me. I would also like to make note that I viewed the
>> Error
>> Log and could not identify any possible causes to this error, I have also
>> checked the Event Log and nothing logged there either.
>> --
>> Microsoft SQL-DMO (ODBC SQLState: 01000)
>> --
>> Error 926: Database 'msdb' cannot be opened. It has been marked SUSPECT by
>> recovery. See the SQL Server errorlog for more information.
>> --
>> OK
>> --
>> Regards,
>> John
>|||IIRC on sql2000 you can directly modify system tables. perhaps you can try
updating sysdatabases to clear the suspect flag (there may even be advice on
this in BOL) after you exhaust all other attempts and just before you
restore/rebuild your msdb.
You can also consider calling Microsoft for support.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"John" <John@.discussions.microsoft.com> wrote in message
news:10ECD5A7-3C9A-427C-BD9B-F0DAA3F1FBD9@.microsoft.com...
> We are currently using SQL 2000 SP4. When I try to backup any of our
> Databases I receive the error message below, any suggestions that would
> fix
> this problem for me. I would also like to make note that I viewed the
> Error
> Log and could not identify any possible causes to this error, I have also
> checked the Event Log and nothing logged there either.
> --
> Microsoft SQL-DMO (ODBC SQLState: 01000)
> --
> Error 926: Database 'msdb' cannot be opened. It has been marked SUSPECT by
> recovery. See the SQL Server errorlog for more information.
> --
> OK
> --
> Regards,
> John
>

Database Diagram and SP4

I had originally posted this in the sqlserver.server group, which was
probably incorrect. So, I thought I would try the tools group. Sorry to those
who end up reading it twice
I installed SP4 on my local SQL Server and all is well, except...
The database designer is behaving a little differently. If I move or resize
a table, the relationship lines are re-arranged more radically than they were
in the previous version (SP3). After this, the lines (including simple
straight lines) seem to have a break in the middle, and it is impossible to
reposition a simple line by dragging the whole thing at once. You have to
drag the top and bottom (or left and right) halves separately, and then they
rarely line up in a straight line again. If you drag on the break in the
middle, the line will, in some cases, create new sections and double back on
itself.
Is this a bug in SP4? Is there any way to change this behavior?
Hi
I have not noticed anything different, but it does seem to render quicker.
I will play with it a bit to see what I find.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ray Novak" <RayNovak@.discussions.microsoft.com> wrote in message
news:DE21797B-73D9-422E-8CB6-FF12EA21A986@.microsoft.com...
>I had originally posted this in the sqlserver.server group, which was
> probably incorrect. So, I thought I would try the tools group. Sorry to
> those
> who end up reading it twice
> I installed SP4 on my local SQL Server and all is well, except...
> The database designer is behaving a little differently. If I move or
> resize
> a table, the relationship lines are re-arranged more radically than they
> were
> in the previous version (SP3). After this, the lines (including simple
> straight lines) seem to have a break in the middle, and it is impossible
> to
> reposition a simple line by dragging the whole thing at once. You have to
> drag the top and bottom (or left and right) halves separately, and then
> they
> rarely line up in a straight line again. If you drag on the break in the
> middle, the line will, in some cases, create new sections and double back
> on
> itself.
> Is this a bug in SP4? Is there any way to change this behavior?
>
|||Thanx Mike - I have also noticed that the diagrams seem faster.
It seems that this problem applies to diagrams I created "pre-SP4". I tried
creating a new diagram and it is (so far) behaving correctly.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> I have not noticed anything different, but it does seem to render quicker.
> I will play with it a bit to see what I find.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Ray Novak" <RayNovak@.discussions.microsoft.com> wrote in message
> news:DE21797B-73D9-422E-8CB6-FF12EA21A986@.microsoft.com...
>
>

Database data file does not auto-grow

Dear all,
We are using SQL2000 sp4 on Windows 2000 Svr.
We found one of the database size become zero in Enterprise manager.
We have checked the database setting and the database is already set
auto-growth, without limit, by 1000Mb each time.
The data file size reach 6G now.
Is there anything we miss? That's the first time we meet this case.
We have checked the Harddisk drive for the data file and it still have over
100G space.
IvanIvan wrote:
> Dear all,
> We are using SQL2000 sp4 on Windows 2000 Svr.
> We found one of the database size become zero in Enterprise manager.
> We have checked the database setting and the database is already set
> auto-growth, without limit, by 1000Mb each time.
> The data file size reach 6G now.
> Is there anything we miss? That's the first time we meet this case.
> We have checked the Harddisk drive for the data file and it still have over
> 100G space.
> Ivan
Do you get an error message saying the filegroup is full?
What is the output of EXEC sp_spaceused?
Auto-growth is usually a bad idea and isn't something I would normally
recommend for a production application. Why would you want to grow a
6GB database in 1GB increments?
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||dbcc updateusage?
Cheers,
"Ivan" wrote:
> Dear all,
> We are using SQL2000 sp4 on Windows 2000 Svr.
> We found one of the database size become zero in Enterprise manager.
> We have checked the database setting and the database is already set
> auto-growth, without limit, by 1000Mb each time.
> The data file size reach 6G now.
> Is there anything we miss? That's the first time we meet this case.
> We have checked the Harddisk drive for the data file and it still have over
> 100G space.
> Ivan
>
>

Database data file does not auto-grow

Dear all,
We are using SQL2000 sp4 on Windows 2000 Svr.
We found one of the database size become zero in Enterprise manager.
We have checked the database setting and the database is already set
auto-growth, without limit, by 1000Mb each time.
The data file size reach 6G now.
Is there anything we miss? That's the first time we meet this case.
We have checked the Harddisk drive for the data file and it still have over
100G space.
Ivan
Ivan wrote:
> Dear all,
> We are using SQL2000 sp4 on Windows 2000 Svr.
> We found one of the database size become zero in Enterprise manager.
> We have checked the database setting and the database is already set
> auto-growth, without limit, by 1000Mb each time.
> The data file size reach 6G now.
> Is there anything we miss? That's the first time we meet this case.
> We have checked the Harddisk drive for the data file and it still have over
> 100G space.
> Ivan
Do you get an error message saying the filegroup is full?
What is the output of EXEC sp_spaceused?
Auto-growth is usually a bad idea and isn't something I would normally
recommend for a production application. Why would you want to grow a
6GB database in 1GB increments?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||dbcc updateusage?
Cheers,
"Ivan" wrote:

> Dear all,
> We are using SQL2000 sp4 on Windows 2000 Svr.
> We found one of the database size become zero in Enterprise manager.
> We have checked the database setting and the database is already set
> auto-growth, without limit, by 1000Mb each time.
> The data file size reach 6G now.
> Is there anything we miss? That's the first time we meet this case.
> We have checked the Harddisk drive for the data file and it still have over
> 100G space.
> Ivan
>
>

Database data file does not auto-grow

Dear all,
We are using SQL2000 sp4 on Windows 2000 Svr.
We found one of the database size become zero in Enterprise manager.
We have checked the database setting and the database is already set
auto-growth, without limit, by 1000Mb each time.
The data file size reach 6G now.
Is there anything we miss? That's the first time we meet this case.
We have checked the Harddisk drive for the data file and it still have over
100G space.
IvanIvan wrote:
> Dear all,
> We are using SQL2000 sp4 on Windows 2000 Svr.
> We found one of the database size become zero in Enterprise manager.
> We have checked the database setting and the database is already set
> auto-growth, without limit, by 1000Mb each time.
> The data file size reach 6G now.
> Is there anything we miss? That's the first time we meet this case.
> We have checked the Harddisk drive for the data file and it still have ove
r
> 100G space.
> Ivan
Do you get an error message saying the filegroup is full?
What is the output of EXEC sp_spaceused?
Auto-growth is usually a bad idea and isn't something I would normally
recommend for a production application. Why would you want to grow a
6GB database in 1GB increments?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||dbcc updateusage?
Cheers,
"Ivan" wrote:

> Dear all,
> We are using SQL2000 sp4 on Windows 2000 Svr.
> We found one of the database size become zero in Enterprise manager.
> We have checked the database setting and the database is already set
> auto-growth, without limit, by 1000Mb each time.
> The data file size reach 6G now.
> Is there anything we miss? That's the first time we meet this case.
> We have checked the Harddisk drive for the data file and it still have ove
r
> 100G space.
> Ivan
>
>

Database could not be removed

We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now we
are facing a strange case where when we tried to restore and overwrite a
database, it always say database is in use and cannot be overwritten. I have
tried to detach and even delete it but everytime it just said the database is
in use and refused to give up. I have checked and make sure no users and
connections are make to the said database. Eventually I have to resort to
restarting the SQL service to remove this database.
Is there a way to remove or overwrite a database without restarting the SQL
service?Are you sure YOU are not in the database when you are trying to restore or
detach?
If you run the sp_lock procedure, look for DB locks in the database you are
trying to remove. There will then be a spid, which can let you determine who
is in the database.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"mpWong" <mpWong@.discussions.microsoft.com> wrote in message
news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
> We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now
> we
> are facing a strange case where when we tried to restore and overwrite a
> database, it always say database is in use and cannot be overwritten. I
> have
> tried to detach and even delete it but everytime it just said the database
> is
> in use and refused to give up. I have checked and make sure no users and
> connections are make to the said database. Eventually I have to resort to
> restarting the SQL service to remove this database.
> Is there a way to remove or overwrite a database without restarting the
> SQL
> service?
>|||Thanks for your advice. I will check next time this happens again. I am
absolutely sure nobody or myself was using the database when I tried the
restore.
"Kalen Delaney" wrote:
> Are you sure YOU are not in the database when you are trying to restore or
> detach?
> If you run the sp_lock procedure, look for DB locks in the database you are
> trying to remove. There will then be a spid, which can let you determine who
> is in the database.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "mpWong" <mpWong@.discussions.microsoft.com> wrote in message
> news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
> > We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now
> > we
> > are facing a strange case where when we tried to restore and overwrite a
> > database, it always say database is in use and cannot be overwritten. I
> > have
> > tried to detach and even delete it but everytime it just said the database
> > is
> > in use and refused to give up. I have checked and make sure no users and
> > connections are make to the said database. Eventually I have to resort to
> > restarting the SQL service to remove this database.
> > Is there a way to remove or overwrite a database without restarting the
> > SQL
> > service?
> >
>
>

Database could not be removed

We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now we
are facing a strange case where when we tried to restore and overwrite a
database, it always say database is in use and cannot be overwritten. I have
tried to detach and even delete it but everytime it just said the database is
in use and refused to give up. I have checked and make sure no users and
connections are make to the said database. Eventually I have to resort to
restarting the SQL service to remove this database.
Is there a way to remove or overwrite a database without restarting the SQL
service?
Are you sure YOU are not in the database when you are trying to restore or
detach?
If you run the sp_lock procedure, look for DB locks in the database you are
trying to remove. There will then be a spid, which can let you determine who
is in the database.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"mpWong" <mpWong@.discussions.microsoft.com> wrote in message
news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
> We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now
> we
> are facing a strange case where when we tried to restore and overwrite a
> database, it always say database is in use and cannot be overwritten. I
> have
> tried to detach and even delete it but everytime it just said the database
> is
> in use and refused to give up. I have checked and make sure no users and
> connections are make to the said database. Eventually I have to resort to
> restarting the SQL service to remove this database.
> Is there a way to remove or overwrite a database without restarting the
> SQL
> service?
>
|||Thanks for your advice. I will check next time this happens again. I am
absolutely sure nobody or myself was using the database when I tried the
restore.
"Kalen Delaney" wrote:

> Are you sure YOU are not in the database when you are trying to restore or
> detach?
> If you run the sp_lock procedure, look for DB locks in the database you are
> trying to remove. There will then be a spid, which can let you determine who
> is in the database.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "mpWong" <mpWong@.discussions.microsoft.com> wrote in message
> news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
>
>

Database could not be removed

We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now w
e
are facing a strange case where when we tried to restore and overwrite a
database, it always say database is in use and cannot be overwritten. I have
tried to detach and even delete it but everytime it just said the database i
s
in use and refused to give up. I have checked and make sure no users and
connections are make to the said database. Eventually I have to resort to
restarting the SQL service to remove this database.
Is there a way to remove or overwrite a database without restarting the SQL
service?Are you sure YOU are not in the database when you are trying to restore or
detach?
If you run the sp_lock procedure, look for DB locks in the database you are
trying to remove. There will then be a spid, which can let you determine who
is in the database.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"mpWong" <mpWong@.discussions.microsoft.com> wrote in message
news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
> We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now
> we
> are facing a strange case where when we tried to restore and overwrite a
> database, it always say database is in use and cannot be overwritten. I
> have
> tried to detach and even delete it but everytime it just said the database
> is
> in use and refused to give up. I have checked and make sure no users and
> connections are make to the said database. Eventually I have to resort to
> restarting the SQL service to remove this database.
> Is there a way to remove or overwrite a database without restarting the
> SQL
> service?
>|||Thanks for your advice. I will check next time this happens again. I am
absolutely sure nobody or myself was using the database when I tried the
restore.
"Kalen Delaney" wrote:

> Are you sure YOU are not in the database when you are trying to restore or
> detach?
> If you run the sp_lock procedure, look for DB locks in the database you ar
e
> trying to remove. There will then be a spid, which can let you determine w
ho
> is in the database.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "mpWong" <mpWong@.discussions.microsoft.com> wrote in message
> news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
>
>