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