Monday, November 30, 2015

Script: To find the size of the tables in a database

SELECT
table_name = SCHEMA_NAME(o.[schema_id]) + '.' + o.name

, data_size_mb = CAST(do.pages * 8. / 1024 AS DECIMAL(8,4))

FROM sys.objects o

JOIN (




SELECT
p.[object_id]

, total_rows = SUM(p.[rows])

, total_pages = SUM(a.total_pages)

, usedpages = SUM(a.used_pages)

, pages = SUM(




CASE
WHEN it.internal_type IN (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222) THEN 0

WHEN a.[type]! = 1 AND p.index_id < 2 THEN a.used_pages

WHEN p.index_id < 2 THEN a.data_pages ELSE 0




END
)
FROM sys.partitions p

JOIN sys.allocation_units a ON p.[partition_id] = a.container_id

LEFT JOIN sys.internal_tables it ON p.[object_id] = it.[object_id]

GROUP BY p.[object_id]

) do ON o.[object_id] = do.[object_id]

WHERE o.[type] = 'U'

Thursday, November 5, 2015

script: Change the owner of the sql jobs

DECLARE @name_holder VARCHAR(1000)
DECLARE My_Cursor CURSOR
FOR
SELECT [name]  FROM msdb..sysjobs 
OPEN My_Cursor
FETCH NEXT FROM My_Cursor INTO @name_holder
WHILE (@@FETCH_STATUS <> -1)
BEGIN
exec msdb..sp_update_job
        @job_name = @name_holder,
        @owner_login_name = 'sa'
FETCH NEXT FROM My_Cursor INTO @name_holder
END 
CLOSE My_Cursor
DEALLOCATE My_Cursor

Work adventure : MoveSQL Agent Jobs to another instanse


It is very easy to move (recreate) the sql agent job that you want to export to another instance of SQL server

  1. Highlight the job you want o export
  2. Right click
  3. Choose Scrip Job as CREATE TO


The script is created and you can run that in the new instance that will create the job.

To move multiple jobs
You can also script multiple jobs to a single file. In SSMS

Click on Jobs,
hit F7 to get the 'Object Explorer Details' tab.
Highlight the jobs you want,
Script job as...


Monday, November 2, 2015

CPU consumption

--from SQLAuthority website
--SQL SERVER – Who is consuming CPU on my SQL Server?

WITH DB_CPU_Statistics




AS


(SELECT pa.DatabaseID, DB_NAME(pa.DatabaseID) AS [Database Name], SUM(qs.total_worker_time/1000) AS [CPU_Time_Ms]

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

CROSS APPLY (SELECT CONVERT(INT, value) AS [DatabaseID]

FROM sys.dm_exec_plan_attributes(qs.plan_handle)

WHERE attribute = N'dbid') AS pa

GROUP BY DatabaseID)

SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Ranking],

[Database Name], [CPU_Time_Ms] AS [CPU Time (ms)],

CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]

FROM DB_CPU_Statistics

WHERE DatabaseID <> 32767 -- ResourceDB

ORDER BY [CPU Ranking] OPTION (RECOMPILE);

Thursday, October 29, 2015

Add linked servers

--view linked servers



exec sp_linkedservers




--OR



select * from sys.servers where is_linked = 1



 

--add a linked server


EXEC sp_addlinkedserver

@server=N'STAGINGCONNECT',

@srvproduct=N'SQL Sever',

@provider=N'SQLNCLI',

@datasrc=N'DB7830F';



 

 

--view the linked servers and the logins to the linked server



select s.name, p.principal_id, l.remote_name

from sys.servers s

join sys.linked_logins l

on s.server_id = l.server_id

left join sys.server_principals p

on l.local_principal_id = p.principal_id

where s.is_linked = 1