Database level lock: 2012

Thursday, March 29, 2012

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.

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.

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....?

how about you ?

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

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!

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

|||

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.

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....?

how about you ?

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

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!

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

|||

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.

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....?

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

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!

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.
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.

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?

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.

|||

I left off version info from previous reply.

|||

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.

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 stat
e. 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 th
at
> 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.sql

Database in Recovering/suspect mode

Hi,
We have database of around 500 GB. Y'day nite we got a error msg saying
unable to write error to errorlog file.We were not able to login to the
sqlserver this morning.
So we restarted our server. It went to recover mode & started recovering
the database. It was fine till 96% then
we got a error msg
"Could not redo log record (635160:1109455:186),
for transaction ID (0:733497362), on page (4:840931), database 'VADI_NFDW'
(8). Page: LSN = (635147:63129:440), type = 2. Log: OpCode = 3, context 3,
PrevPageLSN: (635160:1100226:202)."
and one more msg as
"Error while redoing logged operation in database 'VADI_NFDW'. Error at log
record ID (635160:1109455:186)"
How could we solve this problem...It's our production db...
Thanks
Muthu
You need to contact PSS to help you with this.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"muthu" <muthu@.discussions.microsoft.com> wrote in message
news:DFB0D7F3-5236-48B9-8A4B-87BB0C1F8D24@.microsoft.com...
> Hi,
> We have database of around 500 GB. Y'day nite we got a error msg saying
> unable to write error to errorlog file.We were not able to login to the
> sqlserver this morning.
> So we restarted our server. It went to recover mode & started recovering
> the database. It was fine till 96% then
> we got a error msg
> "Could not redo log record (635160:1109455:186),
> for transaction ID (0:733497362), on page (4:840931), database 'VADI_NFDW'
> (8). Page: LSN = (635147:63129:440), type = 2. Log: OpCode = 3, context 3,
> PrevPageLSN: (635160:1100226:202)."
> and one more msg as
> "Error while redoing logged operation in database 'VADI_NFDW'. Error at
log
> record ID (635160:1109455:186)"
> How could we solve this problem...It's our production db...
> Thanks
> Muthu
>

Database in Recovering/suspect mode

Hi,
We have database of around 500 GB. Y'day nite we got a error msg saying
unable to write error to errorlog file.We were not able to login to the
sqlserver this morning.
So we restarted our server. It went to recover mode & started recovering
the database. It was fine till 96% then
we got a error msg
"Could not redo log record (635160:1109455:186),
for transaction ID (0:733497362), on page (4:840931), database 'VADI_NFDW'
(8). Page: LSN = (635147:63129:440), type = 2. Log: OpCode = 3, context 3,
PrevPageLSN: (635160:1100226:202)."
and one more msg as
"Error while redoing logged operation in database 'VADI_NFDW'. Error at log
record ID (635160:1109455:186)"
How could we solve this problem...It's our production db...
Thanks
MuthuYou need to contact PSS to help you with this.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"muthu" <muthu@.discussions.microsoft.com> wrote in message
news:DFB0D7F3-5236-48B9-8A4B-87BB0C1F8D24@.microsoft.com...
> Hi,
> We have database of around 500 GB. Y'day nite we got a error msg saying
> unable to write error to errorlog file.We were not able to login to the
> sqlserver this morning.
> So we restarted our server. It went to recover mode & started recovering
> the database. It was fine till 96% then
> we got a error msg
> "Could not redo log record (635160:1109455:186),
> for transaction ID (0:733497362), on page (4:840931), database 'VADI_NFDW'
> (8). Page: LSN = (635147:63129:440), type = 2. Log: OpCode = 3, context 3,
> PrevPageLSN: (635160:1100226:202)."
> and one more msg as
> "Error while redoing logged operation in database 'VADI_NFDW'. Error at
log
> record ID (635160:1109455:186)"
> How could we solve this problem...It's our production db...
> Thanks
> Muthu
>

Database in Recovering/suspect mode

