Counter settings: databases

Showing posts with label databases. Show all posts

Thursday, March 22, 2012

Couldn't see some databases in the object explorer window of Mangement Studio

hey guys,
I have one question. But it is not related with the Reporting Service. I got my computer set up with SQL Server 2005 version yesterday. When I connect to the database engine, I can see only system databases(master,model,msdb and tempdb) under the system database folder. I couldn't see other databases(like pubs,northwind) in the object explorer window of Management Studio. Do you have any idea how to solve this difficulty?

Thanks.

Amde

Pubs and northwind are no longer installed by default.|||

How about the other databases like Adventurework db? If you have some idea how to configure the database engine, please let me know.

I appreciate your response.

Amde

|||

Adventureworks is not installed by default either. Your questions are more appropriately suited for the "SQL Server Tools General" forum. http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1.

An easy test is to simply create a new database, refresh the object explorer, and you should see it there.

I am not certain what the best way to install the sample databases is post-installation. You should ask that question either in the tools forum or the setup forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=95&SiteID=1).

|||

Thank you for your advise. I will post this question on the appropriate form discussion board.

|||

Hi, Amde --

This content will be available in the next version of BOL --

Before you can use SQL Server 2005 samples, you must install them. Installing the samples is a two-step process. Samples are not installed by default and SQL Server Setup does not run the sample installation program for you. First, when running setup, on the Features Component page, select Samples. This will copy the samples installation program to your computer during setup. Second, to run the samples installation program, point to the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Documentation and Samples, point to Samples, and click Microsoft SQL Server 2005 Samples. To get the most recent samples, uninstall your current samples, and download and install the latest SQL Server 2005 samples from the Microsoft download Web site. To uninstall the current version of samples, point to the Start menu, point to Control Panel, and click Add or Remove Programs. Click Microsoft SQL Server 2005 Samples, and then click Remove. To find the most current samples, search http://www.microsoft.com/downloads/ for “SQL Server 2005 Samples and Sample Databases”. Choose the edition that matches your Reporting Services installation.

-- Mary

Couldn't see some databases in the object explorer window of Mangement Studio

Thanks.

Amde

Pubs and northwind are no longer installed by default.|||

How about the other databases like Adventurework db? If you have some idea how to configure the database engine, please let me know.

I appreciate your response.

Amde

|||

An easy test is to simply create a new database, refresh the object explorer, and you should see it there.

|||

Thank you for your advise. I will post this question on the appropriate form discussion board.

|||

Hi, Amde --

This content will be available in the next version of BOL --

-- Mary

sql

Tuesday, March 20, 2012

Could not run BEGIN TRANSACTION in database whatever because the database is read-only.

I had a databases running on SQL Server (EVALUATION VERSION).

I moved it to another system running:

-Windows 2000 Server
-IIS 5.0
-BlueDragon Server
-MSDE

I applied all of the latest service packs, security updates, etc...
Intalled SQL Server Web Data Administrator and .NET framework.

I attached the database using sp_attach_db:
>osql U sa
>password
>Sp_attach_db whatever',
>@.filename1 = C:\Program Files\Microsoft SQL
Server\MSSQL\Data\CollaborationTools.mdf',
>@.filename2 = C:\Program Files\Microsoft SQL
Server\MSSQL\Data\CollaborationTools.ldf'
>go

I added my datasource using the ODBC Admin Tool...
Verified it using the BlueDragon Admin Datasources page...

But now I'm getting the error message:

'Could not run BEGIN TRANSACTION in database 'whatever' because the
database is read-only.'

Any help/guidance would be appreciated.
Thanks very much.

Shaunokay, thanks for the guidance...

here's what i did:

i went to the location of the .mdf and .ldf files and made sure the
read only attribute wasn't checked.

i then entered the following commands via the cmd prompt:

>osql U sa
>password
>USE master
>EXEC sp_dboption collaboration', read only', FALSE'
>go

I verified by viewing the database properties in the Web Data
Administrator. The Status of the Database now reads Normal' not
Standby'.

Thanks very much.

-shaun|||"SS" <stiznoit@.yahoo.com> wrote in message
news:dab7211.0404141325.6df43251@.posting.google.co m...
> But now I'm getting the error message:
> 'Could not run BEGIN TRANSACTION in database 'whatever' because the
> database is read-only.'

Unless I'm missing something, it's pretty obvious. For some reason database
"whatever" has the read-only flag set.

See db_options to reset.

And since of course since it's read only, there's no point in allowing a
transaction since you can't do anything in it anyway.

> Any help/guidance would be appreciated.
> Thanks very much.
> Shaun|||okay, thanks for the guidance...

here's what i did:

i went to the location of the .mdf and .ldf files and made sure the
read only attribute wasn't checked.

i then entered the following commands via the cmd prompt:

>osql U sa
>password
>USE master
>EXEC sp_dboption collaboration', read only', FALSE'
>go

I verified by viewing the database properties in the Web Data
Administrator. The Status of the Database now reads Normal' not
Standby'.

Thanks very much.

-shaun

Could not run BEGIN TRANSACTION in database whatever because the database is read-only.

I had a databases running on SQL Server (EVALUATION VERSION).

I moved it to another system running:

-Windows 2000 Server
-IIS 5.0
-BlueDragon Server
-MSDE

I applied all of the latest service packs, security updates, etc...
Intalled SQL Server Web Data Administrator and .NET framework.

I added my datasource using the ODBC Admin Tool...
Verified it using the BlueDragon Admin Datasources page...

But now I'm getting the error message:

'Could not run BEGIN TRANSACTION in database 'whatever' because the
database is read-only.'

Any help/guidance would be appreciated.
Thanks very much.

Shaun"SS" <stiznoit@.yahoo.com> wrote in message
news:dab7211.0404141325.6df43251@.posting.google.co m...
> But now I'm getting the error message:
> 'Could not run BEGIN TRANSACTION in database 'whatever' because the
> database is read-only.'

Unless I'm missing something, it's pretty obvious. For some reason database
"whatever" has the read-only flag set.

See db_options to reset.

And since of course since it's read only, there's no point in allowing a
transaction since you can't do anything in it anyway.

> Any help/guidance would be appreciated.
> Thanks very much.
> Shaunsql

Wednesday, March 7, 2012

Could not find server '<server ip address here>' in sysservers.

Hi There,
I am planning on migrating databases from SQL 7 on a WIN2K box to SQL 2000 on a WIN 2003 box. Both are different domains. I have set up several databases to test and most work fine except for some .NET applications where I get this error spasmodically:
Could not find server '<server ip address here>' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
Im using the IP address of the server in the db connection string. In Master db > sysserver table the server name is VIVID01 and there is no mention of IP address.
Is there a reason why this error occurs only for some of the databases?
thanks - matt.Can you show us which exact statement is failing? Are you trying to use the
4-part naming convention to connect from one server to another. In that
case, you will have to configure that other server as a linked server.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:5B8A7E57-322A-4674-A95B-1DDC9281DD6A@.microsoft.com...
Hi There,
I am planning on migrating databases from SQL 7 on a WIN2K box to SQL 2000
on a WIN 2003 box. Both are different domains. I have set up several
databases to test and most work fine except for some .NET applications where
I get this error spasmodically:
Could not find server '<server ip address here>' in sysservers. Execute
sp_addlinkedserver to add the server to sysservers.
Im using the IP address of the server in the db connection string. In Master
db > sysserver table the server name is VIVID01 and there is no mention of
IP address.
Is there a reason why this error occurs only for some of the databases?
thanks - matt.|||The statement is:
SELECT * FROM OPENQUERY([<ipaddresshere>],'CCI.dbo.p_GetCMSGroupsByContentID "6918"') WHERE ID = 6
We have used the IP address instead of the server name.
This only fails when using the OPENQUERY statement. If I add a server using sp_addlinkedserver with the IP address specified it works.
"Narayana Vyas Kondreddi" wrote:
> Can you show us which exact statement is failing? Are you trying to use the
> 4-part naming convention to connect from one server to another. In that
> case, you will have to configure that other server as a linked server.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:5B8A7E57-322A-4674-A95B-1DDC9281DD6A@.microsoft.com...
> Hi There,
> I am planning on migrating databases from SQL 7 on a WIN2K box to SQL 2000
> on a WIN 2003 box. Both are different domains. I have set up several
> databases to test and most work fine except for some .NET applications where
> I get this error spasmodically:
> Could not find server '<server ip address here>' in sysservers. Execute
> sp_addlinkedserver to add the server to sysservers.
> Im using the IP address of the server in the db connection string. In Master
> db > sysserver table the server name is VIVID01 and there is no mention of
> IP address.
> Is there a reason why this error occurs only for some of the databases?
> thanks - matt.
>
>

Could not find server '<server ip address here>' in sysservers.

Sunday, February 19, 2012

Could not allocate new page for database TEMPDB

I get the following error when doing a variety of basic queries on other databases:

"Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth."

This doesn't make any sense since they are set to auto grow and there is plenty of disk space to do so.

Both data and transaction files of tempdb are set to:
"Automatically grow file" is checked
"Maximum file size" is set to "Unrestricted file growth"
Growth rate of 10%

Both tempdb data file and transaction file are on D: but all drives have ample space:
c: 25 GB free
D: 69 GB free
E: 175 GB free

sp_spaceused returns the following for tempdb:

name size unallocated space
tempdb 4.00 MB 1.45 MB

reserved data index_size unused
568 KB 176 KB 304 KB 88 KB

sp_helpdb returned for tempdb:

tempdb
4.00 MB sa 2 Apr 23 2004 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 80

This is with SQL Server 2000 (Personal Edition)
Product Version: 8.00.760 (SP3)

Is this some strange limitation of Personal Edition? Is it worth upgrading to Standard Edition?doesn't have anything to do with edition, it's your transaction. if it affects a lot of data, and in your case if work tables are needed to be created (this happens in tempdb) for sorting or grouping purposes, or if your indexes created with sort_in_tempdb, the data device will continue to grow for as much as it's needed based on your increment setting (default of 10% is not a good idea) once the limit is reached but the transaction has not completed, - it gets rolled back and the allocated size goes back to almost 0. if the "auto shrink" option is set, upon completion of rollback the allocated size goes down too. this explains why your sp_spaceused shows such a small number.|||doesn't have anything to do with edition, it's your transaction. if it affects a lot of data, and in your case if work tables are needed to be created (this happens in tempdb) for sorting or grouping purposes, or if your indexes created with sort_in_tempdb, the data device will continue to grow for as much as it's needed based on your increment setting (default of 10% is not a good idea) once the limit is reached but the transaction has not completed, - it gets rolled back and the allocated size goes back to almost 0. if the "auto shrink" option is set, upon completion of rollback the allocated size goes down too. this explains why your sp_spaceused shows such a small number.

