Data File will not shrink

I have an issue, and hopefully I can explain this fully enough. We
have a 3 server environment, all running SQL Server 2000 SP4 on
Windows 2000 Server. Server A, Server B, and Server C. Server A
replicates to Server B, Server B then replicates to Server C.
Server B has a database that two data files and two log files on two
separate drives. So drive D has 1 datafile and 1 log file, and drive
E has the same. Drive E is running out of disk space and drive D had
some to spare. In Enterprise Manager, I went to the Shrink Database
menu for that database, and I choosed 'Empty the file (data will
migrate to other files in the file group) option. That finished
successfully. But now I see that only 2 mb is being used, but the
database size is still 22358 mb. I have tried the other shrink
options with no luck.
So I have tried:
-Compress Pages and then truncate free space from the file
-Truncate free space from the other end of file
-Shrink file to "min size"
-I have also tried checking the 'Move pages to beginning of file
before shrinking, then retrying the steps above.
Also, we have tried shrinking the transaction table using a stored
proceedure called sp_forceshrink_log as well as a few other things we
have seen online.
After all this, the file size is still 20+ gb. My question is this: Is
there anything else we can try to free up the close to 20 gigs of
space? Also, this database is in the middle of a replicated chain,
what effect would there be in deleting the empty file in the
properties of the database. Is that even possible with replication
enabled?
Thanks for any help you can provide.
JonIf you want to shrink a log file, you should do it immediately after
you backup the transaction log. If you do it later, you will get a
warning like "Cannot shrink log file 2 (LogFile) because all logical
log files are in use." and the file will not be shrinked.
If you get a warning saying "The log was not truncated because records
at the beginning of the log are pending replication. Ensure the Log
Reader Agent is running or use sp_repldone to mark transactions as
distributed", you should see this thread, for example:
http://groups.google.com/group/comp.databases.ms-sqlserver/browse_frm/th
read/23ca52b9df791e59/
In any case, you should not delete the transaction log file (especially
if the database is in a transactional replication chain).
--
Razvan Socol
SQL Server MVP|||Thanks for your response, but the issue is not with the log file, but
the data file. I emptied the contents of one data file to another
data file on another drive. So I have 2 mb used in the file, but the
file size is 20+ gb on the physical drive. I am unable to shrink the
file size using the options found in the "Shrink Database" window. So
I am looking for alternatives to getting the data file size down.
Jon
On Feb 3, 2:42 am, "Razvan Socol" <rso...@.gmail.com> wrote:
> If you want to shrink a log file, you should do it immediately after
> you backup the transaction log. If you do it later, you will get a
> warning like "Cannot shrink log file 2 (LogFile) because all logical
> log files are in use." and the file will not be shrinked.
> If you get a warning saying "The log was not truncated because records
> at the beginning of the log are pending replication. Ensure the Log
> Reader Agent is running or use sp_repldone to mark transactions as
> distributed", you should see this thread, for example:http://groups.google.com/group/comp.databases.ms-sqlserver/browse_frm/th
> read/23ca52b9df791e59/
> In any case, you should not delete the transaction log file (especially
> if the database is in a transactional replication chain).
> --
> Razvan Socol
> SQL Server MVP

Data File Remaining Space

I am running SQL 2000. I have a data file set up with restricted growth. Is
there a stored procedure or script available that I can schedule or run
when I want manually that will give me the space remaining in the data file?
Message posted via http://www.sqlmonster.com
Checkout sp_spaceused in BOL.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQLMonster.c om...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.sqlmonster.com
|||sp_helpdb YourDBNameHere
gives some information regarding database and file size.
Keith
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQLMonster.c om...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.sqlmonster.com

Data File Remaining Space

I am running SQL 2000. I have a data file set up with restricted growth. Is
there a stored procedure or script available that I can schedule or run
when I want manually that will give me the space remaining in the data file?
Message posted via http://www.droptable.comCheckout sp_spaceused in BOL.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQ
droptable.com...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.droptable.com|||sp_helpdb YourDBNameHere
gives some information regarding database and file size.
Keith
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQ
droptable.com...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.droptable.com

Data File Remaining Space

I am running SQL 2000. I have a data file set up with restricted growth. Is
there a stored procedure or script available that I can schedule or run
when I want manually that will give me the space remaining in the data file?
--
Message posted via http://www.sqlmonster.comCheckout sp_spaceused in BOL.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQLMonster.com...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.sqlmonster.com|||sp_helpdb YourDBNameHere
gives some information regarding database and file size.
--
Keith
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQLMonster.com...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.sqlmonster.comsql

Data Driven Subscriptions unavailable, please help

I'm running sql2000 Standard with SP4 applied. I also have Reporting Services 2000 with service packs running on the same machine. I'm trying to get a data-driven subscription, but the button to access is unavailable.

I've assigned the account I'm working under the Publisher role and have modified this role to have all permissions (including managing subscriptions). I have stored credentials with my data source as well.

