Disk Size

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
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
|||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[vbcol=seagreen]
>news:22f4901c45e7c$417e04f0$a601280a@.phx.gbl...
find a[vbcol=seagreen]
that i[vbcol=seagreen]
takes
>
>.
>
|||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...[vbcol=seagreen]
> I need to determine the actual size of the disks, not the
> free space available
> message
> find a
> that i
> takes
|||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...[vbcol=seagreen]
> I need to determine the actual size of the disks, not the
> free space available
> message
> find a
> that i
> takes

Disk Size

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...
find a[vbcol=seagreen]
that i[vbcol=seagreen]
takes[vbcol=seagreen]
>
>.
>|||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...[vbcol=seagreen]
> I need to determine the actual size of the disks, not the
> free space available
> message
> find a
> that i
> takes|||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...[vbcol=seagreen]
> I need to determine the actual size of the disks, not the
> free space available
> message
> find a
> that i
> takessql

Disk defragmentation on SQLserver

SQLserver 200 SP3 running on Win2000
I check disk D: where all the databases reside and found that it is heavily
fragmented. The list of most fragmented files include some important
database (MDF) files.
I would like to defragment the disk but also concerned about possible
problem with the databases in question since they are up and running during
the defragmentation process.
What's a safe procedure to perform defragmentation on the the disk and how
to reduce fragment in the furture?
Any suggestion is greatly appreciated.
Bill
First off always make sure you have FULL and tested backups before
attempting something like that. While some vendors state they can defrag
the sql files while running I personally would not trust my data to it. If
you want to prevent fragmentation in the first place you should always start
with a clean disk and each time you add a new database you should do the
following:
One at a time:
1. Create the database with the files at say 10MB in size. (this is so you
can always shrink it down later if need be)
2. Immediately after creating the database, alter the database and grow
each file to the size you think you will need for the next year or two
allowing plenty of room for free space in each file.
If you do that for each database as you create them the files will be
contiguous on disk and there will be no need to defrag them in the future
unless you srink or grow them again.
Andrew J. Kelly SQL MVP
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:%2368scSPcEHA.2520@.TK2MSFTNGP12.phx.gbl...
> SQLserver 200 SP3 running on Win2000
> I check disk D: where all the databases reside and found that it is
heavily
> fragmented. The list of most fragmented files include some important
> database (MDF) files.
> I would like to defragment the disk but also concerned about possible
> problem with the databases in question since they are up and running
during
> the defragmentation process.
> What's a safe procedure to perform defragmentation on the the disk and how
> to reduce fragment in the furture?
> Any suggestion is greatly appreciated.
> Bill
>

Disk defragmentation on SQLserver

SQLserver 200 SP3 running on Win2000
I check disk D: where all the databases reside and found that it is heavily
fragmented. The list of most fragmented files include some important
database (MDF) files.
I would like to defragment the disk but also concerned about possible
problem with the databases in question since they are up and running during
the defragmentation process.
What's a safe procedure to perform defragmentation on the the disk and how
to reduce fragment in the furture?
Any suggestion is greatly appreciated.
BillFirst off always make sure you have FULL and tested backups before
attempting something like that. While some vendors state they can defrag
the sql files while running I personally would not trust my data to it. If
you want to prevent fragmentation in the first place you should always start
with a clean disk and each time you add a new database you should do the
following:
One at a time:
1. Create the database with the files at say 10MB in size. (this is so you
can always shrink it down later if need be)
2. Immediately after creating the database, alter the database and grow
each file to the size you think you will need for the next year or two
allowing plenty of room for free space in each file.
If you do that for each database as you create them the files will be
contiguous on disk and there will be no need to defrag them in the future
unless you srink or grow them again.
--
Andrew J. Kelly SQL MVP
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:%2368scSPcEHA.2520@.TK2MSFTNGP12.phx.gbl...
> SQLserver 200 SP3 running on Win2000
> I check disk D: where all the databases reside and found that it is
heavily
> fragmented. The list of most fragmented files include some important
> database (MDF) files.
> I would like to defragment the disk but also concerned about possible
> problem with the databases in question since they are up and running
during
> the defragmentation process.
> What's a safe procedure to perform defragmentation on the the disk and how
> to reduce fragment in the furture?
> Any suggestion is greatly appreciated.
> Bill
>

