MSSQL Mistake: working

Showing posts with label working. Show all posts

Friday, March 30, 2012

Multi value parameter not working

I have installed MS sql server 2005 developer edition beta2.
In report designer,i have given a parameter to a multi value and when see in
preview i am able to see the multi valued parameter.
but when i run it and see it in reportserver,even though i select multi
values,while displaying the results its taking only the last value selected.
please help me in this regard.I think that with Beta 2 there were some problems with multi-value. It is
working in the release version. If you have MSDN you can download it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sirisha" <sirisha@.discussions.microsoft.com> wrote in message
news:D7D10346-8D31-4810-A287-AD6D1D41030B@.microsoft.com...
>I have installed MS sql server 2005 developer edition beta2.
> In report designer,i have given a parameter to a multi value and when see
> in
> preview i am able to see the multi valued parameter.
> but when i run it and see it in reportserver,even though i select multi
> values,while displaying the results its taking only the last value
> selected.
> please help me in this regard.
>sql

Friday, March 23, 2012

Multi access in SQL Server

Hello,

I am working on an Access VBA application, working in client/server
mode, with a shared SQL Server base.

I have to INSERT some data in the base, and the know what is the ID
that SQL Server associated with my data in the table. So I need to do
the following

INSERT data INTO TABLE
SELECT MAX ID FROM TABLE (to get the ID)

but since I am working in client/server, if some DATA has been inserted
by another client after my INSERT, then MAX ID is not the convenient
ID of my data ...

Is there a way for me to get the ID when I do the INSERT, or to
lock/unlock the table before the INSERT and after the SELECT ?

Thank you for any hint about that.

--
L'ordinateur peut faire plus de calculs que le cerveau de l'homme car
il n'a que a faireByB (email@.email.com) writes:

Quote:

Originally Posted by

I am working on an Access VBA application, working in client/server
mode, with a shared SQL Server base.
>
I have to INSERT some data in the base, and the know what is the ID
that SQL Server associated with my data in the table. So I need to do
the following
>
INSERT data INTO TABLE
SELECT MAX ID FROM TABLE (to get the ID)
>
but since I am working in client/server, if some DATA has been inserted
by another client after my INSERT, then MAX ID is not the convenient
ID of my data ...
>
Is there a way for me to get the ID when I do the INSERT, or to
lock/unlock the table before the INSERT and after the SELECT ?

It sounds like your table has the IDENTITY property. In such case, use
the function scope_identity() to retrieve last generated ID value.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Multable database on one machine