Bingo! The query actually caused tempdb to use the whole 27GB available on the C: drive, failed, shrunk to a few MB, and gave the illusion that space wasn't an issue. I moved tempdb to the E: drive, and it works perfectly.

Thanks!

Friday, February 17, 2012

cost/benefit of consolidating databases

The database group in my company charges back to our department
according to the number of database schemas we use. Since we have many
apps, each with its own database (on the same server), our manager
wants us to consolidate them in order to save money.
My question is how much does that hurt performance ?
Whereas the applications used to query its respective databases, they
will now all hit the same database, albeit different tables.
Does sql server automatically place all objects in the same database
close to each other (same area or platter of the harddisk for example),
hence we will lose this advantage and slow down all our applications
when we condolidate ?
Basically, everything being equal, with no tablename/permissioning
conflict, if we have a hundred giant queries hitting 2 databases (50
each), will the queries slow down, speed up, or stay the same if we
consolidate them and have all 100 queries hit the same database.
On a side note, how much does it hurt to cross databases when joining
tables ? My guess is that for large queries it can be quite a bit
since now you have to open up more tables to check for locks,
permissioing, etc."Buma" <devtai@.gmail.com> wrote in message
news:1133895996.342197.189820@.g14g2000cwa.googlegroups.com...
> The database group in my company charges back to our department
> according to the number of database schemas we use. Since we have many
> apps, each with its own database (on the same server), our manager
> wants us to consolidate them in order to save money.
> My question is how much does that hurt performance ?
Probably not -- this is not really a performance question since you're
on a single server, but more of a maintainability question. Splitting
things up makes them easier to maintain, which may be cheaper in the long
run than consolidation. Which is more expensive? Your time, or the charges
to the database group?
What is the rationale behind charging based on number of schemas? That
really doesn't make much sense to me...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Buma wrote:
> The database group in my company charges back to our department
> according to the number of database schemas we use. Since we have many
> apps, each with its own database (on the same server), our manager
> wants us to consolidate them in order to save money.
>
The database group is part of your company? So what is the business
case for you spending time on this when the net effect is just to move
money around from one department to another? Seems like your manager
wants to *spend* money in order to disguise a failure of budget
management - nothing will be saved.
In performance terms the consolidation may well be negligible. You can
place each application's data on separate file groups on separate
physical drives if necessary to eliminate any contention.
I would have thought the most important implications were around
configuration and management. Do all your schemas have the same
requirements of maintenance plans, backup and availability? If not,
then you are probably better off with separate databases.
What about release management? It will be harder to guarantee that
changes in one part of a single database don't affect another unrelated
application. Do you intend to regression test all your applications
each time just one of them changes? I think that would be a big issue
for me if I was an application owner or responsible for testing and
support.
--
David Portas
SQL Server MVP
--|||You are correct in that the net effect of db consolidation to the
company is nil (if not negative because of time necessary to
consolidate db and more work needed to manage a larger db). But since
this is a large company, and considerable investment has been to made
to their charge-back methodology, there is virtually no chance that
they will change this anytime soon.
I was sorta hoping that sql server is optimized in such a way that
database separation allows for greater concurrency and increase
performance, however minor, but gets magnified as resources are
strained. It would be so much easier to justify the status quo if I
can show for example, that during heavy usage when all the apps (some
applications are webbased, some are heavy middleware such as building
olap cubes) are running, that the websites can slow down considerably,
or even for a few seconds.
Maintenance, management, backups, etc., are all pretty standard across
databases. They are in fact handle by the dba, so in some way it might
be easier for them to do one database whether than separate them.
As for complicating releases/debugging because of added database
complexities. That is difficult to quantify and I doubt would be
enough to convince management.|||"Buma" <devtai@.gmail.com> wrote in message
news:1133907692.066720.59050@.z14g2000cwz.googlegroups.com...
> I was sorta hoping that sql server is optimized in such a way that
> database separation allows for greater concurrency and increase
> performance, however minor, but gets magnified as resources are
> strained. It would be so much easier to justify the status quo if I
Well, you may be able to make that argument in some cases for multiple
instances, each with processor affinities and maximum memory utilization...
but that might make certain situations even worse.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--

cost/benefit of consolidating databases

