re-install Windows, how to restore databases?

I have SQL Server 2000 running on Windows 2000 Professional, and need
to upgrade this to Windows 2000 Server. In SQL, I have 64 separate
databases used for client development work. After upgrading Windows,
and re-installing SQL Server, how can I reconnect all of the existing
databases without too much work? I'd rather not have to backup, add,
and restore each one individually.How about scripting it. Write some TSQL using a cursor to loop sysdatabases and backup each
databases using dynamic SQL. Then use a similar way to restore based on the files in that directory.
For the restore part, you can use some of the code I have at:
http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<rgurganus@.citidc.com> wrote in message
news:1124991326.785503.210230@.f14g2000cwb.googlegroups.com...
>I have SQL Server 2000 running on Windows 2000 Professional, and need
> to upgrade this to Windows 2000 Server. In SQL, I have 64 separate
> databases used for client development work. After upgrading Windows,
> and re-installing SQL Server, how can I reconnect all of the existing
> databases without too much work? I'd rather not have to backup, add,
> and restore each one individually.
>|||Hi,
You can detach the databases and Copy both Data and log files.
After reinstalling win2000 you can attach it back
regards,
--
Herbert
"rgurganus@.citidc.com" wrote:
> I have SQL Server 2000 running on Windows 2000 Professional, and need
> to upgrade this to Windows 2000 Server. In SQL, I have 64 separate
> databases used for client development work. After upgrading Windows,
> and re-installing SQL Server, how can I reconnect all of the existing
> databases without too much work? I'd rather not have to backup, add,
> and restore each one individually.
>|||Yes, but with 64+ different databases, I was hoping to avoid detatching
each one, and re-attaching each one. I'm coming out of a MySQL
background, where this would be a piece of cake. You'd just copy the
whole data directory where all the database files are, and restart the
service. Oh well...|||Did you read my post?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<rgurganus@.citidc.com> wrote in message news:1125068039.740731.98460@.g49g2000cwa.googlegroups.com...
> Yes, but with 64+ different databases, I was hoping to avoid detatching
> each one, and re-attaching each one. I'm coming out of a MySQL
> background, where this would be a piece of cake. You'd just copy the
> whole data directory where all the database files are, and restart the
> service. Oh well...
>|||Yes, thanks. That may help. I'm just giving into dislike and bashing
of MS SQL in general. Systems should make things easier, not more
difficult. :(

re-install Windows, how to restore databases?

I have SQL Server 2000 running on Windows 2000 Professional, and need
to upgrade this to Windows 2000 Server. In SQL, I have 64 separate
databases used for client development work. After upgrading Windows,
and re-installing SQL Server, how can I reconnect all of the existing
databases without too much work? I'd rather not have to backup, add,
and restore each one individually.
How about scripting it. Write some TSQL using a cursor to loop sysdatabases and backup each
databases using dynamic SQL. Then use a similar way to restore based on the files in that directory.
For the restore part, you can use some of the code I have at:
http://www.karaszi.com/SQLServer/uti...ll_in_file.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<rgurganus@.citidc.com> wrote in message
news:1124991326.785503.210230@.f14g2000cwb.googlegr oups.com...
>I have SQL Server 2000 running on Windows 2000 Professional, and need
> to upgrade this to Windows 2000 Server. In SQL, I have 64 separate
> databases used for client development work. After upgrading Windows,
> and re-installing SQL Server, how can I reconnect all of the existing
> databases without too much work? I'd rather not have to backup, add,
> and restore each one individually.
>
|||Hi,
You can detach the databases and Copy both Data and log files.
After reinstalling win2000 you can attach it back
regards,
Herbert
"rgurganus@.citidc.com" wrote:

> I have SQL Server 2000 running on Windows 2000 Professional, and need
> to upgrade this to Windows 2000 Server. In SQL, I have 64 separate
> databases used for client development work. After upgrading Windows,
> and re-installing SQL Server, how can I reconnect all of the existing
> databases without too much work? I'd rather not have to backup, add,
> and restore each one individually.
>
|||Yes, but with 64+ different databases, I was hoping to avoid detatching
each one, and re-attaching each one. I'm coming out of a MySQL
background, where this would be a piece of cake. You'd just copy the
whole data directory where all the database files are, and restart the
service. Oh well...
|||Did you read my post?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<rgurganus@.citidc.com> wrote in message news:1125068039.740731.98460@.g49g2000cwa.googlegro ups.com...
> Yes, but with 64+ different databases, I was hoping to avoid detatching
> each one, and re-attaching each one. I'm coming out of a MySQL
> background, where this would be a piece of cake. You'd just copy the
> whole data directory where all the database files are, and restart the
> service. Oh well...
>
|||Yes, thanks. That may help. I'm just giving into dislike and bashing
of MS SQL in general. Systems should make things easier, not more
difficult.

re-install Windows, how to restore databases?

I have SQL Server 2000 running on Windows 2000 Professional, and need
to upgrade this to Windows 2000 Server. In SQL, I have 64 separate
databases used for client development work. After upgrading Windows,
and re-installing SQL Server, how can I reconnect all of the existing
databases without too much work? I'd rather not have to backup, add,
and restore each one individually.How about scripting it. Write some TSQL using a cursor to loop sysdatabases
and backup each
databases using dynamic SQL. Then use a similar way to restore based on the
files in that directory.
For the restore part, you can use some of the code I have at:
http://www.karaszi.com/SQLServer/ut...all_in_file.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<rgurganus@.citidc.com> wrote in message
news:1124991326.785503.210230@.f14g2000cwb.googlegroups.com...
>I have SQL Server 2000 running on Windows 2000 Professional, and need
> to upgrade this to Windows 2000 Server. In SQL, I have 64 separate
> databases used for client development work. After upgrading Windows,
> and re-installing SQL Server, how can I reconnect all of the existing
> databases without too much work? I'd rather not have to backup, add,
> and restore each one individually.
>|||Hi,
You can detach the databases and Copy both Data and log files.
After reinstalling win2000 you can attach it back
regards,
--
Herbert
"rgurganus@.citidc.com" wrote:

> I have SQL Server 2000 running on Windows 2000 Professional, and need
> to upgrade this to Windows 2000 Server. In SQL, I have 64 separate
> databases used for client development work. After upgrading Windows,
> and re-installing SQL Server, how can I reconnect all of the existing
> databases without too much work? I'd rather not have to backup, add,
> and restore each one individually.
>|||Yes, but with 64+ different databases, I was hoping to avoid detatching
each one, and re-attaching each one. I'm coming out of a MySQL
background, where this would be a piece of cake. You'd just copy the
whole data directory where all the database files are, and restart the
service. Oh well...|||Did you read my post?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<rgurganus@.citidc.com> wrote in message news:1125068039.740731.98460@.g49g2000cwa.googlegroup
s.com...
> Yes, but with 64+ different databases, I was hoping to avoid detatching
> each one, and re-attaching each one. I'm coming out of a MySQL
> background, where this would be a piece of cake. You'd just copy the
> whole data directory where all the database files are, and restart the
> service. Oh well...
>|||Yes, thanks. That may help. I'm just giving into dislike and bashing
of MS SQL in general. Systems should make things easier, not more
difficult.

Reinstall SQL 2005 Management Studio

We are running SQL 2005 Standard Server. Apparently our server was slightly
"damaged" and SQL 2005 Management Studio is no longer installed.
How can I re-intall this? I have tried reloading SQL to no avail. I am not
seeing it as an option. I am selecting client tools during the installation.
SQL Server is running as Dynamics GP is functional.
I am wondering if it is installed and the shortcut is missing. Can anyone
tell me the executable program name and the location so I can check this.
All help greatly appreciated.Jerry
Try Start--Run--sqlwb.exe
"Jerry Flatto" <JerryFlatto@.discussions.microsoft.com> wrote in message
news:63E1823D-01D7-435E-AF5B-25629A2CEF7B@.microsoft.com...
> We are running SQL 2005 Standard Server. Apparently our server was
> slightly
> "damaged" and SQL 2005 Management Studio is no longer installed.
> How can I re-intall this? I have tried reloading SQL to no avail. I am not
> seeing it as an option. I am selecting client tools during the
> installation.
> SQL Server is running as Dynamics GP is functional.
> I am wondering if it is installed and the shortcut is missing. Can anyone
> tell me the executable program name and the location so I can check this.
> All help greatly appreciated.|||THANKS!!!. That solved the problem. Unfortunately, the exe's name was so
cryptic, I did not realize that the program was still even installed.
Jerry
"Uri Dimant" wrote:
> Jerry
> Try Start--Run--sqlwb.exe
>|||In SQL Server 2008, SQL Server Management Studio' s executable file name is
"ssms.exe".
--
Ekrem Ã?nsoy
"Jerry Flatto" <JerryFlatto@.discussions.microsoft.com> wrote in message
news:48CCC995-0509-4BCA-8FC9-EAC9197C3F72@.microsoft.com...
> THANKS!!!. That solved the problem. Unfortunately, the exe's name was so
> cryptic, I did not realize that the program was still even installed.
> Jerry
> "Uri Dimant" wrote:
>> Jerry
>> Try Start--Run--sqlwb.exe
>>|||The tool was, while in beta, called "SQL Server WorkBench", and the name for the exe file stayed...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jerry Flatto" <JerryFlatto@.discussions.microsoft.com> wrote in message
news:48CCC995-0509-4BCA-8FC9-EAC9197C3F72@.microsoft.com...
> THANKS!!!. That solved the problem. Unfortunately, the exe's name was so
> cryptic, I did not realize that the program was still even installed.
> Jerry
> "Uri Dimant" wrote:
>> Jerry
>> Try Start--Run--sqlwb.exe
>>

Reinstall RS Client?

Hi,
I have purchased a new copy of SQL Server for running Reporting Service as
the Evaluation copy has expired.
I have installed Visual Studio with RS client on a workstation as I don't
want to install Report Designer on the Server.
I would like to know is it necessary for me to uninstall the Evaluation Copy
RS Client on my workstation before installing the new RS Client ?
Your advice is sought.I am pretty sure the evaluation expiration is only the server part of RS,
not the design tools. Are you getting a message saying that it has expired
when you try to design? You should still be able to design and preview. If
that is the case (I am trying to remember what I did but I don't think I did
anything with the client tools). Unless you have a problem my suggestion is
to leave the designer alone. After installing the new server you should
download SP2 and install that at both the server and the client.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:%239RZUnfsFHA.3424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have purchased a new copy of SQL Server for running Reporting Service as
> the Evaluation copy has expired.
> I have installed Visual Studio with RS client on a workstation as I don't
> want to install Report Designer on the Server.
> I would like to know is it necessary for me to uninstall the Evaluation
> Copy RS Client on my workstation before installing the new RS Client ?
> Your advice is sought.
>

Reinstall of RS on a 64 bit box

We have had the same issue on three 64 bit boxes when we tried to
reinstall RS. After removing RS and reinstalling when running the RS
configuration tool, when we hit the 'find' button it just blinks and
nothing happens. When running a profile in the back ground we see it
is trying to run the 'getverison' stored procedure in the master
database. This sp exists in the RS database. Since we have just done
a reinstall and not yet configured the system should realize the RS db
does not exsist and not even run this sp. I have tried restoring the
RS dbs from backup. Creating all of the RS objects in the master db
(I know not a good idea) nothing helps. I opened a PS case with
Microsoft. They suggested removing RS as well as the client tools.
This worked on 32 bit boxes, but not 64. I spent a month on this PS
case and they could only suggest rekicking the box.
Any ideas?On Aug 23, 5:14 pm, jim.stei...@.rackspace.com wrote:
> We have had the same issue on three 64 bit boxes when we tried to
> reinstall RS. After removing RS and reinstalling when running the RS
> configuration tool, when we hit the 'find' button it just blinks and
> nothing happens. When running a profile in the back ground we see it
> is trying to run the 'getverison' stored procedure in the master
> database. This sp exists in the RS database. Since we have just done
> a reinstall and not yet configured the system should realize the RS db
> does not exsist and not even run this sp. I have tried restoring the
> RS dbs from backup. Creating all of the RS objects in the master db
> (I know not a good idea) nothing helps. I opened a PS case with
> Microsoft. They suggested removing RS as well as the client tools.
> This worked on 32 bit boxes, but not 64. I spent a month on this PS
> case and they could only suggest rekicking the box.
> Any ideas?
You might want to make sure that you removed all of the Reporting
Services components in the registry and possibly even the Assembly
references in the GAC. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

re-install MSDE

Hi,
I've been trying to install the IBUYSPY samples and have been running into server problems. I've done everything from uninstalling and reinstalling MSDE to manually erasing the data files via window explorer. I've got everything so screwed up now, that e
ven though MSDE is installed, I can't access it through Server Explorer in VS DotNet. I have 2 questions
1) How can I do a clean install of MSDE (erase everything, then reinstall everything)?
2) What significance do the aspnet_wp and SQLDebugger accounts have? Should these accounts be set as administrator accounts, and should they be password protected?
I've had both IBUYSPY samples installed and working properly before... I'm almost ready to re-format my hard drive and start from scratch!!
Thanks!!
Curt
Hi Curt,
For reinstalling MSDE, I would suggest you go through the steps listed in
the following KB article of uninstalling the MSDE and then you can do a
fresh installation.
HOW TO: Manually Remove a Microsoft SQL Server 2000 Desktop Engine (MSDE
2000) Instance
http://support.microsoft.com/default...&Product=sql2k
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
sql

