Another SQL Server 2k Setup failed to configure the server error. Please help

Hi all,
I realised that there are many postings regarding this error, however,
I have tried all possible proposed solutions based on the replies for
the past 24 hours trying to solve this problem, but failed. Frustrating
it is, but I hope you guys can help me with this.
Problem arises when I tried to install SQL Server 2k Developer's
Edition on a Windows XP Professional. I managed to get it installed
until towards the end when it tried to configure the server, it failed
miserably, with a message:
"Setup failed to configure the server. Refer to the server error logs
and sqlstp.log for more information."
Here is the snippet of the error log file (sqlstp.log)
10:32:59 C:\Program Files\Microsoft SQL
Server\80\Tools\Binn\cnfgsvr.exe -F "C:\WINDOWS\sqlstp.log" -I
MSSQLSERVER -V 1 -M 0 -Q "Latin1_General_CI_AS" -H 2360174 -U sa -P
###############################################################################
Starting Service ...
Latin1_General_CI_AS
-m -Q -T4022 -T3659
Connecting to Server ...
driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified
driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified
driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified
SQL Server configuration failed.
###############################################################################
Please Help. Thanks.
Cheers
AlvinHave you read this article?
http://support.microsoft.com/kb/306345/EN-US/
Is it given you an idea?
"alvinyyt" <alvinyyt@.gmail.com> wrote in message
news:1154481632.633873.163050@.m79g2000cwm.googlegroups.com...
> Hi all,
> I realised that there are many postings regarding this error, however,
> I have tried all possible proposed solutions based on the replies for
> the past 24 hours trying to solve this problem, but failed. Frustrating
> it is, but I hope you guys can help me with this.
> Problem arises when I tried to install SQL Server 2k Developer's
> Edition on a Windows XP Professional. I managed to get it installed
> until towards the end when it tried to configure the server, it failed
> miserably, with a message:
> "Setup failed to configure the server. Refer to the server error logs
> and sqlstp.log for more information."
> Here is the snippet of the error log file (sqlstp.log)
> 10:32:59 C:\Program Files\Microsoft SQL
> Server\80\Tools\Binn\cnfgsvr.exe -F "C:\WINDOWS\sqlstp.log" -I
> MSSQLSERVER -V 1 -M 0 -Q "Latin1_General_CI_AS" -H 2360174 -U sa -P
> ###############################################################################
> Starting Service ...
> Latin1_General_CI_AS
> -m -Q -T4022 -T3659
> Connecting to Server ...
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> SQL Server configuration failed.
> ###############################################################################
> Please Help. Thanks.
> Cheers
> Alvin
>|||Hi Alvin,
It sounds to me that you may have a problem with your MDAC installation
which is causing SQLserver not to boot correctly.
I would suggest you try to either reinstall the current version of MDAC on
you server or install the latest version.
You can obtain a utility from MS (http://support.microsoft.com/kb/301202/)
to determine your current MDAC version.
Then try to perform the installation again.
Not sure of previous suggestions, so apologise if I'm susggesting something
you may have already tried?
Regards
Immy
"alvinyyt" <alvinyyt@.gmail.com> wrote in message
news:1154481632.633873.163050@.m79g2000cwm.googlegroups.com...
> Hi all,
> I realised that there are many postings regarding this error, however,
> I have tried all possible proposed solutions based on the replies for
> the past 24 hours trying to solve this problem, but failed. Frustrating
> it is, but I hope you guys can help me with this.
> Problem arises when I tried to install SQL Server 2k Developer's
> Edition on a Windows XP Professional. I managed to get it installed
> until towards the end when it tried to configure the server, it failed
> miserably, with a message:
> "Setup failed to configure the server. Refer to the server error logs
> and sqlstp.log for more information."
> Here is the snippet of the error log file (sqlstp.log)
> 10:32:59 C:\Program Files\Microsoft SQL
> Server\80\Tools\Binn\cnfgsvr.exe -F "C:\WINDOWS\sqlstp.log" -I
> MSSQLSERVER -V 1 -M 0 -Q "Latin1_General_CI_AS" -H 2360174 -U sa -P
> ###############################################################################
> Starting Service ...
> Latin1_General_CI_AS
> -m -Q -T4022 -T3659
> Connecting to Server ...
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> SQL Server configuration failed.
> ###############################################################################
> Please Help. Thanks.
> Cheers
> Alvin
>|||Hi
Thanks for responding to my queries, greatly appreciated. However, I
have tried installing MDAC separately and yet it still failed when
reinstalling SQL Server 2000. I have sorta given up on this problem,
but tried again on another machine and it worked fine. So again I don't
know what was wrong with the settings in the first PC. No resolution =(
Cheers
Alvin
Immy wrote:
> Hi Alvin,
> It sounds to me that you may have a problem with your MDAC installation
> which is causing SQLserver not to boot correctly.
> I would suggest you try to either reinstall the current version of MDAC on
> you server or install the latest version.
> You can obtain a utility from MS (http://support.microsoft.com/kb/301202/)
> to determine your current MDAC version.
> Then try to perform the installation again.
> Not sure of previous suggestions, so apologise if I'm susggesting something
> you may have already tried?
> Regards
> Immy
>
> "alvinyyt" <alvinyyt@.gmail.com> wrote in message
> news:1154481632.633873.163050@.m79g2000cwm.googlegroups.com...
> > Hi all,
> >
> > I realised that there are many postings regarding this error, however,
> > I have tried all possible proposed solutions based on the replies for
> > the past 24 hours trying to solve this problem, but failed. Frustrating
> > it is, but I hope you guys can help me with this.
> >
> > Problem arises when I tried to install SQL Server 2k Developer's
> > Edition on a Windows XP Professional. I managed to get it installed
> > until towards the end when it tried to configure the server, it failed
> > miserably, with a message:
> >
> > "Setup failed to configure the server. Refer to the server error logs
> > and sqlstp.log for more information."
> >
> > Here is the snippet of the error log file (sqlstp.log)
> >
> > 10:32:59 C:\Program Files\Microsoft SQL
> > Server\80\Tools\Binn\cnfgsvr.exe -F "C:\WINDOWS\sqlstp.log" -I
> > MSSQLSERVER -V 1 -M 0 -Q "Latin1_General_CI_AS" -H 2360174 -U sa -P
> > ###############################################################################
> >
> > Starting Service ...
> > Latin1_General_CI_AS
> > -m -Q -T4022 -T3659
> > Connecting to Server ...
> > driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> > [Microsoft][ODBC Driver Manager] Data source name not found and no
> > default driver specified
> > driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> > [Microsoft][ODBC Driver Manager] Data source name not found and no
> > default driver specified
> > driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> > [Microsoft][ODBC Driver Manager] Data source name not found and no
> > default driver specified
> > SQL Server configuration failed.
> >
> > ###############################################################################
> >
> > Please Help. Thanks.
> >
> > Cheers
> >
> > Alvin
> >

Another SQL Server 2k Setup failed to configure the server error. Please help

Hi all,
I realised that there are many postings regarding this error, however,
I have tried all possible proposed solutions based on the replies for
the past 24 hours trying to solve this problem, but failed. Frustrating
it is, but I hope you guys can help me with this.
Problem arises when I tried to install SQL Server 2k Developer's
Edition on a Windows XP Professional. I managed to get it installed
until towards the end when it tried to configure the server, it failed
miserably, with a message:
"Setup failed to configure the server. Refer to the server error logs
and sqlstp.log for more information."
Here is the snippet of the error log file (sqlstp.log)
10:32:59 C:\Program Files\Microsoft SQL
Server\80\Tools\Binn\cnfgsvr.exe -F "C:\WINDOWS\sqlstp.log" -I
MSSQLSERVER -V 1 -M 0 -Q "Latin1_General_CI_AS" -H 2360174 -U sa -P
########################################
####################################
###
Starting Service ...
Latin1_General_CI_AS
-m -Q -T4022 -T3659
Connecting to Server ...
driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified
driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified
driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified
SQL Server configuration failed.
########################################
####################################
###
Please Help. Thanks.
Cheers
AlvinHave you read this article?
http://support.microsoft.com/kb/306345/EN-US/
Is it given you an idea?
"alvinyyt" <alvinyyt@.gmail.com> wrote in message
news:1154481632.633873.163050@.m79g2000cwm.googlegroups.com...
> Hi all,
> I realised that there are many postings regarding this error, however,
> I have tried all possible proposed solutions based on the replies for
> the past 24 hours trying to solve this problem, but failed. Frustrating
> it is, but I hope you guys can help me with this.
> Problem arises when I tried to install SQL Server 2k Developer's
> Edition on a Windows XP Professional. I managed to get it installed
> until towards the end when it tried to configure the server, it failed
> miserably, with a message:
> "Setup failed to configure the server. Refer to the server error logs
> and sqlstp.log for more information."
> Here is the snippet of the error log file (sqlstp.log)
> 10:32:59 C:\Program Files\Microsoft SQL
> Server\80\Tools\Binn\cnfgsvr.exe -F "C:\WINDOWS\sqlstp.log" -I
> MSSQLSERVER -V 1 -M 0 -Q "Latin1_General_CI_AS" -H 2360174 -U sa -P
> ########################################
##################################
#####
> Starting Service ...
> Latin1_General_CI_AS
> -m -Q -T4022 -T3659
> Connecting to Server ...
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> SQL Server configuration failed.
> ########################################
##################################
#####
> Please Help. Thanks.
> Cheers
> Alvin
>|||Hi Alvin,
It sounds to me that you may have a problem with your MDAC installation
which is causing SQLserver not to boot correctly.
I would suggest you try to either reinstall the current version of MDAC on
you server or install the latest version.
You can obtain a utility from MS (http://support.microsoft.com/kb/301202/)
to determine your current MDAC version.
Then try to perform the installation again.
Not sure of previous suggestions, so apologise if I'm susggesting something
you may have already tried?
Regards
Immy
"alvinyyt" <alvinyyt@.gmail.com> wrote in message
news:1154481632.633873.163050@.m79g2000cwm.googlegroups.com...
> Hi all,
> I realised that there are many postings regarding this error, however,
> I have tried all possible proposed solutions based on the replies for
> the past 24 hours trying to solve this problem, but failed. Frustrating
> it is, but I hope you guys can help me with this.
> Problem arises when I tried to install SQL Server 2k Developer's
> Edition on a Windows XP Professional. I managed to get it installed
> until towards the end when it tried to configure the server, it failed
> miserably, with a message:
> "Setup failed to configure the server. Refer to the server error logs
> and sqlstp.log for more information."
> Here is the snippet of the error log file (sqlstp.log)
> 10:32:59 C:\Program Files\Microsoft SQL
> Server\80\Tools\Binn\cnfgsvr.exe -F "C:\WINDOWS\sqlstp.log" -I
> MSSQLSERVER -V 1 -M 0 -Q "Latin1_General_CI_AS" -H 2360174 -U sa -P
> ########################################
##################################
#####
> Starting Service ...
> Latin1_General_CI_AS
> -m -Q -T4022 -T3659
> Connecting to Server ...
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> SQL Server configuration failed.
> ########################################
##################################
#####
> Please Help. Thanks.
> Cheers
> Alvin
>|||Hi
Thanks for responding to my queries, greatly appreciated. However, I
have tried installing MDAC separately and yet it still failed when
reinstalling SQL Server 2000. I have sorta given up on this problem,
but tried again on another machine and it worked fine. So again I don't
know what was wrong with the settings in the first PC. No resolution =(
Cheers
Alvin
Immy wrote:[vbcol=seagreen]
> Hi Alvin,
> It sounds to me that you may have a problem with your MDAC installation
> which is causing SQLserver not to boot correctly.
> I would suggest you try to either reinstall the current version of MDAC on
> you server or install the latest version.
> You can obtain a utility from MS (http://support.microsoft.com/kb/301202/)
> to determine your current MDAC version.
> Then try to perform the installation again.
> Not sure of previous suggestions, so apologise if I'm susggesting somethin
g
> you may have already tried?
> Regards
> Immy
>
> "alvinyyt" <alvinyyt@.gmail.com> wrote in message
> news:1154481632.633873.163050@.m79g2000cwm.googlegroups.com...

Another question regarding licensing

Hi all,
I read different statements below and now I'm totally confused. My case is
as following: We want install SQLServer 2000 Standard Edition on a single
processor server. Our application Saperion is the only application which uses
that SQL instance. For Saperion we have a concurrent licensing model. That
means we have defined about 40 user who could use these applicaton, but at
the same time only 20 user have access. So, in maximum 20 user can work with
Saperion with SQL as the database engine.
Now the question is what kind of license do I have to buy. I understand the
processor licensing model, but the device CAL's and User CAL's not!
Thank you for your kind help in this matter.
Regards,
KE
User and device CAL licensing is based on total numbers rather than
concurrent numbers. On the information given it seems you would need 40 user
CALs if you chose the User CAL model. Device CALs are licensed per end-user
device so if you have multiple users sharing a workstation for example then
device CALs may be more cost effective than user CALs.
That's my understanding, but don't take my word for it. Ask a Microsoft
representative if you want a definitive answer.
Lots of info on licensing here:
http://www.microsoft.com/sql/howtobuy/default.asp
You may find the FAQ helpful.
David Portas
SQL Server MVP
|||Hi David,
Thank you very much for your answer. 40 user CAL's are not logical for me,
because Saperion never allows us to have more than 20 users online. That
means maximum 20 users can use the SQL server at a certain time. Logical for
me would be 20 user CAL's. If I'm wrong, tell me why......
Best regards,
KE
"David Portas" wrote:

> User and device CAL licensing is based on total numbers rather than
> concurrent numbers. On the information given it seems you would need 40 user
> CALs if you chose the User CAL model. Device CALs are licensed per end-user
> device so if you have multiple users sharing a workstation for example then
> device CALs may be more cost effective than user CALs.
> That's my understanding, but don't take my word for it. Ask a Microsoft
> representative if you want a definitive answer.
> Lots of info on licensing here:
> http://www.microsoft.com/sql/howtobuy/default.asp
> You may find the FAQ helpful.
> --
> David Portas
> SQL Server MVP
> --
>
|||SQL Server isn't licensed per CONCURRENT user. The fact that Saperion is
licensed that way wouldn't affect SQL Server licensing.
A user licence grants A USER a licence to use the software so one licence is
required PER USER not per connection or connected user.
David Portas
SQL Server MVP
|||Hi David,
Thank you very much for your explanation. In my point of view the SQL
licensing NOT per CONCURRENT user is very strange. Logical for me would be
only the CONCURRENT approach. However MS can do what they want!?!?!?
I have following additional question. Do we need for each SQL server
installation CAL's for all possible user (40 in our case)?
Example 1:
2 SQL Server Standard with user CAL's, 40 user can work with applications,
which use these servers.
Do we need 2 server licenses and 40 CAL's or 80 CAL's?
Example 2:
1 SQL Server Standard with processor license, 1 SQL Server Standard with
user CAL's.
According to MS homepage for the SQL Server with processor license I do not
need any CAL's. Is that correct? If yes, I assume that we need 1 server
license and 40 CAL's for the second SQL installation?
Thank you very much in advance.
Regards,
KE
"David Portas" wrote:

> SQL Server isn't licensed per CONCURRENT user. The fact that Saperion is
> licensed that way wouldn't affect SQL Server licensing.
> A user licence grants A USER a licence to use the software so one licence is
> required PER USER not per connection or connected user.
> --
> David Portas
> SQL Server MVP
> --
>
|||Inline below:

> I have following additional question. Do we need for each SQL server
> installation CAL's for all possible user (40 in our case)?
> Example 1:
> 2 SQL Server Standard with user CAL's, 40 user can work with applications,
> which use these servers.
> Do we need 2 server licenses and 40 CAL's or 80 CAL's?
2 server licenses. 40 CAL's. You can look at it like each user has a piece of paper that gives
him/her the right to connect to SQL Servers.

> Example 2:
> 1 SQL Server Standard with processor license, 1 SQL Server Standard with
> user CAL's.
> According to MS homepage for the SQL Server with processor license I do not
> need any CAL's. Is that correct? If yes, I assume that we need 1 server
> license and 40 CAL's for the second SQL installation?
Correct.
FYI: To speak to someone regarding licensing:
You can call 1-800-426-9400 (select option 4), Monday through Friday, 6:00
A.M. to 6:00 P.M. (PST) to speak directly to a Microsoft licensing
specialist for licensing problem. Worldwide customers can use the Guide to
Worldwide Microsoft Licensing Sites
http://www.microsoft.com/licensing/index/worldwide.asp to find contact
information in their locations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KE" <KE@.discussions.microsoft.com> wrote in message
news:7D9A468D-FB00-4D72-AD37-2324CF8C84AF@.microsoft.com...[vbcol=seagreen]
> Hi David,
> Thank you very much for your explanation. In my point of view the SQL
> licensing NOT per CONCURRENT user is very strange. Logical for me would be
> only the CONCURRENT approach. However MS can do what they want!?!?!?
> I have following additional question. Do we need for each SQL server
> installation CAL's for all possible user (40 in our case)?
> Example 1:
> 2 SQL Server Standard with user CAL's, 40 user can work with applications,
> which use these servers.
> Do we need 2 server licenses and 40 CAL's or 80 CAL's?
> Example 2:
> 1 SQL Server Standard with processor license, 1 SQL Server Standard with
> user CAL's.
> According to MS homepage for the SQL Server with processor license I do not
> need any CAL's. Is that correct? If yes, I assume that we need 1 server
> license and 40 CAL's for the second SQL installation?
> Thank you very much in advance.
> Regards,
> KE
>
>
>
> "David Portas" wrote:

Andrew J. Kelly please

Since you are so helpful, here is another question regarding my original
post yesterday:
************************************************** ******************
My database has one data file only, and the data file was 900M of data after
I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space so
it does not have to expand in a while; I ran DBCC
DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
clustered index). I was expecting the database to expand 900 X 10% = 90M.
However, when it completed, the data file became 1.9G in total. When I view
the database from EM (taskpad view), looks like 50% free space. Why? Thanks.
************************************************** ******************
Question:
I actually have 100+ such databases on the server and it is running out of
space. I would like to somehow get some free space back to OS (leaving
10-20% of free space in data files instead of 50% right now). And we also
want to re-org data files as we have lots of insert/delete/updates going on
daily. Since DBCC DBREINDEX will not release free space, what are my
options? I don't care about log file growth as they are in seperate RAID
with plenty of spaces. Thank you very much.
John
Im not Andrew Kelly, but up until he replies, take a loot at DBCC Shrinkfile
and have your data files in there as parameters.
"john" <john@.aic.com> wrote in message
news:un9R5ZabEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Since you are so helpful, here is another question regarding my original
> post yesterday:
> ************************************************** ******************
> My database has one data file only, and the data file was 900M of data
after
> I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space
so
> it does not have to expand in a while; I ran DBCC
> DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
> clustered index). I was expecting the database to expand 900 X 10% = 90M.
> However, when it completed, the data file became 1.9G in total. When I
view
> the database from EM (taskpad view), looks like 50% free space. Why?
Thanks.
> ************************************************** ******************
> Question:
> I actually have 100+ such databases on the server and it is running out of
> space. I would like to somehow get some free space back to OS (leaving
> 10-20% of free space in data files instead of 50% right now). And we also
> want to re-org data files as we have lots of insert/delete/updates going
on
> daily. Since DBCC DBREINDEX will not release free space, what are my
> options? I don't care about log file growth as they are in seperate RAID
> with plenty of spaces. Thank you very much.
> John
>
>
|||Well to shrink only the data file you should use DBCC SHRINKFILE and specify
the appropriate parameters. But as noted in the earlier posts this will
undoubtedly fragment your existing tables again. If you shrink it to only
have 10 - 20% free space when you run DBREINDEX it will most likely expand
again. Remember the rule is 1.2 times the size of the object in free space.
Ideally you want that free space to be one big contiguous space on the drive
but it is unlikely to be that way with minimal free space all the time.
Even with 50% free it is most likely scattered and interleaved amongst all
the objects throughout the file. So the less free space you have the more
likely the newly defragmented object will still be interleaved amongst the
other objects but you will cut down on Logical fragmentation. If you have a
lot of Inserts and Updates that cause page splits and fragmentation you
should consider lowering the fill factor to minimize this effect and
increase the time between reindexing. What your asking for is your cake and
the ability to eat it too<g>. Having lots of free space in lots of DB's
does add up to lots of disk space and I can see why you want to cut it down.
But the less free space you have the more likely you will eventually wind up
with no room on the disk when you need to reindex and the file is forced to
grow. The real answer is not what anyone wants to hear and that is to get
more disk space. But in the mean time you can do one of several things to
see if it works for you. One is to only reindex your crucial tables or ones
that have high fragmentation to begin with vs. all the tables. You can also
try using DBCC INDEXDEFRAG instead of REINDEX.
Andrew J. Kelly SQL MVP
"john" <john@.aic.com> wrote in message
news:un9R5ZabEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Since you are so helpful, here is another question regarding my original
> post yesterday:
> ************************************************** ******************
> My database has one data file only, and the data file was 900M of data
after
> I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space
so
> it does not have to expand in a while; I ran DBCC
> DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
> clustered index). I was expecting the database to expand 900 X 10% = 90M.
> However, when it completed, the data file became 1.9G in total. When I
view
> the database from EM (taskpad view), looks like 50% free space. Why?
Thanks.
> ************************************************** ******************
> Question:
> I actually have 100+ such databases on the server and it is running out of
> space. I would like to somehow get some free space back to OS (leaving
> 10-20% of free space in data files instead of 50% right now). And we also
> want to re-org data files as we have lots of insert/delete/updates going
on
> daily. Since DBCC DBREINDEX will not release free space, what are my
> options? I don't care about log file growth as they are in seperate RAID
> with plenty of spaces. Thank you very much.
> John
>
>
|||Thank you very much. Really appreciated.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ekL2m$cbEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Well to shrink only the data file you should use DBCC SHRINKFILE and
specify
> the appropriate parameters. But as noted in the earlier posts this will
> undoubtedly fragment your existing tables again. If you shrink it to only
> have 10 - 20% free space when you run DBREINDEX it will most likely expand
> again. Remember the rule is 1.2 times the size of the object in free
space.
> Ideally you want that free space to be one big contiguous space on the
drive
> but it is unlikely to be that way with minimal free space all the time.
> Even with 50% free it is most likely scattered and interleaved amongst all
> the objects throughout the file. So the less free space you have the more
> likely the newly defragmented object will still be interleaved amongst the
> other objects but you will cut down on Logical fragmentation. If you have
a
> lot of Inserts and Updates that cause page splits and fragmentation you
> should consider lowering the fill factor to minimize this effect and
> increase the time between reindexing. What your asking for is your cake
and
> the ability to eat it too<g>. Having lots of free space in lots of DB's
> does add up to lots of disk space and I can see why you want to cut it
down.
> But the less free space you have the more likely you will eventually wind
up
> with no room on the disk when you need to reindex and the file is forced
to
> grow. The real answer is not what anyone wants to hear and that is to get
> more disk space. But in the mean time you can do one of several things to
> see if it works for you. One is to only reindex your crucial tables or
ones
> that have high fragmentation to begin with vs. all the tables. You can
also[vbcol=seagreen]
> try using DBCC INDEXDEFRAG instead of REINDEX.
> --
> Andrew J. Kelly SQL MVP
>
> "john" <john@.aic.com> wrote in message
> news:un9R5ZabEHA.3752@.TK2MSFTNGP12.phx.gbl...
> after
space[vbcol=seagreen]
> so
90M.[vbcol=seagreen]
> view
> Thanks.
of[vbcol=seagreen]
also
> on
>

Andrew J. Kelly please

Since you are so helpful, here is another question regarding my original
post yesterday:
********************************************************************
My database has one data file only, and the data file was 900M of data after
I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space so
it does not have to expand in a while; I ran DBCC
DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
clustered index). I was expecting the database to expand 900 X 10% = 90M.
However, when it completed, the data file became 1.9G in total. When I view
the database from EM (taskpad view), looks like 50% free space. Why? Thanks.
********************************************************************
Question:
I actually have 100+ such databases on the server and it is running out of
space. I would like to somehow get some free space back to OS (leaving
10-20% of free space in data files instead of 50% right now). And we also
want to re-org data files as we have lots of insert/delete/updates going on
daily. Since DBCC DBREINDEX will not release free space, what are my
options? I don't care about log file growth as they are in seperate RAID
with plenty of spaces. Thank you very much.
JohnIm not Andrew Kelly, but up until he replies, take a loot at DBCC Shrinkfile
and have your data files in there as parameters.
"john" <john@.aic.com> wrote in message
news:un9R5ZabEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Since you are so helpful, here is another question regarding my original
> post yesterday:
> ********************************************************************
> My database has one data file only, and the data file was 900M of data
after
> I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space
so
> it does not have to expand in a while; I ran DBCC
> DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
> clustered index). I was expecting the database to expand 900 X 10% = 90M.
> However, when it completed, the data file became 1.9G in total. When I
view
> the database from EM (taskpad view), looks like 50% free space. Why?
Thanks.
> ********************************************************************
> Question:
> I actually have 100+ such databases on the server and it is running out of
> space. I would like to somehow get some free space back to OS (leaving
> 10-20% of free space in data files instead of 50% right now). And we also
> want to re-org data files as we have lots of insert/delete/updates going
on
> daily. Since DBCC DBREINDEX will not release free space, what are my
> options? I don't care about log file growth as they are in seperate RAID
> with plenty of spaces. Thank you very much.
> John
>
>|||Well to shrink only the data file you should use DBCC SHRINKFILE and specify
the appropriate parameters. But as noted in the earlier posts this will
undoubtedly fragment your existing tables again. If you shrink it to only
have 10 - 20% free space when you run DBREINDEX it will most likely expand
again. Remember the rule is 1.2 times the size of the object in free space.
Ideally you want that free space to be one big contiguous space on the drive
but it is unlikely to be that way with minimal free space all the time.
Even with 50% free it is most likely scattered and interleaved amongst all
the objects throughout the file. So the less free space you have the more
likely the newly defragmented object will still be interleaved amongst the
other objects but you will cut down on Logical fragmentation. If you have a
lot of Inserts and Updates that cause page splits and fragmentation you
should consider lowering the fill factor to minimize this effect and
increase the time between reindexing. What your asking for is your cake and
the ability to eat it too<g>. Having lots of free space in lots of DB's
does add up to lots of disk space and I can see why you want to cut it down.
But the less free space you have the more likely you will eventually wind up
with no room on the disk when you need to reindex and the file is forced to
grow. The real answer is not what anyone wants to hear and that is to get
more disk space. But in the mean time you can do one of several things to
see if it works for you. One is to only reindex your crucial tables or ones
that have high fragmentation to begin with vs. all the tables. You can also
try using DBCC INDEXDEFRAG instead of REINDEX.
--
Andrew J. Kelly SQL MVP
"john" <john@.aic.com> wrote in message
news:un9R5ZabEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Since you are so helpful, here is another question regarding my original
> post yesterday:
> ********************************************************************
> My database has one data file only, and the data file was 900M of data
after
> I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space
so
> it does not have to expand in a while; I ran DBCC
> DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
> clustered index). I was expecting the database to expand 900 X 10% = 90M.
> However, when it completed, the data file became 1.9G in total. When I
view
> the database from EM (taskpad view), looks like 50% free space. Why?
Thanks.
> ********************************************************************
> Question:
> I actually have 100+ such databases on the server and it is running out of
> space. I would like to somehow get some free space back to OS (leaving
> 10-20% of free space in data files instead of 50% right now). And we also
> want to re-org data files as we have lots of insert/delete/updates going
on
> daily. Since DBCC DBREINDEX will not release free space, what are my
> options? I don't care about log file growth as they are in seperate RAID
> with plenty of spaces. Thank you very much.
> John
>
>|||Thank you very much. Really appreciated.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ekL2m$cbEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Well to shrink only the data file you should use DBCC SHRINKFILE and
specify
> the appropriate parameters. But as noted in the earlier posts this will
> undoubtedly fragment your existing tables again. If you shrink it to only
> have 10 - 20% free space when you run DBREINDEX it will most likely expand
> again. Remember the rule is 1.2 times the size of the object in free
space.
> Ideally you want that free space to be one big contiguous space on the
drive
> but it is unlikely to be that way with minimal free space all the time.
> Even with 50% free it is most likely scattered and interleaved amongst all
> the objects throughout the file. So the less free space you have the more
> likely the newly defragmented object will still be interleaved amongst the
> other objects but you will cut down on Logical fragmentation. If you have
a
> lot of Inserts and Updates that cause page splits and fragmentation you
> should consider lowering the fill factor to minimize this effect and
> increase the time between reindexing. What your asking for is your cake
and
> the ability to eat it too<g>. Having lots of free space in lots of DB's
> does add up to lots of disk space and I can see why you want to cut it
down.
> But the less free space you have the more likely you will eventually wind
up
> with no room on the disk when you need to reindex and the file is forced
to
> grow. The real answer is not what anyone wants to hear and that is to get
> more disk space. But in the mean time you can do one of several things to
> see if it works for you. One is to only reindex your crucial tables or
ones
> that have high fragmentation to begin with vs. all the tables. You can
also
> try using DBCC INDEXDEFRAG instead of REINDEX.
> --
> Andrew J. Kelly SQL MVP
>
> "john" <john@.aic.com> wrote in message
> news:un9R5ZabEHA.3752@.TK2MSFTNGP12.phx.gbl...
> > Since you are so helpful, here is another question regarding my original
> > post yesterday:
> >
> > ********************************************************************
> > My database has one data file only, and the data file was 900M of data
> after
> > I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free
space
> so
> > it does not have to expand in a while; I ran DBCC
> > DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
> > clustered index). I was expecting the database to expand 900 X 10% =90M.
> > However, when it completed, the data file became 1.9G in total. When I
> view
> > the database from EM (taskpad view), looks like 50% free space. Why?
> Thanks.
> > ********************************************************************
> >
> > Question:
> >
> > I actually have 100+ such databases on the server and it is running out
of
> > space. I would like to somehow get some free space back to OS (leaving
> > 10-20% of free space in data files instead of 50% right now). And we
also
> > want to re-org data files as we have lots of insert/delete/updates going
> on
> > daily. Since DBCC DBREINDEX will not release free space, what are my
> > options? I don't care about log file growth as they are in seperate RAID
> > with plenty of spaces. Thank you very much.
> >
> > John
> >
> >
> >
> >
>