Disk defragmentation on SQLserver

SQLserver 200 SP3 running on Win2000
I check disk D: where all the databases reside and found that it is heavily
fragmented. The list of most fragmented files include some important
database (MDF) files.
I would like to defragment the disk but also concerned about possible
problem with the databases in question since they are up and running during
the defragmentation process.
What's a safe procedure to perform defragmentation on the the disk and how
to reduce fragment in the furture?
Any suggestion is greatly appreciated.
BillFirst off always make sure you have FULL and tested backups before
attempting something like that. While some vendors state they can defrag
the sql files while running I personally would not trust my data to it. If
you want to prevent fragmentation in the first place you should always start
with a clean disk and each time you add a new database you should do the
following:
One at a time:
1. Create the database with the files at say 10MB in size. (this is so you
can always shrink it down later if need be)
2. Immediately after creating the database, alter the database and grow
each file to the size you think you will need for the next year or two
allowing plenty of room for free space in each file.
If you do that for each database as you create them the files will be
contiguous on disk and there will be no need to defrag them in the future
unless you srink or grow them again.
Andrew J. Kelly SQL MVP
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:%2368scSPcEHA.2520@.TK2MSFTNGP12.phx.gbl...
> SQLserver 200 SP3 running on Win2000
> I check disk D: where all the databases reside and found that it is
heavily
> fragmented. The list of most fragmented files include some important
> database (MDF) files.
> I would like to defragment the disk but also concerned about possible
> problem with the databases in question since they are up and running
during
> the defragmentation process.
> What's a safe procedure to perform defragmentation on the the disk and how
> to reduce fragment in the furture?
> Any suggestion is greatly appreciated.
> Bill
>

Disk capacity and CPU/memory utilisation question?

Folks,
This is a dumb question I know but I'm a newbie and require your
help. Can anyone answer the following 2 questions concerning SQL
Server 2000:
1. How do you check for disk capacity on a SQL Server 2000 box?
2. How do you look at a log of CPU/memory utilisation and the
processes running at that time?
Any commetns/ideas/suggestions - much appreciated.
Thank you,
ColmYou check disk capacity the same as with any other box...you can use
perfmon, etc... To see how much logical space is available INSIDE the
physical files, sp_spaceused will do it...
In order to save and look at processes that were running at a particular
time, you would run perfmon and save the output. Then you may go back and do
reports on specific time periods... There are also 3rd party monitoring
tools available to you..
Microsoft also has MOM. I forget what the acronym stands for, but it is an
operational tool for monitoring windows based machines. AND there is an
add-in for SQL Server as well... This is a sku'd product which you must
purchase.
If you wish to get into this in a BIG, do it yourself kind of way, take a
look at WMI. A microsoft scripting language which allows you to do many,
many different kinds of things..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Al Murphy" <almurph@.altavista.com> wrote in message
news:a23daf3f.0411240429.3f373918@.posting.google.com...
> Folks,
> This is a dumb question I know but I'm a newbie and require your
> help. Can anyone answer the following 2 questions concerning SQL
> Server 2000:
> 1. How do you check for disk capacity on a SQL Server 2000 box?
> 2. How do you look at a log of CPU/memory utilisation and the
> processes running at that time?
>
> Any commetns/ideas/suggestions - much appreciated.
> Thank you,
> Colm|||In addition to Waynes wonderful response you might find these useful:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Al Murphy" <almurph@.altavista.com> wrote in message
news:a23daf3f.0411240429.3f373918@.posting.google.com...
> Folks,
> This is a dumb question I know but I'm a newbie and require your
> help. Can anyone answer the following 2 questions concerning SQL
> Server 2000:
> 1. How do you check for disk capacity on a SQL Server 2000 box?
> 2. How do you look at a log of CPU/memory utilisation and the
> processes running at that time?
>
> Any commetns/ideas/suggestions - much appreciated.
> Thank you,
> Colm|||Al,
In addition to Andrew's and Waynes replies, you may wish to have a look
at some SQL Server stored procedures.
exec master..xp_fixeddrives
exec master..sp_monitor
I've found these useful in the past.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Al Murphy wrote:
> Folks,
> This is a dumb question I know but I'm a newbie and require your
> help. Can anyone answer the following 2 questions concerning SQL
> Server 2000:
> 1. How do you check for disk capacity on a SQL Server 2000 box?
> 2. How do you look at a log of CPU/memory utilisation and the
> processes running at that time?
>
> Any commetns/ideas/suggestions - much appreciated.
> Thank you,
> Colm