reinitialized subscription - more info

Running - Win 2K / SQL 2K
I am constantly, once a week or so,getting an error on a
transactional publication that I have...
The subscription(s) have been marked inactive and must be
reinitialized. NoSync subscriptions will need to be
dropped and recreated.
This publication is set up to be continious and is
checked daily to verify it is running. I will come in
one morning and there will be an error. There are 15
remote locations using a transactional/continous
publication to move data to the main office.
How can I prevent this from happening?
What is causing this?
Is there some logging option I can turn on to see more
info about the transactions?
Larry....
By default is a publisher and subscriber have not "talked to" each other for
14 days the subscriber is marked inactive and the subscription may be
dropped. You must reinitialize, regenerate and redistribute the snapshot to
clear this condition.
You can set the interval by right clicking on your publication, selecting
properties and in the subscription expiration section, set a long expiration
value.
I normally run scripts that detect whether the distribution agent has
replicated a transaction to the subscriber in the last 24 hours, and if not,
it raises an alert which causes operations to be paged.
This raises the visibility of these errors.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"larry..." <anonymous@.discussions.microsoft.com> wrote in message
news:9b3601c43446$63f03c20$a401280a@.phx.gbl...
> Running - Win 2K / SQL 2K
> I am constantly, once a week or so,getting an error on a
> transactional publication that I have...
> The subscription(s) have been marked inactive and must be
> reinitialized. NoSync subscriptions will need to be
> dropped and recreated.
> This publication is set up to be continious and is
> checked daily to verify it is running. I will come in
> one morning and there will be an error. There are 15
> remote locations using a transactional/continous
> publication to move data to the main office.
> How can I prevent this from happening?
> What is causing this?
> Is there some logging option I can turn on to see more
> info about the transactions?
> Larry....
>

reinitialized subscription

Running - Win 2K / SQL 2K
I am constantly getting an error on a merge
publication/subscription that I have...
The subscription(s) have been marked inactive and must be
reinitialized. NoSync subscriptions will need to be
dropped and recreated.
How can I prevent this from happening?
What is causing this?
Larry...
you normally get this error when your subscribers haven't connected to your
publisher within 14 days for transactional and merge publications.
The way to prevent this is to ensure your subscriber connects to your
publisher within this period.
Exactly how frequently are you getting this error?
"larry" <anonymous@.discussions.microsoft.com> wrote in message
news:89a401c432b4$81e001e0$a401280a@.phx.gbl...
> Running - Win 2K / SQL 2K
> I am constantly getting an error on a merge
> publication/subscription that I have...
> The subscription(s) have been marked inactive and must be
> reinitialized. NoSync subscriptions will need to be
> dropped and recreated.
> How can I prevent this from happening?
> What is causing this?
> Larry...
|||.....Exactly how frequently are you getting this error?
Hilary,
I get this on one of my continous transactional
subscriptions once every couple of weeks. I check
replication every day and in fact, the SQL server agent
gets restarted every night @. 2:00 am.
Larry...

REINDEXING?

I have a big table with more than 7583117
records. The table is updated every day during the End of the day activity.

There is a job running a query on this table for updating another table.

The table is not reindexed and the job running freezes on the EXECUTING state many times is it because of not reindexing the indexes?

Thanks in advance

Jacx

Hi Jacx,

What dou you see in SQL Server Error Log?

In the moment of this job running you monitoring the locks?

Regards,

|||

I checked the error log nothing about that particular job because it is not cancelled because of an error but it stays in the executing state.

|||When you say it "freezes" what do you mean, where are you seeing it "freeze"? Have you run the Profiler to see what is reallly going on?

How are you updating the table and selecting the records? Are you doing:

INSERT INTO XXX
SELECT ......

If so, that is probably expanding tempdb when you see it "freeze" because it writtes the entire select to tempdb and then inserts it into the target table.

Without seeing exactly how you are "updating" the table, it is hard to say what it is doing. The index being corrupt and needing reindexing is not normally a problem in MS SQL.

ReIndexing while using replication Important

Does anyone know of a good set of guidelines to follow, after running DBCC
Showcontig on a publisher of replication and DBCC reindexing is called for?
What are the pitfalls and things to watch out for? I can't afford to make a
mistake since there are three servers involved and it's in production.
I can do the reindexing during night time hours. I've read a lot of material
but haven't found "replication considerations" and have not found anything
other than generalities when explaining how do analyze the Index fragmenting
statistics.
Matthew Mark
For merge replication it helps to reindex or defragment the msmerge tables.
You need to stop the merge agents which you are doing this. Ideally you
would stop all users while you are doing the reindexing.
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
"Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
news:15C56BD5-DED8-41D2-9C16-9C87B664751C@.microsoft.com...
> Does anyone know of a good set of guidelines to follow, after running DBCC
> Showcontig on a publisher of replication and DBCC reindexing is called
> for?
> What are the pitfalls and things to watch out for? I can't afford to make
> a
> mistake since there are three servers involved and it's in production.
> I can do the reindexing during night time hours. I've read a lot of
> material
> but haven't found "replication considerations" and have not found anything
> other than generalities when explaining how do analyze the Index
> fragmenting
> statistics.
> --
> Matthew Mark
>
|||Here's what I understand from this.
Since I couldn't find a MSMerge table I assume you mean to reindex the
Article (tables) in MSarticles from the publisher.
If so, how does this fall into the procedure. For example, based on what I
know now, here's how I would do this.
1. Turn all merge agents off.
2. Put DB in restricted mode.
3. Run DBCC reindex, looping through all tables in this database.
4. Put dB back in Full access mode.
5. Turn merge agents back on.
So, how does your suggesting fit in or modify this plan?
Does the reindexing naturally replicate to the subscribers? or ?
thanks
Matthew Mark
"Hilary Cotter" wrote:

> For merge replication it helps to reindex or defragment the msmerge tables.
> You need to stop the merge agents which you are doing this. Ideally you
> would stop all users while you are doing the reindexing.
> --
> 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
>
> "Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
> news:15C56BD5-DED8-41D2-9C16-9C87B664751C@.microsoft.com...
>
>
|||reindexing does not propagate to the subscribers, I meant that you should
reinindex msmerge_contents, and msmerge_tombstone.
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
"Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
news:2CB7AB6E-8D3B-48D8-A092-E55A8A25540A@.microsoft.com...[vbcol=seagreen]
> Here's what I understand from this.
> Since I couldn't find a MSMerge table I assume you mean to reindex the
> Article (tables) in MSarticles from the publisher.
> If so, how does this fall into the procedure. For example, based on what I
> know now, here's how I would do this.
> 1. Turn all merge agents off.
> 2. Put DB in restricted mode.
> 3. Run DBCC reindex, looping through all tables in this database.
> 4. Put dB back in Full access mode.
> 5. Turn merge agents back on.
> So, how does your suggesting fit in or modify this plan?
> Does the reindexing naturally replicate to the subscribers? or ?
>
> thanks
>
> --
> Matthew Mark
>
> "Hilary Cotter" wrote:
|||When I do reindex, and loop through the tables, doesn't it include these
tabels too?
Matthew Mark
"Hilary Cotter" wrote:

> reindexing does not propagate to the subscribers, I meant that you should
> reinindex msmerge_contents, and msmerge_tombstone.
> --
> 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
>
> "Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
> news:2CB7AB6E-8D3B-48D8-A092-E55A8A25540A@.microsoft.com...
>
>
|||Matthew,
this partly depends on what you are reindexing - system tables or user
tables. Generally speaking though, I ensure synchronization isn't occurring
while reindexing takes place, otherwise there tend to be job failures
because of blocking.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul,
I have never run reindexing on a replicated db. I have ,though, run a script
that runs the reindexing on all user tables. Are these MSarticles etc tables
system tables? I'd assume they are since the replication process creates it.
To avoid asking you a lot of specific questions, is there a comprehensive
guide I can read? The microsoft info online is usually good, only if you
already know the pitfalls. I don't.
If I make a mistake here because I didn't do my homework..., well, I'm sure
you understand. We don't replicate on our development server, so I can't
exactly practice ahead of time.
Matthew Mark
"Paul Ibison" wrote:

> Matthew,
> this partly depends on what you are reindexing - system tables or user
> tables. Generally speaking though, I ensure synchronization isn't occurring
> while reindexing takes place, otherwise there tend to be job failures
> because of blocking.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||I am unsure what you mean here. Can you clarify it? I normally issue the
following dbcc dbreindex('msmerge_contents')
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
"Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
news:9038947F-DAA7-476C-92ED-729E905E77E3@.microsoft.com...[vbcol=seagreen]
> When I do reindex, and loop through the tables, doesn't it include these
> tabels too?
> --
> Matthew Mark
>
> "Hilary Cotter" wrote:
|||Matthew,
I don't know of any guide about reindexing and replication. However I'm not
too sure there's much to it. If you know the difference between DBCC
DBREINDEX and DBCC INDEXDEFRAG, understand locking and blocking and can see
what the agents require access to in order to work, these are the basic
facts. All I can sey is that a simple guideline is to run DBCC DBREINDEX not
overly often and when you do, try to ensure that synchronization isn't
occurring.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Re-indexing through clocks changing

I was planning to re-index my database (225GB) this coming weekend however I
have just realised that it will be running through the server clock changing
(advancing 1 hour). Will this have any adverse effect on the procedure?
GavNo problem. The only thing to be careful with is when you go back in time,
and you have transactions occurring during that time, you can find yourself
in a position where you cannot do point in time restore for transaction that
occurred during that hour.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Gavin Metcalfe" <gavin.metcalfe@.btinternet.com> wrote in message
news:c3n37r$l3p$1@.hercules.btinternet.com...
> I was planning to re-index my database (225GB) this coming weekend however
I
> have just realised that it will be running through the server clock
changing
> (advancing 1 hour). Will this have any adverse effect on the procedure?
> Gav
>

Re-Index SQL Server Express User Instance?

Is there a way to re-index a SQL Server Express User Instance? If I try to open the .mdf while the website is still running, I get a message stating that the file is in use. If I shut down the web service and open the .mdf, then restart the website, then the website cannot access the .mdf while I have it open in VStudio (reminds me a lot of Access).

In the past I tried to open a user instance with SQL Server Management Studio, but then it goofed up my user-instance...so I am hesitant to try that again. Is there any way to re-index?

Thanks!

Backup and restore and adjust Web.Config, then ALTER Table drop INDEX and REINDEX, the reason is currently your User Instance is behaving like a separate instance of SQL Server and that is not valid. Hope this helps.

http://forums.asp.net/thread/1454694.aspx

Reindex depleting Working Set

