Display date: single

显示标签为“single”的博文。显示所有博文

2012年3月20日星期二

disconnecting yourself after setting db to single user

Is there a way to drop your connection to a database, specifically after you
have set the database to single user mode?
What I'm trying to do is run a test restore of a SQL Database. What I'm
doing is using the command :
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
to put the db into single user mode, however the problem is that in the
process of doing this it establishes a connection to the db, therefore
taking the one and only available connection to the db. Currently the only
way I can disconnect the connection is to re-start SQL, so I can then run
the restore, and the backup server can successfully connect to the db
(otherwise it complains that it cannot obtain exclusive access to the
database as it is in use).
Needless to say I'd prefer to not need to kill all SQL connections to the
entire server just so I can test the backups are being successful.
KeithWhy not grab the SINGLE_USER for yourself and do the restore.
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
USE <dbname>
go
RESTORE ...
"Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
> Is there a way to drop your connection to a database, specifically after
> you
> have set the database to single user mode?
> What I'm trying to do is run a test restore of a SQL Database. What I'm
> doing is using the command :
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
> to put the db into single user mode, however the problem is that in the
> process of doing this it establishes a connection to the db, therefore
> taking the one and only available connection to the db. Currently the only
> way I can disconnect the connection is to re-start SQL, so I can then run
> the restore, and the backup server can successfully connect to the db
> (otherwise it complains that it cannot obtain exclusive access to the
> database as it is in use).
> Needless to say I'd prefer to not need to kill all SQL connections to the
> entire server just so I can test the backups are being successful.
> Keith
>|||Because I'm using Backup Exec to do the backup and restore, which is a
different connection to the one that I set the db to single user with. The
error below even happens if I set the db to single user from the backup
server itself, rather than on the SQL Server box.
Keith
"Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...
> Why not grab the SINGLE_USER for yourself and do the restore.
> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> go
> USE <dbname>
> go
> RESTORE ...
>
> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
> > Is there a way to drop your connection to a database, specifically after
> > you
> > have set the database to single user mode?
> >
> > What I'm trying to do is run a test restore of a SQL Database. What I'm
> > doing is using the command :
> >
> > ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
> >
> > to put the db into single user mode, however the problem is that in the
> > process of doing this it establishes a connection to the db, therefore
> > taking the one and only available connection to the db. Currently the
only
> > way I can disconnect the connection is to re-start SQL, so I can then
run
> > the restore, and the backup server can successfully connect to the db
> > (otherwise it complains that it cannot obtain exclusive access to the
> > database as it is in use).
> >
> > Needless to say I'd prefer to not need to kill all SQL connections to
the
> > entire server just so I can test the backups are being successful.
> >
> > Keith
> >
> >
>|||I don't have much experience with Backup Exec but 1 thing springs to mind.
Restore the database with a different name, once restored drop the original
database then rename the restored one.
"Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
news:OgCTK6etEHA.908@.TK2MSFTNGP10.phx.gbl...
> Because I'm using Backup Exec to do the backup and restore, which is a
> different connection to the one that I set the db to single user with. The
> error below even happens if I set the db to single user from the backup
> server itself, rather than on the SQL Server box.
> Keith
> "Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
> news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...
>> Why not grab the SINGLE_USER for yourself and do the restore.
>> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
>> go
>> USE <dbname>
>> go
>> RESTORE ...
>>
>> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
>> news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
>> > Is there a way to drop your connection to a database, specifically
>> > after
>> > you
>> > have set the database to single user mode?
>> >
>> > What I'm trying to do is run a test restore of a SQL Database. What I'm
>> > doing is using the command :
>> >
>> > ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
>> >
>> > to put the db into single user mode, however the problem is that in the
>> > process of doing this it establishes a connection to the db, therefore
>> > taking the one and only available connection to the db. Currently the
> only
>> > way I can disconnect the connection is to re-start SQL, so I can then
> run
>> > the restore, and the backup server can successfully connect to the db
>> > (otherwise it complains that it cannot obtain exclusive access to the
>> > database as it is in use).
>> >
>> > Needless to say I'd prefer to not need to kill all SQL connections to
> the
>> > entire server just so I can test the backups are being successful.
>> >
>> > Keith
>> >
>> >
>>
>|||That's basically what I'm doing currently.
With Backup Exec, if you want to restore the db to a database other than the
original, (and since it's just a test, I don't want to mess around with the
live db) you need to have an existing db in place which you can point the
restore job to. Backup Exec then needs to be able to gain exclusive access
to the target db before it can restore the information to it.
Keith
"Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
news:uOdJJKftEHA.2688@.TK2MSFTNGP14.phx.gbl...
> I don't have much experience with Backup Exec but 1 thing springs to mind.
> Restore the database with a different name, once restored drop the
original
> database then rename the restored one.
> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> news:OgCTK6etEHA.908@.TK2MSFTNGP10.phx.gbl...
> > Because I'm using Backup Exec to do the backup and restore, which is a
> > different connection to the one that I set the db to single user with.
The
> > error below even happens if I set the db to single user from the backup
> > server itself, rather than on the SQL Server box.
> >
> > Keith
> >
> > "Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
> > news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...
> >> Why not grab the SINGLE_USER for yourself and do the restore.
> >>
> >> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> >> go
> >> USE <dbname>
> >> go
> >> RESTORE ...
> >>
> >>
> >> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> >> news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
> >> > Is there a way to drop your connection to a database, specifically
> >> > after
> >> > you
> >> > have set the database to single user mode?
> >> >
> >> > What I'm trying to do is run a test restore of a SQL Database. What
I'm
> >> > doing is using the command :
> >> >
> >> > ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK
IMMEDIATE.
> >> >
> >> > to put the db into single user mode, however the problem is that in
the
> >> > process of doing this it establishes a connection to the db,
therefore
> >> > taking the one and only available connection to the db. Currently the
> > only
> >> > way I can disconnect the connection is to re-start SQL, so I can then
> > run
> >> > the restore, and the backup server can successfully connect to the db
> >> > (otherwise it complains that it cannot obtain exclusive access to the
> >> > database as it is in use).
> >> >
> >> > Needless to say I'd prefer to not need to kill all SQL connections to
> > the
> >> > entire server just so I can test the backups are being successful.
> >> >
> >> > Keith
> >> >
> >> >
> >>
> >>
> >
> >
>

