Database is running slow after running DBCC Shrinkdatabase command

We have SQL 2000 Enterprise Edition running on Windows 2000 Server. I ran
the DBCC Shrinkdatabase command last week. Now, the database is slow when I
open one of the work orders. Do you have any recommendation on how to fix
the slowness? Am I doing something incorrectly? I ran the following
commands under Query Analyzer. Thank you.
BACKUP LOG Support_DATA WITH TRUNCATE_ONLY
GO
DBCC SHRINKDATABASE (Support_DATA, TRUNCATEONLY)Diane,
Just a guess...perhaps there is very little free space in the database and
the growth interval is small (i.e., 1MB) so the database continually grows
as new data is inserted. If this is the case, increase the size of the
database and the the growth setting. Degrading performance can also be
attributed to index fragmentation and out-of-date statistics.
HTH
Jerr
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:ek4k5hgxFHA.1132@.TK2MSFTNGP10.phx.gbl...
> We have SQL 2000 Enterprise Edition running on Windows 2000 Server. I ran
> the DBCC Shrinkdatabase command last week. Now, the database is slow when
> I open one of the work orders. Do you have any recommendation on how to
> fix the slowness? Am I doing something incorrectly? I ran the following
> commands under Query Analyzer. Thank you.
> BACKUP LOG Support_DATA WITH TRUNCATE_ONLY
> GO
> DBCC SHRINKDATABASE (Support_DATA, TRUNCATEONLY)
>|||The shrinkdatabase command isn't very useful in my opinion.
You don't have much control over which files are being
shrunk to what size. Shrinking is typically something that
you would do ad hoc and for a specific file using shrinkfile
- after a run away transaction filled up the log, a database
where the log wasn't being backed up and the size became too
large, etc. The goal should be to size the files
appropriately, do regular log back ups to keep the log size
reasonable and avoid shrinking activities.
The slowness may or may not be related to having shrunk the
database last week. When you query the table, you would want
to monitor what is going on - checking for other activity
and locking/blocking by executing sp_who2 and querying the
sysprocesses table in the master database. And you'd want to
use something other than Enterprise Manager to view data in
tables - especially if it has a lot of data. Enterprise
Manager isn't necessarily a good tool to use for viewing or
manipulating data. Use Query Analyzer instead.
-Sue
On Fri, 30 Sep 2005 14:46:53 -0700, "Diane Walker"
<ett9300@.yahoo.com> wrote:
>We have SQL 2000 Enterprise Edition running on Windows 2000 Server. I ran
>the DBCC Shrinkdatabase command last week. Now, the database is slow when I
>open one of the work orders. Do you have any recommendation on how to fix
>the slowness? Am I doing something incorrectly? I ran the following
>commands under Query Analyzer. Thank you.
>BACKUP LOG Support_DATA WITH TRUNCATE_ONLY
>GO
>DBCC SHRINKDATABASE (Support_DATA, TRUNCATEONLY)
>|||Possibly autogrow as suggested or that indexes became fragmented due to the shrink:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:ek4k5hgxFHA.1132@.TK2MSFTNGP10.phx.gbl...
> We have SQL 2000 Enterprise Edition running on Windows 2000 Server. I ran the DBCC Shrinkdatabase
> command last week. Now, the database is slow when I open one of the work orders. Do you have any
> recommendation on how to fix the slowness? Am I doing something incorrectly? I ran the following
> commands under Query Analyzer. Thank you.
> BACKUP LOG Support_DATA WITH TRUNCATE_ONLY
> GO
> DBCC SHRINKDATABASE (Support_DATA, TRUNCATEONLY)
>sql

Database is read-only.

Hi

I have been learning on how to use the new features of the .NET 2.0, and Well I using VS2005 and everything is pretty straight foward. I did a Membership webform where you log in and create users and roles etc. I installed the SQL Server 2005 express as my Data provider and thats where my Tables are. I used the aspnetsql_reg comand to integrate the server to this application. Anyway my problem started when i decided to copy my project to my IIS server and well when I run the app from the Browser tells me that he Database It's set to Read-Omly and therefore it cant update it. This happens right after log in. but then I open the App. on hte VS and it runs find.

This is the error:

Exception Details: System.Data.SqlClient.SqlException: Failed to update database "C:\INETPUB\WWWROOT\ESPM\APP_DATA\ASPNETDB.MDF" because the database is read-only.

If anyone have any ideas I would really aprecciate.

I got the same error - no doubt you have solved it by now - can you share....?

Thanks
ian|||Actually I haven't but I thikn i know where it is going. I thikn it has to be with the IIS, and something has to do with the permissions of write on the user that is use to access de app. Other than that I am out of ideas.

how about you ?

|||I went through deleting the files, using SSEUTIL to detach database, reset permissions on the folders and then copy back. Still no good - and I can't find any other ideas on the Internet.......
Thanks anyway
Ian|||I went through deleting the files and using the SSEUTIL program as well. I still have not been able to resolve this issue. Has anyone else figured it out?

Thanks
Zane|||I am having the exact same problem, and would really like to know if anyone has found the answer to this.
|||Ditto! Nothing I can do will make it work. I would MUCH prefer to run it on SQL Server 2000, but there are tons of tables, views, stored procedures, and even a few functions in the express databases. Not worth manually copying each one.
If anyone figures this out, please let us know. I googled:
"ASPNETDB.MDF" read only
and found only THREE links on google groups, this being one of them.
I created a new personal web site (using Visual Studio .NET 2005 Beta 2, not VWD)|||

With a bit of searching on Google I found out that this is a bug from an older release. Microsoft claimed to have fixed it in the latest release but the problem still excists :(

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=dd6d161b-df08-40bc-b9ed-fbca71949ddc

Guess we have to wait..and wait..and wait|||Can you try the following steps.

1. Check the file attributes on the MDF to make sure its writable.
2. Make sure the user has permissions to the file.
3. Run the following query;
use master
select name, status from sysdatabases

If you are using AttachDBFilename in the connection string you will need to insert some debug code that does a debug print/console.writeline or something to get the output once the conneciton has been made and before any sql has been executed.

If you are using regular connections then just run the above from sqlcmd or express manager|||I spent so long on this (and other bugs/problems with VS .Net 2005) that I gave up. Went back to using VS 2003 and regular SQL Server for the security database. It boggles my mind that something so simple can cause so many problems - I have read 20 different solutions to get this ever-so-simple-idea working - WTF are people doing?|||

I have been struggling with this for the past week or so. Just as I was ready to give up and go back to VS2003 I decided to try one last thing. Now it works!

Here is the process I went through:
I first deleted my site files from c:\inetpup\wwwroot. Then using the sseutil, I detatched every database I was using for the site including the aspnetdb.mdf. I then changed the permissions on the wwwroot folder to allow full control for user aspnet (using IIS 5.1). Then from inside VS2005, I copied the site again to the root of the IIS server.

As I mentioned earlier, this finally worked for me. I hope it works for you as well.

||| This worked for me!
Thanks a lot!
|||You got it right my friend ! And you don't even have to remove your application before and to put it back after .. juste add the permissions to aspnet. Thank a lot !|||Actually all you have to do is give write permissions to ASPNET for the App_Data directory instead of all of wwwroot (you probably don't want to do that).