I am running SQL 2005, SP2, 64 bit Standard Edition, with 16 GB RAM. The Min
Server Memory setting is 0, and Max is set at 14 GB.
When I reindex (ALTER INDEX...REBUILD) a database that is approximately 6GB
in size, the server grinds to a halt and the error log contains errors
similar to the following:
A significant part of sql server process memory has been paged out. This
may result in a performance degradation. Duration: 655 seconds. Working set
(KB): 1009520, committed (KB): 7169156, memory utilization: 14%.
Reporting Services is also running on this box, but when the Reindex is
taking place, the Reindex job is the only active SPID.
The "PF Usage" in Task Manager is at 15.3 GB.
Any idea what one can do to allow this Reindex to take place?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1Hi
Check you are locking pages in memory see:
http://support.microsoft.com/kb/918483
John
"cbrichards via SQLMonster.com" wrote:
> I am running SQL 2005, SP2, 64 bit Standard Edition, with 16 GB RAM. The Min
> Server Memory setting is 0, and Max is set at 14 GB.
> When I reindex (ALTER INDEX...REBUILD) a database that is approximately 6GB
> in size, the server grinds to a halt and the error log contains errors
> similar to the following:
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 655 seconds. Working set
> (KB): 1009520, committed (KB): 7169156, memory utilization: 14%.
> Reporting Services is also running on this box, but when the Reindex is
> taking place, the Reindex job is the only active SPID.
> The "PF Usage" in Task Manager is at 15.3 GB.
> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||John,
Not applicable to my situation. Lock Pages in Memory is ignored when running
Standard Edition.
Still in need of help!
John Bell wrote:
>Hi
>Check you are locking pages in memory see:
>http://support.microsoft.com/kb/918483
>John
>> I am running SQL 2005, SP2, 64 bit Standard Edition, with 16 GB RAM. The Min
>> Server Memory setting is 0, and Max is set at 14 GB.
>[quoted text clipped - 13 lines]
>> Any idea what one can do to allow this Reindex to take place?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||Hi
Have you tried lowering the max memory and setting the minimum to the same
value (say 12GB)?
John
"cbrichards via SQLMonster.com" wrote:
> John,
> Not applicable to my situation. Lock Pages in Memory is ignored when running
> Standard Edition.
> Still in need of help!
> John Bell wrote:
> >Hi
> >
> >Check you are locking pages in memory see:
> >
> >http://support.microsoft.com/kb/918483
> >
> >John
> >
> >> I am running SQL 2005, SP2, 64 bit Standard Edition, with 16 GB RAM. The Min
> >> Server Memory setting is 0, and Max is set at 14 GB.
> >[quoted text clipped - 13 lines]
> >>
> >> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||I have heard of that option, but I do not have the understanding of SQL
Servers memory structures to know how that would change my reindexing issue.
From what I understand is happening with my reindexing, is that the
reindexing is consuming all 14GB dedicated to SQL Servers buffer pool and
from there, needs even more memory, which is then going to the Page File.
Whether that is correct or not, I do not know, I just know that the Working
Set is being trimmed.
Are you saying that setting the Max and Min server memory settings to the
same value, that this will eliminate Page File usage when reindexing? I know
it will prevent the Working Set from being trimmed, but does that eliminate
Page File swapping?
John Bell wrote:
>Hi
>Have you tried lowering the max memory and setting the minimum to the same
>value (say 12GB)?
>John
>> John,
>> Not applicable to my situation. Lock Pages in Memory is ignored when running
>[quoted text clipped - 15 lines]
>> >>
>> >> Any idea what one can do to allow this Reindex to take place?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||Hi
By setting a lower maximum then it may allow other processes to be allocated
memory without SQL Server grabbing/regrabbing it.
John
"cbrichards via SQLMonster.com" wrote:
> I have heard of that option, but I do not have the understanding of SQL
> Servers memory structures to know how that would change my reindexing issue.
> From what I understand is happening with my reindexing, is that the
> reindexing is consuming all 14GB dedicated to SQL Servers buffer pool and
> from there, needs even more memory, which is then going to the Page File.
> Whether that is correct or not, I do not know, I just know that the Working
> Set is being trimmed.
> Are you saying that setting the Max and Min server memory settings to the
> same value, that this will eliminate Page File usage when reindexing? I know
> it will prevent the Working Set from being trimmed, but does that eliminate
> Page File swapping?
> John Bell wrote:
> >Hi
> >
> >Have you tried lowering the max memory and setting the minimum to the same
> >value (say 12GB)?
> >
> >John
> >
> >> John,
> >> Not applicable to my situation. Lock Pages in Memory is ignored when running
> >[quoted text clipped - 15 lines]
> >> >>
> >> >> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||So you are saying that other processes are chewing up SQL Servers Working Set,
and that by lowering SQL Servers Working Set (from 14Gb to 12Gb) it will
still have enough Working Set memory?
John Bell wrote:
>Hi
>By setting a lower maximum then it may allow other processes to be allocated
>memory without SQL Server grabbing/regrabbing it.
>John
>> I have heard of that option, but I do not have the understanding of SQL
>> Servers memory structures to know how that would change my reindexing issue.
>[quoted text clipped - 22 lines]
>> >> >>
>> >> >> Any idea what one can do to allow this Reindex to take place?
--
Message posted via http://www.sqlmonster.com|||WorkingSet is a perfmon counter for _ALL_ demands against virtual memory,
not just SQL Server.
John is suggesting that you reduce the amount that SQL Server is asking for
in the first place.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79ca83aaaea1d@.uwe...
> So you are saying that other processes are chewing up SQL Servers Working
> Set,
> and that by lowering SQL Servers Working Set (from 14Gb to 12Gb) it will
> still have enough Working Set memory?
> John Bell wrote:
>>Hi
>>By setting a lower maximum then it may allow other processes to be
>>allocated
>>memory without SQL Server grabbing/regrabbing it.
>>John
>> I have heard of that option, but I do not have the understanding of SQL
>> Servers memory structures to know how that would change my reindexing
>> issue.
>>[quoted text clipped - 22 lines]
>> >> >>
>> >> >> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via http://www.sqlmonster.com
>|||Okay, as far as I can tell the reindexing is the only active SPID and this
reindexing is depleting the Working Set.
So you are saying that reducing the amount SQL Server is asking for, will in
turn not deplete the Working Set any more than it is currently? Does this
mean that reducing the amount SQL Server is asking for (reducing from 14GB to
12Gb) will make the Working Set larger?
Jay wrote:
>WorkingSet is a perfmon counter for _ALL_ demands against virtual memory,
>not just SQL Server.
>John is suggesting that you reduce the amount that SQL Server is asking for
>in the first place.
>> So you are saying that other processes are chewing up SQL Servers Working
>> Set,
>[quoted text clipped - 15 lines]
>> >> >>
>> >> >> Any idea what one can do to allow this Reindex to take place?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||What do you mean when you say "Working Set"?
I think you mean the (Windows) perfmon counter that measures the total
demands against virtual memory. However, I have no idea how that concept can
include the term "deplete".
Since it measures the TOTAL, if you reduce the amount of memory demanded,
then the TOTAL demanded will also be reduced.
Beyond that, just try what John suggested without completely understanding
everything first. You will probably be happy with the result and once you
see it, you'll understand better.
Jay
PS. I'm not feeling so well, so I doubt I'll repost until tomorrow at the
soonest.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79cc0a5c0e9bb@.uwe...
> Okay, as far as I can tell the reindexing is the only active SPID and this
> reindexing is depleting the Working Set.
> So you are saying that reducing the amount SQL Server is asking for, will
> in
> turn not deplete the Working Set any more than it is currently? Does this
> mean that reducing the amount SQL Server is asking for (reducing from 14GB
> to
> 12Gb) will make the Working Set larger?
> Jay wrote:
>>WorkingSet is a perfmon counter for _ALL_ demands against virtual memory,
>>not just SQL Server.
>>John is suggesting that you reduce the amount that SQL Server is asking
>>for
>>in the first place.
>> So you are saying that other processes are chewing up SQL Servers
>> Working
>> Set,
>>[quoted text clipped - 15 lines]
>> >> >>
>> >> >> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||When I say "Working Set" I am referring to SQL Servers Working Set, not the
perfmon counter. This is the error in my SQL Error Log that I posted at the
beginning of this thread:
A significant part of sql server process memory has been paged out. This
may result in a performance degradation. Duration: 655 seconds. Working set
(KB): 1009520, committed (KB): 7169156, memory utilization: 14%.
And while I believe John's suggestion may be the solution, I really need to
be able to explain it to my team, before I just make a wild configuration
change.
Which gets back to my latest inquiry:
As far as I can tell the reindexing is the only active SPID and this
reindexing is depleting the Working Set.
And you are saying that reducing the amount SQL Server is asking for, will in
turn not deplete the Working Set any more than it is currently? Does this
mean that reducing the amount SQL Server is asking for (reducing from 14GB to
12Gb) will make the Working Set larger?
Jay wrote:
>What do you mean when you say "Working Set"?
>I think you mean the (Windows) perfmon counter that measures the total
>demands against virtual memory. However, I have no idea how that concept can
>include the term "deplete".
>Since it measures the TOTAL, if you reduce the amount of memory demanded,
>then the TOTAL demanded will also be reduced.
>Beyond that, just try what John suggested without completely understanding
>everything first. You will probably be happy with the result and once you
>see it, you'll understand better.
>Jay
>PS. I'm not feeling so well, so I doubt I'll repost until tomorrow at the
>soonest.
>> Okay, as far as I can tell the reindexing is the only active SPID and this
>> reindexing is depleting the Working Set.
>[quoted text clipped - 19 lines]
>> >> >>
>> >> >> Any idea what one can do to allow this Reindex to take place?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||cbrichards,
I decided to answer your last post first (below) and then look at the
problem from the beginning. You don't really care about Working Sets, paging
and max memory, you just want to re-index a table.
If you read BOL (SQL Server Books Online) for the page "ALTER INDEX" you
will see it is a rich and fully featured command (I was amazed when I first
read it). There are ways you can make it less intrusive to your system.
At the top of my list would be the following three options:
From BOL:
--
REORGANIZE
Specifies the index leaf level will be reorganized. This clause is
equivalent to DBCC INDEXDEFRAG. ALTER INDEX REORGANIZE statement is always
performed online. This means long-term blocking table locks are not held and
queries or updates to the underlying table can continue during the ALTER
INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a disabled
index or an index with ALLOW_PAGE_LOCKS set to OFF.
-and-
The rebuild operation can be minimally logged if the database recovery model
is set to either bulk-logged or simple. For more information, see Choosing a
Recovery Model for Index Operations.
--
and rebuilding one index at a time, not using the ALL option.
I'm not sure how the logging change will affect memory, but it could easily
help. One thing is for sure, your .ldf file won't bloat.
Reorganize is the option I always go for first when dealing with indexes.
It's lower impact overall and will frequently do the job. There might even
be more options that can help you in there, but I can't tell you because I
don't have a heavy use 2005 server yet (14 2000's and 2 2005's).
DBCC SHOWCONTIG('Table') WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS will
tell you the fragmentation within the index.
Also, INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0 will eliminate
indexes where the defrag will do no good.
On to current answers.
> When I say "Working Set" I am referring to SQL Servers Working Set, not
> the
> perfmon counter. This is the error in my SQL Error Log that I posted at
> the
> beginning of this thread:
It is the same thing. The Working Set (for the 3rd time) is the TOTAL demand
for memory. Your total memory is RAM + (Page File size - RAM). If you go
over the RAM you have available, you start to page.
Please read the following link. It is a short article and very good.
http://support.microsoft.com/kb/555223
> And while I believe John's suggestion may be the solution, I really need
> to
> be able to explain it to my team, before I just make a wild configuration
> change.
His suggestion was to reduce SQL Server's MAX memory setting so that it
didn't ask for as much from Windows. Hardly a "wild configuration change".
> Which gets back to my latest inquiry:
> As far as I can tell the reindexing is the only active SPID and this
> reindexing is depleting the Working Set.
The SPID you are looking at is in SQL Server and therefore controlled by the
server's settings.
> And you are saying that reducing the amount SQL Server is asking for, will
> in
> turn not deplete the Working Set any more than it is currently? Does this
> mean that reducing the amount SQL Server is asking for (reducing from 14GB
> to
> 12Gb) will make the Working Set larger?
Sigh. No, it will not make the working set larger, it will make it smaller -
which is what you want. You seem to think the Working Set is the pool of
memory you are drawing from, it is not. The Working Set is drawing from
system memory.
> Jay wrote:
>>What do you mean when you say "Working Set"?
>>I think you mean the (Windows) perfmon counter that measures the total
>>demands against virtual memory. However, I have no idea how that concept
>>can
>>include the term "deplete".
>>Since it measures the TOTAL, if you reduce the amount of memory demanded,
>>then the TOTAL demanded will also be reduced.
>>Beyond that, just try what John suggested without completely understanding
>>everything first. You will probably be happy with the result and once you
>>see it, you'll understand better.
>>Jay
>>PS. I'm not feeling so well, so I doubt I'll repost until tomorrow at the
>>soonest.
>> Okay, as far as I can tell the reindexing is the only active SPID and
>> this
>> reindexing is depleting the Working Set.
>>[quoted text clipped - 19 lines]
>>> >> >>
>>> >> >> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||1) is this a repeatable error?
2) do you have other applications/services running on the server?
3) Is this an HP box? If so, they have a VERY nasty bug in 64 bit in their
iLO system that will flush memory.
4) Check if you are doing any large-file copies at the time. Win2003 64 bit
ALSO has a very nasty bug that will flush memory.
5) only 2GB available is probably not enough even if you don't have other
stuff running other than just the sql relational engine (things such as
reporting services, iis, analysis services, etc, etc)
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79bdf69e9af82@.uwe...
>I am running SQL 2005, SP2, 64 bit Standard Edition, with 16 GB RAM. The
>Min
> Server Memory setting is 0, and Max is set at 14 GB.
> When I reindex (ALTER INDEX...REBUILD) a database that is approximately
> 6GB
> in size, the server grinds to a halt and the error log contains errors
> similar to the following:
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 655 seconds. Working
> set
> (KB): 1009520, committed (KB): 7169156, memory utilization: 14%.
> Reporting Services is also running on this box, but when the Reindex is
> taking place, the Reindex job is the only active SPID.
> The "PF Usage" in Task Manager is at 15.3 GB.
> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||Thanks Jay for the detailed response and the link to RAM, Virtual Memory, and
the Paging File.
I am still not understanding some things.
So the Working Set is drawing from system memory, and when you say system
memory I interpret that as being RAM + Paging File.
In decreasing the Max Server memory I therefore decrease the Working Set
dedicated to SQL Server.
From the following two error messages I read that the sql server process is
utilizing (Max Server or Working Set) memory 0% and 12% respectively.
A significant part of sql server process memory has been paged out. This
may result in a performance degradation. Duration: 0 seconds. Working set
(KB): 93180, committed (KB): 15052932, memory utilization: 0%.
A significant part of sql server process memory has been paged out. This
may result in a performance degradation. Duration: 328 seconds. Working set
(KB): 609700, committed (KB): 4931236, memory utilization: 12%.
My interpretation to my issue of not being able to reindex, is that this
reindexing job exceeds the amount of RAM available and the OS is moving pages
out of SQL Servers Working Set (comparing the (KB) in the the error log
entries above for the Working Set). If in setting the Max Server memory to a
lower setting decreases the Working Set, and the error log shows the Working
Set as suffering already when under stress, I do not see how lowering Max
Server memory will help.
Please clarify.
Jay wrote:
>cbrichards,
>I decided to answer your last post first (below) and then look at the
>problem from the beginning. You don't really care about Working Sets, paging
>and max memory, you just want to re-index a table.
>If you read BOL (SQL Server Books Online) for the page "ALTER INDEX" you
>will see it is a rich and fully featured command (I was amazed when I first
>read it). There are ways you can make it less intrusive to your system.
>At the top of my list would be the following three options:
>From BOL:
>--
> REORGANIZE
> Specifies the index leaf level will be reorganized. This clause is
>equivalent to DBCC INDEXDEFRAG. ALTER INDEX REORGANIZE statement is always
>performed online. This means long-term blocking table locks are not held and
>queries or updates to the underlying table can continue during the ALTER
>INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a disabled
>index or an index with ALLOW_PAGE_LOCKS set to OFF.
>-and-
>The rebuild operation can be minimally logged if the database recovery model
>is set to either bulk-logged or simple. For more information, see Choosing a
>Recovery Model for Index Operations.
>--
>and rebuilding one index at a time, not using the ALL option.
>I'm not sure how the logging change will affect memory, but it could easily
>help. One thing is for sure, your .ldf file won't bloat.
>Reorganize is the option I always go for first when dealing with indexes.
>It's lower impact overall and will frequently do the job. There might even
>be more options that can help you in there, but I can't tell you because I
>don't have a heavy use 2005 server yet (14 2000's and 2 2005's).
>DBCC SHOWCONTIG('Table') WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS will
>tell you the fragmentation within the index.
>Also, INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0 will eliminate
>indexes where the defrag will do no good.
>On to current answers.
>> When I say "Working Set" I am referring to SQL Servers Working Set, not
>> the
>> perfmon counter. This is the error in my SQL Error Log that I posted at
>> the
>> beginning of this thread:
>It is the same thing. The Working Set (for the 3rd time) is the TOTAL demand
>for memory. Your total memory is RAM + (Page File size - RAM). If you go
>over the RAM you have available, you start to page.
>Please read the following link. It is a short article and very good.
>http://support.microsoft.com/kb/555223
>> And while I believe John's suggestion may be the solution, I really need
>> to
>> be able to explain it to my team, before I just make a wild configuration
>> change.
>His suggestion was to reduce SQL Server's MAX memory setting so that it
>didn't ask for as much from Windows. Hardly a "wild configuration change".
>> Which gets back to my latest inquiry:
>> As far as I can tell the reindexing is the only active SPID and this
>> reindexing is depleting the Working Set.
>The SPID you are looking at is in SQL Server and therefore controlled by the
>server's settings.
>> And you are saying that reducing the amount SQL Server is asking for, will
>> in
>> turn not deplete the Working Set any more than it is currently? Does this
>> mean that reducing the amount SQL Server is asking for (reducing from 14GB
>> to
>> 12Gb) will make the Working Set larger?
>Sigh. No, it will not make the working set larger, it will make it smaller -
>which is what you want. You seem to think the Working Set is the pool of
>memory you are drawing from, it is not. The Working Set is drawing from
>system memory.
>>What do you mean when you say "Working Set"?
>[quoted text clipped - 21 lines]
>>> >> >>
>>> >> >> Any idea what one can do to allow this Reindex to take place?
--
Message posted via http://www.sqlmonster.com|||Before going on (and to continue) I require answers to the following:
1) Why are you doing a reindex? What told you you needed it?
2) Syntax specifically how are you doing it?
3) Why have you completly ignored the posibility of using indexdefrag, or
changing the Recovery model while you reindex?
Please post both your reasons and the ALTER INDEX statement (you may, of
course change the database/table/index names, if you feel those shouldn't be
posted).
> Thanks Jay for the detailed response and the link to RAM, Virtual Memory,
> and
> the Paging File.
> I am still not understanding some things.
Did you study the link?
> So the Working Set is drawing from system memory, and when you say system
> memory I interpret that as being RAM + Paging File.
correct.
> In decreasing the Max Server memory I therefore decrease the Working Set
> dedicated to SQL Server.
Decreasing Max Server memory will decrease the Working Set. However, since
the Working Set is a TOTAL for Windows, we neither know, or care what is
dedicated to SQL Server.
> From the following two error messages I read that the sql server process
> is
> utilizing (Max Server or Working Set) memory 0% and 12% respectively.
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 0 seconds. Working set
> (KB): 93180, committed (KB): 15052932, memory utilization: 0%.
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 328 seconds. Working
> set
> (KB): 609700, committed (KB): 4931236, memory utilization: 12%.
> My interpretation to my issue of not being able to reindex, is that this
> reindexing job exceeds the amount of RAM available and the OS is moving
> pages
> out of SQL Servers Working Set (comparing the (KB) in the the error log
> entries above for the Working Set). If in setting the Max Server memory to
> a
> lower setting decreases the Working Set, and the error log shows the
> Working
> Set as suffering already when under stress, I do not see how lowering Max
> Server memory will help.
Because, decreasing the Max Server memory will reduce the amount of used
memory on the system, thus making more available for other tasks.
AND SQL SERVER DOES NOT HAVE A WORKING SET! IT IS USING MEMORY IN WINDOWS
WHERE THE TOTAL DEMAND FOR MEMORY IS THE WORKING SET!
> Please clarify.
>
> Jay wrote:
>>cbrichards,
>>I decided to answer your last post first (below) and then look at the
>>problem from the beginning. You don't really care about Working Sets,
>>paging
>>and max memory, you just want to re-index a table.
>>If you read BOL (SQL Server Books Online) for the page "ALTER INDEX" you
>>will see it is a rich and fully featured command (I was amazed when I
>>first
>>read it). There are ways you can make it less intrusive to your system.
>>At the top of my list would be the following three options:
>>From BOL:
>>--
>> REORGANIZE
>> Specifies the index leaf level will be reorganized. This clause is
>>equivalent to DBCC INDEXDEFRAG. ALTER INDEX REORGANIZE statement is always
>>performed online. This means long-term blocking table locks are not held
>>and
>>queries or updates to the underlying table can continue during the ALTER
>>INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a
>>disabled
>>index or an index with ALLOW_PAGE_LOCKS set to OFF.
>>-and-
>>The rebuild operation can be minimally logged if the database recovery
>>model
>>is set to either bulk-logged or simple. For more information, see Choosing
>>a
>>Recovery Model for Index Operations.
>>--
>>and rebuilding one index at a time, not using the ALL option.
>>I'm not sure how the logging change will affect memory, but it could
>>easily
>>help. One thing is for sure, your .ldf file won't bloat.
>>Reorganize is the option I always go for first when dealing with indexes.
>>It's lower impact overall and will frequently do the job. There might even
>>be more options that can help you in there, but I can't tell you because I
>>don't have a heavy use 2005 server yet (14 2000's and 2 2005's).
>>DBCC SHOWCONTIG('Table') WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS will
>>tell you the fragmentation within the index.
>>Also, INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0 will eliminate
>>indexes where the defrag will do no good.
>>On to current answers.
>> When I say "Working Set" I am referring to SQL Servers Working Set, not
>> the
>> perfmon counter. This is the error in my SQL Error Log that I posted at
>> the
>> beginning of this thread:
>>It is the same thing. The Working Set (for the 3rd time) is the TOTAL
>>demand
>>for memory. Your total memory is RAM + (Page File size - RAM). If you go
>>over the RAM you have available, you start to page.
>>Please read the following link. It is a short article and very good.
>>http://support.microsoft.com/kb/555223
>> And while I believe John's suggestion may be the solution, I really need
>> to
>> be able to explain it to my team, before I just make a wild
>> configuration
>> change.
>>His suggestion was to reduce SQL Server's MAX memory setting so that it
>>didn't ask for as much from Windows. Hardly a "wild configuration change".
>> Which gets back to my latest inquiry:
>> As far as I can tell the reindexing is the only active SPID and this
>> reindexing is depleting the Working Set.
>>The SPID you are looking at is in SQL Server and therefore controlled by
>>the
>>server's settings.
>> And you are saying that reducing the amount SQL Server is asking for,
>> will
>> in
>> turn not deplete the Working Set any more than it is currently? Does
>> this
>> mean that reducing the amount SQL Server is asking for (reducing from
>> 14GB
>> to
>> 12Gb) will make the Working Set larger?
>>Sigh. No, it will not make the working set larger, it will make it
>>smaller -
>>which is what you want. You seem to think the Working Set is the pool of
>>memory you are drawing from, it is not. The Working Set is drawing from
>>system memory.
>>What do you mean when you say "Working Set"?
>>[quoted text clipped - 21 lines]
>>> >> >>
>>> >> >> Any idea what one can do to allow this Reindex to take
>>> >> >> place?
> --
> Message posted via http://www.sqlmonster.com
>|||Before I answer the questions you posed, let me say that it is not just
reindexing that is cratering the server, but queries in general on the server
itself. These queries would not crater the system (I am assuming) if we could
get the tables they referenced, reindexed. But in trying to reindex, the
reindex craters the server. So either way, we are up against a wall.
1a. Why am I doing a reindex?
For the reason most people want to reindex, to get the data they are asking
for returned in a timely and efficient manner.
1b. What told you you needed it?
A. The queries themself taking a long time to return results and at times
degrading overall server performance. Which in turn pointed to index
fragmentation.
B. The following query (when we were lucky enough to get results) confirmed
our suspicions that our indexes were greatly fragmented:
SELECT s.name,
i.name,
ps.page_count,
ps.avg_fragmentation_in_percent,
ps.fragment_count
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
JOIN master.sys.dm_db_index_physical_stats (' + CAST(@.DBID as varchar(max)
) + ', NULL, NULL , NULL, 'LIMITED') ps
ON s.object_id = ps.object_id
AND i.index_id = ps.index_id
WHERE s.type_desc = 'USER_TABLE'
AND i.index_id > 0
AND i.index_id < 255
AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
AND ps.Page_Count >= 100
AND (ps.avg_fragmentation_in_percent >= 5.0
OR ps.fragment_count >= 50)
GROUP BY s.name,
i.name,
ps.page_count,
ps.avg_fragmentation_in_percent,
ps.fragment_count
2. Syntax specifically how are you doing it?
ALTER INDEX ' + @.IndexName + ' ON [' + @.DBName + '].[dbo].[' + @.TableName + ']
REBUILD WITH ( FILLFACTOR = ' + CAST(@.FillFactor AS varchar(3)) + ',
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = OFF )
3. Why have you completly [sic] ignored the posibility [sic] of using
indexdefrag, or changing the Recovery model while you reindex?
I have not ignored the possibility of using indexdefrag. We might give it a
try once we implement new Max/Min Server memory settings. You might say we
are a bit gun shy of making these changes without understanding all the
implications, and additionally, since any resource intense operation seems to
greatly degrade performance, I am a bit hesitant in performing an indexdefrag,
too.
Lastly, the Recovery model on the database is already set to SIMPLE.
Jay wrote:
>Before going on (and to continue) I require answers to the following:
>1) Why are you doing a reindex? What told you you needed it?
>2) Syntax specifically how are you doing it?
>3) Why have you completly ignored the posibility of using indexdefrag, or
>changing the Recovery model while you reindex?
>Please post both your reasons and the ALTER INDEX statement (you may, of
>course change the database/table/index names, if you feel those shouldn't be
>posted).
>> Thanks Jay for the detailed response and the link to RAM, Virtual Memory,
>> and
>> the Paging File.
>> I am still not understanding some things.
>Did you study the link?
>> So the Working Set is drawing from system memory, and when you say system
>> memory I interpret that as being RAM + Paging File.
>correct.
>> In decreasing the Max Server memory I therefore decrease the Working Set
>> dedicated to SQL Server.
>Decreasing Max Server memory will decrease the Working Set. However, since
>the Working Set is a TOTAL for Windows, we neither know, or care what is
>dedicated to SQL Server.
>> From the following two error messages I read that the sql server process
>> is
>[quoted text clipped - 19 lines]
>> Set as suffering already when under stress, I do not see how lowering Max
>> Server memory will help.
>Because, decreasing the Max Server memory will reduce the amount of used
>memory on the system, thus making more available for other tasks.
>AND SQL SERVER DOES NOT HAVE A WORKING SET! IT IS USING MEMORY IN WINDOWS
>WHERE THE TOTAL DEMAND FOR MEMORY IS THE WORKING SET!
>> Please clarify.
>[quoted text clipped - 107 lines]
>>>> >> >> Any idea what one can do to allow this Reindex to take
>>>> >> >> place?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||Hmm, thank you sir. Three follow up questions:
Have you tried recompiling any procedures to see if it improves performance?
(see sp_recompile in BOL)
Is the drive itself fragmented?
Have you used the Profiler to verify effecient query plans?
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79d74e1593f56@.uwe...
> Before I answer the questions you posed, let me say that it is not just
> reindexing that is cratering the server, but queries in general on the
> server
> itself. These queries would not crater the system (I am assuming) if we
> could
> get the tables they referenced, reindexed. But in trying to reindex, the
> reindex craters the server. So either way, we are up against a wall.
> 1a. Why am I doing a reindex?
> For the reason most people want to reindex, to get the data they are
> asking
> for returned in a timely and efficient manner.
> 1b. What told you you needed it?
> A. The queries themself taking a long time to return results and at times
> degrading overall server performance. Which in turn pointed to index
> fragmentation.
> B. The following query (when we were lucky enough to get results)
> confirmed
> our suspicions that our indexes were greatly fragmented:
> SELECT s.name,
> i.name,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count
> FROM sys.objects s
> JOIN sys.indexes i
> ON s.object_id = i.object_id
> JOIN master.sys.dm_db_index_physical_stats (' + CAST(@.DBID as
> varchar(max)
> ) + ', NULL, NULL , NULL, 'LIMITED') ps
> ON s.object_id = ps.object_id
> AND i.index_id = ps.index_id
> WHERE s.type_desc = 'USER_TABLE'
> AND i.index_id > 0
> AND i.index_id < 255
> AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
> AND ps.Page_Count >= 100
> AND (ps.avg_fragmentation_in_percent >= 5.0
> OR ps.fragment_count >= 50)
> GROUP BY s.name,
> i.name,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count
> 2. Syntax specifically how are you doing it?
> ALTER INDEX ' + @.IndexName + ' ON [' + @.DBName + '].[dbo].[' + @.TableName
> + ']
> REBUILD WITH ( FILLFACTOR = ' + CAST(@.FillFactor AS varchar(3)) + ',
> SORT_IN_TEMPDB = ON,
> STATISTICS_NORECOMPUTE = OFF )
> 3. Why have you completly [sic] ignored the posibility [sic] of using
> indexdefrag, or changing the Recovery model while you reindex?
> I have not ignored the possibility of using indexdefrag. We might give it
> a
> try once we implement new Max/Min Server memory settings. You might say we
> are a bit gun shy of making these changes without understanding all the
> implications, and additionally, since any resource intense operation seems
> to
> greatly degrade performance, I am a bit hesitant in performing an
> indexdefrag,
> too.
> Lastly, the Recovery model on the database is already set to SIMPLE.
> Jay wrote:
>>Before going on (and to continue) I require answers to the following:
>>1) Why are you doing a reindex? What told you you needed it?
>>2) Syntax specifically how are you doing it?
>>3) Why have you completly ignored the posibility of using indexdefrag, or
>>changing the Recovery model while you reindex?
>>Please post both your reasons and the ALTER INDEX statement (you may, of
>>course change the database/table/index names, if you feel those shouldn't
>>be
>>posted).
>> Thanks Jay for the detailed response and the link to RAM, Virtual
>> Memory,
>> and
>> the Paging File.
>> I am still not understanding some things.
>>Did you study the link?
>> So the Working Set is drawing from system memory, and when you say
>> system
>> memory I interpret that as being RAM + Paging File.
>>correct.
>> In decreasing the Max Server memory I therefore decrease the Working Set
>> dedicated to SQL Server.
>>Decreasing Max Server memory will decrease the Working Set. However, since
>>the Working Set is a TOTAL for Windows, we neither know, or care what is
>>dedicated to SQL Server.
>> From the following two error messages I read that the sql server process
>> is
>>[quoted text clipped - 19 lines]
>> Set as suffering already when under stress, I do not see how lowering
>> Max
>> Server memory will help.
>>Because, decreasing the Max Server memory will reduce the amount of used
>>memory on the system, thus making more available for other tasks.
>>AND SQL SERVER DOES NOT HAVE A WORKING SET! IT IS USING MEMORY IN WINDOWS
>>WHERE THE TOTAL DEMAND FOR MEMORY IS THE WORKING SET!
>> Please clarify.
>>[quoted text clipped - 107 lines]
>>>> >> >> Any idea what one can do to allow this Reindex to take
>>>> >> >> place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||Thanks again Jay. Your tolerance on this matter is appreciated. Getting to
your questions:
1. Have you tried recompiling any procedures to see if it improves
performance?
(see sp_recompile in BOL)
No, this has not been attempted. The tables are mostly archive data, and for
now, we mostly compose ad-hoc queries to mine the data when we need it.
Is the drive itself fragmented?
I do not believe it is very fragmented, as the server was newly built as 64
bit, and has only been in operation for 30 days. Our files are set to auto
grow, but they have not grown since they were created, as we grew them to
begin with, with ample room to start.
Have you used the Profiler to verify effecient query plans?
No, I did not put a trace on my reindex attempt, and the other ad-hoc query
that greatly degraded performance I did not either. I would be interested in
the query plan output, but the degredation after converting to 64 bit has me
focusing my time more on understanding the nuances of 64 bit versus 32 bit.
Jay wrote:
>Hmm, thank you sir. Three follow up questions:
>Have you tried recompiling any procedures to see if it improves performance?
>(see sp_recompile in BOL)
>Is the drive itself fragmented?
>Have you used the Profiler to verify effecient query plans?
>> Before I answer the questions you posed, let me say that it is not just
>> reindexing that is cratering the server, but queries in general on the
>[quoted text clipped - 121 lines]
>>>> >> >> Any idea what one can do to allow this Reindex to take
>>>> >> >> place?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||Jay,
You are probably quite put out with my understanding of the Working Set, and
understandably so. My thinking that the SQL Server has it's own Working Set
came from the following blog:
http://blogs.technet.com/askperf/archive/2007/05/18/sql-and-the-working-set.aspx
The second paragraph reads:
"First - let's define what exactly "Working Set" is. The working set of a
program is a collection of those pages in its virtual address space that have
been recently referenced. This includes both shared and private data. The
shared data includes pages that contain all instructions an application
executes, including those in its own DLL's and the system DLL's. As the
working set size increases, memory demand increases. A process has an
associated minimum working set size and maximum working set size. Each time
a process is created, it reserves the minimum working set size for the
process. The virtual memory manager attempts to keep enough memory for the
minimum working set resident when the process is active, but keeps no more
than the maximum size."
The following statements from the above paragraph led me to believe there was
a Working Set for each process, rather than a single Working Set shared by
all processes, which I believe you have stated. Nevertheless, the following
clips from the above paragraph led me to believe that SQL Server has its own
Working Set:
1. "The working set of a program"
2. "Each time a process is created, it reserves the minimum working set size
for the process."
There are other references in the article that support multiple Working Sets,
or a Working Set per process, such as: "If available server memory drops
below 100MB, then the Memory Manager will trim the Working Set of all
processes."
You may slap me alongside the head with a wet fish, if you please. I am just
confused and seeking for better understanding as to how, setting the Min/Max
Memory server settings to the same value and reducing the Max Memory Server
setting will help reduce the following error log entries:
"A significant part of sql server process memory has been paged out. This
may result in a performance degradation. Duration: 655 seconds. Working set
(KB): 1009520, committed (KB): 7169156, memory utilization: 14%."
Thanks again for sharing your knowledge and tolerating my ignorance.
Jay wrote:
>Before going on (and to continue) I require answers to the following:
>1) Why are you doing a reindex? What told you you needed it?
>2) Syntax specifically how are you doing it?
>3) Why have you completly ignored the posibility of using indexdefrag, or
>changing the Recovery model while you reindex?
>Please post both your reasons and the ALTER INDEX statement (you may, of
>course change the database/table/index names, if you feel those shouldn't be
>posted).
>> Thanks Jay for the detailed response and the link to RAM, Virtual Memory,
>> and
>> the Paging File.
>> I am still not understanding some things.
>Did you study the link?
>> So the Working Set is drawing from system memory, and when you say system
>> memory I interpret that as being RAM + Paging File.
>correct.
>> In decreasing the Max Server memory I therefore decrease the Working Set
>> dedicated to SQL Server.
>Decreasing Max Server memory will decrease the Working Set. However, since
>the Working Set is a TOTAL for Windows, we neither know, or care what is
>dedicated to SQL Server.
>> From the following two error messages I read that the sql server process
>> is
>[quoted text clipped - 19 lines]
>> Set as suffering already when under stress, I do not see how lowering Max
>> Server memory will help.
>Because, decreasing the Max Server memory will reduce the amount of used
>memory on the system, thus making more available for other tasks.
>AND SQL SERVER DOES NOT HAVE A WORKING SET! IT IS USING MEMORY IN WINDOWS
>WHERE THE TOTAL DEMAND FOR MEMORY IS THE WORKING SET!
>> Please clarify.
>[quoted text clipped - 107 lines]
>>>> >> >> Any idea what one can do to allow this Reindex to take
>>>> >> >> place?
--
Message posted via http://www.sqlmonster.com|||The quote from the blog is correct and led to a unfortunate
misunderstanding.
In the second paragraph it says:
"As the working set size increases, memory demand increases."
Meaning that it is not a fixed resource, but variable to an outside pool.
Beyond that, it does indeed talk about the working set for seperate
processes, which makes sense. Its just that when speaking about paging and
running out of system memory, it is generally assumed that you're looking at
the total in Windows.
Actually, after reading the link, if I knew about it before hand, I would
have sent it to you as it supports John's suggestion.
However, I'm not so sure that reindexing will solve your problem. I think
it's in the query plan, but not sure exactly why (of if) it is an issue.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79d8a46a06185@.uwe...
> Jay,
> You are probably quite put out with my understanding of the Working Set,
> and
> understandably so. My thinking that the SQL Server has it's own Working
> Set
> came from the following blog:
> http://blogs.technet.com/askperf/archive/2007/05/18/sql-and-the-working-set.aspx
>
> The second paragraph reads:
> "First - let's define what exactly "Working Set" is. The working set of a
> program is a collection of those pages in its virtual address space that
> have
> been recently referenced. This includes both shared and private data.
> The
> shared data includes pages that contain all instructions an application
> executes, including those in its own DLL's and the system DLL's. As the
> working set size increases, memory demand increases. A process has an
> associated minimum working set size and maximum working set size. Each
> time
> a process is created, it reserves the minimum working set size for the
> process. The virtual memory manager attempts to keep enough memory for
> the
> minimum working set resident when the process is active, but keeps no more
> than the maximum size."
> The following statements from the above paragraph led me to believe there
> was
> a Working Set for each process, rather than a single Working Set shared by
> all processes, which I believe you have stated. Nevertheless, the
> following
> clips from the above paragraph led me to believe that SQL Server has its
> own
> Working Set:
> 1. "The working set of a program"
> 2. "Each time a process is created, it reserves the minimum working set
> size
> for the process."
> There are other references in the article that support multiple Working
> Sets,
> or a Working Set per process, such as: "If available server memory drops
> below 100MB, then the Memory Manager will trim the Working Set of all
> processes."
> You may slap me alongside the head with a wet fish, if you please. I am
> just
> confused and seeking for better understanding as to how, setting the
> Min/Max
> Memory server settings to the same value and reducing the Max Memory
> Server
> setting will help reduce the following error log entries:
> "A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 655 seconds. Working
> set
> (KB): 1009520, committed (KB): 7169156, memory utilization: 14%."
> Thanks again for sharing your knowledge and tolerating my ignorance.
>
> Jay wrote:
>>Before going on (and to continue) I require answers to the following:
>>1) Why are you doing a reindex? What told you you needed it?
>>2) Syntax specifically how are you doing it?
>>3) Why have you completly ignored the posibility of using indexdefrag, or
>>changing the Recovery model while you reindex?
>>Please post both your reasons and the ALTER INDEX statement (you may, of
>>course change the database/table/index names, if you feel those shouldn't
>>be
>>posted).
>> Thanks Jay for the detailed response and the link to RAM, Virtual
>> Memory,
>> and
>> the Paging File.
>> I am still not understanding some things.
>>Did you study the link?
>> So the Working Set is drawing from system memory, and when you say
>> system
>> memory I interpret that as being RAM + Paging File.
>>correct.
>> In decreasing the Max Server memory I therefore decrease the Working Set
>> dedicated to SQL Server.
>>Decreasing Max Server memory will decrease the Working Set. However, since
>>the Working Set is a TOTAL for Windows, we neither know, or care what is
>>dedicated to SQL Server.
>> From the following two error messages I read that the sql server process
>> is
>>[quoted text clipped - 19 lines]
>> Set as suffering already when under stress, I do not see how lowering
>> Max
>> Server memory will help.
>>Because, decreasing the Max Server memory will reduce the amount of used
>>memory on the system, thus making more available for other tasks.
>>AND SQL SERVER DOES NOT HAVE A WORKING SET! IT IS USING MEMORY IN WINDOWS
>>WHERE THE TOTAL DEMAND FOR MEMORY IS THE WORKING SET!
>> Please clarify.
>>[quoted text clipped - 107 lines]
>>>> >> >> Any idea what one can do to allow this Reindex to take
>>>> >> >> place?
> --
> Message posted via http://www.sqlmonster.com
>|||Hi cbrichards,
Any movement?
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79bdf69e9af82@.uwe...
>I am running SQL 2005, SP2, 64 bit Standard Edition, with 16 GB RAM. The
>Min
> Server Memory setting is 0, and Max is set at 14 GB.
> When I reindex (ALTER INDEX...REBUILD) a database that is approximately
> 6GB
> in size, the server grinds to a halt and the error log contains errors
> similar to the following:
> A significant part of sql server process memory has been paged out. This
> may result in a performance degradation. Duration: 655 seconds. Working
> set
> (KB): 1009520, committed (KB): 7169156, memory utilization: 14%.
> Reporting Services is also running on this box, but when the Reindex is
> taking place, the Reindex job is the only active SPID.
> The "PF Usage" in Task Manager is at 15.3 GB.
> Any idea what one can do to allow this Reindex to take place?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||Jay,
Check this article out:
918483 How to reduce paging of buffer pool memory in the 64-bit version of
SQL Server 2005
http://support.microsoft.com/default.aspx?scid=kb;EN-US;918483
There are also some drivers that are known to cause this issue, like the the
iLO Management Channel Interface Driver (Cpqcidrv.sys) from Hewlett-Packard
is known to cause this issue on x64 editions of SQL Server 2005.
Also some of the Broadcom drivers , so apart from setting the locked pages
in memory option to on, I would also make sure you are running the latest
drivers for your hardware and software if possible.
HTH
"Jay" <nospan@.nospam.org> wrote in message
news:%23dipTOMFIHA.3716@.TK2MSFTNGP03.phx.gbl...
> Hi cbrichards,
> Any movement?
> "cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
> news:79bdf69e9af82@.uwe...
>>I am running SQL 2005, SP2, 64 bit Standard Edition, with 16 GB RAM. The
>>Min
>> Server Memory setting is 0, and Max is set at 14 GB.
>> When I reindex (ALTER INDEX...REBUILD) a database that is approximately
>> 6GB
>> in size, the server grinds to a halt and the error log contains errors
>> similar to the following:
>> A significant part of sql server process memory has been paged out. This
>> may result in a performance degradation. Duration: 655 seconds. Working
>> set
>> (KB): 1009520, committed (KB): 7169156, memory utilization: 14%.
>> Reporting Services is also running on this box, but when the Reindex is
>> taking place, the Reindex job is the only active SPID.
>> The "PF Usage" in Task Manager is at 15.3 GB.
>> Any idea what one can do to allow this Reindex to take place?
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>

