Database level lock: running

Showing posts with label running. Show all posts

Thursday, March 29, 2012

Database is running slow after running DBCC Shrinkdatabase command

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

Tuesday, March 27, 2012

Database Hammer & Stress testing

Hello all
I am trying to stress test an SQL2000 server running on Windows 2003.
I am using the reskit utility database hammer.
The utility creates a table with 10,000,000 rows in it fine but when I try
to use the query/update part of the tool(procmaster.exe), it fails and I get
an error: 'Problem encountered during processing -2147217913'.
There is plenty of space in my log and data files etc.
Does anybody know what might be going on?
Thanks in advance.
Jane
PS I have also left a copy of this post in the sqlserver tools group.Hi
Sounds something like:
http://support.microsoft.com/default.aspx?scid=kb;en-us;265166
You may want to run profiler to see where it is having problems.
John
"Jane Reynolds" <nospam@.please.com> wrote in message
news:%23i9J6wU1EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Hello all
> I am trying to stress test an SQL2000 server running on Windows 2003.
> I am using the reskit utility database hammer.
> The utility creates a table with 10,000,000 rows in it fine but when I try
> to use the query/update part of the tool(procmaster.exe), it fails and I
> get an error: 'Problem encountered during processing -2147217913'.
> There is plenty of space in my log and data files etc.
> Does anybody know what might be going on?
> Thanks in advance.
> Jane
> PS I have also left a copy of this post in the sqlserver tools group.
>|||Thanks
It looks like it is a code problem iin the utility then. I have done some
more research and found that there is a bug in the code. I don't know VB
though to try to edit it so will have to think of something else.
Jane
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23ZiKizV1EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Hi
> Sounds something like:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;265166
> You may want to run profiler to see where it is having problems.
> John
> "Jane Reynolds" <nospam@.please.com> wrote in message
> news:%23i9J6wU1EHA.2676@.TK2MSFTNGP12.phx.gbl...
>> Hello all
>> I am trying to stress test an SQL2000 server running on Windows 2003.
>> I am using the reskit utility database hammer.
>> The utility creates a table with 10,000,000 rows in it fine but when I
>> try to use the query/update part of the tool(procmaster.exe), it fails
>> and I get an error: 'Problem encountered during processing -2147217913'.
>> There is plenty of space in my log and data files etc.
>> Does anybody know what might be going on?
>> Thanks in advance.
>> Jane
>> PS I have also left a copy of this post in the sqlserver tools group.
>|||Hi
The article does not relate to a SQL Server back end, therefore if you did
profile this you can get the exact statement that fails and see what error
message Query Analyser would give you. If it is a datatype problem then you
may want to remove the column from the test or possibly install a different
version of MDAC.
John
"Jane Reynolds" <nospam@.please.com> wrote in message
news:OkyHRvY1EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Thanks
> It looks like it is a code problem iin the utility then. I have done some
> more research and found that there is a bug in the code. I don't know VB
> though to try to edit it so will have to think of something else.
> Jane
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23ZiKizV1EHA.1404@.TK2MSFTNGP11.phx.gbl...
>> Hi
>> Sounds something like:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;265166
>> You may want to run profiler to see where it is having problems.
>> John
>> "Jane Reynolds" <nospam@.please.com> wrote in message
>> news:%23i9J6wU1EHA.2676@.TK2MSFTNGP12.phx.gbl...
>> Hello all
>> I am trying to stress test an SQL2000 server running on Windows 2003.
>> I am using the reskit utility database hammer.
>> The utility creates a table with 10,000,000 rows in it fine but when I
>> try to use the query/update part of the tool(procmaster.exe), it fails
>> and I get an error: 'Problem encountered during processing -2147217913'.
>> There is plenty of space in my log and data files etc.
>> Does anybody know what might be going on?
>> Thanks in advance.
>> Jane
>> PS I have also left a copy of this post in the sqlserver tools group.
>>
>

Database Hammer & Stress testing

Database growing unexpectedly

Hi all,
Running sql2000 ee SP4 on win2003.
I have a small database which havent been inserted/updated since it was
created 6 months ago. No DDL changes. This database was about 1,5 Mb for
the first months, but have started to grow. The size is now 30 Mb and it
is growing with about 2 Mb a month. It is set to autogrow with 10%.
Can someone explain this growth?
name rows reserved
form_lang 1 56 KB
grid_expr 0 0 KB
GridViewCategory 0 0 KB
GridViewDefinition 0 0 KB
hierarchy_tables 0 0 KB
invitation 0 0 KB
iteration 1 24 KB
lookup 0 0 KB
lookup_lang 0 0 KB
PanelistCredit 0 0 KB
quota 1 56 KB
response_control 2 104 KB
response1 0 0 KB
SampleLog 0 0 KB
weightmodel 0 0 KB
weightmodelform 0 0 KBTry running DBCC UPDATEUSAGE(0) and report back the space used.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns977AEC59B7F29gurbaohotmailcom@.12
9.250.171.65...
Hi all,
Running sql2000 ee SP4 on win2003.
I have a small database which havent been inserted/updated since it was
created 6 months ago. No DDL changes. This database was about 1,5 Mb for
the first months, but have started to grow. The size is now 30 Mb and it
is growing with about 2 Mb a month. It is set to autogrow with 10%.
Can someone explain this growth?
name rows reserved
form_lang 1 56 KB
grid_expr 0 0 KB
GridViewCategory 0 0 KB
GridViewDefinition 0 0 KB
hierarchy_tables 0 0 KB
invitation 0 0 KB
iteration 1 24 KB
lookup 0 0 KB
lookup_lang 0 0 KB
PanelistCredit 0 0 KB
quota 1 56 KB
response_control 2 104 KB
response1 0 0 KB
SampleLog 0 0 KB
weightmodel 0 0 KB
weightmodelform 0 0 KB|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in news:#noh#6kPGHA.1532
@.TK2MSFTNGP12.phx.gbl:

> Try running DBCC UPDATEUSAGE(0) and report back the space used.
>
Hi,
seems the first output from
USE MyDb; EXEC sp_MSforeachtable 'EXEC sp_spaceused [?]'
was missing some tables...
I ran UpdateUsage and the sp_spaceused again and found the reason for the
growth.
Case closed :-)
Thanks,

Database growing unexpectedly

