Alright...you've inherited a database somewhere along the way, and you're doing your best to optimize performance. You see in profiler traces queries being executed that don't seem to be anywhere in the code for the application you now support.
Could they be tucked away in a stored procedure somewhere? Oh, wait...you see dozens of stored procedures where it could be hiding -- oh, no.
A quick and easy means of searching through stored procedures can be done using the following in query analyzer:
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%text to search for%'
AND ROUTINE_TYPE='PROCEDURE'
Simply substitute "text to search for" with what you're hoping to find, run the query, and hopefully you'll be rewarded with the culprit procedure(s) you're trying to track down. There are other means of doing this, of course, however this quick/easy routine will often suffice.
SQL 2000: Listing tables in a database
Here's a handy little script which should return all of the user tables in a database:
You can modify the xtype value in the WHERE clause to return other information, such as 'V' for views, and 'S' for system tables.
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?
- Launch Control Panel
- Find and launch "SQL Server 2000 Licensing Setup"
- 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)
- 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.
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.
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:
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.
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:
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)
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.
Subscribe to:
Posts (Atom)