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]
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
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
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
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…
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
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.
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
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…
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