Determining Security: running

Showing posts with label running. Show all posts

Thursday, March 29, 2012

Determine when a Service Pack was applied?

We are running SQL Server 2000 with SP3. Is there a way to determine
the date the last service pack was applied?
Thanks
Randy K wrote:
> We are running SQL Server 2000 with SP3. Is there a way to determine
> the date the last service pack was applied?
> Thanks
You could look in the error log and see when that version was first
booted (assuming they have no been overwritten). Use SQL EM or use the
undocumented procedures from Query Analyzer:
Exec sp_enumerrorlogs -- returns each error log and it's Archive #
Exec sp_readerrorlog -- no parameter for current log or pass in archive
# to read another log
David Gugick - SQL Server MVP
Quest Software
|||Look for the Sqlsp.log in your Windows directory. It's the logfile from
your last Service pack installation.
Markus

Determine when a Service Pack was applied?

We are running SQL Server 2000 with SP3. Is there a way to determine
the date the last service pack was applied?
ThanksRandy K wrote:
> We are running SQL Server 2000 with SP3. Is there a way to determine
> the date the last service pack was applied?
> Thanks
You could look in the error log and see when that version was first
booted (assuming they have no been overwritten). Use SQL EM or use the
undocumented procedures from Query Analyzer:
Exec sp_enumerrorlogs -- returns each error log and it's Archive #
Exec sp_readerrorlog -- no parameter for current log or pass in archive
# to read another log
David Gugick - SQL Server MVP
Quest Software|||Look for the Sqlsp.log in your Windows directory. It's the logfile from
your last Service pack installation.
Markus

Determine when a Service Pack was applied?

We are running SQL Server 2000 with SP3. Is there a way to determine
the date the last service pack was applied?
ThanksRandy K wrote:
> We are running SQL Server 2000 with SP3. Is there a way to determine
> the date the last service pack was applied?
> Thanks
You could look in the error log and see when that version was first
booted (assuming they have no been overwritten). Use SQL EM or use the
undocumented procedures from Query Analyzer:
Exec sp_enumerrorlogs -- returns each error log and it's Archive #
Exec sp_readerrorlog -- no parameter for current log or pass in archive
# to read another log
David Gugick - SQL Server MVP
Quest Software|||Look for the Sqlsp.log in your Windows directory. It's the logfile from
your last Service pack installation.
Markus

Determine what User Account SQL Server is running under

(MS SQL 2000)
Is there a way to determine what network user account SQL Server is
installed and running under? More importantly, can I easily change it?
Thanks!If you have permissions to the server have a look in Control Panel /
Services / MSSQLServer / Logon Tab
It can be easily changed but you do need to stop/start the services for it
to take effect
--
HTH. Ryan
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:uLoxF9TEGHA.1508@.TK2MSFTNGP15.phx.gbl...
> (MS SQL 2000)
> Is there a way to determine what network user account SQL Server is
> installed and running under? More importantly, can I easily change it?
> Thanks!
>|||Thanks! That was pretty easy.
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23zgi8AUEGHA.2724@.TK2MSFTNGP12.phx.gbl...
> If you have permissions to the server have a look in Control Panel /
> Services / MSSQLServer / Logon Tab
> It can be easily changed but you do need to stop/start the services for it
> to take effect
> --
> HTH. Ryan
>
> "D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:uLoxF9TEGHA.1508@.TK2MSFTNGP15.phx.gbl...
>> (MS SQL 2000)
>> Is there a way to determine what network user account SQL Server is
>> installed and running under? More importantly, can I easily change it?
>> Thanks!
>>
>|||Hi,
The proper method to change the account is via the Enterprise Manager or you
have to grant the new account some additional permissions on the server. I do
not remember all of the permissions, but they include 'act as part of os',
'run as service', etc.
If the account is changed via the EM, all of the required permisions are
automatically granted.
hth
DeeJay Puar
MCDBA
"D. Shane Fowlkes" wrote:
> Thanks! That was pretty easy.
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:%23zgi8AUEGHA.2724@.TK2MSFTNGP12.phx.gbl...
> > If you have permissions to the server have a look in Control Panel /
> > Services / MSSQLServer / Logon Tab
> >
> > It can be easily changed but you do need to stop/start the services for it
> > to take effect
> >
> > --
> > HTH. Ryan
> >
> >
> > "D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> > news:uLoxF9TEGHA.1508@.TK2MSFTNGP15.phx.gbl...
> >> (MS SQL 2000)
> >>
> >> Is there a way to determine what network user account SQL Server is
> >> installed and running under? More importantly, can I easily change it?
> >>
> >> Thanks!
> >>
> >>
> >
> >
>
>|||Thanks again.
"DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> wrote in message
news:5881C956-4A72-4868-A3BC-1E3907C3667E@.microsoft.com...
> Hi,
> The proper method to change the account is via the Enterprise Manager or
> you
> have to grant the new account some additional permissions on the server. I
> do
> not remember all of the permissions, but they include 'act as part of os',
> 'run as service', etc.
> If the account is changed via the EM, all of the required permisions are
> automatically granted.
> hth
> DeeJay Puar
> MCDBA
> "D. Shane Fowlkes" wrote:
>> Thanks! That was pretty easy.
>>
>> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
>> news:%23zgi8AUEGHA.2724@.TK2MSFTNGP12.phx.gbl...
>> > If you have permissions to the server have a look in Control Panel /
>> > Services / MSSQLServer / Logon Tab
>> >
>> > It can be easily changed but you do need to stop/start the services for
>> > it
>> > to take effect
>> >
>> > --
>> > HTH. Ryan
>> >
>> >
>> > "D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
>> > news:uLoxF9TEGHA.1508@.TK2MSFTNGP15.phx.gbl...
>> >> (MS SQL 2000)
>> >>
>> >> Is there a way to determine what network user account SQL Server is
>> >> installed and running under? More importantly, can I easily change
>> >> it?
>> >>
>> >> Thanks!
>> >>
>> >>
>> >
>> >
>>

