can't configure mirroring

Hi,
I've already configured a few databases from our server A to use mirroring
to our DR server. Now I've got a different primary server B and I am trying
to configure it so it mirrors to the same DR server. But I can't.
Is it supposed to work on the same port number as the first mirroring
connection between A & DR? or B & DR has to use a different port number? I've
also tried using a different port number (first check with netstat that it's
not used), but the Wizard doesn't allow me to enter a different port number
it still defaults to the same as between A & DR.
The error says that: The server network address "TCP://DR-server.local:5022"
can not be reached or does not exist. Check the network address name and that
the ports for the local and remote endpoints are operational. (Microsoft SQL
Server, Error: 1418)
Any ideas? Thanks.
Panos.
I have the same problem. Three new servers setting up a mirror from a to b
with c as the witness. I have verified that I can connect to each from each
by going to a DOS prompt on each and executing "osql -Usa
-SserverDNSname,5022" It returns with an osql command prompt. I am using SQL
server 2005 Standard with SP2 on windows server 2003 sp1.
While going through the mirror setup wizard I get the error:
An error occurred while starting mirroring.
Additional information:
Alter fail for database 'test'. (Microsoft.sqlserver.smo)
An exception occurred while executing a transact-sql statement or batch.
(Micorosft.sqlserver.connectioninfo)
The server networkaddress "TCP://address.given.here.com:5022" can not be
reached or does not exists. Check the network address name and that the ports
for the local and remote endpoints are operational. (Microsoft sql server,
Error: 1418)
I did verify that the dsn names resolve. I used both integrated NT accounts
for authentication and the SQL Server sa account for setting up security
between all the servers and the same message results.
I used a port scanner and netstat to verify that 5022 is live.
It gets this message when it attempts to connect to the local host where
mirroring is being initiated from. There is no opportunity to put in
authentication information for the local host when setting up mirroring. I am
using an account that is in the administrators group on all three servers.
These servers are not part of a domain.
Seems to me there is a fundamental flaw with mirror setup.
Suggestions for further diagnosing this problem would be helpful.
|||Well mine is slightly different as the DR server (B) I am trying to make the
target has already been set-up successfully for mirroring with another server
(A). I am now trying to setup mirroring from Server C to server B but I get
the error message. Are you sure you can use mirroring with Developer edition?
I thought you need Enterprise but maybe that was before SP2. Check this
anyway.
Panos.
"DBA449" wrote:

> I have the same problem. Three new servers setting up a mirror from a to b
> with c as the witness. I have verified that I can connect to each from each
> by going to a DOS prompt on each and executing "osql -Usa
> -SserverDNSname,5022" It returns with an osql command prompt. I am using SQL
> server 2005 Standard with SP2 on windows server 2003 sp1.
> While going through the mirror setup wizard I get the error:
> An error occurred while starting mirroring.
> Additional information:
> Alter fail for database 'test'. (Microsoft.sqlserver.smo)
> An exception occurred while executing a transact-sql statement or batch.
> (Micorosft.sqlserver.connectioninfo)
> The server networkaddress "TCP://address.given.here.com:5022" can not be
> reached or does not exists. Check the network address name and that the ports
> for the local and remote endpoints are operational. (Microsoft sql server,
> Error: 1418)
> I did verify that the dsn names resolve. I used both integrated NT accounts
> for authentication and the SQL Server sa account for setting up security
> between all the servers and the same message results.
> I used a port scanner and netstat to verify that 5022 is live.
> It gets this message when it attempts to connect to the local host where
> mirroring is being initiated from. There is no opportunity to put in
> authentication information for the local host when setting up mirroring. I am
> using an account that is in the administrators group on all three servers.
> These servers are not part of a domain.
> Seems to me there is a fundamental flaw with mirror setup.
> Suggestions for further diagnosing this problem would be helpful.
|||Mirroring is now supported for SQL Server Standard edition 2005 with SP2
installed. I saw this on the Microsoft web site.
"Panos Stavroulis." wrote:
[vbcol=seagreen]
> Well mine is slightly different as the DR server (B) I am trying to make the
> target has already been set-up successfully for mirroring with another server
> (A). I am now trying to setup mirroring from Server C to server B but I get
> the error message. Are you sure you can use mirroring with Developer edition?
> I thought you need Enterprise but maybe that was before SP2. Check this
> anyway.
> Panos.
> "DBA449" wrote:
|||Microsoft?
I think we need a little help here?
What da ya sa...
"Panos Stavroulis." wrote:

> Hi,
> I've already configured a few databases from our server A to use mirroring
> to our DR server. Now I've got a different primary server B and I am trying
> to configure it so it mirrors to the same DR server. But I can't.
> Is it supposed to work on the same port number as the first mirroring
> connection between A & DR? or B & DR has to use a different port number? I've
> also tried using a different port number (first check with netstat that it's
> not used), but the Wizard doesn't allow me to enter a different port number
> it still defaults to the same as between A & DR.
>
> The error says that: The server network address "TCP://DR-server.local:5022"
> can not be reached or does not exist. Check the network address name and that
> the ports for the local and remote endpoints are operational. (Microsoft SQL
> Server, Error: 1418)
> Any ideas? Thanks.
> Panos.
|||Solution found.
In my case the problem was that I had used the SQL Server Configuration
Manager to configure SQL Server to listen on port 5022. The documentation had
led me to believe that this had to be confgured manually when I couldn't
originally get it to work.
Apparently SQLServer will automatically set up that port to listen on when
mirroring is configured. If you set it up manually, it conflicts with the
automatic configuration.
I opened a case with Microsoft to resolve this issue, and they led me down a
path to configure SQLServer to run under a domain account. Previously I had
it configured to run as a local account in the administrators group. This
path led nowhere, the same error always resulted. Currently I have SQL server
to run under a local account that is part of the administrators group on all
three servers, principle, mirror and witness. And it seems to be functioning
fine.
Further, I was led to believe that you had to log in to the server using the
same account that SQL Server was running under in order to properly configure
SQLServer. This is not true either. I use one account to log into the server
for administrative purposes, and SQLServer runs under a different account.
"DBA449" wrote:
[vbcol=seagreen]
> Mirroring is now supported for SQL Server Standard edition 2005 with SP2
> installed. I saw this on the Microsoft web site.
> "Panos Stavroulis." wrote:
|||I had to revisit this!! The issue is resolved by creating a certificate on
the server, then linking it with the end point. Transferring the file to the
target, load and link with the other end point.
Panos.
"DBA449" wrote:
[vbcol=seagreen]
> Microsoft?
> I think we need a little help here?
> What da ya sa...
> "Panos Stavroulis." wrote:
|||that is what microsoft told me to do. But as indicated in the previous post,
it was not necessary
"Panos Stavroulis." wrote:
[vbcol=seagreen]
> I had to revisit this!! The issue is resolved by creating a certificate on
> the server, then linking it with the end point. Transferring the file to the
> target, load and link with the other end point.
> Panos.
> "DBA449" wrote:
sql

Cant Cascade Delete

I have a Sql Server 2005 table with 3 fields, an ID field (primary key), a parent ID field, and Name. The parent ID references the ID field (foreign to primary - many to one) within the same table so that records can reference their parent. I would like to place a cascade delete on the ID field so that when the primary ID is removed it will automatically remove all those records with a parent ID that match. Sql server does not allow me to establish this cascade delete.

I was considering a trigger instead but only know how tio use the AFTER paramter and not an alternative.

Thanks

Hello my friend,

I see that you have a tree-like table. You have records that have a parent record, that can have a parent record that can have a parent record in the same table, and so on. The function at the bottom will help. You will need to change the table name from tblTree and the field names ParentID and PageID to whatever you have called them.

Anyway, the function will return a list of all child records. For example, if PageID 2 had childs 5 and 8, and 5 had 3 childs 67, 68, and 70, the resultset would look like the following: -

2
5
67
68
70
8