Reindex

How do you keep a system running optimum if you can't run
dbcc reindex because it locks the tables? The indexdefrag
has no perceptible performance increase, or very little
compared to rebuilding the indexes.
The db is about 60G. If we cant run the reindex because
of other jobs running all night that would get killed by
it, the speed the next day is affected, searches take
longer, concurrency gets affected, etc.
MichelIt's hard to believe that indexdefrag has little effect. If one nights
worth of work fragments the tables that badly then you might want to change
the fill factor or possibly rethink the clustered index. How fragmented do
the tables get after just one night of work?
--
Andrew J. Kelly
SQL Server MVP
"Michel" <michel@.micro-world.ca> wrote in message
news:081501c37735$a9c52ef0$a101280a@.phx.gbl...
> How do you keep a system running optimum if you can't run
> dbcc reindex because it locks the tables? The indexdefrag
> has no perceptible performance increase, or very little
> compared to rebuilding the indexes.
> The db is about 60G. If we cant run the reindex because
> of other jobs running all night that would get killed by
> it, the speed the next day is affected, searches take
> longer, concurrency gets affected, etc.
> Michel|||dbcc showcontig shows between 10 - 50% fragmentation on
the tables.
Some has high as 100% (log tables with lots of inserts).
The fill factor is 80% on tables. Its really not that much
new data.
All tables have a decent clustered index. It's just a
matter of one or
2 tables. This DB is fully relational with about 300
related tables.
It just seems that when data is inserted, the index just
dosent work
well anymore until I reindex. I may be missing something?
80% fill
factor on a table with 10M rows, mostly queried all day,
maybe 20-50 new
records added not much more. All the related tables get a
few records
here and there inserted. I can give more details but not
sure wich
details to give to help.
Michel
>--Original Message--
>It's hard to believe that indexdefrag has little
effect. If one nights
>worth of work fragments the tables that badly then you
might want to change
>the fill factor or possibly rethink the clustered
index. How fragmented do
>the tables get after just one night of work?
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Michel" <michel@.micro-world.ca> wrote in message
>news:081501c37735$a9c52ef0$a101280a@.phx.gbl...
>> How do you keep a system running optimum if you can't
run
>> dbcc reindex because it locks the tables? The
indexdefrag
>> has no perceptible performance increase, or very little
>> compared to rebuilding the indexes.
>> The db is about 60G. If we cant run the reindex because
>> of other jobs running all night that would get killed by
>> it, the speed the next day is affected, searches take
>> longer, concurrency gets affected, etc.
>> Michel
>
>.
>