I can create a standard subscription with no problems, but just don't get the button to do the data-driven option.

Any assistance is appreciated.

You are running RS 2000 Standard Edition. Data driven subscriptions are only available on Enterprise Edition and Developer Edition.

The same applies to data driven subscriptions on RS 2005: http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx

-- Robert

|||Is it possible to run the enterprise edition of Reporting Services on the Standard edition of SQL 2000?|||

Actually, you can run a Reporting Services Enterprise Edition with its metadata store on a SQL Server Standard Edition.

-- Robert

|||We have the same issue and we are using the enterprise version. The user who is trying to set-up the data-driven subscription is also an admin on the report server settings...|||

Does anybody know the license implications if you install reporting services 2000 enterprise on a sql server 2000 standard install?

|||Does anybody know the license implications if you install reporting services 2000 enterprise on a sql server 2000 standard install?

Data Driven Subscriptions unavailable, please help

I'm running sql2000 Standard with SP4 applied. I also have Reporting Services 2000 with service packs running on the same machine. I'm trying to get a data-driven subscription, but the button to access is unavailable.

I've assigned the account I'm working under the Publisher role and have modified this role to have all permissions (including managing subscriptions). I have stored credentials with my data source as well.

I can create a standard subscription with no problems, but just don't get the button to do the data-driven option.

Any assistance is appreciated.

You are running RS 2000 Standard Edition. Data driven subscriptions are only available on Enterprise Edition and Developer Edition.

The same applies to data driven subscriptions on RS 2005: http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx

-- Robert

|||Is it possible to run the enterprise edition of Reporting Services on the Standard edition of SQL 2000?|||

Actually, you can run a Reporting Services Enterprise Edition with its metadata store on a SQL Server Standard Edition.

-- Robert

|||We have the same issue and we are using the enterprise version. The user who is trying to set-up the data-driven subscription is also an admin on the report server settings...|||

Does anybody know the license implications if you install reporting services 2000 enterprise on a sql server 2000 standard install?

|||Does anybody know the license implications if you install reporting services 2000 enterprise on a sql server 2000 standard install?sql

Data Driven Subscriptions unavailable, please help

I'm running sql2000 Standard with SP4 applied. I also have Reporting Services 2000 with service packs running on the same machine. I'm trying to get a data-driven subscription, but the button to access is unavailable.

I've assigned the account I'm working under the Publisher role and have modified this role to have all permissions (including managing subscriptions). I have stored credentials with my data source as well.

I can create a standard subscription with no problems, but just don't get the button to do the data-driven option.

Any assistance is appreciated.

You are running RS 2000 Standard Edition. Data driven subscriptions are only available on Enterprise Edition and Developer Edition.

The same applies to data driven subscriptions on RS 2005: http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx

-- Robert

|||Is it possible to run the enterprise edition of Reporting Services on the Standard edition of SQL 2000?|||

Actually, you can run a Reporting Services Enterprise Edition with its metadata store on a SQL Server Standard Edition.

-- Robert

|||We have the same issue and we are using the enterprise version. The user who is trying to set-up the data-driven subscription is also an admin on the report server settings...|||

Does anybody know the license implications if you install reporting services 2000 enterprise on a sql server 2000 standard install?

|||Does anybody know the license implications if you install reporting services 2000 enterprise on a sql server 2000 standard install?

Data corruption on a replicated database

