Multiple SQL Server management

Our company already has an infrastructure monitoring tool, and it plays very nicely with SQL Server versions 2000 through 2008. It is primarily a monitoring tool, and so is somewhat limited on historical reporting or trending.

I’m currently working on an SSIS package that I can run on a schedule from a central SQL Server that will go and gather data from my other servers. One function that I’ve been testing is the sp_readerrorlog procedure to capture and then merge the SQL Log files into a single table.

Other options I’m looking to start include an hourly file sizing report by database. This would allow me to make some fancy trending reports to the management types so they can watch the growth of their data.

Another is performance information like Plan Cache and Buffer Cache, User connections, Agent Job history… etc.

Get size of tables in DB

Here’s how I query to get sizing info for each table in a database


BEGIN TRAN GETTABLESIZES

DECLARE @TempSIZINGTable  TABLE
( [Table_Name] varchar(50),
Row_Count int,
Table_Size varchar(50),
Data_Space_Used varchar(50),
Index_Space_Used varchar(50),
Unused_Space varchar(50)
)
INSERT INTO @TempSIZINGTable EXEC('sp_msforeachtable ''sp_spaceused "?"''')

/*
sp_spaceused adds a KB to the end of the sizing fields
we use substring and a Pattern Index search for a space,
so that 1024 KB turns into 1024 which we convert to an int
for sorting
*/

SELECT [TABLE_NAME],
[ROW_COUNT],
CAST(substring( [TABLE_SIZE],1,PATINDEX('%[ ]%', [TABLE_SIZE])-1) AS INT) AS [TABLE_SIZE in KB],
CAST(substring( [DATA_SPACE_USED],1,PATINDEX('%[ ]%', [DATA_SPACE_USED])-1) AS INT) AS [DATA_SPACE_USED in KB],
CAST(substring( [INDEX_SPACE_USED],1,PATINDEX('%[ ]%', [INDEX_SPACE_USED])-1) AS INT) AS [INDEX_SPACE_USED in KB],
CAST(substring( [UNUSED_SPACE],1,PATINDEX('%[ ]%', [UNUSED_SPACE])-1) AS INT) AS [UNUSED_SPACE in KB]
FROM @TempSIZINGTable
ORDER BY [TABLE_SIZE in KB] desc

COMMIT TRAN GETTABLESIZES

Beef Stew

2 lbs Stew Meat

3 strips thick cut bacon

6 large potatoes cut into 1″ cubes (I use Idaho Russets – the starch helps thicken the stew)

18 baby carrots or carrots sliced into equal bite size pieces

1 medium onion

8 oz can tomato paste

2 cups low sodium beef stock (chicken stock or veggie stock will work fine)

1 cup quality red wine

Preheat oven to 300.

Cut bacon into 1/2 strips, fry in heavy pan, remove when browned – SAVE

Season beef with Pepper, Garlic Powder, Emeril’s Seasoning.  Sear beef over medium high heat in bacon fat in batches.  Last batch add 1/2 to 1 cup of good red wine to deglaze.  Place seared beef in tin foil pouch, pour remaining sauce / frond into pouch.  Seal tightly – I used 5 large pieces of foil and ended up with a foil burrito.

Place pouch of beef into oven on pan.  Braise in oven for 1 and 1/2 hours.

Remove pouch, poke a hole and drain juice into round heat proof container.  Cover juice and place in refrigerator.  Let sit in fridge for 1 hour.  Leave beef pouch out at room temp during the hour.

Preheat oven to 350.

Remove cold juice, you should have a “puck” of fat that has congealed on the top of the container.   Remove, and cut 1/4 of fat add to heavy pan.   Save remaining fat for other uses.  Add sliced onion to hot fat and saute for 3 minute until translucent.

Add potatoes to dutch oven, add onions, season.  Add carrots.  Add remaining beef sauce.  Add beef.  Add tomato paste.  Add diced potatoes.   Add reserved bacon bits.  Add beef stock.  Stir to mix well.  Place lid on dutch oven (if lid fits loosely, cover dutch oven with tin foil first and then place lid on top) and place in center of oven for 2-3 hours… until potatoes are fork tender.

Season to taste and allow to cool to serving temp.

Options:

Braise beef the day before, and keep wrapped tightly in foil after draining sauce.  Place beef in freezer over night, thaw the next day.  The freezing will help to gelatinize the connective tissues of the beef making it even more tender.

Skip the deg-lazing with wine if that’s not your thing.

Add frozen peas or other veggies.