Hi all,
Running sql2000 ee SP4 on win2003.
I have a small database which havent been inserted/updated since it was
created 6 months ago. No DDL changes. This database was about 1,5 Mb for
the first months, but have started to grow. The size is now 30 Mb and it
is growing with about 2 Mb a month. It is set to autogrow with 10%.
Can someone explain this growth?
name rows reserved
form_lang 1 56 KB
grid_expr 0 0 KB
GridViewCategory 0 0 KB
GridViewDefinition 0 0 KB
hierarchy_tables 0 0 KB
invitation 0 0 KB
iteration 1 24 KB
lookup 0 0 KB
lookup_lang 0 0 KB
PanelistCredit 0 0 KB
quota 1 56 KB
response_control 2 104 KB
response1 0 0 KB
SampleLog 0 0 KB
weightmodel 0 0 KB
weightmodelform 0 0 KB
Try running DBCC UPDATEUSAGE(0) and report back the space used.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns977AEC59B7F29gurbaohotmailcom@.129.250.171. 65...
Hi all,
Running sql2000 ee SP4 on win2003.
I have a small database which havent been inserted/updated since it was
created 6 months ago. No DDL changes. This database was about 1,5 Mb for
the first months, but have started to grow. The size is now 30 Mb and it
is growing with about 2 Mb a month. It is set to autogrow with 10%.
Can someone explain this growth?
name rows reserved
form_lang 1 56 KB
grid_expr 0 0 KB
GridViewCategory 0 0 KB
GridViewDefinition 0 0 KB
hierarchy_tables 0 0 KB
invitation 0 0 KB
iteration 1 24 KB
lookup 0 0 KB
lookup_lang 0 0 KB
PanelistCredit 0 0 KB
quota 1 56 KB
response_control 2 104 KB
response1 0 0 KB
SampleLog 0 0 KB
weightmodel 0 0 KB
weightmodelform 0 0 KB
|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in news:#noh#6kPGHA.1532
@.TK2MSFTNGP12.phx.gbl:

> Try running DBCC UPDATEUSAGE(0) and report back the space used.
>
Hi,
seems the first output from
USE MyDb; EXEC sp_MSforeachtable 'EXEC sp_spaceused [?]'
was missing some tables...
I ran UpdateUsage and the sp_spaceused again and found the reason for the
growth.
Case closed :-)
Thanks,

Database growing unexpectedly

Hi all,
Running sql2000 ee SP4 on win2003.
I have a small database which havent been inserted/updated since it was
created 6 months ago. No DDL changes. This database was about 1,5 Mb for
the first months, but have started to grow. The size is now 30 Mb and it
is growing with about 2 Mb a month. It is set to autogrow with 10%.
Can someone explain this growth?
name rows reserved
form_lang 1 56 KB
grid_expr 0 0 KB
GridViewCategory 0 0 KB
GridViewDefinition 0 0 KB
hierarchy_tables 0 0 KB
invitation 0 0 KB
iteration 1 24 KB
lookup 0 0 KB
lookup_lang 0 0 KB
PanelistCredit 0 0 KB
quota 1 56 KB
response_control 2 104 KB
response1 0 0 KB
SampleLog 0 0 KB
weightmodel 0 0 KB
weightmodelform 0 0 KBTry running DBCC UPDATEUSAGE(0) and report back the space used.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns977AEC59B7F29gurbaohotmailcom@.129.250.171.65...
Hi all,
Running sql2000 ee SP4 on win2003.
I have a small database which havent been inserted/updated since it was
created 6 months ago. No DDL changes. This database was about 1,5 Mb for
the first months, but have started to grow. The size is now 30 Mb and it
is growing with about 2 Mb a month. It is set to autogrow with 10%.
Can someone explain this growth?
name rows reserved
form_lang 1 56 KB
grid_expr 0 0 KB
GridViewCategory 0 0 KB
GridViewDefinition 0 0 KB
hierarchy_tables 0 0 KB
invitation 0 0 KB
iteration 1 24 KB
lookup 0 0 KB
lookup_lang 0 0 KB
PanelistCredit 0 0 KB
quota 1 56 KB
response_control 2 104 KB
response1 0 0 KB
SampleLog 0 0 KB
weightmodel 0 0 KB
weightmodelform 0 0 KB|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in news:#noh#6kPGHA.1532
@.TK2MSFTNGP12.phx.gbl:
> Try running DBCC UPDATEUSAGE(0) and report back the space used.
>
Hi,
seems the first output from
USE MyDb; EXEC sp_MSforeachtable 'EXEC sp_spaceused [?]'
was missing some tables...
I ran UpdateUsage and the sp_spaceused again and found the reason for the
growth.
Case closed :-)
Thanks,sql

Sunday, March 25, 2012

Database fragmentation causing problems

I have really high fragmentation on the tables in my database even after running DBCC DBREINDEX and I was curious as to why this would happen. Could someone help me with this?

Hi Evidica. Could you provide some additional information on what type of fragmentation you are seeing and/or output from a sample dbcc showcontig on the given tables/indexes? Are you seeing extent scan fragmentation, logical scan fragmentation, what types of indexes do you have on the tables, etc.?

For example, if you are seeing extent scan fragmentation but have multiple data files in the given database, this is an expected result given the way pages/extents are allocated amoung files in a given database...

|||

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

http://www.microsoft.com/technet/abouttn/flash/tips/tips_083104.mspx

|||Sorry about leaving this dead. I did some research and found out that the person that created the database didnt know what they were doing. Turns out he had not put any primary keys on any of the tables.

Database fragmentation causing problems

I have really high fragmentation on the tables in my database even after running DBCC DBREINDEX and I was curious as to why this would happen. Could someone help me with this?

|||

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

http://www.microsoft.com/technet/abouttn/flash/tips/tips_083104.mspx

Database for storing Polish characters

