Database hung after failure of large update query

I tried to run a large update query last night that did not complete.
But now I cannot access that database. In fact, when I click on Databases
in Enterprise Manager, that hangs and shows an hourglass.
I suspect that it failed due to a disc space. When I look at the process
status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working'
on that database.
I don't really need to complete the update (partial complete would be fine).
I just need to free up the database so I can work on the database.
Any thoughts?
- Jesse
Jesse A wrote:
> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on
> Databases in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the
> process status, I see a SPID that has a wait type of PAGEIOLatch_EX
> that is 'working' on that database.
> I don't really need to complete the update (partial complete would be
> fine). I just need to free up the database so I can work on the
> database.
> Any thoughts?
There is no such thing as a partial update unless you were using batches
to perform the updates in the first place. What likely happened is that
you ran out of space and SQL Server may be in the process of rolling
back the transaction (which can take just as long as the original update
took).
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||If u dont want any transaction for that particular SPID then u will kill the
particular process by KILL SPID
hope this will help
Herbert
"Jesse A" wrote:

> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on Databases
> in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the process
> status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working'
> on that database.
> I don't really need to complete the update (partial complete would be fine).
> I just need to free up the database so I can work on the database.
> Any thoughts?
> --
> - Jesse

Database hung after failure of large update query

I tried to run a large update query last night that did not complete.
But now I cannot access that database. In fact, when I click on Databases
in Enterprise Manager, that hangs and shows an hourglass.
I suspect that it failed due to a disc space. When I look at the process
status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working'
on that database.
I don't really need to complete the update (partial complete would be fine).
I just need to free up the database so I can work on the database.
Any thoughts?
--
- JesseJesse A wrote:
> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on
> Databases in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the
> process status, I see a SPID that has a wait type of PAGEIOLatch_EX
> that is 'working' on that database.
> I don't really need to complete the update (partial complete would be
> fine). I just need to free up the database so I can work on the
> database.
> Any thoughts?
There is no such thing as a partial update unless you were using batches
to perform the updates in the first place. What likely happened is that
you ran out of space and SQL Server may be in the process of rolling
back the transaction (which can take just as long as the original update
took).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||If u dont want any transaction for that particular SPID then u will kill the
particular process by KILL SPID
hope this will help
--
Herbert
"Jesse A" wrote:
> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on Databases
> in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the process
> status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working'
> on that database.
> I don't really need to complete the update (partial complete would be fine).
> I just need to free up the database so I can work on the database.
> Any thoughts?
> --
> - Jesse

Database hung after failure of large update query

I tried to run a large update query last night that did not complete.
But now I cannot access that database. In fact, when I click on Databases
in Enterprise Manager, that hangs and shows an hourglass.
I suspect that it failed due to a disc space. When I look at the process
status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working
'
on that database.
I don't really need to complete the update (partial complete would be fine).
I just need to free up the database so I can work on the database.
Any thoughts?
--
- JesseJesse A wrote:
> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on
> Databases in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the
> process status, I see a SPID that has a wait type of PAGEIOLatch_EX
> that is 'working' on that database.
> I don't really need to complete the update (partial complete would be
> fine). I just need to free up the database so I can work on the
> database.
> Any thoughts?
There is no such thing as a partial update unless you were using batches
to perform the updates in the first place. What likely happened is that
you ran out of space and SQL Server may be in the process of rolling
back the transaction (which can take just as long as the original update
took).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||If u dont want any transaction for that particular SPID then u will kill the
particular process by KILL SPID
hope this will help
Herbert
"Jesse A" wrote:

> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on Database
s
> in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the process
> status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'worki
ng'
> on that database.
> I don't really need to complete the update (partial complete would be fine
).
> I just need to free up the database so I can work on the database.
> Any thoughts?
> --
> - Jessesql

Database Files (file name)

I've detached a database and then reactached it specifing a new name for
the database within SQL Server 2000. But, if i right click and select
the database properties and then select the "Data Files" tab the file
name is still the same as the original database. How do i go about
changing that? Thanks in advance.
Vincent
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Hi Vincent
Changing the database name only changes one value, in the sysdatabases table
in the master database. It doesn't change any info in the database itself,
including the logical names associated with the database files which are
stored in the sysfiles table in the database itself.
To change the logical file names, you can use ALTER DATABASE... MODIFY FILE,
and specify a value for NEWNAME.
Please see the complete ALTER DATABASE syntax in the Books Online.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Vincent Cristiano" <vincec@.mail.com> wrote in message
news:eTK%23YXruEHA.1984@.TK2MSFTNGP14.phx.gbl...
> I've detached a database and then reactached it specifing a new name for
> the database within SQL Server 2000. But, if i right click and select
> the database properties and then select the "Data Files" tab the file
> name is still the same as the original database. How do i go about
> changing that? Thanks in advance.
> Vincent
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
sql