Andrew J. Kelly please

Since you are so helpful, here is another question regarding my original
post yesterday:
****************************************
****************************
My database has one data file only, and the data file was 900M of data after
I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space so
it does not have to expand in a while; I ran DBCC
DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
clustered index). I was expecting the database to expand 900 X 10% = 90M.
However, when it completed, the data file became 1.9G in total. When I view
the database from EM (taskpad view), looks like 50% free space. Why? Thanks.
****************************************
****************************
Question:
I actually have 100+ such databases on the server and it is running out of
space. I would like to somehow get some free space back to OS (leaving
10-20% of free space in data files instead of 50% right now). And we also
want to re-org data files as we have lots of insert/delete/updates going on
daily. Since DBCC DBREINDEX will not release free space, what are my
options? I don't care about log file growth as they are in seperate RAID
with plenty of spaces. Thank you very much.
JohnIm not Andrew Kelly, but up until he replies, take a loot at DBCC Shrinkfile
and have your data files in there as parameters.
"john" <john@.aic.com> wrote in message
news:un9R5ZabEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Since you are so helpful, here is another question regarding my original
> post yesterday:
> ****************************************
****************************
> My database has one data file only, and the data file was 900M of data
after
> I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space
so
> it does not have to expand in a while; I ran DBCC
> DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
> clustered index). I was expecting the database to expand 900 X 10% = 90M.
> However, when it completed, the data file became 1.9G in total. When I
view
> the database from EM (taskpad view), looks like 50% free space. Why?
Thanks.
> ****************************************
****************************
> Question:
> I actually have 100+ such databases on the server and it is running out of
> space. I would like to somehow get some free space back to OS (leaving
> 10-20% of free space in data files instead of 50% right now). And we also
> want to re-org data files as we have lots of insert/delete/updates going
on
> daily. Since DBCC DBREINDEX will not release free space, what are my
> options? I don't care about log file growth as they are in seperate RAID
> with plenty of spaces. Thank you very much.
> John
>
>|||Well to shrink only the data file you should use DBCC SHRINKFILE and specify
the appropriate parameters. But as noted in the earlier posts this will
undoubtedly fragment your existing tables again. If you shrink it to only
have 10 - 20% free space when you run DBREINDEX it will most likely expand
again. Remember the rule is 1.2 times the size of the object in free space.
Ideally you want that free space to be one big contiguous space on the drive
but it is unlikely to be that way with minimal free space all the time.
Even with 50% free it is most likely scattered and interleaved amongst all
the objects throughout the file. So the less free space you have the more
likely the newly defragmented object will still be interleaved amongst the
other objects but you will cut down on Logical fragmentation. If you have a
lot of Inserts and Updates that cause page splits and fragmentation you
should consider lowering the fill factor to minimize this effect and
increase the time between reindexing. What your asking for is your cake and
the ability to eat it too<g>. Having lots of free space in lots of DB's
does add up to lots of disk space and I can see why you want to cut it down.
But the less free space you have the more likely you will eventually wind up
with no room on the disk when you need to reindex and the file is forced to
grow. The real answer is not what anyone wants to hear and that is to get
more disk space. But in the mean time you can do one of several things to
see if it works for you. One is to only reindex your crucial tables or ones
that have high fragmentation to begin with vs. all the tables. You can also
try using DBCC INDEXDEFRAG instead of REINDEX.
Andrew J. Kelly SQL MVP
"john" <john@.aic.com> wrote in message
news:un9R5ZabEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Since you are so helpful, here is another question regarding my original
> post yesterday:
> ****************************************
****************************
> My database has one data file only, and the data file was 900M of data
after
> I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space
so
> it does not have to expand in a while; I ran DBCC
> DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
> clustered index). I was expecting the database to expand 900 X 10% = 90M.
> However, when it completed, the data file became 1.9G in total. When I
view
> the database from EM (taskpad view), looks like 50% free space. Why?
Thanks.
> ****************************************
****************************
> Question:
> I actually have 100+ such databases on the server and it is running out of
> space. I would like to somehow get some free space back to OS (leaving
> 10-20% of free space in data files instead of 50% right now). And we also
> want to re-org data files as we have lots of insert/delete/updates going
on
> daily. Since DBCC DBREINDEX will not release free space, what are my
> options? I don't care about log file growth as they are in seperate RAID
> with plenty of spaces. Thank you very much.
> John
>
>|||Thank you very much. Really appreciated.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ekL2m$cbEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Well to shrink only the data file you should use DBCC SHRINKFILE and
specify
> the appropriate parameters. But as noted in the earlier posts this will
> undoubtedly fragment your existing tables again. If you shrink it to only
> have 10 - 20% free space when you run DBREINDEX it will most likely expand
> again. Remember the rule is 1.2 times the size of the object in free
space.
> Ideally you want that free space to be one big contiguous space on the
drive
> but it is unlikely to be that way with minimal free space all the time.
> Even with 50% free it is most likely scattered and interleaved amongst all
> the objects throughout the file. So the less free space you have the more
> likely the newly defragmented object will still be interleaved amongst the
> other objects but you will cut down on Logical fragmentation. If you have
a
> lot of Inserts and Updates that cause page splits and fragmentation you
> should consider lowering the fill factor to minimize this effect and
> increase the time between reindexing. What your asking for is your cake
and
> the ability to eat it too<g>. Having lots of free space in lots of DB's
> does add up to lots of disk space and I can see why you want to cut it
down.
> But the less free space you have the more likely you will eventually wind
up
> with no room on the disk when you need to reindex and the file is forced
to
> grow. The real answer is not what anyone wants to hear and that is to get
> more disk space. But in the mean time you can do one of several things to
> see if it works for you. One is to only reindex your crucial tables or
ones
> that have high fragmentation to begin with vs. all the tables. You can
also
> try using DBCC INDEXDEFRAG instead of REINDEX.
> --
> Andrew J. Kelly SQL MVP
>
> "john" <john@.aic.com> wrote in message
> news:un9R5ZabEHA.3752@.TK2MSFTNGP12.phx.gbl...
> after
space[vbcol=seagreen]
> so
90M.[vbcol=seagreen]
> view
> Thanks.
of[vbcol=seagreen]
also[vbcol=seagreen]
> on
>