Determine what server i'm running on

I've generated a report that will need to display different information
based upon where it is being run. For instance, if the report is executing
on our DVLP server, i need a title to say "Developement". Is there is simple
way to determine what environment the report is being run in? Check the URL?
Server variables?
Thanks,
--
Brian Grant
Senior Programmer
SI International
www.si-intl.com=System.Environment.MachineName
Warning: You'll need to give expression host FullTrust in order to use this
expression on report server, which may be a security risk.
If your SQL Server is on the same machine, creating a dataset against it and
using SELECT @.@.servername would be a better approach.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:uZDVm2lbEHA.3988@.tk2msftngp13.phx.gbl...
> I've generated a report that will need to display different information
> based upon where it is being run. For instance, if the report is executing
> on our DVLP server, i need a title to say "Developement". Is there is
simple
> way to determine what environment the report is being run in? Check the
URL?
> Server variables?
> Thanks,
> --
> Brian Grant
> Senior Programmer
> SI International
> www.si-intl.com
>sql

Determine table size

Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica
Hi Mica Cooper,
To Check table sizes, Select the db name in the enterprise under Databases,
In the Main Menu Click View and select Taskpad, you will get 3 tabs :
General, Table Info and Wizards, Select Table Info and you'll see all the
tables listed with rows count and size
Mario Aoun
"Mica Cooper" wrote:

> Hi,
> I have a client with a database that is running over 1G in size. Looking at
> the tables, I can't find any that would seem to be larger than a 1-2 meg
> with total size should be around 30meg. Is there any way to find out table
> sizes?
> Thanks,
> Mica
>
>
|||Use sp_spaceused or see: http://vyaskn.tripod.com/sp_show_biggest_tables.htm
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica
|||Database size is a fuzzy thing. I don't know your level of SQL experience,
so forgive me if I start off with some basics.
What do you mean, when you say "over 1GB in size"? Right click on the
database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go to
properties. On the first tab ("General"), there's a "Size", and then a
"Space Available". Is this the 1GB you're talking about? What does the
"Space Available" have beside it?
If that is the size that you're talking about, that includes the log file as
well as the actual data file. Click on the next tab "Data Files", and see
how much space is allocated. Then go to the "Transaction Log" tab, and see
the same thing there. If the transaction log is huge, it may be as simple
as either adjusting your backup procedures, or changing the "Recovery Model"
on the "Options" tab.
The other thing that can happen is that SQL database grow automatically, but
the default settings do not let the databases shrink automatically. To
shrink a database, right click on the database, and go to "All Tasks", then
"Shrink Database". I seem to have the best luck with shrinking by clicking
on the "Files" button on the first form, then manually setting the desired
database file sizes by selecting first the data (.mdf) file, then the log
(.ldf) file. You likely have to either do a complete backup or change the
"Recovery Model" before you'll get a lot of space out of the log file.
To find out the actual size of a particular table, you can execute the
"sp_spaceused <tablename>" function in Query Analyzer. It will give you the
number of rows, reserved size, data size, index size, and unused size.
I hope this helped! I'm not a SQL expert, and I don't play one on TV. But
post back with your results and any new questions, if you need more
guidance.
Clint
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a client with a database that is running over 1G in size. Looking
> at the tables, I can't find any that would seem to be larger than a 1-2
> meg with total size should be around 30meg. Is there any way to find out
> table sizes?
> Thanks,
> Mica
>
|||Mario,
That is COOL! In all the years I've used SQL Server I have never seen that.
Its exactly what I was looking for.
Mica
"Mario Aoun" <MarioAoun@.discussions.microsoft.com> wrote in message
news:8AD1DCE1-0E27-463B-BE9E-59D00D5137C2@.microsoft.com...[vbcol=seagreen]
> Hi Mica Cooper,
> To Check table sizes, Select the db name in the enterprise under
> Databases,
> In the Main Menu Click View and select Taskpad, you will get 3 tabs :
> General, Table Info and Wizards, Select Table Info and you'll see all the
> tables listed with rows count and size
> Mario Aoun
> "Mica Cooper" wrote:
|||Clint,
I have already shrank the db and it didn't change the size. I am looking for
a problem becuase I know the db shouldn't be anywhere near that large and
this info should help. Mario's post was great. I have NEVER seen that before
and it told the story pretty quick.
So guess what? Look at the view told me what a dunce I am. The problem is
the log file is autmatically growing...1.3G. I should have seen that by
looking at the .LDF. Gotta go fix that now.
Thanks Guys,
Mica
"Clint" <nobody@.nowhere.non> wrote in message
news:O6TXngrkFHA.1044@.tk2msftngp13.phx.gbl...
> Database size is a fuzzy thing. I don't know your level of SQL
> experience, so forgive me if I start off with some basics.
> What do you mean, when you say "over 1GB in size"? Right click on the
> database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go
> to properties. On the first tab ("General"), there's a "Size", and then a
> "Space Available". Is this the 1GB you're talking about? What does the
> "Space Available" have beside it?
> If that is the size that you're talking about, that includes the log file
> as well as the actual data file. Click on the next tab "Data Files", and
> see how much space is allocated. Then go to the "Transaction Log" tab,
> and see the same thing there. If the transaction log is huge, it may be
> as simple as either adjusting your backup procedures, or changing the
> "Recovery Model" on the "Options" tab.
> The other thing that can happen is that SQL database grow automatically,
> but the default settings do not let the databases shrink automatically.
> To shrink a database, right click on the database, and go to "All Tasks",
> then "Shrink Database". I seem to have the best luck with shrinking by
> clicking on the "Files" button on the first form, then manually setting
> the desired database file sizes by selecting first the data (.mdf) file,
> then the log (.ldf) file. You likely have to either do a complete backup
> or change the "Recovery Model" before you'll get a lot of space out of the
> log file.
> To find out the actual size of a particular table, you can execute the
> "sp_spaceused <tablename>" function in Query Analyzer. It will give you
> the number of rows, reserved size, data size, index size, and unused size.
> I hope this helped! I'm not a SQL expert, and I don't play one on TV.
> But post back with your results and any new questions, if you need more
> guidance.
> Clint
> "Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
> news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
>

