DECLARE @MonthEndInd bit
DECLARE @MonthEnd char(8)
DECLARE @Today char(8)
SELECT @MonthEnd = CONVERT(varchar , DateAdd(Month, DateDiff(Month, 0, Dateadd(month, 1, getdate())), 0) -1 , 112)
SELECT @Today = CONVERT(varchar,GETDATE(),112)
IF @Today = @MonthEnd
BEGIN
SELECT @MonthEndInd = 1
END
ELSE
SELECT @MonthEndInd = 0
Sunday, May 6, 2007
Monday, April 9, 2007
SQL Server Agent: Disable and enable all jobs
CREATE PROC prc_DBA_job_Enable_Disable @penable bit
AS
BEGIN
/****HeaderStart
Author: Kevin Foreman
Date: 10-04-2007
Description:
Enable/disable all jobs on a server based on current enable status
Places all enabled job names into a table in DBAFunctions and loops
through to disable them. Loops through to enable again and then clears table.
Designed to be used twice as a wrapper around a process/task that reqiures
jobs disabled.
Starts auto-start jobs when re-enabling them.
-- CREATE TABLE [tbl_DBA_job_Enable_Disable] (
-- [job_id] [uniqueidentifier] NULL ,
-- [name] [sysname] NOT NULL ,
-- [job_enabled] [tinyint] NULL ,
-- [freq_type] [int] NULL ,
-- [schedule_enabled] [tinyint] NULL ,
-- [last_updated] [datetime] NULL ,
-- [updated_by] [sysname] NOT NULL
-- )
Input: @penable 1 = enable jobs / 0 = disable
Example:
EXEC DBAFunctions.dbo.prc_DBA_job_Enable_Disable @penable = 0
*** [do something] ***
EXEC DBAFunctions.dbo.prc_DBA_job_Enable_Disable @penable = 1
Health Warning: NO ERROR TRAPPING
Version: SQL2000.01.00P
Maintained:
HeaderEnd****/
SET NOCOUNT ON
DECLARE @command TABLE (Command nvarchar(1000) , Done bit)
DECLARE @sql nvarchar(1000)
DECLARE @s nvarchar(1000)
--cleardown and repopulate the holding table if we are disabling jobs
IF @penable = 0
BEGIN
SELECT @s = 'DISABLING jobs on server: ' + QUOTENAME(@@SERVERNAME) + ' at: ' + QUOTENAME(GETDATE())
SELECT @s
DELETE [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
--get the name and id of enabled jobs
INSERT [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
(job_id,name,job_enabled,freq_type,schedule_enabled,last_updated,updated_by)
SELECT SJ.job_id,SJ.name, SJ.enabled,SJS.freq_type,SJS.enabled, GETDATE(), SYSTEM_USER
FROM msdb.dbo.sysjobs AS SJ
LEFT OUTER JOIN msdb.dbo.sysjobschedules AS SJS
ON SJ.job_id = SJS.job_id
WHERE SJ.enabled = 1
SELECT DISTINCT name FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
INSERT @command
SELECT DISTINCT 'EXEC msdb.dbo.sp_update_job @job_name = ' + QUOTENAME(name) + ' , @enabled = 0' AS Command , 0 AS Done
FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
END
ELSE
--enable jobs
BEGIN
SELECT @s = 'ENABLING jobs on server: ' + QUOTENAME(@@SERVERNAME) + ' at: ' + QUOTENAME(GETDATE())
SELECT @s
SELECT name FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
INSERT @command
SELECT 'EXEC msdb.dbo.sp_update_job @job_name = ' + QUOTENAME(name) + ' , @enabled = 1' AS Command , 0 AS Done
FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
END
WHILE EXISTS (SELECT Command FROM @command WHERE Done = 0)
BEGIN
SELECT @sql = (SELECT TOP 1 Command FROM @command WHERE Done = 0)
SELECT @sql
--EXEC @sql
UPDATE @command SET Done = 1 WHERE Command = @sql
END
IF @penable = 1
BEGIN
--restart jobs that auto-start with SQL Server Agent
IF EXISTS (SELECT * FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
WHERE freq_type = 64 AND schedule_enabled = 1)
BEGIN
SELECT @s = 'STARTING auto-start jobs on server: ' + QUOTENAME(@@SERVERNAME) + ' at: ' + QUOTENAME(GETDATE())
SELECT @s
DELETE @command
INSERT @command
SELECT DISTINCT 'EXEC msdb.dbo.sp_start_job @job_name = ' + QUOTENAME(name) AS Command , 0 AS Done
FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
WHERE freq_type = 64
AND schedule_enabled = 1
WHILE EXISTS (SELECT Command FROM @command WHERE Done = 0)
BEGIN
SELECT @sql = (SELECT TOP 1 Command FROM @command WHERE Done = 0)
SELECT @sql
--EXEC @sql
WAITFOR DELAY '000:00:05'
UPDATE @command SET Done = 1 WHERE Command = @sql
END
END
--cleardown table
DELETE [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
END
END
AS
BEGIN
/****HeaderStart
Author: Kevin Foreman
Date: 10-04-2007
Description:
Enable/disable all jobs on a server based on current enable status
Places all enabled job names into a table in DBAFunctions and loops
through to disable them. Loops through to enable again and then clears table.
Designed to be used twice as a wrapper around a process/task that reqiures
jobs disabled.
Starts auto-start jobs when re-enabling them.
-- CREATE TABLE [tbl_DBA_job_Enable_Disable] (
-- [job_id] [uniqueidentifier] NULL ,
-- [name] [sysname] NOT NULL ,
-- [job_enabled] [tinyint] NULL ,
-- [freq_type] [int] NULL ,
-- [schedule_enabled] [tinyint] NULL ,
-- [last_updated] [datetime] NULL ,
-- [updated_by] [sysname] NOT NULL
-- )
Input: @penable 1 = enable jobs / 0 = disable
Example:
EXEC DBAFunctions.dbo.prc_DBA_job_Enable_Disable @penable = 0
*** [do something] ***
EXEC DBAFunctions.dbo.prc_DBA_job_Enable_Disable @penable = 1
Health Warning: NO ERROR TRAPPING
Version: SQL2000.01.00P
Maintained:
HeaderEnd****/
SET NOCOUNT ON
DECLARE @command TABLE (Command nvarchar(1000) , Done bit)
DECLARE @sql nvarchar(1000)
DECLARE @s nvarchar(1000)
--cleardown and repopulate the holding table if we are disabling jobs
IF @penable = 0
BEGIN
SELECT @s = 'DISABLING jobs on server: ' + QUOTENAME(@@SERVERNAME) + ' at: ' + QUOTENAME(GETDATE())
SELECT @s
DELETE [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
--get the name and id of enabled jobs
INSERT [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
(job_id,name,job_enabled,freq_type,schedule_enabled,last_updated,updated_by)
SELECT SJ.job_id,SJ.name, SJ.enabled,SJS.freq_type,SJS.enabled, GETDATE(), SYSTEM_USER
FROM msdb.dbo.sysjobs AS SJ
LEFT OUTER JOIN msdb.dbo.sysjobschedules AS SJS
ON SJ.job_id = SJS.job_id
WHERE SJ.enabled = 1
SELECT DISTINCT name FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
INSERT @command
SELECT DISTINCT 'EXEC msdb.dbo.sp_update_job @job_name = ' + QUOTENAME(name) + ' , @enabled = 0' AS Command , 0 AS Done
FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
END
ELSE
--enable jobs
BEGIN
SELECT @s = 'ENABLING jobs on server: ' + QUOTENAME(@@SERVERNAME) + ' at: ' + QUOTENAME(GETDATE())
SELECT @s
SELECT name FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
INSERT @command
SELECT 'EXEC msdb.dbo.sp_update_job @job_name = ' + QUOTENAME(name) + ' , @enabled = 1' AS Command , 0 AS Done
FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
END
WHILE EXISTS (SELECT Command FROM @command WHERE Done = 0)
BEGIN
SELECT @sql = (SELECT TOP 1 Command FROM @command WHERE Done = 0)
SELECT @sql
--EXEC @sql
UPDATE @command SET Done = 1 WHERE Command = @sql
END
IF @penable = 1
BEGIN
--restart jobs that auto-start with SQL Server Agent
IF EXISTS (SELECT * FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
WHERE freq_type = 64 AND schedule_enabled = 1)
BEGIN
SELECT @s = 'STARTING auto-start jobs on server: ' + QUOTENAME(@@SERVERNAME) + ' at: ' + QUOTENAME(GETDATE())
SELECT @s
DELETE @command
INSERT @command
SELECT DISTINCT 'EXEC msdb.dbo.sp_start_job @job_name = ' + QUOTENAME(name) AS Command , 0 AS Done
FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
WHERE freq_type = 64
AND schedule_enabled = 1
WHILE EXISTS (SELECT Command FROM @command WHERE Done = 0)
BEGIN
SELECT @sql = (SELECT TOP 1 Command FROM @command WHERE Done = 0)
SELECT @sql
--EXEC @sql
WAITFOR DELAY '000:00:05'
UPDATE @command SET Done = 1 WHERE Command = @sql
END
END
--cleardown table
DELETE [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
END
END
Wednesday, March 7, 2007
TSQL: Restore commands for al DBs
Generate restore commands for all user databases on a server. To replace curent file locations with a new file location you can strip out the filenames and feed in a paramer for the new data/log folder. Or edit/replace the output.
SELECT SUBSTRING
( 'filename',
LEN('filename') - CHARINDEX('\',REVERSE('filename')) + 2 ,
CHARINDEX('\',REVERSE('filename'))
)
/****CommentStart
Author : Kevin Foreman
Date: 21-11-2006
Description:
Generates restore copmmands for all databases on a server
where dbid > 4
Uses WITH MOVE, STATS = 10 and REPLACE
char(13) used to format it nicely if exec in text
Maintained:
CommentEnd****/
SET NOCOUNT ON
-- setup varaibles
DECLARE
@db_name sysname ,
@sql nvarchar(2000) ,
@aCur_db_name sysname ,
@bCur_name varchar(200),
@bCur_filename varchar(200)
DECLARE
@Database_Name TABLE
(
name sysname,
Done bit DEFAULT 0
)
CREATE TABLE
#Database_Files
(
name sysname ,
fileid int,
filename varchar(2000),
filegroup varchar(100) ,
size varchar(32) ,
maxsize varchar(32) ,
growth varchar(32) ,
usage varchar(32)
)
CREATE TABLE
#Database_Files_Summary
(
database_name sysname NULL,
name sysname ,
fileid int,
filename varchar(2000),
)
--get user database names
INSERT
@Database_Name
(Name)
SELECT
sd.name AS 'Name'
FROM
master.dbo.sysdatabases AS sd
WHERE
sd.dbid > 4
-- get user database file info
WHILE EXISTS (SELECT * FROM @Database_Name WHERE Done = 0)
BEGIN
SELECT @db_name =
(SELECT TOP 1 dn.name
FROM @Database_name as dn
WHERE Done = 0)
SELECT @sql = @db_name + '.dbo.sp_helpfile'
INSERT #Database_Files
EXEC sp_executesql @sql
INSERT #Database_Files_Summary
(database_name,name,fileid,filename)
SELECT
@db_name,name,fileid,filename FROM #Database_Files
DELETE FROM #Database_Files
UPDATE @Database_name
SET Done = 1
WHERE name = @db_name
END
--cursor through database names and then files to generate restore commands
--database name cursor
DECLARE aCur CURSOR
FOR SELECT name FROM @Database_Name ORDER BY name ASC
OPEN aCur
FETCH NEXT FROM aCur INTO @aCur_db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'RESTORE DATABASE ''' + LTRIM(RTRIM(@aCur_db_name)) + ''' FROM DISK = ''[filename]''' + char(13) + 'WITH ' + char(13)
--database file cursor
DECLARE bCur CURSOR
FOR SELECT name , filename FROM #Database_Files_Summary WHERE database_name = @aCur_db_name ORDER BY fileid ASC
OPEN bCur
FETCH NEXT FROM bCur INTO @bCur_name , @bCur_filename
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM bCur INTO @bCur_name , @bCur_filename
SELECT @sql = @sql + ' MOVE ''' + LTRIM(RTRIM(@bCur_name)) + ''' TO ''' + LTRIM(RTRIM(@bCur_filename)) + ''' ,' + char(13)
END
CLOSE bCur
DEALLOCATE bCur
FETCH NEXT FROM aCur INTO @aCur_db_name
SELECT @sql = @sql + ' STATS = 10 , REPLACE'
--return the restore command
SELECT @sql
END
CLOSE aCur
DEALLOCATE aCur
--cleanup
DROP TABLE #Database_Files
DROP TABLE #Database_Files_Summary
SELECT SUBSTRING
( 'filename',
LEN('filename') - CHARINDEX('\',REVERSE('filename')) + 2 ,
CHARINDEX('\',REVERSE('filename'))
)
Better still, and to be posted later, have a stanard folder location and naming convention and move the logical files to a standard named file based upon the name of the DB.
/****CommentStart
Author : Kevin Foreman
Date: 21-11-2006
Description:
Generates restore copmmands for all databases on a server
where dbid > 4
Uses WITH MOVE, STATS = 10 and REPLACE
char(13) used to format it nicely if exec in text
Maintained:
CommentEnd****/
SET NOCOUNT ON
-- setup varaibles
DECLARE
@db_name sysname ,
@sql nvarchar(2000) ,
@aCur_db_name sysname ,
@bCur_name varchar(200),
@bCur_filename varchar(200)
DECLARE
@Database_Name TABLE
(
name sysname,
Done bit DEFAULT 0
)
CREATE TABLE
#Database_Files
(
name sysname ,
fileid int,
filename varchar(2000),
filegroup varchar(100) ,
size varchar(32) ,
maxsize varchar(32) ,
growth varchar(32) ,
usage varchar(32)
)
CREATE TABLE
#Database_Files_Summary
(
database_name sysname NULL,
name sysname ,
fileid int,
filename varchar(2000),
)
--get user database names
INSERT
@Database_Name
(Name)
SELECT
sd.name AS 'Name'
FROM
master.dbo.sysdatabases AS sd
WHERE
sd.dbid > 4
-- get user database file info
WHILE EXISTS (SELECT * FROM @Database_Name WHERE Done = 0)
BEGIN
SELECT @db_name =
(SELECT TOP 1 dn.name
FROM @Database_name as dn
WHERE Done = 0)
SELECT @sql = @db_name + '.dbo.sp_helpfile'
INSERT #Database_Files
EXEC sp_executesql @sql
INSERT #Database_Files_Summary
(database_name,name,fileid,filename)
SELECT
@db_name,name,fileid,filename FROM #Database_Files
DELETE FROM #Database_Files
UPDATE @Database_name
SET Done = 1
WHERE name = @db_name
END
--cursor through database names and then files to generate restore commands
--database name cursor
DECLARE aCur CURSOR
FOR SELECT name FROM @Database_Name ORDER BY name ASC
OPEN aCur
FETCH NEXT FROM aCur INTO @aCur_db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'RESTORE DATABASE ''' + LTRIM(RTRIM(@aCur_db_name)) + ''' FROM DISK = ''[filename]''' + char(13) + 'WITH ' + char(13)
--database file cursor
DECLARE bCur CURSOR
FOR SELECT name , filename FROM #Database_Files_Summary WHERE database_name = @aCur_db_name ORDER BY fileid ASC
OPEN bCur
FETCH NEXT FROM bCur INTO @bCur_name , @bCur_filename
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM bCur INTO @bCur_name , @bCur_filename
SELECT @sql = @sql + ' MOVE ''' + LTRIM(RTRIM(@bCur_name)) + ''' TO ''' + LTRIM(RTRIM(@bCur_filename)) + ''' ,' + char(13)
END
CLOSE bCur
DEALLOCATE bCur
FETCH NEXT FROM aCur INTO @aCur_db_name
SELECT @sql = @sql + ' STATS = 10 , REPLACE'
--return the restore command
SELECT @sql
END
CLOSE aCur
DEALLOCATE aCur
--cleanup
DROP TABLE #Database_Files
DROP TABLE #Database_Files_Summary
TSQL: Generate sp_configure commands
Generate sp_configure commands from a server to replay on another or after a rebuild. Correct spelling of create before runnig
SET NOCOUNT ON
SELECT '/****--' + @@SERVERNAME
EXEC sp_configure 'show advanced options' , '1'
RECONFIGURE WITH OVERRIDE
SELECT '****/'
CREAT TABLE #temp
(
name varchar(35) ,
minimum varchar(12) ,
maximum varchar(12) ,
config_value varchar(12) ,
run_value varchar(12) ,
)
INSERT #temp
exec sp_configure
SELECT 'EXEC sp_configure ''' + LTRIM(RTRIM(name)) + ''' , ''' + LTRIM(RTRIM(run_value)) + '''' FROM #temp
DROP TABLE #temp
SET NOCOUNT ON
SELECT '/****--' + @@SERVERNAME
EXEC sp_configure 'show advanced options' , '1'
RECONFIGURE WITH OVERRIDE
SELECT '****/'
CREAT TABLE #temp
(
name varchar(35) ,
minimum varchar(12) ,
maximum varchar(12) ,
config_value varchar(12) ,
run_value varchar(12) ,
)
INSERT #temp
exec sp_configure
SELECT 'EXEC sp_configure ''' + LTRIM(RTRIM(name)) + ''' , ''' + LTRIM(RTRIM(run_value)) + '''' FROM #temp
DROP TABLE #temp
TSQL: Generate Make Directory (MD) commands
Cheap and cherful way to create MD commands from an existing server. Handy for building a new server based upon an old.
/*
CREATE PROCEDURE #DBA_Create_Folder_Create_Batch_File
AS
BEGIN
*/
/****CommentStart
Author: Kevin Foreman
Date: 09-11-2006
Description:
Quick and dirty piece of code to generate make directory (MD) commands
based upon an existing folder structure. Save output to a .BAT/.CMD
file, eyaball and edit as required then call on destination server.
Input:
@drive_letter >> drive that you want folder structure from,
@include_folder1 to @include_folder4 >> 4 top level folders
Output:
MD commands for all subfolder/folders included
HEALTH WARNING:
No error checking
Maintained:
CommentEnd****/
SET NOCOUNT ON
--setup variables
DECLARE
@s varchar(2000) ,
@sql varchar(2000),
@i int ,
@drive_letter char(1) ,
@include_folder1 varchar(200) ,
@include_folder2 varchar(200) ,
@include_folder3 varchar(200) ,
@include_folder4 varchar(200)
--populate variables
SELECT
@drive_letter = 'i' ,
@include_folder1 = 'Build' ,
@include_folder2 = 'SQLBACK' ,
@include_folder3 = 'wibble' ,
@include_folder4 = 'wobble'
SELECT
@s = 'DIR ' + @drive_letter + ': /AD /S /B'
--create temp table to hold folder list
CREAT TABLE
#Folder_Name
(Folder_Name varchar(2000))
--get folder info
INSERT
#Folder_Name (Folder_Name)
EXEC
master.dbo.xp_cmdshell @s
--output make directory commands
SELECT DISTINCT
'MD "' + LTRIM(RTRIM(Folder_Name)) + '"' AS 'Create_Folder'
FROM
#Folder_Name
WHERE
(
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder1)) ) = UPPER ( LTRIM(RTRIM(Folder_Name)) )
OR
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder1)) + '\' ) = UPPER ( SUBSTRING(Folder_Name,1,LEN(LTRIM(RTRIM(@include_folder1))) + 4) )
)
OR
(
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder2)) ) = UPPER ( LTRIM(RTRIM(Folder_Name)) )
OR
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder2)) + '\' ) = UPPER ( SUBSTRING(Folder_Name,1,LEN(LTRIM(RTRIM(@include_folder2))) + 4) )
)
OR
(
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder3)) ) = UPPER ( LTRIM(RTRIM(Folder_Name)) )
OR
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder3)) + '\' ) = UPPER ( SUBSTRING(Folder_Name,1,LEN(LTRIM(RTRIM(@include_folder3))) + 4) )
)
OR
(
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder4)) ) = UPPER ( LTRIM(RTRIM(Folder_Name)) )
OR
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder4)) + '\' ) = UPPER ( SUBSTRING(Folder_Name,1,LEN(LTRIM(RTRIM(@include_folder4))) + 4) )
)
ORDER BY
'Create_Folder'
--cleanup
DROP TABLE #Folder_Name
/*
END
EXEC #DBA_Create_Folder_Create_Batch_File
DROP PROC #DBA_Create_Folder_Create_Batch_File
*/
/*
CREATE PROCEDURE #DBA_Create_Folder_Create_Batch_File
AS
BEGIN
*/
/****CommentStart
Author: Kevin Foreman
Date: 09-11-2006
Description:
Quick and dirty piece of code to generate make directory (MD) commands
based upon an existing folder structure. Save output to a .BAT/.CMD
file, eyaball and edit as required then call on destination server.
Input:
@drive_letter >> drive that you want folder structure from,
@include_folder1 to @include_folder4 >> 4 top level folders
Output:
MD commands for all subfolder/folders included
HEALTH WARNING:
No error checking
Maintained:
CommentEnd****/
SET NOCOUNT ON
--setup variables
DECLARE
@s varchar(2000) ,
@sql varchar(2000),
@i int ,
@drive_letter char(1) ,
@include_folder1 varchar(200) ,
@include_folder2 varchar(200) ,
@include_folder3 varchar(200) ,
@include_folder4 varchar(200)
--populate variables
SELECT
@drive_letter = 'i' ,
@include_folder1 = 'Build' ,
@include_folder2 = 'SQLBACK' ,
@include_folder3 = 'wibble' ,
@include_folder4 = 'wobble'
SELECT
@s = 'DIR ' + @drive_letter + ': /AD /S /B'
--create temp table to hold folder list
CREAT TABLE
#Folder_Name
(Folder_Name varchar(2000))
--get folder info
INSERT
#Folder_Name (Folder_Name)
EXEC
master.dbo.xp_cmdshell @s
--output make directory commands
SELECT DISTINCT
'MD "' + LTRIM(RTRIM(Folder_Name)) + '"' AS 'Create_Folder'
FROM
#Folder_Name
WHERE
(
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder1)) ) = UPPER ( LTRIM(RTRIM(Folder_Name)) )
OR
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder1)) + '\' ) = UPPER ( SUBSTRING(Folder_Name,1,LEN(LTRIM(RTRIM(@include_folder1))) + 4) )
)
OR
(
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder2)) ) = UPPER ( LTRIM(RTRIM(Folder_Name)) )
OR
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder2)) + '\' ) = UPPER ( SUBSTRING(Folder_Name,1,LEN(LTRIM(RTRIM(@include_folder2))) + 4) )
)
OR
(
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder3)) ) = UPPER ( LTRIM(RTRIM(Folder_Name)) )
OR
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder3)) + '\' ) = UPPER ( SUBSTRING(Folder_Name,1,LEN(LTRIM(RTRIM(@include_folder3))) + 4) )
)
OR
(
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder4)) ) = UPPER ( LTRIM(RTRIM(Folder_Name)) )
OR
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder4)) + '\' ) = UPPER ( SUBSTRING(Folder_Name,1,LEN(LTRIM(RTRIM(@include_folder4))) + 4) )
)
ORDER BY
'Create_Folder'
--cleanup
DROP TABLE #Folder_Name
/*
END
EXEC #DBA_Create_Folder_Create_Batch_File
DROP PROC #DBA_Create_Folder_Create_Batch_File
*/
Monday, March 5, 2007
TSQL: SQLServerAgent job history
Cheap and cheerful summary of most recent run of jobs. Note the comments before --create. Those are required for me to post the code, if I start with the c-word I get a page error.
--create table #tbl
(
instance_id int ,
job_id uniqueidentifier ,
job_name sysname ,
step_id int ,
step_name sysname ,
sql_message_id int ,
sql_severity int ,
message nvarchar(1024) ,
run_status int ,
run_date char(8) ,
run_time char(23) ,
run_duration int ,
operator_emailed nvarchar(20) ,
operator_netsent nvarchar(20) ,
operator_paged nvarchar(20) ,
retries_attempted int ,
server nvarchar(30)
)
insert #tbl
exec msdb.dbo.sp_help_jobhistory @mode = 'FULL'
GO
--format our date and time
--add leading zeroes
update #tbl
set run_time =
CASE len(run_time)
WHEN 6 THEN run_time
WHEN 5 THEN '0' + run_time
WHEN 4 THEN '00' + run_time
WHEN 3 THEN '000' + run_time
WHEN 2 THEN '0000' + run_time
WHEN 1 THEN '00000' + run_time
else 'wibble'
end
--add colons
update #tbl
set run_time = run_date + ' ' + STUFF(STUFF(ltrim(rtrim(run_time)), 3, 0, ':'), 6, 0, ':') + ':000'
select
max(instance_id) as instance_id
into
#tbl2
from
#tbl
where
step_id = 0
group by
job_name
select #tbl.* from #tbl
inner join #tbl2 on #tbl.instance_id = #tbl2.instance_id
drop table #tbl
drop table #tbl2
--create table #tbl
(
instance_id int ,
job_id uniqueidentifier ,
job_name sysname ,
step_id int ,
step_name sysname ,
sql_message_id int ,
sql_severity int ,
message nvarchar(1024) ,
run_status int ,
run_date char(8) ,
run_time char(23) ,
run_duration int ,
operator_emailed nvarchar(20) ,
operator_netsent nvarchar(20) ,
operator_paged nvarchar(20) ,
retries_attempted int ,
server nvarchar(30)
)
insert #tbl
exec msdb.dbo.sp_help_jobhistory @mode = 'FULL'
GO
--format our date and time
--add leading zeroes
update #tbl
set run_time =
CASE len(run_time)
WHEN 6 THEN run_time
WHEN 5 THEN '0' + run_time
WHEN 4 THEN '00' + run_time
WHEN 3 THEN '000' + run_time
WHEN 2 THEN '0000' + run_time
WHEN 1 THEN '00000' + run_time
else 'wibble'
end
--add colons
update #tbl
set run_time = run_date + ' ' + STUFF(STUFF(ltrim(rtrim(run_time)), 3, 0, ':'), 6, 0, ':') + ':000'
select
max(instance_id) as instance_id
into
#tbl2
from
#tbl
where
step_id = 0
group by
job_name
select #tbl.* from #tbl
inner join #tbl2 on #tbl.instance_id = #tbl2.instance_id
drop table #tbl
drop table #tbl2
TSQL: Rowcount for all tables
Why do it this way? To return a single rowset rather than one for each table.
SET NOCOUNT ON
DECLARE
@database_name sysname ,
@sql nvarchar(1000)
DECLARE
@All_Tables_Count_Rows table (Table_Name sysname, Row_Count int)
--for SQL Server 2000 create and drop a table
--CREATE TABLE #All_Tables_Count_Rows (Table_Name sysname, Row_Count int)
SELECT
@database_name = 'msdb'
SELECT
@sql = QUOTENAME(@database_name) + '.[dbo].[sp_msforeachtable] ''SELECT ''''?'''', COUNT(*) FROM ?'''
--SELECT @sql
--INSERT #All_Tables_Count_Rows
INSERT @All_Tables_Count_Rows table
EXEC (@sql)
SELECT Table_Name AS Table_Name , Row_Count AS Row_Count
FROM @All_Tables_Count_Rows
--FROM #All_Tables_Count_Rows
ORDER BY Table_Name ASC
--DROP TABLE #All_Tables_Count_Rows
SET NOCOUNT ON
DECLARE
@database_name sysname ,
@sql nvarchar(1000)
DECLARE
@All_Tables_Count_Rows table (Table_Name sysname, Row_Count int)
--for SQL Server 2000 create and drop a table
--CREATE TABLE #All_Tables_Count_Rows (Table_Name sysname, Row_Count int)
SELECT
@database_name = 'msdb'
SELECT
@sql = QUOTENAME(@database_name) + '.[dbo].[sp_msforeachtable] ''SELECT ''''?'''', COUNT(*) FROM ?'''
--SELECT @sql
--INSERT #All_Tables_Count_Rows
INSERT @All_Tables_Count_Rows table
EXEC (@sql)
SELECT Table_Name AS Table_Name , Row_Count AS Row_Count
FROM @All_Tables_Count_Rows
--FROM #All_Tables_Count_Rows
ORDER BY Table_Name ASC
--DROP TABLE #All_Tables_Count_Rows
Sunday, March 4, 2007
SQL-DMO: Script all DB objects, all DBs
Script all database objects - including indexes and triggers - in all databases on a server. The long integer values control what it is that you are scripting. Values can be found here.
'--- setup objects
Set objSQL = CreateObject("SQLDMO.SQLServer")
Set fso = CreateObject ("Scripting.FileSystemObject")
Set WshShell = CreateObject("WScript.Shell")
'--- provide a root directory for folder and files
rootDir = "C:\Datadump\INSTANCE01\"
'---trusted login
objSQL.LoginSecure = True
' --- or non-trusted connection
' objSQL.LoginSecure = False
' objSQL.Login = "sa"
' objSQL.Password = "sa_pswd"
' ---
objSQL.Connect "SERVER01\INSTANCE01"
'---create the root folder if not already there
If Not(fso.FolderExists(rootDir)) Then
WshShell.Run "cmd /c md " & rootDir
End If
'---go get our data
Set objDB = objSQL.Databases
For each database in objDB
' Create subfolder for database
If NOT(fso.FolderExists(rootDir & database.name)) Then
fso.CreateFolder(rootDir & database.name)
End If
ScriptOut database.tables, "Tables"
ScriptOut database.StoredProcedures, "StoredProcs"
ScriptOut database.Views, "Views"
ScriptOut database.Users, "Users"
ScriptOut database.Rules, "Rules"
ScriptOut database.UserDefinedDatatypes, "UserDefDataType"
ScriptOut database.UserDefinedFunctions, "UserDefFunc"
Next
'---script SQLServerAgent jobs
Set objJob = objSQL.JobServer
If NOT(fso.FolderExists(rootDir & "\Jobs")) Then
fso.CreateFolder(rootDir & "\Jobs")
End If
For each job in objJob.Jobs
sFileName = rootDir & "\Jobs\" & Replace(job.Name, "\", "-") & ".sql"
job.Script 1203896487, sfileName
Next
'---routine to do the work
Sub ScriptOut(object, foldername)
on error resume next
For each item in object
If item.SystemObject = False Then
If NOT(fso.FolderExists(rootDir & database.Name & "\" & foldername)) Then
fso.CreateFolder(rootDir & database.Name & "\" & foldername )
End If
sFileName = rootDir & database.Name & "\" & foldername & "\" & Replace(item.Name, "\", "-") & ".sql"
item.Script 1204232383, sFileName
End If
Next
End Sub
'--- tidy up
Set objSQL = Nothing
Set fso = Nothing
Set objDB = Nothing
'--- setup objects
Set objSQL = CreateObject("SQLDMO.SQLServer")
Set fso = CreateObject ("Scripting.FileSystemObject")
Set WshShell = CreateObject("WScript.Shell")
'--- provide a root directory for folder and files
rootDir = "C:\Datadump\INSTANCE01\"
'---trusted login
objSQL.LoginSecure = True
' --- or non-trusted connection
' objSQL.LoginSecure = False
' objSQL.Login = "sa"
' objSQL.Password = "sa_pswd"
' ---
objSQL.Connect "SERVER01\INSTANCE01"
'---create the root folder if not already there
If Not(fso.FolderExists(rootDir)) Then
WshShell.Run "cmd /c md " & rootDir
End If
'---go get our data
Set objDB = objSQL.Databases
For each database in objDB
' Create subfolder for database
If NOT(fso.FolderExists(rootDir & database.name)) Then
fso.CreateFolder(rootDir & database.name)
End If
ScriptOut database.tables, "Tables"
ScriptOut database.StoredProcedures, "StoredProcs"
ScriptOut database.Views, "Views"
ScriptOut database.Users, "Users"
ScriptOut database.Rules, "Rules"
ScriptOut database.UserDefinedDatatypes, "UserDefDataType"
ScriptOut database.UserDefinedFunctions, "UserDefFunc"
Next
'---script SQLServerAgent jobs
Set objJob = objSQL.JobServer
If NOT(fso.FolderExists(rootDir & "\Jobs")) Then
fso.CreateFolder(rootDir & "\Jobs")
End If
For each job in objJob.Jobs
sFileName = rootDir & "\Jobs\" & Replace(job.Name, "\", "-") & ".sql"
job.Script 1203896487, sfileName
Next
'---routine to do the work
Sub ScriptOut(object, foldername)
on error resume next
For each item in object
If item.SystemObject = False Then
If NOT(fso.FolderExists(rootDir & database.Name & "\" & foldername)) Then
fso.CreateFolder(rootDir & database.Name & "\" & foldername )
End If
sFileName = rootDir & database.Name & "\" & foldername & "\" & Replace(item.Name, "\", "-") & ".sql"
item.Script 1204232383, sFileName
End If
Next
End Sub
'--- tidy up
Set objSQL = Nothing
Set fso = Nothing
Set objDB = Nothing
TSQL: SQLServerAgent job detail summary
Get job details and check if input files to OSQL steps exist
/****CommentStart
Author: Kevin Foreman
Date: 17-11-2006
Description:
Summary SQL Server Agent Job information.
Input:
@job_name_string >> part of the job name (used in 'like' comparison)
Output:
Job name, description, step id, subsystem, success/fail actions,
step name, command, output file, OSQL input file specified and
if that file exists
HEALTH WARNING:
Check for OSQL input files is shakey.
xp_fileexists not tested with UNC files
Looks for cmdexec steps with strings
osql >> call to osql
-i >>input file
.sql / .prc >> assumes input files are .sql or .prc
Input file comes between first occurance of -i and .sql or .prc
Maintained:
17-11-2006 KF Added .prc as well as .sql for file ext to look for
05-12-2006 KF deal with doubl quotes in input file names
CommentEnd****/
--set up variables
DECLARE
@job_name_string varchar(200) ,
@filename varchar(400) ,
@i int
--populate here with job name part
SELECT
@job_name_string = 'purge'
SELECT --just in case
@job_name_string = '%' + @job_name_string + '%'
--get job into a temp table var
DECLARE @Table_Info TABLE
(
Job_Name varchar(256),
Description varchar (1024),
Step_ID int,
Subsystem varchar(80),
Success_Action tinyint,
Fail_Action tinyint,
Step_Name varchar(256),
Command varchar(4000),
Output_File varchar(400),
OSQL_Input_File varchar(400),
OSQL_Input_File_Exists bit
)
INSERT
@Table_Info
SELECT
sj.name,
sj.Description,
sjs.step_id,
sjs.subsystem,
sjs.on_success_action,
sjs.on_fail_action,
sjs.step_name,
sjs.command,
sjs.output_file_name,
NULL,
NULL
FROM
msdb.dbo.sysjobs AS sj
INNER JOIN
msdb.dbo.sysjobsteps AS sjs
ON
sj.job_id = sjs.job_id
WHERE
UPPER(sj.name) like UPPER(@job_name_string)
--get file name/paths ending .sql or .prc and remove leading/training spaces
UPDATE @Table_Info
SET OSQL_Input_File = LTRIM(RTRIM(SUBSTRING( Command, CHARINDEX('-I',UPPER(Command))+2 , CHARINDEX('.SQL',UPPER(Command))-CHARINDEX('-I',UPPER(Command))+2 )))
WHERE Subsystem = 'CmdExec'
AND UPPER(Command) LIKE '%OSQL%'
AND UPPER (Command) LIKE '%.SQL%'
UPDATE @Table_Info
SET OSQL_Input_File = LTRIM(RTRIM(SUBSTRING( Command, CHARINDEX('-I',UPPER(Command))+2 , CHARINDEX('.PRC',UPPER(Command))-CHARINDEX('-I',UPPER(Command))+2 )))
WHERE Subsystem = 'CmdExec'
AND UPPER(Command) LIKE '%OSQL%'
AND UPPER (Command) LIKE '%.PRC%'
--deal with quotes in input file
UPDATE @Table_Info
SET OSQL_Input_File = REPLACE( OSQL_Input_File , '"' , '')
--check for existence of o/s files
UPDATE @Table_Info
SET OSQL_Input_File_Exists = 0
WHERE OSQL_Input_File IS NULL
WHILE EXISTS (SELECT 1 FROM @Table_Info WHERE OSQL_Input_File_Exists IS NULL)
BEGIN
SELECT @filename = (SELECT TOP 1 OSQL_Input_File FROM @Table_Info WHERE OSQL_Input_File_Exists IS NULL)
EXEC master..xp_fileexist @filename, @i output
UPDATE @Table_Info SET OSQL_Input_File_Exists = @i WHERE @filename = OSQL_Input_File
END
SELECT
Job_Name AS 'Job_Name',
Description AS 'Description',
Step_ID AS 'Step_ID',
Subsystem AS 'Subsystem',
CASE Success_Action
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Fail'
WHEN 3 THEN 'Next'
END AS 'Success_Action',
CASE Fail_Action
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Fail'
WHEN 3 THEN 'Next'
END AS 'Fail_Action',
Step_Name AS 'Step_Name',
Command AS 'Command',
Output_File AS 'Output_File',
OSQL_Input_File AS 'OSQL_Input_File',
OSQL_Input_File_Exists AS 'OSQL_Input_File_Exists'
FROM
@Table_Info
ORDER BY
Job_Name ,
Step_ID
/****CommentStart
Author: Kevin Foreman
Date: 17-11-2006
Description:
Summary SQL Server Agent Job information.
Input:
@job_name_string >> part of the job name (used in 'like' comparison)
Output:
Job name, description, step id, subsystem, success/fail actions,
step name, command, output file, OSQL input file specified and
if that file exists
HEALTH WARNING:
Check for OSQL input files is shakey.
xp_fileexists not tested with UNC files
Looks for cmdexec steps with strings
osql >> call to osql
-i >>input file
.sql / .prc >> assumes input files are .sql or .prc
Input file comes between first occurance of -i and .sql or .prc
Maintained:
17-11-2006 KF Added .prc as well as .sql for file ext to look for
05-12-2006 KF deal with doubl quotes in input file names
CommentEnd****/
--set up variables
DECLARE
@job_name_string varchar(200) ,
@filename varchar(400) ,
@i int
--populate here with job name part
SELECT
@job_name_string = 'purge'
SELECT --just in case
@job_name_string = '%' + @job_name_string + '%'
--get job into a temp table var
DECLARE @Table_Info TABLE
(
Job_Name varchar(256),
Description varchar (1024),
Step_ID int,
Subsystem varchar(80),
Success_Action tinyint,
Fail_Action tinyint,
Step_Name varchar(256),
Command varchar(4000),
Output_File varchar(400),
OSQL_Input_File varchar(400),
OSQL_Input_File_Exists bit
)
INSERT
@Table_Info
SELECT
sj.name,
sj.Description,
sjs.step_id,
sjs.subsystem,
sjs.on_success_action,
sjs.on_fail_action,
sjs.step_name,
sjs.command,
sjs.output_file_name,
NULL,
NULL
FROM
msdb.dbo.sysjobs AS sj
INNER JOIN
msdb.dbo.sysjobsteps AS sjs
ON
sj.job_id = sjs.job_id
WHERE
UPPER(sj.name) like UPPER(@job_name_string)
--get file name/paths ending .sql or .prc and remove leading/training spaces
UPDATE @Table_Info
SET OSQL_Input_File = LTRIM(RTRIM(SUBSTRING( Command, CHARINDEX('-I',UPPER(Command))+2 , CHARINDEX('.SQL',UPPER(Command))-CHARINDEX('-I',UPPER(Command))+2 )))
WHERE Subsystem = 'CmdExec'
AND UPPER(Command) LIKE '%OSQL%'
AND UPPER (Command) LIKE '%.SQL%'
UPDATE @Table_Info
SET OSQL_Input_File = LTRIM(RTRIM(SUBSTRING( Command, CHARINDEX('-I',UPPER(Command))+2 , CHARINDEX('.PRC',UPPER(Command))-CHARINDEX('-I',UPPER(Command))+2 )))
WHERE Subsystem = 'CmdExec'
AND UPPER(Command) LIKE '%OSQL%'
AND UPPER (Command) LIKE '%.PRC%'
--deal with quotes in input file
UPDATE @Table_Info
SET OSQL_Input_File = REPLACE( OSQL_Input_File , '"' , '')
--check for existence of o/s files
UPDATE @Table_Info
SET OSQL_Input_File_Exists = 0
WHERE OSQL_Input_File IS NULL
WHILE EXISTS (SELECT 1 FROM @Table_Info WHERE OSQL_Input_File_Exists IS NULL)
BEGIN
SELECT @filename = (SELECT TOP 1 OSQL_Input_File FROM @Table_Info WHERE OSQL_Input_File_Exists IS NULL)
EXEC master..xp_fileexist @filename, @i output
UPDATE @Table_Info SET OSQL_Input_File_Exists = @i WHERE @filename = OSQL_Input_File
END
SELECT
Job_Name AS 'Job_Name',
Description AS 'Description',
Step_ID AS 'Step_ID',
Subsystem AS 'Subsystem',
CASE Success_Action
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Fail'
WHEN 3 THEN 'Next'
END AS 'Success_Action',
CASE Fail_Action
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Fail'
WHEN 3 THEN 'Next'
END AS 'Fail_Action',
Step_Name AS 'Step_Name',
Command AS 'Command',
Output_File AS 'Output_File',
OSQL_Input_File AS 'OSQL_Input_File',
OSQL_Input_File_Exists AS 'OSQL_Input_File_Exists'
FROM
@Table_Info
ORDER BY
Job_Name ,
Step_ID
DBCC CHECKDB: Summarise output file
Cut to the chase - just tell me if there are any errors in my DBCC CHECKDB output file
SET NOCOUNT ON
/****CommentStart
Author: Kevin Foreman
Date: 15-01-2007
Description:
Uploads a specified DBCC CHECKDB output file
into a table and returns jobstep start and summary rows
Account executing muct have access to exec
extended procs and to the specified file
EDIT FOR SQL2000
replace temp table variable
with temp table
Input:
@file >> file name
Output:
summary data
Maintained:
CommentEnd****/
DECLARE @DBCC_CHECKDB_ERRORS table
--CREATE TABLE #DBCC_CHECKDB_ERRORS
( aLine nvarchar(2000) NULL,
Database_Name sysname NULL,
Allocation_Errors int NULL,
Consistency_Errors int NULL
)
DECLARE
@s nvarchar(2000) ,
@i int,
@file nvarchar(1000)
SELECT
@file = 'C:\Datadump\Empty\PRDDBA_DBCC_CheckDB_TLA.log'
SELECT
@s = 'type "' + @file + '"'
BEGIN
EXEC master.dbo.xp_fileexist @file,@i output
IF @i != 1
RAISERROR ('ERROR in detect input file',16,1)
END
BEGIN
INSERT @DBCC_CHECKDB_ERRORS (aLine)
-- INSERT #DBCC_CHECKDB_ERRORS (aLine)
EXEC @i = master.dbo.xp_cmdshell @s
IF @i != 0
RAISERROR ('ERROR in exec command shell',16,1)
END
UPDATE
@DBCC_CHECKDB_ERRORS
-- #DBCC_CHECKDB_ERRORS
SET
Database_Name = SUBSTRING( aLine, CHARINDEX('errors in database ',aLine)+20, CHARINDEX('''.',aLine) -(CHARINDEX('errors in database ',aLine)+20) ) ,
Allocation_Errors = SUBSTRING( aLine, 15, CHARINDEX('allocation errors',aLine)-15 ) ,
Consistency_Errors = SUBSTRING( aLine, CHARINDEX('allocation errors and ',aLine)+22, CHARINDEX('consistency errors',aLine)-(CHARINDEX('allocation errors and ',aLine)+22) )
WHERE
aLine LIKE '%CHECKDB FOUND%'
SELECT
Database_Name AS 'Databse_Name' ,
Allocation_Errors AS 'Allocation_Errors',
Consistency_Errors AS 'Consistency_Errors'
--,aLine AS 'Output_Text'
FROM
@DBCC_CHECKDB_ERRORS
WHERE
UPPER(aLine) Like '%CHECKDB FOUND%' -- what you want to see
--DROP TABLE #DBCC_CHECKDB_ERRORS
SET NOCOUNT ON
/****CommentStart
Author: Kevin Foreman
Date: 15-01-2007
Description:
Uploads a specified DBCC CHECKDB output file
into a table and returns jobstep start and summary rows
Account executing muct have access to exec
extended procs and to the specified file
EDIT FOR SQL2000
replace temp table variable
with temp table
Input:
@file >> file name
Output:
summary data
Maintained:
CommentEnd****/
DECLARE @DBCC_CHECKDB_ERRORS table
--CREATE TABLE #DBCC_CHECKDB_ERRORS
( aLine nvarchar(2000) NULL,
Database_Name sysname NULL,
Allocation_Errors int NULL,
Consistency_Errors int NULL
)
DECLARE
@s nvarchar(2000) ,
@i int,
@file nvarchar(1000)
SELECT
@file = 'C:\Datadump\Empty\PRDDBA_DBCC_CheckDB_TLA.log'
SELECT
@s = 'type "' + @file + '"'
BEGIN
EXEC master.dbo.xp_fileexist @file,@i output
IF @i != 1
RAISERROR ('ERROR in detect input file',16,1)
END
BEGIN
INSERT @DBCC_CHECKDB_ERRORS (aLine)
-- INSERT #DBCC_CHECKDB_ERRORS (aLine)
EXEC @i = master.dbo.xp_cmdshell @s
IF @i != 0
RAISERROR ('ERROR in exec command shell',16,1)
END
UPDATE
@DBCC_CHECKDB_ERRORS
-- #DBCC_CHECKDB_ERRORS
SET
Database_Name = SUBSTRING( aLine, CHARINDEX('errors in database ',aLine)+20, CHARINDEX('''.',aLine) -(CHARINDEX('errors in database ',aLine)+20) ) ,
Allocation_Errors = SUBSTRING( aLine, 15, CHARINDEX('allocation errors',aLine)-15 ) ,
Consistency_Errors = SUBSTRING( aLine, CHARINDEX('allocation errors and ',aLine)+22, CHARINDEX('consistency errors',aLine)-(CHARINDEX('allocation errors and ',aLine)+22) )
WHERE
aLine LIKE '%CHECKDB FOUND%'
SELECT
Database_Name AS 'Databse_Name' ,
Allocation_Errors AS 'Allocation_Errors',
Consistency_Errors AS 'Consistency_Errors'
--,aLine AS 'Output_Text'
FROM
@DBCC_CHECKDB_ERRORS
WHERE
UPPER(aLine) Like '%CHECKDB FOUND%' -- what you want to see
--DROP TABLE #DBCC_CHECKDB_ERRORS
Thursday, March 1, 2007
TSQL: Get drive space
Returns drive total size and free space in mb and percent free
CREATE PROCEDURE #prcDBA_Get_Drive_Space_Used
@pdrive char(1) ,
@pDriveSize numeric(20) OUTPUT
AS
DECLARE
@s varchar(13), --string holder
@i integer, --integer holder
@file_system_object integer,
@drive_letter integer,
@drive_size varchar(20)
SELECT
@s = 'GetDrive("' + @pdrive + '")'
EXEC
@i = sp_OACreate 'Scripting.FileSystemObject', @file_system_object OUTPUT
IF @i = 0
BEGIN
EXEC @i = sp_OAMethod @file_system_object, @s, @drive_letter OUTPUT
IF @i = 0
BEGIN
EXEC @i = sp_OAGetProperty @drive_letter,'TotalSize', @drive_size OUTPUT
IF @i <> 0
SELECT @drive_size = NULL
END
END
SELECT @pDriveSize = convert(numeric(20),@drive_size)
EXEC sp_OADestroy @drive_letter
EXEC sp_OADestroy @file_system_object
GO
SET NOCOUNT ON
--setup variables
DECLARE
@drive_letter char(1),
@mb_free integer,
@drive_size numeric(20)
DECLARE
@disk_space table
(
Drive char(1),
MBSize int, MBFree int
)
--get free space
CREATE TABLE #fixeddrives
(
Drive char(1),
MBFree integer NOT NULL
)
INSERT INTO
#fixeddrives
EXEC
master.dbo.xp_fixeddrives
--get total space
WHILE EXISTS (SELECT Drive FROM #fixeddrives WHERE Drive NOT IN (SELECT Drive FROM @disk_space))
BEGIN
SELECT @drive_letter = (SELECT TOP 1 Drive FROM #fixeddrives WHERE Drive NOT IN (SELECT Drive FROM @disk_space))
EXEC #prcDBA_Get_Drive_Space_Used @drive_letter, @drive_size OUTPUT
INSERT INTO @disk_space
(Drive, MBSize, MBFree)
SELECT
@drive_letter , CONVERT(integer,ROUND(@drive_size/1048576,0)) , MBFree
FROM #fixeddrives
WHERE Drive = @drive_letter
END
/*
DECLARE cur_drive_letter_cursor CURSOR FOR
SELECT
Drive,
MBFree
FROM
#fixeddrives
OPEN
cur_drive_letter_cursor
FETCH NEXT FROM cur_drive_letter_cursor INTO @drive_letter, @mb_free
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC #prcDBA_Get_Drive_Space_Used @drive_letter, @drive_size OUTPUT
INSERT INTO @disk_space
(Drive, MBSize, MBFree)
VALUES
(@drive_letter, CONVERT(integer,ROUND(@drive_size/1048576,0)), @mb_free)
FETCH NEXT FROM cur_drive_letter_cursor into @drive_letter, @mb_free
END
CLOSE cur_drive_letter_cursor
DEALLOCATE cur_drive_letter_cursor
*/
--display drive, total, free and pct free
SELECT
Drive AS Drive,
MBSize AS MBSize,
MBFree AS MBFree,
MBFree*100/MBSize AS PercentFree
FROM
@disk_space
GO
--cleanup
DROP PROC #prcDBA_Get_Drive_Space_Used
DROP TABLE #fixeddrives
CREATE PROCEDURE #prcDBA_Get_Drive_Space_Used
@pdrive char(1) ,
@pDriveSize numeric(20) OUTPUT
AS
DECLARE
@s varchar(13), --string holder
@i integer, --integer holder
@file_system_object integer,
@drive_letter integer,
@drive_size varchar(20)
SELECT
@s = 'GetDrive("' + @pdrive + '")'
EXEC
@i = sp_OACreate 'Scripting.FileSystemObject', @file_system_object OUTPUT
IF @i = 0
BEGIN
EXEC @i = sp_OAMethod @file_system_object, @s, @drive_letter OUTPUT
IF @i = 0
BEGIN
EXEC @i = sp_OAGetProperty @drive_letter,'TotalSize', @drive_size OUTPUT
IF @i <> 0
SELECT @drive_size = NULL
END
END
SELECT @pDriveSize = convert(numeric(20),@drive_size)
EXEC sp_OADestroy @drive_letter
EXEC sp_OADestroy @file_system_object
GO
SET NOCOUNT ON
--setup variables
DECLARE
@drive_letter char(1),
@mb_free integer,
@drive_size numeric(20)
DECLARE
@disk_space table
(
Drive char(1),
MBSize int, MBFree int
)
--get free space
CREATE TABLE #fixeddrives
(
Drive char(1),
MBFree integer NOT NULL
)
INSERT INTO
#fixeddrives
EXEC
master.dbo.xp_fixeddrives
--get total space
WHILE EXISTS (SELECT Drive FROM #fixeddrives WHERE Drive NOT IN (SELECT Drive FROM @disk_space))
BEGIN
SELECT @drive_letter = (SELECT TOP 1 Drive FROM #fixeddrives WHERE Drive NOT IN (SELECT Drive FROM @disk_space))
EXEC #prcDBA_Get_Drive_Space_Used @drive_letter, @drive_size OUTPUT
INSERT INTO @disk_space
(Drive, MBSize, MBFree)
SELECT
@drive_letter , CONVERT(integer,ROUND(@drive_size/1048576,0)) , MBFree
FROM #fixeddrives
WHERE Drive = @drive_letter
END
/*
DECLARE cur_drive_letter_cursor CURSOR FOR
SELECT
Drive,
MBFree
FROM
#fixeddrives
OPEN
cur_drive_letter_cursor
FETCH NEXT FROM cur_drive_letter_cursor INTO @drive_letter, @mb_free
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC #prcDBA_Get_Drive_Space_Used @drive_letter, @drive_size OUTPUT
INSERT INTO @disk_space
(Drive, MBSize, MBFree)
VALUES
(@drive_letter, CONVERT(integer,ROUND(@drive_size/1048576,0)), @mb_free)
FETCH NEXT FROM cur_drive_letter_cursor into @drive_letter, @mb_free
END
CLOSE cur_drive_letter_cursor
DEALLOCATE cur_drive_letter_cursor
*/
--display drive, total, free and pct free
SELECT
Drive AS Drive,
MBSize AS MBSize,
MBFree AS MBFree,
MBFree*100/MBSize AS PercentFree
FROM
@disk_space
GO
--cleanup
DROP PROC #prcDBA_Get_Drive_Space_Used
DROP TABLE #fixeddrives
TSQL: Get database space info
Generate friendly space used and free data for each database on a server. Replace word 'less-than' with the less than symbol I had to add o get this to post correctly...
CREATE PROC prcDBA_Database_Space_Used @pdbname_filter varchar(100) = '%'
AS
BEGIN
/****CommentStart
Author: Kevin Foreman
Date: 28-02-2007
Version: SQL2000_001.01
Description:
Uses code pilfered from SQL Server 2000 sp_spaceused
and the output of DBCC SQLPERF (LOGSPACE) to generate
a friendly resultset of database total, data and log
space used/free for all DBs.
Only looks at ONLINE databases if all databases queried
RAISERROR if DBNAME FILTER does not match a local DB name
RAISERROR if any negative space values returned
Health Warning:
Frankencode with limited error handling
Inputs: @pdbname_filter : string database name part
Outputs: Resultset
Used By: DBA
Maintained:
01-03-2007 KF added various quotenames to deal with dodgy DB names
such as those starting with a number and not a character
08-03-2007 KF SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to avoid
blocking problems
CommentEnd****/
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--to run as SQL instead of a proc
--uncomment next three lines and run from here to final END
-- DECLARE
-- @pdbname_filter varchar(100)
-- SET @pdbname_filter = '%'
DECLARE
@pdbname sysname ,
@dbsize dec(15,2) ,
@logsize dec(15,2) ,
@bytesperpage dec(15,2) ,
@pagesperMB dec(15,2) ,
@unallocated_space dec(15,2) ,
@sql nvarchar(2000) ,
@s varchar(2000),
@i smallint
--add wildcards to dbname filter
SELECT @pdbname_filter = '%' + @pdbname_filter + '%'
--check dbname_filter
IF NOT EXISTS (SELECT name from master.dbo.sysdatabases WHERE UPPER(name) LIKE (UPPER(@pdbname_filter)))
BEGIN
SELECT @s = 'DBNAME_FILTER: ' + QUOTENAME(REPLACE(@pdbname_filter,'%','')) + ' not valid on this server. Data for ALL databases will be returned.'
RAISERROR (@s,11,1)
SELECT @pdbname_filter = '%'
END
CREATE TABLE ##database_space
(dbname sysname NULL,
dbsize dec(15,2) NULL,
logsize dec(15,2) NULL,
unallocated_space dec(15,2) NULL ,
logused decimal(15,2) NULL
)
CREATE TABLE #log_space
(database_name sysname ,
logsize decimal(15,2),
logused decimal(15,2) ,
status bit)
--get some logspace info
SELECT @sql = 'DBCC SQLPERF(LOGSPACE)'
INSERT #log_space
EXEC (@sql)
--get some sizing info
SELECT @bytesperpage = low
FROM master.dbo.spt_values
WHERE number = 1
AND type = 'E'
SELECT @pagesperMB = 1048576 / @bytesperpage
--get spaceused info for each database
WHILE EXISTS
(SELECT name FROM master.dbo.sysdatabases
WHERE DATABASEPROPERTYEX(name,'status') = 'ONLINE'
AND name LIKE @pdbname_filter
AND name NOT IN (SELECT dbname FROM ##database_space))
BEGIN
SELECT @pdbname = (SELECT TOP 1 name FROM master.dbo.sysdatabases
WHERE DATABASEPROPERTYEX(name,'status') = 'ONLINE'
AND name LIKE @pdbname_filter
AND name NOT IN (SELECT dbname FROM ##database_space))
SELECT @sql =
'INSERT ##database_space (dbname , dbsize)
SELECT ''' + @pdbname + ''' , SUM(CONVERT(dec(15,2),size))
FROM ' + QUOTENAME(@pdbname) + '.[dbo].[sysfiles]
WHERE (status & 64 = 0)'
EXEC (@sql)
SELECT @sql =
'UPDATE ##database_space SET logsize =
(SELECT SUM(CONVERT(dec(15,2),size))
FROM ' + QUOTENAME(@pdbname) + '.[dbo].[sysfiles]
WHERE (status & 64 <> 0)) WHERE dbname = ''' + @pdbname + ''''
EXEC (@sql)
SELECT @sql =
'UPDATE ##database_space
SET unallocated_space =
(dbsize -
(SELECT SUM(CONVERT(dec(15,2),reserved))
FROM ' + QUOTENAME(@pdbname) + '.[dbo].[sysindexes]
WHERE indid in (0, 1, 255))
) / ' + CONVERT(varchar,@pagesperMB) + '
WHERE dbname = ''' + @pdbname + ''''
EXEC (@sql)
END
--consolidate and tidy up data
UPDATE ##database_space
SET dbsize = (ds.dbsize + ds.logsize) / @pagesperMB ,
logsize = ls.logsize ,
logused = ls.logused
FROM ##database_space AS ds, #log_space AS ls
WHERE ds.dbname = ls.database_name
--check to see if our numbers are valid
SELECT @i = 0
SELECT @s = 'WARNING: '
IF EXISTS
(SELECT * FROM ##database_space WHERE dbsize less-than 0)
SELECT @i = 1 , @s = 'DBSIZE:'
IF EXISTS
(SELECT * FROM ##database_space WHERE logsize less-than 0)
SELECT @i = @i + 1 , @s = @s + 'LOGSIZE:'
IF EXISTS
(SELECT * FROM ##database_space WHERE unallocated_space less-than 0)
SELECT @i = @i + 1 , @s = @s + 'UNALLOCATED_SPACE:'
IF @i > 0
BEGIN
SELECT @s = @s + ' Usage data error. Call a DBA!'
RAISERROR(@s,11,1)
END
--return results
SELECT
dbname AS 'Database_Name',
dbsize AS 'Databse_Size' ,
dbsize - logsize AS 'Data_Size',
logsize AS 'logsize' ,
dbsize - unallocated_space - logsize AS 'Used_Data_Space' ,
unallocated_space AS 'Free_Data_Space' ,
CONVERT(dec(15,2),logsize * logused / 100) AS 'Used_log_Space' ,
CONVERT(dec(15,2),logsize - (logsize * logused / 100)) AS 'Free_log_Space'
FROM
##database_space
ORDER BY
dbname ASC
--tidy up
DROP TABLE ##database_space
DROP TABLE #log_space
END
CREATE PROC prcDBA_Database_Space_Used @pdbname_filter varchar(100) = '%'
AS
BEGIN
/****CommentStart
Author: Kevin Foreman
Date: 28-02-2007
Version: SQL2000_001.01
Description:
Uses code pilfered from SQL Server 2000 sp_spaceused
and the output of DBCC SQLPERF (LOGSPACE) to generate
a friendly resultset of database total, data and log
space used/free for all DBs.
Only looks at ONLINE databases if all databases queried
RAISERROR if DBNAME FILTER does not match a local DB name
RAISERROR if any negative space values returned
Health Warning:
Frankencode with limited error handling
Inputs: @pdbname_filter : string database name part
Outputs: Resultset
Used By: DBA
Maintained:
01-03-2007 KF added various quotenames to deal with dodgy DB names
such as those starting with a number and not a character
08-03-2007 KF SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to avoid
blocking problems
CommentEnd****/
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--to run as SQL instead of a proc
--uncomment next three lines and run from here to final END
-- DECLARE
-- @pdbname_filter varchar(100)
-- SET @pdbname_filter = '%'
DECLARE
@pdbname sysname ,
@dbsize dec(15,2) ,
@logsize dec(15,2) ,
@bytesperpage dec(15,2) ,
@pagesperMB dec(15,2) ,
@unallocated_space dec(15,2) ,
@sql nvarchar(2000) ,
@s varchar(2000),
@i smallint
--add wildcards to dbname filter
SELECT @pdbname_filter = '%' + @pdbname_filter + '%'
--check dbname_filter
IF NOT EXISTS (SELECT name from master.dbo.sysdatabases WHERE UPPER(name) LIKE (UPPER(@pdbname_filter)))
BEGIN
SELECT @s = 'DBNAME_FILTER: ' + QUOTENAME(REPLACE(@pdbname_filter,'%','')) + ' not valid on this server. Data for ALL databases will be returned.'
RAISERROR (@s,11,1)
SELECT @pdbname_filter = '%'
END
CREATE TABLE ##database_space
(dbname sysname NULL,
dbsize dec(15,2) NULL,
logsize dec(15,2) NULL,
unallocated_space dec(15,2) NULL ,
logused decimal(15,2) NULL
)
CREATE TABLE #log_space
(database_name sysname ,
logsize decimal(15,2),
logused decimal(15,2) ,
status bit)
--get some logspace info
SELECT @sql = 'DBCC SQLPERF(LOGSPACE)'
INSERT #log_space
EXEC (@sql)
--get some sizing info
SELECT @bytesperpage = low
FROM master.dbo.spt_values
WHERE number = 1
AND type = 'E'
SELECT @pagesperMB = 1048576 / @bytesperpage
--get spaceused info for each database
WHILE EXISTS
(SELECT name FROM master.dbo.sysdatabases
WHERE DATABASEPROPERTYEX(name,'status') = 'ONLINE'
AND name LIKE @pdbname_filter
AND name NOT IN (SELECT dbname FROM ##database_space))
BEGIN
SELECT @pdbname = (SELECT TOP 1 name FROM master.dbo.sysdatabases
WHERE DATABASEPROPERTYEX(name,'status') = 'ONLINE'
AND name LIKE @pdbname_filter
AND name NOT IN (SELECT dbname FROM ##database_space))
SELECT @sql =
'INSERT ##database_space (dbname , dbsize)
SELECT ''' + @pdbname + ''' , SUM(CONVERT(dec(15,2),size))
FROM ' + QUOTENAME(@pdbname) + '.[dbo].[sysfiles]
WHERE (status & 64 = 0)'
EXEC (@sql)
SELECT @sql =
'UPDATE ##database_space SET logsize =
(SELECT SUM(CONVERT(dec(15,2),size))
FROM ' + QUOTENAME(@pdbname) + '.[dbo].[sysfiles]
WHERE (status & 64 <> 0)) WHERE dbname = ''' + @pdbname + ''''
EXEC (@sql)
SELECT @sql =
'UPDATE ##database_space
SET unallocated_space =
(dbsize -
(SELECT SUM(CONVERT(dec(15,2),reserved))
FROM ' + QUOTENAME(@pdbname) + '.[dbo].[sysindexes]
WHERE indid in (0, 1, 255))
) / ' + CONVERT(varchar,@pagesperMB) + '
WHERE dbname = ''' + @pdbname + ''''
EXEC (@sql)
END
--consolidate and tidy up data
UPDATE ##database_space
SET dbsize = (ds.dbsize + ds.logsize) / @pagesperMB ,
logsize = ls.logsize ,
logused = ls.logused
FROM ##database_space AS ds, #log_space AS ls
WHERE ds.dbname = ls.database_name
--check to see if our numbers are valid
SELECT @i = 0
SELECT @s = 'WARNING: '
IF EXISTS
(SELECT * FROM ##database_space WHERE dbsize less-than 0)
SELECT @i = 1 , @s = 'DBSIZE:'
IF EXISTS
(SELECT * FROM ##database_space WHERE logsize less-than 0)
SELECT @i = @i + 1 , @s = @s + 'LOGSIZE:'
IF EXISTS
(SELECT * FROM ##database_space WHERE unallocated_space less-than 0)
SELECT @i = @i + 1 , @s = @s + 'UNALLOCATED_SPACE:'
IF @i > 0
BEGIN
SELECT @s = @s + ' Usage data error. Call a DBA!'
RAISERROR(@s,11,1)
END
--return results
SELECT
dbname AS 'Database_Name',
dbsize AS 'Databse_Size' ,
dbsize - logsize AS 'Data_Size',
logsize AS 'logsize' ,
dbsize - unallocated_space - logsize AS 'Used_Data_Space' ,
unallocated_space AS 'Free_Data_Space' ,
CONVERT(dec(15,2),logsize * logused / 100) AS 'Used_log_Space' ,
CONVERT(dec(15,2),logsize - (logsize * logused / 100)) AS 'Free_log_Space'
FROM
##database_space
ORDER BY
dbname ASC
--tidy up
DROP TABLE ##database_space
DROP TABLE #log_space
END
SQL-DMO: SQLServerAgent Jobs to files
Generate a separate script for each SQLServerAgent job. Filename the same as the job name.
Const ForWriting = 2
Dim job
Dim oFSO
Dim oFSOWrite
Dim oSQLServer
Dim s
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.LoginSecure = True
oSQLServer.Connect "SERVER\INSTANCE"
For Each job in oSQLServer.JobServer.Jobs
Set oFSOWrite = oFSO.OpenTextFile("c:\Datadump\" & job.name & ".sql", ForWriting, True)
oFSOWrite.WriteLine job.Script
oFSOWrite.Close
Next
Set oFSO = Nothingo
SQLServer.Disconnect
Set oSQLServer = Nothing
Make sure SQLServerAgent job names contain no illegal filename characters and service account has write permissions to the destination folder. Edit for database servername and folder location for output files.
Const ForWriting = 2
Dim job
Dim oFSO
Dim oFSOWrite
Dim oSQLServer
Dim s
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.LoginSecure = True
oSQLServer.Connect "SERVER\INSTANCE"
For Each job in oSQLServer.JobServer.Jobs
Set oFSOWrite = oFSO.OpenTextFile("c:\Datadump\" & job.name & ".sql", ForWriting, True)
oFSOWrite.WriteLine job.Script
oFSOWrite.Close
Next
Set oFSO = Nothingo
SQLServer.Disconnect
Set oSQLServer = Nothing
Subscribe to:
Comments (Atom)