disconnecting yourself after setting db to single user

2012年3月19日星期一

disconnect a user whoz locking the DB with diff SPIDs

I have issued a simple insert statement on table and the statement could not insert a single row into the table.When i check the locks on the tables i found that almost 5000(five thousand) locks on the table for a single user with different SPID(s).
The user is and sql server user(not a windows user) and used to connect to the application.I wanted to disconnect the user from the DB so that all the locks will be freed.But i dunno how to disconnect a user from the DB. I know that i can issue KILL command to Terminate a user process based on the system process ID,but here the same user has nearly 1000 SPIDs.I thought that it would be very big job to kill each and every process and restared the sql server.but i guess its not the correct process to do.
how can i dosconnect a specific user from the DB.

Thanks.Well if you think it's a big job to kill 1,000 spids, how big of a job is it to create them?

I would want to know what process is soing that.

Also, watch the ROLLBACK...it'll be twice as long as letting the work COMMIT

But you've got bigger problems...

ALTER DATABASE <database> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Will blow everyone out, rollback all uncommitted work, and give you control

2012年2月24日星期五

Disable triggers for a single update

Hello,
I have a trigger on a table (after update) which keeps track of modified
records / columns. I need to have a function wich can make updates to that
table without firing the trigger (i.e. I don't want to track changes made by
that function).
So, I need is to make the following statements "atomic" (otherwise I could
miss other updates I want to keep tracking)
- alter table disable trigger
- update table
- alter table enable trigger.
The first option I considered was using a lock on the whole table, which i'd
like to avoid due to the usual performance issues. Now I'm thinking about
using a transaction. I know that transactions ensure actions are atomic with
respecto to recovery, I'd like to know if they can solve this kind of issues
too.
Thank you,
Luca> The first option I considered was using a lock on the whole table, which
> i'd like to avoid due to the usual performance issues. Now I'm thinking
> about using a transaction. I know that transactions ensure actions are
> atomic with respecto to recovery, I'd like to know if they can solve this
> kind of issues too.
Wrapping the ALTER/UPDATE/ALTER in a transaction will ensure the operation
is atomic. However, the ALTER will acquire a Sch-M lock on the table so no
other users can access the table for the duration of the transaction.
If this is the sort of thing you do often, consider including code in your
trigger to conditionally bypass the update. This will improve concurrency
and provide an atomic transaction as well. The example below uses a temp
table existence check:
CREATE TRIGGER TR_MyTable
ON MyTable FOR UPDATE
AS
IF OBJECT_ID('tempdb..#BypassTrigger') IS NULL
BEGIN
UPDATE MyTable
SET MyAuditDate = CURRENT_TIMESTAMP
WHERE EXISTS
(
SELECT *
FROM inserted
WHERE MyTable.MyPK = inserted.MyPK
)
END
GO
You can then skip the trigger code for the current session by creating the
dummy temp table beforehand:
CREATE TABLE #BypassTrigger(Col1 int)
UPDATE MyTable
SET MyData = 'test'
WHERE MyPk = 1
DROP TABLE #BypassTrigger
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Luca" <luca@.none.net> wrote in message
news:Z3WXd.639929$b5.29135411@.news3.tin.it...
> Hello,
> I have a trigger on a table (after update) which keeps track of modified
> records / columns. I need to have a function wich can make updates to that
> table without firing the trigger (i.e. I don't want to track changes made
> by that function).
> So, I need is to make the following statements "atomic" (otherwise I could
> miss other updates I want to keep tracking)
> - alter table disable trigger
> - update table
> - alter table enable trigger.
> The first option I considered was using a lock on the whole table, which
> i'd like to avoid due to the usual performance issues. Now I'm thinking
> about using a transaction. I know that transactions ensure actions are
> atomic with respecto to recovery, I'd like to know if they can solve this
> kind of issues too.
> Thank you,
> Luca
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> ha scritto nel messaggio
news:eqTc$AXJFHA.2764@.tk2msftngp13.phx.gbl...
> --
> Hope this helps.
>
This was really helpful, thanks a lot.
Luca