All you need to do is run a delete against this returned set as follows, which deletes number 2 and all of its children: -

DELETE FROM tblTree WHERE PageID IN (SELECT PageID FROM dbo.fnGetPages(2))

The function is as follows: -

CREATE FUNCTION dbo.fnGetPages
(
@.PageID AS INT
)

RETURNS @.ChildPageIDs TABLE(PageID INT)

AS

BEGIN
INSERT INTO @.ChildPageIDs (PageID)
SELECT PageID FROM tblTree WHERE ParentID = @.PageID

DECLARE @.TempChildPageIDs TABLE(PageID INT)
INSERT INTO @.TempChildPageIDs (PageID)
SELECT PageID FROM @.ChildPageIDs ORDER BY PageID

DECLARE @.ChildPageID AS INT
SET @.ChildPageID = (SELECT TOP 1 PageID FROM @.TempChildPageIDs)

WHILE (@.ChildPageID IS NOT NULL)
BEGIN
INSERT INTO @.ChildPageIDs (PageID)
SELECT PageID FROM dbo.fnGetPages(@.ChildPageID)
DELETE FROM @.TempChildPageIDs WHERE PageID = @.ChildPageID

SET @.ChildPageID = (SELECT TOP 1 PageID FROM @.TempChildPageIDs)
END
RETURN
END

If you have any questions on this, please let me know.

Kind regards

Scotty

|||Excellent stuff. Thank you!

can't backup database

I have SQL2000 running on a member 2003server.
Have setup a maintenance plan but our primary database as well as the Main
database are not getting backed up among other things.
Looking at the history log I see many errors:
Check data and index linkage errors out with the message "Repair statement
not processed. Database needs to be in single user mode"
Backup transaction log "Backup can not be performed on this database.
This sub task is ignored"
I don't even have error messages for the backups that aren't happening for
these 2.
All the other databases, Northwind, model, msdb, and pubs are doing fine.
What am I missing here?> Check data and index linkage errors out with the message "Repair statement
> not processed. Database needs to be in single user mode"
Remove the "attempt to repair minor problems" from the integrity check part of the maint plan.
> Backup transaction log "Backup can not be performed on this database.
> This sub task is ignored"
Don't attempt to backup of the log of databases in simple recovery model.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rockitman" <Rockitman@.discussions.microsoft.com> wrote in message
news:B631B1DA-9D97-4FB6-886C-BD26ABE249CF@.microsoft.com...
>I have SQL2000 running on a member 2003server.
> Have setup a maintenance plan but our primary database as well as the Main
> database are not getting backed up among other things.
> Looking at the history log I see many errors:
> Check data and index linkage errors out with the message "Repair statement
> not processed. Database needs to be in single user mode"
> Backup transaction log "Backup can not be performed on this database.
> This sub task is ignored"
> I don't even have error messages for the backups that aren't happening for
> these 2.
> All the other databases, Northwind, model, msdb, and pubs are doing fine.
> What am I missing here?|||Why do I need to remove the "attempt to repair minor problems"? Why is this
option available if it only screws up the whole maintenance plan?
Why does it say that database needs to be in single user mode'
How does one make a database into single user mode'
And your last statement is ? to me as well: "Don't attempt to backup the
log of the databases in simple recovery model." What the heck does that
mean'
You didn't address why my databases are not even getting backed up nor why
there is no error message as to why they're not getting backed up.
Please forgive me, I am not an SQL guru, only know enough to keep this
shit running, but I've had no problems in running all these things in prior
maintenance plans in the past.
Please be a bit more thourough in your explanations. Thanks man.
"Tibor Karaszi" wrote:
> > Check data and index linkage errors out with the message "Repair statement
> > not processed. Database needs to be in single user mode"
> Remove the "attempt to repair minor problems" from the integrity check part of the maint plan.
>
> > Backup transaction log "Backup can not be performed on this database.
> > This sub task is ignored"
> Don't attempt to backup of the log of databases in simple recovery model.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rockitman" <Rockitman@.discussions.microsoft.com> wrote in message
> news:B631B1DA-9D97-4FB6-886C-BD26ABE249CF@.microsoft.com...
> >I have SQL2000 running on a member 2003server.
> >
> > Have setup a maintenance plan but our primary database as well as the Main
> > database are not getting backed up among other things.
> >
> > Looking at the history log I see many errors:
> >
> > Check data and index linkage errors out with the message "Repair statement
> > not processed. Database needs to be in single user mode"
> >
> > Backup transaction log "Backup can not be performed on this database.
> > This sub task is ignored"
> >
> > I don't even have error messages for the backups that aren't happening for
> > these 2.
> >
> > All the other databases, Northwind, model, msdb, and pubs are doing fine.
> >
> > What am I missing here?
>|||> Why do I need to remove the "attempt to repair minor problems"? Why is
> this
> option available if it only screws up the whole maintenance plan?
The same reason some laptops, and the spark plugs on '78 datsun b210s, were
prone to catching fire. Sadly, not every scenario is completely tested. At
least in this case the outcome is not potential death, but merely a
suggestion to uncheck a checkbox.
> Why does it say that database needs to be in single user mode'
Because for some repair options that's just what is required. You wouldn't
expect to talk on your cell phone while the tech is trying to fix it?
> How does one make a database into single user mode'
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
> And your last statement is ? to me as well: "Don't attempt to backup
> the
> log of the databases in simple recovery model." What the heck does that
> mean'
That means if your database is in simple recovery mode, don't back up the
log. It's pretty pointless since the log is useless in this case.
> You didn't address why my databases are not even getting backed up nor
> why
> there is no error message as to why they're not getting backed up.
We don't know the answer to the question. We can merely suggest ways you
can avoid the errors. I think Tibor's suggestions are sound, and you should
try them instead of complaining...
A

