אינדקס אתרים - HH3
 
 
 

install__ installed__  when does the server been installed

CREATE view [dbo].[v_date_server_installed]
as
SELECT 
@@servername as srv_name,
create_date as date_server_installed
FROM sys.server_principals
WHERE sid = 0x010100000000000512000000
GO
 

for OS__

 
U:\>systeminfo | find "Original Install Date"
Original Install Date:     02/22/2016, 01:58:30

 

 

 
 
 
powershell__ ps1__
 
 
get the cluster name that this machine belong to:
 
import-module failoverclusters
 
Get-Cluster | fl * 
 
Get-ClusterNode 

Get-ClusterGroup

--check if this machine is : VM or not 

get-wmiobject win32_computersystem | fl model

 

 

 

 
 
 

Location__ of all db's

 

select DB_NAME ( h.database_id ) as db_mames,

       h.physical_name as file_loaction

FROM master.sys.master_files h

order by database_id asc

 

 

 

 
 
 

find column__ טור__   שדה__

SELECT 
T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], 
P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE],   
CAST(P.PRECISION AS VARCHAR) +'/'+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]
FROM SYS.OBJECTS AS T
JOIN SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC='USER_TABLE'
and p.name like '%varchar%'
 
 
------------------------
 
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
       COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
       DATETIME_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS

 

 

 
 
 

 

 

 

 

 
 

datetime__  date__ time__  בוריס  boris    שעה__    sysdate__  between

 ,DATEPART(HOUR, GETDATE()) as ddd    -- just hour 
 ,FORMAT(GETDATE(),'hh:mm:ss') as eee -- hour + minutes +seconds
 
 
SELECT *
  FROM [a0_admin].[dbo].[T_tmp1_elampse_time]
  where DATEPART(HOUR, last_execution_time) between 3 and 5 
 

 

--------------------------------------------------------------------------------------
time__   date__  datetime__  
USE TestDB;
GO
 
SELECT h.*
FROM TestTable h
where h.date > '20160220' 
 
SELECT *
      --CONVERT(VARCHAR(10),[ChangedOn],111)
            
FROM [db407].[dbo].[PrincipalObjectAccess]
--where CONVERT(VARCHAR(10),[ChangedOn],111) > '01012'
where [ChangedOn] > '20160101'
 

 

sysdate__   current time timestap__    time stamp 

select SYSDATETIME ( )  

 

--get current time -30 days
 
select getdate ( ) - 30  
 
 
 
 

get all memeber__ members__  of login__  group__  בוריס boris__  GROUP__

 
exec xp_logininfo 'DOM001\Db_prod_bidba','members'
 
exec xp_logininfo 'DOM001\dba','members'
 

 

--activity monitor   :: grant first read on master  -- בוריס  boris

--to view a0_admin --BUT:you must grant before datareader to that USER

use master

go 

GRANT VIEW SERVER STATE TO [DOM001\c0718334] 

 

get lis of all groups AD users 

 
select sp.name as login,
       sp.type_desc as login_type,
       sl.password_hash,
       sp.create_date,
       sp.modify_date,
       case when sp.is_disabled = 1 then 'Disabled'
            else 'Enabled' end as status
from sys.server_principals sp
left join sys.sql_logins sl
          on sp.principal_id = sl.principal_id
--where sp.type not in ('G', 'R')
where sp.type_desc = 'WINDOWS_GROUP'
order by sp.name
 

 

 
 
USE [a0_admin]
GO
 
