This script will allow you to specify a linked server name, a remote database on that linked server, and a local database name in which to copy all the tables from on server onto another. [cc lang=”sql”] CREATE PROCEDURE [dba].[pr_refresh_create_tables] @linked_server varchar(100), @source_db varchar(100), @target_db varchar(100), @debug_mode int = 0 AS — dba.pr_refresh_create_tables ‘10.10.60.58’, ‘yourdbname’, ‘targetdb’, 0 — SELECT * FROM admindb.dbo.refresh_table_columns BEGIN DECLARE @SQL nvarchar(max); DECLARE @SQLColumnList nvarchar(max); DECLARE @SQLSelectColumnList nvarchar(max); DECLARE @CRLF nvarchar(2); DECLARE @Tab nvarchar(1); DECLARE @RecordKeyName nvarchar(50); DECLARE @RecordKeyDataType nvarchar(50); DECLARE @TableID int; DECLARE @TableCount int; DECLARE @source varchar(max) = ‘[‘ + @linked_server + ‘].[‘ + @source_db + ‘]’ DECLARE @TableName varchar(255); DECLARE @TableList TABLE ( TableID int IDENTITY(1, 1) NOT NULL, TableName varchar(255) ); IF OBJECT_ID(N’admindb.dbo.refresh_table_columns_tmp’) IS NOT NULL DROP TABLE admindb.dbo.refresh_table_columns_tmp IF OBJECT_ID(N’admindb.dbo.refresh_table_columns’) IS NOT NULL DROP TABLE admindb.dbo.refresh_table_columns IF OBJECT_ID(N’admindb.tmp.refresh_create_tables’) IS NOT NULL DROP TABLE admindb.tmp.refresh_create_tables CREATE TABLE admindb.dbo.refresh_table_columns_tmp ( RowID int NOT NULL, TableName varchar(255), ColumnName varchar(255), IsIdentity bit, DataType varchar(50), IsDataTypeCast bit, IsNullable bit, IsLast bit ); CREATE TABLE admindb.tmp.refresh_create_tables ( ID INT identity(1,1), table_name varchar(255), create_table varchar(max) ) SET NOCOUNT ON; SET @CRLF = CHAR(13) + CHAR(10); SET @Tab = CHAR(9); SET @sql = ‘INSERT INTO admindb.dbo.refresh_table_columns_tmp ( RowID, TableName, ColumnName, IsIdentity, DataType, IsDataTypeCast, IsNullable ) SELECT RowID = ROW_NUMBER() OVER(PARTITION BY t.TableName ORDER BY t.IsIdentity DESC, t.ORDINAL_POSITION), TableName, ColumnName, IsIdentity, DataType, IsDataTypeCast, IsNullable FROM ( SELECT DISTINCT TableName = c.TABLE_NAME, ColumnName = c.COLUMN_NAME, IsIdentity = sc.is_identity, DataType = ”” + CASE WHEN LOWER(c.DATA_TYPE) IN (”numeric”, ”decimal”, ”real”) THEN LOWER(c.DATA_TYPE) […]
Continue reading ...
This is a useful script if you want to turn a specific list of jobs that are currently enable to a disabled status and vice versa. Just specify the list of job names in the VALUES section below: [cc lang=”sql”] use msdb go IF OBJECT_ID(N’tempdb..#jobs’) IS NOT NULL BEGIN DROP TABLE #jobs END CREATE TABLE #jobs ( job_name nvarchar(max), ) INSERT INTO #jobs(job_name) VALUES (‘Job Name 1 here’), (‘Job Name 2 here’), (‘Run index selection Job’) IF OBJECT_ID(N’tempdb..#job_status’) IS NOT NULL BEGIN DROP TABLE #job_status END SELECT ID = identity(int, 1,1), [job_name], enabled INTO #job_status FROM [msdb].[dbo].[sysjobs] AS [sJOB] JOIN #jobs j ON j.job_name = sJOB.name DECLARE @job_name nvarchar(max) = ”; DECLARE @enabled int; DECLARE @iter int = (SELECT MAX(ID) FROM #job_status); WHILE @iter > 0 BEGIN SET @job_name = ” SELECT @job_name = job_name, @enabled = CASE WHEN [enabled] = 0 THEN 1 ELSE 0 END FROM #job_status WHERE ID = @iter; EXEC msdb.dbo.sp_update_job @job_name = @job_name, @enabled = @enabled ; PRINT cast(@job_name as varchar) + ‘:’ + cast(@enabled as varchar) SET @iter -= 1; END [/cc]
Continue reading ...
This is a great DMV if you don’t want to leave SSMS to see the CPU usage or if you don’t have access. What’s good about this method is it also shows you the history of the cpu usage. [cc lang=”sql”] declare @ts_now bigint select @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) from sys.dm_os_sys_info; select record_id, dateadd(ms, -1 * (@ts_now – [timestamp]), GetDate()) as EventTime, SQLProcessUtilization, SystemIdle, 100 – SystemIdle – SQLProcessUtilization as OtherProcessUtilization 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 ‘%%’) as x ) as y order by record_id desc [/cc]
Continue reading ...
The SQL Server Agent by default only keeps 1000 rows of history for all jobs. There is also a limitation for each job to keep only 100 rows each. These rows do not only include the a row for the entire job, but the rows for each step in the job also. These rows can quickly get filled leaving you in the dark when you need to troubleshoot what had happened in a job. To change these values, you need to have sysadmin access to the SQL Server Agent. Within the object explorer, right click on the SQL Server Agent node, and choose properties. A new pop-up window will appear displaying the SQL Server Agent properties. Select the History node and the history settings are displayed: Here you have the option to not limit the agent job history, by unchecking the corresponding box. You can also choose the maximum # of rows to store for all jobs, and an option to limit each job. The last option is to Remove history that’s older than a specified time period. This would be recommended if you do not limit the history log size, since you will not be pruning any records otherwise.
Continue reading ...
This command shows the last statement executed for a particular SPID. This statement is mostly used for troubleshooting purposes to determine the exact command a particular SPID is running. You must be a member of the sys admin fixed server role, or have VIEW SERVER STATE permission (if SQL 2005+). As for a real world use, I will mostly use this command after using sp_who2 to find out which SPID is taking a lot of resources. To execute, simply replace the SPID 55 below with the one you want to spy on. [cc lang=”sql”] DBCC INPUTBUFFER(55) [/cc] And here are the results: The results you are looking for are displayed in the third column above. This shows the last statement that has been or is currently being executed by the SPID. Another alternative to the DBCC INPUTBUFFER is to use the dm_exec_requests DMV. This query will show the currently executing statement for a SPID: [cc lang=”sql”] SELECT 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 ) FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt WHERE er.session_id = 54 [/cc] This DMV will only work on SQL 2005+ and you also need the VIEW SERVER STATE permission as a minimum. The one advantage DBCC INPUTBUFFER has over this, is the DMV will only show the currently executing request and not the previously executed request so you need to be a little quicker to catch it. The above DMV […]
Continue reading ...