Database File Size

I have a Database which when I Right Click and go to Properties size is 52 GB

But the Size of MDF + NDF Files is 25 + 7 = 32 GB. Log file Size is 20 GB. So I am thinking -- Properties Size of DB includes size of Log Files too -- is that correct?

But when I do a Full Backup the .bak File Size is 26 GB -- does the Full Backup Shrink a DB ?

I thot Full Backup only Shrink the Log Files and could not find anywhere in BOL where it says BACKUP shrinks the empty space in Database -- can somebody confirm this?

Hi JaguarRDA,

Its because backup is always lower than original size of database, because backup copy only used pages.

|||

Yeah -- also Backups dont touch the Log Files -- that was a wrong statement on my behalf that they shrunk the log files

|||

JaguarRDA283361 wrote:

Yeah -- also Backups dont touch the Log Files --

Depends on the type of "recovery model", see

sql

database error -help

I'm using sqlserver 2000 and when i go to enterprise
manager and click on one of the database it is giving me
internet explorer script error. it is refering to
c:/programfiles/mssql2000/80/tools/bin/resources/1033/sqlmm
c.rll/tabs.html
Can anyone help please?Try switching from taskpad view to anything else, select
the database (should be no error), then go back to taskpad
view. Of the top of my head, this is the one issue I can
think of...
>--Original Message--
>I'm using sqlserver 2000 and when i go to enterprise
>manager and click on one of the database it is giving me
>internet explorer script error. it is refering to
>c:/programfiles/mssql2000/80/tools/bin/resources/1033/sqlm
m
>c.rll/tabs.html
>Can anyone help please?
>.
>

Database Diagrams in SQL 2005

I have installed SQL Server 2005 Standard Edition... but I am unable to use the database diagram designer. I have tried the right click and the only options I have are refresh and help. Looking at the node, it is an empty folder...

Have I not installed this correctly or am I missing an important step along the way?

Thanks in advance for any info!

Sam

Which version of SQL Server are you running (Can be determined with SELECT @.@.Version)

HTH, Jens Suessmeyer.

-
http://www.sqlserver2005.de
-

Database diagrams

I have just installed SQL server Express and am trying to create a database diagram. When I right click on database diagram, I just get the options to use help or to refresh. There is no option to create a new diagram,

Thanks,

Sarah

Express doesn't contain that option. You might try some of the freeware offerings to create database diagrams, or you can use Microsoft's Visio for Enterprise Architects.

Buck Woody

|||Can they not be created in the management studio?|||

Yes, but not against SQL Server Express.

Buck Woody

|||

I have now installed SQL server 2005 and need to be able to produce ERD's. Through the management studio, I do not ge the option to create new diagrams, I only get refresh or help. Should I be creating the some other way?

Thanks.

|||

No, you're in the right place. If you have installed a Standard Edition of SQL Server 2005 or higher, you will be able to create diagrams, but not against an Express database. Express databases don't have the ability to create the objects required for a database diagram. If you're using the 2005 tools to hit a 2000 database, you'll run into the same issue. You need to create 2000 diagrams in 2000.

|||i currently have the managemet studio express on my machine and there is support for creating database diagrams. if your are connecting to a remote server through the management studio it could be because of the security settings server side that will disallow the option. if you set up a local server it will give you the option

Database diagrams

Hi
I'm trying to view a SQL 2000 Diagram that was created by my colleage. When
I click on "Design Diagram" it appears blank (ie it does show any of the
table links). However if I create a diagram I can view that and my colleage I
can view the diagram that I've created and my colleage can also view it.
Any ideas.
TIA
Dwight
Dwight,
Did you apply Visual Studio Service Pack 6?
If so, this has happened to many people. I don't know if
there is a knowledge base article or supported fix for the
issue. Others have reported working around the issue by
installing an older version of mdt2df.dll
-Sue
On Mon, 10 Jan 2005 03:47:03 -0800, Dwight
<Dwight@.discussions.microsoft.com> wrote:

>Hi
>I'm trying to view a SQL 2000 Diagram that was created by my colleage. When
>I click on "Design Diagram" it appears blank (ie it does show any of the
>table links). However if I create a diagram I can view that and my colleage I
>can view the diagram that I've created and my colleage can also view it.
>Any ideas.
>TIA
>Dwight
|||Hi Sue,
Yes I have install VS SP6. I'll see if I can find an old version of
mdt2df.dll and reinstall it.
Thanks
"Sue Hoegemeier" wrote:

> Dwight,
> Did you apply Visual Studio Service Pack 6?
> If so, this has happened to many people. I don't know if
> there is a knowledge base article or supported fix for the
> issue. Others have reported working around the issue by
> installing an older version of mdt2df.dll
> -Sue
> On Mon, 10 Jan 2005 03:47:03 -0800, Dwight
> <Dwight@.discussions.microsoft.com> wrote:
>
>

Database diagrams

Hi
I'm trying to view a SQL 2000 Diagram that was created by my colleage. When
I click on "Design Diagram" it appears blank (ie it does show any of the
table links). However if I create a diagram I can view that and my colleage
I
can view the diagram that I've created and my colleage can also view it.
Any ideas.
TIA
DwightDwight,
Did you apply Visual Studio Service Pack 6?
If so, this has happened to many people. I don't know if
there is a knowledge base article or supported fix for the
issue. Others have reported working around the issue by
installing an older version of mdt2df.dll
-Sue
On Mon, 10 Jan 2005 03:47:03 -0800, Dwight
<Dwight@.discussions.microsoft.com> wrote:

>Hi
>I'm trying to view a SQL 2000 Diagram that was created by my colleage. When
>I click on "Design Diagram" it appears blank (ie it does show any of the
>table links). However if I create a diagram I can view that and my colleage
I
>can view the diagram that I've created and my colleage can also view it.
>Any ideas.
>TIA
>Dwight|||Hi Sue,
Yes I have install VS SP6. I'll see if I can find an old version of
mdt2df.dll and reinstall it.
Thanks
"Sue Hoegemeier" wrote:

> Dwight,
> Did you apply Visual Studio Service Pack 6?
> If so, this has happened to many people. I don't know if
> there is a knowledge base article or supported fix for the
> issue. Others have reported working around the issue by
> installing an older version of mdt2df.dll
> -Sue
> On Mon, 10 Jan 2005 03:47:03 -0800, Dwight
> <Dwight@.discussions.microsoft.com> wrote:
>
>

database diagrams

Hello all,

I upsized an access backend to sql 2005. In the process, the relationships were lost. When I go to the database in Sql, right click on database diagrams, all I get is refresh and a link to go to learn about database diagrams. How can I recreate the diagrams?

Thanks

You'd be better asking the question here: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1

-Jamie

Database diagrams

I have just installed SQL server Express and am trying to create a database diagram. When I right click on database diagram, I just get the options to use help or to refresh. There is no option to create a new diagram,

Thanks,

Sarah

Express doesn't contain that option. You might try some of the freeware offerings to create database diagrams, or you can use Microsoft's Visio for Enterprise Architects.

Buck Woody

|||Can they not be created in the management studio?|||

Yes, but not against SQL Server Express.

Buck Woody

|||

I have now installed SQL server 2005 and need to be able to produce ERD's. Through the management studio, I do not ge the option to create new diagrams, I only get refresh or help. Should I be creating the some other way?

Thanks.

|||

No, you're in the right place. If you have installed a Standard Edition of SQL Server 2005 or higher, you will be able to create diagrams, but not against an Express database. Express databases don't have the ability to create the objects required for a database diagram. If you're using the 2005 tools to hit a 2000 database, you'll run into the same issue. You need to create 2000 diagrams in 2000.

|||i currently have the managemet studio express on my machine and there is support for creating database diagrams. if your are connecting to a remote server through the management studio it could be because of the security settings server side that will disallow the option. if you set up a local server it will give you the option

Database diagrams

I have just installed SQL server Express and am trying to create a database diagram. When I right click on database diagram, I just get the options to use help or to refresh. There is no option to create a new diagram,

Thanks,

Sarah

Express doesn't contain that option. You might try some of the freeware offerings to create database diagrams, or you can use Microsoft's Visio for Enterprise Architects.

Buck Woody

|||Can they not be created in the management studio?|||

