BPSoftware.com Blog

Tuesday, November 25, 2008

SQL: DBCC CHECKTABLE on multiple tables

Part of decent SQL database maintenence is periodically checking the integrity, both logical and physical, of database objects. SQL Server has the DBCC CHECKDB command that will check the integrity of the objects and report back the results. DBCC CHECKDB runs CHECKALLOC, CHECKTABLE and CHECKCATALOG on the specified database.

in SQL 2005 you can use table variables if you do not need or want to store the data you can store it in a table variable instead of a temporary table

in SQL 2005 you can use table variables if you do not need or want to store the data you can store it in a table variable instead of a temporary table

DBCC CHECKTABLE allows you to check the integrity of a specified table or indexed view. If there are a large number of tables to check, the task of running the command for each tablename could be time consuming. Creating a script that loops through a set of database tables and runs DBCC CHECKTABLE for each, is a bit easier.

The sys.objects contains each of the user-defined schema objects in a database. Rows that are of [Type] = 'U' are tables. This makes it easy to get all the tables within a database (of course other filters can be applied). The following script will loop through and run the DBCC CHECKTABLE command for each table in the database (the command can be easily replaced to perform another action on each returned table):

-- set options
3

SET NOCOUNT ON;
4

SET ANSI_PADDING ON;
5

SET ANSI_NULLS ON;
6

SET QUOTED_IDENTIFIER ON;
7

-- declare table to store table names
10

-- in SQL 2005 you can use table variables
11

-- if you do not need or want to store the data you can store it in a table
12

-- variable instead of a temporary table
13

DECLARE @tablenames TABLE (
14

TableName VARCHAR(255)
15

);
16

-- populate table with tablenames
20

INSERT INTO @tablenames
21

SELECT
22

name
23

FROM
24

sys.objects
25

-- additional filters can be set to narrow table selection
26

WHERE
27

([type] = 'U');
28

-- Declare a cursor to loop through table
32

DECLARE tablenames INSENSITIVE SCROLL CURSOR
33

FOR
34

SELECT
35

TableName
36

FROM
37

@tablenames;
38

-- Open the cursor.
43

OPEN tablenames
44

-- declare variables
48

DECLARE @curtablename AS VARCHAR(255);
49

DECLARE @cmd AS VARCHAR(1024);
50

-- Loop through all the tables
54

WHILE (1=1)
55

BEGIN
56

FETCH NEXT FROM tablenames INTO @curtablename;
57

IF @@fetch_status <> 0 BREAK;
58

-- run the command
59

set @cmd = 'DBCC CHECKTABLE ([' + rtrim(@curtablename) + '])';
60

exec (@cmd)
61

print @cmd + CHAR(10) + CHAR(13);
62

end
63

-- Close cursor
67

CLOSE tablenames;
68

DEALLOCATE tablenames;
69

Labels: Code, SQL

posted by Brad Prendergast at 9:04:00 PM (0 comments)
Links to this post
Permalink

Monday, November 24, 2008

SQL: Index Fragmentation Maintenance

In a previous post, I discussed SQL Server's built in ability for suggesting missing indexes. As records are inserted, updated and deleted from tables indexes can become fragmented.

A fragmented index affects performance (table scans and index seeks) when the DBMS needs to search for records.

This probably leaves the question, "How can one defragment the indexes of SQL tables?"

Within SQL Server 2005 and greater you can setup a maintenance plan to defragment all the indexes within all or a particular database. You can also right click on an index and select to either Rebuild or Reorganize and index (rebuilding will re-create the index whereas reorganize reorders the index pages).

I prefer to have a little more control and use the following script as part of my maintenance plan. This script allows for a little more control in the selecting of tables, indexes and databases to maintain. I tend to rebuild indexes that are more than 30% fragmented and reorganize those that are greater than 5%.

SQL Server 2005 (and greater) has index information more readily accessible. The key to this script is the index information that is contained in sys.dm_db_index_physical_stats. This table returns the size and fragmentation information for each index.

/** **/
2

-- Create table to hold index information
3

SET NOCOUNT ON;
4

create table _IndexInformation (
6

database_id smallint,
7

ObjectName varchar(255),
8

[object_id] int,
9

IndexName varchar(255),
10

[index_id] int,
11

index_level int,
12

index_type_desc varchar(60),
13

avg_fragmentation_in_percent float,
14

fragment_count float,
15

page_count bigint,
16

record_count bigint,
17

index_depth int
18

);
19

/** **/

/** **/
2

-- clear table contents
3

truncate table _IndexInformation;
4

-- populate table
6

insert into _IndexInformation
7

select
8

database_id,
9

object_name(i.object_id) AS [ObjectName],
10

i.[object_id],
11

i.name AS [IndexName],
12

dm.index_id,
13

dm.index_level,
14

dm.index_type_desc,
15

dm.avg_fragmentation_in_percent,
16

dm.fragment_count,
17

dm.page_count,
18

dm.record_count,
19

dm.index_depth
20

from
21

-- database, table and index information can be specified
22

/*
23

sys.dm_db_index_physical_stats (
24

{ database_id NULL 0 DEFAULT }
25

, { object_id NULL 0 DEFAULT }
26

, { index_id NULL 0 -1 DEFAULT }
27

, { partition_number NULL 0 DEFAULT }
28

, { mode NULL DEFAULT }
29

)
29

This example uses database 1; you should replace this with the ID of the database you wish to process
30

*/
31

sys.dm_db_index_physical_stats(1,null,null,null,'detailed') dm
32

INNER JOIN sys.indexes i ON
33