Determine table size

Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
MicaHi Mica Cooper,
To Check table sizes, Select the db name in the enterprise under Databases,
In the Main Menu Click View and select Taskpad, you will get 3 tabs :
General, Table Info and Wizards, Select Table Info and you'll see all the
tables listed with rows count and size
Mario Aoun
"Mica Cooper" wrote:
> Hi,
> I have a client with a database that is running over 1G in size. Looking at
> the tables, I can't find any that would seem to be larger than a 1-2 meg
> with total size should be around 30meg. Is there any way to find out table
> sizes?
> Thanks,
> Mica
>
>|||Use sp_spaceused or see: http://vyaskn.tripod.com/sp_show_biggest_tables.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica|||Database size is a fuzzy thing. I don't know your level of SQL experience,
so forgive me if I start off with some basics.
What do you mean, when you say "over 1GB in size"? Right click on the
database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go to
properties. On the first tab ("General"), there's a "Size", and then a
"Space Available". Is this the 1GB you're talking about? What does the
"Space Available" have beside it?
If that is the size that you're talking about, that includes the log file as
well as the actual data file. Click on the next tab "Data Files", and see
how much space is allocated. Then go to the "Transaction Log" tab, and see
the same thing there. If the transaction log is huge, it may be as simple
as either adjusting your backup procedures, or changing the "Recovery Model"
on the "Options" tab.
The other thing that can happen is that SQL database grow automatically, but
the default settings do not let the databases shrink automatically. To
shrink a database, right click on the database, and go to "All Tasks", then
"Shrink Database". I seem to have the best luck with shrinking by clicking
on the "Files" button on the first form, then manually setting the desired
database file sizes by selecting first the data (.mdf) file, then the log
(.ldf) file. You likely have to either do a complete backup or change the
"Recovery Model" before you'll get a lot of space out of the log file.
To find out the actual size of a particular table, you can execute the
"sp_spaceused <tablename>" function in Query Analyzer. It will give you the
number of rows, reserved size, data size, index size, and unused size.
I hope this helped! I'm not a SQL expert, and I don't play one on TV. But
post back with your results and any new questions, if you need more
guidance.
Clint
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a client with a database that is running over 1G in size. Looking
> at the tables, I can't find any that would seem to be larger than a 1-2
> meg with total size should be around 30meg. Is there any way to find out
> table sizes?
> Thanks,
> Mica
>|||Mario,
That is COOL! In all the years I've used SQL Server I have never seen that.
Its exactly what I was looking for.
Mica
"Mario Aoun" <MarioAoun@.discussions.microsoft.com> wrote in message
news:8AD1DCE1-0E27-463B-BE9E-59D00D5137C2@.microsoft.com...
> Hi Mica Cooper,
> To Check table sizes, Select the db name in the enterprise under
> Databases,
> In the Main Menu Click View and select Taskpad, you will get 3 tabs :
> General, Table Info and Wizards, Select Table Info and you'll see all the
> tables listed with rows count and size
> Mario Aoun
> "Mica Cooper" wrote:
>> Hi,
>> I have a client with a database that is running over 1G in size. Looking
>> at
>> the tables, I can't find any that would seem to be larger than a 1-2 meg
>> with total size should be around 30meg. Is there any way to find out
>> table
>> sizes?
>> Thanks,
>> Mica
>>|||Clint,
I have already shrank the db and it didn't change the size. I am looking for
a problem becuase I know the db shouldn't be anywhere near that large and
this info should help. Mario's post was great. I have NEVER seen that before
and it told the story pretty quick.
So guess what? Look at the view told me what a dunce I am. The problem is
the log file is autmatically growing...1.3G. I should have seen that by
looking at the .LDF. Gotta go fix that now.
Thanks Guys,
Mica
"Clint" <nobody@.nowhere.non> wrote in message
news:O6TXngrkFHA.1044@.tk2msftngp13.phx.gbl...
> Database size is a fuzzy thing. I don't know your level of SQL
> experience, so forgive me if I start off with some basics.
> What do you mean, when you say "over 1GB in size"? Right click on the
> database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go
> to properties. On the first tab ("General"), there's a "Size", and then a
> "Space Available". Is this the 1GB you're talking about? What does the
> "Space Available" have beside it?
> If that is the size that you're talking about, that includes the log file
> as well as the actual data file. Click on the next tab "Data Files", and
> see how much space is allocated. Then go to the "Transaction Log" tab,
> and see the same thing there. If the transaction log is huge, it may be
> as simple as either adjusting your backup procedures, or changing the
> "Recovery Model" on the "Options" tab.
> The other thing that can happen is that SQL database grow automatically,
> but the default settings do not let the databases shrink automatically.
> To shrink a database, right click on the database, and go to "All Tasks",
> then "Shrink Database". I seem to have the best luck with shrinking by
> clicking on the "Files" button on the first form, then manually setting
> the desired database file sizes by selecting first the data (.mdf) file,
> then the log (.ldf) file. You likely have to either do a complete backup
> or change the "Recovery Model" before you'll get a lot of space out of the
> log file.
> To find out the actual size of a particular table, you can execute the
> "sp_spaceused <tablename>" function in Query Analyzer. It will give you
> the number of rows, reserved size, data size, index size, and unused size.
> I hope this helped! I'm not a SQL expert, and I don't play one on TV.
> But post back with your results and any new questions, if you need more
> guidance.
> Clint
> "Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
> news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I have a client with a database that is running over 1G in size. Looking
>> at the tables, I can't find any that would seem to be larger than a 1-2
>> meg with total size should be around 30meg. Is there any way to find out
>> table sizes?
>> Thanks,
>> Mica
>sql