2012年2月14日星期二

Disable export options for a SINGLE report

On our report server, I would like to make the export option only present one
rendering type, PDF. I do not want to make the change in the server
configuration file as my understanding is that this would affect the export
options for ALL reports on the report server.
Can you enter any code someplace in the designer that will allow me to
restrict the export options to the user when they render the report on the
server?
ThanksAs you have already found out, the config file determines export options for
all reports on a report server. Restrictions for individual reports are not
supported.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pauldes" <Pauldes@.discussions.microsoft.com> wrote in message
news:8051BF1A-F36D-437F-B25A-3F746B1F578C@.microsoft.com...
> On our report server, I would like to make the export option only present
> one
> rendering type, PDF. I do not want to make the change in the server
> configuration file as my understanding is that this would affect the
> export
> options for ALL reports on the report server.
> Can you enter any code someplace in the designer that will allow me to
> restrict the export options to the user when they render the report on the
> server?
> Thanks

Disable Client-Side Printing on Single Report

I know I can disable client-side printing for the whole server, but I would
like to be able to disable it for a single report that I have. If I can not
disable it, can I at least hide the print button on the report. Thanks.you can specify another style with .toolbarprint {display: none;}...it is
passed in the url with (i think) rc:stlye=<myStyle>...this should point you
in the right direction
"Matt" wrote:
> I know I can disable client-side printing for the whole server, but I would
> like to be able to disable it for a single report that I have. If I can not
> disable it, can I at least hide the print button on the report. Thanks.

订阅：博文 (Atom)

2012年3月20日星期二

disconnecting yourself after setting db to single user

disconnecting yourself after setting db to single user

disconnecting yourself after setting db to single user

2012年3月19日星期一

disconnect a user whoz locking the DB with diff SPIDs

2012年2月24日星期五

Disable triggers for a single update

2012年2月14日星期二

Disable export options for a SINGLE report

Disable Client-Side Printing on Single Report

Display date

Blog Archive

About Me