The database group in my company charges back to our department
according to the number of database schemas we use. Since we have many
apps, each with its own database (on the same server), our manager
wants us to consolidate them in order to save money.
My question is how much does that hurt performance ?
Whereas the applications used to query its respective databases, they
will now all hit the same database, albeit different tables.
Does sql server automatically place all objects in the same database
close to each other (same area or platter of the harddisk for example),
hence we will lose this advantage and slow down all our applications
when we condolidate ?
Basically, everything being equal, with no tablename/permissioning
conflict, if we have a hundred giant queries hitting 2 databases (50
each), will the queries slow down, speed up, or stay the same if we
consolidate them and have all 100 queries hit the same database.
On a side note, how much does it hurt to cross databases when joining
tables ? My guess is that for large queries it can be quite a bit
since now you have to open up more tables to check for locks,
permissioing, etc.
"Buma" <devtai@.gmail.com> wrote in message
news:1133895996.342197.189820@.g14g2000cwa.googlegr oups.com...
> The database group in my company charges back to our department
> according to the number of database schemas we use. Since we have many
> apps, each with its own database (on the same server), our manager
> wants us to consolidate them in order to save money.
> My question is how much does that hurt performance ?
Probably not -- this is not really a performance question since you're
on a single server, but more of a maintainability question. Splitting
things up makes them easier to maintain, which may be cheaper in the long
run than consolidation. Which is more expensive? Your time, or the charges
to the database group?
What is the rationale behind charging based on number of schemas? That
really doesn't make much sense to me...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
|||Buma wrote:

> The database group in my company charges back to our department
> according to the number of database schemas we use. Since we have many
> apps, each with its own database (on the same server), our manager
> wants us to consolidate them in order to save money.
>
The database group is part of your company? So what is the business
case for you spending time on this when the net effect is just to move
money around from one department to another? Seems like your manager
wants to *spend* money in order to disguise a failure of budget
management - nothing will be saved.
In performance terms the consolidation may well be negligible. You can
place each application's data on separate file groups on separate
physical drives if necessary to eliminate any contention.
I would have thought the most important implications were around
configuration and management. Do all your schemas have the same
requirements of maintenance plans, backup and availability? If not,
then you are probably better off with separate databases.
What about release management? It will be harder to guarantee that
changes in one part of a single database don't affect another unrelated
application. Do you intend to regression test all your applications
each time just one of them changes? I think that would be a big issue
for me if I was an application owner or responsible for testing and
support.
David Portas
SQL Server MVP
|||You are correct in that the net effect of db consolidation to the
company is nil (if not negative because of time necessary to
consolidate db and more work needed to manage a larger db). But since
this is a large company, and considerable investment has been to made
to their charge-back methodology, there is virtually no chance that
they will change this anytime soon.
I was sorta hoping that sql server is optimized in such a way that
database separation allows for greater concurrency and increase
performance, however minor, but gets magnified as resources are
strained. It would be so much easier to justify the status quo if I
can show for example, that during heavy usage when all the apps (some
applications are webbased, some are heavy middleware such as building
olap cubes) are running, that the websites can slow down considerably,
or even for a few seconds.
Maintenance, management, backups, etc., are all pretty standard across
databases. They are in fact handle by the dba, so in some way it might
be easier for them to do one database whether than separate them.
As for complicating releases/debugging because of added database
complexities. That is difficult to quantify and I doubt would be
enough to convince management.
|||"Buma" <devtai@.gmail.com> wrote in message
news:1133907692.066720.59050@.z14g2000cwz.googlegro ups.com...
> I was sorta hoping that sql server is optimized in such a way that
> database separation allows for greater concurrency and increase
> performance, however minor, but gets magnified as resources are
> strained. It would be so much easier to justify the status quo if I
Well, you may be able to make that argument in some cases for multiple
instances, each with processor affinities and maximum memory utilization...
but that might make certain situations even worse.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457

cost/benefit of consolidating databases

The database group in my company charges back to our department
according to the number of database schemas we use. Since we have many
apps, each with its own database (on the same server), our manager
wants us to consolidate them in order to save money.
My question is how much does that hurt performance ?
Whereas the applications used to query its respective databases, they
will now all hit the same database, albeit different tables.
Does sql server automatically place all objects in the same database
close to each other (same area or platter of the harddisk for example),
hence we will lose this advantage and slow down all our applications
when we condolidate ?
Basically, everything being equal, with no tablename/permissioning
conflict, if we have a hundred giant queries hitting 2 databases (50
each), will the queries slow down, speed up, or stay the same if we
consolidate them and have all 100 queries hit the same database.
On a side note, how much does it hurt to cross databases when joining
tables ? My guess is that for large queries it can be quite a bit
since now you have to open up more tables to check for locks,
permissioing, etc."Buma" <devtai@.gmail.com> wrote in message
news:1133895996.342197.189820@.g14g2000cwa.googlegroups.com...
> The database group in my company charges back to our department
> according to the number of database schemas we use. Since we have many
> apps, each with its own database (on the same server), our manager
> wants us to consolidate them in order to save money.
> My question is how much does that hurt performance ?
Probably not -- this is not really a performance question since you're
on a single server, but more of a maintainability question. Splitting
things up makes them easier to maintain, which may be cheaper in the long
run than consolidation. Which is more expensive? Your time, or the charges
to the database group?
What is the rationale behind charging based on number of schemas? That
really doesn't make much sense to me...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Buma wrote:

> The database group in my company charges back to our department
> according to the number of database schemas we use. Since we have many
> apps, each with its own database (on the same server), our manager
> wants us to consolidate them in order to save money.
>
The database group is part of your company? So what is the business
case for you spending time on this when the net effect is just to move
money around from one department to another? Seems like your manager
wants to *spend* money in order to disguise a failure of budget
management - nothing will be saved.
In performance terms the consolidation may well be negligible. You can
place each application's data on separate file groups on separate
physical drives if necessary to eliminate any contention.
I would have thought the most important implications were around
configuration and management. Do all your schemas have the same
requirements of maintenance plans, backup and availability? If not,
then you are probably better off with separate databases.
What about release management? It will be harder to guarantee that
changes in one part of a single database don't affect another unrelated
application. Do you intend to regression test all your applications
each time just one of them changes? I think that would be a big issue
for me if I was an application owner or responsible for testing and
support.
David Portas
SQL Server MVP
--|||You are correct in that the net effect of db consolidation to the
company is nil (if not negative because of time necessary to
consolidate db and more work needed to manage a larger db). But since
this is a large company, and considerable investment has been to made
to their charge-back methodology, there is virtually no chance that
they will change this anytime soon.
I was sorta hoping that sql server is optimized in such a way that
database separation allows for greater concurrency and increase
performance, however minor, but gets magnified as resources are
strained. It would be so much easier to justify the status quo if I
can show for example, that during heavy usage when all the apps (some
applications are webbased, some are heavy middleware such as building
olap cubes) are running, that the websites can slow down considerably,
or even for a few seconds.
Maintenance, management, backups, etc., are all pretty standard across
databases. They are in fact handle by the dba, so in some way it might
be easier for them to do one database whether than separate them.
As for complicating releases/debugging because of added database
complexities. That is difficult to quantify and I doubt would be
enough to convince management.|||"Buma" <devtai@.gmail.com> wrote in message
news:1133907692.066720.59050@.z14g2000cwz.googlegroups.com...
> I was sorta hoping that sql server is optimized in such a way that
> database separation allows for greater concurrency and increase
> performance, however minor, but gets magnified as resources are
> strained. It would be so much easier to justify the status quo if I
Well, you may be able to make that argument in some cases for multiple
instances, each with processor affinities and maximum memory utilization...
but that might make certain situations even worse.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--

Cost of unicode?

I've converted most SQL Server databases to unicode since the AS 400 stores data in unicode (UTF-16). So far the only difference I see is that running queries to text in Unicode takes a lot longer, and UTF-16 files are 4x as large as Cp1252 files.

Any way to get the file sizes / query times of old with Unicode?Try Transact SQL forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1), you are more likely to find SQL perf experts there. This forum is specific to SSIS.

Anyway, UTF-16 files should be two times larger than CP 1252 files (not sure how you get 4x larger files). There is nothing that can be done with it other than switch to different encoding, e.g. UTF-8 which is another Unicode encoding and might be more compact if your files mostly contain latin characters.|||Thanks for the suggestion... I've created a new thread here.

Corruption

Hi. Quick question:
Are SQL server databases prone to corruption?
MikeNot in 7.0 or 2000. Corruption these days is usually due to faulty hardware
or power outages, both of which can be mostly avoided with the right
configuration.
--
Andrew J. Kelly
SQL Server MVP
"Mike Tremendous" <noone@.hotmail.com> wrote in message
news:%23I3aZBxrDHA.2304@.TK2MSFTNGP12.phx.gbl...
> Hi. Quick question:
> Are SQL server databases prone to corruption?
> Mike
>|||> > Hi. Quick question:
> > Are SQL server databases prone to corruption?
> Not in 7.0 or 2000. Corruption these days is usually due to faulty
hardware
> or power outages, both of which can be mostly avoided with the right
> configuration.
What about with so-called 'abnormal' use? Eg:
- What if there are clients not disconneting cleanly and leaving
transactions open?
- How about creating foreign keys and the like whilst other users are
connected?
- What about executing a huge transactions that effects, say, 10,000,000
rows, and then rolling it back?
- What about restarting the SQL Server service with clients connected and
transactions open?
Can it cope with these scenarios?
Mike|||Yes these are essentially non-issues when it comes to corrupting the
database. Any time a user is disconnected the server will automatically
roll back any open trans. Creating keys and huge trans are all Atomic
operations and the only issue is user performance not stability. Even
restarting a server should not be an issue from a sql server standpoint as
when it restarts it will automatically roll back any uncommitted trans
before it allows new ones in. I wouldn't recommend restarting a server with
users in it for general purposes but SQL Server wouldn't be much of an
enterprise db if it didn't handle the issues you pose correctly.
--
Andrew J. Kelly
SQL Server MVP
"Mike Tremendous" <noone@.hotmail.com> wrote in message
news:OLKPgZzrDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > Hi. Quick question:
> > > Are SQL server databases prone to corruption?
> >
> > Not in 7.0 or 2000. Corruption these days is usually due to faulty
> hardware
> > or power outages, both of which can be mostly avoided with the right
> > configuration.
> What about with so-called 'abnormal' use? Eg:
> - What if there are clients not disconneting cleanly and leaving
> transactions open?
> - How about creating foreign keys and the like whilst other users are
> connected?
> - What about executing a huge transactions that effects, say, 10,000,000
> rows, and then rolling it back?
> - What about restarting the SQL Server service with clients connected and
> transactions open?
> Can it cope with these scenarios?
> Mike
>|||> Yes these are essentially non-issues when it comes to corrupting the
> database. Any time a user is disconnected the server will automatically
> roll back any open trans. Creating keys and huge trans are all Atomic
> operations and the only issue is user performance not stability. Even
> restarting a server should not be an issue from a sql server standpoint as
> when it restarts it will automatically roll back any uncommitted trans
> before it allows new ones in. I wouldn't recommend restarting a server
with
> users in it for general purposes but SQL Server wouldn't be much of an
> enterprise db if it didn't handle the issues you pose correctly.
Thanks for the advice.
Mike