Its probably advisable to use a real sql database anyway but for testing purposes these files aren't too bad.|||So to be clear to resolve the issue where you have a .mdf database file generated by VS2005 and it gives you read-only errors you must do these things:

1.) Make sure your virtual directory is running ASP.NET v2.*
2.) Give the user ASPNET full control over the App_Data directory found within your website.
3.) Restart IIS to refresh its permissions.

Database is read-only.

Hi

I have been learning on how to use the new features of the .NET 2.0, and Well I using VS2005 and everything is pretty straight foward. I did a Membership webform where you log in and create users and roles etc. I installed the SQL Server 2005 express as my Data provider and thats where my Tables are. I used the aspnetsql_reg comand to integrate the server to this application. Anyway my problem started when i decided to copy my project to my IIS server and well when I run the app from the Browser tells me that he Database It's set to Read-Omly and therefore it cant update it. This happens right after log in. but then I open the App. on hte VS and it runs find.

This is the error:

Exception Details: System.Data.SqlClient.SqlException: Failed to update database "C:\INETPUB\WWWROOT\ESPM\APP_DATA\ASPNETDB.MDF" because the database is read-only.

If anyone have any ideas I would really aprecciate.

I got the same error - no doubt you have solved it by now - can you share....?

Thanks
ian|||Actually I haven't but I thikn i know where it is going. I thikn it has to be with the IIS, and something has to do with the permissions of write on the user that is use to access de app. Other than that I am out of ideas.

how about you ?

|||I went through deleting the files, using SSEUTIL to detach database, reset permissions on the folders and then copy back. Still no good - and I can't find any other ideas on the Internet.......
Thanks anyway
Ian|||I went through deleting the files and using the SSEUTIL program as well. I still have not been able to resolve this issue. Has anyone else figured it out?

Thanks
Zane|||I am having the exact same problem, and would really like to know if anyone has found the answer to this.
|||Ditto! Nothing I can do will make it work. I would MUCH prefer to run it on SQL Server 2000, but there are tons of tables, views, stored procedures, and even a few functions in the express databases. Not worth manually copying each one.
If anyone figures this out, please let us know. I googled:
"ASPNETDB.MDF" read only
and found only THREE links on google groups, this being one of them.
I created a new personal web site (using Visual Studio .NET 2005 Beta 2, not VWD)|||

With a bit of searching on Google I found out that this is a bug from an older release. Microsoft claimed to have fixed it in the latest release but the problem still excists :(

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=dd6d161b-df08-40bc-b9ed-fbca71949ddc

Guess we have to wait..and wait..and wait

|||Can you try the following steps.

1. Check the file attributes on the MDF to make sure its writable.
2. Make sure the user has permissions to the file.
3. Run the following query;
use master
select name, status from sysdatabases

If you are using AttachDBFilename in the connection string you will need to insert some debug code that does a debug print/console.writeline or something to get the output once the conneciton has been made and before any sql has been executed.

If you are using regular connections then just run the above from sqlcmd or express manager|||I spent so long on this (and other bugs/problems with VS .Net 2005) that I gave up. Went back to using VS 2003 and regular SQL Server for the security database. It boggles my mind that something so simple can cause so many problems - I have read 20 different solutions to get this ever-so-simple-idea working - WTF are people doing?|||

I have been struggling with this for the past week or so. Just as I was ready to give up and go back to VS2003 I decided to try one last thing. Now it works!

Here is the process I went through:
I first deleted my site files from c:\inetpup\wwwroot. Then using the sseutil, I detatched every database I was using for the site including the aspnetdb.mdf. I then changed the permissions on the wwwroot folder to allow full control for user aspnet (using IIS 5.1). Then from inside VS2005, I copied the site again to the root of the IIS server.

As I mentioned earlier, this finally worked for me. I hope it works for you as well.

||| This worked for me!
Thanks a lot!
|||You got it right my friend ! And you don't even have to remove your application before and to put it back after .. juste add the permissions to aspnet. Thank a lot !|||Actually all you have to do is give write permissions to ASPNET for the App_Data directory instead of all of wwwroot (you probably don't want to do that).

Its probably advisable to use a real sql database anyway but for testing purposes these files aren't too bad.|||So to be clear to resolve the issue where you have a .mdf database file generated by VS2005 and it gives you read-only errors you must do these things:

1.) Make sure your virtual directory is running ASP.NET v2.*
2.) Give the user ASPNET full control over the App_Data directory found within your website.
3.) Restart IIS to refresh its permissions.

Database is read-only.

Hi

I have been learning on how to use the new features of the .NET 2.0, and Well I using VS2005 and everything is pretty straight foward. I did a Membership webform where you log in and create users and roles etc. I installed the SQL Server 2005 express as my Data provider and thats where my Tables are. I used the aspnetsql_reg comand to integrate the server to this application. Anyway my problem started when i decided to copy my project to my IIS server and well when I run the app from the Browser tells me that he Database It's set to Read-Omly and therefore it cant update it. This happens right after log in. but then I open the App. on hte VS and it runs find.

This is the error:

Exception Details: System.Data.SqlClient.SqlException: Failed to update database "C:\INETPUB\WWWROOT\ESPM\APP_DATA\ASPNETDB.MDF" because the database is read-only.

If anyone have any ideas I would really aprecciate.

I got the same error - no doubt you have solved it by now - can you share....?

Thanks
ian|||Actually I haven't but I thikn i know where it is going. I thikn it has to be with the IIS, and something has to do with the permissions of write on the user that is use to access de app. Other than that I am out of ideas.

how about you ?

|||I went through deleting the files, using SSEUTIL to detach database, reset permissions on the folders and then copy back. Still no good - and I can't find any other ideas on the Internet.......
Thanks anyway
Ian|||I went through deleting the files and using the SSEUTIL program as well. I still have not been able to resolve this issue. Has anyone else figured it out?