Hi,
We have database of around 500 GB. Y'day nite we got a error msg saying
unable to write error to errorlog file.We were not able to login to the
sqlserver this morning.
So we restarted our server. It went to recover mode & started recovering
the database. It was fine till 96% then
we got a error msg
"Could not redo log record (635160:1109455:186),
for transaction ID (0:733497362), on page (4:840931), database 'VADI_NFDW'
(8). Page: LSN = (635147:63129:440), type = 2. Log: OpCode = 3, context 3,
PrevPageLSN: (635160:1100226:202)."
and one more msg as
"Error while redoing logged operation in database 'VADI_NFDW'. Error at log
record ID (635160:1109455:186)"
How could we solve this problem...It's our production db...
Thanks
MuthuYou need to contact PSS to help you with this.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"muthu" <muthu@.discussions.microsoft.com> wrote in message
news:DFB0D7F3-5236-48B9-8A4B-87BB0C1F8D24@.microsoft.com...
> Hi,
> We have database of around 500 GB. Y'day nite we got a error msg saying
> unable to write error to errorlog file.We were not able to login to the
> sqlserver this morning.
> So we restarted our server. It went to recover mode & started recovering
> the database. It was fine till 96% then
> we got a error msg
> "Could not redo log record (635160:1109455:186),
> for transaction ID (0:733497362), on page (4:840931), database 'VADI_NFDW'
> (8). Page: LSN = (635147:63129:440), type = 2. Log: OpCode = 3, context 3,
> PrevPageLSN: (635160:1100226:202)."
> and one more msg as
> "Error while redoing logged operation in database 'VADI_NFDW'. Error at
log
> record ID (635160:1109455:186)"
> How could we solve this problem...It's our production db...
> Thanks
> Muthu
>

Database in project

I'm using C#.Net 1.1 and i have a sample database inside my project (the actual database is in a folder in the project) and I simply want to connect tot the database inside the project and select and process data. i'm normally connecting to external databases so i'm kind of at a loss as to how i should connect to the database in the project (it's the Northwind Database and I'm using sql server 200).

any advice greatly appreciated.

In asp.net 2.0 you would add the database to the app_code folder

In asp.net 1.1 youll need to attach the db to sql server and query using a connection string.

Do you need help in attaching the db to sql server?

Cheers
Gregor

|||Yes, how do I attach the database to sql server?|||

Hi Myron

Do you have the database as an mdf file or do you have database scripts?

Cheers
Gregor

|||i have the database as an mdf file (Northwind.mdf)|||

The latest on this: in VS 2003 i went to Server Explorer to "Add Connection" . I am entering the following values (please not <addconnection> tags indicate start and end:

Select or enter server name: (localhost)

Enter information to log onto server: chose "Use Windows NT integrated security)

Select the database on the server:

Attach a db file...: Northwind

Using the filename: C:\SQL Server 2000 Sample Databases\NORTHWND.MDF

</AddConnection>

But when i go to test the connection I am getting the following error:

<ERROR>

Test connection failed because of an error in initializing provider. [DBNETLIB].[ConnectionOpen (Connect()).]SqlServer does not exist or access denied.

</ERROR>

do you know that this means? the db exists so i now assume that I have to check the rights on the database...

Any help on this greatly appreciated

Database in loading state

I have several databases in a loading state, I have tried using the detach
option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
SQLState: 42000)
Error 15010: The database 'name' does not exist. Use sp_helpdb to show
available databases.
When using the sp_helpdb command, of course it only shows the databases that
are not in the loading state.
I need to get the databases that in loading state, detached or removed
completely. My nightly backups are failing since they can not attach to the
databases (loading)
All assistance in this matter is greatly appreciated and thanks in advance
Forgot to advise. Using SQL 2000 with SP 3 on Win2K server
"scuba79" wrote:

> I have several databases in a loading state, I have tried using the detach
> option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
> SQLState: 42000)
> Error 15010: The database 'name' does not exist. Use sp_helpdb to show
> available databases.
> When using the sp_helpdb command, of course it only shows the databases that
> are not in the loading state.
> I need to get the databases that in loading state, detached or removed
> completely. My nightly backups are failing since they can not attach to the
> databases (loading)
> All assistance in this matter is greatly appreciated and thanks in advance
|||Never mind... I found the solution... Forgot that I needed to use the
RECOVERY DATABASE WITH RECOVERY command
"scuba79" wrote:

