|
|
Inspired by Jason Strate’s (Blog| Twitter) post “PowerShell: Download List of SQL Server MVPs”, which was inspired by John Samson’s (Blog| Twitter) post “The Best Database Administrators Automate Everything” – which also inspired me. I decided to create a Powershell script to grab my starred items in Google Reader and format them into an HTML Page with links for my Weekly Challenges/Good Reading Posts.
The Set Up
I had an HTML template created for these pages, so I split it into three parts:
- The portion above the Good Reading and Links – I saved this to a text file located at “c:\temp\TopTemplate.txt”
- The Good Reading and Links
- The portion below the Good reading and Links – I saved this to a text file located at “c:\temp\BotTemplate.txt”
I then looked at my public page for Starred Items on Google Reader. Note this script works for any public listing on Google Reader, so I can create a Public Page and change the URL in this script and it still works.
The Code
##------------------------##
##--Reader Starred Items--##
##------------------------##
$webclient = New-Object system.net.webclient
$SourceFile = "c:\temp\Reader.txt"
$HTMLPage = "C:\temp\GoodReading.txt"
$TopTemplate = "c:\temp\TopTemplate.txt"
$BotTemplate = "c:\temp\BotTemplate.txt"
$url="http://www.google.com/reader/shared/user/14135896925018987441/state/com.google/starred"
$webclient.DownloadFile($url,$SourceFile)
#Get the titles with links
$itemtitle = Select-String $SourceFile -pattern "h2 class=""item-title""" |
% {$_ -replace "<div id=""items""><div class=""item""><h2 class=""item-title""><div class="""">", ""} |
% {$_ -replace "</div></h2>", ""} |
% {$_ -replace "<div class=""clear""></div></div></div> <div class=""item""><h2 class=""item-title""><div class="""">", ""} |
% {$_ -replace "c:\\temp\\Reader\.txt:[0-9]*:", ""}
#Get the details with links
$iteminfo = Select-String $SourceFile -pattern "<div class=""item-info"">" |
% {$_ -replace "c:\\temp\\Reader\.txt:[0-9]*:<div class=""item-info"">", ""} |
% {$_ -replace "</div>", ""}
$i=0
$FinalContent = " "
foreach ($t in $itemtitle)
{$FinalContent= $FinalContent +"<li>"+ $itemtitle[$i] + " " + $iteminfo[$i]+"</li>" + "`r`n "
$i++}
$FinalContent = "<p><strong>Good Reading from the Week</strong>:</p>" + "`r`n" + "<ul>" + "`r`n" + $FinalContent + "`r`n" + "</ul>"
$TopTemp = Get-Content $TopTemplate
$BotTemp = Get-Content $BotTemplate
$FinalContent = $TopTemp + $FinalContent + $BotTemp
Set-Content $HTMLPage $FinalContent
invoke-item $HTMLPage
When the script completes, I simply update a couple of statuses and change the photo and it’s good to go!
I’ve got a new step in a job that I’m concerned about the duration. Here’s a script to get the duration of a specific step of a job. Note I found that whole conversion of the Run_DateTime column from Ramblings of a DBA – Tara Kizer – kudos, Tara.
Declare @JobName varchar(255),
@StepName varchar(255)
Set @JobName = '<<Enter Job Name Here>>'
Set @StepName = '<<Enter Job Step Name Here>>'
Select top 1000 run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
run_duration = Left(RIGHT('000000' + CONVERT(varchar(6), run_duration), 6), 2)+':'
+ Substring(RIGHT('000000' + CONVERT(varchar(6), run_duration), 6), 3, 2)+':'
+ Right('00' + CONVERT(varchar(6), run_duration), 2),
SJ.name,
SJH.step_name
from msdb.dbo.sysjobs SJ
Join msdb.dbo.sysjobhistory SJH on SJH.job_id = SJ.job_id
where SJ.name = @JobName
and SJH.step_name = @StepName
Order by run_datetime desc --Use this to find the most recent
--Order by run_duration desc --Use this to find the slowest
When someone states that adding a column to an existing table is easy and you need a list of risks, feel free to copy and paste this, add your own, etc. In fact, contact me via email, comments, twitter – @SQLQuill with any additions. Adding a column to an existing table – in theory and best practices world is easy – but in reality it is not always as easy as ALTER TABLE ADD COLUMN. The risk is that any procedure, view or application code that uses a “Select *” or an “Insert <TableName> Values <…>” (without column names explicitly stated) may not function, or may not function correctly, with an additional column added to the table. Also note there could be temp tables, table variables and table value parameters built off the table that an additional column affects as well. Best practices and my database development guidelines says not to use the “Select *” or the “Insert <TableName> Values <…>”.
A quick, simple and “easy” way to find deadlocks I’ve found is to do the following:
- Turn on the trace flag 1222
- Wait for a deadlock to happen
- Open query analyzer and run the following script:
Create Table #ErrorLogTable
(
LogDate DATETIME NOT NULL,
ProcessInfo VARCHAR(75),
LogInfo VARCHAR(MAX)
)
SET NOCOUNT ON
INSERT INTO #ErrorLogTable
EXEC xp_readerrorlog
Select
*
-- , Left(right(loginfo, Len(loginfo) - 20), charindex(' ',(right(loginfo, Len(loginfo) - 20))))
from #ErrorLogTable
where Logdate > getdate() - 1 --'2/11/09 12:00 AM'
and ProcessInfo not in ('Backup', 'Logon')
and loginfo not like 'This instance of SQL Server has been using a process ID %'
--and loginfo like '%procname%'
--and LogDate > '2010-05-06 11:25:15.780'
order by Logdate
--Drop table #ErrorLogTable
To Get the Objects involved:
- Uncomment the line “and loginfo like ‘%procname%’”
- Uncomment the line ”Left(right(loginfo, Len(loginfo) – 20), charindex(‘ ‘,(right(loginfo, Len(loginfo) – 20))))”
- Use the Logdate to limit your results.
Here’s a simple script that I use often – a quick hitter to find table row counts, that doesn’t use the Select Count(*). It uses the sysindexes table thus the reason it’s called “fairly accurate”.
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 DESC
/*
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Creates a header for scripts --
-- Pastes the results of this into a script --
-- --
-- Have the results returned to text --
-- Change the maximun characters return to 700 --
-- --
-- Created by Andy Lohn on 12/15/09 --
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*/
Set nocount on
Declare @Comments1 varchar(74),
@Comments2 varchar(74),
@Comments3 varchar(74),
@Comments4 varchar(74),
@Comments5 varchar(74),
@Comments6 varchar(74),
@Comments7 varchar(74)
Select @Comments1 = 'This creates a simple header for scripts'
Select @Comments2 = '<<<fill in comments/notes here>>>>'
Select @Comments3 = '<<<fill in comments/notes here>>>>'
Select @Comments4 = '<<<fill in comments/notes here>>>>'
Select @Comments5 = '<<<fill in comments/notes here>>>>'
Select @Comments6 = ''
Select @Comments7 = 'Created by Andy Lohn of Lohn Solutions on ' + Convert(varchar(20), Getdate(), 1)
Select '/*' +
Char(13) + Char(10) +
Replicate('-', 80) +
Char(13) + Char(10) +
'-- ' + @Comments1 + SPACE(74 - Len(@Comments1)) + '--' +
Char(13) + Char(10) +
'-- ' + @Comments2 + SPACE(74 - Len(@Comments2)) + '--' +
Char(13) + Char(10) +
'-- ' + @Comments3 + SPACE(74 - Len(@Comments3)) + '--' +
Char(13) + Char(10) +
'-- ' + @Comments4 + SPACE(74 - Len(@Comments4)) + '--' +
Char(13) + Char(10) +
'-- ' + @Comments5 + SPACE(74 - Len(@Comments5)) + '--' +
Char(13) + Char(10) +
'-- ' + @Comments6 + SPACE(74 - Len(@Comments6)) + '--' +
Char(13) + Char(10) +
'-- ' + @Comments7 + SPACE(74 - Len(@Comments7)) + '--' +
Char(13) + Char(10) +
Replicate('-', 80) +
Char(13) + Char(10) +
'*/'
--This will give you the amount of disk space that each table uses in a database.
SET NOCOUNT ON
DECLARE @cmdstr varchar(100)
DECLARE @Sort bit
CREATE TABLE #TempTable
( [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)
)
SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''
INSERT INTO #TempTable EXEC(@cmdstr)
--Use one of these depending what you are looking for.
--SELECT * FROM #TempTable ORDER BY Table_Name
SELECT * FROM #TempTable ORDER BY Len(Table_Size) DESC, Table_Size DESC
--DROP TABLE #TempTable
Seven base SQL Servers used for the web application:
- 3 – Clustered, SQL 2005 SP3 Enterprise edition
- 2 – Clustered, SQL 2005 SP3 Standard edition
- 2 – Stand Alone, SQL 2005 SP3 Standard edition
Three Log Shipping SQL servers:
- 1 – Clustered, SQL 2005 SP3 Enterprise edition
- 2 – Stand Alone, SQL 2005 SP3 Standard edition
One Utility Clustered Standard edition:
- 1 – Clustered, SQL 2005 SP3 Standard edition
I was the lead DBA on the most recent release of our newest version of our website. We moved all the databases to new servers and made a bunch of updates. Here were some of the challenges:
- Total data size – 2.2 TB of data
- SQL Servers crossing 4 domains and still communicating
- Changes to environment:
- Code changes for new functionality
- SQL2005 SP3 applied
- Litespeed upgraded from version 4.8 to 5.02
- New SQL Server in the environment and one DB moved to it
- Maintenance window reduction to 4 hours per week
- Security – Kerberos, double hop,
- Tightened,
- Changes in how hot fixes are deployed
- Tools – I3, Litespeed
- CPU Spikes post deploy – scheduled procedure wasn’t tuned properly
- LogShipping to get large DBs ready for deployment
- LogShipping Issues post deploy – restores missing files, manually restoring a log or two every few hours to keep things in sync.
Here are the current SQL 2005 Technologies that we are using in production:
- Clustering
- Partitioning
- Fragmentation based re-indexing
- Synonyms
- LogShipping with both native and LiteSpeed
|
|
