Archives: January 2012
This article explains how to send an email through Powershell.
Very useful if you have a requirement to automate your scripts and send notifications .
The example is calling the SmtpClient Class , which is part of the .Net Framework.
You can call a Function – which makes your scripting … Read more
During a large ETL process from a staging table , UPDATE was creating an error on a CONVERT.
Msg 245, Sev 16, State 1, Line 17 : Conversion failed when converting the nvarchar value '??u? ? ? ?AA???????????W?????????a??????K???????????????????1217' to data type int. [SQLSTATE 22018]
It was necessary to create… Read more
Generate SQL Server INSERT statements with Excel Concatenate.
A developer requested access to a Production database. The details were to COPY and PASTE from a Excel spreadsheet into Access , which then updated SQL Server. Upon asking him , why he used this method , he said “It’s the way… Read more
If some long running error situation , the Error Log can become very big. In those situations , it can be time-consuming to search for specific Messages.
It is a good idea to recycle the SQL Server Error Logs . To create a new Error Log file without restarting SQL… Read more
The BACKUP LOG WITH NO_LOG and WITH TRUNCATE_ONLY options are discontinued in SQL Server 2008.
There is no replacement for this functionality. When the command is executed there is the message suggesting to switch to Simple Recovery.
Q. “I need to modify Production Data directly on a Production database. What are some steps I can follow to ensure rollback is possible? Also, some steps to ensure I can capture mistakes before the data becomes invalid “
A. Here are some suggestions
1. Take a Log Backup… Read more
For each cleared cachestore in the plan cache SQL Server reports a message in the error logs , such as :
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
1) … Read more
SQL Server, Powershell and Excel are a good mix . But saving to Powershell output to HTML is versatile – if requiring email attachments or publishing results to a web page .
Different queries are optimized for different objectives
Response Times and Throughput are two standard objectives.
Response Time is about retrieving the hits as quickly as possible. In a search engine , that would mean returning the first few rows immediately
Throughput is about returning ALL the hits (rows) in the… Read more
Hungarian notation refers to a identifier naming convention. The variable indicates the variables use – as opposed to what they are. Hungarian notation is often applied incorrectly.
1) How can I execute a SQL Server Agent Job every few seconds? Administrators try to use SQL Server Agent as a real-time scheduler - and will attempt to exploit the in- build scheduler functionality.
2) I wouldn’t recommend SQL Server Agent for sub-minute frequency scheduling. There are better suited tools for… Read more
In the SAN environment , the Logical Disk Performance objects counters monitor the logical partitions.
I use the Logical Disk Read Bytes/sec as a starting point when I troubleshoot Disk IO issues. If I can isolate the activity , this gives me a good guideline as to Read rates from… Read more
A neat test for read speed backup throughput is to use the Backup to NUL .
NUL is a special “file”. It's as a nul device , anything “written” to it is discarded.Don’t confuse with the SQL reserved word NULL.
Before you run this statement , note that even as the… Read more
A busy Database Server and application development environment can have – Sandbox, Dev, Test, QA, Staging, Production .
Maintaining multiple environments requires consideration of various factors.
Reasons for maintaining different environments
Part of well audited database server system includes Segregation of Duty. Part of Segregation of duty is to define… Read more
SQL Server Books online defines the wait type WRITELOG as “Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits. “
convert(datetime, my_date_column) ,
threw an arithmetical overflow error with the message :
Message Arithmetic overflow error converting expression to data type datetime. [SQLSTATE 22003] (Error 8115) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
Use… Read more
Copy and Paste the statements , execute on the database. I’ve included the URL column for easy click through to some notes on the code
1) Generate… Read more
Use the script below to rebuild an index on a table
ALTER INDEX MY_INDEX_NAME on MY_TABLE_NAME REBUILD ;
Check SQL Server BOL for a the full list of options
1) Issuing an Index Rebuild request , drops and recreates the index
2) If rebuilding a CLUSTERED INDEX ,… Read more
Use the script below to disable an index on a SQL Server table
ALTER INDEX MY_INDEX_NAME on MY_TABLE_NAME DISABLE;
Executing the code will prevent access to the index.
If it’s a CLUSTERED INDEX , the data remains intact on the table , but no DML can access the data Read more
Fragmentation of an index can severely affect performance. When logical ordering of the key within a page does not match the physical ordering within the data file, fragmentation exists.
I execute index maintenance scripts for databases on a regular basis. If I’m executing a custom job, such as a large… Read more