Char and Varchar: via

Showing posts with label via. Show all posts

Thursday, March 22, 2012

check if database is in use via a script

Hi I have a large script that creates a database and imports data from
another database. If first drops the destination database if it exists. I
was wondering if there is a way to put conditional code in a script to check
if the database is in use and if so skip the script and display a warning
message? Since the script takes a few minutes to run this could save some
time! Thanks.
--
Paul G
Software engineer.Do a select from master..sysprocesses where dbid = <dbid>
If at least 1 record is returned then the db id in use.
--
MG
"Paul" wrote:
> Hi I have a large script that creates a database and imports data from
> another database. If first drops the destination database if it exists. I
> was wondering if there is a way to put conditional code in a script to check
> if the database is in use and if so skip the script and display a warning
> message? Since the script takes a few minutes to run this could save some
> time! Thanks.
> --
> Paul G
> Software engineer.|||You can use things like sysprocesses (etc, depending on your version) to check whether there are
connection in the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:9552307B-1C24-4948-947E-B0CF052C78FA@.microsoft.com...
> Hi I have a large script that creates a database and imports data from
> another database. If first drops the destination database if it exists. I
> was wondering if there is a way to put conditional code in a script to check
> if the database is in use and if so skip the script and display a warning
> message? Since the script takes a few minutes to run this could save some
> time! Thanks.
> --
> Paul G
> Software engineer.|||HI thanks for the response. Is the dbid the database process id or just the
database name?
--
Paul G
Software engineer.
"Hurme" wrote:
> Do a select from master..sysprocesses where dbid = <dbid>
> If at least 1 record is returned then the db id in use.
> --
> MG
>
> "Paul" wrote:
> > Hi I have a large script that creates a database and imports data from
> > another database. If first drops the destination database if it exists. I
> > was wondering if there is a way to put conditional code in a script to check
> > if the database is in use and if so skip the script and display a warning
> > message? Since the script takes a few minutes to run this could save some
> > time! Thanks.
> > --
> > Paul G
> > Software engineer.|||Thanks for the information. I tried
select * from sysprocesses --
and it returned the name of the database along with the DB_ID, database ID,
although it did not return any information reguarding if in use. Using
MSSQL 2000 enterprise.
Paul G
Software engineer.
"Tibor Karaszi" wrote:
> You can use things like sysprocesses (etc, depending on your version) to check whether there are
> connection in the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:9552307B-1C24-4948-947E-B0CF052C78FA@.microsoft.com...
> > Hi I have a large script that creates a database and imports data from
> > another database. If first drops the destination database if it exists. I
> > was wondering if there is a way to put conditional code in a script to check
> > if the database is in use and if so skip the script and display a warning
> > message? Since the script takes a few minutes to run this could save some
> > time! Thanks.
> > --
> > Paul G
> > Software engineer.
>|||On Tue, 10 Jul 2007 23:04:08 +0200, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>You can use things like sysprocesses (etc, depending on your version) to check whether there are
>connection in the database.
sysprocesses will certainly tell you if any connection is defaulting
to a particular database, but I don't think it reveals that the
connection made to database Foo is referencing Bar..object. Perhaps
checking locks too would take care of that?
Roy Harvey
Beacon Falls, CT|||The DBID is the database ID of the database. If you execute sp_helpdb you
can find out what it is. If you don't see any records in the sysprocesses
table with that dbid then the db is not in use.
--
MG
"Paul" wrote:
> Thanks for the information. I tried
> select * from sysprocesses --
> and it returned the name of the database along with the DB_ID, database ID,
> although it did not return any information reguarding if in use. Using
> MSSQL 2000 enterprise.
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
> > You can use things like sysprocesses (etc, depending on your version) to check whether there are
> > connection in the database.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:9552307B-1C24-4948-947E-B0CF052C78FA@.microsoft.com...
> > > Hi I have a large script that creates a database and imports data from
> > > another database. If first drops the destination database if it exists. I
> > > was wondering if there is a way to put conditional code in a script to check
> > > if the database is in use and if so skip the script and display a warning
> > > message? Since the script takes a few minutes to run this could save some
> > > time! Thanks.
> > > --
> > > Paul G
> > > Software engineer.
> >
> >|||ok thanks for the information, so for each record that is returned that
means that the corresponding database is in use?
--
Paul G
Software engineer.
"Hurme" wrote:
> The DBID is the database ID of the database. If you execute sp_helpdb you
> can find out what it is. If you don't see any records in the sysprocesses
> table with that dbid then the db is not in use.
> --
> MG
>
> "Paul" wrote:
> > Thanks for the information. I tried
> > select * from sysprocesses --
> > and it returned the name of the database along with the DB_ID, database ID,
> > although it did not return any information reguarding if in use. Using
> > MSSQL 2000 enterprise.
> >
> > Paul G
> > Software engineer.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> > > You can use things like sysprocesses (etc, depending on your version) to check whether there are
> > > connection in the database.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://sqlblog.com/blogs/tibor_karaszi
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:9552307B-1C24-4948-947E-B0CF052C78FA@.microsoft.com...
> > > > Hi I have a large script that creates a database and imports data from
> > > > another database. If first drops the destination database if it exists. I
> > > > was wondering if there is a way to put conditional code in a script to check
> > > > if the database is in use and if so skip the script and display a warning
> > > > message? Since the script takes a few minutes to run this could save some
> > > > time! Thanks.
> > > > --
> > > > Paul G
> > > > Software engineer.
> > >
> > >|||YEs, each row represent a connection to the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:35FC149B-8748-46A4-9E06-59A621E59857@.microsoft.com...
> ok thanks for the information, so for each record that is returned that
> means that the corresponding database is in use?
> --
> Paul G
> Software engineer.
>
> "Hurme" wrote:
>> The DBID is the database ID of the database. If you execute sp_helpdb you
>> can find out what it is. If you don't see any records in the sysprocesses
>> table with that dbid then the db is not in use.
>> --
>> MG
>>
>> "Paul" wrote:
>> > Thanks for the information. I tried
>> > select * from sysprocesses --
>> > and it returned the name of the database along with the DB_ID, database ID,
>> > although it did not return any information reguarding if in use. Using
>> > MSSQL 2000 enterprise.
>> >
>> > Paul G
>> > Software engineer.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> > > You can use things like sysprocesses (etc, depending on your version) to check whether there
>> > > are
>> > > connection in the database.
>> > >
>> > > --
>> > > Tibor Karaszi, SQL Server MVP
>> > > http://www.karaszi.com/sqlserver/default.asp
>> > > http://sqlblog.com/blogs/tibor_karaszi
>> > >
>> > >
>> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> > > news:9552307B-1C24-4948-947E-B0CF052C78FA@.microsoft.com...
>> > > > Hi I have a large script that creates a database and imports data from
>> > > > another database. If first drops the destination database if it exists. I
>> > > > was wondering if there is a way to put conditional code in a script to check
>> > > > if the database is in use and if so skip the script and display a warning
>> > > > message? Since the script takes a few minutes to run this could save some
>> > > > time! Thanks.
>> > > > --
>> > > > Paul G
>> > > > Software engineer.
>> > >
>> > >|||dbid is the id for the database. Use DB_NAME() to translate to database name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:85F2D15F-6203-4C43-91E1-635D6BD448D5@.microsoft.com...
> HI thanks for the response. Is the dbid the database process id or just the
> database name?
> --
> Paul G
> Software engineer.
>
> "Hurme" wrote:
>> Do a select from master..sysprocesses where dbid = <dbid>
>> If at least 1 record is returned then the db id in use.
>> --
>> MG
>>
>> "Paul" wrote:
>> > Hi I have a large script that creates a database and imports data from
>> > another database. If first drops the destination database if it exists. I
>> > was wondering if there is a way to put conditional code in a script to check
>> > if the database is in use and if so skip the script and display a warning
>> > message? Since the script takes a few minutes to run this could save some
>> > time! Thanks.
>> > --
>> > Paul G
>> > Software engineer.

