Database Stored on NaS

Can anyone tell me if it is possible to save SQL 2000 Database files on a
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
Thanks
Possible? yes.
Advisable? definitely NOT.
Supported? No.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>
|||Yes, you can store database files on a NaS device, but this will often be a
substantial tradeoff in terms of performance (while you didn't really give
us any details about your specific NaS architecture, typically this is used
for low $-per-GB storage, and not for high performance).
http://www.aspfaq.com/
(Reverse address to reply.)
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:#AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>
|||check kb below
http://support.microsoft.com/default...b;en-us;304261
You might want to look at iSCSI as an alternative
http://support.microsoft.com/default...b;en-us;833770
Andy.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>
|||You will pay a disk I/O performance hit not just because NAS is IP connected
but because Windows will not be able to issue Scatter Gather I/O requests
against it.
SQL Server uses these APIs to enhance its file maintenance and usage.
Sincerely,
Anthony Thomas

"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
Can anyone tell me if it is possible to save SQL 2000 Database files on a
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
Thanks
|||Thanks for everyone's input. The performance is not really an issue. We
have several customers that we support and for every customer we have a copy
of their SQL Data. We do periodically need to run some transactions through
the customers database but that doesn't really happen very often. All of
the data is currently sitting on our SQL box and I need to shift it
somewhere else. I thought the NAS would be the easiest option but I am now
just tempted to buy another SQL box just for the supported DB's.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>

Database Stored on NaS

Can anyone tell me if it is possible to save SQL 2000 Database files on a
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
ThanksPossible? yes.
Advisable? definitely NOT.
Supported? No.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>|||Yes, you can store database files on a NaS device, but this will often be a
substantial tradeoff in terms of performance (while you didn't really give
us any details about your specific NaS architecture, typically this is used
for low $-per-GB storage, and not for high performance).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:#AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>|||check kb below
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
You might want to look at iSCSI as an alternative
http://support.microsoft.com/default.aspx?scid=kb;en-us;833770
Andy.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>|||You will pay a disk I/O performance hit not just because NAS is IP connected
but because Windows will not be able to issue Scatter Gather I/O requests
against it.
SQL Server uses these APIs to enhance its file maintenance and usage.
Sincerely,
Anthony Thomas
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
Can anyone tell me if it is possible to save SQL 2000 Database files on a
NAS. I am running out of Disk Space on our SQL Server and thought a NAS
would be the easiest option for expanding the capacity.
Thanks|||Thanks for everyone's input. The performance is not really an issue. We
have several customers that we support and for every customer we have a copy
of their SQL Data. We do periodically need to run some transactions through
the customers database but that doesn't really happen very often. All of
the data is currently sitting on our SQL box and I need to shift it
somewhere else. I thought the NAS would be the easiest option but I am now
just tempted to buy another SQL box just for the supported DB's.
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%23AOymtT4EHA.824@.TK2MSFTNGP11.phx.gbl...
> Can anyone tell me if it is possible to save SQL 2000 Database files on a
> NAS. I am running out of Disk Space on our SQL Server and thought a NAS
> would be the easiest option for expanding the capacity.
> Thanks
>

Database state loading after restore.

Hi,
I have sql server 2000 running on windows 2003 clusters. While i
tried to restore the database i got the message that The restore
operation was successful. But still the database is showing state
"Loading."
I restored the database with recovery (Leave database opeational in
EM). I kept the database for a day and it shows still status as
"Loading".
Any help for this is highly appreciated.
Regards,
Jay
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Are you sure it is not only a refers issue in EM? Did you try below:
RESTORE DATABASE dbname WITH RECOVERY
And if you try above, what messages do you get?
Also, if you open QA and say "USE dbname", any errors?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jigar Patel" <jigar_pcs@.rediffmail.com> wrote in message
news:uCLjUt4rDHA.2828@.tk2msftngp13.phx.gbl...
> Hi,
> I have sql server 2000 running on windows 2003 clusters. While i
> tried to restore the database i got the message that The restore
> operation was successful. But still the database is showing state
> "Loading."
> I restored the database with recovery (Leave database opeational in
> EM). I kept the database for a day and it shows still status as
> "Loading".
> Any help for this is highly appreciated.
> Regards,
> Jay
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||I am sure as i have done this operation for atleast 15 times as of now.
I tried everything.
When i try to use
Use Database
Go
The message comes saying the database is still loading is not available.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||So what errors/messages do you get from the below command?
RESTORE DATABASE dbname WITH RECOVERY
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jigar Patel" <jigar_pcs@.rediffmail.com> wrote in message
news:uovOaN7rDHA.2360@.TK2MSFTNGP09.phx.gbl...
> I am sure as i have done this operation for atleast 15 times as of now.
> I tried everything.
> When i try to use
> Use Database
> Go
> The message comes saying the database is still loading is not available.
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Database Space Available Is Zero Even AFter Running Backup Log With No Truncate

I refreshed the SQL server name after rnning a backup log with no
truncate ....and still found out that space available size is still 0!
I also shrunk the Trans Log and still the same rusults.
Would very much appreciate your help
Thank you
*** Sent via Developersdex http://www.codecomments.com ***Hi,
What is the Recovery model of your database?
If it is FULL OR BULK_LOGGED then
If you need the transaction log backup execute below to clear the
Transaction log:-
backup log <dbname> to disk='d:\backup\dbname.trn
Incase if you do not require the transaction log backup go for;
backup log <dbname> with truncate_only
The above commands will clear the log , bit to reduce the physical LDF file
size you will have to execute the below command:-
DBCC SHRINKFILE (db1_log1_logical_name,truncat_eonly)
After doing these execute the below command to get the log size
dbcc sqlperf(logspace)
Thanks
Hari
SQL Server MVP
"mike" <-nospam@.yahoo.com> wrote in message
news:%23F7ieaVkFHA.2852@.TK2MSFTNGP14.phx.gbl...
> I refreshed the SQL server name after rnning a backup log with no
> truncate ....and still found out that space available size is still 0!
> I also shrunk the Trans Log and still the same rusults.
> Would very much appreciate your help
> Thank you
>
>
> *** Sent via Developersdex http://www.codecomments.com ***|||did you hear mike <-nospam@.yahoo.com> say in news:#F7ieaVkFHA.2852
@.TK2MSFTNGP14.phx.gbl:

> AFter Running Backup Log With No Truncate
sorry, but wouldn't this be the problem? if you wish to remove the
committed transactions from the log you should remove the no_truncate
option.
http://msdn.microsoft.com/library/d...rl=/library/en-
us/tsqlref/ts_ba-bz_35ww.asp
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||thank you. It worked, perfectly-
Mike
*** Sent via Developersdex http://www.codecomments.com ***

Database Space Available Is Zero Even AFter Running Backup Log With No Truncate