Cannot Update Identity Column while doing Select statement

Hi All,
i do have stored procedure
in which there select id,name from tablename
id i primary key and Identity seed is there for this column
When i directly query this statement it " select id,name from
tablename" , it shows the results
But when i call the same thing via stored procedure , it says Cannot
update identity colum
exec usp_getvalues , it gives the error
Thanks in Advance
Thomson
Hi
You would not expect this error from the query you have given therefore I
suspect that your stored procedure is doing something different. Post the
code for the store stored procedure and related tables see
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL in the news
group.
At a guess there is an insert/update statement and you are either including
the identity column or don't have SET IDENTITY_INSERT ON for the table.
John
"thomson" wrote:

> Hi All,
> i do have stored procedure
> in which there select id,name from tablename
> id i primary key and Identity seed is there for this column
>
> When i directly query this statement it " select id,name from
> tablename" , it shows the results
> But when i call the same thing via stored procedure , it says Cannot
> update identity colum
> exec usp_getvalues , it gives the error
> Thanks in Advance
> Thomson
>
|||identity columns are not updateable.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1159770767.122534.31190@.h48g2000cwc.googlegro ups.com...
> Hi All,
> i do have stored procedure
> in which there select id,name from tablename
> id i primary key and Identity seed is there for this column
>
> When i directly query this statement it " select id,name from
> tablename" , it shows the results
> But when i call the same thing via stored procedure , it says Cannot
> update identity colum
> exec usp_getvalues , it gives the error
> Thanks in Advance
> Thomson
>

Cannot Update Identity Column while doing Select statement