Monday, March 19, 2012

check diskspace UNC via T-SQL

Hello,
I have an EM Job script that backups up my databases using SQLsafe to a
target UNC.
Sometimes, the target server that will store the backups is off line or
it's disk is full.
I am wondering how I can check to see if the machine is up via the job
and secondly, check the disk space.
If either check fails, I would then check another machine machine.
Any ideas appreciated,
Thanks
Rob
SQL 2000 Server and Enterprise, Windows 2003
SQL 2005 Server and Etnerprise, Windows 2003
Target storage is a Windows 2003 and I connect via UNCYou can use a couple of extended procs to get what you want. I have a
stored proc that jumps through some hoops to give me that information.

First I call: EXEC master.dbo.xp_availablemedia

That returns a list of devices on the database server. I loop over the
results from that and do:

EXEC master..xp_cmdshell 'DIR /-C <drive>'

and I look for the line that has "bytes free" and parse that for the
number.

It's not terribly elegant or fancy, but it does the job. The SQL for
the stored proc is below if you're curious. I also reference a table
that I created in msdb to help me track growth over time. You can just
eliminate that part.

Hope it helps,
Teresa Masino

CREATE procedure sp_checkdbspace
AS
SET nocount ON

CREATE TABLE #DriveList (
namevarchar(20)null,
lowfreeintnull,
highfree intnull,
mediatype intnull
)