Thanks
Zane|||I am having the exact same problem, and would really like to know if anyone has found the answer to this.
|||Ditto! Nothing I can do will make it work. I would MUCH prefer to run it on SQL Server 2000, but there are tons of tables, views, stored procedures, and even a few functions in the express databases. Not worth manually copying each one.
If anyone figures this out, please let us know. I googled:
"ASPNETDB.MDF" read only
and found only THREE links on google groups, this being one of them.
I created a new personal web site (using Visual Studio .NET 2005 Beta 2, not VWD)|||

With a bit of searching on Google I found out that this is a bug from an older release. Microsoft claimed to have fixed it in the latest release but the problem still excists :(

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=dd6d161b-df08-40bc-b9ed-fbca71949ddc

Guess we have to wait..and wait..and wait

|||Can you try the following steps.

1. Check the file attributes on the MDF to make sure its writable.
2. Make sure the user has permissions to the file.
3. Run the following query;
use master
select name, status from sysdatabases

If you are using AttachDBFilename in the connection string you will need to insert some debug code that does a debug print/console.writeline or something to get the output once the conneciton has been made and before any sql has been executed.

If you are using regular connections then just run the above from sqlcmd or express manager|||I spent so long on this (and other bugs/problems with VS .Net 2005) that I gave up. Went back to using VS 2003 and regular SQL Server for the security database. It boggles my mind that something so simple can cause so many problems - I have read 20 different solutions to get this ever-so-simple-idea working - WTF are people doing?|||

I have been struggling with this for the past week or so. Just as I was ready to give up and go back to VS2003 I decided to try one last thing. Now it works!

Here is the process I went through:
I first deleted my site files from c:\inetpup\wwwroot. Then using the sseutil, I detatched every database I was using for the site including the aspnetdb.mdf. I then changed the permissions on the wwwroot folder to allow full control for user aspnet (using IIS 5.1). Then from inside VS2005, I copied the site again to the root of the IIS server.

As I mentioned earlier, this finally worked for me. I hope it works for you as well.

||| This worked for me!
Thanks a lot!
|||You got it right my friend ! And you don't even have to remove your application before and to put it back after .. juste add the permissions to aspnet. Thank a lot !|||Actually all you have to do is give write permissions to ASPNET for the App_Data directory instead of all of wwwroot (you probably don't want to do that).

Its probably advisable to use a real sql database anyway but for testing purposes these files aren't too bad.|||So to be clear to resolve the issue where you have a .mdf database file generated by VS2005 and it gives you read-only errors you must do these things:

1.) Make sure your virtual directory is running ASP.NET v2.*
2.) Give the user ASPNET full control over the App_Data directory found within your website.
3.) Restart IIS to refresh its permissions.

Database is read-only.

Hi

I have been learning on how to use the new features of the .NET 2.0, and Well I using VS2005 and everything is pretty straight foward. I did a Membership webform where you log in and create users and roles etc. I installed the SQL Server 2005 express as my Data provider and thats where my Tables are. I used the aspnetsql_reg comand to integrate the server to this application. Anyway my problem started when i decided to copy my project to my IIS server and well when I run the app from the Browser tells me that he Database It's set to Read-Omly and therefore it cant update it. This happens right after log in. but then I open the App. on hte VS and it runs find.

This is the error:

Exception Details: System.Data.SqlClient.SqlException: Failed to update database "C:\INETPUB\WWWROOT\ESPM\APP_DATA\ASPNETDB.MDF" because the database is read-only.

If anyone have any ideas I would really aprecciate.

I got the same error - no doubt you have solved it by now - can you share....?

Thanks
ian|||Actually I haven't but I thikn i know where it is going. I thikn it has to be with the IIS, and something has to do with the permissions of write on the user that is use to access de app. Other than that I am out of ideas.

how about you ?|||I went through deleting the files, using SSEUTIL to detach database, reset permissions on the folders and then copy back. Still no good - and I can't find any other ideas on the Internet.......
Thanks anyway
Ian|||I went through deleting the files and using the SSEUTIL program as well. I still have not been able to resolve this issue. Has anyone else figured it out?

Thanks
Zane|||I am having the exact same problem, and would really like to know if anyone has found the answer to this.|||Ditto! Nothing I can do will make it work. I would MUCH prefer to run it on SQL Server 2000, but there are tons of tables, views, stored procedures, and even a few functions in the express databases. Not worth manually copying each one.
If anyone figures this out, please let us know. I googled:
"ASPNETDB.MDF" read only
and found only THREE links on google groups, this being one of them.
I created a new personal web site (using Visual Studio .NET 2005 Beta 2, not VWD)|||

With a bit of searching on Google I found out that this is a bug from an older release. Microsoft claimed to have fixed it in the latest release but the problem still excists :(

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=dd6d161b-df08-40bc-b9ed-fbca71949ddc

Guess we have to wait..and wait..and wait

|||Can you try the following steps.

1. Check the file attributes on the MDF to make sure its writable.
2. Make sure the user has permissions to the file.
3. Run the following query;
use master
select name, status from sysdatabases

If you are using AttachDBFilename in the connection string you will need to insert some debug code that does a debug print/console.writeline or something to get the output once the conneciton has been made and before any sql has been executed.

If you are using regular connections then just run the above from sqlcmd or express manager|||I spent so long on this (and other bugs/problems with VS .Net 2005) that I gave up. Went back to using VS 2003 and regular SQL Server for the security database. It boggles my mind that something so simple can cause so many problems - I have read 20 different solutions to get this ever-so-simple-idea working - WTF are people doing?|||

I have been struggling with this for the past week or so. Just as I was ready to give up and go back to VS2003 I decided to try one last thing. Now it works!

Here is the process I went through:
I first deleted my site files from c:\inetpup\wwwroot. Then using the sseutil, I detatched every database I was using for the site including the aspnetdb.mdf. I then changed the permissions on the wwwroot folder to allow full control for user aspnet (using IIS 5.1). Then from inside VS2005, I copied the site again to the root of the IIS server.

As I mentioned earlier, this finally worked for me. I hope it works for you as well.

||| This worked for me!
Thanks a lot!|||You got it right my friend ! And you don't even have to remove your application before and to put it back after .. juste add the permissions to aspnet. Thank a lot !|||Actually all you have to do is give write permissions to ASPNET for the App_Data directory instead of all of wwwroot (you probably don't want to do that).

Its probably advisable to use a real sql database anyway but for testing purposes these files aren't too bad.

|||So to be clear to resolve the issue where you have a .mdf database file generated by VS2005 and it gives you read-only errors you must do these things:

1.) Make sure your virtual directory is running ASP.NET v2.*
2.) Give the user ASPNET full control over the App_Data directory found within your website.
3.) Restart IIS to refresh its permissions.