Tuesday, February 14, 2012

Corrupted table

When I try and examine a table in one of my MS SQL Server 2000 databases I
get the following error from Enterprise Manager:
[Microsoft][ODBC..] I/O error (bad page ID) detected during read at offset
0X00000000038000 in file ...MDF
How do I go about fixing this?
Thanks,"Andrew Chalk" <achalk@.XXXmagnacartasoftware.com> wrote in
news:O4XR1rJWFHA.3280@.TK2MSFTNGP09.phx.gbl:
> I/O error (bad page ID) detected during read at offset
Run...
DBCC CHECKDB
--
Regards
JTC ^..^|||Hi
In Query Analyzer, run DBCC CHECKDB to get an idea of how widespread the
corruption is. Check all the DBs on the server.
Start looking for you most recent backup.
If the corruption is not too bad, you might be able to run DBCC CECKDB
REPAIR_REBUILD
else, you would need to use DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS (but you may
loose data).
Generally, corruption is caused by hardware faults.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andrew Chalk" <achalk@.XXXmagnacartasoftware.com> wrote in message
news:O4XR1rJWFHA.3280@.TK2MSFTNGP09.phx.gbl...
> When I try and examine a table in one of my MS SQL Server 2000 databases I
> get the following error from Enterprise Manager:
> [Microsoft][ODBC..] I/O error (bad page ID) detected during read at offset
> 0X00000000038000 in file ...MDF
> How do I go about fixing this?
> Thanks,
>
>|||Also look at http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OgvrizJWFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Hi
> In Query Analyzer, run DBCC CHECKDB to get an idea of how widespread the
> corruption is. Check all the DBs on the server.
> Start looking for you most recent backup.
> If the corruption is not too bad, you might be able to run DBCC CECKDB
> REPAIR_REBUILD
> else, you would need to use DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS (but you
> may loose data).
> Generally, corruption is caused by hardware faults.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Andrew Chalk" <achalk@.XXXmagnacartasoftware.com> wrote in message
> news:O4XR1rJWFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> When I try and examine a table in one of my MS SQL Server 2000 databases
>> I
>> get the following error from Enterprise Manager:
>> [Microsoft][ODBC..] I/O error (bad page ID) detected during read at
>> offset
>> 0X00000000038000 in file ...MDF
>> How do I go about fixing this?
>> Thanks,
>>
>|||Thanks Mike. I was able to Export the table SQL definition, delete the table
and then recreate it from the script.
- Andrew
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uzMoM3JWFHA.584@.TK2MSFTNGP15.phx.gbl...
> Also look at http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:OgvrizJWFHA.3320@.TK2MSFTNGP12.phx.gbl...
> > Hi
> >
> > In Query Analyzer, run DBCC CHECKDB to get an idea of how widespread the
> > corruption is. Check all the DBs on the server.
> >
> > Start looking for you most recent backup.
> >
> > If the corruption is not too bad, you might be able to run DBCC CECKDB
> > REPAIR_REBUILD
> > else, you would need to use DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS (but you
> > may loose data).
> >
> > Generally, corruption is caused by hardware faults.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > IM: mike@.epprecht.net
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> > "Andrew Chalk" <achalk@.XXXmagnacartasoftware.com> wrote in message
> > news:O4XR1rJWFHA.3280@.TK2MSFTNGP09.phx.gbl...
> >> When I try and examine a table in one of my MS SQL Server 2000
databases
> >> I
> >> get the following error from Enterprise Manager:
> >>
> >> [Microsoft][ODBC..] I/O error (bad page ID) detected during read at
> >> offset
> >> 0X00000000038000 in file ...MDF
> >>
> >> How do I go about fixing this?
> >>
> >> Thanks,
> >>
> >>
> >>
> >
> >
>|||Hi Andrew,
Now you should be in good shape, but still run a DBCC CHECKDB(DBNAME) and
ensure that database is in good shape.
Note:
As part of your routine maintenance please schedule a DBCC CHECKDB for all
production databases. This will help you to identify issues earlier.
Thanks
Hari
SQL Server MVP
"Andrew Chalk" <achalk@.XXXmagnacartasoftware.com> wrote in message
news:e2s03xOWFHA.628@.tk2msftngp13.phx.gbl...
> Thanks Mike. I was able to Export the table SQL definition, delete the
> table
> and then recreate it from the script.
> - Andrew
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uzMoM3JWFHA.584@.TK2MSFTNGP15.phx.gbl...
>> Also look at http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>> --
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> news:OgvrizJWFHA.3320@.TK2MSFTNGP12.phx.gbl...
>> > Hi
>> >
>> > In Query Analyzer, run DBCC CHECKDB to get an idea of how widespread
>> > the
>> > corruption is. Check all the DBs on the server.
>> >
>> > Start looking for you most recent backup.
>> >
>> > If the corruption is not too bad, you might be able to run DBCC CECKDB
>> > REPAIR_REBUILD
>> > else, you would need to use DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS (but
>> > you
>> > may loose data).
>> >
>> > Generally, corruption is caused by hardware faults.
>> >
>> > Regards
>> > --
>> > Mike Epprecht, Microsoft SQL Server MVP
>> > Zurich, Switzerland
>> >
>> > IM: mike@.epprecht.net
>> >
>> > MVP Program: http://www.microsoft.com/mvp
>> >
>> > Blog: http://www.msmvps.com/epprecht/
>> >
>> > "Andrew Chalk" <achalk@.XXXmagnacartasoftware.com> wrote in message
>> > news:O4XR1rJWFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> >> When I try and examine a table in one of my MS SQL Server 2000
> databases
>> >> I
>> >> get the following error from Enterprise Manager:
>> >>
>> >> [Microsoft][ODBC..] I/O error (bad page ID) detected during read at
>> >> offset
>> >> 0X00000000038000 in file ...MDF
>> >>
>> >> How do I go about fixing this?
>> >>
>> >> Thanks,
>> >>
>> >>
>> >>
>> >
>> >
>>
>|||Andrew,
A key here that Mike mentioned is "Generally, corruption is caused by
hardware faults".
now that you solved the immediate issue of data\table corruption. The next
step is to figure out "WHY".
If you dont do this, your data is likely going to become corrupt again very
soon.
I suspect a disk is going haywire on you or your RAID Controller is kicking
the bucket.
I just went through this with two different customers. One turned out to be
disk, hte other turned out to be RAID Controller.
cheers
Greg Jackson
PDX, Oregon|||I'll do that.
- Andrew
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:efyvtDTWFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hi Andrew,
> Now you should be in good shape, but still run a DBCC CHECKDB(DBNAME) and
> ensure that database is in good shape.
> Note:
> As part of your routine maintenance please schedule a DBCC CHECKDB for all
> production databases. This will help you to identify issues earlier.
> Thanks
> Hari
> SQL Server MVP
> "Andrew Chalk" <achalk@.XXXmagnacartasoftware.com> wrote in message
> news:e2s03xOWFHA.628@.tk2msftngp13.phx.gbl...
> > Thanks Mike. I was able to Export the table SQL definition, delete the
> > table
> > and then recreate it from the script.
> >
> > - Andrew
> > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> > news:uzMoM3JWFHA.584@.TK2MSFTNGP15.phx.gbl...
> >> Also look at
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
> >>
> >> --
> >> --
> >> Mike Epprecht, Microsoft SQL Server MVP
> >> Zurich, Switzerland
> >>
> >> IM: mike@.epprecht.net
> >>
> >> MVP Program: http://www.microsoft.com/mvp
> >>
> >> Blog: http://www.msmvps.com/epprecht/
> >>
> >> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> >> news:OgvrizJWFHA.3320@.TK2MSFTNGP12.phx.gbl...
> >> > Hi
> >> >
> >> > In Query Analyzer, run DBCC CHECKDB to get an idea of how widespread
> >> > the
> >> > corruption is. Check all the DBs on the server.
> >> >
> >> > Start looking for you most recent backup.
> >> >
> >> > If the corruption is not too bad, you might be able to run DBCC
CECKDB
> >> > REPAIR_REBUILD
> >> > else, you would need to use DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS (but
> >> > you
> >> > may loose data).
> >> >
> >> > Generally, corruption is caused by hardware faults.
> >> >
> >> > Regards
> >> > --
> >> > Mike Epprecht, Microsoft SQL Server MVP
> >> > Zurich, Switzerland
> >> >
> >> > IM: mike@.epprecht.net
> >> >
> >> > MVP Program: http://www.microsoft.com/mvp
> >> >
> >> > Blog: http://www.msmvps.com/epprecht/
> >> >
> >> > "Andrew Chalk" <achalk@.XXXmagnacartasoftware.com> wrote in message
> >> > news:O4XR1rJWFHA.3280@.TK2MSFTNGP09.phx.gbl...
> >> >> When I try and examine a table in one of my MS SQL Server 2000
> > databases
> >> >> I
> >> >> get the following error from Enterprise Manager:
> >> >>
> >> >> [Microsoft][ODBC..] I/O error (bad page ID) detected during read at
> >> >> offset
> >> >> 0X00000000038000 in file ...MDF
> >> >>
> >> >> How do I go about fixing this?
> >> >>
> >> >> Thanks,
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||This is on a development machine rather than a production server so I
suspect it may have been roughly rebooted periodically. There hasn't been
any other evidence of disk problems so i am just backing up frequently for
now.
Thanks,
Andrew
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OPRePnVWFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Andrew,
> A key here that Mike mentioned is "Generally, corruption is caused by
> hardware faults".
> now that you solved the immediate issue of data\table corruption. The next
> step is to figure out "WHY".
> If you dont do this, your data is likely going to become corrupt again
very
> soon.
> I suspect a disk is going haywire on you or your RAID Controller is
kicking
> the bucket.
> I just went through this with two different customers. One turned out to
be
> disk, hte other turned out to be RAID Controller.
>
> cheers
>
> Greg Jackson
> PDX, Oregon
>