I refreshed the SQL server name after rnning a backup log with no
truncate ....and still found out that space available size is still 0!
I also shrunk the Trans Log and still the same rusults.
Would very much appreciate your help
Thank you
*** Sent via Developersdex http://www.codecomments.com ***
Hi,
What is the Recovery model of your database?
If it is FULL OR BULK_LOGGED then
If you need the transaction log backup execute below to clear the
Transaction log:-
backup log <dbname> to disk='d:\backup\dbname.trn
Incase if you do not require the transaction log backup go for;
backup log <dbname> with truncate_only
The above commands will clear the log , bit to reduce the physical LDF file
size you will have to execute the below command:-
DBCC SHRINKFILE (db1_log1_logical_name,truncatXeonly)
After doing these execute the below command to get the log size
dbcc sqlperf(logspace)
Thanks
Hari
SQL Server MVP
"mike" <-nospam@.yahoo.com> wrote in message
news:%23F7ieaVkFHA.2852@.TK2MSFTNGP14.phx.gbl...
> I refreshed the SQL server name after rnning a backup log with no
> truncate ....and still found out that space available size is still 0!
> I also shrunk the Trans Log and still the same rusults.
> Would very much appreciate your help
> Thank you
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||did you hear mike <-nospam@.yahoo.com> say in news:#F7ieaVkFHA.2852
@.TK2MSFTNGP14.phx.gbl:

> AFter Running Backup Log With No Truncate
sorry, but wouldn't this be the problem? if you wish to remove the
committed transactions from the log you should remove the no_truncate
option.
http://msdn.microsoft.com/library/de...l=/library/en-
us/tsqlref/ts_ba-bz_35ww.asp
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||thank you. It worked, perfectly-
Mike
*** Sent via Developersdex http://www.codecomments.com ***

Database Space Available Is Zero Even AFter Running Backup Log With No Truncate

I refreshed the SQL server name after rnning a backup log with no
truncate ....and still found out that space available size is still 0!
I also shrunk the Trans Log and still the same rusults.
Would very much appreciate your help
Thank you
*** Sent via Developersdex http://www.developersdex.com ***Hi,
What is the Recovery model of your database?
If it is FULL OR BULK_LOGGED then
If you need the transaction log backup execute below to clear the
Transaction log:-
backup log <dbname> to disk='d:\backup\dbname.trn
Incase if you do not require the transaction log backup go for;
backup log <dbname> with truncate_only
The above commands will clear the log , bit to reduce the physical LDF file
size you will have to execute the below command:-
DBCC SHRINKFILE (db1_log1_logical_name,truncat­eonly)
After doing these execute the below command to get the log size
dbcc sqlperf(logspace)
Thanks
Hari
SQL Server MVP
"mike" <-nospam@.yahoo.com> wrote in message
news:%23F7ieaVkFHA.2852@.TK2MSFTNGP14.phx.gbl...
> I refreshed the SQL server name after rnning a backup log with no
> truncate ....and still found out that space available size is still 0!
> I also shrunk the Trans Log and still the same rusults.
> Would very much appreciate your help
> Thank you
>
>
> *** Sent via Developersdex http://www.developersdex.com ***|||did you hear mike <-nospam@.yahoo.com> say in news:#F7ieaVkFHA.2852
@.TK2MSFTNGP14.phx.gbl:
> AFter Running Backup Log With No Truncate
sorry, but wouldn't this be the problem? if you wish to remove the
committed transactions from the log you should remove the no_truncate
option.
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/tsqlref/ts_ba-bz_35ww.asp
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogssql

Database running slow

To all,
I am running SQL Server 2000 SP3 and I have 6 database running on one
machine, in one instance, which has 4GB of ram. All the database are running
fine, except for one. The users are complaining of slowness and query
timeouts. The network traffic is fine and I am not sure what else it could
be. I have also noticed that one of the connections had a waittype called
'CXPACKET'
Does anyone know what could be causing the problem?
Thanks in advance,
BelindaCan you post the query this happens on? How many CPU does the server has and
speed? How are the disks set-up?
CXPACKET It means that one thread of the query is is waiting for a message
packet from another, and the one it is waiting on is either blocked by a
traditional cause or has hit some sort of parallelism bug.
Try Using MAXDOP(1) option on your queries, else make sure you have the
latest service packs applied.
Yovan Fernandez
"Belinda Rodriguez" <rodrigub@.symbol.com> wrote in message
news:e$971JxdDHA.3332@.TK2MSFTNGP09.phx.gbl...
> To all,
> I am running SQL Server 2000 SP3 and I have 6 database running on one
> machine, in one instance, which has 4GB of ram. All the database are
running
> fine, except for one. The users are complaining of slowness and query
> timeouts. The network traffic is fine and I am not sure what else it
could
> be. I have also noticed that one of the connections had a waittype called
> 'CXPACKET'
> Does anyone know what could be causing the problem?
> Thanks in advance,
> Belinda
>

Database retains size