Determine table size

Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
MicaHi Mica Cooper,
To Check table sizes, Select the db name in the enterprise under Databases,
In the Main Menu Click View and select Taskpad, you will get 3 tabs :
General, Table Info and Wizards, Select Table Info and you'll see all the
tables listed with rows count and size
Mario Aoun
"Mica Cooper" wrote:

> Hi,
> I have a client with a database that is running over 1G in size. Looking a
t
> the tables, I can't find any that would seem to be larger than a 1-2 meg
> with total size should be around 30meg. Is there any way to find out table
> sizes?
> Thanks,
> Mica
>
>|||Use sp_spaceused or see: http://vyaskn.tripod.com/sp_show_biggest_tables.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica|||Database size is a fuzzy thing. I don't know your level of SQL experience,
so forgive me if I start off with some basics.
What do you mean, when you say "over 1GB in size"? Right click on the
database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go to
properties. On the first tab ("General"), there's a "Size", and then a
"Space Available". Is this the 1GB you're talking about? What does the
"Space Available" have beside it?
If that is the size that you're talking about, that includes the log file as
well as the actual data file. Click on the next tab "Data Files", and see
how much space is allocated. Then go to the "Transaction Log" tab, and see
the same thing there. If the transaction log is huge, it may be as simple
as either adjusting your backup procedures, or changing the "Recovery Model"
on the "Options" tab.
The other thing that can happen is that SQL database grow automatically, but
the default settings do not let the databases shrink automatically. To
shrink a database, right click on the database, and go to "All Tasks", then
"Shrink Database". I seem to have the best luck with shrinking by clicking
on the "Files" button on the first form, then manually setting the desired
database file sizes by selecting first the data (.mdf) file, then the log
(.ldf) file. You likely have to either do a complete backup or change the
"Recovery Model" before you'll get a lot of space out of the log file.
To find out the actual size of a particular table, you can execute the
"sp_spaceused <tablename>" function in Query Analyzer. It will give you the
number of rows, reserved size, data size, index size, and unused size.
I hope this helped! I'm not a SQL expert, and I don't play one on TV. But
post back with your results and any new questions, if you need more
guidance.
Clint
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a client with a database that is running over 1G in size. Looking
> at the tables, I can't find any that would seem to be larger than a 1-2
> meg with total size should be around 30meg. Is there any way to find out
> table sizes?
> Thanks,
> Mica
>|||Mario,
That is COOL! In all the years I've used SQL Server I have never seen that.
Its exactly what I was looking for.
Mica
"Mario Aoun" <MarioAoun@.discussions.microsoft.com> wrote in message
news:8AD1DCE1-0E27-463B-BE9E-59D00D5137C2@.microsoft.com...[vbcol=seagreen]
> Hi Mica Cooper,
> To Check table sizes, Select the db name in the enterprise under
> Databases,
> In the Main Menu Click View and select Taskpad, you will get 3 tabs :
> General, Table Info and Wizards, Select Table Info and you'll see all the
> tables listed with rows count and size
> Mario Aoun
> "Mica Cooper" wrote:
>|||Clint,
I have already shrank the db and it didn't change the size. I am looking for
a problem becuase I know the db shouldn't be anywhere near that large and
this info should help. Mario's post was great. I have NEVER seen that before
and it told the story pretty quick.
So guess what? Look at the view told me what a dunce I am. The problem is
the log file is autmatically growing...1.3G. I should have seen that by
looking at the .LDF. Gotta go fix that now.
Thanks Guys,
Mica
"Clint" <nobody@.nowhere.non> wrote in message
news:O6TXngrkFHA.1044@.tk2msftngp13.phx.gbl...
> Database size is a fuzzy thing. I don't know your level of SQL
> experience, so forgive me if I start off with some basics.
> What do you mean, when you say "over 1GB in size"? Right click on the
> database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go
> to properties. On the first tab ("General"), there's a "Size", and then a
> "Space Available". Is this the 1GB you're talking about? What does the
> "Space Available" have beside it?
> If that is the size that you're talking about, that includes the log file
> as well as the actual data file. Click on the next tab "Data Files", and
> see how much space is allocated. Then go to the "Transaction Log" tab,
> and see the same thing there. If the transaction log is huge, it may be
> as simple as either adjusting your backup procedures, or changing the
> "Recovery Model" on the "Options" tab.
> The other thing that can happen is that SQL database grow automatically,
> but the default settings do not let the databases shrink automatically.
> To shrink a database, right click on the database, and go to "All Tasks",
> then "Shrink Database". I seem to have the best luck with shrinking by
> clicking on the "Files" button on the first form, then manually setting
> the desired database file sizes by selecting first the data (.mdf) file,
> then the log (.ldf) file. You likely have to either do a complete backup
> or change the "Recovery Model" before you'll get a lot of space out of the
> log file.
> To find out the actual size of a particular table, you can execute the
> "sp_spaceused <tablename>" function in Query Analyzer. It will give you
> the number of rows, reserved size, data size, index size, and unused size.
> I hope this helped! I'm not a SQL expert, and I don't play one on TV.
> But post back with your results and any new questions, if you need more
> guidance.
> Clint
> "Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
> news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
>