Corrupted ReportServerTempDB

Hi,
Need help here. I have reporting services installed on a sql cluster
and backup running every night for the 2 databases: ReportServer and
ReportServerTempDB. However a few weeks ago, the backup started
failing because ReportServerTempDB failed the DBCC CheckDB test with
the following error message:
Incorrect PFS free space information for page (1:184) in object ID
2105058535, index ID 1, partition ID 72057594038517760, alloc unit ID
72057594043498496 (type LOB data).
Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
>From what I've read, that usually means that the db is corrupted and I
should restore from the backup... but seeing that this is the
temporary db, would it be possible to just delete the corrupted on and
recreate the ReportServerTempDB? Any impact?
ThxHello,
Yes you can drop ReportServerTempDB database and then recreate it:
After creating a new database ReportServerTempDB, create a Role
called RSExecRole , then you can run the script CatalogTempDB.sql. to
recreate the missing ReportServerTempDB objects.
(Go to your original installation bits and grab the file named
CatalogTempDB.sql located in the directory ..\Setup\Report Server\Reporting
Services\ReportServer.)
You will also need to make sure all the users that are in the RSExecRole in
the ReportServer Database
are in the ReportServerTempDB's RSExecRole.
Regards,
Radoslaw Lebkowski
Uzytkownik <aifong@.gmail.com> napisal w wiadomosci
news:1170909371.919215.218860@.l53g2000cwa.googlegroups.com...
> Hi,
> Need help here. I have reporting services installed on a sql cluster
> and backup running every night for the 2 databases: ReportServer and
> ReportServerTempDB. However a few weeks ago, the backup started
> failing because ReportServerTempDB failed the DBCC CheckDB test with
> the following error message:
> Incorrect PFS free space information for page (1:184) in object ID
> 2105058535, index ID 1, partition ID 72057594038517760, alloc unit ID
> 72057594043498496 (type LOB data).
> Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
>>From what I've read, that usually means that the db is corrupted and I
> should restore from the backup... but seeing that this is the
> temporary db, would it be possible to just delete the corrupted on and
> recreate the ReportServerTempDB? Any impact?
> Thx
>|||Hi Radoslaw,
Thanks! I just tested it on a separate installation and it works.
Just a note on the collation, it has to be exactly the same as
ReportServer DB otherwise it gives an error. Other than that, A-ok.
Thanks,
AF
On Feb 8, 8:27 pm, "Radoslaw Lebkowski" <rade...@.wp.pl> wrote:
> Hello,
> Yes you can drop ReportServerTempDB database and then recreate it:
> After creating a new database ReportServerTempDB, create a Role
> called RSExecRole , then you can run the script CatalogTempDB.sql. to
> recreate the missing ReportServerTempDB objects.
> (Go to your original installation bits and grab the file named
> CatalogTempDB.sql located in the directory ..\Setup\Report Server\Reporting
> Services\ReportServer.)
> You will also need to make sure all the users that are in the RSExecRole in
> the ReportServer Database
> are in the ReportServerTempDB's RSExecRole.
> Regards,
> Radoslaw Lebkowski
> Uzytkownik <aif...@.gmail.com> napisal w wiadomoscinews:1170909371.919215.218860@.l53g2000cwa.googlegroups.com...
>
> > Hi,
> > Need help here. I have reporting services installed on a sql cluster
> > and backup running every night for the 2 databases: ReportServer and
> > ReportServerTempDB. However a few weeks ago, the backup started
> > failing because ReportServerTempDB failed the DBCC CheckDB test with
> > the following error message:
> > Incorrect PFS free space information for page (1:184) in object ID
> > 2105058535, index ID 1, partition ID 72057594038517760, alloc unit ID
> > 72057594043498496 (type LOB data).
> > Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
> >>From what I've read, that usually means that the db is corrupted and I
> > should restore from the backup... but seeing that this is the
> > temporary db, would it be possible to just delete the corrupted on and
> > recreate the ReportServerTempDB? Any impact?
> > Thx- Hide quoted text -
> - Show quoted text -

Thursday, March 22, 2012

Couldn't see some databases in the object explorer window of Mangement Studio

Couldn't see some databases in the object explorer window of Mangement Studio

Tuesday, March 20, 2012

Could not run BEGIN TRANSACTION in database whatever because the database is read-only.

Could not run BEGIN TRANSACTION in database whatever because the database is read-only.

Wednesday, March 7, 2012

Could not find server '<server ip address here>' in sysservers.

Could not find server '<server ip address here>' in sysservers.

Could not find server '<server ip address here>' in sysservers.

Sunday, February 19, 2012

Could not allocate new page for database TEMPDB

Friday, February 17, 2012

cost/benefit of consolidating databases

cost/benefit of consolidating databases

cost/benefit of consolidating databases

Cost of unicode?

Corruption

Tuesday, February 14, 2012

Corrupted table

Corrupted table

Corrupted table

Corrupted table

Corrupted ReportServerTempDB

Counter settings

Blog Archive

About Me