ASP.NET News from Blogger:
SQL Server DBA
Total News: 148
Question: I cannot connect to Sql server remotely from management studio , It is the correct User name and password.Cannot connect to xxxx.xxxx.xxxx.xxxx===================================A network-re...
Question: This SQL Server Error message appears in the logs :Timeout occurred while waiting for latch: class 'LOG_MANAGER', id 000000001B8009F8, type 4, Task 0x0000000004ED9288 : 0, waittime 300, flag...
Question: This SQL Server Error message is appearing on a SQL Server Production Server Error Logs :The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your...
Question: Id like to create a regular SQL Server security audit. This information will be scanned by an internal audit tool . Could you recommend a list of SQL Server procedures to cover users and obj...
Question :This message appears in the SQL Server Logs Cannot use Large Page Extensions: lock memory privilege was not granted. Its a SQL Server 2008 64 bit on Windows 2008 R2 64-bit Operating System. ...
Question: How can I compare two files and report on differences using Powershell ? I have two large T-SQL scripts , which both parse successfully on SSMS , but give me different resultsAnswer: Compari...
Question: How can I export a Powershell recordset to a CSV file format, so I can then open the file in MS Excel?Answer: Powershell has the Export-CSV cmdlet . The Export-CSV cmdlet creates a CSV file ...
Question:I started seeing the SDD_Servicemigrating physical servers to Virtual Servers (VMWare 4.1), as part of Virtualization and database servers project7011Server1ErrorTimeout (30000 milliseconds) ...
Question : When a CREATE INDEX is executed on an existing table with rows in it, is it required to execute a UPDATE STATISTICS or will CREATE INDEX update the stats? Does the STATISTICS_NORECOMPUTE ef...
Question:
Some DDL changes have occurred on the SQL Server database. Can I find out who made the changes?
Answer:
Yes. The SQL Server default trace has the Object Altered event.
Read this F...
Question: How do I modify a SQL Server CHECK CONSTRAINT?
Answer : To modify a CHECK constraint, you must first delete the existing CHECK constraint and then re-create it with the new definition
An exa...
Question : The error 26073 - TCP connection closed but a child process of SQL Server may be holding a duplicate of the connection's socket appears in the SQL Server Error Logs. How can I fix it ?Answe...
Question: Users were complaing of very slow SQL Server database performance . Using this query through the default trace , I identified some long running LOG FILE AUTOGROWTH activity. Why is it so slo...
Has someone deleted a table?Are you trying to track auto grow events? Problem scenarios such as Database autogrow and slow database recoveryWhen did SQL memory usage change? Read SQL Server Performanc...
Q. How can I obtain the members of a role within a database?A. One method is to use sp_helprolememberEXEC mydatabase.dbo.sp_helprolemember
--returns
DBRole MemberName MemberSID
My_Admin_Role DOMAI...
As a follow up to an earlier posts - SQL Server - BACKUP LOG WITH NO_LOG As of SQL Server 2008 , BACKUP LOG with TRUNCATE_ONLY is discontinued. I still find plenty of SQL Server maintenance scripts us...
This error message can appear in the SQL Server Error Log files .Autogrow of file 'MY_DB' in database 'MY_DB' took 455320 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for t...
Event ID 7026 and i8042prt is a recurring error in the Windows Event Logs. I usually see this error on a server reboot .Its normal for a server to not have a keyboard or mouse attached. I usually supp...
The System logs returned :The device, xxxxxxxxxxxxxx, did not respond within the timeout period.Some common causes1) SCSI controller firmware2) Incorrect transfer settings3) SCSI Cabling4) Other contr...
Transactions generate IO, latching and locking on tables and indexes , while attempting to accessdata. The sys.dm_db_index_operational_stats DMV returns aggregated data on this activity.Warning : sys....
Specialise or a generalise? It all depends on how you define these terms.A SQL Server DBA may be considered to be a specialist by a Enterprise Architect but a generalist by a SSIS expert.The DBA is cu...
When log records remain active for a long time truncation can be delayed. The delays can cause the transaction logs to fill. This can lead to a Error 9002In the Full Recovery mode , a Log Backup will...
On a routine scan of Event Viewer System, Logs I found this message on 4 different servers hosting SQL Server installationsThe kerberos client received a KRB_AP_ERR_MODIFIED error from the server MYCO...
To display network connections and various network interface statistics netstat is a handy command line based tool. Why is it useful for the DBA ?1) Execute netstat an to view all active connections...
Use the system view sys.master_files for a single view of databases and database files.Instead of using sys.sysfiles joing to sys.sysdatabases use the sys.master_filesselect DB_NAME(database_id),mf.na...
1) SQL Server 2008 R2 Reporting Services doesnt support Windows 2003 (or R2) Itanium based servers2) SQL-DMO support is stopped in SQL Server 2008 Express3) Cannot enable Web Assistant with sp_configu...
Common reasons for query time-outs are :1) The application starts using a query not optimal for the index2) Hardware changes\ Configuration changes3) Increased loadIf you suspect the query time-out is...
Managing a large SQL Server inventory requires an efficient management processes.Following on from DBA productivity and less is more , this post outlines the system I use to manage the DBA scripts acr...
To list a SQL Server Agent Job Steps use the T-SQL example below.This example displays the step id, SQL Server Agent Job name, Step name.The code has 1 input parameter - @job_nameDECLARE @job_name VAR...
This post explains how to list Event Log Messages with Powershell Get-EventLog , on multiple servers and output the results to a HTML file.It is only one extra step to send an email attachment.This ex...
Developers constantly ask about placing the latest code\feature\process\third party applications onto Production Database Servers.Im building an FAQ to supply Developers as this could save me having ...
Working with Powershell and Active Directory simplifies some complex tasks for the DBA.
Active Directory is LDAP compliant. This means the RFC 1779 and RFC 2247 standards are met.
This example l...
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 01 = read only0 = read wri...
A typical DBA day can include a mixture of Operational, Engineering and Architectural tasks. Whilst maintaining 100% database server availability and acceptable performance levels.Im always looking fo...
This post discusses some methods in configuring the Powershell default start directoryBy default , the installation process will add a value to the Start In parameter.When you start Powershell , the d...
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 late...
This message appears in the TDP SQL dsmsched file. Its usually associated with low levels of contiguous memoryBackupVirtualDeviceSet::SetBufferParms: Request large buffers failure on backup device 'TD...
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 us...
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 ...
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 ...
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
/***...
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 Smtp...
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?????...
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...
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 Err...
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 suggesti...
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 dat...
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 Plan...
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...
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 ...
View Other bloggers