Database Mail configuration

1. Open the SSMS and right click on the Database Mail in Management portion. Click on the Configure Database Mail.

2. Click Next on the wizerd.

3. Select Set up Database Mail by performing the following tasks and click on the Next.

4. Type Profile name and select Add.. button.

 5. Click on New Account.

6. Type Account name, Email address, Display name, Reply e-mail and Server name. Then click ok.

7. Select created profile name Default. Select Next.

8. Test default database mail by executing following store procedure.

EXEC msdb.dbo.sp_send_dbmail 

@recipients = 'parvez.nawaz@gamil.com',

    @subject = 'Test mail from UAT',

    --@profile_name = 'AMS Development',

    @query = 'select getdate()',

    @attach_query_result_as_file = 0

List all the tables in a database

SELECT * FROM sysobjects WHERE xtype='U'

Here is a list of other object types you can search for as well:

• C: Check constraint
• D: Default constraint
• F: Foreign Key constraint
• L: Log
• P: Stored procedure
• PK: Primary Key constraint
• RF: Replication Filter stored procedure
• S: System table
• TR: Trigger
• U: User table
• UQ: Unique constraint
• V: View
• X: Extended stored procedure

In SQL Server 2012, you can use

SELECT * FROM information_schema.tables where table_type='BASE TABLE'

You can also use

select * from sys.tables

Create user that can execute only single stored procedure

For windows account

CREATE LOGIN [BNS\EPMDataReaders] FROM WINDOWS WITH DEFAULT_DATABASE=[AssetMgmtServicesReport], DEFAULT_LANGUAGE=[us_english]

USE AssetMgmtServices;
CREATE USER [BNS\EPMDataReaders] FOR LOGIN [BNS\EPMDataReaders]
GRANT SELECT ON  [dbo].AssetInventory TO [BNS\EPMDataReaders]
GRANT SELECT ON  [dbo].Region TO [BNS\EPMDataReaders]
GRANT EXECUTE ON dbo.fnLocationString TO [BNS\EPMDataReaders]
GRANT EXECUTE ON dbo.fnFormatFloor TO [BNS\EPMDataReaders]


USE AssetMgmtServicesReport;
CREATE USER [BNS\EPMDataReaders] FOR LOGIN [BNS\EPMDataReaders]
GRANT SELECT ON dbo.fnGreenIT_GetAssets TO [BNS\EPMDataReaders]
GRANT SELECT ON dbo.GreenITCategoryMapper TO [BNS\EPMDataReaders]
GRANT SELECT ON dbo.GreenITCategory TO [BNS\EPMDataReaders]
GRANT EXEC ON dbo.GreenIT_GetAssets_YYMM TO [BNS\EPMDataReaders]


USE master
CREATE USER [BNS\EPMDataReaders] FOR LOGIN [BNS\EPMDataReaders]
EXEC sp_addrolemember N'security_role', N'BNS\EPMDataReaders'

For local sql account

CREATE LOGIN [GreenItUser] WITH PASSWORD=N'Scotia123', DEFAULT_DATABASE=[AssetMgmtServicesReport], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE AssetMgmtServices;
CREATE USER [GreenItUser] FOR LOGIN [GreenItUser]
GRANT SELECT ON  [dbo].AssetInventory TO [GreenItUser]
GRANT SELECT ON  [dbo].Region TO [GreenItUser]
GRANT EXECUTE ON dbo.fnLocationString TO [GreenItUser]
GRANT EXECUTE ON dbo.fnFormatFloor TO [GreenItUser]


USE AssetMgmtServicesReport;
CREATE USER [GreenItUser] FOR LOGIN [GreenItUser]
GRANT SELECT ON dbo.fnGreenIT_GetAssets TO [GreenItUser]
GRANT SELECT ON dbo.GreenITCategoryMapper TO [GreenItUser]
GRANT SELECT ON dbo.GreenITCategory TO [GreenItUser]
GRANT EXEC ON dbo.GreenIT_GetAssets_YYMM TO [GreenItUser]


USE master
CREATE USER [GreenItUser] FOR LOGIN [GreenItUser]
EXEC sp_addrolemember N'security_role', N'GreenItUser'

Adding new assembly and functions

1. Check if there is any previous assembly exists. If there is any, then remove it.

SELECT
        SCHEMA_NAME(O.schema_id) AS [Schema], O.name,
        A.name AS assembly_name, AM.assembly_class,
        AM.assembly_method,
        A.permission_set_desc,
        O.[type_desc]
