If you’ve ever been a production DBA, or if you’ve worked on a mission-critical software application then you’ve come across a situation you don’t know how to handle…but you have to handle it five minutes ago and the clock is ticking. While most queries will turn up a slew of entries using Google (or another search engine to keep it fair), sometimes you just can’t find the answer you’re looking for, or you’re not sure if you can trust what’s been submitted to the forum you’ve landed on.

Enter the warchest: the collection of websites you know you turn to for definitive answers to even your most seemingly niche questions. Here are my favorites:

• SQLServerPedia (I’m not above shameless self-promotion)
• SQL Server Storage Engine
• PSS SQL Server Engineers (Troubleshooting)
  
• Kalen Delaney’s Blog (SQL Server Internals)
• Ken Henderson’s Blog (SQL Server Internals)
  
• Itzik Ben-Gan’s T-SQL Blackbelt SQLMag posts (T-SQL Programming)
• Tips, Tricks and Advice from the SQL Query Processing Team (Query Processing)

9 times out of 10, if you can at least narrow-down your question to one of the groups above (T-SQL, the Storage Engine, Troubleshooting, Internals), they’ll guide you to answers you can trust.

Q: Last night my SQL Server instance apparently froze – when I could login and sp_who2, I see GHOST CLEANUP as the top process using cpu. It is not killable. What is it doing, and how can I prevent it from running during critical time periods? (NOTE: this was on a virtual machine).

Bryan Oliver says: Here’s a couple of things that you might want to do and/or check to help get past this issue.

1. Have you got auto shrink turn on for any of the databases or have you got a schd task to run a shrink?

2. Are you getting errors in the SQL log? Sounds like if there is a Delete command pending which maybe causing the problem. Do a SQL Profiler at the times when the Ghost Clean up occurs (before if you can).

3. Also it may be worth running a DBCC Checkdb on the database that is causing the Ghost Clean Up process.

4. Check these items on the Microsoft knowledge base as well:
  http://support.microsoft.com/kb/931975
  http://support.microsoft.com/kb/815594

Kevin Kline says: The Ghost Thread is a system activity that monitors other normal & natural internal processes such as the lazywriter, checkpoints, and so forth.  I find it hard to believe that it’s consuming many of the system resources unless there’s a bug at play.  One such bug that comes into play is when you have memory problems associated with MemToLeave or with opening but not closing XML documents.  Make sure you’re up-to-date on the latest service pack for your version of SQL Server (you didn’t say which).

Finally, you can start-up SQL Server without a Ghost Process by using the -T661 startup switch.  I do not recommend this course of action, however, a Microsoft PSS rep may advise you to do so.  Without the Ghost Thread, you won’t see those omnipresent 4 or 5 activities in Current Activities (or via sp_who) since the Ghost Thread is what monitors those internal processes.

Hi,

I will be attending

Storage Expo in London

October 17th - 18th

http://www.storage-expo.com/

IT Forum in Barcelona

November 12th - 16th

http://www.mseventseurope.com/teched/07/itforum/content/Pages/Default.aspx

So, if you are attending, please come over to the Quest Software stand and say hello. We will be providing demos of Quest products, but if you just want to have a chat about SQL Server then that would be great!

 Iain Kick

Q:   My web hosting company has limited my SQL server size to 100 MB though the data there is only of 50 MB but what if the size keeps on increasing to 200 mb or something? How do I manage the space properly?

Bryan Oliver says:  It sounds like your log file is growing or the tempdb if you are using a lot of calls to tembdb.

If you are seeing large log file growth you can put your database in simple mode or look at doing frequent log backups to reduce the amount of growth that occurs between backups.

Technorati Tags:
database growth, log file, tempdb
Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |

Q:   How can I link a SQL Server database to MS Access using link tables in MS Access?

Bryan Oliver says:   Using access to query SQL Server Data - check it out:

Create the database manually (on SQL Server 2005), then right-click the DB (on SQL Server 2005) and choose Task|Import Data, drill into your Access DB and import the tables. Once imported you can create a Database diagram (on SQL Server 2005) since all constraints will now be handled by SQL server. (make sure you key all of the tables)

Then create an ODBC file DSN for this database connection (to SQL Server 2005), then fire up the Access database and right-click in the ‘Tables’ window, choose Link Tables, choose ODBC Databases() and then choose the DSN you created (to SQL Server 2005 database) and finally all of the tables you need to link.

You’ll want to rename the old tables to “tableName_old” then rename the linked tables removing the “dbo_” from the table names.

Linked server

You can add a Access database to a SQL Server database. Following steps are -

(1) Open EM.
(2) Goto the Server to which you want to add it as linked server.
(3) Then goto security > Linked Servers section from console tree.
(4) Right click on the Client area. Then New Linked Server.
(5) Give a name and Specify Microsoft Jet 4.0 as Provider string.
(6) Prvide the location of the MDB file.
(7) Click OK.

– OR –
Issue this statement in QA of SQL Server-

EXEC sp_addlinkedserver @server = ‘DBName’, @provider = ‘Microsoft.Jet.OLEDB.4.0′, @srvproduct = ‘OLE DB Provider for Jet’, @datasrc = ‘C:\MSOffice\Access\Samples\Northwind.mdb’

You have added it as linked server now. Then, use full qualified name to issue your statements.