Total newbie question i know.
The company i just started working for has SQL 2000 installed on four
machines. Is there any info i can get about putting all database on one
machine, Then having an application server running the apps (Great Plains
7.5, Sharepoint, track-it, and a custom made database)
Thanks
LeeYes, you can have multiple databases on the same machine, and on the same
instance of SQL Server. Your limitations are: (a) two databases can't have
the same name, or expect their data/log files to have the same path, and (b)
the available resources on the server. If it's a low-powered box it may
perform acceptably hosting one application's database
As for the right way to perform the consolidation, if you are comfortable
that it is the right move, there are several options. Andrew Kelly has
posted this list of KB articles that should help you out:
http://tinyurl.com/up5ok
"Lee J" <faked@.fc.com> wrote in message
news:ejg6to$5ap@.dispatch.concentric.net...
> Total newbie question i know.
> The company i just started working for has SQL 2000 installed on four
> machines. Is there any info i can get about putting all database on one
> machine, Then having an application server running the apps (Great Plains
> 7.5, Sharepoint, track-it, and a custom made database)
> Thanks
> Lee
>|||Great, Thank you, I am looking to be buying some new blade servers
"Aaron Bertrand [MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u7yEr9QCHHA.5064@.TK2MSFTNGP02.phx.gbl...
> Yes, you can have multiple databases on the same machine, and on the same
> instance of SQL Server. Your limitations are: (a) two databases can't
> have the same name, or expect their data/log files to have the same path,
> and (b) the available resources on the server. If it's a low-powered box
> it may perform acceptably hosting one application's database
> As for the right way to perform the consolidation, if you are comfortable
> that it is the right move, there are several options. Andrew Kelly has
> posted this list of KB articles that should help you out:
> http://tinyurl.com/up5ok
>
> "Lee J" <faked@.fc.com> wrote in message
> news:ejg6to$5ap@.dispatch.concentric.net...
>> Total newbie question i know.
>> The company i just started working for has SQL 2000 installed on four
>> machines. Is there any info i can get about putting all database on one
>> machine, Then having an application server running the apps (Great Plains
>> 7.5, Sharepoint, track-it, and a custom made database)
>> Thanks
>> Lee
>|||Wow, I don't think I've come across anyone successfully running SQL Server
on a blade, but hey, all the best.
Actually I'm sitting with Geoff at PASS and he said that, unfortunately, he
has seen this, and it didn't work out too well. But again, all the best.
If it works out, please let us know. :-)
"Lee J" <faked@.fc.com> wrote in message
news:ejg8fo$5s5@.dispatch.concentric.net...
> Great, Thank you, I am looking to be buying some new blade servers
>
> "Aaron Bertrand [MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:u7yEr9QCHHA.5064@.TK2MSFTNGP02.phx.gbl...
>> Yes, you can have multiple databases on the same machine, and on the same
>> instance of SQL Server. Your limitations are: (a) two databases can't
>> have the same name, or expect their data/log files to have the same path,
>> and (b) the available resources on the server. If it's a low-powered box
>> it may perform acceptably hosting one application's database
>> As for the right way to perform the consolidation, if you are comfortable
>> that it is the right move, there are several options. Andrew Kelly has
>> posted this list of KB articles that should help you out:
>> http://tinyurl.com/up5ok
>>
>> "Lee J" <faked@.fc.com> wrote in message
>> news:ejg6to$5ap@.dispatch.concentric.net...
>> Total newbie question i know.
>> The company i just started working for has SQL 2000 installed on four
>> machines. Is there any info i can get about putting all database on one
>> machine, Then having an application server running the apps (Great
>> Plains 7.5, Sharepoint, track-it, and a custom made database)
>> Thanks
>> Lee
>>
>|||well, thats good to know, i am just in the discovery stage.
what kind of specs do you recommed?
My most heavly used database is on a dual 1GHz intel with 2GB of RAM. The
blades i was looking at were dual 1.8 xeons with 2GB.
this kind has landed on my lap
"Aaron Bertrand [MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OQi6PFRCHHA.4908@.TK2MSFTNGP03.phx.gbl...
> Wow, I don't think I've come across anyone successfully running SQL Server
> on a blade, but hey, all the best.
> Actually I'm sitting with Geoff at PASS and he said that, unfortunately,
> he has seen this, and it didn't work out too well. But again, all the
> best. If it works out, please let us know. :-)
>
>
> "Lee J" <faked@.fc.com> wrote in message
> news:ejg8fo$5s5@.dispatch.concentric.net...
>> Great, Thank you, I am looking to be buying some new blade servers
>>
>> "Aaron Bertrand [MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> news:u7yEr9QCHHA.5064@.TK2MSFTNGP02.phx.gbl...
>> Yes, you can have multiple databases on the same machine, and on the
>> same instance of SQL Server. Your limitations are: (a) two databases
>> can't have the same name, or expect their data/log files to have the
>> same path, and (b) the available resources on the server. If it's a
>> low-powered box it may perform acceptably hosting one application's
>> database
>> As for the right way to perform the consolidation, if you are
>> comfortable that it is the right move, there are several options.
>> Andrew Kelly has posted this list of KB articles that should help you
>> out:
>> http://tinyurl.com/up5ok
>>
>> "Lee J" <faked@.fc.com> wrote in message
>> news:ejg6to$5ap@.dispatch.concentric.net...
>> Total newbie question i know.
>> The company i just started working for has SQL 2000 installed on four
>> machines. Is there any info i can get about putting all database on one
>> machine, Then having an application server running the apps (Great
>> Plains 7.5, Sharepoint, track-it, and a custom made database)
>> Thanks
>> Lee
>>
>>
>

Multable database on one machine

much luck with IndexDefrag?