Hi All,
i do have stored procedure
in which there select id,name from tablename
id i primary key and Identity seed is there for this column
When i directly query this statement it " select id,name from
tablename" , it shows the results
But when i call the same thing via stored procedure , it says Cannot
update identity colum
exec usp_getvalues , it gives the error
Thanks in Advance
ThomsonHi
You would not expect this error from the query you have given therefore I
suspect that your stored procedure is doing something different. Post the
code for the store stored procedure and related tables see
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL in the news
group.
At a guess there is an insert/update statement and you are either including
the identity column or don't have SET IDENTITY_INSERT ON for the table.
John
"thomson" wrote:
> Hi All,
> i do have stored procedure
> in which there select id,name from tablename
> id i primary key and Identity seed is there for this column
>
> When i directly query this statement it " select id,name from
> tablename" , it shows the results
> But when i call the same thing via stored procedure , it says Cannot
> update identity colum
> exec usp_getvalues , it gives the error
> Thanks in Advance
> Thomson
>|||identity columns are not updateable.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1159770767.122534.31190@.h48g2000cwc.googlegroups.com...
> Hi All,
> i do have stored procedure
> in which there select id,name from tablename
> id i primary key and Identity seed is there for this column
>
> When i directly query this statement it " select id,name from
> tablename" , it shows the results
> But when i call the same thing via stored procedure , it says Cannot
> update identity colum
> exec usp_getvalues , it gives the error
> Thanks in Advance
> Thomson
>

Cannot Update Identity Column while doing Select statement

Hi All,
i do have stored procedure
in which there select id,name from tablename
id i primary key and Identity seed is there for this column
When i directly query this statement it " select id,name from
tablename" , it shows the results
But when i call the same thing via stored procedure , it says Cannot
update identity colum
exec usp_getvalues , it gives the error
Thanks in Advance
ThomsonHi
You would not expect this error from the query you have given therefore I
suspect that your stored procedure is doing something different. Post the
code for the store stored procedure and related tables see
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL in the news
group.
At a guess there is an insert/update statement and you are either including
the identity column or don't have SET IDENTITY_INSERT ON for the table.
John
"thomson" wrote:

> Hi All,
> i do have stored procedure
> in which there select id,name from tablename
> id i primary key and Identity seed is there for this column
>
> When i directly query this statement it " select id,name from
> tablename" , it shows the results
> But when i call the same thing via stored procedure , it says Cannot
> update identity colum
> exec usp_getvalues , it gives the error
> Thanks in Advance
> Thomson
>|||identity columns are not updateable.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1159770767.122534.31190@.h48g2000cwc.googlegroups.com...
> Hi All,
> i do have stored procedure
> in which there select id,name from tablename
> id i primary key and Identity seed is there for this column
>
> When i directly query this statement it " select id,name from
> tablename" , it shows the results
> But when i call the same thing via stored procedure , it says Cannot
> update identity colum
> exec usp_getvalues , it gives the error
> Thanks in Advance
> Thomson
>

Cannot update ID because of no existing relationship

I have one table Phone and a table SmsMessage that are linked by the Cellnumber. Cellnumber is the primary key in Phone.

For some reason in the table Phone the Cellnumbers are stored with extra spaces like: '+27000000000 ', but in the SmsMessage table the same value is stored as '+27000000000'. However when I want perform an update to trim the Cellnumbers, I get the message 'Cannot modify values Cellnumber in Phone because there are dependent values in SmsMessage.

The fact is that there are no dependent values, but for MSSQL '+27000000000' and '+27000000000 ' is the same??! Note that the function Len(Cellnumber) gives me the length of the string WITHOUT the spaces as well.

Even if I remove all relationships from Phone, I still get the same error. Are there more places in MSSQL where relationships are stored besides the Diagrams?

Or is there a command that tells MSSQL to ignore all relationships for the next query?

Any ideas?I suspect that the data is stored in the column without the trailing spaces, although it may be presented with spaces added. This is common when retrieving data in columnar form. To test this, retrieve an expression rather than the raw column to see what you actually get, something like:SELECT myColumn, '[' + myColumn + ']'
FROM myTableThe real relationship information is stored in system tables in the form of constraints. The graphic representation is easier to understand, but the constraints are the "final answer" in terms of the relationships that MS-SQL enforces.

-PatP

Cannot shrink large database file