We are running SQL 2000 EI SP3 on a Windows 2000 Adv. Server
We have recently setup a database for replication using transactional replication every ten minutes
Shortly after implementing this, we started receiving integrity errors on the database on a daily basis
Many of the errors are requiring checktable with repair data loss to correct the problem. The corruption has shown up on both replicated and non-replicated tables, but only occurs if replication is running. As soon as replication was stopped, the database quit reporting integrity errors
Please let me know if you need further information
Has anyone else ran into this?I don't think replication has anything to do with it. Probably replication
is stressing your hard disk subsystem, leading to errors. So the real
culprit could be hardware. Check your event viewer and any hardware logs for
clues.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:B6B0118A-C15D-41A5-9E11-FA01F4AD14BD@.microsoft.com...
We are running SQL 2000 EI SP3 on a Windows 2000 Adv. Server.
We have recently setup a database for replication using transactional
replication every ten minutes.
Shortly after implementing this, we started receiving integrity errors on
the database on a daily basis.
Many of the errors are requiring checktable with repair data loss to correct
the problem. The corruption has shown up on both replicated and
non-replicated tables, but only occurs if replication is running. As soon
as replication was stopped, the database quit reporting integrity errors.
Please let me know if you need further information.
Has anyone else ran into this?|||Our first thought was hardware as well, however, a few things are pointing to something else causing the problem
1. All of the hardware is brand new, and the server is running at about a quarter of our target performance. (This server is not yet hosting databases that will be added later this year... During peak time we are seeing a max utilization of 15% across all processors). The hardware vendor has already been over this server and hasn't found anything, and there is no hardware related errors in the server event log
2. This is the first enterprise class server we have run in a SAN environment. To eliminate the SAN hardware or network we moved all of the data to internal drives on the server. The problem continues to occur
3. This server is hosting multiple application databases. The only database with errors is the replicated database. If this was hardware related we would expect to see the other databases having problems, as they were using the same internal hardware and SAN array to hold their data as the replicated database
Any thoughts|||Okay, could you post the exact errors you are seeing in the SQL Server error
logs?
Are you replicating text or image columns?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:34BB1D32-6935-40A8-B061-1372260A868B@.microsoft.com...
Our first thought was hardware as well, however, a few things are pointing
to something else causing the problem:
1. All of the hardware is brand new, and the server is running at about a
quarter of our target performance. (This server is not yet hosting databases
that will be added later this year... During peak time we are seeing a max
utilization of 15% across all processors). The hardware vendor has already
been over this server and hasn't found anything, and there is no hardware
related errors in the server event logs
2. This is the first enterprise class server we have run in a SAN
environment. To eliminate the SAN hardware or network we moved all of the
data to internal drives on the server. The problem continues to occur.
3. This server is hosting multiple application databases. The only
database with errors is the replicated database. If this was hardware
related we would expect to see the other databases having problems, as they
were using the same internal hardware and SAN array to hold their data as
the replicated database.
Any thoughts?|||Yes, There are both image and text columns in some of the tables
Below is excerpt from the maintenance log on the errors we are getting
[1] Database ServiceCenter: Check Data and Index Linkage..
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8929: [Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 525244926: Errors found in text ID 462078345216 owned by data record identified by RID = (1:202550:3)
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 525244926. The text, ntext, or image node at page (1:40468), slot 12, text ID 462078345216 is referenced by page (1:202550), slot 3, but was not seen in the scan
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 525244926. The text, ntext, or image node at page (1:695828), slot 12, text ID 462078345216 is not referenced
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Database 'ServiceCenter', index 'applicationm1.applicationm1_P' (ID 1589580701) (index ID 2). Extra or invalid key for the keys
[Microsoft][ODBC SQL Server Driver][SQL Server]Index row (1:81744:140) with values (application = 'cc.find.problem' and label = 'build.s) points to the data row identified by (RID = (1:348126:8))
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 3 consistency errors in table 'problemm1' (object ID 525244926)
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in table 'applicationm1' (object ID 1589580701)
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 4 consistency errors in database 'ServiceCenter'
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (ServiceCenter ).|||There are a lot of bugs/problems, replicating text/image columns. First make
sure all participating servers are on the latest service pack. Then see if
you find any hits for those error numbers at: http://support.microsoft.com
And consider contacting Microsoft support.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:07926FA4-64B3-4038-8934-E2A3A631DDE3@.microsoft.com...
Yes, There are both image and text columns in some of the tables.
Below is excerpt from the maintenance log on the errors we are getting:
[1] Database ServiceCenter: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8929: [Microsoft][ODBC SQL
Server Driver][SQL Server]Object ID 525244926: Errors found in text ID
462078345216 owned by data record identified by RID = (1:202550:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
525244926. The text, ntext, or image node at page (1:40468), slot 12, text
ID 462078345216 is referenced by page (1:202550), slot 3, but was not seen
in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
525244926. The text, ntext, or image node at page (1:695828), slot 12, text
ID 462078345216 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Database
'ServiceCenter', index 'applicationm1.applicationm1_P' (ID 1589580701)
(index ID 2). Extra or invalid key for the keys:
[Microsoft][ODBC SQL Server Driver][SQL Server]Index row (1:81744:140) with
values (application = 'cc.find.problem' and label = 'build.s) points to the
data row identified by (RID = (1:348126:8)).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 3 consistency errors in table 'problemm1' (object ID 525244926).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 1 consistency errors in table 'applicationm1' (object ID
1589580701).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 4 consistency errors in database 'ServiceCenter'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the
minimum repair level for the errors found by DBCC CHECKDB (ServiceCenter ).

Data corruption on a replicated database

We are running SQL 2000 EI SP3 on a Windows 2000 Adv. Server.
We have recently setup a database for replication using transactional replic
ation every ten minutes.
Shortly after implementing this, we started receiving integrity errors on th
e database on a daily basis.
Many of the errors are requiring checktable with repair data loss to correct
the problem. The corruption has shown up on both replicated and non-replic
ated tables, but only occurs if replication is running. As soon as replicat
ion was stopped, the databa
se quit reporting integrity errors.
Please let me know if you need further information.
Has anyone else ran into this?I don't think replication has anything to do with it. Probably replication
is stressing your hard disk subsystem, leading to errors. So the real
culprit could be hardware. Check your event viewer and any hardware logs for
clues.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:B6B0118A-C15D-41A5-9E11-FA01F4AD14BD@.microsoft.com...
We are running SQL 2000 EI SP3 on a Windows 2000 Adv. Server.
We have recently setup a database for replication using transactional
replication every ten minutes.
Shortly after implementing this, we started receiving integrity errors on
the database on a daily basis.
Many of the errors are requiring checktable with repair data loss to correct
the problem. The corruption has shown up on both replicated and
non-replicated tables, but only occurs if replication is running. As soon
as replication was stopped, the database quit reporting integrity errors.
Please let me know if you need further information.
Has anyone else ran into this?|||Our first thought was hardware as well, however, a few things are pointing t
o something else causing the problem:
1. All of the hardware is brand new, and the server is running at about a q
uarter of our target performance. (This server is not yet hosting databases
that will be added later this year... During peak time we are seeing a max u
tilization of 15% across
all processors). The hardware vendor has already been over this server and
hasn't found anything, and there is no hardware related errors in the server
event logs
2. This is the first enterprise class server we have run in a SAN environme
nt. To eliminate the SAN hardware or network we moved all of the data to in
ternal drives on the server. The problem continues to occur.
3. This server is hosting multiple application databases. The only databas
e with errors is the replicated database. If this was hardware related we w
ould expect to see the other databases having problems, as they were using t
he same internal hardware
and SAN array to hold their data as the replicated database.
Any thoughts?|||Okay, could you post the exact errors you are seeing in the SQL Server error
logs?
Are you replicating text or image columns?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:34BB1D32-6935-40A8-B061-1372260A868B@.microsoft.com...
Our first thought was hardware as well, however, a few things are pointing
to something else causing the problem:
1. All of the hardware is brand new, and the server is running at about a
quarter of our target performance. (This server is not yet hosting databases
that will be added later this year... During peak time we are seeing a max
utilization of 15% across all processors). The hardware vendor has already
been over this server and hasn't found anything, and there is no hardware
related errors in the server event logs
2. This is the first enterprise class server we have run in a SAN
environment. To eliminate the SAN hardware or network we moved all of the
data to internal drives on the server. The problem continues to occur.
3. This server is hosting multiple application databases. The only
database with errors is the replicated database. If this was hardware
related we would expect to see the other databases having problems, as they
were using the same internal hardware and SAN array to hold their data as
the replicated database.
Any thoughts?|||Yes, There are both image and text columns in some of the tables.
Below is excerpt from the maintenance log on the errors we are getting:
[1] Database ServiceCenter: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8929: [Microsoft][ODBC SQL Server Driv
er][SQL Server]Object ID 525244926: Errors found in text ID 462078345216 own
ed by data record identified by RID = (1:202550:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 525244926. The te
xt, ntext, or image node at page (1:40468), slot 12, text ID 462078345216 is
referenced by page (1:202550), slot 3, but was not seen in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 525244926. The te
xt, ntext, or image node at page (1:695828), slot 12, text ID 462078345216 i
s not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Database 'ServiceCenter', i
ndex 'applicationm1.applicationm1_P' (ID 1589580701) (index ID 2). Extra or
invalid key for the keys:
[Microsoft][ODBC SQL Server Driver][SQL Server]Index row (1:81744:140) with values (app
lication = 'cc.find.problem' and label = 'build.s) points to the data row id
entified by (RID = (1:348126:8)).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 3
consistency errors in table 'problemm1' (object ID 525244926).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1
consistency errors in table 'applicationm1' (object ID 1589580701).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 4
consistency errors in database 'ServiceCenter'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum re
pair level for the errors found by DBCC CHECKDB (ServiceCenter ).|||There are a lot of bugs/problems, replicating text/image columns. First make
sure all participating servers are on the latest service pack. Then see if
you find any hits for those error numbers at: http://support.microsoft.com
And consider contacting Microsoft support.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:07926FA4-64B3-4038-8934-E2A3A631DDE3@.microsoft.com...
Yes, There are both image and text columns in some of the tables.
Below is excerpt from the maintenance log on the errors we are getting:
[1] Database ServiceCenter: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8929: [Microsoft][ODBC SQL
Server Driver][SQL Server]Object ID 525244926: Errors found in text ID
462078345216 owned by data record identified by RID = (1:202550:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
525244926. The text, ntext, or image node at page (1:40468), slot 12, text
ID 462078345216 is referenced by page (1:202550), slot 3, but was not seen
in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
525244926. The text, ntext, or image node at page (1:695828), slot 12, text
ID 462078345216 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Database
'ServiceCenter', index 'applicationm1.applicationm1_P' (ID 1589580701)
(index ID 2). Extra or invalid key for the keys:
[Microsoft][ODBC SQL Server Driver][SQL Server]Index row (1:81744:140) with
values (application = 'cc.find.problem' and label = 'build.s) points to the
data row identified by (RID = (1:348126:8)).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 3 consistency errors in table 'problemm1' (object ID 525244926).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 1 consistency errors in table 'applicationm1' (object ID
1589580701).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 4 consistency errors in database 'ServiceCenter'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the
minimum repair level for the errors found by DBCC CHECKDB (ServiceCenter ).

Data Copy

Hi,
Currently, we are running CRM application for 125 users on SQL 2000/Windows
NT. Due to some unforseen reasons, we could not perform
capacity planning before rolling the application to the connected and remote
users. As result, our current database server is running on low memory
and most important is shortage of disk space on the C drive. We decided to
replace the existing server with another robust server, taking into
current and future requirement. With regards to server replacement, couple o
f questions and require your technical expertise.
a) The current CRM users are connected to existing server using configuratio
n file. This configuration file is stored on the users client machine
and and we like to ensure the same configuration file is used by the users.
Due to this constraint, the naming convention of new machine name
will be exactly the same name as used in our current database server machine
. We like to ensure, when the existing box is replaced with new server
the users will be connected to new server.
b) Is it possible to copy the entire SQL data lying on the existing server t
o another server. Following set of data to be copied. Can you
please let us know, which tool to be used for copying the following set of d
ata.
The data to be copied to the new server will be (Master, MSDB, Pubs, CRM app
lication data i.e (Tables, Users, SP, etc)).
DTS Packages from existing to newly built server
All the security Logins
Can you please let us know, are there any other steps which is missed out fo
r copying data from server to server. We will be testing this
process on test box before rolling out to production users.
Need your technical advise on how to proceed.
Thanks
ThomasI've used the following approach: http://www.support.microsoft.com/?i..._sql_server.htm .

Data Copy

Hi,
Currently, we are running CRM application for 125 users on SQL 2000/Windows NT. Due to some unforseen reasons, we could not perform
capacity planning before rolling the application to the connected and remote users. As result, our current database server is running on low memory
and most important is shortage of disk space on the C drive. We decided to replace the existing server with another robust server, taking into
current and future requirement. With regards to server replacement, couple of questions and require your technical expertise.
a) The current CRM users are connected to existing server using configuration file. This configuration file is stored on the users client machine
and and we like to ensure the same configuration file is used by the users. Due to this constraint, the naming convention of new machine name
will be exactly the same name as used in our current database server machine. We like to ensure, when the existing box is replaced with new server
the users will be connected to new server.
b) Is it possible to copy the entire SQL data lying on the existing server to another server. Following set of data to be copied. Can you
please let us know, which tool to be used for copying the following set of data.
The data to be copied to the new server will be (Master, MSDB, Pubs, CRM application data i.e (Tables, Users, SP, etc)).
DTS Packages from existing to newly built server
All the security Logins
Can you please let us know, are there any other steps which is missed out for copying data from server to server. We will be testing this
process on test box before rolling out to production users.
Need your technical advise on how to proceed.
Thanks
Thomas
I've used the following approach: http://www.support.microsoft.com/?id=314546 . Here the master and msdb databases are not copied to the new server. Instead you transfer the logins, jobs, packages etc as outlined here. If you plan on using the old serv
er's name, you need to run sp_dropserver and sp_addserver (after renaming the server) to change @.@.servername ie:
1. sp_dropserver 'currentservername'
2. sp_addserver 'desiredservername' , 'local'
3. restart the service and check @.@.servername
I've seen description sof the approach you're asking about but have never tried it myself - it assumes that your drive set up for example is the same. Here is a link that describes this http://vyaskn.tripod.com/moving_sql_server.htm .

Data Conversion failed due to Potential Loss of data

Hi,

I am getting this error when my ssis package is running

Data Conversion failed due to Potential Loss of data

the input column is in string format and output is in sql server bigint

the error is occuring when there is an empty string in the input. what should i do to overcome this

It is an ID field and should i convert to bigint or should i leave it as char datatype is it i a good solution or is there a way to over come this.

Add a derived column to either change the empty string to NULL or a zero. Up to you, but you can't insert a string into an integer field.|||

I am not sure why a string is being passed into a BigInt but I would not leave an input field null. I would use the Conditional operator ? : to provide the empty string a value of 0 if it is empty using the following in an expression:

ISNULL(<<input field>>) ? 0 : <<input field>>

In other words the above states that if the incoming field is NULL then fill it with a 0 otherwise pass the incoming value.

|||

desibull wrote:

I am not sure why a string is being passed into a BigInt but I would not leave an input field null. I would use the Conditional operator ? : to provide the empty string a value of 0 if it is empty using the following in an expression:

ISNULL(<<input field>>) ? 0 : <<input field>>

In other words the above states that if the incoming field is NULL then fill it with a 0 otherwise pass the incoming value.

NULL and "empty string" are two very different things.

To expand on what I suggested earlier and desibull's code above:

ISNULL([InputColumn]) || [InputColumn] == "" ? 0 : [InputColumn]

OR

ISNULL([InputColumn]) || [InputColumn] == "" ? NULL(DT_I8) : [InputColumn]

Data conversion error

I'm importing data from a text table, into a temp table, and then on to a final working table. I'm running into difficulty converting data from text into a 'datetime' format. I'm getting the following error:

"Arithmetic overflow error" when trying to convert into a column with the data type "DateTime"

I half expected it to reject all conversions into a Date format because of the source file being in text format, but it allows this conversion in other columns.

If I switch the Data type to 'nvarchar' instead of 'datetime' it converts and pops up with a date format.

My question is: Will this nvarchar that looks like a date behave like a date? For example, if someone tries to perform a calculation with a date format, will the nvarchar suffice, or would it cause problems?

Any ideas?It won't convert what's causing this error. You need to do something like this:

SELECT CASE WHEN ISDATE(text_field) = 0 THEN '01/01/01' ELSE CAST(text_field AS DATETIME) END
FROM table

Data Conversion

Hello everyone,

I am running SQL-2000, I have a table that one field ddefined as char. The data is actually Dollar values(no $ signs just 99.25 for example). I need to convert this column from char to Numeric. I am trying to use Enterprise manager to redesign the table but I get "error converting data type VARCHAR to numeric". Enterprise manager shows the field as CHAR. I have no Idea why that error is comming up. I would like any info that could help me with this conversion. Thanks in advance.

EvThe isnum function can be used to find values in your data which cannot be converted to numbers:

select *
from [YOURTABLE]
where isnum([VALUEFIELD]) = 0|||Hi you can use the following query
select value=convert(numeric,ddefined) from table

Madhivanan|||I ran the ISNUM function and all rows are good. However I get the same error.|||I might be off here, but perhaps there's a value in there that's a valid numeric but can't be converted from varchar to numeric (see: http://www.dbforums.com/t998353.html) ? Anyway, I ran into the same thing a while back; http://www.dbforums.com/t1023776.html got it solved.|||You are right. I am not sure why I can't convert to Numeric but I am able to convert to MONEY. Thats works fine for the application. Thanks for your help.

Ev

data base size, help!

The size of an sql server database has is 1gig and the log file is 1 gig.
The server I'm on is running out of space and was wondering if you have ever
run into this problem and what I should do about it?
How can I move previous year records off the database?
Should I make a backup and then delete them from the current database?
Do I need the log file?
How do I reduce the size?
> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
> ever
> run into this problem and what I should do about it?
Buy a bigger disk? Why does your server have a 2GB volume?

> How can I move previous year records off the database?
You can use dozens of methods, including DTS them to another database and
then delete.

> Do I need the log file?
Yes, you need the log file. It's not there because it's pretty.

> How do I reduce the size?
http://www.aspfaq.com/2471
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||LU
http://support.microsoft.com/default...650-- how
to shrink tr log
http://support.microsoft.com/default...72318-- the
same shrink on sql2000
"LU" <LU@.discussions.microsoft.com> wrote in message
news:9765408F-F850-4531-8257-9F1A19867049@.microsoft.com...
> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
ever
> run into this problem and what I should do about it?
> How can I move previous year records off the database?
> Should I make a backup and then delete them from the current database?
> Do I need the log file?
> How do I reduce the size?

data base size, help!

The size of an sql server database has is 1gig and the log file is 1 gig.
The server I'm on is running out of space and was wondering if you have ever
run into this problem and what I should do about it?
How can I move previous year records off the database?
Should I make a backup and then delete them from the current database?
Do I need the log file?
How do I reduce the size?> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
> ever
> run into this problem and what I should do about it?
Buy a bigger disk? Why does your server have a 2GB volume?
> How can I move previous year records off the database?
You can use dozens of methods, including DTS them to another database and
then delete.
> Do I need the log file?
Yes, you need the log file. It's not there because it's pretty.
> How do I reduce the size?
http://www.aspfaq.com/2471
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||LU
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q256650-- how
to shrink tr log
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q272318-- the
same shrink on sql2000
"LU" <LU@.discussions.microsoft.com> wrote in message
news:9765408F-F850-4531-8257-9F1A19867049@.microsoft.com...
> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
ever
> run into this problem and what I should do about it?
> How can I move previous year records off the database?
> Should I make a backup and then delete them from the current database?
> Do I need the log file?
> How do I reduce the size?

data base size, help!

The size of an sql server database has is 1gig and the log file is 1 gig.
The server I'm on is running out of space and was wondering if you have ever
run into this problem and what I should do about it?
How can I move previous year records off the database?
Should I make a backup and then delete them from the current database?
Do I need the log file?
How do I reduce the size?> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
> ever
> run into this problem and what I should do about it?
Buy a bigger disk? Why does your server have a 2GB volume?

> How can I move previous year records off the database?
You can use dozens of methods, including DTS them to another database and
then delete.

> Do I need the log file?
Yes, you need the log file. It's not there because it's pretty.

> How do I reduce the size?
http://www.aspfaq.com/2471
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||LU
http://support.microsoft.com/defaul...6650-- h
ow
to shrink tr log
http://support.microsoft.com/defaul...272318-- the
same shrink on sql2000
"LU" <LU@.discussions.microsoft.com> wrote in message
news:9765408F-F850-4531-8257-9F1A19867049@.microsoft.com...
> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
ever
> run into this problem and what I should do about it?
> How can I move previous year records off the database?
> Should I make a backup and then delete them from the current database?
> Do I need the log file?
> How do I reduce the size?

data base access

Hi,
I have problem accessing my sqlDatabase via asp.net
When running a test application I get the following error message:

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'P900\ASPNET'.

I know the connection to the db is ok since I can gain access from a simple console application.

Anybody knows what's wrong and how to fix it??
Many thanks,

Cat33Is your ASPNET user on machine P900 a user in the SQL Server you are trying to connect to?

If you are using integrated security, a console mode applicaiton might work because integrated security while you are running a console mode application uses YOUR security context. Running an ASP.NET application, you are using the ASP.NET users' security context (ASPNET by default).

You need to either add the ASPNET user to SQL Server or use a username and password.

Look at the different possible connection strings here:

www.connectionstrings.com|||Ok, thanks for quick response. I have one simple question however;
How do I add the ASPNET user to the SQL Server?
Thanks in advance,
Cat33|||Do you have Enterprise Manager? If so, Expand out the Security folder, and right click on Logins and add a new login.

If not, there is a command line tool called OSQL you can use. Find it and add the folder to the path, or from the folder where OSQL is, run the following:

osql -S servername\instancename -E -q
 --Line numbers will appear
EXEC sp_grantlogin 'COMPUTERNAME\ASPNET'
go
use <databasename>
go
EXEC sp_grantdbaccess 'COMPUTERNAME\ASPNET'
go
EXEC sp_addrolemember 'db_owner', 'COMPUTERNAME\ASPNET'
go

|||Hi,
Sorry to have to plague you with this access question, but after having done what you said, using the osql-tool, nothing improved. I still get the same Error Message, wheré the line 299 is hightlighted. I'm running Framework 1.1 with IIS 5.1 on a XP Pro machine. I have earlier been able to access the database so I simply don't know what to do. I have seen to it that the ASPNET account has been added to the directories in question. What do I do now? Do you have any idea as to where the source to the error is?
Thanks again,
Catharina

Error Message:

Login failed for user 'P900\ASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'P900\ASPNET'.

Source Error:

Line 294:
Line 295:cmd = new SqlCommand(sql, con);
Line 296:con.Open();
Line 297:
Line 298:bool doredirect = true;

Source File: c:\inetpub\wwwroot\webapperikspage\start\newuser.aspx.cs Line: 296

Stack Trace:

[SqlException: Login failed for user 'P900\ASPNET'.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction)
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
System.Data.SqlClient.SqlConnection.Open()
WebAppEriksPage.start.NewUser.InsertUser() in c:\inetpub\wwwroot\webapperikspage\start\newuser.aspx.cs:296
WebAppEriksPage.start.NewUser.btnAccept_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\webapperikspage\start\newuser.aspx.cs:147
System.Web.UI.WebControls.Button.OnClick(EventArgs e)
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()|||When you ran the script in the previous message, you did replace COMPUTERNAME with your computer's name (P900), correct? Were there any error messages when you ran those lines through OSQL?|||Hi,
this is what I wrote:
osql - S P900\NetSDK -E -q
1> EXEC sp_grantlogin 'P900\ASPNET'
2> go

1> use Northwind
2> go

1> EXEC sp_grantdbaccess 'P900\ASPNET'
2> go

1> EXEC sp_addrolemember 'db_owner', 'P900\ASPNET'
2>go

1>exit
and I got no error messages

/Catharina|||OK. Also, replace Northwind with the name of the database you want to use. Northwind is a sample database used for that script.|||Well, at this stage the Northwind database is the database I'm using since this is a test trial to see that things work, which they don't, and I can access that database via a console application and also a Windows application.
Any more leads?|||If you added the user to the database, and there were no errors, then I cannot think of anything else.

Can you show your exact connection string (it should not have any password in it, so you should be able to just cut and paste it). If you did have access to Enterprise Manager, I would suggest looking there to verify that the user is really there.|||

con = new SqlConnection("server= p900\\NetSDK; Trusted_Connection=yes; database= Northwind");

I don't have access to Enterprise Manager, can I verify via osql?
Cheers,
Catharina|||When you start OSQL, rather than

osql -S servername\instancename -E -q

Should be, in your case:

osql -S p900\NetSDK -E -q

I have always used Trusted_Connection=true rather than =yes, so I would try that.

Data and log files and Raid

I have a development SQL 2000 server system running on Windows 2000 Pro.
I keep this system updated with all production data so I can test
everything.
This question might not be answerable without knowing what kind of data
I have, but I'll ask anyway.
I was thinking about adding a couple of SATA disks to the system. Here
are some possibilities:
1) Leave the log file on its current IDE disk, and set the new SATA
disks up as Raid 0, and put the data file there,
2) Move the log to one of the new SATA disks and the data to the other
SATA disk,
3) Set up the new SATA disks as Raid 0 and put the log and the data both
on the new RAID logical drive that results;
4) Some other arrangement?
It's a 45 GB database that's mostly used for reading (Select queries)
except when I update the database with new transactions and customer
account records daily.
One question...When writing data to a database, is the log file as
active as the data file? Isn't everything written to the log file and
then to the data file?
Thanks for any insight with this very general question.
David WalkerDWalker wrote:
> I have a development SQL 2000 server system running on Windows 2000 Pro.
> I keep this system updated with all production data so I can test
> everything.
> This question might not be answerable without knowing what kind of data
> I have, but I'll ask anyway.
> I was thinking about adding a couple of SATA disks to the system. Here
> are some possibilities:
> 1) Leave the log file on its current IDE disk, and set the new SATA
> disks up as Raid 0, and put the data file there,
> 2) Move the log to one of the new SATA disks and the data to the other
> SATA disk,
> 3) Set up the new SATA disks as Raid 0 and put the log and the data both
> on the new RAID logical drive that results;
> 4) Some other arrangement?
> It's a 45 GB database that's mostly used for reading (Select queries)
> except when I update the database with new transactions and customer
> account records daily.
> One question...When writing data to a database, is the log file as
> active as the data file? Isn't everything written to the log file and
> then to the data file?
> Thanks for any insight with this very general question.
> David Walker
>
Hi David,
All changes are written to the Log first and then to the Data files when
a Checkpoint occours. You could use (3) and leave the System tables on
the IDE disks. This would ensure speed and minimal security (if you
lose the RAID then the system tables are safe on the IDE, if you lose
the IDE then you have most likely lost the OS as well;)
James|||James Wilson <JamesWilson@.bryggemail.dk> wrote in
news:umGxjbwLGHA.3496@.TK2MSFTNGP14.phx.gbl:

> Hi David,
> All changes are written to the Log first and then to the Data files
> when a Checkpoint occours. You could use (3) and leave the System
> tables on the IDE disks. This would ensure speed and minimal security
> (if you lose the RAID then the system tables are safe on the IDE, if
> you lose the IDE then you have most likely lost the OS as well;)
> James
>
OK, thanks for the advice.
David

Data and log files and Raid

I have a development SQL 2000 server system running on Windows 2000 Pro.
I keep this system updated with all production data so I can test
everything.
This question might not be answerable without knowing what kind of data
I have, but I'll ask anyway.
I was thinking about adding a couple of SATA disks to the system. Here
are some possibilities:
1) Leave the log file on its current IDE disk, and set the new SATA
disks up as Raid 0, and put the data file there,
2) Move the log to one of the new SATA disks and the data to the other
SATA disk,
3) Set up the new SATA disks as Raid 0 and put the log and the data both
on the new RAID logical drive that results;
4) Some other arrangement?
It's a 45 GB database that's mostly used for reading (Select queries)
except when I update the database with new transactions and customer
account records daily.
One question...When writing data to a database, is the log file as
active as the data file? Isn't everything written to the log file and
then to the data file?
Thanks for any insight with this very general question.
David Walker
DWalker wrote:
> I have a development SQL 2000 server system running on Windows 2000 Pro.
> I keep this system updated with all production data so I can test
> everything.
> This question might not be answerable without knowing what kind of data
> I have, but I'll ask anyway.
> I was thinking about adding a couple of SATA disks to the system. Here
> are some possibilities:
> 1) Leave the log file on its current IDE disk, and set the new SATA
> disks up as Raid 0, and put the data file there,
> 2) Move the log to one of the new SATA disks and the data to the other
> SATA disk,
> 3) Set up the new SATA disks as Raid 0 and put the log and the data both
> on the new RAID logical drive that results;
> 4) Some other arrangement?
> It's a 45 GB database that's mostly used for reading (Select queries)
> except when I update the database with new transactions and customer
> account records daily.
> One question...When writing data to a database, is the log file as
> active as the data file? Isn't everything written to the log file and
> then to the data file?
> Thanks for any insight with this very general question.
> David Walker
>
Hi David,
All changes are written to the Log first and then to the Data files when
a Checkpoint occours. You could use (3) and leave the System tables on
the IDE disks. This would ensure speed and minimal security (if you
lose the RAID then the system tables are safe on the IDE, if you lose
the IDE then you have most likely lost the OS as well;)
James
|||James Wilson <JamesWilson@.bryggemail.dk> wrote in
news:umGxjbwLGHA.3496@.TK2MSFTNGP14.phx.gbl:

> Hi David,
> All changes are written to the Log first and then to the Data files
> when a Checkpoint occours. You could use (3) and leave the System
> tables on the IDE disks. This would ensure speed and minimal security
> (if you lose the RAID then the system tables are safe on the IDE, if
> you lose the IDE then you have most likely lost the OS as well;)
> James
>
OK, thanks for the advice.
David