I want to find the size of the physical disks on a SQL
server. I have tried using xp_cmdshell but i cannot find a
command that will return the disk size. The closest that i
have come is forating the results of chkdsk but this takes
time to run. Can anyone offer any other suggestions. Ist
there a command to show this or perhaps there is a XP or
SP that will do the job
Cheersmat
CREATE table DriveTable (Drive varchar(10),[MB Free] int)
INSERT into Drivetable Exec xp_fixeddrives
SELECT * FROM DriveTable
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:22f4901c45e7c$417e04f0$a601280a@.phx.gbl...
> I want to find the size of the physical disks on a SQL
> server. I have tried using xp_cmdshell but i cannot find a
> command that will return the disk size. The closest that i
> have come is forating the results of chkdsk but this takes
> time to run. Can anyone offer any other suggestions. Ist
> there a command to show this or perhaps there is a XP or
> SP that will do the job
> Cheers|||Hi,
Execute the command "XP_FIXEDDRIVES" from query analyzer to the Free size in
MB for each of the fixed drives.
--
Thanks
Hari
MCDBA
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:22f4901c45e7c$417e04f0$a601280a@.phx.gbl...
> I want to find the size of the physical disks on a SQL
> server. I have tried using xp_cmdshell but i cannot find a
> command that will return the disk size. The closest that i
> have come is forating the results of chkdsk but this takes
> time to run. Can anyone offer any other suggestions. Ist
> there a command to show this or perhaps there is a XP or
> SP that will do the job
> Cheers|||Hi ,
use following
exec master..xp_fixeddrives
it will return free space on the drives
Thanks
Ajay
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:22f4901c45e7c$417e04f0$a601280a@.phx.gbl...
> I want to find the size of the physical disks on a SQL
> server. I have tried using xp_cmdshell but i cannot find a
> command that will return the disk size. The closest that i
> have come is forating the results of chkdsk but this takes
> time to run. Can anyone offer any other suggestions. Ist
> there a command to show this or perhaps there is a XP or
> SP that will do the job
> Cheers|||I need to determine the actual size of the disks, not the
free space available
>--Original Message--
>mat
>CREATE table DriveTable (Drive varchar(10),[MB Free] int)
>INSERT into Drivetable Exec xp_fixeddrives
>SELECT * FROM DriveTable
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message
>news:22f4901c45e7c$417e04f0$a601280a@.phx.gbl...
>> I want to find the size of the physical disks on a SQL
>> server. I have tried using xp_cmdshell but i cannot
find a
>> command that will return the disk size. The closest
that i
>> have come is forating the results of chkdsk but this
takes
>> time to run. Can anyone offer any other suggestions. Ist
>> there a command to show this or perhaps there is a XP or
>> SP that will do the job
>> Cheers
>
>.
>|||Jasper Smith (SQL Server MVP) has written very useful stored procedure
use master
go
CREATE PROCEDURE sp_diskspace
AS
SET NOCOUNT ON
DECLARE @.hr int
DECLARE @.fso int
DECLARE @.drive char(1)
DECLARE @.odrive int
DECLARE @.TotalSize varchar(20)
DECLARE @.MB bigint ; SET @.MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)
INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @.hr=sp_OACreate 'Scripting.FileSystemObject',@.fso OUT
IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @.drive
WHILE @.@.FETCH_STATUS=0
BEGIN
EXEC @.hr = sp_OAMethod @.fso,'GetDrive', @.odrive OUT, @.drive
IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.fso
EXEC @.hr = sp_OAGetProperty @.odrive,'TotalSize', @.TotalSize OUT
IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.odrive
UPDATE #drives
SET TotalSize=@.TotalSize/@.MB
WHERE drive=@.drive
FETCH NEXT FROM dcur INTO @.drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @.hr=sp_OADestroy @.fso
IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.fso
SELECT drive,
FreeSpace as 'Free(MB)',
TotalSize as 'Total(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'
FROM #drives
ORDER BY drive
DROP TABLE #drives
RETURN
go
<anonymous@.discussions.microsoft.com> wrote in message
news:237d901c45e7d$924f25c0$a501280a@.phx.gbl...
> I need to determine the actual size of the disks, not the
> free space available
> >--Original Message--
> >mat
> >CREATE table DriveTable (Drive varchar(10),[MB Free] int)
> >INSERT into Drivetable Exec xp_fixeddrives
> >
> >SELECT * FROM DriveTable
> >
> >"mat" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:22f4901c45e7c$417e04f0$a601280a@.phx.gbl...
> >> I want to find the size of the physical disks on a SQL
> >> server. I have tried using xp_cmdshell but i cannot
> find a
> >> command that will return the disk size. The closest
> that i
> >> have come is forating the results of chkdsk but this
> takes
> >> time to run. Can anyone offer any other suggestions. Ist
> >> there a command to show this or perhaps there is a XP or
> >> SP that will do the job
> >>
> >> Cheers
> >
> >
> >.
> >|||The code can be found here http://www.sqldbatips.com/showcode.asp?ID=4
--
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
<anonymous@.discussions.microsoft.com> wrote in message
news:237d901c45e7d$924f25c0$a501280a@.phx.gbl...
> I need to determine the actual size of the disks, not the
> free space available
> >--Original Message--
> >mat
> >CREATE table DriveTable (Drive varchar(10),[MB Free] int)
> >INSERT into Drivetable Exec xp_fixeddrives
> >
> >SELECT * FROM DriveTable
> >
> >"mat" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:22f4901c45e7c$417e04f0$a601280a@.phx.gbl...
> >> I want to find the size of the physical disks on a SQL
> >> server. I have tried using xp_cmdshell but i cannot
> find a
> >> command that will return the disk size. The closest
> that i
> >> have come is forating the results of chkdsk but this
> takes
> >> time to run. Can anyone offer any other suggestions. Ist
> >> there a command to show this or perhaps there is a XP or
> >> SP that will do the job
> >>
> >> Cheers
> >
> >
> >.
> >
2012年3月20日星期二
DISCOVER_MEMORYUSAGE
The DISCOVER_MEMORYUSAGE command is not well documented. Any chance someone from the dev team could give a brief definition of each field you get in the results of this command?
I'm hoping for a definition so I can provide better documentation here (see the field definitions at the bottom):
http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=MemoryUsage&referringTitle=Home
Thanks.
I was doing research on this recently and this is the information I gathered:
CanShrink - true if it can shrink (represented in perfmon counter Shrinkable, versus Unshrinkable)
MemoryUsed - bytes of memory used
MemoryAllocated - bytes of memory allocated
MemoryAllocBase - bytes for base (initial) allocation
MemoryAllocFromAlloc - this is for recursive definitions
Elements - count of elements
I hope this helps.
|||Thanks for the info. I probably shouldn't have asked for "brief" definitions as I was hoping for a little more info. But those definitions are better than nothing!
Some other questions if you should feel so inclined to answer:
1. What's considered an "element"? Does this tie to count of members in a dimension? Count of rows in a partition? Count of attribute hierarchies in a dimension?
2. What are recursive definitions?
3. Would it be more accurate to say that for memory that's shrinkable, it's the MemoryUsed or the MemoryAllocated that can be shrunk?
4. Any general suggestions for things to look for when you're studying the results of DISCOVER_MEMORYUSAGE?
5. Any feedback on the MemoryUsage class would be welcome: http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=MemoryUsage&referringTitle=Home
2012年3月7日星期三
Disabling triggers with alter table, execute as?
I'm disabling some triggers in SQL Server 2000 with the following command
launched from another trigger:
ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
I want normal users to be able to run that command. Is there a kind of
"execute as" I can use in SQL Server 2000 to do it?
Thanks.Before I answer: are you sure you want to do this? The entire point of a
trigger is to fire regardless of user control. Be very careful here. I'm
going to lay out an option in 2000, but I don't endorse it's usage (I'm
presenting it simply because I don't know exactly what you're business goal
is - it may be perfectly valid).
Technically you could create a job that disables the trigger, then give
users access to the job and let them call it from within a stored procedure
or such. Be sure not to create a schedule for the job. With a SQL Agent job,
you can control (somewhat) the execution authority.
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||Is that wise?
For the user to execute an ALTER table, they will have to be provided table
ownership privileges. That means that the users can alter the table anyway
they desire -DROP columns, ADD columns, DROP TABLE, etc.
If you are trying to stop nested Triggers, there is another way. Use
sp_configure to turn on/off the Using Nested Triggers configuration option.
(Or use Server properties in Enterprise Manager.)
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||b3nny80y wrote:
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.
Why would you want to do this? Please explain further what you're
trying to accomplish. If you're trying to disable the triggers for
specific users, you can accomplish the same effect by putting IF/ELSE
code in the trigger that checks the current username, and simply doesn't
execute the code in the trigger.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com
launched from another trigger:
ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
I want normal users to be able to run that command. Is there a kind of
"execute as" I can use in SQL Server 2000 to do it?
Thanks.Before I answer: are you sure you want to do this? The entire point of a
trigger is to fire regardless of user control. Be very careful here. I'm
going to lay out an option in 2000, but I don't endorse it's usage (I'm
presenting it simply because I don't know exactly what you're business goal
is - it may be perfectly valid).
Technically you could create a job that disables the trigger, then give
users access to the job and let them call it from within a stored procedure
or such. Be sure not to create a schedule for the job. With a SQL Agent job,
you can control (somewhat) the execution authority.
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||Is that wise?
For the user to execute an ALTER table, they will have to be provided table
ownership privileges. That means that the users can alter the table anyway
they desire -DROP columns, ADD columns, DROP TABLE, etc.
If you are trying to stop nested Triggers, there is another way. Use
sp_configure to turn on/off the Using Nested Triggers configuration option.
(Or use Server properties in Enterprise Manager.)
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||b3nny80y wrote:
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.
Why would you want to do this? Please explain further what you're
trying to accomplish. If you're trying to disable the triggers for
specific users, you can accomplish the same effect by putting IF/ELSE
code in the trigger that checks the current username, and simply doesn't
execute the code in the trigger.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com
2012年2月25日星期六
Disabling SQL jobs using command line
Hi,
Is it possble to disable a SQL using command line?
If so, pls show me how or direct me to somewhere where I can find the
information.
Thanks in advance
Simply stop the service, see the Screencast on my website for more
detailed information.
In summary you just use the NET STOP command.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Sorry did not read the "job" in the subject:
UPDATE sysjobs
SET enabled = 0
WHERE jobId = 'SomeId'
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Or
calling sp_update_job via SQLCMD or OSQL.
(No I think I am done. :-) )
|||Sure. try:
exec msdb..sp_update_job @.job_name = 'yourjobname', @.enabled=0
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Thanks everyboday. It worked great!!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OmCG5gCUHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Sure. try:
> exec msdb..sp_update_job @.job_name = 'yourjobname', @.enabled=0
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
Is it possble to disable a SQL using command line?
If so, pls show me how or direct me to somewhere where I can find the
information.
Thanks in advance
Simply stop the service, see the Screencast on my website for more
detailed information.
In summary you just use the NET STOP command.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Sorry did not read the "job" in the subject:
UPDATE sysjobs
SET enabled = 0
WHERE jobId = 'SomeId'
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Or
calling sp_update_job via SQLCMD or OSQL.
(No I think I am done. :-) )
|||Sure. try:
exec msdb..sp_update_job @.job_name = 'yourjobname', @.enabled=0
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Thanks everyboday. It worked great!!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OmCG5gCUHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Sure. try:
> exec msdb..sp_update_job @.job_name = 'yourjobname', @.enabled=0
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
Disabling SQL jobs using command line
Hi,
Is it possble to disable a SQL using command line?
If so, pls show me how or direct me to somewhere where I can find the
information.
Thanks in advanceSimply stop the service, see the Screencast on my website for more
detailed information.
In summary you just use the NET STOP command.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Sorry did not read the "job" in the subject:
UPDATE sysjobs
SET enabled = 0
WHERE jobId = 'SomeId'
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Or
calling sp_update_job via SQLCMD or OSQL.
(No I think I am done. :-) )|||Check out sp_update_job.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AppleDopod" <hk_007@.hotmail.com> wrote in message news:OgWwQxBUHHA.4832@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Is it possble to disable a SQL using command line?
> If so, pls show me how or direct me to somewhere where I can find the information.
> Thanks in advance
>|||Sure. try:
exec msdb..sp_update_job @.job_name = 'yourjobname', @.enabled=0
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Thanks everyboday. It worked great!!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OmCG5gCUHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Sure. try:
> exec msdb..sp_update_job @.job_name = 'yourjobname', @.enabled=0
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
Is it possble to disable a SQL using command line?
If so, pls show me how or direct me to somewhere where I can find the
information.
Thanks in advanceSimply stop the service, see the Screencast on my website for more
detailed information.
In summary you just use the NET STOP command.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Sorry did not read the "job" in the subject:
UPDATE sysjobs
SET enabled = 0
WHERE jobId = 'SomeId'
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Or
calling sp_update_job via SQLCMD or OSQL.
(No I think I am done. :-) )|||Check out sp_update_job.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AppleDopod" <hk_007@.hotmail.com> wrote in message news:OgWwQxBUHHA.4832@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Is it possble to disable a SQL using command line?
> If so, pls show me how or direct me to somewhere where I can find the information.
> Thanks in advance
>|||Sure. try:
exec msdb..sp_update_job @.job_name = 'yourjobname', @.enabled=0
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Thanks everyboday. It worked great!!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OmCG5gCUHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Sure. try:
> exec msdb..sp_update_job @.job_name = 'yourjobname', @.enabled=0
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
Disabling SQL jobs using command line
Hi,
Is it possble to disable a SQL using command line?
If so, pls show me how or direct me to somewhere where I can find the
information.
Thanks in advanceSimply stop the service, see the Screencast on my website for more
detailed information.
In summary you just use the NET STOP command.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Sorry did not read the "job" in the subject:
UPDATE sysjobs
SET enabled = 0
WHERE jobId = 'SomeId'
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Check out sp_update_job.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AppleDopod" <hk_007@.hotmail.com> wrote in message news:OgWwQxBUHHA.4832@.TK2MSFTNGP04.phx.gb
l...
> Hi,
> Is it possble to disable a SQL using command line?
> If so, pls show me how or direct me to somewhere where I can find the info
rmation.
> Thanks in advance
>|||Or
calling sp_update_job via SQLCMD or OSQL.
(No I think I am done. :-) )|||Sure. try:
exec msdb..sp_update_job @.job_name = 'yourjobname', @.enabled=0
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Thanks everyboday. It worked great!!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OmCG5gCUHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Sure. try:
> exec msdb..sp_update_job @.job_name = 'yourjobname', @.enabled=0
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
Is it possble to disable a SQL using command line?
If so, pls show me how or direct me to somewhere where I can find the
information.
Thanks in advanceSimply stop the service, see the Screencast on my website for more
detailed information.
In summary you just use the NET STOP command.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Sorry did not read the "job" in the subject:
UPDATE sysjobs
SET enabled = 0
WHERE jobId = 'SomeId'
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Check out sp_update_job.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AppleDopod" <hk_007@.hotmail.com> wrote in message news:OgWwQxBUHHA.4832@.TK2MSFTNGP04.phx.gb
l...
> Hi,
> Is it possble to disable a SQL using command line?
> If so, pls show me how or direct me to somewhere where I can find the info
rmation.
> Thanks in advance
>|||Or
calling sp_update_job via SQLCMD or OSQL.
(No I think I am done. :-) )|||Sure. try:
exec msdb..sp_update_job @.job_name = 'yourjobname', @.enabled=0
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Thanks everyboday. It worked great!!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OmCG5gCUHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Sure. try:
> exec msdb..sp_update_job @.job_name = 'yourjobname', @.enabled=0
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
订阅：
博文 (Atom)