Registry entries not found

I have a Notification Services instance/application running on the same machine that runs the website that manages the Subscribers. The instance is registered and it is running perfectly fine as a Windows service.

However, when running the subscription-management website i get the following error:

"The registry entries for the specified instance of Notification Services could not be found."

Please note, that the website is running on the same machine as the instance (and the SQL Server as well).

Any suggestions on how to solve this problem will be greatly appreciated.

Thanks.

Double check the string you are using to specify the instance name. It should be what you've defined in the following node of the ICF.

<!--Instance name-->
<InstanceName>MyInstance</InstanceName>

It's not the name of the service.

HTH...

Joe|||

Thanks for your reply.

Yes I know that the service name and the instance name is not the same. My instance name is PWNotifications and the name of the service (which is running fine by the way - chronicle tables are updated as scheduled) is NS$PWNotifications.

The error I get is in the ASP.NET code when this statement gets executed:

new Instance("PWNotifications");

It is very likely that there are some security-restrictions of the process running ASP.NET, I haven't looked into that yet, but the research I done so far tells my that this error is not security related - it is simply a matter of the service not being found.

I have also checked the registry - there is plenty of NS$PWNotifications-related nodes in there.

Another thing that might be relevant: The subscription-management part (the website) was originally written for an application running in .NET 1.x and Notification Services running on MS SQL 2000. It has now been converted to .NET 2.0, but otherwise it has not been changed. Is the [new Instance("PWNotifications")] syntax obsolete in the NS 2005 API?