FROM
        sys.assembly_modules AM
        INNER JOIN sys.assemblies A ON A.assembly_id = AM.assembly_id
        INNER JOIN sys.objects O ON O.object_id = AM.object_id
ORDER BY
        A.name, AM.assembly_class

2. Copy dll file to server (eg: F:\Parvez\Msdn.SqlRegex.dll')

3. Create assembly in database.

                    create assembly "Msdn.SqlRegex" from 'F:\Parvez\Msdn.SqlRegex.dll'

4. Create sql object using that assembly.
   
USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[RegexMatch]    Script Date: 10/28/2014 16:34:10 ******/
CREATE FUNCTION [dbo].[RegexMatch](@input [nvarchar](max), @pattern [nvarchar](4000))
RETURNS [bit] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Msdn.SqlRegex].[UserDefinedFunctions].[RegexMatch]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatch'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'RegexMatch.cs' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatch'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=11 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatch'


USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[RegexGroup]    Script Date: 10/28/2014 16:34:48 ******/
CREATE FUNCTION [dbo].[RegexGroup](@input [nvarchar](max), @pattern [nvarchar](4000), @name [nvarchar](4000))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Msdn.SqlRegex].[UserDefinedFunctions].[RegexGroup]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroup'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'RegexGroup.cs' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroup'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=9 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroup'

USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[RegexGroups]    Script Date: 10/28/2014 16:35:31 ******/
CREATE FUNCTION [dbo].[RegexGroups](@input [nvarchar](max), @pattern [nvarchar](4000))
RETURNS  TABLE (
[Index] [int] NULL,
[Group] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Text] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Msdn.SqlRegex].[UserDefinedFunctions].[RegexGroups]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroups'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'RegexGroups.cs' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroups'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=80 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroups'



USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[RegexMatches]    Script Date: 10/28/2014 16:36:02 ******/
CREATE FUNCTION [dbo].[RegexMatches](@input [nvarchar](max), @pattern [nvarchar](4000))
RETURNS  TABLE (
[Index] [int] NULL,
[Text] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Msdn.SqlRegex].[UserDefinedFunctions].[RegexMatches]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatches'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'RegexMatches.cs' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatches'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=61 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatches'

5. Check created objects

   SELECT

        SCHEMA_NAME(O.schema_id) AS [Schema], O.name,
        A.name AS assembly_name, AM.assembly_class,
        AM.assembly_method,
        A.permission_set_desc,
        O.[type_desc]
FROM
        sys.assembly_modules AM
        INNER JOIN sys.assemblies A ON A.assembly_id = AM.assembly_id
        INNER JOIN sys.objects O ON O.object_id = AM.object_id
ORDER BY
        A.name, AM.assembly_class

List all the objects associated with their assembly name

SELECT
        SCHEMA_NAME(O.schema_id) AS [Schema], O.name,
        A.name AS assembly_name, AM.assembly_class,
        AM.assembly_method,
        A.permission_set_desc,
        O.[type_desc]
FROM
        sys.assembly_modules AM
        INNER JOIN sys.assemblies A ON A.assembly_id = AM.assembly_id
        INNER JOIN sys.objects O ON O.object_id = AM.object_id
ORDER BY
        A.name, AM.assembly_class

Dropping all views, stored procedure and functions

Dropping all the views

1. Get all the views

select name from sys.objects where type='v' order by name

2. Drop all the views

DECLARE @viewName varchar(500)
DECLARE cur CURSOR
      FOR SELECT [name] FROM sys.objects WHERE type in ('V')
      OPEN cur

      FETCH NEXT FROM cur INTO @viewName
      WHILE @@fetch_status = 0
      BEGIN
            EXEC('DROP VIEW ' + @viewName)
Select @viewName
            FETCH NEXT FROM cur INTO @viewName
      END
      CLOSE cur
      DEALLOCATE cur

Dropping all the stored procedures

1. Display all the stored procedure.

Select name from sys.objects where type='p' order by name

2. Drop all the stored procedure

DECLARE @viewName varchar(500)
DECLARE cur CURSOR
      FOR SELECT [name] FROM sys.objects WHERE type in ('P')
      OPEN cur

      FETCH NEXT FROM cur INTO @viewName
      WHILE @@fetch_status = 0
      BEGIN
            EXEC('DROP PROCEDURE ' + @viewName)