So I started working for a place that hasn't done any sort of ReIndexing for
at least a year, perhaps longer. We are a 24x7 shop so the idea of suddenly
going to DBReIndex freaks people out. I've never done IndexDefrag, but I
have the higher up's convinced to let me take a crack at it. Does anyone use
this regularly? I would imagine yes do to the benifits it has over
DBReIndex. But do you guys get many benifits from it? Does it defrag enough
to warrant the time spent on it?
Also, I would imagine that if I do it at the slowest times of day, it would
have a greater affect? Am I incorrect in that thinking?
TIA, ChrisR
It's not as thorough as DBCC DBREINDEX but it does have it's benefits
particularly
for your situation (24x7). It doesn't lock indexes like DBCC DBREINDEX
which will help with concurrency. However, note it doesn't deal with extent
hops so it doesn't remove all fragmentation.
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
for
> at least a year, perhaps longer. We are a 24x7 shop so the idea of
suddenly
> going to DBReIndex freaks people out. I've never done IndexDefrag, but I
> have the higher up's convinced to let me take a crack at it. Does anyone
use
> this regularly? I would imagine yes do to the benifits it has over
> DBReIndex. But do you guys get many benifits from it? Does it defrag
enough
> to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
would
> have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>
|||Thanks Armando. Do you know if it would help to deo at slower times, or does
it not make a difference?
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:e7JBzh1dFHA.3620@.TK2MSFTNGP09.phx.gbl...
> It's not as thorough as DBCC DBREINDEX but it does have it's benefits
> particularly
> for your situation (24x7). It doesn't lock indexes like DBCC DBREINDEX
> which will help with concurrency. However, note it doesn't deal with
> extent
> hops so it doesn't remove all fragmentation.
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> for
> suddenly
> use
> enough
> would
>
|||Your shop must be really small to not have defragged nor reindexed for that
long.
As Amando stated, defrag is an online process. You can run it with the db
used. Reindex will take the table out of use while doing it.
Since defrag is an online process, time spent there is not too much a big
deal. It would compete for system resources with your transactions, so
choose a slow time is asvisable. At slow times the defrag would go faster,
your transactions will be less affected, but the benefit of the defrag is
independent of when it is run.
hth
Quentin
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
> for at least a year, perhaps longer. We are a 24x7 shop so the idea of
> suddenly going to DBReIndex freaks people out. I've never done
> IndexDefrag, but I have the higher up's convinced to let me take a crack
> at it. Does anyone use this regularly? I would imagine yes do to the
> benifits it has over DBReIndex. But do you guys get many benifits from it?
> Does it defrag enough to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
> would have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>
|||you will experience maybe 10% performance impact during the indexDefrag
Notes:
This is not a trivial undertaking
1. since it's been so long since your db has been defragged, you'll probably
want to do a DBReindex FIRST to clean out the cobwebs. Otherwise, the index
defrag will take VERY long and will Log to the Transaction logs massively.
Because of this, I would shoot for a downtime window to do a DBReindex to
get yourself back in line
AFTER That, I would regularly schedule indexDefrags.
keep in mind that indexDefrag does log activity so your T-logs will grow.
You'll want to make sure that T-Logs are backed up very regularly to avoid
blowing logspace, etc
some DBAs put the Database into Simple Recovery Mode or during the
maintenance window. I am not an advocate of that. I prefer to actively
manage the logspace.
the indexdefrag will defrag indexes enough for a 24x7 shop. IF you ever have
downtime windows in the future, that wouild be a great time to do a
DBReindex again (I would argue that it would not really be necessary to do
this...just a "Nice To Have").
Cheers
Greg Jackson
PDX, Oregon
|||You might find this white paper on index defragmenting useful.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
> for at least a year, perhaps longer. We are a 24x7 shop so the idea of
> suddenly going to DBReIndex freaks people out. I've never done
> IndexDefrag, but I have the higher up's convinced to let me take a crack
> at it. Does anyone use this regularly? I would imagine yes do to the
> benifits it has over DBReIndex. But do you guys get many benifits from it?
> Does it defrag enough to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
> would have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>
|||I'd run it during slower periods of activity so there are no contention
issues. That's when I personally do all maintenance.
Also, I found this link that gives a great overview of both methods
http://www.microsoft.com/technet/pro...idbp.mspx#EEAA
"ChrisR" <noemail@.bla.com> wrote in message
news:%23QMC091dFHA.3488@.tk2msftngp13.phx.gbl...
> Thanks Armando. Do you know if it would help to deo at slower times, or
does[vbcol=seagreen]
> it not make a difference?
>
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:e7JBzh1dFHA.3620@.TK2MSFTNGP09.phx.gbl...
ReIndexing[vbcol=seagreen]
I[vbcol=seagreen]
anyone
>
|||It's not huge... but I've definately worked in smaller. We have 6 production
servers. 2 main db's each box ranging from 10 to 90 gigs each. There was no
DBA for 8 months before my arrival and I'm not sure if the last one was
pro-active with stuff like this or not.
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:%23irhWJ2dFHA.3932@.TK2MSFTNGP12.phx.gbl...
> Your shop must be really small to not have defragged nor reindexed for
> that long.
> As Amando stated, defrag is an online process. You can run it with the db
> used. Reindex will take the table out of use while doing it.
> Since defrag is an online process, time spent there is not too much a big
> deal. It would compete for system resources with your transactions, so
> choose a slow time is asvisable. At slow times the defrag would go
> faster, your transactions will be less affected, but the benefit of the
> defrag is independent of when it is run.
> hth
> Quentin
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
>