Disk capacity and CPU/memory utilisation question?

Folks,
This is a dumb question I know but I'm a newbie and require your
help. Can anyone answer the following 2 questions concerning SQL
Server 2000:
1. How do you check for disk capacity on a SQL Server 2000 box?
2. How do you look at a log of CPU/memory utilisation and the
processes running at that time?
Any commetns/ideas/suggestions - much appreciated.
Thank you,
Colm
You check disk capacity the same as with any other box...you can use
perfmon, etc... To see how much logical space is available INSIDE the
physical files, sp_spaceused will do it...
In order to save and look at processes that were running at a particular
time, you would run perfmon and save the output. Then you may go back and do
reports on specific time periods... There are also 3rd party monitoring
tools available to you..
Microsoft also has MOM. I forget what the acronym stands for, but it is an
operational tool for monitoring windows based machines. AND there is an
add-in for SQL Server as well... This is a sku'd product which you must
purchase.
If you wish to get into this in a BIG, do it yourself kind of way, take a
look at WMI. A microsoft scripting language which allows you to do many,
many different kinds of things..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Al Murphy" <almurph@.altavista.com> wrote in message
news:a23daf3f.0411240429.3f373918@.posting.google.c om...
> Folks,
> This is a dumb question I know but I'm a newbie and require your
> help. Can anyone answer the following 2 questions concerning SQL
> Server 2000:
> 1. How do you check for disk capacity on a SQL Server 2000 box?
> 2. How do you look at a log of CPU/memory utilisation and the
> processes running at that time?
>
> Any commetns/ideas/suggestions - much appreciated.
> Thank you,
> Colm
|||In addition to Waynes wonderful response you might find these useful:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Al Murphy" <almurph@.altavista.com> wrote in message
news:a23daf3f.0411240429.3f373918@.posting.google.c om...
> Folks,
> This is a dumb question I know but I'm a newbie and require your
> help. Can anyone answer the following 2 questions concerning SQL
> Server 2000:
> 1. How do you check for disk capacity on a SQL Server 2000 box?
> 2. How do you look at a log of CPU/memory utilisation and the
> processes running at that time?
>
> Any commetns/ideas/suggestions - much appreciated.
> Thank you,
> Colm
|||Al,
In addition to Andrew's and Waynes replies, you may wish to have a look
at some SQL Server stored procedures.
exec master..xp_fixeddrives
exec master..sp_monitor
I've found these useful in the past.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Al Murphy wrote:
> Folks,
> This is a dumb question I know but I'm a newbie and require your
> help. Can anyone answer the following 2 questions concerning SQL
> Server 2000:
> 1. How do you check for disk capacity on a SQL Server 2000 box?
> 2. How do you look at a log of CPU/memory utilisation and the
> processes running at that time?
>
> Any commetns/ideas/suggestions - much appreciated.
> Thank you,
> Colm

Disconnected Edit in SSIS 2005

Is there a match for SQL Server 2000 DTS Package "Disconnected Edit" in SQL
Server 2005 Integration Services ? If so, What ?
Thanks.In SSIS you can accomplish editing packages without a connection to the SQL
Server Several different ways.
If you are using file system deployment you can just make sure that your
development folder for the dtsx files is something different then the folder
that the agent is executing packages from.
If you are using SQL Server deployment then every package that you are
working is essentially disconnected. You have to deploy it to the sql
server using the deploy option inside of the business intelligence studio or
a package installer.
In both cases, you do not need any type of connection to the SQL server
until you deploy. You will lose some niceties that way since you cant
connect to get column or table info.
Make sense?
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
Tech Blog - www.technologyis.com
*/
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:32FB5B16-0012-4DD8-98CF-E52CF07BB77C@.microsoft.com...
> Is there a match for SQL Server 2000 DTS Package "Disconnected Edit" in
> SQL
> Server 2005 Integration Services ? If so, What ?
> Thanks.|||No it doesn't. Here is why,
I imported a DTS package from a SQL Server 2000 server to 2005 Integration
services on another SQL Server 2005 computer. Regardles of where I open it,
it takes at least 20 second to scroll the horizantal bar 1 inch. I am
assuming that it is trying to check the resources/connections even though
they don't exists on the new server. This is why I thought that disconnected
edit may help to speed editing the DTS package or the SSIS package since it
has been converted..........
Thanks.
"Warren Brunk" wrote:

> In SSIS you can accomplish editing packages without a connection to the SQ
L
> Server Several different ways.
> If you are using file system deployment you can just make sure that your
> development folder for the dtsx files is something different then the fold
er
> that the agent is executing packages from.
> If you are using SQL Server deployment then every package that you are
> working is essentially disconnected. You have to deploy it to the sql
> server using the deploy option inside of the business intelligence studio
or
> a package installer.
> In both cases, you do not need any type of connection to the SQL server
> until you deploy. You will lose some niceties that way since you cant
> connect to get column or table info.
> Make sense?
>
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> Tech Blog - www.technologyis.com
> */
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:32FB5B16-0012-4DD8-98CF-E52CF07BB77C@.microsoft.com...
>
>

Disconnected Edit in SSIS 2005

Is there a match for SQL Server 2000 DTS Package "Disconnected Edit" in SQL
Server 2005 Integration Services ? If so, What ?
Thanks.
In SSIS you can accomplish editing packages without a connection to the SQL
Server Several different ways.
If you are using file system deployment you can just make sure that your
development folder for the dtsx files is something different then the folder
that the agent is executing packages from.
If you are using SQL Server deployment then every package that you are
working is essentially disconnected. You have to deploy it to the sql
server using the deploy option inside of the business intelligence studio or
a package installer.
In both cases, you do not need any type of connection to the SQL server
until you deploy. You will lose some niceties that way since you cant
connect to get column or table info.
Make sense?
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
Tech Blog - www.technologyis.com
*/
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:32FB5B16-0012-4DD8-98CF-E52CF07BB77C@.microsoft.com...
> Is there a match for SQL Server 2000 DTS Package "Disconnected Edit" in
> SQL
> Server 2005 Integration Services ? If so, What ?
> Thanks.
|||No it doesn't. Here is why,
I imported a DTS package from a SQL Server 2000 server to 2005 Integration
services on another SQL Server 2005 computer. Regardles of where I open it,
it takes at least 20 second to scroll the horizantal bar 1 inch. I am
assuming that it is trying to check the resources/connections even though
they don't exists on the new server. This is why I thought that disconnected
edit may help to speed editing the DTS package or the SSIS package since it
has been converted..........
Thanks.
"Warren Brunk" wrote:

> In SSIS you can accomplish editing packages without a connection to the SQL
> Server Several different ways.
> If you are using file system deployment you can just make sure that your
> development folder for the dtsx files is something different then the folder
> that the agent is executing packages from.
> If you are using SQL Server deployment then every package that you are
> working is essentially disconnected. You have to deploy it to the sql
> server using the deploy option inside of the business intelligence studio or
> a package installer.
> In both cases, you do not need any type of connection to the SQL server
> until you deploy. You will lose some niceties that way since you cant
> connect to get column or table info.
> Make sense?
>
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> Tech Blog - www.technologyis.com
> */
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:32FB5B16-0012-4DD8-98CF-E52CF07BB77C@.microsoft.com...
>
>
sql

Disaster/recovery techniques

