SQL 2000: Listing tables in a database

Here's a handy little script which should return all of the user tables in a database:

SELECT name
FROM sysobjects
WHERE (xtype = 'U')
ORDER BY name

You can modify the xtype value in the WHERE clause to return other information, such as 'V' for views, and 'S' for system tables.

SQL 2000: Changing licensing for your SQL Server

If you find yourself in a position where you've changed license criteria for your SQL Server (you've added licensing for additional CPU's or seats, for example), where can you adjust this setting for your current SQL installation?

1. Launch Control Panel
2. Find and launch "SQL Server 2000 Licensing Setup"
3. You will find settings similar to when SQL was installed, allowing you to adjust the Licensing Mode (the mode currently in effect should be represented by what's shown)
4. Click "Continue"

SQL 2000: Reindexing a database

When the time comes where you need to manually reindex a database, the following script will allow you to do so for all tables in that database. Execute it using Query Analyzer, substituting the name of the database you'd like to reindex where "[your database name]" appears. The same script can be used with SQL 2005, although if you have aliases in place, they may interfere with successful script execution. (2005 specific scripts will be included elsewhere)

Reindexing in this fashion should not be done during production hours, as the db in question will be locked while the operation takes place.

USE [your database name]

DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN PRINT 'Now reindexing ' + @TableName
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

Once the operation is complete, you can review the output pane to see if errors occurred when reindexing the various tables in the db. Such errors will typically appear between the "Now reindexing" statement and the "DBCC Execution Completed" statement that corresponds to a given table.

SQL 2000: Script error in Taskpad view

You may find after using the Taskpad view in Enterprise Manager for a while, when you go to a database with that view selected a scripting error results, similar to the following:

Internet Explorer Script Error
Line: 307
Char: 2
Error: Unspecified Error
Code: 0
URL:
res://D:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Binn\Resources\1033\sqlmmc.rll/Tabs.html

In most cases, this error can be dealt with by selecting the database in question, selecting another view for the database, and then changing back to the Taskpad view, if that's your preference. Changing to another view and back to the Taskpad view causes Enterprise Manager to re-create the html page used for the Taskpad view, clearing up the error.

SQL 2000: Truncating a transaction log

With appropriate db backups and regular system maintenance in place, your transaction logs will usually remain a reasonable size.

For whatever reason, though, your logs are growing and you need to cut them down to size. (Perhaps jobs were failing due to running out of space, or for whatever other reason)

First, it's usually a good idea to make sure that a current good backup of the db your dealing with has been made -- after all, recovery is usually why we rely on transaction logs.

Once the db is backed up, you can:

• execute "BACKUP LOG databaseName WITH TRUNCATE_ONLY" using Enterprise Manager or Query Analyzer
• to recover the space that this will free, you then either:
• run "DBCC SHRINKFILE (filename, size )" where is the filename without the ldf extension, and size is the target size for the file
• use the shrink database functionality through Enterprise Manager (right click the db name -> All Tasks -> Shrink Database... -> Click "Files" button, and select the logfile from the dropdown, select the desired "Shrink action" and click "Ok" -> A dialog box should pop up when the shrinking has completed -> Clear the dialog box and cancel out of the "Shrink Database" window, otherwise you may shrink more than you'd bargained for)

Refresh your view of the DB (Right click in Ent. Mgr. -> Refresh), and you should see that space has been freed, if the file was successfully shrunk.

Introduction

"Know the smallest things and the biggest things, the shallowest things and the deepest things. As if it were a straight road mapped out on the ground ... These things cannot be explained in detail. From one thing, know ten thousand things. When you attain the Way of strategy there will not be one thing you cannot see. You must study hard."

- "The Book of Five Rings",

Miyamoto Musashi

To reach your full potential as a problem solver involving computers and IT, being able to adapt, learn, and apply your knowledge are keys to success.

Learning by rote will grant you some capacity to deal with problems which come your way. Not all answers can be found in the pages of certification study manuals, textbooks, or explicitly in any form.

You've got to take what you learn, and put that knowledge to use it in a variety of ways until you properly understand it. When you understand why things are happening, you will be much better equipped to apply that information when approaching new problems, extending your knowledge and understanding beyond what you've been taught.