much luck with IndexDefrag?

So I started working for a place that hasn't done any sort of ReIndexing for
at least a year, perhaps longer. We are a 24x7 shop so the idea of suddenly
going to DBReIndex freaks people out. I've never done IndexDefrag, but I
have the higher up's convinced to let me take a crack at it. Does anyone use
this regularly? I would imagine yes do to the benifits it has over
DBReIndex. But do you guys get many benifits from it? Does it defrag enough
to warrant the time spent on it?
Also, I would imagine that if I do it at the slowest times of day, it would
have a greater affect? Am I incorrect in that thinking?
TIA, ChrisRIt's not as thorough as DBCC DBREINDEX but it does have it's benefits
particularly
for your situation (24x7). It doesn't lock indexes like DBCC DBREINDEX
which will help with concurrency. However, note it doesn't deal with extent
hops so it doesn't remove all fragmentation.
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
for
> at least a year, perhaps longer. We are a 24x7 shop so the idea of
suddenly
> going to DBReIndex freaks people out. I've never done IndexDefrag, but I
> have the higher up's convinced to let me take a crack at it. Does anyone
use
> this regularly? I would imagine yes do to the benifits it has over
> DBReIndex. But do you guys get many benifits from it? Does it defrag
enough
> to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
would
> have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>|||Thanks Armando. Do you know if it would help to deo at slower times, or does
it not make a difference?
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:e7JBzh1dFHA.3620@.TK2MSFTNGP09.phx.gbl...
> It's not as thorough as DBCC DBREINDEX but it does have it's benefits
> particularly
> for your situation (24x7). It doesn't lock indexes like DBCC DBREINDEX
> which will help with concurrency. However, note it doesn't deal with
> extent
> hops so it doesn't remove all fragmentation.
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> for
> suddenly
> use
> enough
> would
>|||Your shop must be really small to not have defragged nor reindexed for that
long.
As Amando stated, defrag is an online process. You can run it with the db
used. Reindex will take the table out of use while doing it.
Since defrag is an online process, time spent there is not too much a big
deal. It would compete for system resources with your transactions, so
choose a slow time is asvisable. At slow times the defrag would go faster,
your transactions will be less affected, but the benefit of the defrag is
independent of when it is run.
hth
Quentin
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
> for at least a year, perhaps longer. We are a 24x7 shop so the idea of
> suddenly going to DBReIndex freaks people out. I've never done
> IndexDefrag, but I have the higher up's convinced to let me take a crack
> at it. Does anyone use this regularly? I would imagine yes do to the
> benifits it has over DBReIndex. But do you guys get many benifits from it?
> Does it defrag enough to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
> would have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>|||you will experience maybe 10% performance impact during the indexDefrag
Notes:
This is not a trivial undertaking
1. since it's been so long since your db has been defragged, you'll probably
want to do a DBReindex FIRST to clean out the cobwebs. Otherwise, the index
defrag will take VERY long and will Log to the Transaction logs massively.
Because of this, I would shoot for a downtime window to do a DBReindex to
get yourself back in line
AFTER That, I would regularly schedule indexDefrags.
keep in mind that indexDefrag does log activity so your T-logs will grow.
You'll want to make sure that T-Logs are backed up very regularly to avoid
blowing logspace, etc
some DBAs put the Database into Simple Recovery Mode or during the
maintenance window. I am not an advocate of that. I prefer to actively
manage the logspace.
the indexdefrag will defrag indexes enough for a 24x7 shop. IF you ever have
downtime windows in the future, that wouild be a great time to do a
DBReindex again (I would argue that it would not really be necessary to do
this...just a "Nice To Have").
Cheers
Greg Jackson
PDX, Oregon|||You might find this white paper on index defragmenting useful.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
> for at least a year, perhaps longer. We are a 24x7 shop so the idea of
> suddenly going to DBReIndex freaks people out. I've never done
> IndexDefrag, but I have the higher up's convinced to let me take a crack
> at it. Does anyone use this regularly? I would imagine yes do to the
> benifits it has over DBReIndex. But do you guys get many benifits from it?
> Does it defrag enough to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
> would have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>|||I'd run it during slower periods of activity so there are no contention
issues. That's when I personally do all maintenance.
Also, I found this link that gives a great overview of both methods
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx#EEAA[
/url]
"ChrisR" <noemail@.bla.com> wrote in message
news:%23QMC091dFHA.3488@.tk2msftngp13.phx.gbl...
> Thanks Armando. Do you know if it would help to deo at slower times, or
does
> it not make a difference?
>
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:e7JBzh1dFHA.3620@.TK2MSFTNGP09.phx.gbl...
ReIndexing[vbcol=seagreen]
I[vbcol=seagreen]
anyone[vbcol=seagreen]
>|||It's not huge... but I've definately worked in smaller. We have 6 production
servers. 2 main db's each box ranging from 10 to 90 gigs each. There was no
DBA for 8 months before my arrival and I'm not sure if the last one was
pro-active with stuff like this or not.
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:%23irhWJ2dFHA.3932@.TK2MSFTNGP12.phx.gbl...
> Your shop must be really small to not have defragged nor reindexed for
> that long.
> As Amando stated, defrag is an online process. You can run it with the db
> used. Reindex will take the table out of use while doing it.
> Since defrag is an online process, time spent there is not too much a big
> deal. It would compete for system resources with your transactions, so
> choose a slow time is asvisable. At slow times the defrag would go
> faster, your transactions will be less affected, but the benefit of the
> defrag is independent of when it is run.
> hth
> Quentin
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
>

