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

SQL Server – read only database status

This post describes how to return the database read-only status via T-SQL .

The system view – sys.databases  returns a column called is_read_only.  

The column returns either 1 or 0

 1 = read only

0 = read write

   SELECT [name] ,is_read_only  
   FROM sys.databases  
   ORDER BY [name]

 

See Also

SQL… Read more

0 comments, 555 reads

Posted in SQLServer-DBA on 17 February 2012

SQL Server – DBA productivity and less is more

A typical DBA day can include a mixture of Operational, Engineering and Architectural tasks. Whilst maintaining 100% database server availability and acceptable performance levels.

I’m always looking for method to focus my efforts.  Automation, documentation , reporting, monitoring are all ways of simplifying the mountains of information. S

Inspired by… Read more

0 comments, 292 reads

Posted in SQLServer-DBA on 15 February 2012

Powershell default start directory

This post discusses some methods in configuring the Powershell  default start directory

 By default , the installation process will add a value to  the Start In parameter.

When you start Powershell , the default path will be  %HOMEDRIVE%%HOMEPATH%.

 Without opening Powershell , view the variable values through the DOS cmd… Read more

0 comments, 723 reads

Posted in SQLServer-DBA on 10 February 2012

SQL Server Product Life Cycle

Part of managing the SQL Server environment , is maintaining the latest Service Packs remain within the Product Support  data range.

The Microsoft web site has a search feature to return the latest dates  about: General availability, maintstream support and Service Pack Support End Dates



http://support.microsoft.com/lifecycle/search/default.aspx?sort=PN&alpha=sql+server+2005&Filter=FilterNO

Read more

0 comments, 377 reads

Posted in SQLServer-DBA on 9 February 2012

Not enough storage is available to complete this operation

This message appears in the TDP SQL dsmsched file.  It’s usually associated with low levels of contiguous memory

BackupVirtualDeviceSet::SetBufferParms: Request large buffers failure on backup device 'TDPSQL-00001BDC-0000'. Operating system error 0x8007000e(Not enough storage is available to complete this operation.). A nonrecoverable I/O error occurred on file "TDPSQL-00001BDC-0000:" 995(The I/O operation…

Read more

0 comments, 1,341 reads

Posted in SQLServer-DBA on 8 February 2012

Send email from Powershell with attachment

This post  describes how to call  to add an email attachment using Powershell.

In an earlier post - SQL Server – Send email using Powershell – the new-object Net.Mail.SmtpClient() Class was used.

To add an email attachment , use New-Object System.Net.Mail.MailMessage and New-Object System.Net.Mail.Attachment, which are part of the .Net… Read more

0 comments, 3,998 reads

Posted in SQLServer-DBA on 7 February 2012

List failed SQL server Jobs with Powershell

This post explains how to list failed SQL Server Jobs , on multiple SQL Server Instances and output the results to a HTML file.

 It is only one extra step to send this file as an attachment.

The example script , returns failed SQL Server Agent Jobs within the last… Read more

0 comments, 1,680 reads

Posted in SQLServer-DBA on 6 February 2012

List SQL Server Instances using Powershell and Get-ItemProperty

This post explains how to list SQL Server Instances installed on the current server, using the Powershell Get-ItemProperty Cmdlet.

The Get-ItemProperty Cmdlet gets the properties of a specific item.

In this example, the command displays the Name and Data from the registry entries in the SQL registry subkey.Notice the  Powershell… Read more

0 comments, 817 reads

Posted in SQLServer-DBA on 4 February 2012

SQL Server – COPY TABLE from another SQL Server

To copy a table from another SQL Server Instance is easy. Create a Linked Server on the destination server and reference the Linked Server in the SELECT statement.

1) Create linked server

/****** Object:  LinkedServer [SERVER1\SQL_SERVER_INSTANCE_1]    Script Date: 02/02/2012 09:29:33 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SERVER1\SQL_SERVER_INSTANCE_1', @srvproduct=N'SQL Server'
/* For…

Read more

0 comments, 506 reads

Posted in SQLServer-DBA on 2 February 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, 968 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, 499 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, 512 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, 384 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, 399 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, 436 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, 457 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, 423 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, 500 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, 434 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, 596 reads

Posted in SQLServer-DBA on 16 January 2012

Newer posts

Older posts