database in standby mode

How to find using tsql, that the current database is readonly or in warm
standby mode ?This information is available with DATABASEPROPERTY:
SELECT
DATABASEPROPERTY('MyDatabase', 'IsInStandBy') AS IsInStandBy,
DATABASEPROPERTY('MyDatabase', 'IsReadOnly') AS IsReadOnly
See the DATABASEPROPERTY and DATABASEPROPERTYEX in the Books Online for more
info.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"VSS" <aa@.aa> wrote in message news:OiEGqxUuGHA.1512@.TK2MSFTNGP03.phx.gbl...
> How to find using tsql, that the current database is readonly or in warm
> standby mode ?
>|||You can also use the below command
sp_helpdb <DBNAME>
See the status column...
Thanks
Hari
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e6vVo5UuGHA.4968@.TK2MSFTNGP03.phx.gbl...
> This information is available with DATABASEPROPERTY:
> SELECT
> DATABASEPROPERTY('MyDatabase', 'IsInStandBy') AS IsInStandBy,
> DATABASEPROPERTY('MyDatabase', 'IsReadOnly') AS IsReadOnly
> See the DATABASEPROPERTY and DATABASEPROPERTYEX in the Books Online for
> more info.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "VSS" <aa@.aa> wrote in message
> news:OiEGqxUuGHA.1512@.TK2MSFTNGP03.phx.gbl...
>> How to find using tsql, that the current database is readonly or in warm
>> standby mode ?
>>
>sql

Database in Standby / Readonly

I recently restored a database to a 2005 SQL Server and it is now
reporting that it is in standby / readonly mode. I've seen some
suggestion that this could be solved through re-restoring the database
but the database restore isn't an option. I've tried taking it offline
so that I could get access to restore it and haven't had any luck
there as well. Is there something I'm missing here or do I just need
to drop and restore the database all over again?
RESTORE DATABASE [database_name] WITH RECOVERY
"johnvmc@.gmail.com" wrote:

> I recently restored a database to a 2005 SQL Server and it is now
> reporting that it is in standby / readonly mode. I've seen some
> suggestion that this could be solved through re-restoring the database
> but the database restore isn't an option. I've tried taking it offline
> so that I could get access to restore it and haven't had any luck
> there as well. Is there something I'm missing here or do I just need
> to drop and restore the database all over again?
>

Database in Standby / Readonly

I recently restored a database to a 2005 SQL Server and it is now
reporting that it is in standby / readonly mode. I've seen some
suggestion that this could be solved through re-restoring the database
but the database restore isn't an option. I've tried taking it offline
so that I could get access to restore it and haven't had any luck
there as well. Is there something I'm missing here or do I just need
to drop and restore the database all over again?RESTORE DATABASE [database_name] WITH RECOVERY
"johnvmc@.gmail.com" wrote:
> I recently restored a database to a 2005 SQL Server and it is now
> reporting that it is in standby / readonly mode. I've seen some
> suggestion that this could be solved through re-restoring the database
> but the database restore isn't an option. I've tried taking it offline
> so that I could get access to restore it and haven't had any luck
> there as well. Is there something I'm missing here or do I just need
> to drop and restore the database all over again?
>

Database in Standby / Readonly

I recently restored a database to a 2005 SQL Server and it is now
reporting that it is in standby / readonly mode. I've seen some
suggestion that this could be solved through re-restoring the database
but the database restore isn't an option. I've tried taking it offline
so that I could get access to restore it and haven't had any luck
there as well. Is there something I'm missing here or do I just need
to drop and restore the database all over again?RESTORE DATABASE [database_name] WITH RECOVERY
"johnvmc@.gmail.com" wrote:

> I recently restored a database to a 2005 SQL Server and it is now
> reporting that it is in standby / readonly mode. I've seen some
> suggestion that this could be solved through re-restoring the database
> but the database restore isn't an option. I've tried taking it offline
> so that I could get access to restore it and haven't had any luck
> there as well. Is there something I'm missing here or do I just need
> to drop and restore the database all over again?
>

database in SQL2K need help in upading/adding 2 memo columns

I have database in SQL2K. I have a memo column called memo1. I want to
update memo with "dicount % "+memo1.
I want to insert "discount %" in front of memo1.
This is going to be just one time shot. There are more 20,000 records need
to be updated.
I would appreciate if someone can help me.
I can run VB program and script so if anyone can help me to create something
in VB, if it is not possible in SQL2K,
I would really appreciate.
Thanks
Hi,
IN SQL 2000 you could write a simple Update statement
Update Tablename
set memo1 = 'discount % ' + memo1
This will update all the 20K records in a single shot.
Did I answered your question?
Thanks
Hari
SQL Server MVP
"Mac" <mac@.hotmail.com> wrote in message
news:u9x4e.14059$Fh2.5711@.trnddc04...
>I have database in SQL2K. I have a memo column called memo1. I want to
>update memo with "dicount % "+memo1.
> I want to insert "discount %" in front of memo1.
> This is going to be just one time shot. There are more 20,000 records
> need to be updated.
> I would appreciate if someone can help me.
> I can run VB program and script so if anyone can help me to create
> something in VB, if it is not possible in SQL2K,
> I would really appreciate.
> Thanks
>
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:386852 microsoft.public.access.modulesdaovba:153018
Hari:
Thanks for your quick response.
I already tried this and I am getting data type error.
Thx
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%234kWKteOFHA.3444@.tk2msftngp13.phx.gbl...
> Hi,
> IN SQL 2000 you could write a simple Update statement
> Update Tablename
> set memo1 = 'discount % ' + memo1
> This will update all the 20K records in a single shot.
> Did I answered your question?
> Thanks
> Hari
> SQL Server MVP
> "Mac" <mac@.hotmail.com> wrote in message
> news:u9x4e.14059$Fh2.5711@.trnddc04...
>
|||Mac,
Looks like your datatype is TEXT. Inthat case you need to use UPDATETEXT
command to update.
See the below archive posts:-
http://groups-beta.google.com/group/...549ff4637eff45
Thanks
Hari
SQL Server MVP
"Mac" <mac@.hotmail.com> wrote in message
news:3Jx4e.25741$k66.15806@.trnddc03...
> Hari:
> Thanks for your quick response.
> I already tried this and I am getting data type error.
> Thx
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%234kWKteOFHA.3444@.tk2msftngp13.phx.gbl...
>

database in SQL2K need help in upading/adding 2 memo columns