much luck with IndexDefrag?

So I started working for a place that hasn't done any sort of ReIndexing for
at least a year, perhaps longer. We are a 24x7 shop so the idea of suddenly
going to DBReIndex freaks people out. I've never done IndexDefrag, but I
have the higher up's convinced to let me take a crack at it. Does anyone use
this regularly? I would imagine yes do to the benifits it has over
DBReIndex. But do you guys get many benifits from it? Does it defrag enough
to warrant the time spent on it?
Also, I would imagine that if I do it at the slowest times of day, it would
have a greater affect? Am I incorrect in that thinking?
TIA, ChrisRIt's not as thorough as DBCC DBREINDEX but it does have it's benefits
particularly
for your situation (24x7). It doesn't lock indexes like DBCC DBREINDEX
which will help with concurrency. However, note it doesn't deal with extent
hops so it doesn't remove all fragmentation.
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
for
> at least a year, perhaps longer. We are a 24x7 shop so the idea of
suddenly
> going to DBReIndex freaks people out. I've never done IndexDefrag, but I
> have the higher up's convinced to let me take a crack at it. Does anyone
use
> this regularly? I would imagine yes do to the benifits it has over
> DBReIndex. But do you guys get many benifits from it? Does it defrag
enough
> to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
would
> have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>|||Thanks Armando. Do you know if it would help to deo at slower times, or does
it not make a difference?
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:e7JBzh1dFHA.3620@.TK2MSFTNGP09.phx.gbl...
> It's not as thorough as DBCC DBREINDEX but it does have it's benefits
> particularly
> for your situation (24x7). It doesn't lock indexes like DBCC DBREINDEX
> which will help with concurrency. However, note it doesn't deal with
> extent
> hops so it doesn't remove all fragmentation.
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> So I started working for a place that hasn't done any sort of ReIndexing
> for
>> at least a year, perhaps longer. We are a 24x7 shop so the idea of
> suddenly
>> going to DBReIndex freaks people out. I've never done IndexDefrag, but I
>> have the higher up's convinced to let me take a crack at it. Does anyone
> use
>> this regularly? I would imagine yes do to the benifits it has over
>> DBReIndex. But do you guys get many benifits from it? Does it defrag
> enough
>> to warrant the time spent on it?
>> Also, I would imagine that if I do it at the slowest times of day, it
> would
>> have a greater affect? Am I incorrect in that thinking?
>>
>> TIA, ChrisR
>>
>>
>|||Your shop must be really small to not have defragged nor reindexed for that
long.
As Amando stated, defrag is an online process. You can run it with the db
used. Reindex will take the table out of use while doing it.
Since defrag is an online process, time spent there is not too much a big
deal. It would compete for system resources with your transactions, so
choose a slow time is asvisable. At slow times the defrag would go faster,
your transactions will be less affected, but the benefit of the defrag is
independent of when it is run.
hth
Quentin
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
> for at least a year, perhaps longer. We are a 24x7 shop so the idea of
> suddenly going to DBReIndex freaks people out. I've never done
> IndexDefrag, but I have the higher up's convinced to let me take a crack
> at it. Does anyone use this regularly? I would imagine yes do to the
> benifits it has over DBReIndex. But do you guys get many benifits from it?
> Does it defrag enough to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
> would have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>|||you will experience maybe 10% performance impact during the indexDefrag
Notes:
This is not a trivial undertaking
1. since it's been so long since your db has been defragged, you'll probably
want to do a DBReindex FIRST to clean out the cobwebs. Otherwise, the index
defrag will take VERY long and will Log to the Transaction logs massively.
Because of this, I would shoot for a downtime window to do a DBReindex to
get yourself back in line
AFTER That, I would regularly schedule indexDefrags.
keep in mind that indexDefrag does log activity so your T-logs will grow.
You'll want to make sure that T-Logs are backed up very regularly to avoid
blowing logspace, etc
some DBAs put the Database into Simple Recovery Mode or during the
maintenance window. I am not an advocate of that. I prefer to actively
manage the logspace.
the indexdefrag will defrag indexes enough for a 24x7 shop. IF you ever have
downtime windows in the future, that wouild be a great time to do a
DBReindex again (I would argue that it would not really be necessary to do
this...just a "Nice To Have").
Cheers
Greg Jackson
PDX, Oregon|||You might find this white paper on index defragmenting useful.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"ChrisR" <noemail@.bla.com> wrote in message
news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> So I started working for a place that hasn't done any sort of ReIndexing
> for at least a year, perhaps longer. We are a 24x7 shop so the idea of
> suddenly going to DBReIndex freaks people out. I've never done
> IndexDefrag, but I have the higher up's convinced to let me take a crack
> at it. Does anyone use this regularly? I would imagine yes do to the
> benifits it has over DBReIndex. But do you guys get many benifits from it?
> Does it defrag enough to warrant the time spent on it?
> Also, I would imagine that if I do it at the slowest times of day, it
> would have a greater affect? Am I incorrect in that thinking?
>
> TIA, ChrisR
>
>|||I'd run it during slower periods of activity so there are no contention
issues. That's when I personally do all maintenance.
Also, I found this link that gives a great overview of both methods
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx#EEAA
"ChrisR" <noemail@.bla.com> wrote in message
news:%23QMC091dFHA.3488@.tk2msftngp13.phx.gbl...
> Thanks Armando. Do you know if it would help to deo at slower times, or
does
> it not make a difference?
>
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:e7JBzh1dFHA.3620@.TK2MSFTNGP09.phx.gbl...
> > It's not as thorough as DBCC DBREINDEX but it does have it's benefits
> > particularly
> > for your situation (24x7). It doesn't lock indexes like DBCC DBREINDEX
> > which will help with concurrency. However, note it doesn't deal with
> > extent
> > hops so it doesn't remove all fragmentation.
> >
> >
> > "ChrisR" <noemail@.bla.com> wrote in message
> > news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
> >> So I started working for a place that hasn't done any sort of
ReIndexing
> > for
> >> at least a year, perhaps longer. We are a 24x7 shop so the idea of
> > suddenly
> >> going to DBReIndex freaks people out. I've never done IndexDefrag, but
I
> >> have the higher up's convinced to let me take a crack at it. Does
anyone
> > use
> >> this regularly? I would imagine yes do to the benifits it has over
> >> DBReIndex. But do you guys get many benifits from it? Does it defrag
> > enough
> >> to warrant the time spent on it?
> >> Also, I would imagine that if I do it at the slowest times of day, it
> > would
> >> have a greater affect? Am I incorrect in that thinking?
> >>
> >>
> >> TIA, ChrisR
> >>
> >>
> >>
> >>
> >
> >
>|||It's not huge... but I've definately worked in smaller. We have 6 production
servers. 2 main db's each box ranging from 10 to 90 gigs each. There was no
DBA for 8 months before my arrival and I'm not sure if the last one was
pro-active with stuff like this or not.
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:%23irhWJ2dFHA.3932@.TK2MSFTNGP12.phx.gbl...
> Your shop must be really small to not have defragged nor reindexed for
> that long.
> As Amando stated, defrag is an online process. You can run it with the db
> used. Reindex will take the table out of use while doing it.
> Since defrag is an online process, time spent there is not too much a big
> deal. It would compete for system resources with your transactions, so
> choose a slow time is asvisable. At slow times the defrag would go
> faster, your transactions will be less affected, but the benefit of the
> defrag is independent of when it is run.
> hth
> Quentin
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%238PWdb1dFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> So I started working for a place that hasn't done any sort of ReIndexing
>> for at least a year, perhaps longer. We are a 24x7 shop so the idea of
>> suddenly going to DBReIndex freaks people out. I've never done
>> IndexDefrag, but I have the higher up's convinced to let me take a crack
>> at it. Does anyone use this regularly? I would imagine yes do to the
>> benifits it has over DBReIndex. But do you guys get many benifits from
>> it? Does it defrag enough to warrant the time spent on it?
>> Also, I would imagine that if I do it at the slowest times of day, it
>> would have a greater affect? Am I incorrect in that thinking?
>>
>> TIA, ChrisR
>>
>>
>