Determine status of processing cube

From C# I need to determine if a cube is currently processing. We are trying to avoid running MDX against a cube while in the act - it results in an error message to our users.

Does anybody know of a way to do this? Maybe with AMO?

Thanks in advance,

John

Hello John,

There is no easy way of doing this in AMO, but you could use the DISCOVER_LOCKS schema rowset to test whether there are write locks on your cube. The LOCK_STATUS and LOCK_TYPE fields returned by this discover could help you find this out.

Hope this helps,

Artur

Tuesday, March 27, 2012

Determine slow running reports

Take a look at the Execution Log topics in Books Online. All of the informaiton you want is stored in the ReportServer database. There is even a DTS package to extract the data and a set of reports which show you statistics.
Brian Welcker
Group Program Manager
SQL Server Reporting Services
--Original Message--
From: Randy Knight
Posted At: Thursday, October 06, 2005 2:53 PM
Posted To: microsoft.public.sqlserver.reportingsvcs
Conversation: Determine slow running reports
Subject: Re: Determine slow running reports
That would be great if I had control of the reports but as I said, the reports are published by analysts from all over the company. I just wondered if RS put some statistics or anything like that in the ReportServer database. It would seem to be useful ... how many times report is run, avg execution times, etc.
Guess I'll resort to good ol' SQL Profiler :)Just what I was looking for. Thanks.

Determine slow running reports

I've been tasked with optimizing our reporting environment and want to
target the worst performers first. The problem is that our reports are
published directly to the reporting server by reporting analysts
throughout the organization so I have very little knowled as to what's
out there, who uses them, etc. Is there a way to query reporting
services to determine historical exectuion times? IOW, give me the top
10 reports ordered by avg exectuion time descending or something of
that nature.In your data source defenition - instead of single query use a stored
procedure or just type in something like that:
declare @.start smalldatetime
declare @.end smalldatetime
set @.start = getdate()
-- your query
set @.end=getdate()
-- and at the end log the execution time like that
insert logtable values (reportname, username, getdate(), @.end-@.start)
"Randy Knight" <knight.randy@.gmail.com> wrote in message
news:1128523651.640818.14420@.o13g2000cwo.googlegroups.com...
> I've been tasked with optimizing our reporting environment and want to
> target the worst performers first. The problem is that our reports are
> published directly to the reporting server by reporting analysts
> throughout the organization so I have very little knowled as to what's
> out there, who uses them, etc. Is there a way to query reporting
> services to determine historical exectuion times? IOW, give me the top
> 10 reports ordered by avg exectuion time descending or something of
> that nature.
>|||That would be great if I had control of the reports but as I said, the
reports are published by analysts from all over the company. I just
wondered if RS put some statistics or anything like that in the
ReportServer database. It would seem to be useful ... how many times
report is run, avg execution times, etc.
Guess I'll resort to good ol' SQL Profiler :)|||SQL 2005 has just what you need... You must install the samples from the SQL
install CD.. This will give you a directory in program files/microsoft sql
server 2005/Report samples/Server management Samples. --
THe directions are included... You can get all kinds of performance related
reports, who runs what, how long it takes, how many times each report has
been run...
Hope this helps
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Randy Knight" wrote:
> I've been tasked with optimizing our reporting environment and want to
> target the worst performers first. The problem is that our reports are
> published directly to the reporting server by reporting analysts
> throughout the organization so I have very little knowled as to what's
> out there, who uses them, etc. Is there a way to query reporting
> services to determine historical exectuion times? IOW, give me the top
> 10 reports ordered by avg exectuion time descending or something of
> that nature.
>

Determine ServicePack level

Hello,
how one can determine ServicePack level of SQL Server 2000 installation? I
would like to do this without server running (not neccessary, but
preferably).
Nice day
Ondra.Have a look here:
http://vyaskn.tripod.com/sqlsps.htm
--
Andrew J. Kelly
SQL Server MVP
"Ondrej Sevecek" <ondra@.sevecek.com> wrote in message
news:u2F33DZaDHA.1832@.TK2MSFTNGP09.phx.gbl...
> Hello,
> how one can determine ServicePack level of SQL Server 2000 installation?
I
> would like to do this without server running (not neccessary, but
> preferably).
> Nice day
> Ondra.
>
>|||Ondra,
Without the SQL Server running? May be you can check the SQL Server errorlog
(C:\Program Files\Microsoft SQL Server\<instancename>\LOG).The first section
is the output of @.@.version.The numbers in the first line indicate the
service pack level.
- 8.00.760(SP3/SP3a)
- 8.00.534 (SP2)
- 8.00.384 (SP1)
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Ondrej Sevecek" <ondra@.sevecek.com> wrote in message
news:u2F33DZaDHA.1832@.TK2MSFTNGP09.phx.gbl...
> Hello,
> how one can determine ServicePack level of SQL Server 2000 installation?
I
> would like to do this without server running (not neccessary, but
> preferably).
> Nice day
> Ondra.
>
>

