What is a Good Way to Run CheckDB on a VLDB?

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.

How Do I Move SQL Database Files Around?

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.

Where Do I Start with PowerShell?

Introduction

This morning I set out to get some information about getting started in PowerShell for a coworker. Rather than spend a bunch of time searching for different sites I threw the question out to the SQL Community on Twitter via #SqlHelp. The response was so overwhelming that I decided I at least owed it to the SQL Community to get it all written down.

Thank you to everyone who supplied a link, you are what keeps the SQL Community great!

So here is everything that came in broken out by category:

Blogs

Aaron Nelson: http://sqlvariant.com/wordpress/

Aaron Nelson – PowerShell Links: http://sqlvariant.com/wordpress/index.php/powershell-links/

Aaron Nelson – Getting Started with PowerShell: http://sqlvariant.com/wordpress/index.php/2010/02/sqlserversqldatabasestables-dir/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+Sqlvariations+%28SQLvariations%3A+SQL+Server%2C+a+little+PowerShell%2C+maybe+some+Hyper-V%29

Aaron Nelson – Get More Done With SQLPSX: http://sqlvariant.com/wordpress/index.php/2010/02/get-more-done-with-sqlpsx/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+Sqlvariations+%28SQLvariations%3A+SQL+Server%2C+a+little+PowerShell%2C+maybe+some+Hyper-V%29

Buck Woody – Carpe Datum: http://blogs.msdn.com/buckwoody/archive/tags/PowerShell/default.aspx

Buck Woody – InformIt: http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=253&rll=1

Buck Woody – Intro to PowerShell Series: http://blogs.technet.com/heyscriptingguy/archive/2009/05/27/how-does-windows-powershell-make-it-easier-to-work-with-sql-server-2008.aspx

Hey, Scripting Guy! Blog: http://blogs.technet.com/heyscriptingguy/archive/tags/getting+started/default.aspx

Laerte Junior – Great Practical Examples on SimpleTalk.com: http://www.simple-talk.com/author/laerte-junior/

Laerte Junior – $hell Your Experience (Portuguese): http://laertejuniordba.spaces.live.com/

Windows PowerShell Blog: http://blogs.msdn.com/powershell/

Community

Powershellcommunity.org: http://www.powershellcommunity.org/

PowerShell.com: http://powershell.com/cs/

eBooks

Master-PowerShell with Dr.Tobias Weltner: http://powershell.com/cs/blogs/ebook/

TechNet

Task-Based Guide to PowerShell: http://technet.microsoft.com/en-us/library/ee332526.aspx

PowerShell Script Center: http://technet.microsoft.com/en-us/scriptcenter/dd742419.aspx

Script Center: http://technet.microsoft.com/en-us/scriptcenter/default.aspx

Windows PowerShell: Survival Guide: http://social.technet.microsoft.com/wiki/contents/articles/windows-powershell-survival-guide.aspx

Applications / GUIs

PowerGUI: http://powergui.org/index.jspa

Videos

Midnight DBA PowerShell Videos: http://midnightdba.itbookworm.com/Admin.aspx

White Papers

Understanding and Using PowerShell Support in SQL Server 2008: http://msdn.microsoft.com/en-us/library/dd938892.aspx

Conclusion

So there you have it, a pretty substantial list of resources to get started in PowerShell.These all look like great resources and I can see I have a ton of reading to do.

How Can I Tell if SQL Agent is Running via SQL?

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.

What is a Good Way to Get the SQL Server Log Directory?

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.