I have a server that is running SQL 2000. There is one large database 200
GB in size made of three files in the primary file group. We just freed up
40 GB of space in the db by archiving a table. The taskpad of SQL indicates
all this comes from the first file in the primary filegroup.
Our problem is that we've had a ton of trouble shrinking it down. We've
tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
haven't done empty file because we do not want to remove the file. Out of
all of our attempts, only one try has succeeded in removing 10 GB, and that
took 4 hours. DBCC CHECKDB shows no errors, we did a backup successfully.
It just looks like it is running slow with high CPU and DISK I/O. The data
files are all on a fiber-optic SAN. Any suggestions? Thanks.

*************************************************
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
*************************************************
The shrinking process usually is very painful and resource intensive. How
long it takes usually depends on the hardware and how much activity you have
in that file. In order to shrink the file SQL Server needs to move any data
pages near the end of the file towards the beginning of the file. This is a
fully logged operation and can be blocked just like any other process that
updates pages. 40GB in a 200GB db is not that much free space. You need
plenty of free space on a regular basis to handle things like reindexing
anyway. If you must shrink then use SHRINKFILE vs. SHRINKDATABASE and make
sure when you are done that all the files in the file group are the same
size. Have a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Andy S." <andymcdba1@.noreply.yahoo.com> wrote in message
news:%23D6br50bFHA.3048@.TK2MSFTNGP12.phx.gbl...
>I have a server that is running SQL 2000. There is one large database 200
>GB in size made of three files in the primary file group. We just freed up
>40 GB of space in the db by archiving a table. The taskpad of SQL
>indicates all this comes from the first file in the primary filegroup.
> Our problem is that we've had a ton of trouble shrinking it down. We've
> tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
> haven't done empty file because we do not want to remove the file. Out of
> all of our attempts, only one try has succeeded in removing 10 GB, and
> that took 4 hours. DBCC CHECKDB shows no errors, we did a backup
> successfully. It just looks like it is running slow with high CPU and DISK
> I/O. The data files are all on a fiber-optic SAN. Any suggestions?
> Thanks.
>
> --
> *************************************************
> Andy S.
> andymcdba1@.noreply.yahoo.com
> Please remove "noreply" before replying.
> *************************************************
>

Cannot shrink large database file

I have a server that is running SQL 2000. There is one large database 200
GB in size made of three files in the primary file group. We just freed up
40 GB of space in the db by archiving a table. The taskpad of SQL indicates
all this comes from the first file in the primary filegroup.
Our problem is that we've had a ton of trouble shrinking it down. We've
tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
haven't done empty file because we do not want to remove the file. Out of
all of our attempts, only one try has succeeded in removing 10 GB, and that
took 4 hours. DBCC CHECKDB shows no errors, we did a backup successfully.
It just looks like it is running slow with high CPU and DISK I/O. The data
files are all on a fiber-optic SAN. Any suggestions? Thanks.
****************************************
*********
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
****************************************
*********The shrinking process usually is very painful and resource intensive. How
long it takes usually depends on the hardware and how much activity you have
in that file. In order to shrink the file SQL Server needs to move any data
pages near the end of the file towards the beginning of the file. This is a
fully logged operation and can be blocked just like any other process that
updates pages. 40GB in a 200GB db is not that much free space. You need
plenty of free space on a regular basis to handle things like reindexing
anyway. If you must shrink then use SHRINKFILE vs. SHRINKDATABASE and make
sure when you are done that all the files in the file group are the same
size. Have a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Andy S." <andymcdba1@.noreply.yahoo.com> wrote in message
news:%23D6br50bFHA.3048@.TK2MSFTNGP12.phx.gbl...
>I have a server that is running SQL 2000. There is one large database 200
>GB in size made of three files in the primary file group. We just freed up
>40 GB of space in the db by archiving a table. The taskpad of SQL
>indicates all this comes from the first file in the primary filegroup.
> Our problem is that we've had a ton of trouble shrinking it down. We've
> tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
> haven't done empty file because we do not want to remove the file. Out of
> all of our attempts, only one try has succeeded in removing 10 GB, and
> that took 4 hours. DBCC CHECKDB shows no errors, we did a backup
> successfully. It just looks like it is running slow with high CPU and DISK
> I/O. The data files are all on a fiber-optic SAN. Any suggestions?
> Thanks.
>
> --
> ****************************************
*********
> Andy S.
> andymcdba1@.noreply.yahoo.com
> Please remove "noreply" before replying.
> ****************************************
*********
>