CREATE TABLE #DirList (
Drive varchar(20) null,
DirResults varchar(255) null
)

INSERT INTO #DriveList EXEC master.dbo.xp_availablemedia

DECLARE @.Drive varchar(20),
@.CMD varchar(255)

DECLARE mycursor CURSOR
FOR
SELECTname
FROM#DriveList
ORDER BY name

OPEN mycursor

FETCH mycursor INTO @.Drive

IF CURSOR_STATUS('variable', '@.mycursor') = 0
BEGIN
PRINT 'No such device'
CLOSE mycursor
DEALLOCATE mycursor
return
END

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.CMD = 'insert into #DirList (DirResults) EXEC
master..xp_cmdshell ''DIR /-C ' + @.Drive + ''''
EXEC (@.CMD)
UPDATE #DirList SET Drive = @.Drive WHERE Drive IS NULL
FETCH mycursor INTO @.Drive
END

CLOSE mycursor
DEALLOCATE mycursor

SELECTDBName, LogicalName, PhysicalName, MinSize = min(SizeMB),
MaxSize = max(SizeMB), MinDate = min(StatusDate), MaxDate =
Max(StatusDate), MaxSizeMB = max(MaxSizeMB)
INTO#SpaceList
FROMmsdb..DBSpaceHistory
GROUP BY DBName, LogicalName, PhysicalName
ORDER BY DBName, LogicalName, PhysicalName

SELECT*, BytesFree = convert(numeric(18,0),
rtrim(ltrim(substring(replace(DirResults, ' bytes free', ''), 26,
50))))
INTO#SpaceOnDisk
FROM#DirList
WHEREDirResults LIKE '%bytes free%'

SELECTDBName = convert(varchar(20), DBName),
PhysicalName = convert(varchar(60), PhysicalName),
MaxSize,
Growth = MaxSize - MinSize,
DiskMBFree = convert(numeric(10,3), BytesFree / 1048576),
GrowthPeriod = datediff(day, MinDate, MaxDate),
DaysLeft = convert(numeric(10,3), (BytesFree / 1048576) / CASE WHEN
(MaxSize - MinSize) <= 0 THEN 1 ELSE ((MaxSize - MinSize) /
datediff(day, MinDate, MaxDate)) END)
FROM#SpaceList, #SpaceOnDisk
WHEREUPPER(substring(PhysicalName, 1, 3)) = Drive

Thursday, March 22, 2012

check if database is in use via a script

check if database is in use via a script

check if database is in use via a script

Monday, March 19, 2012

check diskspace UNC via T-SQL

Char and Varchar

Blog Archive

About Me