/Henrik

|||

The problem is solved.

I had not updated the Microsoft.SQLServer.NotificationServices.dll used by the webproject from version 2.x to 9.x.

/Henrik

Registration

I have a client that is running SQL 2005 Standard edition and they purchased 10 more Cal's, do I have to enter new registration keys, and if so where do you enter them?

Thanks in advance.

Joe

No, you don't have to enter additional reg keys.|||Thank you

Registering/licensing DEMO SQLserver....

Hello,

I am new to SQLserver and have installed the DEMO version of SQLserver 2000 on my system. I got it up and running and verified that it was working properly.

I then purchased two CPU's of the standard edition for my system. The software has arrived, but all that arrived are the CD's. I see no key, nor do I see a way to provide a key even if I had one.

I do not know how to proceeed and need help to figure out how to obtain a key or how to enter it if I ever obtain one.

Any help is greatly appreciated!!!!!

Thanks,

JamesActually, your CD's are supposed to be accompanied by invoice where corresponding keys are clearly printed out. If this is not the case you fell victim to some SW scam.|||Ask the vendor for such keys, in general MS software CDs comes with CD key printed on the CD or a paper attached to the CD cover.|||No such keys on the CD or paper. We are trying to get paperwork for the licenses. When I do get it how do I put them in? I saw no place to put a license in the system. I looked at length, but it obviously means I was not looking in the right place!