I have recently installed SQL Server 2000 on a newly acquired Windows
2003 server. The network admins also got Brightstor Agent for SQL
Server to interface with Brightstor ARCserve Backup for Windows
running on our backup server. I think I have a pretty good handle on
how to setup the SQL Server backups using a combination of
full/differential/transaction log backups to handle any recovery
required for the SQL Server databases.
What are the alternatives for backing up the Server itself for use in
recovery? Two approaches come to mind.
The first is not worry about backing up the server, in which case for
a recovery, I would have to reinstall all the software and service
packs to rebuild the environment. This would certainly require that I
have fully documented what applications (Windows 2003 server, SQL
Server, Outlook, Brightstor Agent, etc) and the install options that
were chosen. The rebuild process for this could take some time, and
there may be some issues that I haven't considered.
The second is to fully backup the server including the registry. If
everything were installed to the "C:\" drive, I could maybe get by
with just backing up the "C:\" drive and the registry. I do have a
weekend window where I could down the server to perform this task on a
weekly basis. Then for recovery I could just install an operating
system, restore from the backup of the "C:\" drive and registry and
proceed to my SQL Server recovery. Are there any problems with this
approach?
Finally, are there other techniques that I should consider? Any
websites or newsgroup threads on this topic would be greatly
appreciated.
Thanks Much, Lane LesperanceI prefere the first approach. it's the databases you should back up, not the
server.
Part of a good recovery plan is to document everything, so you can put the
server back to its original configuration before applying your backup files.
you can use the 2nd one, but to me it will take longer, and you need to
really test it on a test box to make sure you can recover your data that
way.
Steve Lin
MCDBA
"Lane Lesperance" <llesperance@.cooperpower.com> wrote in message
news:66feb5c.0404051118.e0472bf@.posting.google.com...
> I have recently installed SQL Server 2000 on a newly acquired Windows
> 2003 server. The network admins also got Brightstor Agent for SQL
> Server to interface with Brightstor ARCserve Backup for Windows
> running on our backup server. I think I have a pretty good handle on
> how to setup the SQL Server backups using a combination of
> full/differential/transaction log backups to handle any recovery
> required for the SQL Server databases.
> What are the alternatives for backing up the Server itself for use in
> recovery? Two approaches come to mind.
> The first is not worry about backing up the server, in which case for
> a recovery, I would have to reinstall all the software and service
> packs to rebuild the environment. This would certainly require that I
> have fully documented what applications (Windows 2003 server, SQL
> Server, Outlook, Brightstor Agent, etc) and the install options that
> were chosen. The rebuild process for this could take some time, and
> there may be some issues that I haven't considered.
> The second is to fully backup the server including the registry. If
> everything were installed to the "C:\" drive, I could maybe get by
> with just backing up the "C:\" drive and the registry. I do have a
> weekend window where I could down the server to perform this task on a
> weekly basis. Then for recovery I could just install an operating
> system, restore from the backup of the "C:\" drive and registry and
> proceed to my SQL Server recovery. Are there any problems with this
> approach?
> Finally, are there other techniques that I should consider? Any
> websites or newsgroup threads on this topic would be greatly
> appreciated.
> Thanks Much, Lane Lesperance

Disaster Recovery rehearsal