(i.object_id = dm.object_id) AND (i.index_id = dm.index_id);
34

/** **/
35

/** **/
2

-- read index table and maintain indexes
3

SET NOCOUNT ON;
4

SET ANSI_PADDING ON;
5

SET ANSI_NULLS ON;
6

SET QUOTED_IDENTIFIER ON;
7

-- Declare variables.
10

declare @tablename as varchar(255)
11

declare @indexname as varchar(255)
12

declare @frag as float
13

declare @cmd as varchar(1024)
14

-- Declare a cursor.
16

declare indexes insensitive scroll cursor
17

for
18

select
19

ObjectName,
20

IndexName,
21

avg_fragmentation_in_percent
22

from
23

_IndexInformation
24

where
25

(avg_fragmentation_in_percent >= 5);
26

-- Open the cursor.
28

open indexes
29

-- Loop through all the indexes.
32

while (1=1)
33

begin
34

fetch next from indexes into @tablename, @indexname, @frag;
35

if @@fetch_status <> 0 break;
36

-- if fragmentation is above 30% rebuild index
37

-- if fragmentation is above 5% reorganize index
38

if (@frag >= 30)
39

begin
40

set @cmd = 'ALTER INDEX [' + rtrim(@indexname) + '] ON [' + @tablename +'] REBUILD;'
41

exec (@cmd)
42

print @cmd;
43

end
44

else
45

if (@frag >= 5)
46

begin
47

set @cmd = 'ALTER INDEX [' + rtrim(@indexname) + '] ON [' + @tablename +'] REORGANIZE;'
48

exec (@cmd)
49

print @cmd;
50

end;
51

end
52

-- Close cursor
56

close indexes;
57

deallocate indexes;
58

/** **/
59

/** **/
2

-- delete index table
3

drop table _IndexInformation;
4

/** **/
5

I have the script schedule to as part of my database maintenance plan. I run the script several times with additional selection criteria limiting it to groups of tables in a particular database.

Labels: Code, SQL

posted by Brad Prendergast at 9:51:00 PM (2 comments)
Links to this post
Permalink

Friday, November 21, 2008

Off-topic: Uhm, Rickroll?

Last weekend I did a search for a video on YouTube. When the search results came up I clicked the link to a video that seemed to be the most relevant. The video queued up and the video for Rick Astley's "Never Gonna Give You Up" song started to play. Over the video the words 'You have just been Rickrolled' appeared. It took me a moment, but then I realized something funny was going on. I did a quick Google search for 'Rickrolled' and found out exactly what it was...

The weird thing is that I had never heard any hype or talk about 'Rickrolling' until last weekend. Over the past week, I have heard mention of Rickrolling in some form of media. 'Rickrolling' appeared in blogs, news articles and I even heard radio hosts discussing it.

The whole thing is sort of comical to me. Was it that I had never paid attention or is it that the RickRolling buzz is still new?

How long will it last?

Next we'll have "Vanilla Iced" or "Milli-Vanilli'd"

Now that gives me an idea.......

Labels: Misc

posted by Brad Prendergast at 7:25:00 AM (3 comments)
Links to this post
Permalink

Thursday, November 20, 2008

SQL: Where are the database files?

From within the SQL Server Management Studio you can right click on a database and view its properties to get a list of the physical files that make up a database. If you do not have access to SQL Management Studio and the database is online you can run the sp_helpfile stored procedure. The sp_helpfile stored procedure lists the files for the current (individual) database; the database must be online. What about retrieving file information for offline databases?
Another alternative for retrieving the list of database files for a single or collective set of databases is to query the sys.master_files table. You can query the sys.master_files database if the database is online, offline or in one of the other states. The following query retrieves database file information:

SELECT
2

database_id,
3

DB_Name(database_id) as database_name,
4

[file_id],
5

type_desc,
6

[name],
7

physical_name,
8

state_desc,
9

[size],
10

max_size
11

FROM
12

sys.master_files
13

WHERE
14

-- you can use the DB_ID function to return the
15

-- database id of a database by name
16

(database_id = DB_ID(N'master'))
17

-- if you know the id you can filter by it directly
18

-- (database_id = 1)
19

ORDER BY
20

type_desc DESC,
21

physical_name;

The sys.databases table has information about the database in the current SQL Server instance.

SELECT
2

database_id,
3

[name],
4

compatibility_level,
5

collation_name,
6

state_desc
7

FROM
8

sys.databases;

Labels: Code, SQL

posted by Brad Prendergast at 9:08:00 PM (0 comments)
Links to this post
Permalink

Tuesday, November 18, 2008

Show Desktop in my QuickLaunch Toolbar?

It was there before; now it's not.

Where is it?

How do I get it back?

Where is the Show Desktop shortcut on my Quick Launch toolbar?

The Quick Launch toolbar is a great place to place shortcuts to frequently used applications. One of my must have shortcuts, is the Show Desktop shortcut.

This shortcut quickly minimizes all open applications leaving a clear view of the desktop. The Show Desktop shortcut is generally available by default in the Quick Launch toolbar, however, what if it gets removed or it is no longer there?

Adding the Show Desktop shortcut back is easier than you think.

1. Open Notepad and enter the following text:

[Shell]
Command=2
IconFile=explorer.exe,3
[Taskbar]
Command=ToggleDesktop

2. Save the file in the Microsoft\Internet Explorer\Quick Launch folder that is found in your profile's Application Data directory.

3. Save the file as: Show Desktop.scf

Now it is back again!!

Labels: How-To, Tech

posted by Brad Prendergast at 8:41:00 PM (5 comments)
Links to this post
Permalink