Query to find oldest active user transaction and kill them

Use below query to find oldest active user transaction and kill them:


DECLARE @sql varchar(8000)
DECLARE @spid VARCHAR(4000)


DECLARE c_trans cursor FAST_FORWARD FOR 
select est.session_id from sys.dm_tran_active_transactions tas 
inner join sys.dm_tran_database_transactions tds on (tas.transaction_id = tds.transaction_id )
inner join sys.dm_tran_session_transactions est on (est.transaction_id=tas.transaction_id)
where est.is_user_transaction =1 and tas.transaction_state =2
and tds.database_transaction_begin_time is not null


OPEN c_trans 
FETCH NEXT FROM c_trans INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'kill '+ @spid
EXEC(@SQL)
FETCH NEXT FROM c_trans INTO @spid
END 
CLOSE c_trans
DEALLOCATE c_trans

Backing up and Restoring the database with splitting files to multiple disks

Backing up the database with spiltting files to multiple disks 


BACKUP DATABASE DemoData TO
DISK = 'C:\BACKUPDIR\DemoData_1.BAK',
DISK = 'C:\BACKUPDIR\DemoData_2.BAK',
DISK = 'C:\BACKUPDIR\DemoData_3.BAK'


Feaching logical filenames from one of split backup


RESTORE FILELISTONLY 
FROM DISK = 'C:\BACKUPDIR\DemoData_1.BAK'


Restoring database from multiple spilt files 


RESTORE DATABASE DemoData_COPY FROM
DISK = 'C:\BACKUPDIR\DemoData_1.BAK',
DISK = 'C:\BACKUPDIR\DemoData_2.BAK',
DISK = 'C:\BACKUPDIR\DemoData_3.BAK'
WITH REPLACE ,
MOVE 'DemoData' 
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DemoData_COPY_DATA.MDF',
MOVE 'DemoData_LOG' 
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DemoData_COPY_LOG.LDF'

Script to remove old Database backup

Use below script to remove old database backup:


DECLARE @sql varchar(8000)
DECLARE @BAK_PATH VARCHAR(4000)
declare c_bak cursor FAST_FORWARD FOR 
select bmf.physical_device_name
from msdb..backupset bs 
inner join msdb..backupmediafamily bmf
on (bs.media_set_id=bmf.media_set_id)
where DATEDIFF(DAY,bs.backup_start_date,GETDATE())>30
AND DATEDIFF(DAY,bs.backup_start_date,GETDATE())<=60
OPEN c_bak 
FETCH NEXT FROM c_bak INTO @BAK_PATH 
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'EXEC XP_CMDSHELL '+ ''' DEL '+'"'+@BAK_PATH +'"'+''''+', no_output '
PRINT (@SQL)
EXEC(@SQL)
FETCH NEXT FROM c_bak INTO @BAK_PATH 
END 
CLOSE C_BAK
DEALLOCATE C_BAK

How to create Custom Reports for SQL Server Management Studio

In this article I will describe in details (including some examples) how you can build your own SQL Server Management Studio reports that are loaded from Object Explorer. The reports can easily be used on SQL Server 2005, 2008 and 2008R2 environments.

1. Requirements

Firs of all let me start with the basic requirements of the environment you need setup so you can produce such reports. Do not get too scared, it is not that big list.  It is actually just one simple application called BIDS (ha ha, yes, this is Microsoft’s Business Intelligence Development Studio, included in SQL Server installation media). But not just any BIDS, but 2005. SSMS have never been upgraded to use the 2008 report viewer component, so the only way your report to be loaded is if it is written on BIDS 2005. For further reference you can check this Connect article.

2. Behind the scenes

One of the most important things you should know about building custom reports is that at runtime (when the report is rendered/executed in SSMS) there are several predefined parameters passed from Object Explorer (OE) to the report and which you can use freely. The parameters and their short explanation you can find below:

Parameter Name	CLR data type	Comments
ObjectTypeName	String	The type of object. For example, “Database”, “Login”, “Functions”.
ObjectName	String	The name of the object. For example, “Foo”, “AdventureWorksDW”, “GetUserIDFromName”, etc.
ErrorText	String	Used in the Default report to show error information.
Filtered	Boolean	This was used to indicate whether the dataset being passed from OE is filtered or not. We will respect the filters the user has in place in OE and this parameter allows us to indicate on the list reports whether the list is filtered.
ServerName	String	Name of the server and instance currently connected. In the form of “server\instance” for a named instance and “server” for a default instance. Prompt Name: ServerName Allow Null:    checked Allow Blank:  checked Avail Values: none Defaults:       none
FontName	String	Name of the font to be used to display the report. Defaults:       Non-Queried – “Tahoma”
DatabaseName	String	Name of the database containing the current object. If the object is not database scoped, this value will be an empty string.