Determine service pack version of Analysis Services using AMO

Hi,

Our product uses features that require Analysis Services SP2 to be installed. How can I tell if an Analysis Services server is running SP2 or higher using Analysis Services Management Objects (AMO)? I'm not concerned about the Operating System service pack edition, I'm concerned about the SP version of Analysis Services.

Thanks.

Figured it out. Use the Version property off the Server object.

Server svr = new Server();

svr.Connect(serverName);

Version minASVersion = new Version(MIN_AS_SERVICES_VERSION);

Version serverVersion = new Version(svr.Version);

if (serverVersion < minASVersion)

{

// error

}

|||

You can get this using ADOMD too:

http://geekswithblogs.net/darrengosbell/archive/2007/07/14/SSAS-Detecting-the-version-of-SSAS-on-a-given-server.aspx

sql

Determine sequence of transactions in replication

I am running transactional replication with a push transformable
subscription. I am running into a PK error. After researching the error I
have found two transactions tied to record in the subscription database. So I
use the following to determine which transaction is getting fired first.
select * from distribution.dbo.MSrepl_commands
where publisher_database_id = 29
and article_id = 30
and xact_seqno = 0x0000235000000EFA000100000000
This returns a list of 58 transactions with the command_id and the command.
But I am unable to determine what the command is because it is not readable.
If I use
distribution.dbo.Sp_browsereplcmds
it gives me the command but not the command_id (sequence number).
How can I determine what the sequence of transactions is and read the
command?
Thanks for your time. Mike
Mike,
the code in sp_browsereplcmds is not too difficult. You could create your
own custom script using this as a template, but which includes the
command_id.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Determine security access to stored procedure through ASP

I have an ASP application running in a MS Windows Server 2003 computer
joined to a Windows 2000 Active Directory domain.
Different users have different roles, and the security in the SQL
database is based on Active Directory security groups (SQL server is
configured for Windows security and not SQL server security).
I would like to make certain ASP page show an icon depending on whether
the user has EXECUTE permission on a particular stored procedure.
Example: User X@.domain.com is a member of the Active Directory
security group APP-ADMIN. On the SQL server side, APP-ADMIN is the log
in of a SQL user called "App Admins", and App Admins have been granted
EXECUTE permission to the stored procedure spDeleteSomething. I want
the ASP to determine if X@.domain.com has the permission on
spDeleteSomething so an icon is displayed; in this case it should be
displayed.
I hope I made this clear, but if not, feel free to ask for more details.You can use the IS_MEMBER function to check to see if the current login is a
member of the App Admins group:
SELECT IS_MEMBER('domain.com\App Admins')
returns 1 if the current login is a member of the App Admins security group,
0 if they aren't.
"webJose" wrote:

> I have an ASP application running in a MS Windows Server 2003 computer
> joined to a Windows 2000 Active Directory domain.
> Different users have different roles, and the security in the SQL
> database is based on Active Directory security groups (SQL server is
> configured for Windows security and not SQL server security).
> I would like to make certain ASP page show an icon depending on whether
> the user has EXECUTE permission on a particular stored procedure.
> Example: User X@.domain.com is a member of the Active Directory
> security group APP-ADMIN. On the SQL server side, APP-ADMIN is the log
> in of a SQL user called "App Admins", and App Admins have been granted
> EXECUTE permission to the stored procedure spDeleteSomething. I want
> the ASP to determine if X@.domain.com has the permission on
> spDeleteSomething so an icon is displayed; in this case it should be
> displayed.
> I hope I made this clear, but if not, feel free to ask for more details.
>|||In ASP.NET, you can determine if a web user is a member of an Active
Directory group or role without going through SQL Server.
How To: Use Role Manager in ASP.NET 2.0
http://msdn.microsoft.com/library/d... />
000013.asp
For example:
if (Roles.IsUserInRole("TestRole"))
{
Label1.Text = User.Identity.Name + " is in role TestRole";
}
else
{
Label1.Text = User.Identity.Name + " is NOT in role TestRole";
}
"webJose" <webJose@.gmail.com> wrote in message
news:1140194004.035980.226530@.g14g2000cwa.googlegroups.com...
>I have an ASP application running in a MS Windows Server 2003 computer
> joined to a Windows 2000 Active Directory domain.
> Different users have different roles, and the security in the SQL
> database is based on Active Directory security groups (SQL server is
> configured for Windows security and not SQL server security).
> I would like to make certain ASP page show an icon depending on whether
> the user has EXECUTE permission on a particular stored procedure.
> Example: User X@.domain.com is a member of the Active Directory
> security group APP-ADMIN. On the SQL server side, APP-ADMIN is the log
> in of a SQL user called "App Admins", and App Admins have been granted
> EXECUTE permission to the stored procedure spDeleteSomething. I want
> the ASP to determine if X@.domain.com has the permission on
> spDeleteSomething so an icon is displayed; in this case it should be
> displayed.
> I hope I made this clear, but if not, feel free to ask for more details.
>|||JT: Thank you for your response. Although highly enlighting, I am not
using .NET (I know! I should be). :-)
Mark: Thank you for your response. IS_MEMBER workS OK for me. I'll
create user-defined functions to encapsulate this functionality. Now,
out of curiosity, is there a way to test for EXECUTE permissions on any
stored procedure like on the fly? For example, something like:
If CanExecute("spSomeSP") Then
Response.Write "You got it!"
End If
And CanExecute() would test somehow the permissions for the user ID on
the sp name passed as argument.|||You could query it from the sysprotects system table.
For SQL 2000:
IF EXISTS (
SELECT 1 FROM sysprotects
WHERE [id] = OBJECT_ID('yourproc')
AND [uid] = USER_ID()
AND [action] = 224
AND [protecttype] IN (204,205)
)
BEGIN
PRINT 'You have access'
END
For SQL 2005
IF EXISTS (
SELECT 1 FROM sys.database_permissions
WHERE [class] = 1
AND [major_id] = OBJECT_ID('yourproc')
AND [grantee_principal_id] = USER_ID()
AND [type] = 'EX'
AND [state] IN ('G','W')
)
BEGIN
PRINT 'You have access'
END
"webJose" wrote:

> JT: Thank you for your response. Although highly enlighting, I am not
> using .NET (I know! I should be). :-)
> Mark: Thank you for your response. IS_MEMBER workS OK for me. I'll
> create user-defined functions to encapsulate this functionality. Now,
> out of curiosity, is there a way to test for EXECUTE permissions on any
> stored procedure like on the fly? For example, something like:
> If CanExecute("spSomeSP") Then
> Response.Write "You got it!"
> End If
> And CanExecute() would test somehow the permissions for the user ID on
> the sp name passed as argument.
>|||One caveat to this: the will only return that the user has access if the use
r
they map too was explicity given access to execute procedure. If their
permission is inherited from membership in a server or database role, my
script will not show them as having access.
If you are explicitly giving execute permission to each of the users, then
it will work.
"Mark Williams" wrote:
> You could query it from the sysprotects system table.
> For SQL 2000:
> IF EXISTS (
> SELECT 1 FROM sysprotects
> WHERE [id] = OBJECT_ID('yourproc')
> AND [uid] = USER_ID()
> AND [action] = 224
> AND [protecttype] IN (204,205)
> )
> BEGIN
> PRINT 'You have access'
> END
> For SQL 2005
> IF EXISTS (
> SELECT 1 FROM sys.database_permissions
> WHERE [class] = 1
> AND [major_id] = OBJECT_ID('yourproc')
> AND [grantee_principal_id] = USER_ID()
> AND [type] = 'EX'
> AND [state] IN ('G','W')
> )
> BEGIN
> PRINT 'You have access'
> END
> --
> "webJose" wrote:
>

Determine running size of memtoleave area

Hi group,
it there an easy way to determine the size of the MemToLeave area on a
running instance?
I know this is caculated at startup as
[max worker threads] * 0.5MB + [-g Memory]
but we want to verify that the instance is using our -g setting.
TIA,
We use VMSTAT.EXE to monitor the total amount free and xp_memory_size (which
comes with SQL Litespeed) to monitor the max contiguous region. I can't
remember where we got vmstat from but I think it was probably from PSS.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns969E84434E3CDgurbaohotmailcom@.129.250.171. 68...
> Hi group,
> it there an easy way to determine the size of the MemToLeave area on a
> running instance?
> I know this is caculated at startup as
> [max worker threads] * 0.5MB + [-g Memory]
> but we want to verify that the instance is using our -g setting.
> TIA,
|||Will finding max contig help me?
We are experiencing this message from time to time:
2005-07-26 16:49:41.52 spid63 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2005-07-26 16:49:41.63 spid63 Buffer Distribution: Stolen=17899
Free=5 Procedures=161541
Inram=0 Dirty=17995 Kept=0
I/O=0, Latched=1980, Other=138372
2005-07-26 16:49:41.63 spid63 Buffer Counts: Commited=337792 Target=
337792 Hashed=158347
InternalReservation=2266 ExternalReservation=3009 Min
Free=1024
2005-07-26 16:49:41.63 spid63 Procedure Cache: TotalProcs=35140
TotalPages=161541 InUsePages=108631
2005-07-26 16:49:41.63 spid63 Dynamic Memory Manager: Stolen=176976
OS Reserved=10416
OS Committed=9749
OS In Use=8822
Query Plan=171722 Optimizer=0
General=12434
Utilities=1151 Connection=263
2005-07-26 16:49:41.63 spid63 Global Memory Objects: Resource=7963
Locks=105
SQLCache=3311 Replication=4
LockBytes=2 ServerGlobal=56
Xact=93
2005-07-26 16:49:41.63 spid63 Query Memory Manager: Grants=3
Waiting=0 Maximum=163539 Available=158066
For all I know, I may find the size of the memtoleave area in these
numbers?
Regards,
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
news:eY2UaZVkFHA.1048@.tk2msftngp13.phx.gbl:

> We use VMSTAT.EXE to monitor the total amount free and xp_memory_size
> (which comes with SQL Litespeed) to monitor the max contiguous region.
> I can't remember where we got vmstat from but I think it was probably
> from PSS.
>
|||Indeed you can. Under dynamic memory manager you will find,
OS Reserved - Reserved for >64KB requests (This is memtoleave)
OS Committed - Memory that has been committed from memtoleave
OS In Use - Portion of OS Committed backing outstanding memory allocations
Check http://support.microsoft.com/?id=271624 for full reference.

Determine running size of memtoleave area