/****** Object:  View [dbo].[v_pack_CRM_ALL]    Script Date: 03/21/2016 09:56:08 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
create view [dbo].[v_pack_CRM_ALL]
AS
SELECT B.NAME, MAX(End_DateTime) AS a_End_DateTime--, b.jb_description
FROM 
(
select h.*
from 
(
SELECT j.Name, 
--jh.Step_name,
--CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
--(jh.run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/)
--AS Start_DateTime,
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100) / (86399.9964 /* Start Date Time */)
+ ((jh.run_duration/10000 * 3600) + ((jh.run_duration%10000)/100*60) + (jh.run_duration%10000)%100
/*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime
 
,j.description as jb_description
 
from msdb..sysjobhistory jh, msdb..sysjobs j
where jh.job_id=j.job_id
)h
where h.End_DateTime >=(select cast(cast(getdate() as varchar(12)) as datetime))  --just for today__current__ current day - only today  
and h.name like '%db407%'
OR h.name like '%db740%'
 
)B  
GROUP BY B.name,  b.jb_description
--ORDER BY MAX(End_DateTime) ASC
GO
 
 

 

 

 

 

 
 
 

match__ user  מוטי

מוטי

Set user \\\ set_user

בכל מקרה לאחר ביצוע restore    מה שנקרא refresh  מסביבת ייצור . אזי אין התאמה בין היוזר של ה DATA BASE   לבין היוזר של הלוגאין של השרת.

# ‏25/10/2012 11:43:45  הערה חשובה . שתי השיטות אפשריות אך ורק כאשר היוזר עצמו כבר קיים ב סרבר לוגאין . שכן הפקודה לא יוצרת אותו 'יש מאיין' . אם תריץ במקום שבו היוזר לא קיים אזי תקבל הודעה כזו :

Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114

Terminating this procedure. The User name 'Survey_admin' is absent or invalid.

 

 

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/MSSQLServerAdmin/do-you-still-use-sp_change_users_login

 

 

use zabi_db

go

-- 1 method

EXECUTE sp_change_users_login 'Update_One', 'TestLogin', 'TestLogin'

 

 

 

 

use projectportal

go

 

EXECUTE sp_change_users_login 'Update_One', 'ICC_PPortal', 'ICC_PPortal'

 

הערה: 'Update_One'  = תמיד נשאר = קונסטנטה .

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

 

use survey

go

 

-- 1 method

EXECUTE sp_change_users_login 'Update_One', 'Survey_admin', 'Survey_admin'

 

 

 

use zabi_db

go

--2 method

ALTER USER TestLogin WITH LOGIN = TestLogin

 

--2 method

ALTER USER TestLogin WITH LOGIN = TestLogin

 

הערה: ה testlogin   זה היוזר במקרה זה . וכן הסטרינג update_one = מחוייב במקרה זה – בדיוק בשיטה הנ"ל .

 

 

use zabi_db

go

--This is the old way

EXECUTE sp_change_users_login 'Update_One', 'UserName', 'UserName'

 

 

 

--This is the new way -- much cleaner

use BI_GEN_ADMIN

go

 

ALTER USER UserName WITH LOGIN = UserName

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

 

 

use BI_GEN_ADMIN

go

 

ALTER USER [domainX\IT-BI_Team] WITH LOGIN = [domainX\IT-BI_Team]

 

 

use test_1

go 

ALTER USER [domainX\zgutman] WITH LOGIN = [domainX\zgutman]

 

 

 

 

 
 
-- List all heap tables   heap__
 
SELECT SCH.name + '.' + TBL.name AS TableName 
FROM sys.tables AS TBL 
     INNER JOIN sys.schemas AS SCH 
         ON TBL.schema_id = SCH.schema_id 
     INNER JOIN sys.indexes AS IDX 
         ON TBL.object_id = IDX.object_id 
            AND IDX.type = 0 -- = Heap 
ORDER BY TableName

 

 

 
 
 
index__  indexes__   get list of all indexes in datbase
 
SELECT 
     TableName = t.name,
     IndexName = ind.name,
     IndexId = ind.index_id,
     ColumnId = ic.index_column_id,
     ColumnName = col.name,
     ind.*,
     ic.*,
     col.* 
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 
WHERE 
     ind.is_primary_key = 0 
     AND ind.is_unique = 0 
     AND ind.is_unique_constraint = 0 
     AND t.is_ms_shipped = 0 
ORDER BY 
     t.name, ind.name, ind.index_id, ic.index_column_id 
 
 

 

 

 

 

 
 
 
 

jobs__ job__ get all /// tsql get ALL- LIST - schedule of jobs   bi__  dwh__ 

-- https://dba.stackexchange.com/questions/148321/list-jobs-schedules-and-next-scheduled-run-datetimes?fireglass_rsn=true
 
 
 
USE msdb
Go
 
 
SELECT @@servername as instancename ,
dbo.sysjobs.Name AS 'Job Name', 
'Job Enabled' = CASE dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE dbo.sysschedules.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END, 
'Start Date' = CASE active_start_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),active_start_date),1,4) + '/' + 
substring(convert(varchar(15),active_start_date),5,2) + '/' + 
substring(convert(varchar(15),active_start_date),7,2)
END,
'Start Time' = CASE len(active_start_time)
WHEN 1 THEN cast('00:00:0' + right(active_start_time,2) as char(8))
WHEN 2 THEN cast('00:00:' + right(active_start_time,2) as char(8))
WHEN 3 THEN cast('00:0' 
+ Left(right(active_start_time,3),1)  
+':' + right(active_start_time,2) as char (8))
WHEN 4 THEN cast('00:' 
+ Left(right(active_start_time,4),2)  
+':' + right(active_start_time,2) as char (8))
WHEN 5 THEN cast('0' 
+ Left(right(active_start_time,5),1) 
+':' + Left(right(active_start_time,4),2)  
+':' + right(active_start_time,2) as char (8))
WHEN 6 THEN cast(Left(right(active_start_time,6),2) 
+':' + Left(right(active_start_time,4),2)  
+':' + right(active_start_time,2) as char (8))
END,
-- active_start_time as 'Start Time',
CASE len(run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast('00:0' 
+ Left(right(run_duration,3),1)  
+':' + right(run_duration,2) as char (8))
WHEN 4 THEN cast('00:' 
+ Left(right(run_duration,4),2)  
+':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0' 
+ Left(right(run_duration,5),1) 
+':' + Left(right(run_duration,4),2)  
+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2) 
+':' + Left(right(run_duration,4),2)  
+':' + right(run_duration,2) as char (8))
END as 'Max Duration',
    CASE(dbo.sysschedules.freq_subday_interval)
WHEN 0 THEN 'Once'
ELSE cast('Every ' 
+ right(dbo.sysschedules.freq_subday_interval,2) 
+ ' '
+     CASE(dbo.sysschedules.freq_subday_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END as char(16))
    END as 'Subday Frequency'
FROM dbo.sysjobs 
LEFT OUTER JOIN dbo.sysjobschedules 
ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id 
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM dbo.sysjobhistory
GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time = 0
 
UNION
 
SELECT @@servername as instancename ,
dbo.sysjobs.Name AS 'Job Name', 
'Job Enabled' = CASE dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE dbo.sysschedules.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END, 
'Start Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' + 
substring(convert(varchar(15),next_run_date),5,2) + '/' + 
substring(convert(varchar(15),next_run_date),7,2)
END,
'Start Time' = CASE len(next_run_time)
WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))
WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))
WHEN 3 THEN cast('00:0' 
+ Left(right(next_run_time,3),1)  
+':' + right(next_run_time,2) as char (8))
WHEN 4 THEN cast('00:' 
+ Left(right(next_run_time,4),2)  
+':' + right(next_run_time,2) as char (8))
WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1) 
+':' + Left(right(next_run_time,4),2)  
+':' + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2) 
+':' + Left(right(next_run_time,4),2)  
+':' + right(next_run_time,2) as char (8))
END,
-- next_run_time as 'Start Time',
CASE len(run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast('00:0' 
+ Left(right(run_duration,3),1)  
+':' + right(run_duration,2) as char (8))
WHEN 4 THEN cast('00:' 
+ Left(right(run_duration,4),2)  
+':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0' 
+ Left(right(run_duration,5),1) 
+':' + Left(right(run_duration,4),2)  
+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2) 
+':' + Left(right(run_duration,4),2)  
+':' + right(run_duration,2) as char (8))
END as 'Max Duration',
    CASE(dbo.sysschedules.freq_subday_interval)
WHEN 0 THEN 'Once'
ELSE cast('Every ' 
+ right(dbo.sysschedules.freq_subday_interval,2) 
+ ' '
+     CASE(dbo.sysschedules.freq_subday_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END as char(16))
    END as 'Subday Frequency'
FROM dbo.sysjobs 
LEFT OUTER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id 
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM dbo.sysjobhistory
GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time <> 0
 
ORDER BY [Start Date],[Start Time]
 
 
 
 
use a0_admin 
go 
 
 
SELECT msdb.dbo.sysjobs.Name AS 'Job Name', 
'Job Enabled' = CASE msdb.dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE msdb.dbo.sysschedules.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END, 
'Start Date' = CASE active_start_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),active_start_date),1,4) + '/' + 
substring(convert(varchar(15),active_start_date),5,2) + '/' + 
substring(convert(varchar(15),active_start_date),7,2)
END,
'Start Time' = CASE len(active_start_time)
WHEN 1 THEN cast('00:00:0' + right(active_start_time,2) as char(8))
WHEN 2 THEN cast('00:00:' + right(active_start_time,2) as char(8))
WHEN 3 THEN cast('00:0' 
+ Left(right(active_start_time,3),1)  
+':' + right(active_start_time,2) as char (8))
WHEN 4 THEN cast('00:' 
+ Left(right(active_start_time,4),2)  
+':' + right(active_start_time,2) as char (8))
WHEN 5 THEN cast('0' 
+ Left(right(active_start_time,5),1) 
+':' + Left(right(active_start_time,4),2)  
+':' + right(active_start_time,2) as char (8))
WHEN 6 THEN cast(Left(right(active_start_time,6),2) 
+':' + Left(right(active_start_time,4),2)  
+':' + right(active_start_time,2) as char (8))
END,
-- active_start_time as 'Start Time',
CASE len(run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast('00:0' 
+ Left(right(run_duration,3),1)  
+':' + right(run_duration,2) as char (8))
WHEN 4 THEN cast('00:' 
+ Left(right(run_duration,4),2)  
+':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0' 
+ Left(right(run_duration,5),1) 
+':' + Left(right(run_duration,4),2)  
+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2) 
+':' + Left(right(run_duration,4),2)  
+':' + right(run_duration,2) as char (8))
END as 'Max Duration',
    CASE(msdb.dbo.sysschedules.freq_subday_interval)
WHEN 0 THEN 'Once'
ELSE cast('Every ' 
+ right(msdb.dbo.sysschedules.freq_subday_interval,2) 
+ ' '
+     CASE(msdb.dbo.sysschedules.freq_subday_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END as char(16))
    END as 'Subday Frequency'
FROM msdb.dbo.sysjobs 
LEFT OUTER JOIN msdb.dbo.sysjobschedules 
ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobschedules.job_id
INNER JOIN msdb.dbo.sysschedules ON msdb.dbo.sysjobschedules.schedule_id = msdb.dbo.sysschedules.schedule_id 
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM msdb.dbo.sysjobhistory
GROUP BY job_id) Q1
ON msdb.dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time = 0
 
UNION
 
SELECT msdb.dbo.sysjobs.Name AS 'Job Name', 
'Job Enabled' = CASE msdb.dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE msdb.dbo.sysschedules.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END, 
'Start Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' + 
substring(convert(varchar(15),next_run_date),5,2) + '/' + 
substring(convert(varchar(15),next_run_date),7,2)
END,
'Start Time' = CASE len(next_run_time)
WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))
WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))
WHEN 3 THEN cast('00:0' 
+ Left(right(next_run_time,3),1)  
+':' + right(next_run_time,2) as char (8))
WHEN 4 THEN cast('00:' 
+ Left(right(next_run_time,4),2)  
+':' + right(next_run_time,2) as char (8))
WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1) 
+':' + Left(right(next_run_time,4),2)  
+':' + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2) 
+':' + Left(right(next_run_time,4),2)  
+':' + right(next_run_time,2) as char (8))
END,
-- next_run_time as 'Start Time',
CASE len(run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast('00:0' 
+ Left(right(run_duration,3),1)  
+':' + right(run_duration,2) as char (8))
WHEN 4 THEN cast('00:' 
+ Left(right(run_duration,4),2)  
+':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0' 
+ Left(right(run_duration,5),1) 
+':' + Left(right(run_duration,4),2)  
+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2) 
+':' + Left(right(run_duration,4),2)  
+':' + right(run_duration,2) as char (8))
END as 'Max Duration',
    CASE(msdb.dbo.sysschedules.freq_subday_interval)
WHEN 0 THEN 'Once'
ELSE cast('Every ' 
+ right(msdb.dbo.sysschedules.freq_subday_interval,2) 
+ ' '
+     CASE(msdb.dbo.sysschedules.freq_subday_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END as char(16))
    END as 'Subday Frequency'
FROM msdb.dbo.sysjobs 
LEFT OUTER JOIN msdb.dbo.sysjobschedules ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobschedules.job_id
INNER JOIN msdb.dbo.sysschedules ON msdb.dbo.sysjobschedules.schedule_id = msdb.dbo.sysschedules.schedule_id 
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM msdb.dbo.sysjobhistory
GROUP BY job_id) Q1
ON msdb.dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time <> 0
 
ORDER BY [Start Date],[Start Time]

 

 

USE [a0_admin]
GO
 
/****** Object:  View [dbo].[v_jobs_timming]    Script Date: 24/04/2017 14:56:27 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
create view [dbo].[v_jobs_timming]
as 
 
SELECT top 100 percent msdb.dbo.sysjobs.Name AS 'Job Name', 
'Job Enabled' = CASE msdb.dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE msdb.dbo.sysschedules.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END, 
'Start Date' = CASE active_start_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),active_start_date),1,4) + '/' + 
substring(convert(varchar(15),active_start_date),5,2) + '/' + 
substring(convert(varchar(15),active_start_date),7,2)
END,
'Start Time' = CASE len(active_start_time)
WHEN 1 THEN cast('00:00:0' + right(active_start_time,2) as char(8))
WHEN 2 THEN cast('00:00:' + right(active_start_time,2) as char(8))
WHEN 3 THEN cast('00:0' 
+ Left(right(active_start_time,3),1)  
+':' + right(active_start_time,2) as char (8))
WHEN 4 THEN cast('00:' 
+ Left(right(active_start_time,4),2)  
+':' + right(active_start_time,2) as char (8))
WHEN 5 THEN cast('0' 
+ Left(right(active_start_time,5),1) 
+':' + Left(right(active_start_time,4),2)  
+':' + right(active_start_time,2) as char (8))
WHEN 6 THEN cast(Left(right(active_start_time,6),2) 
+':' + Left(right(active_start_time,4),2)  
+':' + right(active_start_time,2) as char (8))
END,
-- active_start_time as 'Start Time',
CASE len(run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast('00:0' 
+ Left(right(run_duration,3),1)  
+':' + right(run_duration,2) as char (8))
WHEN 4 THEN cast('00:' 
+ Left(right(run_duration,4),2)  
+':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0' 
+ Left(right(run_duration,5),1) 
+':' + Left(right(run_duration,4),2)  
+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2) 
+':' + Left(right(run_duration,4),2)  
+':' + right(run_duration,2) as char (8))
END as 'Max Duration',
    CASE(msdb.dbo.sysschedules.freq_subday_interval)
WHEN 0 THEN 'Once'
ELSE cast('Every ' 
+ right(msdb.dbo.sysschedules.freq_subday_interval,2) 
+ ' '
+     CASE(msdb.dbo.sysschedules.freq_subday_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END as char(16))
    END as 'Subday Frequency'
FROM msdb.dbo.sysjobs 
LEFT OUTER JOIN msdb.dbo.sysjobschedules 
ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobschedules.job_id
INNER JOIN msdb.dbo.sysschedules ON msdb.dbo.sysjobschedules.schedule_id = msdb.dbo.sysschedules.schedule_id 
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM msdb.dbo.sysjobhistory
GROUP BY job_id) Q1
ON msdb.dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time = 0
 
UNION
 
SELECT top 100 percent msdb.dbo.sysjobs.Name AS 'Job Name', 
'Job Enabled' = CASE msdb.dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE msdb.dbo.sysschedules.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END, 
'Start Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' + 
substring(convert(varchar(15),next_run_date),5,2) + '/' + 
substring(convert(varchar(15),next_run_date),7,2)
END,
'Start Time' = CASE len(next_run_time)
WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))
WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))
WHEN 3 THEN cast('00:0' 
+ Left(right(next_run_time,3),1)  
+':' + right(next_run_time,2) as char (8))
WHEN 4 THEN cast('00:' 
+ Left(right(next_run_time,4),2)  
+':' + right(next_run_time,2) as char (8))
WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1) 
+':' + Left(right(next_run_time,4),2)  
+':' + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2) 
+':' + Left(right(next_run_time,4),2)  
+':' + right(next_run_time,2) as char (8))
END,
-- next_run_time as 'Start Time',
CASE len(run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast('00:0' 
+ Left(right(run_duration,3),1)  
+':' + right(run_duration,2) as char (8))
WHEN 4 THEN cast('00:' 
+ Left(right(run_duration,4),2)  
+':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0' 
+ Left(right(run_duration,5),1) 
+':' + Left(right(run_duration,4),2)  
+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2) 
+':' + Left(right(run_duration,4),2)  
+':' + right(run_duration,2) as char (8))
END as 'Max Duration',
    CASE(msdb.dbo.sysschedules.freq_subday_interval)
WHEN 0 THEN 'Once'
ELSE cast('Every ' 
+ right(msdb.dbo.sysschedules.freq_subday_interval,2) 
+ ' '
+     CASE(msdb.dbo.sysschedules.freq_subday_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END as char(16))
    END as 'Subday Frequency'
FROM msdb.dbo.sysjobs 
LEFT OUTER JOIN msdb.dbo.sysjobschedules ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobschedules.job_id
INNER JOIN msdb.dbo.sysschedules ON msdb.dbo.sysjobschedules.schedule_id = msdb.dbo.sysschedules.schedule_id 
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM msdb.dbo.sysjobhistory
GROUP BY job_id) Q1
ON msdb.dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time <> 0
 
ORDER BY [Start Date],[Start Time]
 
 
GO
 
 
 
 

 

 

 

 

job__ jobs__   bi__  dwh__   boris__  מתי הג'ובים שלהם הסתיימו באותו יום 
 
 
get the jobs for bi - get the all job ended . 
 
--------------------
 
-- when the job ended --get all BI proccess 
 
SELECT B.NAME, MAX(End_DateTime) AS a_End_DateTime
FROM 
(
select h.*
from 
(
SELECT j.Name, 
--jh.Step_name,
--CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
--(jh.run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/)
--AS Start_DateTime,
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100) / (86399.9964 /* Start Date Time */)
+ ((jh.run_duration/10000 * 3600) + ((jh.run_duration%10000)/100*60) + (jh.run_duration%10000)%100
/*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime
from msdb..sysjobhistory jh, msdb..sysjobs j
where jh.job_id=j.job_id
)h
where h.End_DateTime >=(select cast(cast(getdate() as varchar(12)) as datetime))  --just for today 
and h.name like '%pack_bi%'
)B  
GROUP BY B.name
ORDER BY MAX(End_DateTime) ASC
 
 
 
 
 
------------------------------------
pad__  for filling the dattime that comeup from server 
 
RIGHT(REPLICATE('0',6)+CAST((MAX(h.run_duration)) AS VARCHAR(6)),6)as b_run_duration
 
select RIGHT(REPLICATE('0',6)+CAST(34 AS VARCHAR(6)),6)as b_run_duration  -- 000034
------------------------------------
 
where h.End_DateTime >(select cast(cast(getdate() as varchar(12)) as datetime))  --just for today 
 
------------------------------------
 
--get all BI proccess- for today:  more details  : BI and BI__ for CRM__ --by filter 
 
select h.*
from 
(
SELECT j.Name, 
       jh.run_duration,
       RIGHT(REPLICATE('0',6)+CAST(jh.run_duration AS VARCHAR(6)),6)as b_run_duration,
       jh.step_id,
--jh.Step_name,
--CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
--(jh.run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/)
--AS Start_DateTime,
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100) / (86399.9964 /* Start Date Time */)
+ ((jh.run_duration/10000 * 3600) + ((jh.run_duration%10000)/100*60) + (jh.run_duration%10000)%100
/*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime
from msdb..sysjobhistory jh, msdb..sysjobs j
where jh.job_id=j.job_id
)h
where h.End_DateTime >=(select cast(cast(getdate() as varchar(12)) as datetime))  --just for today 
and h.name like '%pack_bi%'
and h.name like '%crm%'
and h.step_id = 0
 

 

 

more version: add description for each job 

job__ jobs__   BI__  dwh__   boris__  מתי הג'ובים שלהם הסתיימו באותו יום  --description__
 
 
SELECT B.NAME, MAX(End_DateTime) AS a_End_DateTime, b.jb_description
FROM 
(
select h.*
from 
(
SELECT j.Name, 
--jh.Step_name,
--CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
--(jh.run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/)
--AS Start_DateTime,
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100) / (86399.9964 /* Start Date Time */)
+ ((jh.run_duration/10000 * 3600) + ((jh.run_duration%10000)/100*60) + (jh.run_duration%10000)%100
/*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime
 
,j.description as jb_description
 
from msdb..sysjobhistory jh, msdb..sysjobs j
where jh.job_id=j.job_id
)h
where h.End_DateTime >=(select cast(cast(getdate() as varchar(12)) as datetime))  --just for today 
and h.name like '%pack_bi%'
)B  
GROUP BY B.name,  b.jb_description
--having jb_description like '%db829%'
ORDER BY MAX(End_DateTime) ASC
 

 

 

 
 
 
 

  EXCEPT__    שלומי - ווילהם -  william

returns rows from the first SELECT statement that are not returned by the second SELECT statement

 

The basic syntax of EXCEPT is as follows:

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

EXCEPT

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

 

 

 

 
 
 
 
 
 

 

 

 
 
 

-- sysadmin__   sa__   permission__

get list of  SA users 

 

USE master
GO
 
SELECT  p.name AS [loginname] ,
        'is sa user'  as sa_user,
        p.type ,
        p.type_desc ,
        p.is_disabled,
        CONVERT(VARCHAR(10),p.create_date ,101) AS [created],
        CONVERT(VARCHAR(10),p.modify_date , 101) AS [update]
FROM    sys.server_principals p
        JOIN sys.syslogins s ON p.sid = s.sid
WHERE   p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
        -- Logins that are not process logins
        AND p.name NOT LIKE '##%'
        -- Logins that are sysadmins
        AND s.sysadmin = 1
        order by p.type
 
 
-- more option:
EXEC sp_helpsrvrolemember 'sysadmin'
 

 

 

 

 

 

 

 

location__ file location__  path__ 

 

SELECT namephysical_name AS current_file_location
FROM sys.master_files

 

 

 


 

 

 

 

 

 

 
 
 
 
--get last__ access__ for database 


SELECT DatabaseName, MAX(LastAccessDate) LastAccessDate
FROM
    (SELECT
        DB_NAME(database_id) DatabaseName
        , last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update
    
FROM sys.dm_db_index_usage_stats) AS PivotTable
UNPIVOT 
    (LastAccessDate FOR last_user_access IN
        (last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update)
    ) AS UnpivotTable
GROUP BY DatabaseName
HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb')
ORDER BY 2

 

 

 

doc = tsql 444 - חירום__

 

Backup log__  transaction log \\\ tran log \\\tran__  trn__

 

 

backup log AdventureWorksLT To disk = 'D:\zzz.log'

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/9997e75e-f126-4901-9379-de540a708ec9/

 

 

--We can fake it by not writing our backup to a real device.

--SQL Server lets us use the NUL: location as a backup target,

--so the following will do a log backup without actually saving

--the contents anywhere:

 

BACKUP LOG MyDb TO DISK='NUL:'  ניסיתי את הפקודה – היא עובדת

 

BACKUP LOG WITH TRUNCATE_ONLY =  is a dangerous command:

 

בנוסף, יש פה שיטות למצבי חירום__ שהלוג מתנפח : log__  transaction log

http://www.techrepublic.com/blog/datacenter/help-my-sql-server-log-file-is-too-big/448

http://www.mssqltips.com/sqlservertip/1464/issues-with-sql-server-backup-log-with-nolog-or-truncateonly/

http://blog.sqlauthority.com/2010/09/20/sql-server-how-to-stop-growing-log-file-too-big/

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/MSSQLServerAdmin/t-sql-tuesday-25-t

 

 

1. switch the database’s recovery model to simple (This empties out the transaction log)

2. run a DBCC SHRINKFILE afterwards,

3. switch the recovery model back           to full.

4. (back up to null ) = consider  = BACKUP LOG MyDb TO DISK='NUL:'

Processed 7238 pages for database 'Extranet_INT', file 'extranetw_log' on file 1.

BACKUP LOG successfully processed 7238 pages in 0.827 seconds (71.693 MB/sec).

5. Detach & attach = erase the log file

 

 

כעת שיטה לכיווץ קובץ הלוג בפקודה ( שלב א' מצא את שמו במערכת , שלב ב' כווץ אותו ) .

http://www.techrepublic.com/blog/datacenter/help-my-sql-server-log-file-is-too-big/448

 

סקריפטים לאירוע כזה – יש עוד כמובן במסמך = לפי הרלוונטיות .

 

 

 

What causes the abnormal log file growth ?

http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Log%20file%20growth%20in%20SQL%20Server&referringTitle=Home

 

 

SELECT name,

       recovery_model_desc      --recovery__ 

FROM sys.databases

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

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

 

use Northwind

go

 

SELECT name

FROM sys.database_files

WHERE type_desc = 'LOG'

 

DBCC SHRINKFILE ('Northwind_log', 5)

----פה למשל הוא מכווץ אותו ל 5 מגה – היחידות הם ב : מגות .   --

 

 

‏17/12/2012 16:07:37

                                     

--in 2000

 

select name,

databasepropertyex(name, 'Recovery') as RecoveryModel

from master.dbo.sysdatabases

order by name

 

 

 

 

 

Opentran__  open tran  open_tran

‏20/05/2012 14:23:38 

http://www.techrepublic.com/blog/datacenter/help-my-sql-server-log-file-is-too-big/448

 

טרנזאקציות פתוחות__    open__  פתוח__

אם ישנה טרנזאציה ארוכה פתוחה ( מסיבות שונות) זה תוקע ומגדיל לך את הלוג – לכן כדי לראות אם יש לך אחת כזו-הרץ : הוא מדווח על כל הפתוחות.

DBCC OPENTRAN

 

 

‏04/09/2012 13:13:22

use PointsecMI

go

DBCC OPENTRAN

 

Transaction information for database 'PointsecMI'.

 

Oldest active transaction:

    SPID (server process ID): 298

    UID (user ID) : -1

    Name          : INSERT

    LSN           : (399704:1445:1)

    Start time    : Sep  4 2012  1:11:58:153PM

    SID           : 0xc0f8071681d71341ad120ea2233fac60

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

 

 

 

/*

If there are open transactions, DBCC OPENTRAN will provide a session_id (SPID)

of the connection that has the transaction open. You can pass this session_id to sp_who2 to determine

which user has the connection open.

*/

EXECUTE sp_who2 spid

 

--Alternatively, you can run the following query to determine the user.

 

SELECT * FROM sys.dm_exec_sessions

WHERE session_id = spid  -- (from DBCC OPENTRAN)

 

 

 

--or

 

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

AND   r.session_id = SPID  --FROM DBCC OPENTRAN

 

 

 

 

‏31/05/2012 08:41:35

 

עוד שיטה יפה לטיפול במצבי משבר כאלו – אולם עוד לא ניסיתי .

 

http://sqlserverdb.blogspot.com/2011/01/transaction-log-for-database-tempdb-is.html

 

 

--Solution: 1

-- First check Tempdb Tran log file size using  dbcc sqlperf(logspace)

-- tempdb 999.9922 99.99628 0

USE MASTER

GO

ALTER DATABASE TEMPDB MODIFY FILE (NAME='templog', SIZE=1500MB)

 

--Solution:2

ALTER DATABASE Tempdb

ADD LOG FILE

( NAME = tempdblog2,

FILENAME = 'E:\MSSQL.1\MSSQL\DATA\tempdblog2.ldf',

SIZE = 10MB,

MAXSIZE = 100MB,

FILEGROWTH = 10MB)

 

 

 

 

 

 

 

‏29/07/2012 20:11:58

חירום__     Shrink__   tempdb__  וכן לכל database  אחר

פה יש עוד הרבה טיפים – כולל קודים ( עוד לא עברתי על כולם )

http://msdn.microsoft.com/en-us/library/ms176029(v=sql.105).aspx

 

מקבל הודעה כזו:

The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases (Microsoft SQL Server, Error: 9002)

 

 

 

 

‏14/01/2013 09:56:47

 

select log_reuse_wait_desc

from master.sys.databases

where name ='EVDA-Searches'

 

מה שאני מבין שזה תוכנית ה בקאפ לוג – שתקועה – לא ברור מה הסיבה .

 

 

select log_reuse_wait_desc

from master.sys.databases

where name ='tempdb'

 

 

 

 

 

 

 

 

 

--מריץ כדי לקבל גדלים ראשוניים--

use tempdb

go

 

select (size*8)/1024/1024 as FileSizeGB,

       NAME

from sys.database_files

 

--רואה שאכן גודל הלוג הוא מפלצת--

 

 

--מריץ כיווץ ללוג מעביר אותו ל 100 מגה 

use tempdb

go

 

-- http://support.microsoft.com/kb/307487

-- this command shrinks the log file, examine the last paragraph.                  

DBCC shrinkfile (templog, 100)

go

 

 

--שים לב ל שמות הפיזיים של הקבצים שהם קצת שונים מהשמות הלוגיים  --

 

 

--מריץ כדי לקבל גדלים ראשוניים--

use PointsecMI

go

 

select (size*8)/1024/1024 as FileSizeGB,

       (size*8)/1024 as FileSizeMB,

       NAME

from sys.database_files

 

 

 

 

 

 

 

 

 

 

 

 

-- ANOTHER ONE

--מריץ כיווץ ללוג מעביר אותו ל 5 מגה 

use PointsecMI

go

 

-- http://support.microsoft.com/kb/307487

-- this command shrinks the log file, examine the last paragraph.                  

DBCC shrinkfile (PointsecMI_log, 5)

go

 

 

 

-- ‏04/02/2014 06:43:10

 

use ObserveIT

go

 

select (size*8)/1024/1024 as FileSizeGB,

       (size*8)/1024 as FileSizeMB,

       NAME

from sys.database_files

 

 

 

use ObserveIT

go

 

-- http://support.microsoft.com/kb/307487

-- this command shrinks the log file, examine the last paragraph.                  

DBCC shrinkfile (ObserveIT_Log, 1)

go

 

 

 

 

 

 

 

 

 

 

 

לעיתים יש תקלה__   תקלות__

 

http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/ae4db890-c15e-44de-a2af-e85c04260331/

 

Cannot shrink log file because all logical log files are in use

 

Resolve:

Change to ‘simple’ recovery model.

 

‏03/09/2012 11:23:48 הערה: לפעמים אתה מריץ – וזה לא עוזר : או שהוא לא מכווץ בכלל – או שמכווץ אבל לא מספיק . במקרה כזה נסה שוב ושוב (‏17/02/2014 13:44:14   = ממש כך ! לחץ על הרצת הסקריפט כמה וכמה פעמים – מניסיון – זה עובד ... ) או עוד שיטה  סגור את ה SSMS ופתח מחדש . סגור קוננקשיין ופתח מחדש . גבה אותו למיקום לא קיים (כמו שמוסבר פה במסמך ) העבר ל SIMPLE והרץ שרינק שוב .  יש מצב בכלל שהוא בנוי מלכתחילה  ל initial size  גבוה ואז הוא יתן לו כאמור את המינימום הזה – לא יעזור לך כלום ... אלא אם כן תשנה פרמטר זה .

יש במסמך פה עוד חומר בנושא חפש את המילה: חירום__

 

 

# ‏19/02/2014 14:44:46  עוד בעיה : היה עקשן מאוד . מה שבוצע : העברה ל FULL > ביצוע BACKUP   > ביצוע גיבוי ללוג >

לגבי ה initial size   היתה לי בדיוק בעיה כזו ב observeit .

‏20/02/2014 09:34:50 עדכון : יום למחרת כבר הצלחתי – באותה פקודה ממש לצמצם את הלוג . כלומר: כנראה הייתה עליו איזו פעולה שהשתחררה בסוף ולכן למחרת זה כן עבד .

 

בכל מקרה : קבל עוד ארגז כלים .

 

shrink__

use test_1

go

SELECT file_id, name, physical_name, (size * 8 /1024.0) AS SizeMB

FROM sys.database_files

ALTER DATABASE test_1

MODIFY FILE (NAME = test_2_log, SIZE = 9MB)

תקלה__  תקלות__

אתה מקבל הודעה כזו בזמן ניסיון לכווץ הלוג .

MODIFY FILE failed. Specified size is less than or equal to current size.

פיתרון:

 

חייבים להריץ קודם פעולה מקדימה של : TRUNCATEONLY  ואז ניתן לכווץ .

http://www.sqlserver-dba.com/2013/02/modify-sql-transaction-log-file-size-increase-or-decrease.html

use test_1

go

SELECT file_id, name, physical_name, (size * 8 /1024.0) AS SizeMB

FROM sys.database_files

dbcc shrinkfile(test_2_log,TRUNCATEONLY)

GO

ALTER DATABASE test_1

MODIFY FILE (NAME = test_2_log, SIZE = 9MB)

 





 

 

 

 

 

 

 

 

 

 

 

 

 

 

עוד אחת :

Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.

 

הוא פשוט נמצא כרגע במהלך של גיבוי לילי – ואני ניסיתי פעולה באמצע – ולכן הוא זרק את ההודעה הנ"ל  .יש פה בהמשך תיאור איך משנים את הגודל התחלתי הזה .

 

 

 

 

 

 

 

--מריץ שוב בדיקת גדלים

use tempdb

select (size*8)/1024/1024 as FileSizeGB,

       NAME

from sys.database_files

 

use tempdb

select (size*8)/1024 as FileSizeMB,

       NAME

from sys.database_files

 

--אכן כווץ--

 

‏01/08/2013 11:00:21

 

 

 

use VCDB

go

 

select (size*8)/1024/1024 as FileSizeGB,

       (size*8)/1024 as FileSizeMB,

       NAME

from sys.database_files

 

 

use VCDB

go

 

-- http://support.microsoft.com/kb/307487

-- this command shrinks the log file, examine the last paragraph.                  

DBCC shrinkfile (vcdb_log, 100)

go

 

 

 

 

 

 

 

‏25/06/2012 11:37:27

Tempdb__

 

http://blog.sqlauthority.com/2007/12/17/sql-server-t-sql-script-to-find-details-about-tempdb/

 

 

 

 

 

SELECT

name                AS FileName,

size*1.0/128        AS FileSizeinMB,

size*1.0/128/1024   AS FileSizeinGB,

CASE max_size

WHEN 0 THEN 'Autogrowth is off.'

WHEN -1 THEN 'Autogrowth is on.'

ELSE 'Log file will grow to a maximum size of 2 TB.'

END                 AS AutogrowthStatus,

growth              AS 'GrowthValue',

                  'GrowthIncrement' =

CASE

WHEN growth = 0 THEN 'Size is fixed and will not grow.'

WHEN growth > 0

AND is_percent_growth = 0

THEN 'Growth value is in 8-KB pages.'

ELSE 'Growth value is a percentage.'

END

FROM tempdb.sys.database_files;

GO

 

 

 

 

 

 

 

 

‏25/10/2012 15:46:48

Initial__  size__   change initial size

אתה יכול לשנות את ה MDF או ה LDF – שים לב לשמות ה לוגיים כאמור .

 

http://www.sqlserver-training.com/sql-script-to-modify-the-database-file-size/-

 

שים לב ל שמות הלוגיים ולא הפיזיים . שים לב שאתה נותן לו את יחידות הגודל במפורש .

-- general

ALTER DATABASE <database_name> MODIFY FILE

(NAME=logical_db_name , SIZE=size[ KB | MB | GB | TB ])

go

 

 

-- Getting Information about Database files like logical name , location and size.

 

 

use ObserveIT

go

 

sp_helpfile

go

 

 

זה נותן לךף מידע על 2 הקבצים של אותו DB שאתה עובד עליו – ולא על כל השרת .

 

 

 

 

--- Modifying the Initial size of a database logfile to 12 MB, in this example

ALTER DATABASE Northwind MODIFY FILE

(NAME=Northwind_log , SIZE=12MB)

go

 

ALTER DATABASE ObserveIT MODIFY FILE

(NAME=ObserveIT_Log , SIZE=100MB)

go

 

תקלה__ תקלות__

MODIFY FILE failed. Specified size is less than or equal to current size.

 

 

 

 

 

‏05/06/2013 13:21:23

הבא גודל פיזי של ה DATABASE    

sp_spaceused__

 

 

 

 

 

use Agilepoint

go

 

-- Taken from sp_spaceused:

SELECT database_name = db_name()

    ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ' MB')

    ,'unallocated space' = ltrim(str((

                CASE

                    WHEN dbsize >= reservedpages

                        THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576

                    ELSE 0

                    END

                ), 15, 2) + ' MB')