Hello,
I have this 10 GB size database and unfortunately i'm running out of space
in my HDD. I'm using SQL Server 2000.
before i use dbcc shrink file on the database data file, the result of
sp_spaceused are the following:
db_name database_size unallocated space
abc 10881.70 MB -2526.48 MB
After I use: DBCC SHRINKFILE (abc_data, 10000)
I reduce the data file to aprox 10000, that is 800 Megs.
That all works fine, but next day as I check my DB the database size returns
to it's original size.
There's no way that in one night 800 megs of data is inserted in the
database.
I execute DBCC Reindex on 2 tables at night and do some backups..
Do you have any idea of what's maybe happening?
Any tip for regain some space?
Thanks
Eduardo SicouretWhat growth rate do you have specified in the database properties?|||What recovery model is the database in?
You can set up a trace to see when the database is growing. There are
database and log file growth events.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Eduardo Sicouret" <esicouret> wrote in message
news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
Hello,
I have this 10 GB size database and unfortunately i'm running out of space
in my HDD. I'm using SQL Server 2000.
before i use dbcc shrink file on the database data file, the result of
sp_spaceused are the following:
db_name database_size unallocated space
abc 10881.70 MB -2526.48 MB
After I use: DBCC SHRINKFILE (abc_data, 10000)
I reduce the data file to aprox 10000, that is 800 Megs.
That all works fine, but next day as I check my DB the database size returns
to it's original size.
There's no way that in one night 800 megs of data is inserted in the
database.
I execute DBCC Reindex on 2 tables at night and do some backups..
Do you have any idea of what's maybe happening?
Any tip for regain some space?
Thanks
Eduardo Sicouret|||File growth is automatically 64 megs at a time.
ESB
"Tracy McKibben" <tracy.mckibben@.gmail.com> escribió en el mensaje
news:1149779303.783251.118980@.j55g2000cwa.googlegroups.com...
> What growth rate do you have specified in the database properties?
>|||Recovery model is FULL and Autoshrink, Auto update statistics, auto create
statistics are checked
ESB
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escribió en el mensaje
news:%23zoKn2wiGHA.4776@.TK2MSFTNGP05.phx.gbl...
> What recovery model is the database in?
> You can set up a trace to see when the database is growing. There are
> database and log file growth events.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have this 10 GB size database and unfortunately i'm running out of space
> in my HDD. I'm using SQL Server 2000.
> before i use dbcc shrink file on the database data file, the result of
> sp_spaceused are the following:
> db_name database_size unallocated space
> abc 10881.70 MB -2526.48 MB
> After I use: DBCC SHRINKFILE (abc_data, 10000)
> I reduce the data file to aprox 10000, that is 800 Megs.
> That all works fine, but next day as I check my DB the database size
> returns
> to it's original size.
> There's no way that in one night 800 megs of data is inserted in the
> database.
> I execute DBCC Reindex on 2 tables at night and do some backups..
> Do you have any idea of what's maybe happening?
> Any tip for regain some space?
> Thanks
> Eduardo Sicouret
>
>|||I bet the reindex job is leaving unused space in your db. Run DBCC
UPDATEUSAGE after rebuilding the indexes and see if that helps. Ken.
Eduardo Sicouret wrote:
> Recovery model is FULL and Autoshrink, Auto update statistics, auto create
> statistics are checked
> ESB
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escribi=F3 en el mensaje
> news:%23zoKn2wiGHA.4776@.TK2MSFTNGP05.phx.gbl...
> > What recovery model is the database in?
> >
> > You can set up a trace to see when the database is growing. There are
> > database and log file growth events.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Eduardo Sicouret" <esicouret> wrote in message
> > news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> > Hello,
> >
> > I have this 10 GB size database and unfortunately i'm running out of sp=ace
> > in my HDD. I'm using SQL Server 2000.
> >
> > before i use dbcc shrink file on the database data file, the result of
> > sp_spaceused are the following:
> >
> > db_name database_size unallocated space
> > abc 10881.70 MB -2526.48 MB
> >
> > After I use: DBCC SHRINKFILE (abc_data, 10000)
> >
> > I reduce the data file to aprox 10000, that is 800 Megs.
> >
> > That all works fine, but next day as I check my DB the database size
> > returns
> > to it's original size.
> >
> > There's no way that in one night 800 megs of data is inserted in the
> > database.
> >
> > I execute DBCC Reindex on 2 tables at night and do some backups..
> >
> > Do you have any idea of what's maybe happening?
> > Any tip for regain some space?
> >
> > Thanks
> >
> > Eduardo Sicouret
> >
> >
> >|||How big are the tables you're reindexing, and are there clustered
indexes on them? If these are large tables with clustered indexes, the
reindex operation is likely causing the database to grow.
Eduardo Sicouret wrote:
> File growth is automatically 64 megs at a time.
> ESB
> "Tracy McKibben" <tracy.mckibben@.gmail.com> escribi=F3 en el mensaje
> news:1149779303.783251.118980@.j55g2000cwa.googlegroups.com...
> > What growth rate do you have specified in the database properties?
> >|||You shrink the file, then reindex which need a lot of working space which is why the size blows up.
If you shrink again, the reindex is basically undone again. Bottom line is that if you want best
performance, you need this working space. Some extra info here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eduardo Sicouret" <esicouret> wrote in message news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have this 10 GB size database and unfortunately i'm running out of space in my HDD. I'm using
> SQL Server 2000.
> before i use dbcc shrink file on the database data file, the result of sp_spaceused are the
> following:
> db_name database_size unallocated space
> abc 10881.70 MB -2526.48 MB
> After I use: DBCC SHRINKFILE (abc_data, 10000)
> I reduce the data file to aprox 10000, that is 800 Megs.
> That all works fine, but next day as I check my DB the database size returns to it's original
> size.
> There's no way that in one night 800 megs of data is inserted in the database.
> I execute DBCC Reindex on 2 tables at night and do some backups..
> Do you have any idea of what's maybe happening?
> Any tip for regain some space?
> Thanks
> Eduardo Sicouret
>|||Should also probably point out that all this
shrinking/growing/shrinking/growing is going to severly fragment the
database file, eventually hurting performance.
Tibor Karaszi wrote:
> You shrink the file, then reindex which need a lot of working space which is why the size blows up.
> If you shrink again, the reindex is basically undone again. Bottom line is that if you want best
> performance, you need this working space. Some extra info here:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> > Hello,
> >
> > I have this 10 GB size database and unfortunately i'm running out of space in my HDD. I'm using
> > SQL Server 2000.
> >
> > before i use dbcc shrink file on the database data file, the result of sp_spaceused are the
> > following:
> >
> > db_name database_size unallocated space
> > abc 10881.70 MB -2526.48 MB
> >
> > After I use: DBCC SHRINKFILE (abc_data, 10000)
> >
> > I reduce the data file to aprox 10000, that is 800 Megs.
> >
> > That all works fine, but next day as I check my DB the database size returns to it's original
> > size.
> >
> > There's no way that in one night 800 megs of data is inserted in the database.
> >
> > I execute DBCC Reindex on 2 tables at night and do some backups..
> >
> > Do you have any idea of what's maybe happening?
> > Any tip for regain some space?
> >
> > Thanks
> >
> > Eduardo Sicouret
> >|||"Eduardo Sicouret" <esicouret> wrote in message
news:%234bwfGxiGHA.4372@.TK2MSFTNGP03.phx.gbl...
> Recovery model is FULL and Autoshrink, Auto update statistics, auto create
> statistics are checked
>
I'd disable Autoshrink btw.
Growing and shrinking your DB can lead to disk level fragmentation.|||See whether you can shrink your logfile.. too..
"Eduardo Sicouret" <esicouret> wrote in message
news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have this 10 GB size database and unfortunately i'm running out of space
> in my HDD. I'm using SQL Server 2000.
> before i use dbcc shrink file on the database data file, the result of
> sp_spaceused are the following:
> db_name database_size unallocated space
> abc 10881.70 MB -2526.48 MB
> After I use: DBCC SHRINKFILE (abc_data, 10000)
> I reduce the data file to aprox 10000, that is 800 Megs.
> That all works fine, but next day as I check my DB the database size
> returns to it's original size.
> There's no way that in one night 800 megs of data is inserted in the
> database.
> I execute DBCC Reindex on 2 tables at night and do some backups..
> Do you have any idea of what's maybe happening?
> Any tip for regain some space?
> Thanks
> Eduardo Sicouret
>

Database retains size