Friday, March 9, 2012

MSSQLOLAP Service stuck in a loop

Hi
I had installed the SP3a patch for Analysis Services.
Everything was working fine for a day and then now when I
try restarting the MSSQLOLAPService it gets into a loop
and automatically gets restarted again and again.
I am not very sure whether this is because of the SP3a
installation.
I have encountered the same on a few other desktops
running windows xp and a server running win 2000 advanced
server.
Any clue of where have I gone wrong?
Any sort of help would be appreciated!!
thanx in advance,
Lakshman.
On Mon, 12 Apr 2004 15:05:20 -0700, "Lakshman"
<babulaksh@.rediffmail.com> wrote:

>Hi
>I had installed the SP3a patch for Analysis Services.
>Everything was working fine for a day and then now when I
>try restarting the MSSQLOLAPService it gets into a loop
>and automatically gets restarted again and again.
>I am not very sure whether this is because of the SP3a
>installation.
I remember this happening to me and I wish I could be sure but I seem
to remember that it had something to do with the service pack.
Possibly a mismatch between the machine that had AS and another
machine that was hitting it...

>I have encountered the same on a few other desktops
>running windows xp and a server running win 2000 advanced
>server.
>Any clue of where have I gone wrong?
>Any sort of help would be appreciated!!
>thanx in advance,
>Lakshman.

