Avoiding the Autoclose and Autoshrink Options

Q
In the Microsoft SQL Server 2000 Resource Kit, I read about a procedure that checks all databases for options that you should avoid using. I understand why I should avoid using options such as offline, DBO-use only, statistics off, truncate log on checkpoint, and so on. However, the inclusion of two of the options—autoclose and autoshrink—puzzles me.

Autoclose. Closing a file when it is not in use sounds like a prudent thing to do. Yet I see files opening and closing all the time in the error log in SQL Server, even though I know that I never use the database except for viewing in Enterprise Manager. Does viewing a list of databases in Enterprise Manager make the files open? It makes sense to me to use autoclose on databases that I use only occasionally as import staging areas, and which I access for only a fraction of the time my online transaction processing (OLTP) database is in use. SQL Server Books Online says that the autoclose option is automatically set to YES for the SQL Server Personal Edition and to NO for all other editions. Has this option been included only for the Personal Edition?

Autoshrink. The autoshrink option also sounds helpful. However, would using autoshrink on my import staging areas, where I bring in quantities of data for short periods of time, keep these areas small for backups?

A
Autoclose is available in all editions of SQL Server 2000. The amount of memory you save by using this option is relatively small but might be important on memory-constrained systems running the Personal Edition. However, enabling autoclose for the other editions isn’t a good idea, because every time you try to access the database, the query will be slowed by the amount of load time it takes to mount the database. As for the question about your files opening and closing, yes, when you enumerate a database in Enterprise Manager, you access the database, which opens the files.

Leaving autoshrink on for production systems also isn’t a good practice (unless you really need to), because autoshrink might start when your system is otherwise busy with production work, and slow down the system. However, enabling this option is a good idea for desktop or remote systems that don’t get a lot of DBA attention and whose databases could grow out of control before you detect the problem.

Fighting Unknown Problems

Hardware problems are quite difficult to tackle, worse if under Windows. There was once when installing a software, I accidentally inserted the Audio hardware diskette. Thinking it was harmless, I went on, thinking I can always “undo” everything again. How wrong was I. The next moment I restarted Windows, I got the blue screen of death. Yeah, Windows is very nasty when it comes to hardware.

Find The Culprit
Always calm down. Try to solve the problem yourself before resolving to formatting or sending to your computer dealer. First, know what’s happening and the cause of it. In my case, the installation must have overwrite my old setting and replaced the wrong file. There must be a reason why Windows is acting improperly.

Logging Back On
First, try to log back on. If you can’t log into Windows, don’t panic. Restart the computer. When the text “Starting Windows 98…” appears, press F8. A menu will appear. Choose “Safe Mode” to continue. Safe mode will take some time to load. When you are in Windows, go to Control Panel, System. Click on the Device Manager tab. Find the faulty hardware driver and delete it. Open autoexec.bat and config.sys (both are hidden files in your root directory) with notepad. If the hardware is a CD-ROM or sound card, it will probably write add some lines. Check whether there are backup file (autoexec.bak, autoexec.b~k e.g.) since most installation will back up the 2 important files. If there is, simply replace them with the backup files. If there isn’t, put “REM ” in front of every line that loads the hardware.

Repairing
When you restart, your computer is probably ok, since you have deleted all instances of the hardware drivers. But the hardware isn’t functioning. re-install the hardware drivers, restart and you’re done! If the problem persists, your probably have a system conflict go to the next page for more.

CD-ROM

The maximum a floppy disk can contain is 1.44 MB. To play a game with around 200 MB you are stuck with 100 over diskettes. The CD-ROM is a revolutionary invention that allows up to 625 MB at one time. However, it is read only, which means you can’t store in anything.

Buttons Fun
Although most CD-ROMs comes with only 2 buttons, do you know you can also skip, play previous track and play the next track as well with a proper combination? Check properly on your instruction manual for additional functions. You just might be surprised what it can do.

Clean Thoroughly
It is important to clean the CR-ROM at least once every month. Get a CD lens cleaner. If possible, choose one with an alcohol solution drop. This should ensure that your CD-ROM will always be able to read properly.

Clean You CDs
It is just as important to clean your CDs often. Why? Because if you have a speck of dust on the surface of one of your CDs, once spinning at high-speed in the drive, the speck of dust is enough to produce scratches on your precious CD.

Running In DOS
One of the common problems with CD-ROMs is unable to access it under DOS. This might be because the protected-mode CD-ROM driver isn’t available. You’ll need to add the real-mode device driver, usually included with your CD-ROM drive, in CONFIG.SYS and load MSCDEX.EXE, which is available in the Windows\Command folder. You should also make sure both files are on your startup disk.

Run List
Like those cool expensive hi-fi systems, you can customize the run list of your Audio CD, only not with the CD-ROM itself. Use Windows’ CD player to customize the run list and you can have your CD running in any track order you like.

Read Ahead
The read ahead buffer is useful to speed up things. Go to Control Panel, System. Click on the Performance tab. Click on the File System… button. On the File System Properties, click on CD-ROM. Make sure you have the fullest cache. Even if you have a CD-ROM with less than Quad speed, just move ahead. You’ll be surprised to find your CD-ROM running faster.

How Many Sheets

If you’d like to see a quick list of the sheets in the current workbook, simply open the file’s property dialog box. To do so, choose File, Properties. Then, click the Contents tab, and Excel will display a complete Complex workbooks usually contain several worksheets, and keeping up with all those sheets can be a list of sheets in the current workbook.

Adding DOSKEY to your DOS Window

One way to have DOSKEY available when you open a DOS window would be to have it in the AUTOEXEC.BAT file.
This takes away conventional memory, even when you don’t have a DOS window open.
To only use DOSKEY when you open a DOS window:

Right click on your DOS shortcut
Select Properties
Click on the Program tab
Add DOSKEY > NUL to the Batch File field
You can also add /INSERT if you prefer