FROM (

    SELECT dbsize = sum(convert(BIGINT, CASE

                    WHEN STATUS & 64 = 0

                        THEN size

                    ELSE 0

                    END))

        ,logsize = sum(convert(BIGINT, CASE

                    WHEN STATUS & 64 <> 0

                        THEN size

                    ELSE 0

                    END))

    FROM dbo.sysfiles

) AS files

,(

    SELECT reservedpages = sum(a.total_pages)

        ,usedpages = sum(a.used_pages)

        ,pages = sum(CASE

                WHEN it.internal_type IN (

                        202

                        ,204

                        ,211

                        ,212

                        ,213

                        ,214

                        ,215

                        ,216

                        )

                    THEN 0

                WHEN a.type <> 1

                    THEN a.used_pages

                WHEN p.index_id < 2

                    THEN a.data_pages

                ELSE 0

                END)

    FROM sys.partitions p

    INNER 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

) AS partitions

 

 

 

 

 

 

insert__ into__    insert into   מאיר אדר

insert INTO db929.[dbo].[ka_t_email] SELECT * FROM db929_meir.[dbo].[ka_t_email]

 
 
insert INTO db929.[dbo].[ka_t_for_amal] SELECT * FROM db929_meir.[dbo].[ka_t_for_amal]
 
 
insert INTO db929.[dbo].[ka_t_email_plus] SELECT * FROM db929_meir.[dbo].[ka_t_email_plus]
 

