Today I had the opportunity to present on TempDB to the Spokane SQL Server users group (PASS Chapter). The session was titled TempDB – Do This and Don’t Do That”, and it covers a bunch of tips and best practices around what to do and avoid relating to TempDB on your SQL Server. Here is the download of the presentation and …
A while back a wrote a blog post with a query to Visualize the VLF’s in your database. Today I have an update to that script. Here is an updated script that has adds another column called “TextStatus” to the output to give you a better idea of what the different statuses mean. You now get 3 statuses shown, “In …
One of my favorite queries this week is the following query that creates a text based bar chart to quickly help visualize the VLF files on any database log file. In the last month I have given the “TempDB Do This and Don’t Do That” presentation twice, once at the Bellingham SQL Server users group, and another time at SQL …
Today I am presenting my “TempDB – Do This and Don’t Do That” session at SQL Saturday Redmond. Here is the download of the presentation and samples. StedmanTempDbPresentation.zip Some of the sample code from the session: I hope you enjoy the session.
Since the corruption challenge completed yesterday, I have had several request asking how I created the corrupt database. So here is the script that I used to create the Database Corruption Challenge 1. First the initial setup. Most of this I stole from a query training session that I did several weeks ago. All I really needed was a table …
Corruption Challenge 1 – how I corrupted the database Read more »
Being day 22 of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC OUTPUTBUFFER. I missed a few days on the DBCC Commands due to attending SQL Saturday in Redmond, and the release of my book on Amazon.com. I am now back on track to finish out the rest of the month with more DBCC commands. Description: …
Being day six of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC CHECKIDENT. Description: DBCC CHECKIDENT is used for check on the current value in the identity column for a table. It also reports on the largest value in that column. DBCC CheckIdent can also be used to update or set the next identity value on …
Here is a script that I created to get the size of all of the databases on one SQL Server. Generally I stay away from temp tables, especially global temp tables, but I didn’t see a good way to do this without them. CREATE TABLE ##alldatabasesizes ( dbname VARCHAR(1024), type_desc VARCHAR(1024), name VARCHAR(1024), size INTEGER ); EXECUTE Sp_msforeachdb ‘INSERT INTO ##AllDatabaseSizes SELECT db_name() as dbName, type_desc, name, size FROM [?].sys.database_files’ SELECT * FROM ##alldatabasesizes; DROP TABLE ##alldatabasesizes
Working on a new report for the SQL Server Health reports, I needed to display the amount of free disk space on a SQL Server. EXEC MASTER..Xp_fixeddrives; Which was useful if I just wanted to look, but I needed to use the results in a query, and I didn’t want to put the results into a temp table, so here is …
I needed a way to list of of the indexes for an entire database, but I was running into a problem using sys.indexes that I could only see the indexes for the current database. Below is the solution that I put together to list off of the indexes in the entire database. CREATE TABLE ##allindexes ( databse_id INTEGER, table_id INTEGER, index_id INTEGER, table_name VARCHAR(1024) ); EXECUTE Sp_msforeachdb …
Finding the names of all indexes on a SQL Server Read more »