Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Archives: January 2012

SQL Server – Send email using Powershell

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

0 comments, 918 reads

Posted in SQLServer-DBA on 31 January 2012

SQL server – ISNUMERIC and checking for valid numeric tyopes

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

0 comments, 467 reads

Posted in SQLServer-DBA on 30 January 2012

SQL Server - Generate SQL INSERT from Excel

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

0 comments, 488 reads

Posted in SQLServer-DBA on 27 January 2012

SQL Server – Error Logs recycle without SQL Server Restart

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

0 comments, 372 reads

Posted in SQLServer-DBA on 26 January 2012

SQL Server - BACKUP LOG WITH NO_LOG

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.

Of course , a SQL Server DBA knows that this breaks the Log… Read more

0 comments, 356 reads

Posted in SQLServer-DBA on 22 January 2012

SQL Server – Modifying Production Data Good Practise

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

0 comments, 405 reads

Posted in SQLServer-DBA on 21 January 2012

SQL Cachestore flush

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.

 Notes:

1)      … Read more

0 comments, 424 reads

Posted in SQLServer-DBA on 20 January 2012

SQL Server – Powershell Excel to HTML

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 .

 Any Powershell scripts using Excel as an end point can be converted to HTML with just a few… Read more

0 comments, 395 reads

Posted in SQLServer-DBA on 19 January 2012

SQL Performance Tuning - optimizing for response time

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

0 comments, 473 reads

Posted in SQLServer-DBA on 18 January 2012

Hungarian Notation is misunderstood

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.

Hungarian notation usage is characterised by variables using a group of lower case letters followed by a name given by the DBA\Developer\Programmer.… Read more

0 comments, 401 reads

Posted in SQLServer-DBA on 17 January 2012

SQL Agent Jobs – Schedule in seconds

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

0 comments, 562 reads

Posted in SQLServer-DBA on 16 January 2012

SQL Server – Logical Disk Read Bytes/sec and disk io

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

0 comments, 798 reads

Posted in SQLServer-DBA on 12 January 2012

SQL Server – Backup to NUL trick - read throughput

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

0 comments, 404 reads

Posted in SQLServer-DBA on 11 January 2012

SQL Server – Maintaining different environments

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

0 comments, 1,324 reads

Posted in SQLServer-DBA on 10 January 2012

SQL Server –WRITELOG and how to reduce it

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. “

A checkpoint writes all SQL dirty pages , currently in the buffer,  onto disk. Transaction commits make  data modifications… Read more

0 comments, 400 reads

Posted in SQLServer-DBA on 9 January 2012

Arithmetic overflow error and isdate sql

Executing a t-sql statement with a convert function

 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

0 comments, 593 reads

Posted in SQLServer-DBA on 6 January 2012

SQL Server Disable Indexes and Rebuild Indexes dynamically

To generate the ALTER INDEX..DISABLE  and  the ALTER INDEX..REBUILD statements for all nonclustered indexes for a single table use the following sql statements. 

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

0 comments, 557 reads

Posted in SQLServer-DBA on 5 January 2012

SQL SERVER REBUILD INDEX

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

 Notes

1)  Issuing an Index Rebuild request , drops and recreates the index

2) If  rebuilding a  CLUSTERED INDEX ,… Read more

0 comments, 486 reads

Posted in SQLServer-DBA on 4 January 2012

SQL SERVER DISABLE INDEX

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

0 comments, 315 reads

Posted in SQLServer-DBA on 3 January 2012

SQL Index Fragmentation and sys.dm_db_index_physical_stats

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

0 comments, 532 reads

Posted in SQLServer-DBA on 2 January 2012

Older posts