MSSQLOLAP Service stuck in a loop

Hi
I had installed the SP3a patch for Analysis Services.
Everything was working fine for a day and then now when I
try restarting the MSSQLOLAPService it gets into a loop
and automatically gets restarted again and again.
I am not very sure whether this is because of the SP3a
installation.
I have encountered the same on a few other desktops
running Windows XP and a server running win 2000 advanced
server.
Any clue of where have I gone wrong?
Any sort of help would be appreciated!!
thanx in advance,
Lakshman.On Mon, 12 Apr 2004 15:05:20 -0700, "Lakshman"
<babulaksh@.rediffmail.com> wrote:

>Hi
>I had installed the SP3a patch for Analysis Services.
>Everything was working fine for a day and then now when I
>try restarting the MSSQLOLAPService it gets into a loop
>and automatically gets restarted again and again.
>I am not very sure whether this is because of the SP3a
>installation.
I remember this happening to me and I wish I could be sure but I seem
to remember that it had something to do with the service pack.
Possibly a mismatch between the machine that had AS and another
machine that was hitting it...

>I have encountered the same on a few other desktops
>running Windows XP and a server running win 2000 advanced
>server.
>Any clue of where have I gone wrong?
>Any sort of help would be appreciated!!
>thanx in advance,
>Lakshman.