Hi!
We have apps running on a English-based MS SQL Server 2000. One of our
clients
has asked about if it is possible to store Polish characters in database
whose collation
is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to install
their MS SQL Server 2000
server using the Collation for Polish language.
How about the data types used in tables ? we do not use UNICODE data
types such as nvarchar, etc.
Would it become a problem for storing polish characters into SQL Server
2000 which was installed with
collation for Polish ?
Regards,
-- Peter LoPeter,
From the SQL BOL:
"You can specify collations for each character string column using the
COLLATE clause of the CREATE TABLE or ALTER TABLE statement."
Another option if you're not using UNICODE data types.
HTH
Jerry
"Peter Lo" <peter.lo@.tradecapture.com> wrote in message
news:%23zxNdKuuFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi!
> We have apps running on a English-based MS SQL Server 2000. One of our
> clients
> has asked about if it is possible to store Polish characters in database
> whose collation
> is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to install
> their MS SQL Server 2000
> server using the Collation for Polish language.
> How about the data types used in tables ? we do not use UNICODE data
> types such as nvarchar, etc.
> Would it become a problem for storing polish characters into SQL Server
> 2000 which was installed with
> collation for Polish ?
>
> Regards,
> -- Peter Lo
>
>
>|||Thanks for quick response.
So, to be able to store Polish characters in MS SQL Server 2000, I have
two options:
Option #1:
1. install database with SQL_Latin1_General_CP1_CS_AS
2. As you suggested, use ALTER TABLE to set all string-columns to use
the
UNICODE data type
Option #2:
1. Install database with collation for Polish language
2. All string columns in tables must use UNICODE data types.
Please confirm. Thanks
-- Peter Lo
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eYsXiouuFHA.2568@.TK2MSFTNGP15.phx.gbl...
> Peter,
> From the SQL BOL:
> "You can specify collations for each character string column using the
> COLLATE clause of the CREATE TABLE or ALTER TABLE statement."
> Another option if you're not using UNICODE data types.
> HTH
> Jerry
> "Peter Lo" <peter.lo@.tradecapture.com> wrote in message
> news:%23zxNdKuuFHA.1032@.TK2MSFTNGP12.phx.gbl...
> > Hi!
> >
> > We have apps running on a English-based MS SQL Server 2000. One of our
> > clients
> > has asked about if it is possible to store Polish characters in
database
> > whose collation
> > is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to
install
> > their MS SQL Server 2000
> > server using the Collation for Polish language.
> >
> > How about the data types used in tables ? we do not use UNICODE data
> > types such as nvarchar, etc.
> > Would it become a problem for storing polish characters into SQL
Server
> > 2000 which was installed with
> > collation for Polish ?
> >
> >
> > Regards,
> >
> > -- Peter Lo
> >
> >
> >
> >
> >
> >
>|||Peter,
You can use a UNICODE data type, a column level collation or a database
level collation. Choice would probably be dependent on the number of
columns and tables that will be storing Polish characters.
HTH
Jerry
"Peter Lo" <peter.lo@.tradecapture.com> wrote in message
news:%238NEGCvuFHA.252@.TK2MSFTNGP09.phx.gbl...
> Thanks for quick response.
> So, to be able to store Polish characters in MS SQL Server 2000, I have
> two options:
> Option #1:
> 1. install database with SQL_Latin1_General_CP1_CS_AS
> 2. As you suggested, use ALTER TABLE to set all string-columns to use
> the
> UNICODE data type
> Option #2:
> 1. Install database with collation for Polish language
> 2. All string columns in tables must use UNICODE data types.
> Please confirm. Thanks
>
> -- Peter Lo
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eYsXiouuFHA.2568@.TK2MSFTNGP15.phx.gbl...
>> Peter,
>> From the SQL BOL:
>> "You can specify collations for each character string column using the
>> COLLATE clause of the CREATE TABLE or ALTER TABLE statement."
>> Another option if you're not using UNICODE data types.
>> HTH
>> Jerry
>> "Peter Lo" <peter.lo@.tradecapture.com> wrote in message
>> news:%23zxNdKuuFHA.1032@.TK2MSFTNGP12.phx.gbl...
>> > Hi!
>> >
>> > We have apps running on a English-based MS SQL Server 2000. One of
>> > our
>> > clients
>> > has asked about if it is possible to store Polish characters in
> database
>> > whose collation
>> > is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to
> install
>> > their MS SQL Server 2000
>> > server using the Collation for Polish language.
>> >
>> > How about the data types used in tables ? we do not use UNICODE data
>> > types such as nvarchar, etc.
>> > Would it become a problem for storing polish characters into SQL
> Server
>> > 2000 which was installed with
>> > collation for Polish ?
>> >
>> >
>> > Regards,
>> >
>> > -- Peter Lo
>> >
>> >
>> >
>> >
>> >
>> >
>>
>

Thursday, March 22, 2012

Database File and Disk Space

Hi All,
One of the drives that stores the database file is close to running out of space. The chance of me getting more space added to this drive any time soon are really low. What are other options I have?
Thanks.1. Open a new file segment on another drive
2. Shrink the existing log and or data file(s)
3. Tell TPTB that they are going to lose the ability to use the database due to lack of disk space.

Database field length problem

Hi everyone
I had an access database running as the source for a website but it
has become too large to run correctly so it has been ported to MS-SQL
the problem is that 4 of the fields were Memo fields in access and as
such are 5000+ characters long each this overflows the allowed size on
the SQL server (8192)

Is there a way round without splitting those 4 fields into seperate
tales?? as this would cause a truly major re-write of the website

Thanks for any help
Further details available if requiredIf you use VarChar can you not set the max field length to 8000 characters?

I can't believe SQL has such limits, surely there's a way to automatically
use two rows for one record, or does this require additional programming in
ASP?

Cheers, Ash

"Peter" <peter@.iib.ws> wrote in message
news:81307dbc.0406250528.ae1d3d@.posting.google.com ...
> Hi everyone
> I had an access database running as the source for a website but it
> has become too large to run correctly so it has been ported to MS-SQL
> the problem is that 4 of the fields were Memo fields in access and as
> such are 5000+ characters long each this overflows the allowed size on
> the SQL server (8192)
> Is there a way round without splitting those 4 fields into seperate
> tales?? as this would cause a truly major re-write of the website
> Thanks for any help
> Further details available if required|||Hi there
When i try to import a flat text file where i have used the transform
tool to delare field sizes of 6000 for the four fields i get the
following error if they are varchar

cannot create a row of size 8366 which is greater than the allowable
maximum of 8060

If i map them as ntext i dont get an error but the data is truncated

Any ideas
Peter

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Peter Winning (peter@.iib.ws) writes:
> When i try to import a flat text file where i have used the transform
> tool to delare field sizes of 6000 for the four fields i get the
> following error if they are varchar
> cannot create a row of size 8366 which is greater than the allowable
> maximum of 8060
> If i map them as ntext i dont get an error but the data is truncated

ntext is probably the way to go, since that is the only way to have
more than 8060 bytes of data on one row.