Cannot shrink large database file

I have a server that is running SQL 2000. There is one large database 200
GB in size made of three files in the primary file group. We just freed up
40 GB of space in the db by archiving a table. The taskpad of SQL indicates
all this comes from the first file in the primary filegroup.
Our problem is that we've had a ton of trouble shrinking it down. We've
tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
haven't done empty file because we do not want to remove the file. Out of
all of our attempts, only one try has succeeded in removing 10 GB, and that
took 4 hours. DBCC CHECKDB shows no errors, we did a backup successfully.
It just looks like it is running slow with high CPU and DISK I/O. The data
files are all on a fiber-optic SAN. Any suggestions? Thanks.
--
*************************************************
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
*************************************************The shrinking process usually is very painful and resource intensive. How
long it takes usually depends on the hardware and how much activity you have
in that file. In order to shrink the file SQL Server needs to move any data
pages near the end of the file towards the beginning of the file. This is a
fully logged operation and can be blocked just like any other process that
updates pages. 40GB in a 200GB db is not that much free space. You need
plenty of free space on a regular basis to handle things like reindexing
anyway. If you must shrink then use SHRINKFILE vs. SHRINKDATABASE and make
sure when you are done that all the files in the file group are the same
size. Have a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
--
Andrew J. Kelly SQL MVP
"Andy S." <andymcdba1@.noreply.yahoo.com> wrote in message
news:%23D6br50bFHA.3048@.TK2MSFTNGP12.phx.gbl...
>I have a server that is running SQL 2000. There is one large database 200
>GB in size made of three files in the primary file group. We just freed up
>40 GB of space in the db by archiving a table. The taskpad of SQL
>indicates all this comes from the first file in the primary filegroup.
> Our problem is that we've had a ton of trouble shrinking it down. We've
> tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
> haven't done empty file because we do not want to remove the file. Out of
> all of our attempts, only one try has succeeded in removing 10 GB, and
> that took 4 hours. DBCC CHECKDB shows no errors, we did a backup
> successfully. It just looks like it is running slow with high CPU and DISK
> I/O. The data files are all on a fiber-optic SAN. Any suggestions?
> Thanks.
>
> --
> *************************************************
> Andy S.
> andymcdba1@.noreply.yahoo.com
> Please remove "noreply" before replying.
> *************************************************
>

Cannot see index in Object Explorer on Replicated Database

I have replicated all the tables from the Business Data database to my instance of SQL Server 2005. They did not create primary keys on the tables, so I am adding them on the replicated database. I was trying to save one of the tables after adding a new primary key and got this error message:

'BuildingValues' table
- Unable to create index 'PK_BuildingValues'. CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.BuildingValues' and index name 'PK_BuildingValues'. The duplicate key value is (2). Could not create constraint. See previous errors. The statement has been terminated.

The problem is that there is nothing displayed in the Indexes folder for this table in Object Viewer (or any of the other folders for this table), so how would I know an index already existed? The index shows up for this table on the Publisher database, but not the Replicated database. Is this normal?

Sounds like you only replicated the data over without transfering other objects (i.e. pk, constraints, indexes, etc.). The error here is that your current data is not qualified for a pk constraint - pk creates an unique key/constraint underneath, thus, the values cannot be duplicated.
To find the dupes, try:
select [your_pk_col]
from tb
group by [your_pk_col]
having count(*)>1
Resolve that and you should be able to create your pk.
Indexes should get created on the subscriber if you configure it during publication creation - go to the object properties.|||

Thank you very much for your answer. The articles at the Publisher were configured to replicate all the keys and constraints to the Subscriber. You were right about the cause of the problem. The index could not be created, because of duplicate values in the column on the Subscriber. Once I added another column to make it unique, the primary key worked fine. Thanks again.