T-SQL system stored procedures 1

EXEC sp_spaceused

EXEC sp_spaceused dbo.table_name

database_name                                                                                                                    database_size      unallocated space

-------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------

master                                                                                                                           5.25 MB            1.52 MB

 

reserved           data               index_size         unused

------------------ ------------------ ------------------ ------------------

2536 KB            1160 KB            1040 KB            336 KB

Shrink log file

 

USE [master]

GO

ALTER DATABASE [REPORT] SET RECOVERY SIMPLE WITH NO_WAIT

DBCC SHRINKFILE(ANGEL_log, 1)

ALTER DATABASE [REPORT] SET RECOVERY FULL WITH NO_WAIT

GO

Change Server Name –SQL Server Instance adını değiştirme

 

SELECT @@SERVERNAME AS 'Server Name'

 

sp_dropserver 'OLD_NAME'

GO

sp_addserver 'NEW_NAME', local

GO

Restart sql server instance

Adding Custom error message

Exec sp_addmessage

     @msgnum=50002,

     @severity=12,

     @msgtext='error while %s description ;',

     @with_log='False',

       @replace='replace'

       

 

select * from sys.messages

send mail to operator

EXECUTE

      msdb.dbo.sp_notify_operator

      @name=N'operator name',@subject=N'subject of mail',

      @body=N'content of mail message'

Requests of sql server **

            SELECT

            r.session_id,

            r.blocking_session_id,

            s.program_name,

            s.host_name, 

            t.text

      FROM

            sys.dm_exec_requests r

            INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id

            CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t

      WHERE

            s.is_user_process = 1

 

 

report server subscription- agent list

    SELECT    

Schedule.ScheduleID as SubscriptionID,

Subscriptions.*,

Schedule.ScheduleID AS SQLAgent_Job_Name,

                  Subscriptions.Description AS sub_desc, Subscriptions.DeliveryExtension AS sub_delExt,

             [Catalog].Name AS ReportName, [Catalog].Path AS ReportPath

FROM         ReportServer.dbo.ReportSchedule

INNER JOIN ReportServer.dbo.Schedule ON ReportServer.dbo.ReportSchedule.ScheduleID = Schedule.ScheduleID

INNER JOIN ReportServer.dbo.Subscriptions ON ReportServer.dbo.ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID

INNER JOIN ReportServer.dbo.[Catalog] ON ReportServer.dbo.ReportSchedule.ReportID = [Catalog].ItemID AND Subscriptions.Report_OID = [Catalog].ItemID

                WHERE          

         Subscriptions.DeliveryExtension = 'Report Server Email'