Why your ntext data is truncated I don't know, but then again I don't
know how import the data. A CREATE TABLE definition and a sample data
file could help. (You would have to pack the data file into a zip
file, since it surely would be wrecked by news transport, if you
posted it as text.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi
I am using the enterprise manager to upload the database and so far all
attempts no matter how the fields are transformed are still failing
I have tried mapping the fields as vchar, nvchar and ntext with the same
results in every case ie failure to complete or data truncation this has
also been attempted on the server itself
The upsizing wizard in access also failed to do the job

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Peter Winning (peter@.iib.ws) writes:
> I am using the enterprise manager to upload the database and so far all
> attempts no matter how the fields are transformed are still failing
> I have tried mapping the fields as vchar, nvchar and ntext with the same
> results in every case ie failure to complete or data truncation this has
> also been attempted on the server itself
> The upsizing wizard in access also failed to do the job

I have no idea what Enterprise Manager is up to when it imports data;
I didn't even know that it had a function for it, and even less have I
used it.

My general experience of EM, though, is that it tends to occlude some
syntax in order to be helpful, when things go over its head, it leaves
you alone in the dark.

I would try to import the file with BCP, but since I don't know how your
text file looks like, I cannot suggest the exact command line. I repeat
from my previous posting:

A CREATE TABLE definition and a sample data file could help. (You would
have to pack the data file into a zip file, since it surely would be
wrecked by news transport, if you posted it as text.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you
I have to confess that i am a complete novice with sql what is "BCP" and
where would i find some help in how to use it.
The database is a flat field one with about 30 fields 4 of which are
memo fields with very large amounts of data in each

Regards
Peter

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Peter Winning (peter@.iib.ws) writes:
> I have to confess that i am a complete novice with sql what is "BCP" and
> where would i find some help in how to use it.

BCP is a command line tool that permits you to load large amount of
data from files. The files can be text files or binary. BCP is a bit
restricted in that the file has to be fairly square. That is, it not able
to sort out headers, unless you can find a square hole to put them in.

You can read more about BCP in Books Online.

Another alternative is DTS (Data Transformation Service), which is a more
versatile load tool, which I have never used my self though.

> The database is a flat field one with about 30 fields 4 of which are
> memo fields with very large amounts of data in each

Well, it is up to you. If you don't want to post a CREATE TABLE statement
for your table and a sample data file, you don't have to. But then you will
have to find out how to load your file with BCP on your own, because I
don't really feel like guessing your table and data.

If you look in the SQL Server Program group, there is "Import and
Export Data". This takes you to the DTS wizard, which may be able to
guide all the way. But as I said, I have not used DTS. Then again,
there are some nice people in microsoft.public.sqlserver.dts who might
be able to help you if you go that way. But they, too, might want the
table definition and sample data.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

I don't think the DTS Import wizard will truncate the data if you import it
from an Access database! It could be that you are selecting the data in
Query Analyser which has a configurable value (Tools/Options/Results/Maximum
characters per column).

If you have a table such as

CREATE TABLE MyAccessTable ( id int, Memo1 ntext, Memo2 ntext, Memo3 ntext,
Memo4 ntext )

The you can see the number of characters using:
SELECT id,
datalength(memo1)/2,datalength(memo2)/2,datalength(memo3)/2,datalength(memo4
)/2 FROM MyAccessTable

John

"Peter Winning" <peter@.iib.ws> wrote in message
news:40dc5ecd$0$16435$c397aba@.news.newsgroups.ws.. .
> Hi there
> When i try to import a flat text file where i have used the transform
> tool to delare field sizes of 6000 for the four fields i get the
> following error if they are varchar
> cannot create a row of size 8366 which is greater than the allowable
> maximum of 8060
> If i map them as ntext i dont get an error but the data is truncated
> Any ideas
> Peter
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||I've just looked at our WebMail application we're using, it uses a SQL
database to store the messages which generally are over 8000 characters - it
uses NTEXT as the datatype, with a length of '16', how on EARTH does that
relate to a VARCHAR field that has to be set to 8000??

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9514A53A31CFFYazorman@.127.0.0.1...
> Peter Winning (peter@.iib.ws) writes:
> > When i try to import a flat text file where i have used the transform
> > tool to delare field sizes of 6000 for the four fields i get the
> > following error if they are varchar
> > cannot create a row of size 8366 which is greater than the allowable
> > maximum of 8060
> > If i map them as ntext i dont get an error but the data is truncated
> ntext is probably the way to go, since that is the only way to have
> more than 8060 bytes of data on one row.
> Why your ntext data is truncated I don't know, but then again I don't
> know how import the data. A CREATE TABLE definition and a sample data
> file could help. (You would have to pack the data file into a zip
> file, since it surely would be wrecked by news transport, if you
> posted it as text.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||J. Hall (remove_this_ash@.a-hall.com) writes:
> I've just looked at our WebMail application we're using, it uses a SQL
> database to store the messages which generally are over 8000 characters
> - it uses NTEXT as the datatype, with a length of '16', how on EARTH
> does that relate to a VARCHAR field that has to be set to 8000??

16 is the length of the pointer that is stored within the row. The data
itself is stored on separate pages. A varchar value on the other hand is
stored within the row, and since a row can not host more than 8060 bytes
of data, there is an upper limit.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Excellent thanks for clearing that up.

Many thanks,

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9517834BC4F9AYazorman@.127.0.0.1...
> J. Hall (remove_this_ash@.a-hall.com) writes:
> > I've just looked at our WebMail application we're using, it uses a SQL
> > database to store the messages which generally are over 8000 characters
> > - it uses NTEXT as the datatype, with a length of '16', how on EARTH
> > does that relate to a VARCHAR field that has to be set to 8000??
> 16 is the length of the pointer that is stored within the row. The data
> itself is stored on separate pages. A varchar value on the other hand is
> stored within the row, and since a row can not host more than 8060 bytes
> of data, there is an upper limit.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Wednesday, March 21, 2012

database errors on dbcc

We had a failure of a machine, but got the .mdf file off only. The .ldf was
unrecoverable. After restoring it, and running dbcc checkdb i get these
errors (although all seems ok in the database otherwise)
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46098). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46099). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46100). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46101). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46102). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46103). The PageId in the
page header = (0:0).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46097) could not be processed.
See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
(m_freeCnt == freeCnt) failed. Values are 0 and 8094.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
(emptySlotCnt == 0) failed. Values are 1 and 0.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46098) could not be processed.
See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46099) could not be processed.
See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46100) could not be processed.
See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46101) could not be processed.
See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46102) could not be processed.
See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46103) could not be processed.
See other errors for details.
DBCC results for myDatabase.
CHECKDB found 0 allocation errors and 6 consistency errors not associated
with any single object.
_____
DC G
Hi,
First and best option is restore from the latest backup into a new database
and restore the transaction logs, because the data consistency would have
been
lost since the LDF file is lost/corrupted.
If you do not have any backup files then try the below options:
DBCC CHECKDB('dbname','REPAIR_REBUILD ')
if you still have errors then try using
DBCC CHECKDB('DBNAME','REPAIR_ALLOW_DATA_LOSS')
The above command can be ran only in single user mode and will result in
data loss. so the comamnd is not at all recommended.
Thanks
Hari
MCDBA
"DC Gringo" <dcgringo@.visiontechnology.net> wrote in message
news:OMuhAlDnEHA.1800@.TK2MSFTNGP15.phx.gbl...
> We had a failure of a machine, but got the .mdf file off only. The .ldf
> was
> unrecoverable. After restoring it, and running dbcc checkdb i get these
> errors (although all seems ok in the database otherwise)
>
>
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46098). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46099). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46100). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46101). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46102). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46103). The PageId in the
> page header = (0:0).
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46097) could not be processed.
> See other errors for details.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
> (m_freeCnt == freeCnt) failed. Values are 0 and 8094.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
> (emptySlotCnt == 0) failed. Values are 1 and 0.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46098) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46099) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46100) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46101) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46102) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46103) could not be processed.
> See other errors for details.
> DBCC results for myDatabase.
> CHECKDB found 0 allocation errors and 6 consistency errors not associated
> with any single object.
> --
> _____
> DC G
>
|||The most recent update of Books Online has explicit recommendation for most error numbers. Search
for your error numbers in Books Online and see what the recommendations are. Also, you might want to
read:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DC Gringo" <dcgringo@.visiontechnology.net> wrote in message
news:OMuhAlDnEHA.1800@.TK2MSFTNGP15.phx.gbl...
> We had a failure of a machine, but got the .mdf file off only. The .ldf was
> unrecoverable. After restoring it, and running dbcc checkdb i get these
> errors (although all seems ok in the database otherwise)
>
>
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46098). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46099). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46100). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46101). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46102). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46103). The PageId in the
> page header = (0:0).
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46097) could not be processed.
> See other errors for details.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
> (m_freeCnt == freeCnt) failed. Values are 0 and 8094.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
> (emptySlotCnt == 0) failed. Values are 1 and 0.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46098) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46099) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46100) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46101) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46102) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46103) could not be processed.
> See other errors for details.
> DBCC results for myDatabase.
> CHECKDB found 0 allocation errors and 6 consistency errors not associated
> with any single object.
> --
> _____
> DC G
>