I have database in SQL2K. I have a memo column called memo1. I want to
update memo with "dicount % "+memo1.
I want to insert "discount %" in front of memo1.
This is going to be just one time shot. There are more 20,000 records need
to be updated.
I would appreciate if someone can help me.
I can run VB program and script so if anyone can help me to create something
in VB, if it is not possible in SQL2K,
I would really appreciate.
ThanksHi,
IN SQL 2000 you could write a simple Update statement
Update Tablename
set memo1 = 'discount % ' + memo1
This will update all the 20K records in a single shot.
Did I answered your question?
Thanks
Hari
SQL Server MVP
"Mac" <mac@.hotmail.com> wrote in message
news:u9x4e.14059$Fh2.5711@.trnddc04...
>I have database in SQL2K. I have a memo column called memo1. I want to
>update memo with "dicount % "+memo1.
> I want to insert "discount %" in front of memo1.
> This is going to be just one time shot. There are more 20,000 records
> need to be updated.
> I would appreciate if someone can help me.
> I can run VB program and script so if anyone can help me to create
> something in VB, if it is not possible in SQL2K,
> I would really appreciate.
> Thanks
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:386852 microsof
t.public.access.modulesdaovba:153018
Hari:
Thanks for your quick response.
I already tried this and I am getting data type error.
Thx
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%234kWKteOFHA.3444@.tk2msftngp13.phx.gbl...
> Hi,
> IN SQL 2000 you could write a simple Update statement
> Update Tablename
> set memo1 = 'discount % ' + memo1
> This will update all the 20K records in a single shot.
> Did I answered your question?
> Thanks
> Hari
> SQL Server MVP
> "Mac" <mac@.hotmail.com> wrote in message
> news:u9x4e.14059$Fh2.5711@.trnddc04...
>|||Mac,
Looks like your datatype is TEXT. Inthat case you need to use UPDATETEXT
command to update.
See the below archive posts:-
http://groups-beta.google.com/group...37eff
45
Thanks
Hari
SQL Server MVP
"Mac" <mac@.hotmail.com> wrote in message
news:3Jx4e.25741$k66.15806@.trnddc03...
> Hari:
> Thanks for your quick response.
> I already tried this and I am getting data type error.
> Thx
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%234kWKteOFHA.3444@.tk2msftngp13.phx.gbl...
>sql

database in SQL2K need help in upading/adding 2 memo columns

I have database in SQL2K. I have a memo column called memo1. I want to
update memo with "dicount % "+memo1.
I want to insert "discount %" in front of memo1.
This is going to be just one time shot. There are more 20,000 records need
to be updated.
I would appreciate if someone can help me.
I can run VB program and script so if anyone can help me to create something
in VB, if it is not possible in SQL2K,
I would really appreciate.
ThanksHi,
IN SQL 2000 you could write a simple Update statement
Update Tablename
set memo1 = 'discount % ' + memo1
This will update all the 20K records in a single shot.
Did I answered your question?
Thanks
Hari
SQL Server MVP
"Mac" <mac@.hotmail.com> wrote in message
news:u9x4e.14059$Fh2.5711@.trnddc04...
>I have database in SQL2K. I have a memo column called memo1. I want to
>update memo with "dicount % "+memo1.
> I want to insert "discount %" in front of memo1.
> This is going to be just one time shot. There are more 20,000 records
> need to be updated.
> I would appreciate if someone can help me.
> I can run VB program and script so if anyone can help me to create
> something in VB, if it is not possible in SQL2K,
> I would really appreciate.
> Thanks
>|||Hari:
Thanks for your quick response.
I already tried this and I am getting data type error.
Thx
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%234kWKteOFHA.3444@.tk2msftngp13.phx.gbl...
> Hi,
> IN SQL 2000 you could write a simple Update statement
> Update Tablename
> set memo1 = 'discount % ' + memo1
> This will update all the 20K records in a single shot.
> Did I answered your question?
> Thanks
> Hari
> SQL Server MVP
> "Mac" <mac@.hotmail.com> wrote in message
> news:u9x4e.14059$Fh2.5711@.trnddc04...
>>I have database in SQL2K. I have a memo column called memo1. I want to
>>update memo with "dicount % "+memo1.
>> I want to insert "discount %" in front of memo1.
>> This is going to be just one time shot. There are more 20,000 records
>> need to be updated.
>> I would appreciate if someone can help me.
>> I can run VB program and script so if anyone can help me to create
>> something in VB, if it is not possible in SQL2K,
>> I would really appreciate.
>> Thanks
>|||Mac,
Looks like your datatype is TEXT. Inthat case you need to use UPDATETEXT
command to update.
See the below archive posts:-
http://groups-beta.google.com/group/comp.databases.ms-sqlserver/msg/11549ff4637eff45
Thanks
Hari
SQL Server MVP
"Mac" <mac@.hotmail.com> wrote in message
news:3Jx4e.25741$k66.15806@.trnddc03...
> Hari:
> Thanks for your quick response.
> I already tried this and I am getting data type error.
> Thx
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%234kWKteOFHA.3444@.tk2msftngp13.phx.gbl...
>> Hi,
>> IN SQL 2000 you could write a simple Update statement
>> Update Tablename
>> set memo1 = 'discount % ' + memo1
>> This will update all the 20K records in a single shot.
>> Did I answered your question?
>> Thanks
>> Hari
>> SQL Server MVP
>> "Mac" <mac@.hotmail.com> wrote in message
>> news:u9x4e.14059$Fh2.5711@.trnddc04...
>>I have database in SQL2K. I have a memo column called memo1. I want to
>>update memo with "dicount % "+memo1.
>> I want to insert "discount %" in front of memo1.
>> This is going to be just one time shot. There are more 20,000 records
>> need to be updated.
>> I would appreciate if someone can help me.
>> I can run VB program and script so if anyone can help me to create
>> something in VB, if it is not possible in SQL2K,
>> I would really appreciate.
>> Thanks
>>
>

database in simple mode yet txn log file can be out of diskspace ?

Hi ,

I have set up my database to be using "Simple" mode. This will not log any
txns ?

I have got the err message saying the log file is full and i need to do a
txn log backup

i do not understand why , could anyone kindly advise ?

tks & rdgs

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200510/1Having you database in SIMPLE recovery mode does NOT mean that
transactions are not logged. All it means is that all committed
transactions are removed from the T-log when a checkpoint occurs. So if
you run lot's of and especially longrunning transactions, it still can
happen that your T-log grows out of space.

M

Database in recovery

I have a database currently showing to be in recovery. I have not been able to find a GUI method to monitor the progress (to determine when it might complete) so is there a command line T-SQL method to monitor the recovery process?