‏05/06/2013 14:29:41
 

כעת אם רוצים לדגום פר כל יום נתוני גודל של DB לתוך טבלה רלוונטית

 

use tempdb

go

 

insert INTO dbo.tempdb_size_trace

SELECT MYOLDTABLE.*

FROM

 

(

select @@SERVERNAME as server_n,

       CONVERT(VARCHAR,GETDATE(),110)as date_n,

       vvv.*

from 

(

-- Taken from sp_spaceused:

SELECT database_name = db_name()

    ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ' MB')

    ,'unallocated space' = ltrim(str((

                CASE

                    WHEN dbsize >= reservedpages

                        THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576

                    ELSE 0

                    END

                ), 15, 2) + ' MB')

FROM (

    SELECT dbsize = sum(convert(BIGINT, CASE

                    WHEN STATUS & 64 = 0

                        THEN size

                    ELSE 0

                    END))

        ,logsize = sum(convert(BIGINT, CASE

                    WHEN STATUS & 64 <> 0

                        THEN size

                    ELSE 0

                    END))

    FROM dbo.sysfiles

) AS files

,(

    SELECT reservedpages = sum(a.total_pages)

        ,usedpages = sum(a.used_pages)

        ,pages = sum(CASE

                WHEN it.internal_type IN (

                        202

                        ,204

                        ,211

                        ,212

                        ,213

                        ,214

                        ,215

                        ,216

                        )

                    THEN 0

                WHEN a.type <> 1

                    THEN a.used_pages

                WHEN p.index_id < 2

                    THEN a.data_pages

                ELSE 0

                END)

    FROM sys.partitions p

    INNER 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

) AS partitions

 

)vvv

 

)MYOLDTABLE

 

   

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