database errors on dbcc

We had a failure of a machine, but got the .mdf file off only. The .ldf was
unrecoverable. After restoring it, and running dbcc checkdb i get these
errors (although all seems ok in the database otherwise)
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46098). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46099). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46100). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46101). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46102). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46103). The PageId in the
page header = (0:0).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46097) could not be processed.
See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
(m_freeCnt == freeCnt) failed. Values are 0 and 8094.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
(emptySlotCnt == 0) failed. Values are 1 and 0.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46098) could not be processed.
See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46099) could not be processed.
See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46100) could not be processed.
See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46101) could not be processed.
See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46102) could not be processed.
See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46103) could not be processed.
See other errors for details.
DBCC results for myDatabase.
CHECKDB found 0 allocation errors and 6 consistency errors not associated
with any single object.
--
_____
DC GHi,
First and best option is restore from the latest backup into a new database
and restore the transaction logs, because the data consistency would have
been
lost since the LDF file is lost/corrupted.
If you do not have any backup files then try the below options:
DBCC CHECKDB('dbname','REPAIR_REBUILD ')
if you still have errors then try using
DBCC CHECKDB('DBNAME','REPAIR_ALLOW_DATA_LOSS')
The above command can be ran only in single user mode and will result in
data loss. so the comamnd is not at all recommended.
Thanks
Hari
MCDBA
"DC Gringo" <dcgringo@.visiontechnology.net> wrote in message
news:OMuhAlDnEHA.1800@.TK2MSFTNGP15.phx.gbl...
> We had a failure of a machine, but got the .mdf file off only. The .ldf
> was
> unrecoverable. After restoring it, and running dbcc checkdb i get these
> errors (although all seems ok in the database otherwise)
>
>
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46098). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46099). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46100). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46101). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46102). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46103). The PageId in the
> page header = (0:0).
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46097) could not be processed.
> See other errors for details.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
> (m_freeCnt == freeCnt) failed. Values are 0 and 8094.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
> (emptySlotCnt == 0) failed. Values are 1 and 0.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46098) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46099) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46100) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46101) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46102) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46103) could not be processed.
> See other errors for details.
> DBCC results for myDatabase.
> CHECKDB found 0 allocation errors and 6 consistency errors not associated
> with any single object.
> --
> _____
> DC G
>|||The most recent update of Books Online has explicit recommendation for most error numbers. Search
for your error numbers in Books Online and see what the recommendations are. Also, you might want to
read:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DC Gringo" <dcgringo@.visiontechnology.net> wrote in message
news:OMuhAlDnEHA.1800@.TK2MSFTNGP15.phx.gbl...
> We had a failure of a machine, but got the .mdf file off only. The .ldf was
> unrecoverable. After restoring it, and running dbcc checkdb i get these
> errors (although all seems ok in the database otherwise)
>
>
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46098). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46099). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46100). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46101). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46102). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46103). The PageId in the
> page header = (0:0).
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46097) could not be processed.
> See other errors for details.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
> (m_freeCnt == freeCnt) failed. Values are 0 and 8094.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
> (emptySlotCnt == 0) failed. Values are 1 and 0.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46098) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46099) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46100) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46101) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46102) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46103) could not be processed.
> See other errors for details.
> DBCC results for myDatabase.
> CHECKDB found 0 allocation errors and 6 consistency errors not associated
> with any single object.
> --
> _____
> DC G
>sql

Monday, March 19, 2012

Database dump size