Select @viewName
            FETCH NEXT FROM cur INTO @viewName
      END
      CLOSE cur
      DEALLOCATE cur

Dropping all the functions

1. Display all the existing functions

 select name from sys.objects where type='TF' or type='IF' or type='FN' order by name

2. Drop all the existing functions

DECLARE @viewName varchar(500)
DECLARE cur CURSOR
      FOR SELECT [name] FROM sys.objects WHERE type in ('TF','IF','FN')
      OPEN cur

      FETCH NEXT FROM cur INTO @viewName
      WHILE @@fetch_status = 0
      BEGIN
            EXEC('DROP Function ' + @viewName)
Select @viewName
            FETCH NEXT FROM cur INTO @viewName
      END
      CLOSE cur
      DEALLOCATE cur

TFS computer name change

Update TFS workspace when computer name changes

C:\Program Files (x86)\Microsoft Visual Studio 11.0>tf workspaces /updateCompute
rname:TJZR9XNH9H TJZR9XNH9H /s:http://tocgjtim1pv:8080/tfs/SR2012_Collection


Here
updateComputerrname:TJZR9XNH9H ----------------  TJZR9XNH9H is old computer name
TJZR9XNH9H --------------------------------------------  workspace name
http://tocgjtim1pv:8080/tfs/ ------------------------------ TFS url
SR2012_Collection ---------------------------------------  Collection name

Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

1. Create a database named 'aspnetdb'.
2. execute following exe to command prompt
c:\Windows\Microsoft.NET\Framework\v2.0.50727>aspnet_regsql.exe
3.

List of all SSRS reports name and their datasource

SELECT
    DS.Name AS DatasourceName,
    C.Name AS DependentItemName,
    C.Path AS DependentItemPath
FROM
    ReportServer.dbo.Catalog AS C
        INNER JOIN
    ReportServer.dbo.Users AS CU
        ON C.CreatedByID = CU.UserID
        INNER JOIN
    ReportServer.dbo.Users AS MU
        ON C.ModifiedByID = MU.UserID
        LEFT OUTER JOIN
    ReportServer.dbo.SecData AS SD
        ON C.PolicyID = SD.PolicyID AND SD.AuthType = 1
        INNER JOIN
    ReportServer.dbo.DataSource AS DS
        ON C.ItemID = DS.ItemID
WHERE
    DS.Name IS NOT NULL
ORDER BY
    DS.Name;

List all jobs with specified stored procedure in their steps

USE [msdb]
GO
SELECT j.job_id,
s.srvname,
j.name,
js.step_id,
js.command,
j.enabled
FROM dbo.sysjobs j
JOIN dbo.sysjobsteps js
ON js.job_id = j.job_id
JOIN master.dbo.sysservers s
ON s.srvid = j.originating_server_id
WHERE js.command LIKE N'%spAddContractorToBCL%'
GO

List all the jobs with ssis package

USE [msdb]
GO
SELECT
   Srv.srvname AS ServerName,
   Job.name AS JobName,
   JStep.step_id,
   JStep.step_name AS StepName,
   JStep.command,
   Job.enabled
FROM   dbo.sysjobs Job
JOIN   dbo.sysjobsteps JStep
   ON  JStep.job_id = Job.job_id
JOIN   MASTER.dbo.sysservers Srv
   ON  Srv.srvid = Job.originating_server_id
WHERE  JStep.subsystem='SSIS' AND enabled=1

ORDER BY Job.name,step_id

List all the jobs of sql server database

SELECT name AS [Job Name],CASE WHEN enabled=1 THEN 'YES' ELSE 'NO' END AS ENABLED,
description AS Description
FROM msdb.dbo.sysjobs
ORDER BY enabled,description

Migrate SSRS reporting database from 2005 to 2012

1. take database backup from source SSRS.
2. save encryption key from source SSRS.
3. migrate database backup and encryption key file to SSRS destination server.
4. stop SSRS services at destination server.
5. Restore database with overwrite option.
5. update encryption key in destination SSRS server from SSRS configuration window.

Disable all the SSRS Reporting services' Subscription Jobs

1. List all reporting service subscription jobs.

