It says that running the query require SQL to read almost half a million pages. That's an awful lot. The fewer IOs done by a query, the better

Is that with...

TempDB -no. It's recreated every time the service starts.

Model - not unless you've made changes to it.

MSDB - yes. It's where your job definitions and history, backup histories, SSIS/DTS packages...

No. Those are execution statistics.

Maybe. What do you want the query to return?

What indexes do you have on the tables?

Can you give us the table creation script (CREATE TABLE ...), index definitions, sample data,...

This should give you several options

http://www.google.com/search?hl=en&q=Adventureworks+database&meta=

And if you run the hinted query along with the otiginal, how are the relative costs, how are the IOs (SET STATISTICS IO ON), how is the duration (SET STATISTICS...

Firstly, you can set the DB back to simple recovery mode. You can shrink the logs insimple, you just can't back them up.

The open tran info shows that the DB...

rbarryyoung (5/26/2008)

---

My Pet Peeve:

Posters who say "...this returns an error." or "...this doesn't work."

People; PLEASE do not leave it at this, give us ALL of the relevant information! If...

Then rather post in the SQL 2000 forum please. Posting in the 2005 forum, you will get just that, a useless suggestion that wasted both out times.

Now... Is the database...

I take it you didn't like the replies to your previous, identical, post

http://www.sqlservercentral.com/Forums/Topic506239-65-1.aspx

My guess is that the index is not covering (which with 300+ columns in SQL 2000 it can't...

Assuming 2005, query the sys.database view and check the value of the column log_reuse_wait_desc (or something similar). That tells you the reason that the log space cannot be reused.

Don't think so, but you can rename it. Not as good, but it does make it less obvious. Call it something like guest_user_testing or something like that and give it...

State 16 means that the login does not have rights on default db. That would explain why recreating the report service database fixed the issue.

mobasha (5/25/2008)

---

i have already convienced them to remove the sa, but still may some one ask for it as he knows that sa is the most powerfull user, so i...

You can disable the sa account. On SQL 2005, you can also rename it if you like (and create a new login named sa with any permissions you like), however...