Hi there. Running a Sql 2000 installation with latest service pack on
Windows 2000 server. My database dump to disk backup keeps increasing
by about 1 meg per day. The database itself is increasing by a small
percentage of that. In fact the dump is now about the same size as the
database. What's going on? Thanks.
What kind of backup are you doing? If you don't use the INIT option the
current backup will append to the existing ones in the dump device.
Andrew J. Kelly
SQL Server MVP
"cscott" <christopher@.uncommonlaw.com> wrote in message
news:a94f8da8.0406070533.6a59d6fb@.posting.google.c om...
> Hi there. Running a Sql 2000 installation with latest service pack on
> Windows 2000 server. My database dump to disk backup keeps increasing
> by about 1 meg per day. The database itself is increasing by a small
> percentage of that. In fact the dump is now about the same size as the
> database. What's going on? Thanks.
|||Hi ,
To add on to Andrew post, Execute the below comand from query analyzer to
check if BACKUP is executed in append mode.
restore headeronly from disk='Directory\dbname.BAK'
If this list more than 1 entry (Check the postion column in output), means
you have taken the backup in Append mode. To remove he append mode add an
OPTION
INIT along with backup database command
BACKUP DATABASE dbname to disk='c:\dbname.bak' with INIT
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uYtrikJTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> What kind of backup are you doing? If you don't use the INIT option the
> current backup will append to the existing ones in the dump device.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "cscott" <christopher@.uncommonlaw.com> wrote in message
> news:a94f8da8.0406070533.6a59d6fb@.posting.google.c om...
>
|||Used the maintenance plan wizard to set it up. It's a dump to disk.
There aren't many options to select really. It does seem like it's
appending the dump. Is there an option in Enterprise manager to disable
append or will I have to do it the hard way? Thanks.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Maint plan doesn't append. It creates a new backup device each time it performs a backup. Verify using RESTORE
HEADERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Christopher Scott" <christopher@.uncommonlaw.com> wrote in message
news:%23cTXggVTEHA.2416@.TK2MSFTNGP12.phx.gbl...
>
> Used the maintenance plan wizard to set it up. It's a dump to disk.
> There aren't many options to select really. It does seem like it's
> appending the dump. Is there an option in Enterprise manager to disable
> append or will I have to do it the hard way? Thanks.
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Database dump size

Hi there. Running a Sql 2000 installation with latest service pack on
Windows 2000 server. My database dump to disk backup keeps increasing
by about 1 meg per day. The database itself is increasing by a small
percentage of that. In fact the dump is now about the same size as the
database. What's going on? Thanks.What kind of backup are you doing? If you don't use the INIT option the
current backup will append to the existing ones in the dump device.
Andrew J. Kelly
SQL Server MVP
"cscott" <christopher@.uncommonlaw.com> wrote in message
news:a94f8da8.0406070533.6a59d6fb@.posting.google.com...
> Hi there. Running a Sql 2000 installation with latest service pack on
> Windows 2000 server. My database dump to disk backup keeps increasing
> by about 1 meg per day. The database itself is increasing by a small
> percentage of that. In fact the dump is now about the same size as the
> database. What's going on? Thanks.|||Hi ,
To add on to Andrew post, Execute the below comand from query analyzer to
check if BACKUP is executed in append mode.
restore headeronly from disk='Directory\dbname.BAK'
If this list more than 1 entry (Check the postion column in output), means
you have taken the backup in Append mode. To remove he append mode add an
OPTION
INIT along with backup database command
BACKUP DATABASE dbname to disk='c:\dbname.bak' with INIT
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uYtrikJTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> What kind of backup are you doing? If you don't use the INIT option the
> current backup will append to the existing ones in the dump device.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "cscott" <christopher@.uncommonlaw.com> wrote in message
> news:a94f8da8.0406070533.6a59d6fb@.posting.google.com...
>|||Used the maintenance plan wizard to set it up. It's a dump to disk.
There aren't many options to select really. It does seem like it's
appending the dump. Is there an option in Enterprise manager to disable
append or will I have to do it the hard way? Thanks.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Maint plan doesn't append. It creates a new backup device each time it perfo
rms a backup. Verify using RESTORE
HEADERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Christopher Scott" <christopher@.uncommonlaw.com> wrote in message
news:%23cTXggVTEHA.2416@.TK2MSFTNGP12.phx.gbl...
>
> Used the maintenance plan wizard to set it up. It's a dump to disk.
> There aren't many options to select really. It does seem like it's
> appending the dump. Is there an option in Enterprise manager to disable
> append or will I have to do it the hard way? Thanks.
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Database dump size

Hi there. Running a Sql 2000 installation with latest service pack on
Windows 2000 server. My database dump to disk backup keeps increasing
by about 1 meg per day. The database itself is increasing by a small
percentage of that. In fact the dump is now about the same size as the
database. What's going on? Thanks.Hello
Can you please give us details of the type of backup your
doing as well as your recovery model.
J
>--Original Message--
>Hi there. Running a Sql 2000 installation with latest
service pack on
>Windows 2000 server. My database dump to disk backup
keeps increasing
>by about 1 meg per day. The database itself is increasing
by a small
>percentage of that. In fact the dump is now about the
same size as the
>database. What's going on? Thanks.
>.
>|||What kind of backup are you doing? If you don't use the INIT option the
current backup will append to the existing ones in the dump device.
--
Andrew J. Kelly
SQL Server MVP
"cscott" <christopher@.uncommonlaw.com> wrote in message
news:a94f8da8.0406070533.6a59d6fb@.posting.google.com...
> Hi there. Running a Sql 2000 installation with latest service pack on
> Windows 2000 server. My database dump to disk backup keeps increasing
> by about 1 meg per day. The database itself is increasing by a small
> percentage of that. In fact the dump is now about the same size as the
> database. What's going on? Thanks.|||Hi ,
To add on to Andrew post, Execute the below comand from query analyzer to
check if BACKUP is executed in append mode.
restore headeronly from disk='Directory\dbname.BAK'
If this list more than 1 entry (Check the postion column in output), means
you have taken the backup in Append mode. To remove he append mode add an
OPTION
INIT along with backup database command
BACKUP DATABASE dbname to disk='c:\dbname.bak' with INIT
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uYtrikJTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> What kind of backup are you doing? If you don't use the INIT option the
> current backup will append to the existing ones in the dump device.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "cscott" <christopher@.uncommonlaw.com> wrote in message
> news:a94f8da8.0406070533.6a59d6fb@.posting.google.com...
> > Hi there. Running a Sql 2000 installation with latest service pack on
> > Windows 2000 server. My database dump to disk backup keeps increasing
> > by about 1 meg per day. The database itself is increasing by a small
> > percentage of that. In fact the dump is now about the same size as the
> > database. What's going on? Thanks.
>

Sunday, March 11, 2012

Database disappeared from sysdatabases after running out of file space