> I have several databases in a loading state, I have tried using the detach
> option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
> SQLState: 42000)
> Error 15010: The database 'name' does not exist. Use sp_helpdb to show
> available databases.
> When using the sp_helpdb command, of course it only shows the databases that
> are not in the loading state.
> I need to get the databases that in loading state, detached or removed
> completely. My nightly backups are failing since they can not attach to the
> databases (loading)
> All assistance in this matter is greatly appreciated and thanks in advance
sql

Database in loading state

I have several databases in a loading state, I have tried using the detach
option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
SQLState: 42000)
Error 15010: The database 'name' does not exist. Use sp_helpdb to show
available databases.
When using the sp_helpdb command, of course it only shows the databases that
are not in the loading state.
I need to get the databases that in loading state, detached or removed
completely. My nightly backups are failing since they can not attach to the
databases (loading)
All assistance in this matter is greatly appreciated and thanks in advanceForgot to advise. Using SQL 2000 with SP 3 on Win2K server
"scuba79" wrote:
> I have several databases in a loading state, I have tried using the detach
> option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
> SQLState: 42000)
> Error 15010: The database 'name' does not exist. Use sp_helpdb to show
> available databases.
> When using the sp_helpdb command, of course it only shows the databases that
> are not in the loading state.
> I need to get the databases that in loading state, detached or removed
> completely. My nightly backups are failing since they can not attach to the
> databases (loading)
> All assistance in this matter is greatly appreciated and thanks in advance|||Never mind... I found the solution... Forgot that I needed to use the
RECOVERY DATABASE WITH RECOVERY command
"scuba79" wrote:
> I have several databases in a loading state, I have tried using the detach
> option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
> SQLState: 42000)
> Error 15010: The database 'name' does not exist. Use sp_helpdb to show
> available databases.
> When using the sp_helpdb command, of course it only shows the databases that
> are not in the loading state.
> I need to get the databases that in loading state, detached or removed
> completely. My nightly backups are failing since they can not attach to the
> databases (loading)
> All assistance in this matter is greatly appreciated and thanks in advance

Database in loading state

I have several databases in a loading state, I have tried using the detach
option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
SQLState: 42000)
Error 15010: The database 'name' does not exist. Use sp_helpdb to show
available databases.
When using the sp_helpdb command, of course it only shows the databases that
are not in the loading state.
I need to get the databases that in loading state, detached or removed
completely. My nightly backups are failing since they can not attach to the
databases (loading)
All assistance in this matter is greatly appreciated and thanks in advanceForgot to advise. Using SQL 2000 with SP 3 on Win2K server
"scuba79" wrote:

> I have several databases in a loading state, I have tried using the detach
> option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
> SQLState: 42000)
> Error 15010: The database 'name' does not exist. Use sp_helpdb to show
> available databases.
> When using the sp_helpdb command, of course it only shows the databases th
at
> are not in the loading state.
> I need to get the databases that in loading state, detached or removed
> completely. My nightly backups are failing since they can not attach to t
he
> databases (loading)
> All assistance in this matter is greatly appreciated and thanks in advance|||Never mind... I found the solution... Forgot that I needed to use the
RECOVERY DATABASE WITH RECOVERY command
"scuba79" wrote:

> I have several databases in a loading state, I have tried using the detach
> option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
> SQLState: 42000)
> Error 15010: The database 'name' does not exist. Use sp_helpdb to show
> available databases.
> When using the sp_helpdb command, of course it only shows the databases th
at
> are not in the loading state.
> I need to get the databases that in loading state, detached or removed
> completely. My nightly backups are failing since they can not attach to t
he
> databases (loading)
> All assistance in this matter is greatly appreciated and thanks in advance

database in load

Hello,

I am an administrator of a SQL server 2000 machine residing on a windows 2000 box. I tried restoring a database on this server from a backup file that i copied from another server. Halfway through the restore, the user comes up to me saying they did not want the database restored again. I stopped the restore process and since then it has not gone back to its previous state. Secondly it is grayed out with a (loading) text next to the database icon.

how did this happen and how do you suggest i solve this.

regards

This is because the restore process is not complete. Try dropping the database and restore the backup prior to this state as it will recreate the database.

Database in 'In recovery' mode