If this is 2005, this might be what you need:

select der.session_id, der.command, der.status, der.percent_complete, *
from sys.dm_exec_requests as der

It works for other types of commands that have known progress indicators. If not, then I really don't think it tells you. You might also check the error log to see how long it took the previous time...

|||

Thanks Louis! It worked fine and the database eventually did recover.

|||Thanks Louis, it works for me too..

Database In Recovery

What does it actually mean when the database is in recovery? My database has been in recovery overnight. What is normally going on in this situation. Should I wait or take more drastic action?

when your database is marked as recovering it is rolling forward all the committed transations and rolling back the uncommitted transactions......consider this scenario,when you do some bulk insert operations for many hrs and if you want to kill the process it will be roll backing state for hrs.....and if you abruptly restart the sql services it wud be in marked as recovering state and will come to normal if it fully recovers.....which is nothing but rolling forward and roll back operations

guys, pls correct me if am wrong !!!

|||

Is the state reported RESTORING or RECOVERING? They have different meanings and actions.

Thanks,

|||

Based on below I am saying Recovering.

|||

I'm guessing that you tried to paste a screenshot?

Can you tell me:

What version (and SP) of SQL you're running

Where you are looking to find the state

exactly what it says

What happened to the database leading up to this state (i.e. did the server crash and restart, did you restore the database? if so, what command did you use?

One common mistake is to use RESTORE WITH NORECOVERY on the last restore, which will leave the database in a RESTORING state until you run RESTORE WITH RECOVERY on it.

|||

In SQL Server Management Studio when I expand my databases folder it says (In Recovery) in parenthesis next to database name. This apparently was caused as a result of a server crash and restart due to device driver problem. I have tried to run RESTORE WITH RECOVERY, but that or anything else I try with the database tells me I can't do anything until recovery is complete.

|||

I left off version info from previous reply.

Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.832
Operating System 5.1.2600|||I actually have three databases that have been (In Recovery). Two of the databases were my Report Services "ReportServer" and "ReportServerTempDB". My other database has finished, but I have not checked out yet. The "ReportServer" database now says (Suspect) and my "ReportServerTempDB" still says (In Recovery).

|||

refer, http://deepakinsql.blogspot.com/2007/08/how-to-recover-from-suspect-database.html to bring the suspect database back to normalcy.......if the db is marked you cannot use the db.......check if there is sufficient disk space for the log file to grow and ensure that the data and log files are not accessed by any antivirus process etc.....those might be the reason for the db marked as suspect..........

|||

Actually, it sounds like your databases are recovering and coming online.

What we need to look at is why it is taking so long.

Are there hardware errors being reported which could be slowing things down?

Were there long-running transactions which would take a long time to roll back?

Database In Recovery

What does it actually mean when the database is in recovery? My database has been in recovery overnight. What is normally going on in this situation. Should I wait or take more drastic action?

when your database is marked as recovering it is rolling forward all the committed transations and rolling back the uncommitted transactions......consider this scenario,when you do some bulk insert operations for many hrs and if you want to kill the process it will be roll backing state for hrs.....and if you abruptly restart the sql services it wud be in marked as recovering state and will come to normal if it fully recovers.....which is nothing but rolling forward and roll back operations

guys, pls correct me if am wrong !!!

|||

Is the state reported RESTORING or RECOVERING? They have different meanings and actions.

Thanks,

|||

Based on below I am saying Recovering.

|||

I'm guessing that you tried to paste a screenshot?

Can you tell me:

What version (and SP) of SQL you're running

Where you are looking to find the state

exactly what it says

What happened to the database leading up to this state (i.e. did the server crash and restart, did you restore the database? if so, what command did you use?

One common mistake is to use RESTORE WITH NORECOVERY on the last restore, which will leave the database in a RESTORING state until you run RESTORE WITH RECOVERY on it.

|||

In SQL Server Management Studio when I expand my databases folder it says (In Recovery) in parenthesis next to database name. This apparently was caused as a result of a server crash and restart due to device driver problem. I have tried to run RESTORE WITH RECOVERY, but that or anything else I try with the database tells me I can't do anything until recovery is complete.

|||

I left off version info from previous reply.

Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.832
Operating System 5.1.2600|||I actually have three databases that have been (In Recovery). Two of the databases were my Report Services "ReportServer" and "ReportServerTempDB". My other database has finished, but I have not checked out yet. The "ReportServer" database now says (Suspect) and my "ReportServerTempDB" still says (In Recovery).|||

refer, http://deepakinsql.blogspot.com/2007/08/how-to-recover-from-suspect-database.html to bring the suspect database back to normalcy.......if the db is marked you cannot use the db.......check if there is sufficient disk space for the log file to grow and ensure that the data and log files are not accessed by any antivirus process etc.....those might be the reason for the db marked as suspect..........

|||

Actually, it sounds like your databases are recovering and coming online.

What we need to look at is why it is taking so long.

Are there hardware errors being reported which could be slowing things down?

Were there long-running transactions which would take a long time to roll back?

sql

Database In Recovery

Hello all,

Is there a way to check the status of a database in recovery. Like how far along it may be. If not is there a way to stop a database in recovery and just drop it.

Thanks in advance,

Mike

There should be progress messages in the errorlog indicating percent complete and how long it is expected to continue?

What version of SQL are you running?

|||

Sorry im running SQL SERVER 2005 Standard Edition.

And you were correct it is in there. It looks like im looking at 9 hours to recover. Is there a way to stop the recovery? I can drop the database and reload the tables faster than that.

Thanks again,

Mike

|||

The only way to prevent recovery is to stop the server and delete or rename one of the database files. Then you can drop it after it comes up recovery pending on the server restart.

What phase of recovery is the database in? Is there just an enormous transaction to rollback?

|||

The recovery is in phase 2. The trans logs are about 58GB. I think i will do what you said that way I can just drop and create.

Thank You,

Mike

|||

Having same problem with database being in recovery. Has been running for a number of hours.

Below is message that repeats frequently in log file. However, I don't really know how to interpret it.

Process 16:0:0 (0xb78) Worker 0x03D880E8 appears to be non-yielding on Scheduler 1. Thread creation time: 12831081295625. Approx Thread CPU Used: kernel 265 ms, user 76778796 ms. Process Utilization 49%. System Idle 49%. Interval: 76832057 ms.

Any advise would be appreciated.

Database in recovery

Our server ran out of disk space during a stored procedure process. We were able to free up some space, stopped all SQL services, restarted all SQL services and now SQL Management Studio shows the database to be in recovery. Is there a way to monitor this to ensure progress is being made and to estimate when it might complete?

I don't know it can be checked programatically but you can sql error log where you recovery progress will be recorded.

Looks like you guys shutdown the servers in the middle of big transaction....

There will no other option than waiting to recover...

|||

There is a way, via T-SQL to view process progress but apparently not via GUI. The database did eventually recover and we have submitted for a new server as it ran out of disk space during a stored procedure run.

Thanks!

Database in recovery

Hi,
I'm not that familiar with SQL Server, just mainly use it for
development. Anyway I was modifying some keys in some tables last
night. Everything went fine and I continued to use the database.
When i woke this morning and turned on the computer, my database
was in recovery. I am unable to do anything to get it back.
I have tried a few things to alter the database, (ie. set offline, set
single user) but I get an error saying
"Msg 5011, Level 14, State 7, Line 1
User does not have permission to alter database 'trace05e' or the
database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed."
when I do an sp_who I get
spid ecid status loginame
hostname
blk dbname
cmd request_id
-- -- --
------
------
------
-- --
1 0 background sa
0 NULL
RESOURCE MONITOR 0
2 0 suspended sa
0 NULL
LOG WRITER 0
3 0 background sa
0 NULL
LAZY WRITER 0
4 0 background sa
0 master
UNKNOWN TOKEN 0
5 0 background sa
0 NULL
LOCK MONITOR 0
6 0 sleeping sa
0 master
TASK MANAGER 0
7 0 background sa
0 master
TRACE QUEUE TASK 0
8 0 sleeping sa
0 NULL
UNKNOWN TOKEN 0
9 0 background sa
0 master
BRKR TASK 0
10 0 background sa
0 master
TASK MANAGER 0
11 0 suspended sa
0 master
CHECKPOINT 0
17 0 background sa
0 NULL
UNKNOWN TOKEN 0
19 0 background sa
0 master
BRKR EVENT HNDLR 0
20 0 background sa
0 master
BRKR TASK 0
51 0 sleeping NT AUTHORITY\SYSTEM
computer
0 ReportServer$sqlexpress
AWAITING COMMAND 0
53 0 sleeping computer\User
computer
0 master
AWAITING
COMMAND 0
54 0 runnable computer\User
computer
0
master
SELECT
0
56 0 sleeping computer\User
I can't find any commands to kill the last three processes.
I need to know two things.
1) How can I find out why my database went into recovery mode?
2) How can I get my database back into normal mode?
Thanks a million in advance
JD
I managed to kill some processes and now I have two left, the one I am
using and one more, which is needed as a transport.
i still cannot execute this statment
RESTORE DATABASE trace05e
WITH RECOVERY
I get
Msg 3101, Level 16, State 1, Line 3
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
|||Hi
I can only guess. When you start SQL Server it does perfom the opeartion (in
every databases on the server) 'recovering' by using CHECKPOINT command that
means that all transactions are resided in the cache should be REDO/UNDO
depends on its status.
<the_kiddie98@.yahoo.com> wrote in message
news:1141293232.548857.223880@.t39g2000cwt.googlegr oups.com...
> Hi,
> I'm not that familiar with SQL Server, just mainly use it for
> development. Anyway I was modifying some keys in some tables last
> night. Everything went fine and I continued to use the database.
> When i woke this morning and turned on the computer, my database
> was in recovery. I am unable to do anything to get it back.
> I have tried a few things to alter the database, (ie. set offline, set
> single user) but I get an error saying
> "Msg 5011, Level 14, State 7, Line 1
> User does not have permission to alter database 'trace05e' or the
> database does not exist.
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed."
> when I do an sp_who I get
> spid ecid status loginame
> hostname
> blk dbname
> cmd request_id
> -- -- --
> ------
> ------
> --
> ------
> -- --
> 1 0 background sa
>
> 0 NULL
> RESOURCE MONITOR 0
> 2 0 suspended sa
>
> 0 NULL
> LOG WRITER 0
> 3 0 background sa
>
> 0 NULL
> LAZY WRITER 0
> 4 0 background sa
>
> 0 master
> UNKNOWN TOKEN 0
> 5 0 background sa
>
> 0 NULL
> LOCK MONITOR 0
> 6 0 sleeping sa
>
> 0 master
> TASK MANAGER 0
> 7 0 background sa
>
> 0 master
> TRACE QUEUE TASK 0
> 8 0 sleeping sa
>
> 0 NULL
> UNKNOWN TOKEN 0
> 9 0 background sa
>
> 0 master
> BRKR TASK 0
> 10 0 background sa
>
> 0 master
> TASK MANAGER 0
> 11 0 suspended sa
>
> 0 master
> CHECKPOINT 0
> 17 0 background sa
>
> 0 NULL
> UNKNOWN TOKEN 0
> 19 0 background sa
>
> 0 master
> BRKR EVENT HNDLR 0
> 20 0 background sa
>
> 0 master
> BRKR TASK 0
> 51 0 sleeping NT AUTHORITY\SYSTEM
> computer
> 0 ReportServer$sqlexpress
> AWAITING COMMAND 0
> 53 0 sleeping computer\User
> computer
> 0 master
> AWAITING
> COMMAND 0
> 54 0 runnable computer\User
> computer
> 0
> master
> SELECT
> 0
> 56 0 sleeping computer\User
> I can't find any commands to kill the last three processes.
> I need to know two things.
> 1) How can I find out why my database went into recovery mode?
> 2) How can I get my database back into normal mode?
> Thanks a million in advance
> JD
>
|||SQL Server shouldn't let anyone in to the database if it is in recovery state. Perhaps it is only a
refresh problem in EM/SSMS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<the_kiddie98@.yahoo.com> wrote in message
news:1141293565.559941.65460@.v46g2000cwv.googlegro ups.com...
>I managed to kill some processes and now I have two left, the one I am
> using and one more, which is needed as a transport.
> i still cannot execute this statment
> RESTORE DATABASE trace05e
> WITH RECOVERY
> I get
> Msg 3101, Level 16, State 1, Line 3
> Exclusive access could not be obtained because the database is in use.
> Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
>
|||Hi Uri,
Sorry for my ignorance but what does that mean. Is there a way I
can rollback the logs to a certain point in time?
Is there any way outside of sql server that I can gain access to
the stored procedures inside in the mdf file?
Thanks
JD
Uri Dimant wrote:
> Hi
> I can only guess. When you start SQL Server it does perfom the opeartion (in
> every databases on the server) 'recovering' by using CHECKPOINT command that
> means that all transactions are resided in the cache should be REDO/UNDO
> depends on its status.
>
|||nope it isn't a refresh error as I have rebooted a few times and I am
still unable to get the database to work.
I have now resorted to removing the instance and re-installing it and
trying to get back an older database. I can't run any commands like
dbcc checkdb even on master, there seems to be invisible connections
all over the place.