Yes, but not against SQL Server Express.

Buck Woody

|||

I have now installed SQL server 2005 and need to be able to produce ERD's. Through the management studio, I do not ge the option to create new diagrams, I only get refresh or help. Should I be creating the some other way?

Thanks.

|||

No, you're in the right place. If you have installed a Standard Edition of SQL Server 2005 or higher, you will be able to create diagrams, but not against an Express database. Express databases don't have the ability to create the objects required for a database diagram. If you're using the 2005 tools to hit a 2000 database, you'll run into the same issue. You need to create 2000 diagrams in 2000.

|||i currently have the managemet studio express on my machine and there is support for creating database diagrams. if your are connecting to a remote server through the management studio it could be because of the security settings server side that will disallow the option. if you set up a local server it will give you the option

Database diagrams

Hi
I'm trying to view a SQL 2000 Diagram that was created by my colleage. When
I click on "Design Diagram" it appears blank (ie it does show any of the
table links). However if I create a diagram I can view that and my colleage I
can view the diagram that I've created and my colleage can also view it.
Any ideas.
TIA
DwightDwight,
Did you apply Visual Studio Service Pack 6?
If so, this has happened to many people. I don't know if
there is a knowledge base article or supported fix for the
issue. Others have reported working around the issue by
installing an older version of mdt2df.dll
-Sue
On Mon, 10 Jan 2005 03:47:03 -0800, Dwight
<Dwight@.discussions.microsoft.com> wrote:
>Hi
>I'm trying to view a SQL 2000 Diagram that was created by my colleage. When
>I click on "Design Diagram" it appears blank (ie it does show any of the
>table links). However if I create a diagram I can view that and my colleage I
>can view the diagram that I've created and my colleage can also view it.
>Any ideas.
>TIA
>Dwight|||Hi Sue,
Yes I have install VS SP6. I'll see if I can find an old version of
mdt2df.dll and reinstall it.
Thanks
"Sue Hoegemeier" wrote:
> Dwight,
> Did you apply Visual Studio Service Pack 6?
> If so, this has happened to many people. I don't know if
> there is a knowledge base article or supported fix for the
> issue. Others have reported working around the issue by
> installing an older version of mdt2df.dll
> -Sue
> On Mon, 10 Jan 2005 03:47:03 -0800, Dwight
> <Dwight@.discussions.microsoft.com> wrote:
> >Hi
> >
> >I'm trying to view a SQL 2000 Diagram that was created by my colleage. When
> >I click on "Design Diagram" it appears blank (ie it does show any of the
> >table links). However if I create a diagram I can view that and my colleage I
> >can view the diagram that I've created and my colleage can also view it.
> >
> >Any ideas.
> >
> >TIA
> >Dwight
>

database diagram in sql 2000

how do you create a dataase diagram in sql 2005 express..

trying to work on an existing sql 2000 database diagram when we right click it . we encountered an error.

or

how can we move an existing sql 2k database diagram to wotk with sql server 2005 express as client..

The system cannot find the specified path exception from HRESULT 0x800700002 (Microsoft.VisualStudio.VSHelp80)
thanks

If you attach the SQL 2000 mdf file to the SQL 2005 Express instance, and expand the Database Diagrams node, you will get prompted to create Database Diagram objects. If you click yes, the new Database Diagram objects for SQL 2005 will be created in the mdf and you will be able to work with your database diagrams.
I hope this helps
Mike
Visual Studio Data Team

http://blogs.msdn.com/vsdata/|||how do you create a dataase diagram in sql 2005 express..

trying to work on an existing sql 2000 database diagram when we right click it . we encountered an error.

or

how can we move an existing sql 2k database diagram to wotk with sql server 2005 express as client..

The system cannot find the specified path exception from HRESULT 0x800700002 (Microsoft.VisualStudio.VSHelp80)
thanks

Link

Database Diagram in MSSMSE

after creating database in MICROSOFT SQL SERVER MANAGEMNET STUDIO EXPRESS, when i right click on diagram node, a menu appears with these 2 options :

1- working with sql server diagrams

2- refresh

how can i make a new diagram unless there is no such thing there?

I do not have the management tools for express loaded but you should be able to do it. When you do go to the database and expand it there is a diagram node. When you first expand this the engine should ask you if you want it to create the objects. You select yes then you are able to right click and create a new diagram. The other option is to use visual stuido 2005 to work with the digrams.