USE ReportServer
SELECT     Schedule.ScheduleID AS SQLAgent_Job_Name, Subscriptions.Description AS sub_desc, Subscriptions.DeliveryExtension AS sub_delExt,
                      [Catalog].Name AS ReportName, [Catalog].Path AS ReportPath,'exec msdb.dbo.sp_update_job @job_name = '''+CAST(Schedule.ScheduleID AS VARCHAR(max))+''',@enabled = 0' AS sqlcmd
FROM         ReportSchedule INNER JOIN
                      Schedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID INNER JOIN
                      Subscriptions ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID INNER JOIN
                      [Catalog] ON ReportSchedule.ReportID = [Catalog].ItemID AND Subscriptions.Report_OID = [Catalog].ItemID



2. Disable each jobs.

exec msdb.dbo.sp_update_job @job_name = 'A83AE658-E203-457B-83D3-5715EA64A47A',@enabled = 0

List all user permission to all databases

DECLARE @DB_USers TABLE
(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)

INSERT @DB_USers
EXEC sp_MSforeachdb

'
use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''

SELECT

dbname,username ,logintype ,create_date ,modify_date ,

STUFF(

(

SELECT ',' + CONVERT(VARCHAR(500),associatedrole)

FROM @DB_USers user2

WHERE

user1.DBName=user2.DBName AND user1.UserName=user2.UserName

FOR XML PATH('')

)

,1,1,'') AS Permissions_user

FROM @DB_USers user1

GROUP BY

dbname,username ,logintype ,create_date ,modify_date

ORDER BY DBName,username

Query to view job name and owner

SELECT

    sv.name AS [Name],

    sv.job_id AS [JobID],

    l.name AS UserName

    FROM

    msdb.dbo.sysjobs_view AS sv

    INNER JOIN [master].[sys].[syslogins] l ON sv.owner_sid = l.sid

       --WHERE l.name <> 'parvez'

    ORDER BY

    sv.[Name] ASC

Update a single row with transaction

BEGIN TRAN
       UPDATE TORGPSM3PNT.K2Log.dbo.[_ProcInstData]
        SET Value='NSYS\afedorkomilrad'
        WHERE ProcInstID=195763 AND id=6

        IF @@TRANCOUNT=1
         COMMIT TRAN
ELSE
         ROLLBack TRAN

SSIS Package batch export and import

1.

You can create batch export command by following sql.

select 'dtutil /SQL "' +[p].[name] + '" /ENCRYPT FILE;"F:\SSISPackages\'+[p].[name]+'.dtsx;1"'
from msdb.dbo.sysdtspackages90 [p]

Output command will be like this.

   dtutil /SQL "SSIS_Package_Name" /ENCRYPT FILE;"F:\SSISPackages\SSIS_Package_Name.dtsx;1"

Here 1 means, copy all the sensitive data with user key.
Here we are exporting SSIS packages from SQL server to F:\SSISPackages directory.

Run these batch commands in Souce SSIS server's command prompt.

Copy all the .dtsx file from source to destination server.

2.
Batch SSIS packages import commands can be generated by following sql

select 'dtutil /FILE "C:\Users\s5142805\Downloads\SSIS pkg\' +[p].[name] + '.dtsx" /COPY SQL;"/'+[p].[name]+'"'
from msdb.dbo.sysdtspackages90 [p]

Output will be like this.

dtutil /FILE "C:\Users\s5142805\Downloads\SSIS pkg\ABMCashOut - Import v2.dtsx" /COPY SQL;"/ABMCashOut"

Here we are importing SSIS packages from C:\Users\s5142805\Downloads\SSIS pkg\ to SQL server directory.

Run these batch commands in Destination SSIS server's command prompt.

List all the indexes in the database

SELECT
     TableName = t.name,
     IndexName = ind.name,    
     ColumnName = col.name,
     ind.type_desc
FROM
     sys.indexes ind
INNER JOIN
     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
     sys.tables t ON ind.object_id = t.object_id
ORDER BY
     t.name, ind.name, ind.index_id, ic.index_column_id

Find active connections and kill

Find active connections

SELECT DB_NAME(dbid) as DBName, dbid,loginame as LoginName,CAST(SPID AS VARCHAR(4)) AS spid
FROM     sys.sysprocesses WHERE dbid>10
ORDER BY dbid           

You will find spid. Then kill the connection by spid. Do not kill yourself.

KILL 61

MSSQL: Add a new column to table

ALTER TABLE <table name> 
ADD <new column name> <data type> NOT NULL
GO


ALTER TABLE <table name> 
ADD CONSTRAINT <constraint name> DEFAULT <default value> FOR <new column name>
GO
Add foreign Key

ALTER TABLE <Table_Name> ADD FOREIGN KEY(<ColumnName>) REFERENCES <Primary_Table_Name>(<Primary_column_Name>)

ALTER TABLE dbo.AssetInventory ADD FOREIGN KEY(RegionID) REFERENCES Region(RegionID)

Example:

ALTER TABLE dbo.AssetInventory ADD ASSET_PARENT int NULL

MSSQL: Does the column exits in a table

select * from sys.columns
         where
Name = N'[column name]'
and Object_ID = Object_ID(N'[Table Name]')

List all the function name for a database

connect to any database.
Use [Database name]


SELECT name AS function_name,type_desc
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%'

MSSQL: List all table size in a database

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
    sys.tables t
INNER JOIN    
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    t.Name

MSSQL: Creating Link Database Server

You can create any link database server with any provider string.

EXEC master.dbo.sp_addlinkedserver

    @server = N'LZAMSMEXPRD\AMSMEX',

    @srvproduct=N'MSSQL',

    @provider=N'SQLNCLI',

    @provstr=N'PROVIDER=SQLOLEDB;SERVER=lzamsmexqat\amsmex,1433'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LZAMSMEXPRD\AMSMEX',

    @useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='P@ssword!'

Eariler version could have some issue with user having no sysadmin role. You can use the following one for that.

EXEC master.dbo.sp_addlinkedserver

    @server = N'CHRGBKP2NT',

    @srvproduct=N'MSSQL',

    @provider=N'SQLNCLI',

@datasrc='PSM-TESTSVR2'


EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CHRGBKP2NT',
    @useself=N'False',@locallogin=NULL,@rmtuser=N'jcolton',@rmtpassword='jcolton254'


For SQL Server 2012, use the following script.

EXEC master.dbo.sp_addlinkedserver @server = N'TOCGJTIM1PV\JTIME', @srvproduct=N'MSSQL', @provider=N'SQLNCLI', @datasrc=N'TOCFJTIM1UV\JTIME'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TOCGJTIM1PV\JTIME',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

List MSSQL Database size

SELECT
    DB_NAME(db.database_id) DatabaseName,
    (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
    (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
    (CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
    (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
FROM sys.databases db
    LEFT JOIN
   (
   SELECT database_id, SUM(size) RowSize FROM sys.master_files
   WHERE type = 0
   GROUP BY database_id, type
   ) mfrows
   ON mfrows.database_id = db.database_id
   LEFT JOIN
  (
  SELECT database_id, SUM(size) LogSize FROM sys.master_files
  WHERE type = 1
  GROUP BY database_id, type
   ) mflog
  ON mflog.database_id = db.database_id
  LEFT JOIN
  (
  SELECT database_id, SUM(size) StreamSize
  FROM sys.master_files
  WHERE type = 2
  GROUP BY database_id, type
  ) mfstream
  ON mfstream.database_id = db.database_id
  LEFT JOIN
  (
   SELECT database_id, SUM(size) TextIndexSize
   FROM sys.master_files
   WHERE type = 4
   GROUP BY database_id, type
   ) mftext
   ON mftext.database_id = db.database_id

       MSSQL: How to recovery Database from SUSPECT Mode

Database generally Database moves to SUSPECT State due to following Reasons

• Database is corrupted
• ldf file is corrupted
• Disk Hardware Failure or Lack of Disk Space
• SQL Server does not have sufficient memory to Start SQL Server

Step 1

Never detach a suspect database it end up without re attach .as it is a Suspect database we can’t
take Backup of it. take the database into EMERGENCY MODE

– Take the database into Emergency mode
ALTER DATABASE [Database Name] SET EMERGENCY
GO

Step 2

set the database into single user mode using
– Change into single user mode
ALTER DATABASE [Database Name] SET SINGLE_USER

Step 3

Run DBCC Checkdb with allow data loss, by using this command all the open transactions will
be deleted ,
– Then run DBCC CheckDB command. Warning! REPAIR_ALLOW_DATA_LOSS can cause
some data to be lost!.
DBCC CheckDB (<[Database Name]> , REPAIR_ALLOW_DATA_LOSS)

Step 4

when the database repair is done set the database in multi user mode using

– Change from multiuser

ALTER DATABASE dbName SET MULTI_USER

Altogether

ALTER DATABASE ReportServer SET EMERGENCY
ALTER DATABASE ReportServer SET SINGLE_USER
DBCC CheckDB (ReportServer , REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE ReportServer SET MULTI_USER