Hello,
I have this 10 GB size database and unfortunately i'm running out of space
in my HDD. I'm using SQL Server 2000.
before i use dbcc shrink file on the database data file, the result of
sp_spaceused are the following:
db_name database_size unallocated space
abc 10881.70 MB -2526.48 MB
After I use: DBCC SHRINKFILE (abc_data, 10000)
I reduce the data file to aprox 10000, that is 800 Megs.
That all works fine, but next day as I check my DB the database size returns
to it's original size.
There's no way that in one night 800 megs of data is inserted in the
database.
I execute DBCC Reindex on 2 tables at night and do some backups..
Do you have any idea of what's maybe happening?
Any tip for regain some space?
Thanks
Eduardo SicouretWhat growth rate do you have specified in the database properties?|||What recovery model is the database in?
You can set up a trace to see when the database is growing. There are
database and log file growth events.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Eduardo Sicouret" <esicouret> wrote in message
news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
Hello,
I have this 10 GB size database and unfortunately i'm running out of space
in my HDD. I'm using SQL Server 2000.
before i use dbcc shrink file on the database data file, the result of
sp_spaceused are the following:
db_name database_size unallocated space
abc 10881.70 MB -2526.48 MB
After I use: DBCC SHRINKFILE (abc_data, 10000)
I reduce the data file to aprox 10000, that is 800 Megs.
That all works fine, but next day as I check my DB the database size returns
to it's original size.
There's no way that in one night 800 megs of data is inserted in the
database.
I execute DBCC Reindex on 2 tables at night and do some backups..
Do you have any idea of what's maybe happening?
Any tip for regain some space?
Thanks
Eduardo Sicouret|||File growth is automatically 64 megs at a time.
ESB
"Tracy McKibben" <tracy.mckibben@.gmail.com> escribi en el mensaje
news:1149779303.783251.118980@.j55g2000cwa.googlegroups.com...
> What growth rate do you have specified in the database properties?
>|||Recovery model is FULL and Autoshrink, Auto update statistics, auto create
statistics are checked
ESB
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escribi en el mensaje
news:%23zoKn2wiGHA.4776@.TK2MSFTNGP05.phx.gbl...
> What recovery model is the database in?
> You can set up a trace to see when the database is growing. There are
> database and log file growth events.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have this 10 GB size database and unfortunately i'm running out of space
> in my HDD. I'm using SQL Server 2000.
> before i use dbcc shrink file on the database data file, the result of
> sp_spaceused are the following:
> db_name database_size unallocated space
> abc 10881.70 MB -2526.48 MB
> After I use: DBCC SHRINKFILE (abc_data, 10000)
> I reduce the data file to aprox 10000, that is 800 Megs.
> That all works fine, but next day as I check my DB the database size
> returns
> to it's original size.
> There's no way that in one night 800 megs of data is inserted in the
> database.
> I execute DBCC Reindex on 2 tables at night and do some backups..
> Do you have any idea of what's maybe happening?
> Any tip for regain some space?
> Thanks
> Eduardo Sicouret
>
>|||I bet the reindex job is leaving unused space in your db. Run DBCC
UPDATEUSAGE after rebuilding the indexes and see if that helps. Ken.
Eduardo Sicouret wrote:[vbcol=seagreen]
> Recovery model is FULL and Autoshrink, Auto update statistics, auto create
> statistics are checked
> ESB
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escribi=F3 en el mensaje
> news:%23zoKn2wiGHA.4776@.TK2MSFTNGP05.phx.gbl...
ace[vbcol=seagreen]|||How big are the tables you're reindexing, and are there clustered
indexes on them? If these are large tables with clustered indexes, the
reindex operation is likely causing the database to grow.
Eduardo Sicouret wrote:[vbcol=seagreen]
> File growth is automatically 64 megs at a time.
> ESB
> "Tracy McKibben" <tracy.mckibben@.gmail.com> escribi=F3 en el mensaje
> news:1149779303.783251.118980@.j55g2000cwa.googlegroups.com...|||You shrink the file, then reindex which need a lot of working space which is
why the size blows up.
If you shrink again, the reindex is basically undone again. Bottom line is t
hat if you want best
performance, you need this working space. Some extra info here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eduardo Sicouret" <esicouret> wrote in message news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl..
.
> Hello,
> I have this 10 GB size database and unfortunately i'm running out of space
in my HDD. I'm using
> SQL Server 2000.
> before i use dbcc shrink file on the database data file, the result of sp_
spaceused are the
> following:
> db_name database_size unallocated space
> abc 10881.70 MB -2526.48 MB
> After I use: DBCC SHRINKFILE (abc_data, 10000)
> I reduce the data file to aprox 10000, that is 800 Megs.
> That all works fine, but next day as I check my DB the database size retur
ns to it's original
> size.
> There's no way that in one night 800 megs of data is inserted in the datab
ase.
> I execute DBCC Reindex on 2 tables at night and do some backups..
> Do you have any idea of what's maybe happening?
> Any tip for regain some space?
> Thanks
> Eduardo Sicouret
>|||Should also probably point out that all this
shrinking/growing/shrinking/growing is going to severly fragment the
database file, eventually hurting performance.
Tibor Karaszi wrote:[vbcol=seagreen]
> You shrink the file, then reindex which need a lot of working space which
is why the size blows up.
> If you shrink again, the reindex is basically undone again. Bottom line is
that if you want best
> performance, you need this working space. Some extra info here:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message news:esf4dxwiGHA.3848@.TK2M
SFTNGP04.phx.gbl...|||"Eduardo Sicouret" <esicouret> wrote in message
news:%234bwfGxiGHA.4372@.TK2MSFTNGP03.phx.gbl...
> Recovery model is FULL and Autoshrink, Auto update statistics, auto create
> statistics are checked
>
I'd disable Autoshrink btw.
Growing and shrinking your DB can lead to disk level fragmentation.

Database resource won't start after changing cluster subnet