Anyone know how to put the key into the system?

James|||look in adminstrative tools
open licensing
you can add\update your lic there

when you say DEMO version of SQL Server, what do you mean?|||I take it DEMO means eval version, and Licensing service in Control Panel has nothing to do with Product Key, BTW.|||i didnt think that he was talking about a product key
he mentions

No such keys on the CD or paper. We are trying to get paperwork for the licenses. When I do get it how do I put them in? I saw no place to put a license in the system. I looked at length, but it obviously means I was not looking in the right place!

Anyone know how to put the key into the system?


i assume he is discussing lics and then he mentions keys!!!??

i'm linguistically hogtied.|||I think it is I that is not making myself clear due to the fact that I really do not know. I have never done this before, and I am sure it is a very simple question.

We have ordered two CPU licenses for SQL Server and I have not seen anything that looks like a "key" that I would normally associate as a Microsoft license key. Hence, I think we need the paperwork that goes with the licenses from my vendor as the others have mentioned. I am awaiting that now (I hope).

I think the issues came when I asked how to register (license) the product once I get the license keys. I have found no way to actually put those ubiquitous Microsoft keys in (that I am not in possession of at this time) to any software.

To an old unix person it is a mystery.

Thanks again for putting up with my silliness.

James|||Oh, so you're saying that throughout the installation process you did not have to nor were you asked to enter the Product Key?

Can you send me this CD?|||Are those by any chance "SELECT" CD's? If you work for a corporation with volume licensing, your company may get CDs with their registration number "burned in", and pay for licenses based on inventory rather than buying them at retail.

-PatP|||Yup, that's what it sounds like...unless it's an eval, 90 or 120-day.|||I initially loaded the evaluation version that I downloaded off the web. I was calling this the DEMO version in earlier emails, sorry for that mixup.

I was also told that it was a full copy and all I needed to do was apply my licenses when they arrived. That is where the problem is, both not having the license keys, nor knowing how or being able to find out how to apply them when they do arrive.

I am handling getting the keys, but need to know how to apply them when they come.

James|||So, do you have a CD?|||I do now, but I do not see anything that looks like a Microsoft key anywhere on them. Nor did the paperwork have anything like that.|||Did you buy these CDs from a vendor, or requisition them from some kind of "IT Central" within your organization?

-PatP|||If the CD does NOT have anything like "Evaluation...", then uninstall your eval AFTER BACKING UP MASTER, MSDB, and your own DATABASES (!!!), and proceed with normal installation from the CD. If you do run into "Enter Product Key..." situation, contact your SW vendor or whoever you purchased it from, else - keep going. Once installed, you'll need to restore your master (check Books Online) and msdb, along with your user database. Last, but not least - download and apply SP3.|||Heck, I'm paranoid. I remove the TCP/IP, install SQL 2000, install either sp2 or sp3a, then reboot. Once the box has gone down, then I plug the TCP/IP cable back into it.

-PatP|||Answers:

1) These are licenses we pruchased from a vendor we deal with all the time. I actually got two sets of CD's instead of just one, I guess since I ordered two CPU's worth of licenses.

2) We do not have a site license, we are a small organization and this is the first time we are using SQLserver.

Outlook:

So, it looks like I will need to do the thing I didn't want to do which is de-install the evaluation copy and re-install the real one off CD. Of course I will not do this until I get what looks like Microsoft keys (or the like) in my hands.

Thanks for all the help. Hopefully I can learn more about this stuff and help a few others along the way.

James