To shrink tempdb without a restart, run DBCC SHRINKFILE against each tempdb file with a target size in megabytes. This post checks the file sizes and the space use first, shows the statement with its undo, and tests the shrink rules on a demo database, so the tempdb server stays untouched.
Show Primary Key of a Table in SQL Server With sp_pkeys
To show primary key columns for one table, call sp_pkeys with the table name. This post builds a small demo database, reads a single column and a composite key, and handles schemas and errors. It also lists every key in a database with one catalog query.
Signal Waits: Detect CPU Pressure With Wait Statistics
Signal waits are the part of every wait that a task spends in the queue for a free CPU. This post ranks the wait types by signal time, shows why timer waits ruin the total share, and samples the signal share into a small table so a whole working day can be read interval by interval.
How to Skip Top N Rows in SQL Server? – Interview Question of the Week #237
Question: How to skip top N Rows in SQL Server and retrieve the rest of the rows? Answer: I had asked the above question in one of the interviews last week.
SQL SERVER – Patch Install Rule Error – Not Clustered or the Cluster Service is Up and Online
The error which we observed was a failure of a rule – Patch Install Rule Error – Not clustered or the cluster service is up and online.
Delete the Wrong Row: A Cheap Answer That Passes the Test
A cheap answer to the multiple table delete puzzle reads only 6 pages and passes the check. It can still delete the wrong row. This post shows the answer, asks you to find the row it removes by mistake, and fixes it with a delete that costs 5 reads.
Max Worker Count: Why Typing 10000 Slowed a Server
Max worker count looks like a speed dial but behaves like a safety limit. A client typed 10000 on an 8 processor server and slowed it, and a reset to 0 fixed it in minutes. Two read-only queries show your value and whether workers run short.