Hi,
There was the most awful case. During a update of a DB there was a
switching-off of power supplies. After switch on of a server the base has
passed in mode 'Suspect'. To restore base by standard way it was not
possible. Substitution of files and had turned out to open changes of the
status of base data.
Has executed substitution of files of a database. Has successfully opened a
database, structure - all is accessible.
Check of a file of a database on integrity is executed successfully, errors
are not present (DBCC CHECKDB).
I can not create backup, gives out the message that are opened deferred
transactions.
Also, at change of some objects there is a lag of a server that leads to
change of the status of database 'In Recovery'. In 2 and more hours the
status has not changed.
Whether it is necessary to wait for restoration? How to commit deferred tran?
Thank you for any help.
p.s. Backups very old :(Not sure if this is going to work ... a little bit risky but worth a try (I
guess)...
It seemed that some transaction are not clearing so try to rollback the
transaction and then try to backup the database and then try to turn the
database back online, so the command would look like:
USE master
GO
-- First set it to read only and rollback any active transaction
ALTER DATABASE [DatabaseName]
SET READ_ONLY WITH ROLLBACK IMMEDIATE
GO
-- Backup the database, hopefully it should work since there should not be
any active transaction
BACKUP DATABASE [DatabaseName]
TO DISK = '[FilePathAndName]'
WITH INIT
GO
-- Turn the database back online
ALTER DATABASE [DatabaseName]
SET ONLINE WITH ROLLBACK IMMEDIATE
GO
Hopefully that would work (but no guarantee...)
Lucas
"int64" wrote:
> Hi,
> There was the most awful case. During a update of a DB there was a
> switching-off of power supplies. After switch on of a server the base has
> passed in mode 'Suspect'. To restore base by standard way it was not
> possible. Substitution of files and had turned out to open changes of the
> status of base data.
> Has executed substitution of files of a database. Has successfully opened a
> database, structure - all is accessible.
> Check of a file of a database on integrity is executed successfully, errors
> are not present (DBCC CHECKDB).
> I can not create backup, gives out the message that are opened deferred
> transactions.
> Also, at change of some objects there is a lag of a server that leads to
> change of the status of database 'In Recovery'. In 2 and more hours the
> status has not changed.
> Whether it is necessary to wait for restoration? How to commit deferred tran?
> Thank you for any help.
> p.s. Backups very old :(
>

database in a suspect mode

We have a try to migrate a winnt server to another domain,
but had to backout. Both database on the sql server are in
a suspect mode after we backedout. What can I do to bring
them back online?Can I restore them from last night's
backup? Any experiance to be shared with me...Thanks very
muchDear dk,
use this one
sp_resetstatus [ @.DBName = ] 'database'
Regards
Faheem
NETWORK SOLUTION PAKISTAN.
>--Original Message--
>We have a try to migrate a winnt server to another
domain,
>but had to backout. Both database on the sql server are
in
>a suspect mode after we backedout. What can I do to bring
>them back online?Can I restore them from last night's
>backup? Any experiance to be shared with me...Thanks very
>much
>.
>sql

Database Import/Export Question

I tried to export a database which includes tables and stored procedures from development environment to a production environment. For unknown reasons only the tables were copied whereas stored procedures were not copied and no error message shown what happened. Does anybody have a clue? I did the same export to an XP machine and it went through properly.How did you export the data? Did you use DTS?
Maybe you will find my previous post listing the steps I use to export objects to be helpful:http://forums.asp.net/1037418/showpost.aspx
|||Thanks. I got it. Don't know why the import/export said 100% complete but nothing showed up even I refreshed the stroed procedures in Enterprise Manager. And after a while (actually I went to pull someone to see the situation) the SP shown. Weird?|||

vljw8202 wrote:
Thanks. I got it. Don't know why theimport/export said 100% complete but nothing showed up even I refreshedthe stroed procedures in Enterprise Manager. And after a while(actually I went to pull someone to see the situation) the SPshown. Weird?

Enterprise Manager is often annoyingly unaware of schema changes. What I typically do is right-click on the server name and chooseRefresh, do the same for the Database node, the specific database, andthen the Tables node and any other node which should berefreshed. The new objects will then show up.
|||I see. Thanks for your advice.

database import question

Hi all,
I have a database I maintain that has grown quite large. The part of it
I am interested in, is one paticular table that logs the history of
certain high volume "events". I have events back to 2002, and the
database is almost 3 Gigs. I rarely need to query more than 6 months
worth. Here is what I would like to do.
I'd like to split the database, and create an archive database. I want
to move everything before 2006 to this new database. Now, I know I can
do a complete backup, and then delete the stuff I don't need, but I
wanted to know if there is a way to import just the dates I need from
the current database. Of course, there is a timedate field to key off
of.
I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
2003 Server.
Thanks in advance.Hi there
There are several ways to do this. You can try to use DTS to move the data
over. Or you can use query analyzer. If you can use Query Analyzer, you can
actually use a query something like:
SELECT *
INTO [ArchiveDBName].dbo.[TableName]
FROM dbo.[OriginalDBName]
WHERE [DateStamp] < '1/1/2006'
And to delete the records from the original table:
DELETE FROM dbo.[OriginalDBName]
WHERE [DateStamp] < '1/1/2006'
However, it was not clear from you post if you have access to Query Analyzer.
Lucas
"mattdaddym@.gmail.com" wrote:
> Hi all,
> I have a database I maintain that has grown quite large. The part of it
> I am interested in, is one paticular table that logs the history of
> certain high volume "events". I have events back to 2002, and the
> database is almost 3 Gigs. I rarely need to query more than 6 months
> worth. Here is what I would like to do.
> I'd like to split the database, and create an archive database. I want
> to move everything before 2006 to this new database. Now, I know I can
> do a complete backup, and then delete the stuff I don't need, but I
> wanted to know if there is a way to import just the dates I need from
> the current database. Of course, there is a timedate field to key off
> of.
> I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> 2003 Server.
> Thanks in advance.
>|||I do have access to query analyzer. I was hoping for a way to do it in
the GUI since it creats the tables automatically when you do an import.
I don't know for sure, but I will assume if I use your command that I
will need to set the table up first. Not a big deal, though. I guess
I'm just being lazy. Thank you!
Lucas Kartawidjaja wrote:
> Hi there
> There are several ways to do this. You can try to use DTS to move the data
> over. Or you can use query analyzer. If you can use Query Analyzer, you can
> actually use a query something like:
> SELECT *
> INTO [ArchiveDBName].dbo.[TableName]
> FROM dbo.[OriginalDBName]
> WHERE [DateStamp] < '1/1/2006'
> And to delete the records from the original table:
> DELETE FROM dbo.[OriginalDBName]
> WHERE [DateStamp] < '1/1/2006'
> However, it was not clear from you post if you have access to Query Analyzer.
> Lucas
>
> "mattdaddym@.gmail.com" wrote:
> > Hi all,
> >
> > I have a database I maintain that has grown quite large. The part of it
> > I am interested in, is one paticular table that logs the history of
> > certain high volume "events". I have events back to 2002, and the
> > database is almost 3 Gigs. I rarely need to query more than 6 months
> > worth. Here is what I would like to do.
> >
> > I'd like to split the database, and create an archive database. I want
> > to move everything before 2006 to this new database. Now, I know I can
> > do a complete backup, and then delete the stuff I don't need, but I
> > wanted to know if there is a way to import just the dates I need from
> > the current database. Of course, there is a timedate field to key off
> > of.
> >
> > I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> > 2003 Server.
> >
> > Thanks in advance.
> >
> >|||Actually with the SELECT INTO statement the beauty of it is that you don't
need to create the table first. SELECT INTO will create it for you the table
structure will be the same as the original table.
Lucas
"mattdaddym@.gmail.com" wrote:
> I do have access to query analyzer. I was hoping for a way to do it in
> the GUI since it creats the tables automatically when you do an import.
> I don't know for sure, but I will assume if I use your command that I
> will need to set the table up first. Not a big deal, though. I guess
> I'm just being lazy. Thank you!
> Lucas Kartawidjaja wrote:
> > Hi there
> >
> > There are several ways to do this. You can try to use DTS to move the data
> > over. Or you can use query analyzer. If you can use Query Analyzer, you can
> > actually use a query something like:
> >
> > SELECT *
> > INTO [ArchiveDBName].dbo.[TableName]
> > FROM dbo.[OriginalDBName]
> > WHERE [DateStamp] < '1/1/2006'
> >
> > And to delete the records from the original table:
> >
> > DELETE FROM dbo.[OriginalDBName]
> > WHERE [DateStamp] < '1/1/2006'
> >
> > However, it was not clear from you post if you have access to Query Analyzer.
> >
> > Lucas
> >
> >
> > "mattdaddym@.gmail.com" wrote:
> >
> > > Hi all,
> > >
> > > I have a database I maintain that has grown quite large. The part of it
> > > I am interested in, is one paticular table that logs the history of
> > > certain high volume "events". I have events back to 2002, and the
> > > database is almost 3 Gigs. I rarely need to query more than 6 months
> > > worth. Here is what I would like to do.
> > >
> > > I'd like to split the database, and create an archive database. I want
> > > to move everything before 2006 to this new database. Now, I know I can
> > > do a complete backup, and then delete the stuff I don't need, but I
> > > wanted to know if there is a way to import just the dates I need from
> > > the current database. Of course, there is a timedate field to key off
> > > of.
> > >
> > > I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> > > 2003 Server.
> > >
> > > Thanks in advance.
> > >
> > >
>|||That is great. Thank you!
Lucas Kartawidjaja wrote:
> Actually with the SELECT INTO statement the beauty of it is that you don't
> need to create the table first. SELECT INTO will create it for you the table
> structure will be the same as the original table.
> Lucas
> "mattdaddym@.gmail.com" wrote:
> > I do have access to query analyzer. I was hoping for a way to do it in
> > the GUI since it creats the tables automatically when you do an import.
> > I don't know for sure, but I will assume if I use your command that I
> > will need to set the table up first. Not a big deal, though. I guess
> > I'm just being lazy. Thank you!
> > Lucas Kartawidjaja wrote:
> > > Hi there
> > >
> > > There are several ways to do this. You can try to use DTS to move the data
> > > over. Or you can use query analyzer. If you can use Query Analyzer, you can
> > > actually use a query something like:
> > >
> > > SELECT *
> > > INTO [ArchiveDBName].dbo.[TableName]
> > > FROM dbo.[OriginalDBName]
> > > WHERE [DateStamp] < '1/1/2006'
> > >
> > > And to delete the records from the original table:
> > >
> > > DELETE FROM dbo.[OriginalDBName]
> > > WHERE [DateStamp] < '1/1/2006'
> > >
> > > However, it was not clear from you post if you have access to Query Analyzer.
> > >
> > > Lucas
> > >
> > >
> > > "mattdaddym@.gmail.com" wrote:
> > >
> > > > Hi all,
> > > >
> > > > I have a database I maintain that has grown quite large. The part of it
> > > > I am interested in, is one paticular table that logs the history of
> > > > certain high volume "events". I have events back to 2002, and the
> > > > database is almost 3 Gigs. I rarely need to query more than 6 months
> > > > worth. Here is what I would like to do.
> > > >
> > > > I'd like to split the database, and create an archive database. I want
> > > > to move everything before 2006 to this new database. Now, I know I can
> > > > do a complete backup, and then delete the stuff I don't need, but I
> > > > wanted to know if there is a way to import just the dates I need from
> > > > the current database. Of course, there is a timedate field to key off
> > > > of.
> > > >
> > > > I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> > > > 2003 Server.
> > > >
> > > > Thanks in advance.
> > > >
> > > >
> >
> >

Thursday, March 29, 2012

Database is running slow after running DBCC Shrinkdatabase command

Database is read-only.

Database is read-only.

Database is read-only.

Database is read-only.

database in standby mode

Database in Standby / Readonly

Database in Standby / Readonly

Database in Standby / Readonly

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

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

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

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

Database in recovery

Database In Recovery

Database In Recovery

Database In Recovery

Database in recovery

Database in recovery

Database in recovery

Database in recovery

Database in Recovering/suspect mode

Database in Recovering/suspect mode

Database in Recovering/suspect mode

Database in project

Database in loading state

Database in loading state

Database in loading state

database in load

Database in 'In recovery' mode

database in a suspect mode

Database Import/Export Question

database import question

database import question

Database level lock

Blog Archive

About Me