Up until SQL Server 2008, I didn't need PowerShell to manage my dozen servers and know exactly what was going on. Now I do.

And the benefits and capabilities are plentiful! You can start to be convinced by reading this article, "Why This SQL Server DBA is Learning Powershell", brilliantly conceived.

Next, use this site as a resource and examples.

Here is a great set of examples by MAK.

USE MyDatabase
GO

-- Windows 2000 / XP
-- Replace "{servername}" with your SQL Server name

EXEC sp_grantlogin [{servername}\ASPNET] 
EXEC sp_grantdbaccess [{servername}\ASPNET], [NETAPP]

-- GRANT EXECUTE ON [ProcedureName] TO [NETAPP]
grant exec on [dbo].[usp_My_Procedure] TO NETAPP 
GO

-- Windows Server 2003
-- Info.

EXEC sp_grantlogin [NT AUTHORITY\NETWORK SERVICE]
EXEC sp_grantdbaccess [NT AUTHORITY\NETWORK SERVICE]

GRANT EXECUTE ON [ProcedureName] TO [NT AUTHORITY\NETWORK SERVICE]
GO

1. http://en.wikipedia.org/wiki/RAID
2. http://www.raidarray.com/04_01_50.html

SELECT *   
FROM  OPENROWSET (
  'MSDASQL','Driver=Microsoft Visual FoxPro Driver;SourceDB=E:\DATA;SourceType=DBF;',
  'SELECT  * FROM MyFreeTable;'
)

Once the correct drivers were installed we could run the query, above, and create ODBC connections. Then, we were getting this error, but only when not running directly on the server itself.

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Visual FoxPro Driver]File 'hprofile.dbf' does not exist.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".

So, our OPENROWSET queries and the linked server queries could be run locally to the server. There must still be some proxy permissions or security something we still need to set. 

Still, another error we're getting is this: 
Cannot get the schema rowset 'DBSCHEMA_CATALOGS' for OLE DB provider "SQL Server" for linked server "(null)" -- huh?

This problem occurs occasionally after working for a period of time.

The only way I've found to resolve this is to restart the SQL Server and Agent processes.

I've tried refreshing the registry entry (See MS Article ID: 209805) for the file Mstext40.dll / HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text, without change.

Here's an example of the T-SQL query.

SELECT 
*
FROM   OPENROWSET('MSDASQL',  
'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=E:\MSSQL\;', 
'SELECT * FROM [Myfile#txt];'
)

Here's another version of the error.

Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'MSDASQL' reported an error.  
[OLE/DB provider returned message: 
[Microsoft][ODBC Text Driver] System resource exceeded.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' 
IColumnsInfo::GetColumnsInfo returned 0x80004005:   ].

PROBLEM Error message:

[OLE/DB provider returned message: [Microsoft][ODBC Text Driver] System resource exceeded.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005:   ]

SOLUTION:

I got this error running an import of a text file using a bcp format file and BULK INSERT. The solution was to use a different server. I'd run the command on a SQL Server 2000, Windows 2000 server. I seem to remember something about an issue about some DLL mismatch with the OS or another server. Or maybe even after we installed SSMS for SQL 2005.

Trying the same command on a Windows Server 2003 with the SQL 2000 instance (SQL 2005 instance was also on that server) worked like a charm.

SELECT 
*
FROM   OPENROWSET('MSDASQL',  
'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=E:\Data\;', 
'SELECT * FROM [ImportFile#txt];'
)

Find your connection seems to hang?

 The problem presents itself using SSMS, and as it turns out it is running something like
    saving auto recovery information

Change this registry setting to 0. This location was on the server.
    HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM\General\AutoRecover

On my PC it was
    [HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\General\AutoRecover]

Change this:

On SQL 2005 you have to tell SQL Server Agent / Properties / Alert System, which profile that you'd like to enable. Here's what it does.

e.g. and don't forget to restart SQL Agent.

USE [msdb]
GO
EXEC master.dbo.xp_instance_regwrite 
   N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', 
   N'DatabaseMailProfile', 
   N'REG_SZ', 
   N'MyDatabaseProfile'
GO

Here are some useful debugging information queries.

SET nocount ON
            USE master