Guys and gals, I'm in all sorts of trouble here. I've been nursing a server
with very little free space for my sql databases while I get a new one
built, and it's been pretty safe, until a power cut the other day caused
some sort of huge write to the log file and it ran out of space. I mean,
really ran out. The mdf and logs are at about 29.5 gigs and the hdd is
30gig. So, I needed to backup the transaction log, reorganise and shrink
the database. Couldn't do that, no elbow room, so I thought I'd move the
files to a USB hdd I have on there, slow but ok temporarily, and do the
reorg there. Unfortunately, detatching the database has, somehow caused the
sysdatabases table to lose any knowledge of it. I've got the files in their
new location and would dearly love to be able to reattach them somehow to
shrink them down but I'm at a loss now.
I'd really appreciate a pointer here, I've run out of ideas.
Cheers
Chris Weston"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:w9YSf.2115$g76.696@.newsfe2-gui.ntli.net...
> Guys and gals, I'm in all sorts of trouble here. I've been nursing a
server
> with very little free space for my sql databases while I get a new one
> built, and it's been pretty safe, until a power cut the other day caused
> some sort of huge write to the log file and it ran out of space. I mean,
> really ran out. The mdf and logs are at about 29.5 gigs and the hdd is
> 30gig. So, I needed to backup the transaction log, reorganise and shrink
> the database. Couldn't do that, no elbow room, so I thought I'd move the
> files to a USB hdd I have on there, slow but ok temporarily, and do the
> reorg there. Unfortunately, detatching the database has, somehow caused
the
> sysdatabases table to lose any knowledge of it. I've got the files in
their
> new location and would dearly love to be able to reattach them somehow to
> shrink them down but I'm at a loss now.
> I'd really appreciate a pointer here, I've run out of ideas.
Just to follow up here, I've tried attaching the database from the context
menu on my databases folder, but it complains about the log file (all 22g of
it) being faulty. Can't I attach the mdf and let the log file be recreated?
I'm no sql expert but the mdf is the important part, surely?
Thanks,
--
Chris Weston|||sp_attach_single_file_db [ @.dbname = ] 'dbname'
, [ @.physname = ] 'physical_name'
It will generate a new log file.
good luck.
d.
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:tIYSf.2054$H%3.875@.newsfe5-gui.ntli.net...
> "Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
> news:w9YSf.2115$g76.696@.newsfe2-gui.ntli.net...
> > Guys and gals, I'm in all sorts of trouble here. I've been nursing a
> server
> > with very little free space for my sql databases while I get a new one
> > built, and it's been pretty safe, until a power cut the other day caused
> > some sort of huge write to the log file and it ran out of space. I
mean,
> > really ran out. The mdf and logs are at about 29.5 gigs and the hdd is
> > 30gig. So, I needed to backup the transaction log, reorganise and
shrink
> > the database. Couldn't do that, no elbow room, so I thought I'd move
the
> > files to a USB hdd I have on there, slow but ok temporarily, and do the
> > reorg there. Unfortunately, detatching the database has, somehow caused
> the
> > sysdatabases table to lose any knowledge of it. I've got the files in
> their
> > new location and would dearly love to be able to reattach them somehow
to
> > shrink them down but I'm at a loss now.
> >
> > I'd really appreciate a pointer here, I've run out of ideas.
> Just to follow up here, I've tried attaching the database from the context
> menu on my databases folder, but it complains about the log file (all 22g
of
> it) being faulty. Can't I attach the mdf and let the log file be
recreated?
> I'm no sql expert but the mdf is the important part, surely?
> Thanks,
> --
> Chris Weston
>|||"d" <d@.d.com> wrote in message news:ruZSf.1311$I7.1016@.trnddc03...
> sp_attach_single_file_db [ @.dbname = ] 'dbname'
> , [ @.physname = ] 'physical_name'
> It will generate a new log file.
>
> good luck.
>
I appreciate the sentiment :) However, I'm not sure where to run this
command? In a query window in Enterprise Manager?
Many thanks,
Chris Weston|||Hi Chris
This is expected behavior. When you detach a database, it is completely
removed from the system. It is different from DROP in that the files still
exist on disk.
To get the db back, you have to attach it.
If you are attaching through Enterprise Manager, and SQL Server complains
about the log, you can try changing the name of the log file on disk so EM
can't find it, and see if a new log will be rebuilt.
Or, as d suggested, you can run the procedure from sp_attach_single_file_db
from a query window in Query Analyzer.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:w9YSf.2115$g76.696@.newsfe2-gui.ntli.net...
> Guys and gals, I'm in all sorts of trouble here. I've been nursing a
> server
> with very little free space for my sql databases while I get a new one
> built, and it's been pretty safe, until a power cut the other day caused
> some sort of huge write to the log file and it ran out of space. I mean,
> really ran out. The mdf and logs are at about 29.5 gigs and the hdd is
> 30gig. So, I needed to backup the transaction log, reorganise and shrink
> the database. Couldn't do that, no elbow room, so I thought I'd move the
> files to a USB hdd I have on there, slow but ok temporarily, and do the
> reorg there. Unfortunately, detatching the database has, somehow caused
> the
> sysdatabases table to lose any knowledge of it. I've got the files in
> their
> new location and would dearly love to be able to reattach them somehow to
> shrink them down but I'm at a loss now.
> I'd really appreciate a pointer here, I've run out of ideas.
> Cheers
> Chris Weston
>
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uKXsmotSGHA.5500@.TK2MSFTNGP12.phx.gbl...
> Hi Chris
> This is expected behavior. When you detach a database, it is completely
> removed from the system. It is different from DROP in that the files still
> exist on disk.
> To get the db back, you have to attach it.
> If you are attaching through Enterprise Manager, and SQL Server complains
> about the log, you can try changing the name of the log file on disk so EM
> can't find it, and see if a new log will be rebuilt.
> Or, as d suggested, you can run the procedure from
sp_attach_single_file_db
> from a query window in Query Analyzer.
Still, no joy - I have tried both these methods but still get an error
saying that it can't find the .ldf file. How can I get past this?
Thanks,
Chris Weston|||Hi
The methods described by Kalen should work if you followed them exactly.
Can you post the exact sp_attach_single_file_db command that you have used?
Make sure that you have move or renamed the ldf file and there is enough
space to create a new one.
Make sure that the mdf file does exist in the directory you have specified.
Make sure that directory where it wants to create the ldf file exists and
that the permissions allow you to create a new file.
John
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:dz8Tf.2219$g76.437@.newsfe2-gui.ntli.net...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uKXsmotSGHA.5500@.TK2MSFTNGP12.phx.gbl...
>> Hi Chris
>> This is expected behavior. When you detach a database, it is completely
>> removed from the system. It is different from DROP in that the files
>> still
>> exist on disk.
>> To get the db back, you have to attach it.
>> If you are attaching through Enterprise Manager, and SQL Server complains
>> about the log, you can try changing the name of the log file on disk so
>> EM
>> can't find it, and see if a new log will be rebuilt.
>> Or, as d suggested, you can run the procedure from
> xslmailinglist@.btopenworld.com>> from a query window in Query Analyzer.
> Still, no joy - I have tried both these methods but still get an error
> saying that it can't find the .ldf file. How can I get past this?
> Thanks,
> Chris Weston
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e%23SFB3zSGHA.4600@.TK2MSFTNGP11.phx.gbl...
> Hi
> The methods described by Kalen should work if you followed them exactly.
> Can you post the exact sp_attach_single_file_db command that you have
used?
> Make sure that you have move or renamed the ldf file and there is enough
> space to create a new one.
> Make sure that the mdf file does exist in the directory you have
specified.
> Make sure that directory where it wants to create the ldf file exists and
> that the permissions allow you to create a new file.
It exists, it's my default sql data directory and I'm logged in as
administrator. The mdf exists. I'm using
EXEC sp_attach_single_file_db @.dbname = 'SysCompanyA',
@.physname = N'h:\systemp\SyscompA.mdf'
but I get an error saying that 'the physical file name 'E:\program
files\...\data\syscompanyA_log.ldf may be incorrect'
So is it saying it can't create or that it can't find the ldf file?
--
Chris Weston|||HI Chris
Was E:\ the original drive? It looks like when SQL Server creates a new log
file, it wants to create it on the drive where it originally existed.
I have use sp_attach_db to move a db, or to rebuild a log, but never at the
same time.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:o0hTf.2330$g76.772@.newsfe2-gui.ntli.net...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:e%23SFB3zSGHA.4600@.TK2MSFTNGP11.phx.gbl...
>> Hi
>> The methods described by Kalen should work if you followed them exactly.
>> Can you post the exact sp_attach_single_file_db command that you have
> used?
>> Make sure that you have move or renamed the ldf file and there is enough
>> space to create a new one.
>> Make sure that the mdf file does exist in the directory you have
> specified.
>> Make sure that directory where it wants to create the ldf file exists and
>> that the permissions allow you to create a new file.
> It exists, it's my default sql data directory and I'm logged in as
> administrator. The mdf exists. I'm using
> EXEC sp_attach_single_file_db @.dbname = 'SysCompanyA',
> @.physname = N'h:\systemp\SyscompA.mdf'
> but I get an error saying that 'the physical file name 'E:\program
> files\...\data\syscompanyA_log.ldf may be incorrect'
> So is it saying it can't create or that it can't find the ldf file?
> --
> Chris Weston
>
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OUXQlB9SGHA.5172@.TK2MSFTNGP12.phx.gbl...
> HI Chris
> Was E:\ the original drive? It looks like when SQL Server creates a new
log
> file, it wants to create it on the drive where it originally existed.
> I have use sp_attach_db to move a db, or to rebuild a log, but never at
the
> same time.
Kalen,
E:\ was the original drive, and it still exists. I've no problem with it
creating the log there if need be.
--
Chris Weston|||Chris Weston wrote:
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OUXQlB9SGHA.5172@.TK2MSFTNGP12.phx.gbl...
>> HI Chris
>> Was E:\ the original drive? It looks like when SQL Server creates a new
> log
>> file, it wants to create it on the drive where it originally existed.
>> I have use sp_attach_db to move a db, or to rebuild a log, but never at
> the
>> same time.
> Kalen,
> E:\ was the original drive, and it still exists. I've no problem with it
> creating the log there if need be.
>
You have to make sure that the full path/folder exists before you run
the command. SQL server can't create the folder so if it's not present
you'll get an error message like the one you see.
Regards
Steen|||So make sure there is no file on E with the name and path the log should
have. Make sure the directory exists on E and that the Windows account that
SQL Server runs under has access to write to the directory.
Once the attach succeeds, it should have a very small log file. You can then
detach it, move the new small log file to your new location, and then attach
again, specifying explicitly the log file location.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:7msTf.11031$5B4.10504@.newsfe6-gui.ntli.net...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OUXQlB9SGHA.5172@.TK2MSFTNGP12.phx.gbl...
>> HI Chris
>> Was E:\ the original drive? It looks like when SQL Server creates a new
> log
>> file, it wants to create it on the drive where it originally existed.
>> I have use sp_attach_db to move a db, or to rebuild a log, but never at
> the
>> same time.
> Kalen,
> E:\ was the original drive, and it still exists. I've no problem with it
> creating the log there if need be.
> --
> Chris Weston
>
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uUKGRCETGHA.4740@.TK2MSFTNGP14.phx.gbl...
> So make sure there is no file on E with the name and path the log should
> have. Make sure the directory exists on E and that the Windows account
that
> SQL Server runs under has access to write to the directory.
> Once the attach succeeds, it should have a very small log file. You can
then
> detach it, move the new small log file to your new location, and then
attach
> again, specifying explicitly the log file location.
Folks, thanks very much for your efforts. I couldn't attach this db, it
just wasn't having it. I ended up restoring from a backup and we'll just
have to do the work to bring it up to date. I did eventually attach the db
using the procedure detailed here
http://www.spaceprogram.com/knowledge/2002/06/recovering-from-deleted-log-file-on_12.html
although I had to repair it using allow_data_loss, and I can use it to help
me get the restored data back to usability.
So, thanks for your thoughts on this one, but may I ask one question? Why
the consistent top-posting in this ng?
Thanks
--
Chris Weston

Thursday, March 29, 2012

Database is running slow after running DBCC Shrinkdatabase command

Tuesday, March 27, 2012

Database Hammer & Stress testing

Database Hammer & Stress testing

Database Hammer & Stress testing

Database growing unexpectedly

Database growing unexpectedly

Database growing unexpectedly

Sunday, March 25, 2012

Database fragmentation causing problems

Database fragmentation causing problems

Database for storing Polish characters

Database for storing Polish characters

Database for storing Polish characters

Thursday, March 22, 2012

Database File and Disk Space

Database field length problem

Wednesday, March 21, 2012

database errors on dbcc

database errors on dbcc

Monday, March 19, 2012

Database dump size

Database dump size

Database dump size

Sunday, March 11, 2012

Database disappeared from sysdatabases after running out of file space

Database level lock

Blog Archive

About Me