I'm running a SQL Server 2000 SP4 2-node active/acitve cluster on
Windows 2003 Server Enterprise Edition SP1. I changed the subnet mask
of my public network config, as well as the MCS virtual subnet and two
database resource subnets (all are using the same subnet). DNS is
correctly resolving to the IP addresses for all cluster resources. I
have two databases on this cluster. After changing the subnet, the
first one starts correctly on both nodes. The second one fails to
start at the SQL Server resource on node 1, but starts correctly on
node 2. I've included the three major application log errors below.
Before I start troubleshooting any further, I'm looking for any
suggestions. Does anyone know if running the SQL Server installation
advanced options mode would make any difference? Could a machine
reboot be necessary?
Type: Error
User: N/A
Source: MSSQL$LCCSQLINST1
Category: (3)
Event ID: 17052
Description:
[sqsrvres] ODBC sqldriverconnect failed
=======================================
Type: Error
User: N/A
Source: MSSQL$LCCSQLINST1
Category: (3)
Event ID: 17052
Description:
[sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = 11;
message = [Microsoft][ODBC SQL Server
Driver][DBNETLIB]SQL Server does not exist or access denied.
=======================================
Type: Error
User: N/A
Source: MSSQL$LCCSQLINST1
Category: (3)
Event ID: 17052
Description:
[sqsrvres] checkODBCConnectError: sqlstate = 01000; native error = 35;
message = [Microsoft][ODBC SQL Server
Driver][DBNETLIB]ConnectionOpen (Connect()).
=======================================
Thanks,
Curt
http://support.microsoft.com/kb/244980/en-us
There's a section in there on how to change the subnet for the resources on
a SQL Server 2000 failover cluster configuration.
Sincerely,
Anthony Thomas

"Curtis Morrison" <curtmorrison@.hotmail.com> wrote in message
news:1174920700.449721.209260@.l77g2000hsb.googlegr oups.com...
> I'm running a SQL Server 2000 SP4 2-node active/acitve cluster on
> Windows 2003 Server Enterprise Edition SP1. I changed the subnet mask
> of my public network config, as well as the MCS virtual subnet and two
> database resource subnets (all are using the same subnet). DNS is
> correctly resolving to the IP addresses for all cluster resources. I
> have two databases on this cluster. After changing the subnet, the
> first one starts correctly on both nodes. The second one fails to
> start at the SQL Server resource on node 1, but starts correctly on
> node 2. I've included the three major application log errors below.
> Before I start troubleshooting any further, I'm looking for any
> suggestions. Does anyone know if running the SQL Server installation
> advanced options mode would make any difference? Could a machine
> reboot be necessary?
> Type: Error
> User: N/A
> Source: MSSQL$LCCSQLINST1
> Category: (3)
> Event ID: 17052
> Description:
> [sqsrvres] ODBC sqldriverconnect failed
> =======================================
> Type: Error
> User: N/A
> Source: MSSQL$LCCSQLINST1
> Category: (3)
> Event ID: 17052
> Description:
> [sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = 11;
> message = [Microsoft][ODBC SQL Server
> Driver][DBNETLIB]SQL Server does not exist or access denied.
> =======================================
> Type: Error
> User: N/A
> Source: MSSQL$LCCSQLINST1
> Category: (3)
> Event ID: 17052
> Description:
> [sqsrvres] checkODBCConnectError: sqlstate = 01000; native error = 35;
> message = [Microsoft][ODBC SQL Server
> Driver][DBNETLIB]ConnectionOpen (Connect()).
> =======================================
> Thanks,
> Curt
>

Database Replication

I'm a newbie so bare with me. If I have two servers running SQL Server, a primary and a secondary, how can I make sure that both databases (One on each server) are always syncronized? In our situation the secondary server is a hot backup in case the primary fails. If the primary fails then data gets written to the secondary. When the primary comes back up then its database needs to be synced with the secondary. Can anyone tell me how to do this?Have a look on books online about Transactional Replication...

This is a form of replication that will make your two server in sync.

Any changes that are made on one server are automatically replicated over to your other server.

http://www.databasejournal.com/features/mssql/article.php/1438201

http://www.sql-server-performance.com/transactional_replication.asp

Hope this helps mate|||Thanks for the links! Do you know if you are doing transactional replication, if the other server is down, will the server que the transactions and keep trying to send them until it succeees?|||no,

As far as I know it will publish on an all or nothing basis.

If your primary server goes down then replication will stop because it has no server to replicate to (i.e no subscribers)

This is the time you flip the primary to your hot spare and fix the original server. After you have sorted the problem on the original server just kick off replication again.

Cheers|||Why don't you create a 2 node cluster?|||Not using Enterprise. I don't think it is available on standard.|||You're right, Only on Enterprize.

Database reindex

I had job set up do to database reindex using database maintenance
plans...since loading SP4 for sql 2000 these jobs are not running ...i can
do start job and that fine but i need them to run early morning of hours
Any ideasHi Tracey
Have you checked that the jobs and the job schedule are both enabled? What
does the job history say? Have you got any information in the SQL Server log
or the Windows Event Log at the times these jobs are supposed to run? Have
you tried re-creating the maintenance plan or jobs?
John
"TRACEY" wrote:

> I had job set up do to database reindex using database maintenance
> plans...since loading SP4 for sql 2000 these jobs are not running ...i ca
n
> do start job and that fine but i need them to run early morning of hours
> Any ideas|||TRACEY
If I undestsood you , under Optimization Tab choose schedule to create a
job
"TRACEY" <TRACEY@.discussions.microsoft.com> wrote in message
news:3F05031E-F96D-484D-83CF-A4F87A9DD259@.microsoft.com...
>I had job set up do to database reindex using database maintenance
> plans...since loading SP4 for sql 2000 these jobs are not running ...i
> can
> do start job and that fine but i need them to run early morning of hours
> Any ideas|||That how i set up the job..
under Optimization Tab choose schedule to create a
job
Its enabled...and there nothing in the job history nothing in the sql
logs...nothing in events...i just got it to fire off at night and then in
morning i see nothing ...no history
I can just right click it and boom it starts the index which i really don't
want to do during activity..
This only happened since SP4 ........
Im almost going to write a script to do the dbreindex ...but i like to
figure out why..
The only thing we did do was for some of the tables we put these in separate
file groups (the vendor erp creates temp files for each person called
X_.owner) so we put these in file groups so i can see when they get large
...and when i can then run a utility to clear them out...but the data
remains where it is and the log files.|||Hi Tracey
If there is nothing in the job history is sounds like the schedule is wrong.
If you schedule it for a different time rather than manually run it, can you
see the job starting?
John
"TRACEY" wrote:

> That how i set up the job..
> under Optimization Tab choose schedule to create a
> job
> Its enabled...and there nothing in the job history nothing in the sql
> logs...nothing in events...i just got it to fire off at night and then in
> morning i see nothing ...no history
> I can just right click it and boom it starts the index which i really don'
t
> want to do during activity..
> This only happened since SP4 ........
> Im almost going to write a script to do the dbreindex ...but i like to
> figure out why..
> The only thing we did do was for some of the tables we put these in separa
te
> file groups (the vendor erp creates temp files for each person called
> X_.owner) so we put these in file groups so i can see when they get large
> ...and when i can then run a utility to clear them out...but the data
> remains where it is and the log files.
>
>|||Sunday it ran this was the normal day i had it set all the other days monday
thursday failed....odd but at least it ran last night.
I checked the logs
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'SQL1'
Starting maintenance plan 'Reindex Databases Costpoint' on 10/29/2006
2:00:03 AM
[1] Database DeltekTC: Index Rebuild (leaving 100%% free space)...
I have it set to Database maintenance plan optimizations set to regorganize
data and index pages
reorganize pages with the orginal amount of free space ....thats why i get
100%
That means ever time record insert its going to split ha.....
how to change it to 90
Do i select the change free space per page percentage to 10%
will that be DBCC REINDEX 'DB','90'...?
And do you do master, model,
Well im glad it ran last night...
Thanks|||Hi Tracey
In general you would want to pass 0 to the maintainance plan to re-organise
the indexes using their original value. In any given database you may want t
o
have some indexes with fill factors or 0 or 100 (if the data is static!) and
others with less, therefore resetting the index fill factor across the board
is not necessarily a good thing. If you want to change the fill factor for a
give index use
DBCC DBREINDEX ( 'database.owner.table_name' , index_name, fillfactor )
Analyse the information from DBCC SHOWCONTIG or
sys.dm_db_index_physical_stats instead (SQL 2005) for information on what
indexes are fragmented.
John
"TRACEY" wrote:

> Sunday it ran this was the normal day i had it set all the other days mond
ay
> thursday failed....odd but at least it ran last night.
> I checked the logs
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'SQL1'
> Starting maintenance plan 'Reindex Databases Costpoint' on 10/29/2006
> 2:00:03 AM
> [1] Database DeltekTC: Index Rebuild (leaving 100%% free space)...
> I have it set to Database maintenance plan optimizations set to regorganiz
e
> data and index pages
> reorganize pages with the orginal amount of free space ....thats why i ge
t
> 100%
> That means ever time record insert its going to split ha.....
> how to change it to 90
> Do i select the change free space per page percentage to 10%
> will that be DBCC REINDEX 'DB','90'...?
> And do you do master, model,
> Well im glad it ran last night...
> Thanks|||Took me ages to find the thread.
How do you bookmark it so i can go to it.
I have it set to Database maintenance plan optimizations set to regorganize
data and index pages
reorganize pages with the orginal amount of free space
Any ideas why its reporting 100%
That means ever time record insert its going to split
Why is it not taking the default fill factor in the tables.
Thanks|||Hi Tracey
That will depend on what newsreader client you are using. You can set
Outlook Express to only display message threads that you have participated
in, or you can search the technet discussion groups using (say) your email
address. Here is a link for the thread http://tinyurl.com/y3ntk4
The SQL Maint utility uses a value of 100 for the RebldIdx to specify that
you use the original values as this works on free space and not the
fillfactor. DBCC DBREINDEX uses 0 to maintain the current fill factors. You
can reindex specific indexes using this command to change the fill factor an
d
then use a maintenance plan or your own job to keep it (and all other
indexes) at their own level. Another method to restore the fillfactors would
be to drop and re-create the indexes if you already have scripts that for
their original definions. This would be the case if you use a source code
control system such as Visual Source Safe for you database code.
Having a fill factor of 100% is not necessarily a bad thing, if you have
table that contains very static data you may want to use 100% fill factor,
similarly if the table is being treated as an ISAM structure then a high fil
l
factor may be appropriate if you rarely update existing rows.
John
"TRACEY" wrote:

> Took me ages to find the thread.
> How do you bookmark it so i can go to it.
> I have it set to Database maintenance plan optimizations set to regorganiz
e
> data and index pages
> reorganize pages with the orginal amount of free space
> Any ideas why its reporting 100%
> That means ever time record insert its going to split
> Why is it not taking the default fill factor in the tables.
> Thanks

Database reindex

I had job set up do to database reindex using database maintenance
plans...since loading SP4 for sql 2000 these jobs are not running ...i can
do start job and that fine but i need them to run early morning of hours
Any ideasHi Tracey
Have you checked that the jobs and the job schedule are both enabled? What
does the job history say? Have you got any information in the SQL Server log
or the Windows Event Log at the times these jobs are supposed to run? Have
you tried re-creating the maintenance plan or jobs?
John
"TRACEY" wrote:
> I had job set up do to database reindex using database maintenance
> plans...since loading SP4 for sql 2000 these jobs are not running ...i can
> do start job and that fine but i need them to run early morning of hours
> Any ideas|||TRACEY
If I undestsood you , under Optimization Tab choose schedule to create a
job
"TRACEY" <TRACEY@.discussions.microsoft.com> wrote in message
news:3F05031E-F96D-484D-83CF-A4F87A9DD259@.microsoft.com...
>I had job set up do to database reindex using database maintenance
> plans...since loading SP4 for sql 2000 these jobs are not running ...i
> can
> do start job and that fine but i need them to run early morning of hours
> Any ideas|||That how i set up the job..
under Optimization Tab choose schedule to create a
job
Its enabled...and there nothing in the job history nothing in the sql
logs...nothing in events...i just got it to fire off at night and then in
morning i see nothing ...no history
I can just right click it and boom it starts the index which i really don't
want to do during activity..
This only happened since SP4 ........
Im almost going to write a script to do the dbreindex ...but i like to
figure out why..
The only thing we did do was for some of the tables we put these in separate
file groups (the vendor erp creates temp files for each person called
X_.owner) so we put these in file groups so i can see when they get large
...and when i can then run a utility to clear them out...but the data
remains where it is and the log files.|||Hi Tracey
If there is nothing in the job history is sounds like the schedule is wrong.
If you schedule it for a different time rather than manually run it, can you
see the job starting?
John
"TRACEY" wrote:
> That how i set up the job..
> under Optimization Tab choose schedule to create a
> job
> Its enabled...and there nothing in the job history nothing in the sql
> logs...nothing in events...i just got it to fire off at night and then in
> morning i see nothing ...no history
> I can just right click it and boom it starts the index which i really don't
> want to do during activity..
> This only happened since SP4 ........
> Im almost going to write a script to do the dbreindex ...but i like to
> figure out why..
> The only thing we did do was for some of the tables we put these in separate
> file groups (the vendor erp creates temp files for each person called
> X_.owner) so we put these in file groups so i can see when they get large
> ...and when i can then run a utility to clear them out...but the data
> remains where it is and the log files.
>
>|||Sunday it ran this was the normal day i had it set all the other days monday
thursday failed....odd but at least it ran last night.
I checked the logs
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'SQL1'
Starting maintenance plan 'Reindex Databases Costpoint' on 10/29/2006
2:00:03 AM
[1] Database DeltekTC: Index Rebuild (leaving 100%% free space)...
I have it set to Database maintenance plan optimizations set to regorganize
data and index pages
reorganize pages with the orginal amount of free space ....thats why i get
100%
That means ever time record insert its going to split ha.....
how to change it to 90
Do i select the change free space per page percentage to 10%
will that be DBCC REINDEX 'DB','90'...?
And do you do master, model,
Well im glad it ran last night...
Thanks|||Hi Tracey
In general you would want to pass 0 to the maintainance plan to re-organise
the indexes using their original value. In any given database you may want to
have some indexes with fill factors or 0 or 100 (if the data is static!) and
others with less, therefore resetting the index fill factor across the board
is not necessarily a good thing. If you want to change the fill factor for a
give index use
DBCC DBREINDEX ( 'database.owner.table_name' , index_name, fillfactor )
Analyse the information from DBCC SHOWCONTIG or
sys.dm_db_index_physical_stats instead (SQL 2005) for information on what
indexes are fragmented.
John
"TRACEY" wrote:
> Sunday it ran this was the normal day i had it set all the other days monday
> thursday failed....odd but at least it ran last night.
> I checked the logs
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'SQL1'
> Starting maintenance plan 'Reindex Databases Costpoint' on 10/29/2006
> 2:00:03 AM
> [1] Database DeltekTC: Index Rebuild (leaving 100%% free space)...
> I have it set to Database maintenance plan optimizations set to regorganize
> data and index pages
> reorganize pages with the orginal amount of free space ....thats why i get
> 100%
> That means ever time record insert its going to split ha.....
> how to change it to 90
> Do i select the change free space per page percentage to 10%
> will that be DBCC REINDEX 'DB','90'...?
> And do you do master, model,
> Well im glad it ran last night...
> Thanks|||Took me ages to find the thread.
How do you bookmark it so i can go to it.
I have it set to Database maintenance plan optimizations set to regorganize
data and index pages
reorganize pages with the orginal amount of free space
Any ideas why its reporting 100%
That means ever time record insert its going to split
Why is it not taking the default fill factor in the tables.
Thanks|||Hi Tracey
That will depend on what newsreader client you are using. You can set
Outlook Express to only display message threads that you have participated
in, or you can search the technet discussion groups using (say) your email
address. Here is a link for the thread http://tinyurl.com/y3ntk4
The SQL Maint utility uses a value of 100 for the RebldIdx to specify that
you use the original values as this works on free space and not the
fillfactor. DBCC DBREINDEX uses 0 to maintain the current fill factors. You
can reindex specific indexes using this command to change the fill factor and
then use a maintenance plan or your own job to keep it (and all other
indexes) at their own level. Another method to restore the fillfactors would
be to drop and re-create the indexes if you already have scripts that for
their original definions. This would be the case if you use a source code
control system such as Visual Source Safe for you database code.
Having a fill factor of 100% is not necessarily a bad thing, if you have
table that contains very static data you may want to use 100% fill factor,
similarly if the table is being treated as an ISAM structure then a high fill
factor may be appropriate if you rarely update existing rows.
John
"TRACEY" wrote:
> Took me ages to find the thread.
> How do you bookmark it so i can go to it.
> I have it set to Database maintenance plan optimizations set to regorganize
> data and index pages
> reorganize pages with the orginal amount of free space
> Any ideas why its reporting 100%
> That means ever time record insert its going to split
> Why is it not taking the default fill factor in the tables.
> Thanks

Database recovery freezing server?

I'm currently running Windows Server 2003 and just recently installed SQL 2005. I had a few databases that I was attaching from the old SQL 2000 server. I encountered a hard error and when I restarted Management studio, I found 4 of my databases were in recovery. Thinking this would be a short process, I let it run and did work on one of my other machines. Nearly 2 hours later, the databases are still in recovery and nothing appears to have changed. I'm new to sql 2005 and don't know what the best method of bringing those databases back up would be. If it is possible to just detach the databases (or blow them away completely) I have backups that are plenty recent enough, however I find I am unable to make any changes to the databases while one of the system db's is in recovery. Any ideas?

KCanterucci,

I think you can find more accurate answer in sql server engine forum.

Database Performance Halts

Hi,

I have been running a reporting App on an SQL 2000 server, which reads from one large table (roughly 80 million records which grows at around 2 million records a week).

However, it would seem that when multi-users try to access the App and the one large table is Read from using 1 database user name up to approximately 25 times, the system would slow to a halt. Essentially, each request on the App would be a new connection to the SQL database using the same database user name. Recently, we have been running into performance issues since we have increased the number of users for the App.

What would be causing this slow down? and what could solve this problem?

>>What would be causing this slow down? and what could solve this problem?

Do you have indexes on the table? are the queries sargable? are your statistics up to date?

>>and the one large table is Read

What does this mean how is the table read? Are you just doing a select * from table?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

>>Do you have indexes on the table? are the queries sargable? are your statistics up to date?

I have indexes on the table and seems to work perfectly fine when there is only 1 query running. All the queries I am running are based on atleast 1 index on that table. Also which statistics are you referring to?

>>What does this mean how is the table read? Are you just doing a select * from table?

From what I can tell, it should only be a series of select statements or nested select statements that the report App runs and stores on the report App side. The reporting App is making from 10-30 of these requests on that 1 indexed table at a single moment in time.

Database Performance

Hello there,
I am experiencing performance problems with an aplication running over a SQL
Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
memory. When there are just on or two users working, the aplication
performance is suportable, but when more users are using the aplication,
there picks when its almost impossible work normaly.
My question is: what can possible cause this loss of performance?
All workstations have an ODBC connection with 'sa' user. Can it be
important?
What can I do to troubleshoot this problem?
The are serveral important tables with many fields and many records. I think
that I have created indexes correctly.
Can anybody help me? Thanks.
Regards,
Marco Pais
Usually the causes are bad design, poorly written queries, and
poor/inadequate indexing. BOL has a lot of trouble-shooting information
under the topic "Troubleshooting". You probably want to look at blocking
first.
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:ebcvgSTBFHA.3644@.TK2MSFTNGP15.phx.gbl...
> Hello there,
> I am experiencing performance problems with an aplication running over a
SQL
> Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
> memory. When there are just on or two users working, the aplication
> performance is suportable, but when more users are using the aplication,
> there picks when its almost impossible work normaly.
> My question is: what can possible cause this loss of performance?
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
> What can I do to troubleshoot this problem?
> The are serveral important tables with many fields and many records. I
think
> that I have created indexes correctly.
> Can anybody help me? Thanks.
> Regards,
> Marco Pais
>
|||There are lots of things it can be and you need to narrow down the
possibilities. Blocking does indeed sound like a good possibility. Try
running sp_who2 when you have multiple users in the slow state and see if
any show up with a spid in the Blcked by column. These articles should help
to get you going in the right direction.
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:ebcvgSTBFHA.3644@.TK2MSFTNGP15.phx.gbl...
> Hello there,
> I am experiencing performance problems with an aplication running over a
> SQL Server 2000 Database. The server that holds SQL Server has 2.8 GB of
> RAM memory. When there are just on or two users working, the aplication
> performance is suportable, but when more users are using the aplication,
> there picks when its almost impossible work normaly.
> My question is: what can possible cause this loss of performance?
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
> What can I do to troubleshoot this problem?
> The are serveral important tables with many fields and many records. I
> think that I have created indexes correctly.
> Can anybody help me? Thanks.
> Regards,
> Marco Pais
>

Database Performance

Hello there,
I am experiencing performance problems with an aplication running over a SQL
Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
memory. When there are just on or two users working, the aplication
performance is suportable, but when more users are using the aplication,
there picks when its almost impossible work normaly.
My question is: what can possible cause this loss of performance?
All workstations have an ODBC connection with 'sa' user. Can it be
important?
What can I do to troubleshoot this problem?
The are serveral important tables with many fields and many records. I think
that I have created indexes correctly.
Can anybody help me? Thanks.
Regards,
Marco Pais
Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:u$%23YoSTBFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Hello there,
> I am experiencing performance problems with an aplication running over a
> SQL Server 2000 Database. The server that holds SQL Server has 2.8 GB of
> RAM memory. When there are just on or two users working, the aplication
> performance is suportable, but when more users are using the aplication,
> there picks when its almost impossible work normaly.
> My question is: what can possible cause this loss of performance?
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
> What can I do to troubleshoot this problem?
> The are serveral important tables with many fields and many records. I
> think that I have created indexes correctly.
> Can anybody help me? Thanks.
> Regards,
> Marco Pais
>
|||Considering that the problem seems to occur as more users login, first read
up on:
sp_who2 and blocking SPIDs
transaction isolation level
To optimize the runtime of queries:
Analyzing Execution Plans
Also, go to msdn.com and search on "sql server" and "optimization"
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:u$%23YoSTBFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Hello there,
> I am experiencing performance problems with an aplication running over a
SQL
> Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
> memory. When there are just on or two users working, the aplication
> performance is suportable, but when more users are using the aplication,
> there picks when its almost impossible work normaly.
> My question is: what can possible cause this loss of performance?
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
> What can I do to troubleshoot this problem?
> The are serveral important tables with many fields and many records. I
think
> that I have created indexes correctly.
> Can anybody help me? Thanks.
> Regards,
> Marco Pais
>

Database performance

Hello there,
I am experiencing performance problems with an aplication running over a SQL
Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
memory. When there are just on or two users working, the aplication
performance is suportable, but when more users are using the aplication,
there picks when its almost impossible work normaly.
My question is: what can possible cause this loss of performance?
All workstations have an ODBC connection with 'sa' user. Can it be
important?
What can I do to troubleshoot this problem?
The are serveral important tables with many fields and many records. I think
that I have created indexes correctly.
Can anybody help me? Thanks.
Regards,
Marco Pais
Take a look here:
http://support.microsoft.com/default...b;en-us;224587

> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
Yes it's very important, although it has nothing to do with
performance. If user applications connect as a system administrator
then security is non-existent - one rogue user, faulty piece of code or
malicious hack could destroy your database and your server.
David Portas
SQL Server MVP
|||"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:OVwy8STBFHA.2012@.TK2MSFTNGP15.phx.gbl...
> Hello there,
> I am experiencing performance problems with an aplication running over a
SQL
> Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
> memory. When there are just on or two users working, the aplication
> performance is suportable, but when more users are using the aplication,
> there picks when its almost impossible work normaly.
This is almost certainly a code problem. It could be the application is
keeping locks for far longer than necessary. It could be your queries have
changed enough that your indices don't work as well.

> My question is: what can possible cause this loss of performance?
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
As David mentions, this is a HUGE security risk. Not only can users do
anything to the db, they can do anything to the OS.
With the XP_CMDSHELL command they can create domain users, add them to the
domain admin group and take over your system.

> What can I do to troubleshoot this problem?
Simplest, look at output of sp_who2 active and look for blocking. If it's
there consistently, that's your biggest problem. Eliminate it.
Use dbcc inputbuffer(spid) on the blocking spid to see what the code looks
like.
If this doesn't help, time to break out profiler.

> The are serveral important tables with many fields and many records. I
think
> that I have created indexes correctly.
> Can anybody help me? Thanks.
> Regards,
> Marco Pais
>

Database Performance

Hello there,
I am experiencing performance problems with an aplication running over a SQL
Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
memory. When there are just on or two users working, the aplication
performance is suportable, but when more users are using the aplication,
there picks when its almost impossible work normaly.
My question is: what can possible cause this loss of performance?
All workstations have an ODBC connection with 'sa' user. Can it be
important?
What can I do to troubleshoot this problem?
The are serveral important tables with many fields and many records. I think
that I have created indexes correctly.
Can anybody help me? Thanks.
Regards,
Marco PaisPlease don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:u$%23YoSTBFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Hello there,
> I am experiencing performance problems with an aplication running over a
> SQL Server 2000 Database. The server that holds SQL Server has 2.8 GB of
> RAM memory. When there are just on or two users working, the aplication
> performance is suportable, but when more users are using the aplication,
> there picks when its almost impossible work normaly.
> My question is: what can possible cause this loss of performance?
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
> What can I do to troubleshoot this problem?
> The are serveral important tables with many fields and many records. I
> think that I have created indexes correctly.
> Can anybody help me? Thanks.
> Regards,
> Marco Pais
>|||Considering that the problem seems to occur as more users login, first read
up on:
sp_who2 and blocking SPIDs
transaction isolation level
To optimize the runtime of queries:
Analyzing Execution Plans
Also, go to msdn.com and search on "sql server" and "optimization"
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:u$%23YoSTBFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Hello there,
> I am experiencing performance problems with an aplication running over a
SQL
> Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
> memory. When there are just on or two users working, the aplication
> performance is suportable, but when more users are using the aplication,
> there picks when its almost impossible work normaly.
> My question is: what can possible cause this loss of performance?
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
> What can I do to troubleshoot this problem?
> The are serveral important tables with many fields and many records. I
think
> that I have created indexes correctly.
> Can anybody help me? Thanks.
> Regards,
> Marco Pais
>

Database performance

Hello there,
I am experiencing performance problems with an aplication running over a SQL
Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
memory. When there are just on or two users working, the aplication
performance is suportable, but when more users are using the aplication,
there picks when its almost impossible work normaly.
My question is: what can possible cause this loss of performance?
All workstations have an ODBC connection with 'sa' user. Can it be
important?
What can I do to troubleshoot this problem?
The are serveral important tables with many fields and many records. I think
that I have created indexes correctly.
Can anybody help me? Thanks.
Regards,
Marco PaisTake a look here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;224587
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
Yes it's very important, although it has nothing to do with
performance. If user applications connect as a system administrator
then security is non-existent - one rogue user, faulty piece of code or
malicious hack could destroy your database and your server.
--
David Portas
SQL Server MVP
--|||"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:OVwy8STBFHA.2012@.TK2MSFTNGP15.phx.gbl...
> Hello there,
> I am experiencing performance problems with an aplication running over a
SQL
> Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
> memory. When there are just on or two users working, the aplication
> performance is suportable, but when more users are using the aplication,
> there picks when its almost impossible work normaly.
This is almost certainly a code problem. It could be the application is
keeping locks for far longer than necessary. It could be your queries have
changed enough that your indices don't work as well.
> My question is: what can possible cause this loss of performance?
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
As David mentions, this is a HUGE security risk. Not only can users do
anything to the db, they can do anything to the OS.
With the XP_CMDSHELL command they can create domain users, add them to the
domain admin group and take over your system.
> What can I do to troubleshoot this problem?
Simplest, look at output of sp_who2 active and look for blocking. If it's
there consistently, that's your biggest problem. Eliminate it.
Use dbcc inputbuffer(spid) on the blocking spid to see what the code looks
like.
If this doesn't help, time to break out profiler.
> The are serveral important tables with many fields and many records. I
think
> that I have created indexes correctly.
> Can anybody help me? Thanks.
> Regards,
> Marco Pais
>