GO
EXEC xp_msver
SELECT
    'Microsoft SQL Server ' +
            CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion') ) + ' ' +
            CONVERT(VARCHAR, SERVERPROPERTY('ProductLevel') ) + ' ' +
            CONVERT(VARCHAR, SERVERPROPERTY('Edition') ) + ' ' +
            CONVERT(VARCHAR, SERVERPROPERTY('EngineEdition') ),
    @@VERSION
GO
            PRINT '** Database master compatibility level:'
EXEC sp_dbcmptlevel master
GO
            

Here is a good resource to figure out what version of SQL you are running using the version number.

As a DBA you've gotta love Kimberly L. Tripp. Here is what she says.

8) Check/fix your internal fragmentation. OK, so this is the one thatreally helped the customer in Turkey. Often, when transaction logs arenot pre-allocated and/or when there's been a lot of autogrowths, thetransaction log can become internally fragmented. Internally yourtransaction logs are broken down into smaller more granular chunkscalled VLFs (Virtual Log Files). The size and number of VLFs you'llhave depends largely on the size that the chunk is when it's added toyou transaction log. If you add a new chunk to the transaction logwhich is 20MB (through autogrowth or through manual growth) then thenumber of VLFs that are added is 4. If you add a chunk which is greaterthan 64MB but less than or equal to 1GB, you'll add 8 VLFs. If you addmore than 1GB then you'll add 16VLFs. In general, most transaction logswill only have 20 or 30 VLFs - even 50 could be reasonable depending onthe total size of the transaction log. However, in many cases whathappens is that excessive autogrowths can cause an excessive number ofVLFs to be added - sometimes resulting in hundreds of VLFs. Having anexcessive number of VLFs can negatively impact all transaction logrelated activities and you may even see degradation in performance whentransaction log backups occur. To see how many VLFs you have solelylook at the number of rows returned by DBCC LOGINFO. The number of rowsreturned equals the number of VLFs your transaction log file has. Ifyou have more than 50, I would recommend fixing it and adjusting yourautogrowth so that it doesn't occur as fequently. To get rid of all ofthe execessive VLFs, follow these easy steps to shrink off thefragmented chunk and add a new, clean chunk to your transaction log:

1. Wait for an inactive time of day (ideally, it would be best toput the database into single user mode first) and then clear alltransaction log activity through a regular transaction log backup. Ifyou're using the simple recovery model then you don't need to do a logbackup... Instead, just clear the transaction log by running acheckpoint.

 BACKUP LOG databasename TO devicename

2. Shrink the log to as small a size as possible (truncateonly)

DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)

NOTE: if you don't know the logical filename of your transaction log use sp_helpfile to list all of your database files.

3. Alter the database to modify the transaction log file to the appropriate size - in one step

ALTER DATABASE databasename
MODIFY FILE 
( 
      NAME = transactionloglogicalfilename 
    , SIZE = newtotalsize
)

And, that should do it!

--klt

Given that, here is some of the code you might use to optimize your database log file internals. (VLF). I use my own sp__who2 but you just want to see who's in the database. The main feature of this script is it calculates the ALTER DATABASE log file recreate script -- as mentioned above -- so when you're done all the previous file growths have been beautifully reduced to this one growth. Instead of CHECKPOINT on FULL recovery databases you'll need to run a LOG backup.

SELECT 'USE ' + DB_NAME() + ';' + CHAR(13)+CHAR(10) +  
   'EXEC sp__who2 1,null,null,' + DB_NAME() + ';' + CHAR(13)+CHAR(10) +  
   'SELECT DATABASEPROPERTYEX(''' + DB_NAME() + ''',''Recovery'');' + CHAR(13)+CHAR(10) +  
   'DBCC LOGINFO;' + CHAR(13)+CHAR(10) +  
   'CHECKPOINT;' + CHAR(13)+CHAR(10) +  
   'DBCC SHRINKFILE(' + RTRIM(name) + ', TRUNCATEONLY);' + CHAR(13)+CHAR(10) + 
   'ALTER DATABASE ' + DB_NAME() + ' MODIFY FILE (NAME = ' + RTRIM(name) + 
   ', SIZE = ' + CAST(size/1024*8 AS VARCHAR(25)) + ');'+ CHAR(13)+CHAR(10)   +
   'DBCC LOGINFO;' 
FROM   dbo.sysfiles 
WHERE  FILEPROPERTY(name,'IsLogFile') = 1