Hi group,
it there an easy way to determine the size of the MemToLeave area on a
running instance?
I know this is caculated at startup as
[max worker threads] * 0.5MB + [-g Memory]
but we want to verify that the instance is using our -g setting.
TIA,We use VMSTAT.EXE to monitor the total amount free and xp_memory_size (which
comes with SQL Litespeed) to monitor the max contiguous region. I can't
remember where we got vmstat from but I think it was probably from PSS.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns969E84434E3CDgurbaohotmailcom@.12
9.250.171.68...
> Hi group,
> it there an easy way to determine the size of the MemToLeave area on a
> running instance?
> I know this is caculated at startup as
> [max worker threads] * 0.5MB + [-g Memory]
> but we want to verify that the instance is using our -g setting.
> TIA,|||Will finding max contig help me?
We are experiencing this message from time to time:
2005-07-26 16:49:41.52 spid63 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2005-07-26 16:49:41.63 spid63 Buffer Distribution: Stolen=17899
Free=5 Procedures=161541
Inram=0 Dirty=17995 Kept=0
I/O=0, Latched=1980, Other=138372
2005-07-26 16:49:41.63 spid63 Buffer Counts: Commited=337792 Target=
337792 Hashed=158347
InternalReservation=2266 ExternalReservation=3009 Min
Free=1024
2005-07-26 16:49:41.63 spid63 Procedure Cache: TotalProcs=35140
TotalPages=161541 InUsePages=108631
2005-07-26 16:49:41.63 spid63 Dynamic Memory Manager: Stolen=176976
OS Reserved=10416
OS Committed=9749
OS In Use=8822
Query Plan=171722 Optimizer=0
General=12434
Utilities=1151 Connection=263
2005-07-26 16:49:41.63 spid63 Global Memory Objects: Resource=7963
Locks=105
SQLCache=3311 Replication=4
LockBytes=2 ServerGlobal=56
Xact=93
2005-07-26 16:49:41.63 spid63 Query Memory Manager: Grants=3
Waiting=0 Maximum=163539 Available=158066
For all I know, I may find the size of the memtoleave area in these
numbers?
Regards,
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
news:eY2UaZVkFHA.1048@.tk2msftngp13.phx.gbl:

> We use VMSTAT.EXE to monitor the total amount free and xp_memory_size
> (which comes with SQL Litespeed) to monitor the max contiguous region.
> I can't remember where we got vmstat from but I think it was probably
> from PSS.
>|||Indeed you can. Under dynamic memory manager you will find,
OS Reserved - Reserved for >64KB requests (This is memtoleave)
OS Committed - Memory that has been committed from memtoleave
OS In Use - Portion of OS Committed backing outstanding memory allocations
Check http://support.microsoft.com/?id=271624 for full reference.sql

Determine running size of memtoleave area

Hi group,
it there an easy way to determine the size of the MemToLeave area on a
running instance?
I know this is caculated at startup as
[max worker threads] * 0.5MB + [-g Memory]
but we want to verify that the instance is using our -g setting.
TIA,We use VMSTAT.EXE to monitor the total amount free and xp_memory_size (which
comes with SQL Litespeed) to monitor the max contiguous region. I can't
remember where we got vmstat from but I think it was probably from PSS.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns969E84434E3CDgurbaohotmailcom@.129.250.171.68...
> Hi group,
> it there an easy way to determine the size of the MemToLeave area on a
> running instance?
> I know this is caculated at startup as
> [max worker threads] * 0.5MB + [-g Memory]
> but we want to verify that the instance is using our -g setting.
> TIA,|||Will finding max contig help me?
We are experiencing this message from time to time:
2005-07-26 16:49:41.52 spid63 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2005-07-26 16:49:41.63 spid63 Buffer Distribution: Stolen=17899
Free=5 Procedures=161541
Inram=0 Dirty=17995 Kept=0
I/O=0, Latched=1980, Other=138372
2005-07-26 16:49:41.63 spid63 Buffer Counts: Commited=337792 Target=337792 Hashed=158347
InternalReservation=2266 ExternalReservation=3009 Min
Free=1024
2005-07-26 16:49:41.63 spid63 Procedure Cache: TotalProcs=35140
TotalPages=161541 InUsePages=108631
2005-07-26 16:49:41.63 spid63 Dynamic Memory Manager: Stolen=176976
OS Reserved=10416
OS Committed=9749
OS In Use=8822
Query Plan=171722 Optimizer=0
General=12434
Utilities=1151 Connection=263
2005-07-26 16:49:41.63 spid63 Global Memory Objects: Resource=7963
Locks=105
SQLCache=3311 Replication=4
LockBytes=2 ServerGlobal=56
Xact=93
2005-07-26 16:49:41.63 spid63 Query Memory Manager: Grants=3
Waiting=0 Maximum=163539 Available=158066
For all I know, I may find the size of the memtoleave area in these
numbers?
Regards,
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
news:eY2UaZVkFHA.1048@.tk2msftngp13.phx.gbl:
> We use VMSTAT.EXE to monitor the total amount free and xp_memory_size
> (which comes with SQL Litespeed) to monitor the max contiguous region.
> I can't remember where we got vmstat from but I think it was probably
> from PSS.
>

Thursday, March 29, 2012

Determine when a Service Pack was applied?

Determine when a Service Pack was applied?

Determine when a Service Pack was applied?

Determine what User Account SQL Server is running under

Determine what User Account SQL Server is running under

Determine what User Account SQL Server is running under

Determine what server i'm running on

Determine table size

Determine table size

Determine table size

Determine status of processing cube

Tuesday, March 27, 2012

Determine slow running reports

Determine slow running reports

Determine ServicePack level

Determine service pack version of Analysis Services using AMO

Determine sequence of transactions in replication

Determine security access to stored procedure through ASP

Determine running size of memtoleave area

Determine running size of memtoleave area

Determine running size of memtoleave area

Determining Security

Blog Archive

About Me