PROBLEM Mam bazy, które chcę „zesnapshotować”. Ale żeby stworzyć snapshota muszę sprawdzić na jakich plikach danych jest ona oparta, użyć ich logicznych nazw i zmapować ich kopię z plikami (czyli tam, gdzie będą odkładane dane przed zmianą na oryginale). Management studio nie dostarcza żadnego gotowego rozwiązania, więc czas przygotować swoje.Czytaj dalej / Read more
Kategoria: Przydatne skrypty
Dział z mniejszymi/większymi skryptami przydatnymi w codziennej pracy z SQL Server
tempdb – sprawdzenie rozmiarów po sesji
SELECT sys.dm_exec_sessions.session_id AS [SESSION ID] , DB_NAME(su.database_id) AS [DATABASE Name] , host_name AS [System Name] , program_name AS [Program Name] , login_name AS [USER Name] , status , cpu_time AS [CPU TIME (in milisec)] , total_scheduled_time AS [Total Scheduled TIME (in milisec)] , total_elapsed_time AS [Elapsed TIME (in milisec)] ,Czytaj dalej / Read more
sp_who3
SELECT SPID = er.session_id ,Status = ses.status ,[Login] = ses.login_name ,Host = ses.host_name ,BlkBy = er.blocking_session_id ,DBName = DB_Name(er.database_id) ,CommandType = er.command ,SQLStatement = SUBSTRING ( qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2 ELSE er.statement_end_offset END – er.statement_start_offset)/2 ) ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ’.’ +Czytaj dalej / Read more
Session statistics – statystyki sesji cpu memory itp
SELECT sys.dm_exec_sessions.session_id AS [SESSION ID] , DB_NAME(su.database_id) AS [DATABASE Name] , host_name AS [System Name] , program_name AS [Program Name] , login_name AS [USER Name] , status , cpu_time AS [CPU TIME (in milisec)] , total_scheduled_time AS [Total Scheduled TIME (in milisec)] , total_elapsed_time AS [Elapsed TIME (in milisec)] ,Czytaj dalej / Read more
Historia odtworzeń baz (restore history)
SELECT r.destination_database_name , r.restore_Date, r.user_name , b.database_creation_date , b.backup_start_date , b.backup_finish_date , b.compatibility_level , b.database_name , b.server_name , b.machine_name , b.recovery_model, r.destination_database_name FROM [msdb].[dbo].[restorehistory] r JOIN [msdb].[dbo].backupset b ON r.backup_set_id = b.backup_set_id ORDER BY r.restore_date DESC
Statystyki filestats – io na serwerze
USE master go SET NOCOUNT ON DECLARE @crDate DateTime; DECLARE @hours DECIMAL(18,3), @Days int; DECLARE @FinalHours int, @FinalMinutes int, @FinalSeconds int, @total_seconds int; — Determine uptime by checking Tempdb creation datetime: SELECT top 1 @crdate=create_date FROM sys.databases WHERE NAME=’tempdb’ SELECT @hours = DATEDIFF(ss,@crDate,GETDATE())/CAST(60 AS Decimal)/CAST(60 AS Decimal); PRINT 'SQL ServerCzytaj dalej / Read more
Alternatywa sp_lock – blokady na bazie
— List all Locks of the Current Database SELECT TL.resource_type AS ResType ,TL.resource_description AS ResDescr ,TL.request_mode AS ReqMode ,TL.request_type AS ReqType ,TL.request_status AS ReqStatus ,TL.request_owner_type AS ReqOwnerType ,TAT.[name] AS TransName ,TAT.transaction_begin_time AS TransBegin ,DATEDIFF(ss, TAT.transaction_begin_time, GETDATE()) AS TransDura ,ES.session_id AS S_Id ,ES.login_name AS LoginName ,COALESCE(OBJ.name, PAROBJ.name) AS ObjectName ,PARIDX.name ASCzytaj dalej / Read more
Statystyki WAIT TYPES
Author: Paul Randal Original post: http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/ WITH [Waits] AS (SELECT [wait_type], [wait_time_ms] / 1000.0 AS [WaitS], ([wait_time_ms] – [signal_wait_time_ms]) / 1000.0 AS [ResourceS], [signal_wait_time_ms] / 1000.0 AS [SignalS], [waiting_tasks_count] AS [WaitCount], 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum] FROM sys.dm_os_wait_stats WHERE [wait_type]Czytaj dalej / Read more
Historia backupów
Wersja compact: SELECT CONVERT(CHAR(100), SERVERPROPERTY(’Servername’)) AS Server, msdb.dbo.backupset.database_name, MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb..backupset.type = 'D’ GROUP BY msdb.dbo.backupset.database_name ORDER BY msdb.dbo.backupset.database_name Wersja extended: SELECT CONVERT(CHAR(100), SERVERPROPERTY(’Servername’)) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date, CASE msdb..backupset.type WHEN 'D’ THEN 'Database’ WHEN 'L’ THENCzytaj dalej / Read more
Wykorzystanie pamięci przez bazy i obiekty – memory consumption
–sprawdzanie baz 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] WHENCzytaj dalej / Read more