‏07/12/2015

Temp__  tempdb__  temp db

-- free space

SELECT

(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB],

SUM(unallocated_extent_page_count) AS [free pages],

 

(

-- Total Amount of Space (Free and Used)

SELECT SUM(size)*1.0/128 AS [size in MB]

FROM tempdb.sys.database_files

 

)as toatl_Free_and_Used

FROM sys.dm_db_file_space_usage;

 

 

-- Total Amount of Space (Free and Used)

SELECT SUM(size)*1.0/128 AS [toatl_Free_and_Used]

FROM tempdb.sys.database_files

 

USE TempDB
GO
EXEC sp_helpfile
GO
 

 

 

 

 

 

 

 

 

 

 

 

 

 
 
 
 
 
 
 
--get find__ column__ in DATABASE  חפש שדה עמודה
 
select object_name(id), name
from syscolumns
 
 
 
 
 

xml__

 

 
CREATE TABLE #WorkingTable
(Data XML)
 
INSERT INTO #WorkingTable
SELECT * FROM OPENROWSET (BULK 'c:\Sample.xml', SINGLE_BLOB) AS data
 
select * 
from #WorkingTable
 
/*  -- sample.xml
 
<root>
  <data parm1="hellow" parm2="word" />
</root>
 
*/
 
 
-- http://searchsqlserver.techtarget.com/tip/Processing-XML-files-with-SQL-Server-functions
 
