Tuesday, March 27, 2012
Clear Connections command
use enterprise manager to Detach Database. A dialog box appears that lists
the Database status. It says there are 3 Connections using this database.
Next to the number of connections is a Clear button that will drop the active
connections so that you can continue the process of detaching the database.
Does anyone know the command equivalent to pressing the Clear button?
I don't want to use the graphical interface and I assume there must be some
way to issue a command that would do the same thing as pressing the Clear
button in the GUI.
I don't actually want to detach the database - I am just trying to clear all
connections other than my own. Once I finish clearing connections, I want to
issue a command to temporarily put the database into single user mode.
Alter Database FOO set SINGLE_USER with Rollback Immediate
Everything you want in one command. See the ALTER DATABASE command in BOL
for details.
To set it back to 'normal'
Alter Database FOO set MULTI_USER
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cathedr@.wa.state.gov" <cathedrwastategov@.discussions.microsoft.com> wrote
in message news:7EA72915-DF9B-437B-9E08-AE54D210AB9C@.microsoft.com...
> Scenario: You have a database that is in use and you want to detach it.
You
> use enterprise manager to Detach Database. A dialog box appears that
lists
> the Database status. It says there are 3 Connections using this database.
> Next to the number of connections is a Clear button that will drop the
active
> connections so that you can continue the process of detaching the
database.
> Does anyone know the command equivalent to pressing the Clear button?
> I don't want to use the graphical interface and I assume there must be
some
> way to issue a command that would do the same thing as pressing the Clear
> button in the GUI.
> I don't actually want to detach the database - I am just trying to clear
all
> connections other than my own. Once I finish clearing connections, I want
to
> issue a command to temporarily put the database into single user mode.
sqlsql
Clear Connections command
use enterprise manager to Detach Database. A dialog box appears that lists
the Database status. It says there are 3 Connections using this database.
Next to the number of connections is a Clear button that will drop the active
connections so that you can continue the process of detaching the database.
Does anyone know the command equivalent to pressing the Clear button?
I don't want to use the graphical interface and I assume there must be some
way to issue a command that would do the same thing as pressing the Clear
button in the GUI.
I don't actually want to detach the database - I am just trying to clear all
connections other than my own. Once I finish clearing connections, I want to
issue a command to temporarily put the database into single user mode.Alter Database FOO set SINGLE_USER with Rollback Immediate
Everything you want in one command. See the ALTER DATABASE command in BOL
for details.
To set it back to 'normal'
Alter Database FOO set MULTI_USER
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cathedr@.wa.state.gov" <cathedrwastategov@.discussions.microsoft.com> wrote
in message news:7EA72915-DF9B-437B-9E08-AE54D210AB9C@.microsoft.com...
> Scenario: You have a database that is in use and you want to detach it.
You
> use enterprise manager to Detach Database. A dialog box appears that
lists
> the Database status. It says there are 3 Connections using this database.
> Next to the number of connections is a Clear button that will drop the
active
> connections so that you can continue the process of detaching the
database.
> Does anyone know the command equivalent to pressing the Clear button?
> I don't want to use the graphical interface and I assume there must be
some
> way to issue a command that would do the same thing as pressing the Clear
> button in the GUI.
> I don't actually want to detach the database - I am just trying to clear
all
> connections other than my own. Once I finish clearing connections, I want
to
> issue a command to temporarily put the database into single user mode.
Monday, March 19, 2012
Choosing several items from a Pick list
Does anyone know how to pick more than one item from a pick list (drop down
box.)
E.g if I had the months of the year in the pick list how could I select Jan
and Feb'
Any ideas greatfully recieved.
Regards
Munish
--
Technical Data Analyst
Hammersmith & W London CollegeAs fas as I know you cannot do this with the normal report parameters list,
you need to write code to handle the multi select and call the reporting
services web service. I think it is a lot of effort, and I havent seen any
example code.
I think RS 2005 has this multi select functionality included.
"Munish RaasmaanDaas" wrote:
> Morning All,
> Does anyone know how to pick more than one item from a pick list (drop down
> box.)
> E.g if I had the months of the year in the pick list how could I select Jan
> and Feb'
> Any ideas greatfully recieved.
> Regards
> Munish
> --
> Technical Data Analyst
> Hammersmith & W London College
Saturday, February 25, 2012
checkpoint on tempdb
was applied) the following situation started occuring:
- There is a simple scripted trace running on the server that captures
STMTCompleted and BatchCompleted events;
- A scheduled tasks fires every minute and captures the results using
::fn_trace_gettable function;
- tempdb is in Simple recovery mode (doh, that's the only mode that this
database can be in), but without explicitly issuing CHECKPOINT or BACKUP LOG
TEMPDB WITH TRUNCATE_ONLY space used in the log device continues growing.
While investigating the issue I set 3502 trace flag on, and also discovered
that even after explicitly issuing CHECKPOINT the entry about the checkpoint
on tempdb is not made.
As a workaround I added BACKUP LOG TEMPDB WITH TRUNCATE_ONLY to the job that
runs every minute. But what's interesting is that exactly the same scenario
with exactly the same trace works fine in 818 build, and space used in TEMPD
B
log device gets periodically cleared without having to explicitly issue any
checkpoint-related commands.
Is it a bug in SP4? And why 3502 trace flag does not post the entry for
TEMPDB (dbid 2)?
Any pointers would be appreciated.Thanks for reporting this problem.
TF3502 does not post entry in the errorlog for tempdb. This has been the
case since SQL 7.0.
There is another way to find out if there has been a checkpoint:
use tempdb
select * from ::fn_dblog(null, null)
This dumps the log since the last checkpoint.
Can you try that and let us know if there is indeed no checkpoint log
record? Or you could send me the data+log file of your tempdb in a zip file
and I will take a look. But that file may be huge.
This could be a bug. In the mean time I will ask the devs around here to see
if
anybody knows.
Finally, please note that both TF3502 and fn_dblog are undocumented
commands. There might be issues with using them on production systems. You
could open a case with Microsoft Product Support if you are not comfortable
with diagnosing the problem on your own.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Djabarov" <Robert Djabarov@.discussions.microsoft.com> wrote in
message news:B1CA9B5B-34B2-46D2-927B-01815B92E912@.microsoft.com...
> It appears that after applying SP4 (the box has 4GB of RAM, so no post-SP4
> was applied) the following situation started occuring:
> - There is a simple scripted trace running on the server that captures
> STMTCompleted and BatchCompleted events;
> - A scheduled tasks fires every minute and captures the results using
> ::fn_trace_gettable function;
> - tempdb is in Simple recovery mode (doh, that's the only mode that this
> database can be in), but without explicitly issuing CHECKPOINT or BACKUP
> LOG
> TEMPDB WITH TRUNCATE_ONLY space used in the log device continues growing.
> While investigating the issue I set 3502 trace flag on, and also
> discovered
> that even after explicitly issuing CHECKPOINT the entry about the
> checkpoint
> on tempdb is not made.
> As a workaround I added BACKUP LOG TEMPDB WITH TRUNCATE_ONLY to the job
> that
> runs every minute. But what's interesting is that exactly the same
> scenario
> with exactly the same trace works fine in 818 build, and space used in
> TEMPDB
> log device gets periodically cleared without having to explicitly issue
> any
> checkpoint-related commands.
> Is it a bug in SP4? And why 3502 trace flag does not post the entry for
> TEMPDB (dbid 2)?
> Any pointers would be appreciated.
checkpoint on tempdb
was applied) the following situation started occuring:
- There is a simple scripted trace running on the server that captures
STMTCompleted and BatchCompleted events;
- A scheduled tasks fires every minute and captures the results using
::fn_trace_gettable function;
- tempdb is in Simple recovery mode (doh, that's the only mode that this
database can be in), but without explicitly issuing CHECKPOINT or BACKUP LOG
TEMPDB WITH TRUNCATE_ONLY space used in the log device continues growing.
While investigating the issue I set 3502 trace flag on, and also discovered
that even after explicitly issuing CHECKPOINT the entry about the checkpoint
on tempdb is not made.
As a workaround I added BACKUP LOG TEMPDB WITH TRUNCATE_ONLY to the job that
runs every minute. But what's interesting is that exactly the same scenario
with exactly the same trace works fine in 818 build, and space used in TEMPDB
log device gets periodically cleared without having to explicitly issue any
checkpoint-related commands.
Is it a bug in SP4? And why 3502 trace flag does not post the entry for
TEMPDB (dbid 2)?
Any pointers would be appreciated.Thanks for reporting this problem.
TF3502 does not post entry in the errorlog for tempdb. This has been the
case since SQL 7.0.
There is another way to find out if there has been a checkpoint:
use tempdb
select * from ::fn_dblog(null, null)
This dumps the log since the last checkpoint.
Can you try that and let us know if there is indeed no checkpoint log
record? Or you could send me the data+log file of your tempdb in a zip file
and I will take a look. But that file may be huge.
This could be a bug. In the mean time I will ask the devs around here to see
if
anybody knows.
Finally, please note that both TF3502 and fn_dblog are undocumented
commands. There might be issues with using them on production systems. You
could open a case with Microsoft Product Support if you are not comfortable
with diagnosing the problem on your own.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Djabarov" <Robert Djabarov@.discussions.microsoft.com> wrote in
message news:B1CA9B5B-34B2-46D2-927B-01815B92E912@.microsoft.com...
> It appears that after applying SP4 (the box has 4GB of RAM, so no post-SP4
> was applied) the following situation started occuring:
> - There is a simple scripted trace running on the server that captures
> STMTCompleted and BatchCompleted events;
> - A scheduled tasks fires every minute and captures the results using
> ::fn_trace_gettable function;
> - tempdb is in Simple recovery mode (doh, that's the only mode that this
> database can be in), but without explicitly issuing CHECKPOINT or BACKUP
> LOG
> TEMPDB WITH TRUNCATE_ONLY space used in the log device continues growing.
> While investigating the issue I set 3502 trace flag on, and also
> discovered
> that even after explicitly issuing CHECKPOINT the entry about the
> checkpoint
> on tempdb is not made.
> As a workaround I added BACKUP LOG TEMPDB WITH TRUNCATE_ONLY to the job
> that
> runs every minute. But what's interesting is that exactly the same
> scenario
> with exactly the same trace works fine in 818 build, and space used in
> TEMPDB
> log device gets periodically cleared without having to explicitly issue
> any
> checkpoint-related commands.
> Is it a bug in SP4? And why 3502 trace flag does not post the entry for
> TEMPDB (dbid 2)?
> Any pointers would be appreciated.
checkpoint on tempdb
was applied) the following situation started occuring:
- There is a simple scripted trace running on the server that captures
STMTCompleted and BatchCompleted events;
- A scheduled tasks fires every minute and captures the results using
::fn_trace_gettable function;
- tempdb is in Simple recovery mode (doh, that's the only mode that this
database can be in), but without explicitly issuing CHECKPOINT or BACKUP LOG
TEMPDB WITH TRUNCATE_ONLY space used in the log device continues growing.
While investigating the issue I set 3502 trace flag on, and also discovered
that even after explicitly issuing CHECKPOINT the entry about the checkpoint
on tempdb is not made.
As a workaround I added BACKUP LOG TEMPDB WITH TRUNCATE_ONLY to the job that
runs every minute. But what's interesting is that exactly the same scenario
with exactly the same trace works fine in 818 build, and space used in TEMPDB
log device gets periodically cleared without having to explicitly issue any
checkpoint-related commands.
Is it a bug in SP4? And why 3502 trace flag does not post the entry for
TEMPDB (dbid 2)?
Any pointers would be appreciated.
Thanks for reporting this problem.
TF3502 does not post entry in the errorlog for tempdb. This has been the
case since SQL 7.0.
There is another way to find out if there has been a checkpoint:
use tempdb
select * from ::fn_dblog(null, null)
This dumps the log since the last checkpoint.
Can you try that and let us know if there is indeed no checkpoint log
record? Or you could send me the data+log file of your tempdb in a zip file
and I will take a look. But that file may be huge.
This could be a bug. In the mean time I will ask the devs around here to see
if
anybody knows.
Finally, please note that both TF3502 and fn_dblog are undocumented
commands. There might be issues with using them on production systems. You
could open a case with Microsoft Product Support if you are not comfortable
with diagnosing the problem on your own.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Djabarov" <Robert Djabarov@.discussions.microsoft.com> wrote in
message news:B1CA9B5B-34B2-46D2-927B-01815B92E912@.microsoft.com...
> It appears that after applying SP4 (the box has 4GB of RAM, so no post-SP4
> was applied) the following situation started occuring:
> - There is a simple scripted trace running on the server that captures
> STMTCompleted and BatchCompleted events;
> - A scheduled tasks fires every minute and captures the results using
> ::fn_trace_gettable function;
> - tempdb is in Simple recovery mode (doh, that's the only mode that this
> database can be in), but without explicitly issuing CHECKPOINT or BACKUP
> LOG
> TEMPDB WITH TRUNCATE_ONLY space used in the log device continues growing.
> While investigating the issue I set 3502 trace flag on, and also
> discovered
> that even after explicitly issuing CHECKPOINT the entry about the
> checkpoint
> on tempdb is not made.
> As a workaround I added BACKUP LOG TEMPDB WITH TRUNCATE_ONLY to the job
> that
> runs every minute. But what's interesting is that exactly the same
> scenario
> with exactly the same trace works fine in 818 build, and space used in
> TEMPDB
> log device gets periodically cleared without having to explicitly issue
> any
> checkpoint-related commands.
> Is it a bug in SP4? And why 3502 trace flag does not post the entry for
> TEMPDB (dbid 2)?
> Any pointers would be appreciated.
Thursday, February 16, 2012
Checking for free disk space and getting mail when it falls below a certain limit
I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.
I would like to know how I can change it , as a percentage calculation.
For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.
The script I have is :
declare @.MB_Free int
create table #FreeSpace(
Drive char(1),
MB_Free int)
insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @.MB_Free < 4096
exec master.dbo.xp_sendmail
@.recipients ='dvaddi@.domain.edu',
@.subject ='SERVER X - Fresh Space Issue on D Drive',
@.message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace
Thanks
Hi Vaddi -
Why not use the Alerts feature in Performance Monitor? The Logical Disk Performance Object has a Counter for % Free Space and you can select which drive letter you'd like to monitor. Once the limit is reached, you can have it email you using a WSH script.
HTH...
checking for free disk space and getting mail , when falls below a certain limit
I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.
I would like to know how I can change it , as a percentage calculation.
For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.
The script I have is :
declare @.MB_Free int
create table #FreeSpace(
Drive char(1),
MB_Free int)
insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @.MB_Free < 4096
exec master.dbo.xp_sendmail
@.recipients ='dvaddi@.domain.edu',
@.subject ='SERVER X - Fresh Space Issue on D Drive',
@.message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace
ThanksHere's what I use:
set nocount on
declare @.MB_Threshold int
set @.MB_Threshold = 102400
declare @.From varchar(500)
declare @.Subject varchar(500)
declare @.Message varchar(500)
create table #FreeSpace(Drive char(1), MB_Free int)
insert into #FreeSpace exec master..xp_fixeddrives
select @.Message = isnull(@.Message + ', ', 'The following drives have dropped below ' + cast(@.MB_Threshold as varchar(10)) + ' MB free space: ') + Drive
from #FreeSpace
where MB_Free < @.MB_Threshold
set @.From = @.@.ServerName
set @.Subject = 'Drive space warning!'
if len(@.Message) > 0
begin
exec master.dbo.xp_smtp_sendmail
@.SERVER = 'exchange.foobar.corp',
@.FROM = @.From,
@.TO = N'blindman@.dbforums.com',
@.SUBJECT = @.Subject,
@.MESSAGE = @.Message
end
drop table #FreeSpace
go
Sunday, February 12, 2012
Checkboxes in Reporting Services?
Is it possible to have a checkbox as a parameter.. So a user can check a box to include a column or uncheck to hide a column ?
Thanks
Neil
Hello Neil,
I don't believe there is a way to get a checkbox in the parameters section. Would True/False radio buttons work for you? If so, you can use the Boolean type for your parameter.
Hope this helps.
Jarret
|||Hi Jarret,Yes radio buttons could work... Although I have just made a parameter boolean but it still rendered as a drop down..
Is there anything specifically I need to do to make the parameter a radio button ?
Thanks.
Neil
|||
What version of Reporting Services are you using?
You should be able to set your parameter to Boolean without any available values and it should show radio buttons. Make sure you don't have Multi-value selected either.
Jarret
|||There we go!Thanks man!!! I was setting an available value.
Thanks so much!!
Neil.
|||
No problem, glad I could help.
Jarret