Database in recovery

Hi,
I'm not that familiar with SQL Server, just mainly use it for
development. Anyway I was modifying some keys in some tables last
night. Everything went fine and I continued to use the database.
When i woke this morning and turned on the computer, my database
was in recovery. I am unable to do anything to get it back.
I have tried a few things to alter the database, (ie. set offline, set
single user) but I get an error saying
"Msg 5011, Level 14, State 7, Line 1
User does not have permission to alter database 'trace05e' or the
database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed."
when I do an sp_who I get
spid ecid status loginame
hostname
blk dbname
cmd request_id
-- -- --
------
------
--
------
-- --
1 0 background sa
0 NULL
RESOURCE MONITOR 0
2 0 suspended sa
0 NULL
LOG WRITER 0
3 0 background sa
0 NULL
LAZY WRITER 0
4 0 background sa
0 master
UNKNOWN TOKEN 0
5 0 background sa
0 NULL
LOCK MONITOR 0
6 0 sleeping sa
0 master
TASK MANAGER 0
7 0 background sa
0 master
TRACE QUEUE TASK 0
8 0 sleeping sa
0 NULL
UNKNOWN TOKEN 0
9 0 background sa
0 master
BRKR TASK 0
10 0 background sa
0 master
TASK MANAGER 0
11 0 suspended sa
0 master
CHECKPOINT 0
17 0 background sa
0 NULL
UNKNOWN TOKEN 0
19 0 background sa
0 master
BRKR EVENT HNDLR 0
20 0 background sa
0 master
BRKR TASK 0
51 0 sleeping NT AUTHORITY\SYSTEM
computer
0 ReportServer$sqlexpress
AWAITING COMMAND 0
53 0 sleeping computer\User
computer
0 master
AWAITING
COMMAND 0
54 0 runnable computer\User
computer
0
master
SELECT
0
56 0 sleeping computer\User
I can't find any commands to kill the last three processes.
I need to know two things.
1) How can I find out why my database went into recovery mode?
2) How can I get my database back into normal mode?
Thanks a million in advance
JDI managed to kill some processes and now I have two left, the one I am
using and one more, which is needed as a transport.
i still cannot execute this statment
RESTORE DATABASE trace05e
WITH RECOVERY
I get
Msg 3101, Level 16, State 1, Line 3
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.|||Hi
I can only guess. When you start SQL Server it does perfom the opeartion (in
every databases on the server) 'recovering' by using CHECKPOINT command that
means that all transactions are resided in the cache should be REDO/UNDO
depends on its status.
<the_kiddie98@.yahoo.com> wrote in message
news:1141293232.548857.223880@.t39g2000cwt.googlegroups.com...
> Hi,
> I'm not that familiar with SQL Server, just mainly use it for
> development. Anyway I was modifying some keys in some tables last
> night. Everything went fine and I continued to use the database.
> When i woke this morning and turned on the computer, my database
> was in recovery. I am unable to do anything to get it back.
> I have tried a few things to alter the database, (ie. set offline, set
> single user) but I get an error saying
> "Msg 5011, Level 14, State 7, Line 1
> User does not have permission to alter database 'trace05e' or the
> database does not exist.
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed."
> when I do an sp_who I get
> spid ecid status loginame
> hostname
> blk dbname
> cmd request_id
> -- -- --
> ------
> ------
> --
> ------
> -- --
> 1 0 background sa
>
> 0 NULL
> RESOURCE MONITOR 0
> 2 0 suspended sa
>
> 0 NULL
> LOG WRITER 0
> 3 0 background sa
>
> 0 NULL
> LAZY WRITER 0
> 4 0 background sa
>
> 0 master
> UNKNOWN TOKEN 0
> 5 0 background sa
>
> 0 NULL
> LOCK MONITOR 0
> 6 0 sleeping sa
>
> 0 master
> TASK MANAGER 0
> 7 0 background sa
>
> 0 master
> TRACE QUEUE TASK 0
> 8 0 sleeping sa
>
> 0 NULL
> UNKNOWN TOKEN 0
> 9 0 background sa
>
> 0 master
> BRKR TASK 0
> 10 0 background sa
>
> 0 master
> TASK MANAGER 0
> 11 0 suspended sa
>
> 0 master
> CHECKPOINT 0
> 17 0 background sa
>
> 0 NULL
> UNKNOWN TOKEN 0
> 19 0 background sa
>
> 0 master
> BRKR EVENT HNDLR 0
> 20 0 background sa
>
> 0 master
> BRKR TASK 0
> 51 0 sleeping NT AUTHORITY\SYSTEM
> computer
> 0 ReportServer$sqlexpress
> AWAITING COMMAND 0
> 53 0 sleeping computer\User
> computer
> 0 master
> AWAITING
> COMMAND 0
> 54 0 runnable computer\User
> computer
> 0
> master
> SELECT
> 0
> 56 0 sleeping computer\User
> I can't find any commands to kill the last three processes.
> I need to know two things.
> 1) How can I find out why my database went into recovery mode?
> 2) How can I get my database back into normal mode?
> Thanks a million in advance
> JD
>|||SQL Server shouldn't let anyone in to the database if it is in recovery state. Perhaps it is only a
refresh problem in EM/SSMS?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<the_kiddie98@.yahoo.com> wrote in message
news:1141293565.559941.65460@.v46g2000cwv.googlegroups.com...
>I managed to kill some processes and now I have two left, the one I am
> using and one more, which is needed as a transport.
> i still cannot execute this statment
> RESTORE DATABASE trace05e
> WITH RECOVERY
> I get
> Msg 3101, Level 16, State 1, Line 3
> Exclusive access could not be obtained because the database is in use.
> Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
>|||Hi Uri,
Sorry for my ignorance but what does that mean. Is there a way I
can rollback the logs to a certain point in time?
Is there any way outside of sql server that I can gain access to
the stored procedures inside in the mdf file?
Thanks
JD
Uri Dimant wrote:
> Hi
> I can only guess. When you start SQL Server it does perfom the opeartion (in
> every databases on the server) 'recovering' by using CHECKPOINT command that
> means that all transactions are resided in the cache should be REDO/UNDO
> depends on its status.
>|||nope it isn't a refresh error as I have rebooted a few times and I am
still unable to get the database to work.
I have now resorted to removing the instance and re-installing it and
trying to get back an older database. I can't run any commands like
dbcc checkdb even on master, there seems to be invisible connections
all over the place.