/*
The OPENXML code necessary to read this data from the temporary table should 
look something like this:
*/
 
 
DECLARE @XML AS XML, @hDoc AS INT
SELECT  @XML = Data FROM #WorkingTable
 
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT *
FROM OPENXML(@hDoc, '/root/data', 1)
WITH (zab1 VARCHAR(5) '@parm1',
      zab2 VARCHAR(5) '@parm2')
 
EXEC sp_xml_removedocument @hDoc
 
 
 
 

 

 

 

 
 
 

cmd__  get all sql server in domain__  get list__  DOS__ CMD__
 

osql -L >a.txt
 
show__ edit__
type a.txt
 

 

 

 
 
 
 
 
    

i/o__v    cpu__ v  memory__  page per seconds  -TOP

 

-- orange and כתום --cpu for sql and for machine--top__   graph__  metric__
-- Get CPU__ usage__ Utilization History (SQL 2008 Only)
 ----  http://blogs.msdn.com/b/managingsql/archive/2010/06/28/server-activity-history-report-mdw-report-series-part-4.aspx
 
-- orange and כתום --cpu for sql and for machine--top
-- Get CPU__ usage__ Utilization History (SQL 2008 Only)
    DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info); 
 
    SELECT TOP(8) DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] ,
                   SystemIdle AS [System Idle Process], 
  SQLProcessUtilization AS [SQL Server Process CPU Utilization], 
                   100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization], 
                   DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] 
    FROM ( 
          SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, 
                record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') 
                AS [SystemIdle], 
                record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 
                'int') 
                AS [SQLProcessUtilization], [timestamp] 
          FROM ( 
                SELECT [timestamp], convert(xml, record) AS [record] 
                FROM sys.dm_os_ring_buffers 
                WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
                AND record LIKE '%<SystemHealth>%') AS x 
          ) AS y 
    ORDER BY record_id DESC;
 
 
 
 
 
-- שיפור
-- orange and כתום --cpu for sql and for machine--top__   ratio__ crm__
-- Get CPU__ usage__ Utilization History (SQL 2008 Only)
    DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info); 
 
    SELECT TOP(8) DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] ,
                  
  SQLProcessUtilization AS [SQL CPU Utilizate], 
                   100 - SystemIdle - SQLProcessUtilization AS [SYS CPU Utilizate], 
                    SystemIdle AS [System Idle Process],                    
                   DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] 
    FROM ( 
          SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, 
                record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') 
                AS [SystemIdle], 
                record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 
                'int') 
                AS [SQLProcessUtilization], [timestamp] 
          FROM ( 
                SELECT [timestamp], convert(xml, record) AS [record] 
                FROM sys.dm_os_ring_buffers 
                WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
                AND record LIKE '%<SystemHealth>%') AS x 
          ) AS y 
    ORDER BY record_id DESC;
 
--ratio CPU sys - sql 
 
 

 

 

--another one good 

 

SELECT top(20) F.*, CONVERT(VARCHAR,GETDATE(),105)+' '+CONVERT(VARCHAR(8),GETDATE(),108) as il_time_stamp
into #new_table
FROM
(
SELECT spid, 
       db_name(s.dbid)as db_name1, 
       cpu, 
       physical_io, 
       memusage AS memusage_pages, 
       --status, 
       text,
       loginame
FROM master.dbo.sysprocesses as s
cross apply ::fn_get_sql(s.sql_handle)
)F
ORDER BY F.cpu DESC
 
select * from #new_table
drop table #new_table
 

 

 

    
 
 
 

 

-- last time Connection__ Count__ users  last__  servername__  server name
use master
go
 
SELECT @@ServerName AS server
 ,NAME AS dbname
 ,COUNT(STATUS) AS number_of_connections
 ,GETDATE() AS timestamp
FROM sys.databases sd
LEFT JOIN sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1 AND 4
GROUP BY NAME
 
 

 

use db778
go
 
SELECT  db_name(n.database_id)  as db_name1,
        OBJECT_NAME(n.OBJECT_ID)as obj_name_1, 
        n.object_id,
        n.last_user_lookup, 
        n.last_user_scan, 
        n.last_user_seek, 
        n.last_user_update,
        N.*
FROM 
 
(
--- this code work fine !
SELECT OBJECT_NAME(OBJECT_ID) AS objectName, *
      
FROM master.sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'db778')
--AND OBJECT_ID=OBJECT_ID('test')
)N
 
 
 
 

 

 

 

 
 
 
 
 
 
 
 
 
 
 
 
 
----------------------------------------------------------------------------------
-- TOP__ cpu usage by db    graph__  metric__
 
select sss.[db_name], SUM( total_cpu_time ) as cpu_time
from 
 
(
select dbs.name as [db_name], 
       --cacheobjtype, 
       total_cpu_time 
       --,total_execution_count 
 from
    (select top 50
        sum(qs.total_worker_time) as total_cpu_time,  
        sum(qs.execution_count) as total_execution_count, 
        count(*) as  number_of_statements,  
        qs.plan_handle
    from  
        sys.dm_exec_query_stats qs 
    group by qs.plan_handle
    order by sum(qs.total_worker_time) desc
    ) a
inner join 
(SELECT plan_handle, pvt.dbid, cacheobjtype
FROM (
    SELECT plan_handle, epa.attribute, epa.value, cacheobjtype
    FROM sys.dm_exec_cached_plans 
        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
     /* WHERE cacheobjtype = 'Compiled Plan' AND objtype = 'adhoc' */) AS ecpa 
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("dbid", "sql_handle")) AS pvt
) b on a.plan_handle = b.plan_handle
inner join sys.databases dbs on dbid = dbs.database_id
)sss
group by sss.[db_name] 
ORDER BY cpu_time desc 
 
CPU - per database
 
 

 

 

 

 

 

 
 
---------------------------------------------------------------------------------    
    -- Get CPU Utilization History (SQL 2005 Only)
    DECLARE @ts_now bigint; 
    SELECT @ts_now = cpu_ticks / CONVERT(float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info 
 
    SELECT TOP(10) SQLProcessUtilization AS [SQL Server Process CPU Utilization], 
                   SystemIdle AS [System Idle Process], 
                   100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization], 
                   DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] 
    FROM ( 
          SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, 
                record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') 
                AS [SystemIdle], 
                record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 
                'int') 
                AS [SQLProcessUtilization], [timestamp] 
          FROM ( 
                SELECT [timestamp], CONVERT(xml, record) AS [record] 
                FROM sys.dm_os_ring_buffers 
                WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
                AND record LIKE '%<SystemHealth>%') AS x 
          ) AS y 
    ORDER BY record_id DESC;
 
 
---------------------------------------------------------
 
 
--memory__ usage__   top__    graph__  metric__
--these queries tell you which databases are consuming most memory in the buffer cache 
--and then you can drill down to which objects in that database are using the most memory, 
--that might help point you in the direction of the queries that use those objects
 
 
--AAA find out how big buffer pool is and determine percentage used by each database
 
DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value   FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'   AND counter_name = 'Total Pages';
;WITH src AS(   SELECT        database_id, db_buffer_pages = COUNT_BIG(*) 
FROM sys.dm_os_buffer_descriptors       --WHERE database_id BETWEEN 5 AND 32766       
GROUP BY database_id)SELECT   [db_name] = CASE [database_id] WHEN 32767        THEN 'Resource DB'        ELSE DB_NAME([database_id]) END,   db_buffer_pages,   db_buffer_MB = db_buffer_pages / 128,   db_buffer_percent = CONVERT(DECIMAL(6,3),        db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;
 
 
 
MEMORY - per database
 
 
 
--BBB then drill down into memory used by objects in database of your choice  - top__
 
USE db_with_most_memory;
 
WITH src AS(   SELECT       [Object] = o.name,       [Type] = o.type_desc,       [Index] = COALESCE(i.name, ''),       [Index_Type] = i.type_desc,       p.[object_id],       p.index_id,       au.allocation_unit_id   
FROM       sys.partitions AS p   INNER JOIN       sys.allocation_units AS au       ON p.hobt_id = au.container_id   INNER JOIN       sys.objects AS o       ON p.[object_id] = o.[object_id]   INNER JOIN       sys.indexes AS i       ON o.[object_id] = i.[object_id]       AND p.index_id = i.index_id   WHERE       au.[type] IN (1,2,3)       AND o.is_ms_shipped = 0)
SELECT   src.[Object],   src.[Type],   src.[Index],   src.Index_Type,   buffer_pages = COUNT_BIG(b.page_id),   buffer_mb = COUNT_BIG(b.page_id) / 128
FROM   src
INNER JOIN   sys.dm_os_buffer_descriptors AS b  
 ON src.allocation_unit_id = b.allocation_unit_id
WHERE   b.database_id = DB_ID()
GROUP BY   src.[Object],   src.[Type],   src.[Index],   src.Index_Type
ORDER BY   buffer_pages DESC;
 
 
 

 

 

 

 

 
 
 
 
 
 
---------------------
 
-- get list database name AND databaseid  DBID__
SELECT name as Database_Name, 
        database_id as Database_ID
FROM sys.databases;
 
----------------------
 
--INDEX__  INDEXES__  get indexes for table
 
select a.*
from
(
SELECT 
    I.name AS IndexName
    , T.name AS TableName
    , I.is_primary_key AS IsPrimaryKey
 
FROM sys.indexes AS I
    INNER JOIN sys.tables AS T
        ON ( I.object_id = T.object_id )
)a
where a.TableName like '%ktv_rechov_klali%'
 
---------------------------------------------------------------------------------------------
 
 
 
 
 
 
-- get last hour cpu__ usage--top__ -- יוסי  -- אנקונינה
 
select h.*
from
(
SELECT TOP 50
qs.last_execution_time, 
qs.total_worker_time/qs.execution_count as [Avg CPU Time],
SUBSTRING(qt.text,qs.statement_start_offset/2, 
(case when qs.statement_end_offset = -1 
then len(convert(nvarchar(max), qt.text)) * 2 
else qs.statement_end_offset end -qs.statement_start_offset)/2
)as query_text,
qt.dbid, 
dbname=db_name(qt.dbid)--,
/*
qt.objectid--, 
OBJECT_NAME(qt.objectid)as object_name1
*/
FROM sys.dm_exec_query_stats qs
     cross apply 
   sys.dm_exec_sql_text(qs.sql_handle) as qt
   WHERE   qs.last_execution_time > DATEADD(HOUR, -1, GETDATE())
ORDER BY [Avg CPU Time] DESC
) h
 
 

 

 

 

 

 
--top__  query -XML__ -:אנקונינה יוסי
--performance tuning
 
--top__  query -XML__ -:אנקונינה יוסי
--performance tuning
 
SELECT TOP 5
            total_worker_time/execution_count AS [Avg CPU Time],
            last_execution_time,
            Plan_handle,
            query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
WHERE   last_execution_time > DATEADD(HOUR, -1, GETDATE()) -- get last hour
--aavr:if you want to look for specific wors in XML option 1 
--and cast(query_plan as nvarchar(max)) like '%PreviewDateRange%'
 
--aavr:if you want to look for specific wors in XML option 2
and query_plan.exist('//*/text()[contains(upper-case(.),upper-case("PreviewDateRange"))]') = 1
 
ORDER BY total_worker_time/execution_count DESC;
GO
 
 
 
 
 

 

 

 

 
-- http://blog.sqlauthority.com/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/
-- pinal dave
use db636
go 
 
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1)as query,
 
qs.last_execution_time,
 
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time
 
 
 

 

 

 

 

 
-- get last hour cpu__ usage --top__
 
SELECT top 50 F.*
FROM
(
SELECT spid,
       s.last_batch as last_time, 
       db_name(s.dbid)as db_name1, 
       cpu, 
       physical_io, 
       memusage AS memusage_pages, --(aavr:this not good for mem usage)
       --status, 
       text
FROM master.dbo.sysprocesses as s
cross apply ::fn_get_sql(s.sql_handle)
)F
WHERE   f.last_time > DATEADD(HOUR, -1, GETDATE())
ORDER BY F.cpu DESC
 
-- select * FROM master.dbo.sysprocesses
 
 
 
 
 
 
 
-- get last hour IO__ i/o__  usage --top__
 
SELECT top 50 F.*
FROM
(
SELECT spid,
       s.last_batch as last_time, 
       db_name(s.dbid)as db_name1, 
       cpu, 
       physical_io, 
       memusage AS memusage_pages, --(aavr:this not good for mem usage)
       --status, 
       text
FROM master.dbo.sysprocesses as s
cross apply ::fn_get_sql(s.sql_handle)
)F
WHERE   f.last_time > DATEADD(HOUR, -1, GETDATE())
ORDER BY F.physical_io DESC
 
-- select * FROM master.dbo.sysprocesses
 
 
 

 

 

 

 

-- עבור ליאת שרת service desk 
 
SELECT top 100 F.*
FROM
(
SELECT spid,
       s.last_batch as last_time, 
       db_name(s.dbid)as db_name1, 
       cpu, 
       physical_io, 
       memusage AS memusage_pages, --(aavr:this not good for mem usage)
       --status, 
       text
FROM master.dbo.sysprocesses as s
cross apply ::fn_get_sql(s.sql_handle)
)F
WHERE   f.last_time > DATEADD(HOUR, -3, GETDATE())
and DATEPART(HOUR, last_time) between 12 and 13 
--ORDER BY F.cpu DESC, last_time asc
ORDER BY last_time asc

 

 
 
 
 
ssis__  2005 
https://gallery.technet.microsoft.com/scriptcenter/List-all-SSIS-packages-3b247394   packages__  package__
 
-- List all SSIS packages stored in msdb database. 
SELECT PCK.name AS PackageName 
      ,PCK.[description] AS [Description] 
      ,FLD.foldername AS FolderName 
      ,CASE PCK.packagetype 
            WHEN 0 THEN 'Default client' 
            WHEN 1 THEN 'I/O Wizard' 
            WHEN 2 THEN 'DTS Designer' 
            WHEN 3 THEN 'Replication' 
            WHEN 5 THEN 'SSIS Designer' 
            WHEN 6 THEN 'Maintenance Plan' 
            ELSE 'Unknown' END AS PackageTye 
      ,LG.name AS OwnerName 
      ,PCK.isencrypted AS IsEncrypted 
      ,PCK.createdate AS CreateDate 
      ,CONVERT(varchar(10), vermajor) 
       + '.' + CONVERT(varchar(10), verminor) 
       + '.' + CONVERT(varchar(10), verbuild) AS Version 
      ,PCK.vercomments AS VersionComment 
      ,DATALENGTH(PCK.packagedata) AS PackageSize 
FROM msdb.dbo.sysdtspackages90 AS PCK 
     INNER JOIN msdb.dbo.sysdtspackagefolders90 AS FLD 
         ON PCK.folderid = FLD.folderid 
     INNER JOIN sys.syslogins AS LG 
         ON PCK.ownersid = LG.sid 
ORDER BY PCK.name;
 

 

 

 
-- sql server 2000 get list dts packages dts__ 
 
 
SELECT DISTINCT name FROM sysdtspackages
 
-- you can also use 
--msdb..sp_enum_dtspackages
 
 
 

 

-- 2008 
select @@servername as instancename, PackageName,FolderName, PackageTye, version
   
 from
 
(SELECT PCK.name AS PackageName 
      ,PCK.[description] AS [Description] 
      ,FLD.foldername AS FolderName 
      ,CASE PCK.packagetype 
            WHEN 0 THEN 'Default client' 
            WHEN 1 THEN 'I/O Wizard' 
            WHEN 2 THEN 'DTS Designer' 
            WHEN 3 THEN 'Replication' 
            WHEN 5 THEN 'SSIS Designer' 
            WHEN 6 THEN 'Maintenance Plan' 
            ELSE 'Unknown' END AS PackageTye 
      ,LG.name AS OwnerName 
      ,PCK.isencrypted AS IsEncrypted 
      ,PCK.createdate AS CreateDate 
      ,CONVERT(varchar(10), vermajor) 
       + '.' + CONVERT(varchar(10), verminor) 
       + '.' + CONVERT(varchar(10), verbuild) AS Version 
      ,PCK.vercomments AS VersionComment 
      ,DATALENGTH(PCK.packagedata) AS PackageSize 
FROM msdb.dbo.sysssispackages AS PCK 
     LEFT JOIN msdb.dbo.sysssispackagefolders AS FLD 
         ON PCK.folderid = FLD.folderid 
     LEFT JOIN sys.syslogins AS LG 
         ON PCK.ownersid = LG.sid 
--ORDER BY PCK.name;
) hafi

 

 
 
-----------------------------------
grant__
 
--profiler--
Use master
Go
Grant Alter Trace to Login
 
-----------------------------
USE master
GO
GRANT EXECUTE ON xp_sqlagent_enum_jobs TO test;
 
 
use db_xx_name
GO
 
GRANT EXECUTE ON <procedurename> to <username>
grant execute on master.sys.xp_sendmail to [COMV\skravch]  
grant execute on master.sys.xp_sendmail to [ProjectPortal]
 
GO
 
 
GRANT SELECT ON [db928].[dbo].[movment] TO [dom001\MA05553]

 

 

use [a0_admin]
GO
----------------------------
 
GRANT INSERT ON [dbo].[GET_INFO_FRM_UC4_TBL] TO [db648_p]
GO
GRANT UPDATE ON [dbo].[GET_INFO_FRM_UC4_TBL] TO [db648_p]
GO
GRANT DELETE ON [dbo].[GET_INFO_FRM_UC4_TBL] TO [db648_p]
GO
 
GRANT SELECT ON [dbo].[GET_INFO_FRM_UC4_TBL] TO [db648_p]
GO
 

 

 

 

 

 

 

 

 
 
 
SELECT  OBJECT_NAME (referencing_id) AS referencing_object, referenced_database_name, 
     referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL
      AND is_ambiguous = 0; 
 
 
 
 
SELECT *
FROM information_schema.routines ISR
WHERE CHARINDEX('dbo.ktv_mikud_new', ISR.ROUTINE_DEFINITION) > 0
 
 
 
 
------------------------------
 
 
 
sql
 
--לעריכה:חפש בדף למעלה את המילה :עריכה
 