Hi,
I am performing a disaster recovery reharsal for SQL
Server with various test cases on a Test Server. I want to
corrupt the Master Database( without deleting the .ldf)
Manually and then try to recover. Which is the best way to
do this? . also Any resources on Disaster Recovery
reharsals on SQL Servers are appreciated.
Regards
ChipHi
check out following articles about disaster recovery
http://www.sqljunkies.com/HowTo/F30...6CEAD46C79.scuk
http://support.microsoft.com/defaul...%5BLN%5D;307775
Regards,
MD
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:f22a01c3f111$bfee2820$a501280a@.phx.gbl...
> Hi,
> I am performing a disaster recovery reharsal for SQL
> Server with various test cases on a Test Server. I want to
> corrupt the Master Database( without deleting the .ldf)
> Manually and then try to recover. Which is the best way to
> do this? . also Any resources on Disaster Recovery
> reharsals on SQL Servers are appreciated.
> Regards
> Chip|||Do you want it to go suspect or just cause a corruption in the master
database?
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:f22a01c3f111$bfee2820$a501280a@.phx.gbl...
> Hi,
> I am performing a disaster recovery reharsal for SQL
> Server with various test cases on a Test Server. I want to
> corrupt the Master Database( without deleting the .ldf)
> Manually and then try to recover. Which is the best way to
> do this? . also Any resources on Disaster Recovery
> reharsals on SQL Servers are appreciated.
> Regards
> Chip|||hi Tibor,
Thanks for the response. what should i do to go it to
suspect(without deleting .ldf) and how to cause a
corruption. Please suggest both ways so that I can have
two test cases in hand. :-)
Sincere Regards
Chip.
>--Original Message--
>Do you want it to go suspect or just cause a corruption
in the master
>database?
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in
message
>news:f22a01c3f111$bfee2820$a501280a@.phx.gbl...
to
to
>
>.
>|||Chip,
To make it corrupt, you can create a table and for the table hack some value
in the sysindexes table (IAM column, for instance). You need to figure out
how to do modifications against the systems tables as I don't want to put
that information in a public place.
As for suspect, read the source code for sp_resetstatus and reverse what it
does. It should work on the master database...
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:e98501c3f154$c22ad640$a301280a@.phx.gbl...
> hi Tibor,
> Thanks for the response. what should i do to go it to
> suspect(without deleting .ldf) and how to cause a
> corruption. Please suggest both ways so that I can have
> two test cases in hand. :-)
> Sincere Regards
> Chip.
> in the master
> oi=djq&as_ugroup=microsoft.public.sqlserver
> message
> to
> to|||Poor me.
Not a expert dba to hack and crack :-(. If it is possible
to mail the procedure, I will be greatful. I need to
perform this at any cost and send the details to my boss
with step-by-step procedure :-(. Any resources pointing to
achieve this are highly appreciated.
Chip
chipsin007@.NOSPAM.yahoo.com
>--Original Message--
>Chip,
>To make it corrupt, you can create a table and for the
table hack some value
>in the sysindexes table (IAM column, for instance). You
need to figure out
>how to do modifications against the systems tables as I
don't want to put
>that information in a public place.
>As for suspect, read the source code for sp_resetstatus
and reverse what it
>does. It should work on the master database...
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in
message
>news:e98501c3f154$c22ad640$a301280a@.phx.gbl...
want
the .ldf)
way
>
>.
>|||You won't, to my knowledge, find any such resources. And I don't have any
ready-made scripts as I always do them as I go along. On top for the first
one is sp_configure and the "allow updates" option. As for the second one,
you need to read the source code of sp_resetstatus and make appropriate
changes (the suspect status is in the status column if
master..sysdatabases). If this makes no sense to you, you really need to
bring someone in to do these things... :-)
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:e9d401c3f15c$1898d3e0$a301280a@.phx.gbl...
> Poor me.
> Not a expert dba to hack and crack :-(. If it is possible
> to mail the procedure, I will be greatful. I need to
> perform this at any cost and send the details to my boss
> with step-by-step procedure :-(. Any resources pointing to
> achieve this are highly appreciated.
> Chip
> chipsin007@.NOSPAM.yahoo.com
> table hack some value
> need to figure out
> don't want to put
> and reverse what it
> oi=djq&as_ugroup=microsoft.public.sqlserver
> message
> want
> the .ldf)
> way|||You can always change where SQL looks for the master.mdf file. That will
put you in a DR situation when you restart it. SQL will attempt to come
online and will not be able to start. It is also fixable by changing the
regkey back. I'd practice on a lab server until you are comfortable. USE
AT YOUR OWN RISK.
Christian Smith
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:e98501c3f154$c22ad640$a301280a@.phx.gbl...
> hi Tibor,
> Thanks for the response. what should i do to go it to
> suspect(without deleting .ldf) and how to cause a
> corruption. Please suggest both ways so that I can have
> two test cases in hand. :-)
> Sincere Regards
> Chip.
> in the master
> oi=djq&as_ugroup=microsoft.public.sqlserver
> message
> to
> to

Disable trigger

Hi,

can I disable a trigger in Sqlserver 2000? When i run a store procedure who works with one table i want that the trigger doesnt work it. After that the trigger would be enabled again.

I know i can delete it and create it again but something like "ALTER TRIGGER DISABLED" would be ok.

Thanks.I think you want ALTER TABLE myTable DISABLE TRIGGER ALL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_9036.asp) or something very much like it.

-PatP|||fix the procedure?|||fix the procedure?Oh now there's a silly answer!

Fix the problem instead of trying to find a work-around ?!?! Sheeshka, what will they think of next?

-PatP|||What was I thinking...|||Oooh ... oooh - I know the answer to this one :rolleyes: . Create a separate userid and have the trigger check to see if that userid ran the proc that touched the table that fired the trigger. If that user is the active user bypass the trigger, otherwise fire the trigger!
At least, that's what some ** person ** did to a critical set of procs and triggers before I started here, and it would take an act of (insert the appropriate authority here) to do it properly :o .

What some folks won't think of!!|||I actually did exactly that in MS-SQL 6.5 to work around a problem in propriatary code (we didn't have the source to fix the underlying problem).

-PatP