this is a stupid question but..I have a sql server installed, how can I tell
how it is licensed? Meaning per processor or per client? Thanks for your he
lpSELECT SERVERPROPERTY('LicenseType');
If it is per seat you can also see:
SELECT SERVERPROPERTY('NumLicenses');
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"jennifer" <jennifer@.discussions.microsoft.com> wrote in message
news:58E2895C-8CAB-4142-BCA3-CAC7C9C91C64@.microsoft.com...
> this is a stupid question but..I have a sql server installed, how can I
> tell
> how it is licensed? Meaning per processor or per client? Thanks for your
> help
>|||"Aaron Bertrand [SQL Server MVP]" wrote:
> SELECT SERVERPROPERTY('LicenseType');
> If it is per seat you can also see:
> SELECT SERVERPROPERTY('NumLicenses');
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
>
> "jennifer" <jennifer@.discussions.microsoft.com> wrote in message
> news:58E2895C-8CAB-4142-BCA3-CAC7C9C91C64@.microsoft.com...
>
>|||Thanks so much just found it in control panel
now I know 2 ways.
Jennifer
"Aaron Bertrand [SQL Server MVP]" wrote:
> SELECT SERVERPROPERTY('LicenseType');
> If it is per seat you can also see:
> SELECT SERVERPROPERTY('NumLicenses');
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
>
> "jennifer" <jennifer@.discussions.microsoft.com> wrote in message
> news:58E2895C-8CAB-4142-BCA3-CAC7C9C91C64@.microsoft.com...
>
>
Showing posts with label identifying. Show all posts
Showing posts with label identifying. Show all posts
Sunday, February 19, 2012
identifying which license version installed
this is a stupid question but..I have a sql server installed, how can I tell
how it is licensed? Meaning per processor or per client? Thanks for your help
SELECT SERVERPROPERTY('LicenseType');
If it is per seat you can also see:
SELECT SERVERPROPERTY('NumLicenses');
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"jennifer" <jennifer@.discussions.microsoft.com> wrote in message
news:58E2895C-8CAB-4142-BCA3-CAC7C9C91C64@.microsoft.com...
> this is a stupid question but..I have a sql server installed, how can I
> tell
> how it is licensed? Meaning per processor or per client? Thanks for your
> help
>
|||Thanks so much just found it in control panel
now I know 2 ways.
Jennifer
"Aaron Bertrand [SQL Server MVP]" wrote:
> SELECT SERVERPROPERTY('LicenseType');
> If it is per seat you can also see:
> SELECT SERVERPROPERTY('NumLicenses');
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
>
> "jennifer" <jennifer@.discussions.microsoft.com> wrote in message
> news:58E2895C-8CAB-4142-BCA3-CAC7C9C91C64@.microsoft.com...
>
>
how it is licensed? Meaning per processor or per client? Thanks for your help
SELECT SERVERPROPERTY('LicenseType');
If it is per seat you can also see:
SELECT SERVERPROPERTY('NumLicenses');
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"jennifer" <jennifer@.discussions.microsoft.com> wrote in message
news:58E2895C-8CAB-4142-BCA3-CAC7C9C91C64@.microsoft.com...
> this is a stupid question but..I have a sql server installed, how can I
> tell
> how it is licensed? Meaning per processor or per client? Thanks for your
> help
>
|||Thanks so much just found it in control panel
now I know 2 ways.
Jennifer
"Aaron Bertrand [SQL Server MVP]" wrote:
> SELECT SERVERPROPERTY('LicenseType');
> If it is per seat you can also see:
> SELECT SERVERPROPERTY('NumLicenses');
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
>
> "jennifer" <jennifer@.discussions.microsoft.com> wrote in message
> news:58E2895C-8CAB-4142-BCA3-CAC7C9C91C64@.microsoft.com...
>
>
identifying which license version installed
this is a stupid question but..I have a sql server installed, how can I tell
how it is licensed? Meaning per processor or per client? Thanks for your helpSELECT SERVERPROPERTY('LicenseType');
If it is per seat you can also see:
SELECT SERVERPROPERTY('NumLicenses');
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"jennifer" <jennifer@.discussions.microsoft.com> wrote in message
news:58E2895C-8CAB-4142-BCA3-CAC7C9C91C64@.microsoft.com...
> this is a stupid question but..I have a sql server installed, how can I
> tell
> how it is licensed? Meaning per processor or per client? Thanks for your
> help
>|||Thanks so much just found it in control panel
now I know 2 ways.
Jennifer
"Aaron Bertrand [SQL Server MVP]" wrote:
> SELECT SERVERPROPERTY('LicenseType');
> If it is per seat you can also see:
> SELECT SERVERPROPERTY('NumLicenses');
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
>
> "jennifer" <jennifer@.discussions.microsoft.com> wrote in message
> news:58E2895C-8CAB-4142-BCA3-CAC7C9C91C64@.microsoft.com...
> > this is a stupid question but..I have a sql server installed, how can I
> > tell
> > how it is licensed? Meaning per processor or per client? Thanks for your
> > help
> >
> >
>
>|||"Aaron Bertrand [SQL Server MVP]" wrote:
> SELECT SERVERPROPERTY('LicenseType');
> If it is per seat you can also see:
> SELECT SERVERPROPERTY('NumLicenses');
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
>
> "jennifer" <jennifer@.discussions.microsoft.com> wrote in message
> news:58E2895C-8CAB-4142-BCA3-CAC7C9C91C64@.microsoft.com...
> > this is a stupid question but..I have a sql server installed, how can I
> > tell
> > how it is licensed? Meaning per processor or per client? Thanks for your
> > help
> >
> >
>
>
how it is licensed? Meaning per processor or per client? Thanks for your helpSELECT SERVERPROPERTY('LicenseType');
If it is per seat you can also see:
SELECT SERVERPROPERTY('NumLicenses');
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"jennifer" <jennifer@.discussions.microsoft.com> wrote in message
news:58E2895C-8CAB-4142-BCA3-CAC7C9C91C64@.microsoft.com...
> this is a stupid question but..I have a sql server installed, how can I
> tell
> how it is licensed? Meaning per processor or per client? Thanks for your
> help
>|||Thanks so much just found it in control panel
now I know 2 ways.
Jennifer
"Aaron Bertrand [SQL Server MVP]" wrote:
> SELECT SERVERPROPERTY('LicenseType');
> If it is per seat you can also see:
> SELECT SERVERPROPERTY('NumLicenses');
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
>
> "jennifer" <jennifer@.discussions.microsoft.com> wrote in message
> news:58E2895C-8CAB-4142-BCA3-CAC7C9C91C64@.microsoft.com...
> > this is a stupid question but..I have a sql server installed, how can I
> > tell
> > how it is licensed? Meaning per processor or per client? Thanks for your
> > help
> >
> >
>
>|||"Aaron Bertrand [SQL Server MVP]" wrote:
> SELECT SERVERPROPERTY('LicenseType');
> If it is per seat you can also see:
> SELECT SERVERPROPERTY('NumLicenses');
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
>
> "jennifer" <jennifer@.discussions.microsoft.com> wrote in message
> news:58E2895C-8CAB-4142-BCA3-CAC7C9C91C64@.microsoft.com...
> > this is a stupid question but..I have a sql server installed, how can I
> > tell
> > how it is licensed? Meaning per processor or per client? Thanks for your
> > help
> >
> >
>
>
Identifying what Apps are using a database
I'm looking for a little insight on identifying what apps
are updating a particular database. Basically we have a
front end developed for the db but some people may have
created Access dbs and processes in Access to do updates.
I need to determine if this is happening since we may be
moving the db to a different sql server. SQL Profiler
would work, but it's hit and miss. Plus I'd have to let
it run for a few days which would create a huge trace
file. If I did let it run for a few days, I still may not
capture and activity from Access if they didn't happen to
run any Access updates during that time. Is there
anything out there that can help me?
Thanks,
Van Jones
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:012401c4f75f$6fe0e520$a601280a@.phx.gbl...
> I'm looking for a little insight on identifying what apps
> are updating a particular database. Basically we have a
> front end developed for the db but some people may have
> created Access dbs and processes in Access to do updates.
> I need to determine if this is happening since we may be
> moving the db to a different sql server. SQL Profiler
> would work, but it's hit and miss. Plus I'd have to let
> it run for a few days which would create a huge trace
> file. If I did let it run for a few days, I still may not
> capture and activity from Access if they didn't happen to
> run any Access updates during that time. Is there
> anything out there that can help me?
> Thanks,
> Van Jones
I would still go with Profiler. Take a look at the Filters tab. You can
do things like: NOT LIKE and add all of the known applications that will
log in. You can weed out just about all the activity that you don't want
to see. Then your trace file will not be enormous.
The other suggestion is to turn on profiler and ask everyone to hit the
server with their updates so that you can capture everything in one go.
Rick Sawtell
MCT, MCSD, MCDBA
|||
>--Original Message--
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:012401c4f75f$6fe0e520$a601280a@.phx.gbl...
apps[vbcol=seagreen]
updates.[vbcol=seagreen]
not[vbcol=seagreen]
to
>
>I would still go with Profiler. Take a look at the
Filters tab. You can
>do things like: NOT LIKE and add all of the known
applications that will
>log in. You can weed out just about all the activity
that you don't want
>to see. Then your trace file will not be enormous.
>The other suggestion is to turn on profiler and ask
everyone to hit the
>server with their updates so that you can capture
everything in one go.
>
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>.
Yea, I figure profiler might be the only way. The problem
is...is that I don't know if anybody has any Access
processes that update the db. I've been told that they
might. If they do, it could be quartly or even year end
processes that I wouldn't capture if I ran a trace for a
few days.
|||"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:1d5c01c4f761$bc059f70$a401280a@.phx.gbl...
> in message
> apps
> updates.
> not
> to
> Filters tab. You can
> applications that will
> that you don't want
> everyone to hit the
> everything in one go.
> Yea, I figure profiler might be the only way. The problem
> is...is that I don't know if anybody has any Access
> processes that update the db. I've been told that they
> might. If they do, it could be quartly or even year end
> processes that I wouldn't capture if I ran a trace for a
> few days.
Can you take an inventory of the applications that are hitting your
database? You should have this list regardless.
Another method. (Muahahahaha) Change the login credentials.
Rick Sawtell
MCT, MCSD, MCDBA
are updating a particular database. Basically we have a
front end developed for the db but some people may have
created Access dbs and processes in Access to do updates.
I need to determine if this is happening since we may be
moving the db to a different sql server. SQL Profiler
would work, but it's hit and miss. Plus I'd have to let
it run for a few days which would create a huge trace
file. If I did let it run for a few days, I still may not
capture and activity from Access if they didn't happen to
run any Access updates during that time. Is there
anything out there that can help me?
Thanks,
Van Jones
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:012401c4f75f$6fe0e520$a601280a@.phx.gbl...
> I'm looking for a little insight on identifying what apps
> are updating a particular database. Basically we have a
> front end developed for the db but some people may have
> created Access dbs and processes in Access to do updates.
> I need to determine if this is happening since we may be
> moving the db to a different sql server. SQL Profiler
> would work, but it's hit and miss. Plus I'd have to let
> it run for a few days which would create a huge trace
> file. If I did let it run for a few days, I still may not
> capture and activity from Access if they didn't happen to
> run any Access updates during that time. Is there
> anything out there that can help me?
> Thanks,
> Van Jones
I would still go with Profiler. Take a look at the Filters tab. You can
do things like: NOT LIKE and add all of the known applications that will
log in. You can weed out just about all the activity that you don't want
to see. Then your trace file will not be enormous.
The other suggestion is to turn on profiler and ask everyone to hit the
server with their updates so that you can capture everything in one go.
Rick Sawtell
MCT, MCSD, MCDBA
|||
>--Original Message--
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:012401c4f75f$6fe0e520$a601280a@.phx.gbl...
apps[vbcol=seagreen]
updates.[vbcol=seagreen]
not[vbcol=seagreen]
to
>
>I would still go with Profiler. Take a look at the
Filters tab. You can
>do things like: NOT LIKE and add all of the known
applications that will
>log in. You can weed out just about all the activity
that you don't want
>to see. Then your trace file will not be enormous.
>The other suggestion is to turn on profiler and ask
everyone to hit the
>server with their updates so that you can capture
everything in one go.
>
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>.
Yea, I figure profiler might be the only way. The problem
is...is that I don't know if anybody has any Access
processes that update the db. I've been told that they
might. If they do, it could be quartly or even year end
processes that I wouldn't capture if I ran a trace for a
few days.
|||"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:1d5c01c4f761$bc059f70$a401280a@.phx.gbl...
> in message
> apps
> updates.
> not
> to
> Filters tab. You can
> applications that will
> that you don't want
> everyone to hit the
> everything in one go.
> Yea, I figure profiler might be the only way. The problem
> is...is that I don't know if anybody has any Access
> processes that update the db. I've been told that they
> might. If they do, it could be quartly or even year end
> processes that I wouldn't capture if I ran a trace for a
> few days.
Can you take an inventory of the applications that are hitting your
database? You should have this list regardless.
Another method. (Muahahahaha) Change the login credentials.
Rick Sawtell
MCT, MCSD, MCDBA
Identifying what Apps are using a database
I'm looking for a little insight on identifying what apps
are updating a particular database. Basically we have a
front end developed for the db but some people may have
created Access dbs and processes in Access to do updates.
I need to determine if this is happening since we may be
moving the db to a different sql server. SQL Profiler
would work, but it's hit and miss. Plus I'd have to let
it run for a few days which would create a huge trace
file. If I did let it run for a few days, I still may not
capture and activity from Access if they didn't happen to
run any Access updates during that time. Is there
anything out there that can help me?
Thanks,
Van Jones"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:012401c4f75f$6fe0e520$a601280a@.phx.gbl...
> I'm looking for a little insight on identifying what apps
> are updating a particular database. Basically we have a
> front end developed for the db but some people may have
> created Access dbs and processes in Access to do updates.
> I need to determine if this is happening since we may be
> moving the db to a different sql server. SQL Profiler
> would work, but it's hit and miss. Plus I'd have to let
> it run for a few days which would create a huge trace
> file. If I did let it run for a few days, I still may not
> capture and activity from Access if they didn't happen to
> run any Access updates during that time. Is there
> anything out there that can help me?
> Thanks,
> Van Jones
I would still go with Profiler. Take a look at the Filters tab. You can
do things like: NOT LIKE and add all of the known applications that will
log in. You can weed out just about all the activity that you don't want
to see. Then your trace file will not be enormous.
The other suggestion is to turn on profiler and ask everyone to hit the
server with their updates so that you can capture everything in one go.
Rick Sawtell
MCT, MCSD, MCDBA|||>--Original Message--
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote
in message
>news:012401c4f75f$6fe0e520$a601280a@.phx.gbl...
>> I'm looking for a little insight on identifying what
apps
>> are updating a particular database. Basically we have a
>> front end developed for the db but some people may have
>> created Access dbs and processes in Access to do
updates.
>> I need to determine if this is happening since we may be
>> moving the db to a different sql server. SQL Profiler
>> would work, but it's hit and miss. Plus I'd have to let
>> it run for a few days which would create a huge trace
>> file. If I did let it run for a few days, I still may
not
>> capture and activity from Access if they didn't happen
to
>> run any Access updates during that time. Is there
>> anything out there that can help me?
>> Thanks,
>> Van Jones
>
>I would still go with Profiler. Take a look at the
Filters tab. You can
>do things like: NOT LIKE and add all of the known
applications that will
>log in. You can weed out just about all the activity
that you don't want
>to see. Then your trace file will not be enormous.
>The other suggestion is to turn on profiler and ask
everyone to hit the
>server with their updates so that you can capture
everything in one go.
>
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>.
Yea, I figure profiler might be the only way. The problem
is...is that I don't know if anybody has any Access
processes that update the db. I've been told that they
might. If they do, it could be quartly or even year end
processes that I wouldn't capture if I ran a trace for a
few days.|||"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:1d5c01c4f761$bc059f70$a401280a@.phx.gbl...
>>--Original Message--
>>"Van Jones" <anonymous@.discussions.microsoft.com> wrote
> in message
>>news:012401c4f75f$6fe0e520$a601280a@.phx.gbl...
>> I'm looking for a little insight on identifying what
> apps
>> are updating a particular database. Basically we have a
>> front end developed for the db but some people may have
>> created Access dbs and processes in Access to do
> updates.
>> I need to determine if this is happening since we may be
>> moving the db to a different sql server. SQL Profiler
>> would work, but it's hit and miss. Plus I'd have to let
>> it run for a few days which would create a huge trace
>> file. If I did let it run for a few days, I still may
> not
>> capture and activity from Access if they didn't happen
> to
>> run any Access updates during that time. Is there
>> anything out there that can help me?
>> Thanks,
>> Van Jones
>>
>>I would still go with Profiler. Take a look at the
> Filters tab. You can
>>do things like: NOT LIKE and add all of the known
> applications that will
>>log in. You can weed out just about all the activity
> that you don't want
>>to see. Then your trace file will not be enormous.
>>The other suggestion is to turn on profiler and ask
> everyone to hit the
>>server with their updates so that you can capture
> everything in one go.
>>
>>Rick Sawtell
>>MCT, MCSD, MCDBA
>>
>>.
> Yea, I figure profiler might be the only way. The problem
> is...is that I don't know if anybody has any Access
> processes that update the db. I've been told that they
> might. If they do, it could be quartly or even year end
> processes that I wouldn't capture if I ran a trace for a
> few days.
Can you take an inventory of the applications that are hitting your
database? You should have this list regardless.
Another method. (Muahahahaha) Change the login credentials.
Rick Sawtell
MCT, MCSD, MCDBA
are updating a particular database. Basically we have a
front end developed for the db but some people may have
created Access dbs and processes in Access to do updates.
I need to determine if this is happening since we may be
moving the db to a different sql server. SQL Profiler
would work, but it's hit and miss. Plus I'd have to let
it run for a few days which would create a huge trace
file. If I did let it run for a few days, I still may not
capture and activity from Access if they didn't happen to
run any Access updates during that time. Is there
anything out there that can help me?
Thanks,
Van Jones"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:012401c4f75f$6fe0e520$a601280a@.phx.gbl...
> I'm looking for a little insight on identifying what apps
> are updating a particular database. Basically we have a
> front end developed for the db but some people may have
> created Access dbs and processes in Access to do updates.
> I need to determine if this is happening since we may be
> moving the db to a different sql server. SQL Profiler
> would work, but it's hit and miss. Plus I'd have to let
> it run for a few days which would create a huge trace
> file. If I did let it run for a few days, I still may not
> capture and activity from Access if they didn't happen to
> run any Access updates during that time. Is there
> anything out there that can help me?
> Thanks,
> Van Jones
I would still go with Profiler. Take a look at the Filters tab. You can
do things like: NOT LIKE and add all of the known applications that will
log in. You can weed out just about all the activity that you don't want
to see. Then your trace file will not be enormous.
The other suggestion is to turn on profiler and ask everyone to hit the
server with their updates so that you can capture everything in one go.
Rick Sawtell
MCT, MCSD, MCDBA|||>--Original Message--
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote
in message
>news:012401c4f75f$6fe0e520$a601280a@.phx.gbl...
>> I'm looking for a little insight on identifying what
apps
>> are updating a particular database. Basically we have a
>> front end developed for the db but some people may have
>> created Access dbs and processes in Access to do
updates.
>> I need to determine if this is happening since we may be
>> moving the db to a different sql server. SQL Profiler
>> would work, but it's hit and miss. Plus I'd have to let
>> it run for a few days which would create a huge trace
>> file. If I did let it run for a few days, I still may
not
>> capture and activity from Access if they didn't happen
to
>> run any Access updates during that time. Is there
>> anything out there that can help me?
>> Thanks,
>> Van Jones
>
>I would still go with Profiler. Take a look at the
Filters tab. You can
>do things like: NOT LIKE and add all of the known
applications that will
>log in. You can weed out just about all the activity
that you don't want
>to see. Then your trace file will not be enormous.
>The other suggestion is to turn on profiler and ask
everyone to hit the
>server with their updates so that you can capture
everything in one go.
>
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>.
Yea, I figure profiler might be the only way. The problem
is...is that I don't know if anybody has any Access
processes that update the db. I've been told that they
might. If they do, it could be quartly or even year end
processes that I wouldn't capture if I ran a trace for a
few days.|||"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:1d5c01c4f761$bc059f70$a401280a@.phx.gbl...
>>--Original Message--
>>"Van Jones" <anonymous@.discussions.microsoft.com> wrote
> in message
>>news:012401c4f75f$6fe0e520$a601280a@.phx.gbl...
>> I'm looking for a little insight on identifying what
> apps
>> are updating a particular database. Basically we have a
>> front end developed for the db but some people may have
>> created Access dbs and processes in Access to do
> updates.
>> I need to determine if this is happening since we may be
>> moving the db to a different sql server. SQL Profiler
>> would work, but it's hit and miss. Plus I'd have to let
>> it run for a few days which would create a huge trace
>> file. If I did let it run for a few days, I still may
> not
>> capture and activity from Access if they didn't happen
> to
>> run any Access updates during that time. Is there
>> anything out there that can help me?
>> Thanks,
>> Van Jones
>>
>>I would still go with Profiler. Take a look at the
> Filters tab. You can
>>do things like: NOT LIKE and add all of the known
> applications that will
>>log in. You can weed out just about all the activity
> that you don't want
>>to see. Then your trace file will not be enormous.
>>The other suggestion is to turn on profiler and ask
> everyone to hit the
>>server with their updates so that you can capture
> everything in one go.
>>
>>Rick Sawtell
>>MCT, MCSD, MCDBA
>>
>>.
> Yea, I figure profiler might be the only way. The problem
> is...is that I don't know if anybody has any Access
> processes that update the db. I've been told that they
> might. If they do, it could be quartly or even year end
> processes that I wouldn't capture if I ran a trace for a
> few days.
Can you take an inventory of the applications that are hitting your
database? You should have this list regardless.
Another method. (Muahahahaha) Change the login credentials.
Rick Sawtell
MCT, MCSD, MCDBA
Identifying what Apps are using a database
I'm looking for a little insight on identifying what apps
are updating a particular database. Basically we have a
front end developed for the db but some people may have
created Access dbs and processes in Access to do updates.
I need to determine if this is happening since we may be
moving the db to a different sql server. SQL Profiler
would work, but it's hit and miss. Plus I'd have to let
it run for a few days which would create a huge trace
file. If I did let it run for a few days, I still may not
capture and activity from Access if they didn't happen to
run any Access updates during that time. Is there
anything out there that can help me?
Thanks,
Van Jones"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:012401c4f75f$6fe0e520$a601280a@.phx.gbl...
> I'm looking for a little insight on identifying what apps
> are updating a particular database. Basically we have a
> front end developed for the db but some people may have
> created Access dbs and processes in Access to do updates.
> I need to determine if this is happening since we may be
> moving the db to a different sql server. SQL Profiler
> would work, but it's hit and miss. Plus I'd have to let
> it run for a few days which would create a huge trace
> file. If I did let it run for a few days, I still may not
> capture and activity from Access if they didn't happen to
> run any Access updates during that time. Is there
> anything out there that can help me?
> Thanks,
> Van Jones
I would still go with Profiler. Take a look at the Filters tab. You can
do things like: NOT LIKE and add all of the known applications that will
log in. You can weed out just about all the activity that you don't want
to see. Then your trace file will not be enormous.
The other suggestion is to turn on profiler and ask everyone to hit the
server with their updates so that you can capture everything in one go.
Rick Sawtell
MCT, MCSD, MCDBA|||
>--Original Message--
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote
in message
>news:012401c4f75f$6fe0e520$a601280a@.phx.gbl...
apps[vbcol=seagreen]
updates.[vbcol=seagreen]
not[vbcol=seagreen]
to[vbcol=seagreen]
>
>I would still go with Profiler. Take a look at the
Filters tab. You can
>do things like: NOT LIKE and add all of the known
applications that will
>log in. You can weed out just about all the activity
that you don't want
>to see. Then your trace file will not be enormous.
>The other suggestion is to turn on profiler and ask
everyone to hit the
>server with their updates so that you can capture
everything in one go.
>
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>.
Yea, I figure profiler might be the only way. The problem
is...is that I don't know if anybody has any Access
processes that update the db. I've been told that they
might. If they do, it could be quartly or even year end
processes that I wouldn't capture if I ran a trace for a
few days.|||"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:1d5c01c4f761$bc059f70$a401280a@.phx.gbl...
>
> in message
> apps
> updates.
> not
> to
> Filters tab. You can
> applications that will
> that you don't want
> everyone to hit the
> everything in one go.
> Yea, I figure profiler might be the only way. The problem
> is...is that I don't know if anybody has any Access
> processes that update the db. I've been told that they
> might. If they do, it could be quartly or even year end
> processes that I wouldn't capture if I ran a trace for a
> few days.
Can you take an inventory of the applications that are hitting your
database? You should have this list regardless.
Another method. (Muahahahaha) Change the login credentials.
Rick Sawtell
MCT, MCSD, MCDBA
are updating a particular database. Basically we have a
front end developed for the db but some people may have
created Access dbs and processes in Access to do updates.
I need to determine if this is happening since we may be
moving the db to a different sql server. SQL Profiler
would work, but it's hit and miss. Plus I'd have to let
it run for a few days which would create a huge trace
file. If I did let it run for a few days, I still may not
capture and activity from Access if they didn't happen to
run any Access updates during that time. Is there
anything out there that can help me?
Thanks,
Van Jones"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:012401c4f75f$6fe0e520$a601280a@.phx.gbl...
> I'm looking for a little insight on identifying what apps
> are updating a particular database. Basically we have a
> front end developed for the db but some people may have
> created Access dbs and processes in Access to do updates.
> I need to determine if this is happening since we may be
> moving the db to a different sql server. SQL Profiler
> would work, but it's hit and miss. Plus I'd have to let
> it run for a few days which would create a huge trace
> file. If I did let it run for a few days, I still may not
> capture and activity from Access if they didn't happen to
> run any Access updates during that time. Is there
> anything out there that can help me?
> Thanks,
> Van Jones
I would still go with Profiler. Take a look at the Filters tab. You can
do things like: NOT LIKE and add all of the known applications that will
log in. You can weed out just about all the activity that you don't want
to see. Then your trace file will not be enormous.
The other suggestion is to turn on profiler and ask everyone to hit the
server with their updates so that you can capture everything in one go.
Rick Sawtell
MCT, MCSD, MCDBA|||
>--Original Message--
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote
in message
>news:012401c4f75f$6fe0e520$a601280a@.phx.gbl...
apps[vbcol=seagreen]
updates.[vbcol=seagreen]
not[vbcol=seagreen]
to[vbcol=seagreen]
>
>I would still go with Profiler. Take a look at the
Filters tab. You can
>do things like: NOT LIKE and add all of the known
applications that will
>log in. You can weed out just about all the activity
that you don't want
>to see. Then your trace file will not be enormous.
>The other suggestion is to turn on profiler and ask
everyone to hit the
>server with their updates so that you can capture
everything in one go.
>
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>.
Yea, I figure profiler might be the only way. The problem
is...is that I don't know if anybody has any Access
processes that update the db. I've been told that they
might. If they do, it could be quartly or even year end
processes that I wouldn't capture if I ran a trace for a
few days.|||"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:1d5c01c4f761$bc059f70$a401280a@.phx.gbl...
>
> in message
> apps
> updates.
> not
> to
> Filters tab. You can
> applications that will
> that you don't want
> everyone to hit the
> everything in one go.
> Yea, I figure profiler might be the only way. The problem
> is...is that I don't know if anybody has any Access
> processes that update the db. I've been told that they
> might. If they do, it could be quartly or even year end
> processes that I wouldn't capture if I ran a trace for a
> few days.
Can you take an inventory of the applications that are hitting your
database? You should have this list regardless.
Another method. (Muahahahaha) Change the login credentials.
Rick Sawtell
MCT, MCSD, MCDBA
Identifying Virtual Server
I inherited a SQL2005 cluster and while I'm experienced in managing SQL
stand alone I have no experience in a cluster environment. I am trying to
determine the cluster virtual server name, as opposed to instance, so I can
make changes to the service start up configurations. I can't get the utility
to recognize the cluster. How can I determine the proper name to reference?
The instance is the virtual server name. The cluster one is a virtual name
too by the way. @.@.servername
http://msdn2.microsoft.com/en-us/library/aa933172(sql.80).aspx will return
lots of information too.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Sammi" <again5@.hotmail.com> wrote in message
news:B6852C1F-D37B-485D-ADFB-52500B24C6F5@.microsoft.com...
>I inherited a SQL2005 cluster and while I'm experienced in managing SQL
> stand alone I have no experience in a cluster environment. I am trying to
> determine the cluster virtual server name, as opposed to instance, so I
> can
> make changes to the service start up configurations. I can't get the
> utility
> to recognize the cluster. How can I determine the proper name to
> reference?
>
stand alone I have no experience in a cluster environment. I am trying to
determine the cluster virtual server name, as opposed to instance, so I can
make changes to the service start up configurations. I can't get the utility
to recognize the cluster. How can I determine the proper name to reference?
The instance is the virtual server name. The cluster one is a virtual name
too by the way. @.@.servername
http://msdn2.microsoft.com/en-us/library/aa933172(sql.80).aspx will return
lots of information too.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Sammi" <again5@.hotmail.com> wrote in message
news:B6852C1F-D37B-485D-ADFB-52500B24C6F5@.microsoft.com...
>I inherited a SQL2005 cluster and while I'm experienced in managing SQL
> stand alone I have no experience in a cluster environment. I am trying to
> determine the cluster virtual server name, as opposed to instance, so I
> can
> make changes to the service start up configurations. I can't get the
> utility
> to recognize the cluster. How can I determine the proper name to
> reference?
>
Identifying Unused Tables
I have inherited support for a database with many used tables. There
isn't any documentation on what is used or dead. I was hoping to run
traces and capture the objectid but the property doesn't work that
way.
isn't any documentation on what is used or dead. I was hoping to run
traces and capture the objectid but the property doesn't work that
way.
Any good ideas would be appreciated.david_0 (dosberg@.yahoo.com) writes:
> I have inherited support for a database with many used tables. There
> isn't any documentation on what is used or dead. I was hoping to run
> traces and capture the objectid but the property doesn't work that
> way.
If the application uses only stored procedures, then it is fairly easy:
you search the code for all tables, and the keep track of which tables
does not give a hit. Alright, so that opens the question if there are
stored procedures which are not used.
If the application sends SELECT statement from the code, your best bet
is probably to search the application code, again once for each table.
If you find no references for a table you may want to make extra
precautions as it could be a lookup table that is being reference in
a foreign-ley constraint or trigger only. Or it may be part of a view
that is not in use.
Using the Profiler and the search the output is a possibility, but there
may be tables which are used in end-of-the-year functions only.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here is what I came up with that will work for my situation. With a
little rework it could be used in other cases.
It doesn't do anything to detect inline sql on the client side. That
issue doesn't really apply in my situation.
DECLARE @.tbl TABLE(id INT IDENTITY(1,1), tblname VARCHAR(128),
found_flag CHAR(1))
DECLARE @.cnt int
DECLARE @.loop int
DECLARE @.parm varchar(255)
/*
*load variable with tables that aren't in sysdepends
*/
INSERT INTO @.tbl (tblname, found_flag)
SELECT
OBJECT_NAME(a.id),'N'
FROM
sysobjects a LEFT JOIN sysdepends b ON a.id=depid
WHERE
a.type='u' AND b.depid IS NULL
ORDER BY object_name(a.id)
/*
*setup variables for the loop
*/
SELECT @.cnt=MAX(id) FROM @.tbl
SET @.loop=1
/*
*take list of tables with no dependencies and look for job steps
that might reference them.
*/
WHILE @.loop <=@.cnt BEGIN
SELECT @.parm=tblname FROM @.tbl WHERE id=@.loop
IF EXISTS (SELECT job_id FROM msdb..sysjobsteps WHERE
CHARINDEX(@.parm,command)>0)
BEGIN
UPDATE @.tbl SET found_flag='Y' where id=@.loop
END
SET @.loop=@.loop+1
END
/*
*return table names not used in job steps or having object
dependencies
*/
SELECT tblname FROM @.tbl WHERE found_flag='N'
Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9536EBF5675DYazorman@.127.0.0.1>...
> david_0 (dosberg@.yahoo.com) writes:
> > I have inherited support for a database with many used tables. There
> > isn't any documentation on what is used or dead. I was hoping to run
> > traces and capture the objectid but the property doesn't work that
> > way.
> If the application uses only stored procedures, then it is fairly easy:
> you search the code for all tables, and the keep track of which tables
> does not give a hit. Alright, so that opens the question if there are
> stored procedures which are not used.
> If the application sends SELECT statement from the code, your best bet
> is probably to search the application code, again once for each table.
> If you find no references for a table you may want to make extra
> precautions as it could be a lookup table that is being reference in
> a foreign-ley constraint or trigger only. Or it may be part of a view
> that is not in use.
> Using the Profiler and the search the output is a possibility, but there
> may be tables which are used in end-of-the-year functions only.|||david_0 (dosberg@.yahoo.com) writes:
> Here is what I came up with that will work for my situation. With a
> little rework it could be used in other cases.
>...
> INSERT INTO @.tbl (tblname, found_flag)
> SELECT
> OBJECT_NAME(a.id),'N'
> FROM
> sysobjects a LEFT JOIN sysdepends b ON a.id=depid
> WHERE
> a.type='u' AND b.depid IS NULL
> ORDER BY object_name(a.id)
Note however that sysdepends is at best approxamite. For instance if you
drop and recreate a table, you lose all entries in sysdepends for
the reference, so the table may appear unreferenced.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Identifying unused columns in multiple databases
Hi - we have a situation where we have 23 identical databases on
servers across the country. We know that there are fields within this
db that no one is using and would like to be able to identify all of
them so they can be removed across the board. (There are multiple
tables and columns) We would of course have to verify that none of the
23 offices are using a particular field before it's removed as the db's
need to stay identical. (data in each db varies).
Anyway - I came across this posting which sounds like what we're
looking for, but it specifically references varchar and nvarchar fields
- I can't figure out what to modify in it to make this work for any
type of field. Can anyone point me in the right direction?
This is the posting:
> How can I find all the varchar columns in a table?
> I'm looking for an existing empty or null column rather than add a column.
This will generate a script for the current database that will tell you
all
of the varchar or nvarchar columns, and what percentage of them are
"unused" -- either NULL or empty strings.
SELECT 'SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
+DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
+')'',''empty'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
+' WITH (NOLOCK) WHERE
LTRIM(RTRIM(COALESCE('+COLUMN_NAME+','''
')))=''''
UNION
SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
+DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
+')'',''total'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
+' WITH (NOLOCK)'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE LIKE '%varchar'
You can run this in Query Analyzer, and it will generate a script in
the
bottom pane. Copy to a new query window and let 'er rip. Should take
a
while on larger databases. I use WITH (NOLOCK) for this kind of task
to
avoid concurrency and blocking issues; if you need an
up-to-the-millisecond
guaranteed read on the whole table, you may opt to leave that out (but
I do
not recommend it on a production server).
Thank you in advance.Please do not take offense, but if are not familiar enough with SQL Server
and SQL in general to make the necessary change to this query, then you
really are not qualified to be working on the changes you are talking about.
I am not trying to be mean, but your question is much simpler than the task
you intend to eventually perform. If you need help at this point, you will
get disastrous results later on. It is best to get someone who is more of
an expert in such things to handle this project.
"Debbie" <deborah.young@.mail.va.gov> wrote in message
news:1140104286.351275.153030@.g43g2000cwa.googlegroups.com...
> Hi - we have a situation where we have 23 identical databases on
> servers across the country. We know that there are fields within this
> db that no one is using and would like to be able to identify all of
> them so they can be removed across the board. (There are multiple
> tables and columns) We would of course have to verify that none of the
> 23 offices are using a particular field before it's removed as the db's
> need to stay identical. (data in each db varies).
> Anyway - I came across this posting which sounds like what we're
> looking for, but it specifically references varchar and nvarchar fields
> - I can't figure out what to modify in it to make this work for any
> type of field. Can anyone point me in the right direction?
> This is the posting:
>
column.
>
> This will generate a script for the current database that will tell you
> all
> of the varchar or nvarchar columns, and what percentage of them are
> "unused" -- either NULL or empty strings.
> SELECT 'SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
> +')'',''empty'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
> +' WITH (NOLOCK) WHERE
> LTRIM(RTRIM(COALESCE('+COLUMN_NAME+','''
')))=''''
> UNION
> SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
> +')'',''total'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
> +' WITH (NOLOCK)'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE DATA_TYPE LIKE '%varchar'
>
> You can run this in Query Analyzer, and it will generate a script in
> the
> bottom pane. Copy to a new query window and let 'er rip. Should take
> a
> while on larger databases. I use WITH (NOLOCK) for this kind of task
> to
> avoid concurrency and blocking issues; if you need an
> up-to-the-millisecond
> guaranteed read on the whole table, you may opt to leave that out (but
> I do
> not recommend it on a production server).
>
> Thank you in advance.
>|||By "using the column", do you mean:
#1 if the column is populated with at least some data
#2 if there are any queries or procedures that actually reference the
column
The programming below seems to be querying for any columns that are all NULL
or zero length.
Also, a column may have been populated at some point in the past, but is
currently obsolete and not used. If you really need to audit which tables /
columns are in use by the application during normal usage, then you can
setup an object trace in SQL Server Profiler and let it run the background
for a complete business cycle.
http://msdn.microsoft.com/library/d...
ethowto15.asp
"Debbie" <deborah.young@.mail.va.gov> wrote in message
news:1140104286.351275.153030@.g43g2000cwa.googlegroups.com...
> Hi - we have a situation where we have 23 identical databases on
> servers across the country. We know that there are fields within this
> db that no one is using and would like to be able to identify all of
> them so they can be removed across the board. (There are multiple
> tables and columns) We would of course have to verify that none of the
> 23 offices are using a particular field before it's removed as the db's
> need to stay identical. (data in each db varies).
> Anyway - I came across this posting which sounds like what we're
> looking for, but it specifically references varchar and nvarchar fields
> - I can't figure out what to modify in it to make this work for any
> type of field. Can anyone point me in the right direction?
> This is the posting:
>
>
> This will generate a script for the current database that will tell you
> all
> of the varchar or nvarchar columns, and what percentage of them are
> "unused" -- either NULL or empty strings.
> SELECT 'SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
> +')'',''empty'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
> +' WITH (NOLOCK) WHERE
> LTRIM(RTRIM(COALESCE('+COLUMN_NAME+','''
')))=''''
> UNION
> SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
> +')'',''total'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
> +' WITH (NOLOCK)'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE DATA_TYPE LIKE '%varchar'
>
> You can run this in Query Analyzer, and it will generate a script in
> the
> bottom pane. Copy to a new query window and let 'er rip. Should take
> a
> while on larger databases. I use WITH (NOLOCK) for this kind of task
> to
> avoid concurrency and blocking issues; if you need an
> up-to-the-millisecond
> guaranteed read on the whole table, you may opt to leave that out (but
> I do
> not recommend it on a production server).
>
> Thank you in advance.
>|||I'd suggest running the trace (after it has been tuned to only trace relevan
t
events and return relevant data) for a month (or whatever the actual
turn-over time is).
After that you need to analyze the trace data, identifying individual unused
objects.
The following activities should not be done in live production.
Unused objects should first be renamed before actually being dropped -
rename one at a time to get a clear picture of their actual usage.
After the system has been running successfully for your specific turn-over
time with the renamed objects, you could consider dropping them - in a test
environment first, of course.
This is the bottom-to-top approach.
There is, however, another approach to this - analyze your actual business
requirements. If you think you have unused columns, maybe the entire data
model is wrong for you, and needs to be redesigned. This approach may even
yield better results.
ML
http://milambda.blogspot.com/
servers across the country. We know that there are fields within this
db that no one is using and would like to be able to identify all of
them so they can be removed across the board. (There are multiple
tables and columns) We would of course have to verify that none of the
23 offices are using a particular field before it's removed as the db's
need to stay identical. (data in each db varies).
Anyway - I came across this posting which sounds like what we're
looking for, but it specifically references varchar and nvarchar fields
- I can't figure out what to modify in it to make this work for any
type of field. Can anyone point me in the right direction?
This is the posting:
> How can I find all the varchar columns in a table?
> I'm looking for an existing empty or null column rather than add a column.
This will generate a script for the current database that will tell you
all
of the varchar or nvarchar columns, and what percentage of them are
"unused" -- either NULL or empty strings.
SELECT 'SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
+DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
+')'',''empty'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
+' WITH (NOLOCK) WHERE
LTRIM(RTRIM(COALESCE('+COLUMN_NAME+','''
')))=''''
UNION
SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
+DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
+')'',''total'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
+' WITH (NOLOCK)'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE LIKE '%varchar'
You can run this in Query Analyzer, and it will generate a script in
the
bottom pane. Copy to a new query window and let 'er rip. Should take
a
while on larger databases. I use WITH (NOLOCK) for this kind of task
to
avoid concurrency and blocking issues; if you need an
up-to-the-millisecond
guaranteed read on the whole table, you may opt to leave that out (but
I do
not recommend it on a production server).
Thank you in advance.Please do not take offense, but if are not familiar enough with SQL Server
and SQL in general to make the necessary change to this query, then you
really are not qualified to be working on the changes you are talking about.
I am not trying to be mean, but your question is much simpler than the task
you intend to eventually perform. If you need help at this point, you will
get disastrous results later on. It is best to get someone who is more of
an expert in such things to handle this project.
"Debbie" <deborah.young@.mail.va.gov> wrote in message
news:1140104286.351275.153030@.g43g2000cwa.googlegroups.com...
> Hi - we have a situation where we have 23 identical databases on
> servers across the country. We know that there are fields within this
> db that no one is using and would like to be able to identify all of
> them so they can be removed across the board. (There are multiple
> tables and columns) We would of course have to verify that none of the
> 23 offices are using a particular field before it's removed as the db's
> need to stay identical. (data in each db varies).
> Anyway - I came across this posting which sounds like what we're
> looking for, but it specifically references varchar and nvarchar fields
> - I can't figure out what to modify in it to make this work for any
> type of field. Can anyone point me in the right direction?
> This is the posting:
>
column.
>
> This will generate a script for the current database that will tell you
> all
> of the varchar or nvarchar columns, and what percentage of them are
> "unused" -- either NULL or empty strings.
> SELECT 'SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
> +')'',''empty'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
> +' WITH (NOLOCK) WHERE
> LTRIM(RTRIM(COALESCE('+COLUMN_NAME+','''
')))=''''
> UNION
> SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
> +')'',''total'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
> +' WITH (NOLOCK)'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE DATA_TYPE LIKE '%varchar'
>
> You can run this in Query Analyzer, and it will generate a script in
> the
> bottom pane. Copy to a new query window and let 'er rip. Should take
> a
> while on larger databases. I use WITH (NOLOCK) for this kind of task
> to
> avoid concurrency and blocking issues; if you need an
> up-to-the-millisecond
> guaranteed read on the whole table, you may opt to leave that out (but
> I do
> not recommend it on a production server).
>
> Thank you in advance.
>|||By "using the column", do you mean:
#1 if the column is populated with at least some data
#2 if there are any queries or procedures that actually reference the
column
The programming below seems to be querying for any columns that are all NULL
or zero length.
Also, a column may have been populated at some point in the past, but is
currently obsolete and not used. If you really need to audit which tables /
columns are in use by the application during normal usage, then you can
setup an object trace in SQL Server Profiler and let it run the background
for a complete business cycle.
http://msdn.microsoft.com/library/d...
ethowto15.asp
"Debbie" <deborah.young@.mail.va.gov> wrote in message
news:1140104286.351275.153030@.g43g2000cwa.googlegroups.com...
> Hi - we have a situation where we have 23 identical databases on
> servers across the country. We know that there are fields within this
> db that no one is using and would like to be able to identify all of
> them so they can be removed across the board. (There are multiple
> tables and columns) We would of course have to verify that none of the
> 23 offices are using a particular field before it's removed as the db's
> need to stay identical. (data in each db varies).
> Anyway - I came across this posting which sounds like what we're
> looking for, but it specifically references varchar and nvarchar fields
> - I can't figure out what to modify in it to make this work for any
> type of field. Can anyone point me in the right direction?
> This is the posting:
>
>
> This will generate a script for the current database that will tell you
> all
> of the varchar or nvarchar columns, and what percentage of them are
> "unused" -- either NULL or empty strings.
> SELECT 'SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
> +')'',''empty'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
> +' WITH (NOLOCK) WHERE
> LTRIM(RTRIM(COALESCE('+COLUMN_NAME+','''
')))=''''
> UNION
> SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
> +')'',''total'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
> +' WITH (NOLOCK)'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE DATA_TYPE LIKE '%varchar'
>
> You can run this in Query Analyzer, and it will generate a script in
> the
> bottom pane. Copy to a new query window and let 'er rip. Should take
> a
> while on larger databases. I use WITH (NOLOCK) for this kind of task
> to
> avoid concurrency and blocking issues; if you need an
> up-to-the-millisecond
> guaranteed read on the whole table, you may opt to leave that out (but
> I do
> not recommend it on a production server).
>
> Thank you in advance.
>|||I'd suggest running the trace (after it has been tuned to only trace relevan
t
events and return relevant data) for a month (or whatever the actual
turn-over time is).
After that you need to analyze the trace data, identifying individual unused
objects.
The following activities should not be done in live production.
Unused objects should first be renamed before actually being dropped -
rename one at a time to get a clear picture of their actual usage.
After the system has been running successfully for your specific turn-over
time with the renamed objects, you could consider dropping them - in a test
environment first, of course.
This is the bottom-to-top approach.
There is, however, another approach to this - analyze your actual business
requirements. If you think you have unused columns, maybe the entire data
model is wrong for you, and needs to be redesigned. This approach may even
yield better results.
ML
http://milambda.blogspot.com/
Identifying the servercontrol in repeater
Hell Sir,
I am using repeater control to show the result after search .and using checkbox control in itemtemplate row .After searchresult i am facing a problem in identifying the checked checkboxes in the itemtemplate of repeater control .
Please provide appropriate solution ...
thanks for ur attention...
Hi,
take a look at this article: the technique's similar for repeaters:Using a comma delimited string with id's as input parameter for a SQL query. But if I remember correctly you shouldn't loop the Rows but Items instead.
Grz, Kris.
|||Hello sir
i am asking about the repeater control
please forward the solution in case of repeater control
|||Hi,
In your code-behind file, try to use FindControl to get the checkbox on your page.See the following sample.
for (int i = 0; i <this.Repeater1.Items.Count; i++) {bool ifchecked=(Checkbox(this.Repeater1.Items[i].FindControl("Checkbox1"))).Checked;}Hope that helps. Thanks.
Identifying the Logs
Hi All,
We almost have 30 developers in our project and we do have
admin user rights for a username and that is being given to all the the
users,
My issue is somebody deletes one object [ tables, SP]
How will i track it down [ who has deleted it], is there anything that
i will be able to find in the logs.
thanks in advance
thomsonHi,
From logs u can identify all the activities that happend to the database. U
can use the third party tool like 'Lumigent log explorer', which i think is
the best tool for dealing with the logs.
Regards Lara
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1129615680.263276.53610@.o13g2000cwo.googlegroups.com...
> Hi All,
> We almost have 30 developers in our project and we do have
> admin user rights for a username and that is being given to all the the
> users,
> My issue is somebody deletes one object [ tables, SP]
> How will i track it down [ who has deleted it], is there anything that
> i will be able to find in the logs.
>
> thanks in advance
> thomson
>|||Hi,
Thanks for the information Do we have any open source tool to
manipulate these things.
Regards
thomson
Aneesh R wrote:
> Hi,
> From logs u can identify all the activities that happend to the database.
U
> can use the third party tool like 'Lumigent log explorer', which i think i
s
> the best tool for dealing with the logs.
> Regards Lara
>
> "thomson" <saintthomson@.yahoo.com> wrote in message
> news:1129615680.263276.53610@.o13g2000cwo.googlegroups.com...|||If all 30 people use that single user-name that has admin privs, then you
will not know who did what. The best thing is to restrict the permissions
and hand out accounts carefully.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1129615680.263276.53610@.o13g2000cwo.googlegroups.com...
> Hi All,
> We almost have 30 developers in our project and we do have
> admin user rights for a username and that is being given to all the the
> users,
> My issue is somebody deletes one object [ tables, SP]
> How will i track it down [ who has deleted it], is there anything that
> i will be able to find in the logs.
>
> thanks in advance
> thomson
>|||Hi
No, only expensive commercial tools.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1129618925.118960.27150@.g49g2000cwa.googlegroups.com...
> Hi,
> Thanks for the information Do we have any open source tool to
> manipulate these things.
>
> Regards
> thomson
>
> Aneesh R wrote:
>
We almost have 30 developers in our project and we do have
admin user rights for a username and that is being given to all the the
users,
My issue is somebody deletes one object [ tables, SP]
How will i track it down [ who has deleted it], is there anything that
i will be able to find in the logs.
thanks in advance
thomsonHi,
From logs u can identify all the activities that happend to the database. U
can use the third party tool like 'Lumigent log explorer', which i think is
the best tool for dealing with the logs.
Regards Lara
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1129615680.263276.53610@.o13g2000cwo.googlegroups.com...
> Hi All,
> We almost have 30 developers in our project and we do have
> admin user rights for a username and that is being given to all the the
> users,
> My issue is somebody deletes one object [ tables, SP]
> How will i track it down [ who has deleted it], is there anything that
> i will be able to find in the logs.
>
> thanks in advance
> thomson
>|||Hi,
Thanks for the information Do we have any open source tool to
manipulate these things.
Regards
thomson
Aneesh R wrote:
> Hi,
> From logs u can identify all the activities that happend to the database.
U
> can use the third party tool like 'Lumigent log explorer', which i think i
s
> the best tool for dealing with the logs.
> Regards Lara
>
> "thomson" <saintthomson@.yahoo.com> wrote in message
> news:1129615680.263276.53610@.o13g2000cwo.googlegroups.com...|||If all 30 people use that single user-name that has admin privs, then you
will not know who did what. The best thing is to restrict the permissions
and hand out accounts carefully.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1129615680.263276.53610@.o13g2000cwo.googlegroups.com...
> Hi All,
> We almost have 30 developers in our project and we do have
> admin user rights for a username and that is being given to all the the
> users,
> My issue is somebody deletes one object [ tables, SP]
> How will i track it down [ who has deleted it], is there anything that
> i will be able to find in the logs.
>
> thanks in advance
> thomson
>|||Hi
No, only expensive commercial tools.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1129618925.118960.27150@.g49g2000cwa.googlegroups.com...
> Hi,
> Thanks for the information Do we have any open source tool to
> manipulate these things.
>
> Regards
> thomson
>
> Aneesh R wrote:
>
Identifying the logical files before restoring database
We have a requirement to restore the relational database from a .bak file.
This .bak file can contain more than one data file and log file. How do we
programatically determine the logical files within this bak file and restore
accordingly.
I tried using 'Restore Filelistonly', but then here i am not able to capture
the names and location of back up file and store them in a variable which ca
n
be used while restoring.
Pls adviceHi
Create a table with the same number of columns and execure RESTORE
FILELISTONLY.
"GS" <GS@.discussions.microsoft.com> wrote in message
news:523C5E6A-5E5B-493B-BBE2-73A140DB3F01@.microsoft.com...
> We have a requirement to restore the relational database from a .bak file.
> This .bak file can contain more than one data file and log file. How do we
> programatically determine the logical files within this bak file and
> restore
> accordingly.
> I tried using 'Restore Filelistonly', but then here i am not able to
> capture
> the names and location of back up file and store them in a variable which
> can
> be used while restoring.
> Pls advice
>|||Sorry, I should be more specify
CREATE TABLE #Test
(
LogicalName VARCHAR(100),
PhysicalName VARCHAR(100),
Type VARCHAR(2),
FileGroupName VARCHAR(20),
[Size]VARCHAR(100),
[MaxSize] VARCHAR(100),
)
INSERT INTO #Test EXEC('
RESTORE FILELISTONLY FROM DISK=N''c:\backupsql\dbname.bak''')
SELECT * FROM #Test
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uDOs$0z7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Hi
> Create a table with the same number of columns and execure RESTORE
> FILELISTONLY.
>
> "GS" <GS@.discussions.microsoft.com> wrote in message
> news:523C5E6A-5E5B-493B-BBE2-73A140DB3F01@.microsoft.com...
>
This .bak file can contain more than one data file and log file. How do we
programatically determine the logical files within this bak file and restore
accordingly.
I tried using 'Restore Filelistonly', but then here i am not able to capture
the names and location of back up file and store them in a variable which ca
n
be used while restoring.
Pls adviceHi
Create a table with the same number of columns and execure RESTORE
FILELISTONLY.
"GS" <GS@.discussions.microsoft.com> wrote in message
news:523C5E6A-5E5B-493B-BBE2-73A140DB3F01@.microsoft.com...
> We have a requirement to restore the relational database from a .bak file.
> This .bak file can contain more than one data file and log file. How do we
> programatically determine the logical files within this bak file and
> restore
> accordingly.
> I tried using 'Restore Filelistonly', but then here i am not able to
> capture
> the names and location of back up file and store them in a variable which
> can
> be used while restoring.
> Pls advice
>|||Sorry, I should be more specify
CREATE TABLE #Test
(
LogicalName VARCHAR(100),
PhysicalName VARCHAR(100),
Type VARCHAR(2),
FileGroupName VARCHAR(20),
[Size]VARCHAR(100),
[MaxSize] VARCHAR(100),
)
INSERT INTO #Test EXEC('
RESTORE FILELISTONLY FROM DISK=N''c:\backupsql\dbname.bak''')
SELECT * FROM #Test
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uDOs$0z7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Hi
> Create a table with the same number of columns and execure RESTORE
> FILELISTONLY.
>
> "GS" <GS@.discussions.microsoft.com> wrote in message
> news:523C5E6A-5E5B-493B-BBE2-73A140DB3F01@.microsoft.com...
>
identifying the invalid procedure
Is there any option to identify the invalid procedure objects. Say for example if procedure P1 uses a table T1 and the table T1 is now deleted then P1 procedure will not be executed. I need to identify these procedures.
Any guidance?
sql2k suppors 'defered name resolution' thus it's perfectly legal to have a stored procedure that references a non-existent object. However, it's important to note here is that if the referenced object exists prior to the stored procedure creation, then an entry is made in sysdepends for the sproc and the object. Hence, you can use sp_depends to find dependencies.
e.g.
--a valid proc
create proc bogus
as
select *
from nonexistencetable
go
Any guidance?
sql2k suppors 'defered name resolution' thus it's perfectly legal to have a stored procedure that references a non-existent object. However, it's important to note here is that if the referenced object exists prior to the stored procedure creation, then an entry is made in sysdepends for the sproc and the object. Hence, you can use sp_depends to find dependencies.
e.g.
--a valid proc
create proc bogus
as
select *
from nonexistencetable
go
Identifying the ErrorColumn (in rejected data rows)
I've noticed that when a row fails to transform/parse, (I.E., the data was truncated) you can use the DFT audit component to see which column contained the error. However, this error column displays the ID of a particular column, NOT the actual order in which the columns are setup (defined in the conn mgr).
Is there any way to modify this so that it will show the sequencial column number, or at the very least, the externalmetadatacolum number?
thanks,
I had hoped that it would be easy to use a Script Component to add Error Description (in place of ErrorCode) and Column Name (in place of ErrorColumn) to a standard error output.
However, while the former is easy (using Me.ComponentMetadata.GetErrorDescription), the latter is not. The column ID that you have in ErrorColumn is the ID of the column in the column collection of the previous component, where the error occurred, and is no longer the same if you choose to include the column of the same name among the input columns of your Script component. So you can't grab the column object to get its name or any of its other properties and, of course, you can't grab a runtime reference to any other components in the data flow.
-Doug
Subscribe to:
Posts (Atom)