Wednesday, March 7, 2012

mssql wont find NULL values in datetime field?

Hi

I have a really simple query which i can't figure out why its not working. I have a table called 'ADMIN' which has a datetime field called 'date_edited'. Because the majority of records have never been edited, i have allowed null values and they are filled with 'NULL' in each record. How ever, when i try:

SELECT * FROM ADMIN WHERE date_edited = NULL

I get no records, but i can see and know i have hundreds! I know i'm doing somthing really stupid, but for life of me can't figure it out! :eek:

thanksignore me, found the answer

SELECT * FROM ADMIN WHERE date_edited is NULL|||Consider yourself ignored. ;)|||I'm ignoring him right now. Intensely.|||please dun treat newbies like that, we all started off as a newbie right?

maybe we shall have a newbie section for them to post elementary questions...

but seriously, this NULL question does look dumb......TS shall try harder before posting it......|||personally I still hate nulls and three part logic. It causes problems for so many programmers. I slap as many not null constraints in my software as I can. Of course I spend so much time fighting fires these days, I only get to do reactive development or redevelopment.|||ROTFL ... look at Sean's title in the above post ... i almost missed that.

Another contradictory Seanism :p|||Declare @.ZenosParadox varchar(50)
Set @.ZenosParadox = 'This value is null'|||I learned in math class that zeno's paradox was that you could never reach any destination because you first had to travel 1/2 the distance, then 1/2 again, then again, so you'd never reach it.

poor zeno didn't know an infinite series can sum to a finite number.|||http://en.wikipedia.org/wiki/Liar_paradox ?

:)|||NULL value is good, I dun need to be bothered about putting constraint while showing them on a report hehe...

Saturday, February 25, 2012

MSSQL Server Taskbar Icon

I just upgraded Win2000 to SP4 from SP3. I have SQL2000
Server on the a DELL 2600 PowerEdge and it was working
wonderfully. After the upgrade the green arrow on the icon
in the taskbar is missing. When I double click on the
icon, the server name is present but no services to
start. I checked services in Administrator Tools and they
are present and started. I can connect to the MSSQL
Server in Enterprise Manager and Query Analyzer. I
stopped and restarted the services in Administrator Tools
with no luck and rebooted. Any ideas'?Just wondering if the SQL Service Manager has lost focus from your machine
and is looking at a machine that doesn't have SQL installed.
Open the SQL Service Manager, ensure the Server :- pull down list is focused
on the correct machine.
--
HTH
Ryan Waight, MCDBA, MCSE
"Kerry" <kerry.ross@.wheelsus.gknplc.com> wrote in message
news:067c01c38a03$498ba790$a301280a@.phx.gbl...
> I just upgraded Win2000 to SP4 from SP3. I have SQL2000
> Server on the a DELL 2600 PowerEdge and it was working
> wonderfully. After the upgrade the green arrow on the icon
> in the taskbar is missing. When I double click on the
> icon, the server name is present but no services to
> start. I checked services in Administrator Tools and they
> are present and started. I can connect to the MSSQL
> Server in Enterprise Manager and Query Analyzer. I
> stopped and restarted the services in Administrator Tools
> with no luck and rebooted. Any ideas'?

Friday, March 30, 2012

Multi value parameter not working

Friday, March 23, 2012

Multi access in SQL Server

Multable database on one machine

Multable database on one machine

Multable database on one machine

much luck with IndexDefrag?

much luck with IndexDefrag?

much luck with IndexDefrag?

Friday, March 9, 2012

MSSQLOLAP Service stuck in a loop

MSSQLOLAP Service stuck in a loop

Wednesday, March 7, 2012

mssql wont find NULL values in datetime field?

Saturday, February 25, 2012

MSSQL Server Taskbar Icon

MSSQL Mistake

Blog Archive

About Me