Introduction
Today’s script is one that I wrote based on the logic outlined in this post by Paul Randal (Blog|Twitter). This script is written for SQL 2000 but, as Paul notes, the logic will work on SQL 2005.
The Script
This stored procedure stays pretty true to the logic outlined in Paul’s post so I will just cover the differences here. The first thing to notice is that the parameters passed into the procedure are the days of the week that different portions of the check should run, the maximum run time in minutes and whether or not to print debug messages. The stored procedure then parses the input strings and runs CHECKALLOCs and CHECKCATALOGs if requested.
If tables should be checked today a little more work is necessary. I decided to use a utility database to hold work tables for my custom scripts called DBADB. The first part of performing a table check is to see if a work table already exists in the database. If the table does not exist then one is created and loaded with a list of all tables in the database. After the table is loaded, the process begins looping through the table, checking that the run time has not been exceeded then running checktable on each table. This continues until the table list has been processed or time runs out. If time runs out then the process picks up where it left off next time the table check starts to make sure all tables are eventually checked before starting over again.
Here is the script:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
| CREATE PROCEDURE [dbo].[sp_dba_checkdb_vldb] @days_to_run_checkalloc varchar(15) = '1,4', @days_to_run_checkcatalog varchar(15) = '1', @days_to_run_checktable varchar(15) = '1,2,3,4,5,6,7', @max_minutes_to_run int = 360, @debug_flag bit = 0
AS
BEGIN
DECLARE @date_part_search_string char(3),
@start_time datetime,
@sql_text nvarchar(4000),
@current_object_id int
SELECT @date_part_search_string = '%' + CAST(DATEPART(dw, GETDATE()) AS VARCHAR) + '%',
@start_time = GETDATE()
IF PATINDEX(@date_part_search_string, @days_to_run_checkalloc) > 0
BEGIN
IF @debug_flag = 1
PRINT 'DEBUG: Running DBCC CHECKALLOC'
DBCC CHECKALLOC
END
IF PATINDEX(@date_part_search_string, @days_to_run_checkcatalog) > 0
BEGIN
IF @debug_flag = 1
PRINT 'DEBUG: Running DBCC CHECKCATALOG'
DBCC CHECKCATALOG
END
IF PATINDEX(@date_part_search_string, @days_to_run_checktable) > 0
BEGIN
DECLARE @control_table varchar(500)
SELECT @control_table = DB_NAME() + '_dbcc_checktable_worklist'
IF NOT EXISTS(SELECT * FROM [DBADB].[dbo].[sysobjects] WHERE name = @control_table)
BEGIN
SELECT @sql_text = 'SELECT DISTINCT
i.id,
CAST(NULL AS datetime) AS run_date_time
INTO [DBADB].[dbo].' + QUOTENAME(@control_table) + '
FROM sysindexes i
INNER JOIN sysobjects o
ON i.id = o.id
WHERE o.type != ''TF'''
IF @debug_flag = 1
PRINT 'DEBUG: Running sql command: [' + @sql_text + ']'
EXEC sp_executesql @sql_text
SELECT @sql_text = 'CREATE CLUSTERED INDEX IX_' + @control_table + '_id_run_date_time ON [DBADB].[dbo].' + QUOTENAME(@control_table) + ' (id, run_date_time)'
IF @debug_flag = 1
PRINT 'DEBUG: Running sql command: [' + @sql_text + ']'
EXEC sp_executesql @sql_text
END
SELECT @sql_text = ' SELECT TOP 1 @current_object_id = c.id
FROM [DBADB].[dbo].[' + @control_table + '] c
INNER JOIN sysobjects o
ON c.id = o.id
WHERE c.run_date_time IS NULL
AND o.type != ''TF'''
IF @debug_flag = 1
PRINT 'DEBUG: Running sql command: [' + @sql_text + ']'
EXEC sp_executesql @sql_text, N'@current_object_id int OUTPUT', @current_object_id = @current_object_id OUTPUT
IF @debug_flag = 1
PRINT 'DEBUG: @current_object_id = ' + ISNULL(CAST(@current_object_id AS varchar), 'NULL')
WHILE @current_object_id IS NOT NULL AND DATEADD(mi, @max_minutes_to_run, @start_time) > GETDATE()
BEGIN
SELECT @current_object_id = NULL
SELECT @sql_text = ' SELECT TOP 1 @current_object_id = c.id
FROM [DBADB].[dbo].[' + @control_table + '] c
INNER JOIN sysobjects o
ON c.id = o.id
WHERE c.run_date_time IS NULL
AND o.type != ''TF'''
IF @debug_flag = 1
PRINT 'DEBUG: Running sql command: [' + @sql_text + ']'
EXEC sp_executesql @sql_text, N'@current_object_id int OUTPUT', @current_object_id = @current_object_id OUTPUT
IF @debug_flag = 1
PRINT 'DEBUG: @current_object_id = ' + ISNULL(CAST(@current_object_id AS varchar), 'NULL')
IF @debug_flag = 1
PRINT 'DEBUG: Running DBCC CHECKTABLE(' + CAST(@current_object_id AS varchar) + ')'
DBCC CHECKTABLE(@current_object_id)
SELECT @sql_text = ' UPDATE [DBADB].[dbo].[' + @control_table + ']
SET run_date_time = GETDATE()
WHERE id = @current_object_id'
IF @debug_flag = 1
PRINT 'DEBUG: Running sql command: [' + @sql_text + ']'
EXEC sp_executesql @sql_text, N'@current_object_id int OUTPUT', @current_object_id = @current_object_id OUTPUT
END
IF @current_object_id IS NULL
BEGIN
PRINT 'Ran out of work to do...cleaning up and shutting down.'
IF EXISTS(SELECT * FROM [DBADB].[dbo].[sysobjects] WHERE name = @control_table)
BEGIN
SELECT @sql_text = 'DROP TABLE [DBADB].[dbo].' + QUOTENAME(@control_table)
IF @debug_flag = 1
PRINT 'DEBUG: Running sql command: [' + @sql_text + ']'
EXEC sp_executesql @sql_text
END
END
ELSE
PRINT 'Ran out of time...shutting down.'
END
END
GO |
Conclusion
As usual, I hope you find this script helpful. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.
Introduction
Today’s script is one that I had not planned on blogging so soon but since Paul Randal just talked about moving SQL Server files around for TechNet Magazine, it seemed like a good time to break this one out.
The Script
This script is a little different in that it is a script that creates a script, a “turducken” script if you will. The idea here is to run this script and let it output to text then take those results, paste them into a new window, review the resulting script and maybe even run it.
The script starts out by getting the default data and log file locations from SQL Server by checking the registry, using a method learned by watching Profiler while checking the location with Management Studio. (I often comment out these lines to change to specific locations.) The script then begins building the string to output by creating the command to turn xp_cmdshell on. A lot of people, including me, have a policy against xp_cmdshell being turned on on their servers but in cases like this where it is turned on to be used and turned right back off I feel I can get away with it. The next step is to create alter database scripts to take the databases offline. Next, the alter database statements and DOS file move commands are created. The command to set the database online is then added and finally, xp_cmdshell is turned back off.
Updated 07/14/2010 to replace move commands with copy to make sure the files are still good before they are deleted. This does add manual cleanup but the trade off is not having to find out how good your last backup is. Thanks to Paul Randal (Blog|Twitter) and Buck Woody (Blog|Twitter) for pointing this out.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
| DECLARE @file_path nvarchar(520),
@log_path nvarchar(520)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultData', @file_path OUTPUT
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultLog', @log_path OUTPUT
SELECT 'EXEC sp_configure ''show advanced options'', ''1''
GO
RECONFIGURE
GO
EXEC sp_configure ''xp_cmdshell'', ''1''
GO
RECONFIGURE
GO'
SELECT 'ALTER DATABASE [' + DB_NAME(mf.database_id) + '] SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [' + DB_NAME(mf.database_id) + '] MODIFY FILE (NAME = [' + mf.name + '], FILENAME = ''' + @file_path + '' + mf.name + '.mdf'')
GO
ALTER DATABASE [' + DB_NAME(mf.database_id) + '] MODIFY FILE (NAME = [' + mf2.name + '], FILENAME = ''' + @log_path + '' + mf2.name + '.ldf'')
GO
EXEC xp_cmdshell ''copy /Y "' + mf.physical_name + '" "' + @file_path + '' + mf.name + '.mdf"''
GO
EXEC xp_cmdshell ''copy /Y "' + mf2.physical_name + '" "' + @log_path + '' + mf2.name + '.ldf"''
GO
ALTER DATABASE [' + DB_NAME(mf.database_id) + '] SET ONLINE
GO
'
FROM sys.master_files mf
INNER JOIN sys.master_files mf2
ON mf.database_id = mf2.database_id
WHERE DB_NAME(mf.database_id) NOT IN ('master', 'model', 'msdb', 'tempdb') and mf.type_desc = 'ROWS' and mf.file_id = 1 and mf2.type_desc = 'LOG'
AND (mf.physical_name != @file_path + '' + mf.name + '.mdf' OR mf2.physical_name != @log_path + '' + mf2.name + '.ldf')
order by mf.name
SELECT 'EXEC sp_configure ''xp_cmdshell'', ''0''
GO
RECONFIGURE
GO
EXEC sp_configure ''show advanced options'', ''0''
GO
RECONFIGURE
GO' |
Would this Work for TempDB?
Yes and no. The resulting script can be used to alter the location of TempDB but SQL Server must be stopped and started to move the files. There would also have to be manual cleanup in that the old TempDB files would have to be deleted.
Would this Work for System Databases other than TempDB?
No. There is a lot involved in moving a system database. Detailed instructions can be found here: http://msdn.microsoft.com/en-us/library/ms345408.aspx
Conclusion
As usual, I hope you find this script helpful. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.
Introduction
Today’s post is a continuation in my on-going effort to document all of the scripts I use to manage my environment. To date it has been a great exercise both in getting things written down and in getting feedback from others on better ways I could be doing things. To that end, here is a stored procedure that I use to tell if SQL Agent is running before trying to start a job programmatically.
The Script
This script opens with some trickery that I stole from Management Studio using Profiler. This is my new favorite way to determine if a server is a named instance and handle it accordingly. I will be going back to retrofit all of my scripts to use this method.
The next step is to declare a temporary table and pull in the results of xp_servicecontrol to be able to use them later. The xp_servicecontrol extended procedure is undocumented and unsupported but still seems like the best option in this case. Alternatives I have seen include using xp_cmdshell (NO!) or building a CLR stored procedure to call WMI and get this information.
Finally, the service status is selected into the output variable for programmatic use by a caller and the flag is checked to see if the service status should also be returned as a result set for debug reasons.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
| CREATE PROCEDURE [dbo].[sp_dba_GetSqlAgentStatus] @display_results bit = 0, @service_state varchar(100) = null OUTPUT
AS
BEGIN
DECLARE @agent_string nvarchar(256)
IF (SERVERPROPERTY('INSTANCENAME') IS NOT NULL)
SELECT @agent_string = @agent_string + N'SQLAgent$' + CONVERT (sysname, SERVERPROPERTY('INSTANCENAME'))
ELSE
SELECT @agent_string = @agent_string + N'SQLServerAgent'
CREATE TABLE #service_state
(
current_service_state varchar(50)
)
INSERT INTO #service_state
EXEC master.dbo.xp_servicecontrol 'QueryState', 'SQLServerAgent', @service_state OUTPUT
SELECT @service_state = current_service_state
FROM #service_state
DROP TABLE #service_state
IF @display_results = 1
SELECT @service_state AS current_service_state
END
GO |
Conclusion
As usual, I hope you find this script helpful. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.
Introduction
Today’s post is going to be a quick one to get back in the saddle again. I have been on an involuntary hiatus from blogging due to production issues. Things are getting better so it seems like time to get back to blogging.
The script for today is one that I use in all of my job creation scripts as well as a script I have to go back and clean up job output file paths. It gets the location of master on a SQL 2005 or SQL 2008 server, using that to find the Log directory.
The Script
This script is quite simple so I will not spend a ton of time here. It just looks at the location of master, goes up 1 level and swaps in “Log” for “Data” in the directory name. Please note that this may not work well in your environment if you have moved master after installing SQL. Thanks to Argenis Fernandez (Blog|Twitter) for pointing out the ineffcient way I was doing this before, this script now just looks at the location of the SQL Server Error Log and uses the same directory. Warning: This is using undocumented functionality, but the best things in SQL tend to be undocumented.
1
2
3
4
5
6
7
8
9
10
11
12
13
| CREATE PROCEDURE [dbo].[sp_dba_GetJobOutputFileDirectory] @log_directory varchar(400) OUTPUT, @showResults BIT = 1
AS
BEGIN
SELECT @log_directory = REPLACE(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(512)), 'ERRORLOG', '')
IF @showResults = 1
SELECT @log_directory
END
GO |
Conclusion
As usual, I hope you find this script helpful. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.