As you can see those parameters are the key to create dynamic reports on lower level than SQL Server instance (like database or table).

3. How to start

First you can start with creating a BIDS Report Server project. Having that done, create a Shared datasource to the master database of your SQL instance. Name it “master.rds“.

Second – create a report template, that you can use for further starting point for each report. In this template you have to only setup the parameters. Go to Report -> Parameters and insert all parameters using the info at the above table. Make sure you use the same names and caption. After you are done with that task, your parameters screen should look something like this:

You can also use the below XML, paste it into a text file and rename it to .rdl, add it to your BIDS solution and use it as a template.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <InteractiveHeight>11in</InteractiveHeight>
  <ReportParameters>
    <ReportParameter Name="ObjectTypeName">
      <DataType>String</DataType>
      <Nullable>true</Nullable>
      <AllowBlank>true</AllowBlank>
      <Prompt>ObjectTypeName</Prompt>
    </ReportParameter>
    <ReportParameter Name="ObjectName">
      <DataType>String</DataType>
      <Nullable>true</Nullable>
      <AllowBlank>true</AllowBlank>
      <Prompt>ObjectName</Prompt>
    </ReportParameter>
    <ReportParameter Name="ErrorText">
      <DataType>String</DataType>
      <Nullable>true</Nullable>
      <AllowBlank>true</AllowBlank>
      <Prompt>ErrorText</Prompt>
    </ReportParameter>
    <ReportParameter Name="Filtered">
      <DataType>Boolean</DataType>
      <Nullable>true</Nullable>
      <AllowBlank>true</AllowBlank>
      <Prompt>Filtered</Prompt>
    </ReportParameter>
    <ReportParameter Name="ServerName">
      <DataType>String</DataType>
      <Nullable>true</Nullable>
      <AllowBlank>true</AllowBlank>
      <Prompt>ServerName</Prompt>
    </ReportParameter>
    <ReportParameter Name="FontName">
      <DataType>String</DataType>
      <Nullable>true</Nullable>
      <DefaultValue>
        <Values>
          <Value>"Tahoma"</Value>
        </Values>
      </DefaultValue>
      <AllowBlank>true</AllowBlank>
      <Prompt>FontName</Prompt>
    </ReportParameter>
    <ReportParameter Name="DatabaseName">
      <DataType>String</DataType>
      <Nullable>true</Nullable>
      <AllowBlank>true</AllowBlank>
      <Prompt>DatabaseName</Prompt>
    </ReportParameter>
  </ReportParameters>
  <rd:DrawGrid>true</rd:DrawGrid>
  <InteractiveWidth>8.5in</InteractiveWidth>
  <rd:GridSpacing>0.25cm</rd:GridSpacing>
  <rd:SnapToGrid>true</rd:SnapToGrid>
  <RightMargin>2.5cm</RightMargin>
  <LeftMargin>2.5cm</LeftMargin>
  <BottomMargin>2.5cm</BottomMargin>
  <rd:ReportID>ddeb9983-9825-4554-9cc9-545bb2680e52</rd:ReportID>
  <PageWidth>21cm</PageWidth>
  <Width>4.75cm</Width>
  <Body>
    <ColumnSpacing>1cm</ColumnSpacing>
    <ReportItems>
      <Textbox Name="textbox1">
        <rd:DefaultName>textbox1</rd:DefaultName>
        <Style>
          <Color>SteelBlue</Color>
          <FontFamily>Tahoma</FontFamily>
          <FontSize>20pt</FontSize>
          <FontWeight>700</FontWeight>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
          <PaddingBottom>2pt</PaddingBottom>
        </Style>
        <CanGrow>true</CanGrow>
        <Height>0.91429cm</Height>
        <Value>00 Template</Value>
      </Textbox>
    </ReportItems>
    <Height>3.15476cm</Height>
  </Body>
  <Language>en-US</Language>
  <TopMargin>2.5cm</TopMargin>
  <PageHeight>29.7cm</PageHeight>
</Report>

4. Standard SSMS report sources

Finally – from this link you can download all reports that are currently available in Management Studio. Some of those reports can run outside SSMS but some of them cannot due to different reasons. However, inside those reports there are quite valuable queries that you can use in your day-to-day tasks or add them to your script bank.

Ref: http://sqlbg.wordpress.com/2011/03/06/how-to-create-custom-reports-for-sql-server-management-studio/

Get Service Account Name

To get service account name use below query:


DECLARE @MSSqlService VARCHAR(50)
EXECUTE master.dbo.xp_instance_regread
 N'HKEY_LOCAL_MACHINE'
,N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER'
,N'ObjectName'
,@MSSqlService OUTPUT
,N'no_output'
SELECT @MSSqlService AS SQL_Service_Account