doc= ryn__sql__quick_מהיר.txt
 
---1------------------------------------------------------------------------
--CASE__  can be used within SELECT statment.
CASE [ expression ]
 
   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n
 
   ELSE result
 
END
 
 
--IF__  can't be used within SELECT statment.
IF condition THEN
   sequence_of_statements1
ELSE
   sequence_of_statements2
END IF;
 
 
--oracle: = the same
 
 
---2------------------------------------------------------------------------
function__
 
CREATE FUNCTION aaa_fun () RETURNS int
AS
BEGIN
declare @aaa int = 0 ;
RETURN @aaa;
 
END
 
-- select reayon.dbo.aaa_fun ()
 
 
 
CREATE OR REPLACE FUNCTION lsh_number (p_xxx number )
return number
is
    v_nisuy number;   
begin
    v_nisuy:=p_xxx+1;
    return v_nisuy;
end;
 
 
*****
--oracle - plsql:
CREATE OR REPLACE FUNCTION zabi RETURN int /*number*/
as
 
 
BEGIN
   return 7;
END zabi;
 
 
 
CREATE FUNCTION lsh_number (p_xxx number )
return number
is
    v_nisuy number;   
begin
    v_nisuy:=p_xxx+1;
    return v_nisuy;
end;
 
 
 
 
---3--------------------------------------------------------------------------
declare__
 
DECLARE 
@aaa int ;
 
BEGIN 
 
set @aaa=9;
PRINT @aaa;
 
END  
 
***********************
--oracle - plsql:
 
DECLARE 
 a number(1); 
 
 
 begin
    a := 7;
 end;
 
 
---4--------------------------------------------------------------------------
RowNum__
 
SELECT TOP 3
ROW_NUMBER() OVER(ORDER BY Quantity DESC) AS RowNum
,Quantity
FROM dbo.sales_b
 
 
RowNum               Quantity
-------------------- -----------
1                    88
2                    84
3                    68
 
(3 row(s) affected)
 
 
*****
--oracle - plsql:
 
select rownum  ,g.* 
from customers g
 
 
---5---------------------------------------------------------------------------
cte__
 
with aaa_cte(customerid)
as
(
 SELECT customerid  
 FROM customers
 )
 select * from aaa_cte
 
 
*****
--oracle - plsql:
 
with cte 
as 
 SELECT *
 FROM customers
)
select * from cte 
 
 
 
---6---------------------------------------------------------------------------
group by - go to the full file .
 
 
SELECT column_name, 
       aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;
 
 
--find duplicated  duplicate__  __דבל__  כפילויות
select Subcategory, COUNT(*) as a 
from dbo.sales_b
group by Subcategory
HAVING      (COUNT(*) > 1)
 
 
 
--find duplicated  duplicate__  __דבל__  כפילויות
select [emp_tehudat_zehut], [mng_tehudat_zehut] , COUNT(*) as a 
from [orgchart_t_core_data_sql09]
group by [emp_tehudat_zehut], [mng_tehudat_zehut]
HAVING      (COUNT(*) > 1)
 
 
 
---7---------------------------------------------------------------------------
 
--cats__ table--
select * into zabi
from dbo.customers
 
CTAS__  \\\ create table as select   \\\ לבן        לבקן__   \\\  cats__    הלבן__
http://www.sqlservercentral.com/Forums/Topic468306-145-1.aspx#bm468470 
 
--מבנה בלבד-- 
 
 
 
SELECT *
INTO MYNEWTABLE
FROM MYOLDTABLE
WHERE 0=1  -- פה התנאי לא יתקיים ולכןיביא רק מבנה בלי הנתונים 
 
מבנה +נתונים 
 
Select *
INTO NewTable
from MyOldTable
--Where 0=1 – פה נוטרל התנאי ולכן זה מביא גם מבנה וגם נתונים
 
 
SELECT *
INTO [a0_admin].[dbo].oragen2_out_t
FROM [a0_admin].[dbo].[oragen2_out_v]
 
 
 
 
 
------------------------------
*****
--oracle - plsql:
 
create table zabi_2
as 
 
SELECT *
FROM customers
 
 
select dateformat from sys.syslanguages   where name = @@LANGUAGE 
select GETDATE()
 
 
---8---------------------------------------------------------------------------
CREATE TABLE emp  -- create__
(
empId    int NOT NULL,
emp_Name varchar(15) NULL,
emp_sal  int NULL
 
 
 
insert into emp  
values (1, 'ram',10000)
 
insert into emp 
values (2, 'ramesh',11000)
 
insert into emp 
values (3, 'manish',12000)
 
 
 
 
*************
 
 
CREATE TABLE tbl1
(
 
emp_Name varchar(15) NULL,
sales    int NULL
 
-----
 
insert into tbl1  
values ('xxx',1000)
 
insert into tbl1 
values ('yyy',2000)
 
insert into tbl1 
values ('zzz',3000)
 
insert into tbl1 
values ('www',4000)
 
-----
select * from tbl1
 
 
 
------------------------------------------------------------------------------
loop__  לולאה
 
 
DECLARE @i int = 0
WHILE @i < 10 BEGIN
    SET @i = @i + 1
    print @i
END
 
 
*****
--oracle - plsql:
 
DECLARE
   a number(2) := 1;
BEGIN
   WHILE a < 11 LOOP
      dbms_output.put_line('value of a: ' || a);
      a := a + 1;
   END LOOP;
END;
 
------------------------------------------------------------------------------
select into__
 
tsql :
http://www.blackwasp.co.uk/SQLSelectAssign.aspx
 
DECLARE @TotalExperience INT
SELECT @TotalExperience = sum(Quantity) FROM dbo.sales_b
PRINT @TotalExperience
 
 
plsql:
select column into v_column from my_table where ...;
 
-------------------------------------------------------------------------------
 
select [SalesDate], [Subcategory] from [dbo].[sales_b]
group by [SalesDate], [Subcategory]
having [SalesDate] between '2009-01-05' and  '2009-01-05'
 
 
-------------------------------------------------

 

 
NET USER__  FIND__  CHECK__ IF __  does the user exist__ ? (in domain)
 
NET USER 000_prod_scome_siem /DOMAIN
 
 
--login to server
create login [dom001\MA05553] from windows
 
--database user 
use db928
go 
create user [dom001\MA05553]

 

get list of : members of an Active Directory group 

NET GROUP  "dba"  /DOMAIN

 

 

 

 

 

 

 
 
size__  Sum all databases size ( mdf + ldf )  total__  all__    יוסי אנקונינה
 

Get overall sum of all databases size in a SQL Server

 
 
 
select sum(a.Size_MBs)/1024+1 as size_GB
from 
(
SELECT d.name,
ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.name
--ORDER BY d.name
)a
 
-------
SELECT @@servername as server_name, 
CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) As UsedSpace_GB 
FROM master.sys.master_files
 
 
 

 

05/09/2019

-- לא ברור צריך לבדוק שוב 

SELECT CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) As UsedSpace_GB 
FROM master.sys.master_files
where [name] like '%dbtest1%'
-- select * from master.sys.master_files   where master.sys.master_files.name like '%dbtest1%'

 

select CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) As UsedSpace_GB  from sys.sysaltfiles

 

 
 select [dbid],[name], [filename], size * 8.00 / 1024.00 / 1024.00 As UsedSpace_GB  from sys.sysaltfiles
 where [name] like '%temp%'

 

 

09/11/2016

desc__

 
sp_help tablename in sql server
 
desc tablename in oracle

 

 
   -- tsql: Convert exponential__ EX__ to number mike__ token__ 
 
SELECT CONVERT(numeric(16,0), CAST([credit_card_number] AS FLOAT))
 FROM [MIKE2].[dbo].[t_db117]
 -- http://stackoverflow.com/questions/29149910/convert-exponential-to-number-in-sql
 

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

create FUNCTION [dbo].[convert_EX_TO_DEC_fun] (@p_text as varchar (1000) )
returns varchar (1000)
-- by aavr
as
 
begin
       declare @v_ret varchar (1000);
       set @v_ret= CONVERT(numeric(16,0), CAST(@p_text AS FLOAT));
       return @v_ret;
end
 
 
-- select     dbo.convert_EX_TO_DEC_fun ('5.33E+15') as ddd
 
GO
 

 

 

 

 

 

 

 

בניית אתר בחינם

במידה ואתה רוצה לבנות אתר בחינם, תוכל להתנסות באחת מהמערכות שלנו לבניית אתרים בחינם, בחר עיצוב והתחל לבנות אתר לעסק שלך
 
מצד שמאל תוכלו לראות מגוון עיצובים שתוכלו לבחור לאתר הסלולרי החדש שתבנו בזופ בדקות הקרובות
 
לאחר שתכנסו למערכת הניהול תחשפו לאפשרויות רבות לניהול התוכן באתר, תקבלו קוד QR ייחודי לאתר שלכם, תוכלו לנהל דפי תוכן, מפות, טפסים ועוד..
 
לחץ כאן לבניית אתר בחינם

החבילות של זופ

זופ מציעה שירותי פרימיום, לניהול אתר המובייל שלך.  שימוש בדומיין שלך, אחסון ברמה גבוהה , קידום באינדקס אתרים שלנו ועוד...

 

 
לחץ כאן לצפייה בחבילות של זופ