Tips
Administering your Windows Internal Database MICROSOFT##SSEE instance
Microsoft products such as Windows Server Update Services (WSUS) 3.0 and Windows Sharepoint Services (WSS) 3.0 ship with SQL Server 2005 Embedded Edition. Now called the Windows Internal Database, more and more system administrators charged with managing WSUS and WSS are faced with the challenge of managing these databases. Since most of these system administrators are not full-fledged DBAs, how do they manage the Windows Internal Database?
Attach a SQL Server database with a missing transaction log file
The development team approached me with an MDF file of a SQL Server database which they wanted to restore on their server. The developer had some very critical and complex stored procedures which were stored in the ProductsDB.MDF file. However, since this database was kept offline; during the disk cleanup unfortunately one of the developers deleted the transaction log file for this database. As this was a development server there were no database backup jobs scheduled. Since, this database was very critical for the development team they wanted to know if there was a way to recover and use this database. In this tip, we will cover how to attach a database that does not have its transaction log files.
Auto generate SQL Server database attach and detach scripts
Often, you have a SQL Server instance containing many databases. It may happen that over time, you have added disk drives or LUNs and placed different database files on separate disk letters. Now you need to know which databases reside on which drive. You may require this because a drive should be replaced and you need to know which databases will be affected or maybe you need to detach all databases from a drive(s) and copy the contents of the entire drive letter to another server (for example, cloning of Production LUNs into a DEV/QA environment). Another reason may be that you need to map the database locations on the drives for DR purposes, in case you need to rebuild the server from scratch In this tip we look at how to find what d
Capture and Store SQL Server Database Integrity History using DBCC CHECKDB
We have all used the command DBCC CHECKDB from time to time in our DBA careers to verify database integrity and repair corrupt databases. The latest version of this command checks the logical and physical integrity of all objects in the specified database. The standard output can be quite extensive, not formatted for easy analysis, and often is not persisted in any useful way for historical analysis. In this tip we cover how you can simplify this process.
Checking SQL Services Status - An Evolution Part 1
As a production DBA, one of the key things that must proactively be checked on a daily basis is the status of the SQL Server instances, and all of its related SQL Server Services. In editions of SQL Server 2000 and earlier, there were only two main services for concern, MSSQLSERVER and SQLSERVERAGENT. In SQL Server 2005 and higher, there are several new services to consider, as well as some of them being instance independent. This means there could be a multitude of services on a single server, which must be sorted out of all the other existing windows services.
Clean unused space when a SQL Server table with a variable length column is dropped
Whenever rows or columns are deleted from a table, SQL Server reuses space to accommodate new data. However, this is not the case when a variable length column is deleted. The space used by this type of column is not reclaimed automatically. Variable length columns can be of data types varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml. In this tip, I have tried to put forth a solution to reclaim this space and this should work for SQL 2005 and later versions.
Configure Windows Firewall to Work with SQL Server
Windows Firewall is an OS level software firewall. It can be a crucial part of OS level security if you are not using other firewall hardware or software. Since I do not have another firewall I have a need to enable Windows Firewall for my SQL Server database server. In this article I go through the process of configuring Windows Firewall for different scenarios along with the associated considerations.
Configuring Windows Instant File Initialization for SQL Server 2005
SQL Server 2005 supports Database Instant File Initialization which skips zeroing out data pages that can reduce the time when performing operations like creating databases, adding files to an existing database, increasing the size of an existing database file manually or thru autogrowth or restoring a database or filegroup. But how do we make sure that SQL Server is using this feature?
Database Attach Failure in SQL Server 2008 R2
When you attach a database in SQL Server 2008R2 which was detached by a user with a different login you may get this error: CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file... (Microsoft SQL Server, Error 5123). In this tip we will cover how this can be resolved.
Database recovery model change notification report for SQL Server
The database recovery model plays a crucial role for the recovery of a database. With several DBAs having access to a SQL Server instance there are bound to be changes that are not communicated. In this tip we cover a monitoring solution we deployed at our company to alert the DBAs if a database recovery model is different than what it is expected.
Different ways to determine free space for SQL Server databases and database files
One of the functions of a DBA is to keep an eye on free space within the database and database files. The auto grow feature is great as a last resort, but proactively managing the database files is a better approach. Also, this information can be used to shrink data files as needed if there is a lot of free space in the files. In this tip we take a look at a few ways to find the current free space within a database, so you can better manage your database files.
Different Ways to Search for SQL Server Database Objects
In the course of any database development project, searching for database objects is something you will probably do. It can be any kind of search which is done using some text as the search mechanism. Some of the examples of search that a developer / DBA may make within a database or database server are: searching for a database object, searching for occurance of particular text within database objects, searching within the schema of a database object, search within the results of query results or entire tables, etc..
Enable SQL Server 2008 Dedicated Administrator Connection
One of the junior SQL Server Database Administrators in my company approached me yesterday with a dilemma. He want to know how to enabled the Dedicated Administrator Connection in SQL Server 2008. At first I started to tell him, but figured it would be smarter to document the options and share the information. This tips shows you how you can enable this feature in SQL 2008.
Find SQL Server data and log files that are almost out of space
On many SQL Servers database file size, either data or log, may be restricted to a maximum size to ensure there is adequate space on the server. The problem with this is that if your data or log file runs out of space you will get an error message and your transactions will fail. In this tip I will show you a rather simple procedure that can be run to report on potential space problems before they occur.
Handling cross database joins that have different SQL Server collations
Recently I had to create a SQL Server instance with a non-standard collation (or as the vendor that created the database solution we were implementing called it "non-default", which gave me a chuckle that they were arguing the matter.) Now that the collation for the instance is Latin1_General_BIN none of my managerial scripts are working. This is causing a great deal of difficulties in our standardized maintenance jobs we've created for such items as backups, statistics updates, and indexing rebuilds and reorganizations. Is there an easy way to resolve collation issues when the server collation is different than the collation on one or more of the user databases on an instance?
How to Automatically Monitor Windows Event Log from SQL Server
When troubleshooting server related issues like unexpected crashes or service restarts, SQL Server DBAs often browse through the Windows Event log looking for clues. This is a reactive approach where the DBA is investigating after an incident has been reported. The more proactive approach would be to regularly check the log for any warnings or errors. Many production DBAs are often responsible for large numbers of instances in the organization and don't have the time to go through each individual event log of every server. In this tip I will show an automated way of monitoring the application event log of SQL Server boxes. The system generates a summarized report of SQL Server errors and warnings from the target machines' event log and sen
How to isolate the current running commands in SQL Server
My previous article concerned querying the Dynamic Management Objects to return the last query to execute for a selected session. What if you are interested in the current state of activity on your SQL instance though? Examining what has already occurred, while valuable in some cases is most likely not as important as what is currently taxing your server. This being the case, how do you examine the currently executing statement for each of the requests issued against your SQL Server instance?
How to move a SQL Server database within the same instance without losing settings
I want to move my database on my SQL Server 2005 system to a new drive, but I have database options, like cross-database ownership chaining, that I don't want to lose or have to remember to re-apply. How can I do this?
How to shrink the transaction log file in SQL Server 2008
There are times when a SQL Server transaction log file grows so large that it has to be shrunk back to a more reasonable size. Before shrinking the log file it is necessary to remove the recent transaction log entries from the log. That use to be done with the BACKUP LOG.... WITH TRUNCATE_ONLY command. The TRUNCATE_ONLY option was removed in SQL Server 2008. How do you remove the log entries so the transaction log file can be shrunk?
How to use RunAs command for SSMS if option does not exist
As a best practice in the industry, a DBA often has two logins that are used to access SQL Server; one is their normal Windows login and the other is an admin level login account which has sysAdmin rights on the SQL Server boxes. In addition most of the time the SQL Server client tools are only installed on the local desktop and not on the SQL Server Production Box. In order to use the different login to connect to SQL Server using SSMS you need to use the "Run as" feature. What do you do in the case of Windows 7 or Windows Vista where you can’t find the Run As Different User option.
How to use the SQL Server sys.fn_PhysLocFormatter undocumented function
Suppose that my SELECT statement returns some corrupted data or some values are not stored correctly; for example, a varchar field that contains some Unicode characters. It would be nice if we could use the DBCC PAGE command to see exactly what it is stored at the page level, however, in order to do this we need to find a way to correlate the results returned from a SELECT statement with the physical data location. Is that possible? The answer is: YES. All we need to do is use the sys.fn_physLocFormatter function. In this tip, I will cover how to use this undocumented function.
Installing SQL Server 2005 Performance Dashboard Reports
I am trying to leverage the new SQL Server 2005 DMVs for performance analysis. While they are very useful, it would be nice to be able to see graphical representations of the data collected by the DMVs. Is there a graphical utility within SQL Server I can use as well?
Killing a SPID and Checking Rollback progress in SQL Server
From time to time, I encounter blocking situations where I end up issuing a KILL command against the blocking SPID. When I run sp_who2, I can see the killed SPID is in a ROLLBACK state. Is there any way I can get an idea of how long the rollback for the SPID will take?
Limitations when attaching a SQL Server Database
One of the Junior SQL Server Database Administrators in my company wanted to know why he is unable to attach a SQL Server database using sp_attach_db. When he tried to attach the database he got this error message: "Procedure or function sp_attach_db has too many arguments specified". In this tip we look at what causes this issue and how to resolve it.
Maintaining SQL Server database metadata to identify object changes
My company sells a packaged software solution to our customers. From time to time, I am called in to troubleshoot and diagnose customer database issues. A lot of times, I find that our support department has added indexes to the customer's model or modified their database source code. Unfortunately, I have to comb through the database to find these changes. Is there a better way?
Manage the size of SQL Server Transaction Logs
I've mentioned on a number of occasions I support an insane (I-N-S-A-N-E) number of databases and SQL Server instances. Doing so makes it difficult to pay as much attention to all aspects of your environment as you may need. In a perfect situation the DBA sizes their transaction log files and database files appropriately, monitors activity and growth, and makes sure all their backups always succeed. However, most of us don't live in that perfect world. I have created a tool I call LogWatch. It's a stored procedure that resides in a dedicated database on all my Microsoft SQL Server instances to help me keep a handle on those potentially out of control transaction logs.
Managing SQL Server Database and Application Metadata
I am frequently asked to translate the databases that reside on a physical server / SQL instance into their corresponding application names. Typically this need arises when preparing planned downtime notifications for servers, but it also is valuable when communicating to other IT Managers or non-technical staff in my organization. Unless you are the Database Administrator or the Application Analyst for a particular database you are usually oblivious to the naming conventions of the databases that support the applications you rely upon daily. This is why it is important to have the metadata repository in place to provide that translation when the need arises.
Move SQL Server transaction log files to a different location via TSQL and SSMS
I know that moving the log file of a production SQL Server database to separate physical drive is a best practice to optimize the I/O performance. Recently, we have added a physical drive to our SQL Server. Based on this change, I am planning on moving the SQL Server log file of my production database on this drive. I am curious about how it will optimize the performance. What are some of the considerations I should take into account and how can I move the SQL Server database log file to a separate physical location?
Out of space on the C drive of your SQL Server and ways to reclaim disk space
It happens to all DBAs - your C drive has filled up and your database is about to crash or has stopped processing. Perhaps your SQL error log is spamming and has filled up the entire C drive before you have had the chance to address the underlying issue. Your primary concern is getting your SQL Server back online. Here are some steps you can follow to free up some space in a hurry.
Overview of the SQL Server Browser service
A new service, SQL Server Browser, was introduced with SQL Server 2005 and is also used with SQL Server 2008. In this tip I will cover what this is and how to use this service to improve your SQL Server environment.
Performance Analysis Using SQL Server 2008 Activity Monitor Tool
While looking through the new features and improvements in SQL Server 2008 Management Studio (SSMS) we found a potentially interesting one called Activity Monitor. Database developers and Database Administrators can use Activity Monitor to get a quick overview of an SQL Server 2008 system performance. Can you give us a detailed explanation of how we go about using Activity Monitor?
Performance Issue with large number of Virtual Log Files in SQL Server Transaction Log
SQL Server transaction log files have an internal structure called the Virtual Log File or VLF. When the number of VLFs grow out of control due to autogrowth the log can become fragmented and cause delay. In this tip we look at how to see how many VLFs exist as well as how this can be reduced to a more reasonable number.
Remote Desktop Error terminal server has exceeded the maximum number of allowed connections
As DBAs, we are supporting production servers as well a number of development boxes. On the development boxes apart from DBAs, developers probably also have Admin rights and may sometimes connect to the server remotely. There could be times when developers or other DBAs are not disconnecting their remote desktop sessions or they are connecting to the server using MSTSC. Due to this, we sometimes get the "The terminal server has exceeded the maximum number of allowed connections" message. To resolve this so we can connect, we need to disconnect in-active sessions through terminal service manager, but what can we do in the case where all sessions are still active? To resolve this you can follow the below simple solution.
Remotely Shutdown, Restart or Log Off the local or network computers
When installing a SQL Server cluster, Services Packs, Hotfixes, etc.. there may be a need to reboot both the nodes for changes to take affect and to reboot the server we have to login to each node which is time consuming. Also, there may be a time when you are not able to login to the server after a reboot due to the server being hung. In this tip we will take a look at the SHUTDOWN command and how it could be used.
Script to Get Available and Free Disk Space for SQL Server
Often we face the situation where we need to check the total disk space and available disk space for both physical and LUN/Mount drives. The extended stored procedure XP_FixedDrives is unable to help us in this scenario, so we have to log into the machine to check the total and free disk space for each physical/LUN/mount drive. In this tip, I show how this can be done using PowerShell.
Simple process to track and log SQL Server stored procedure use
In many environments there is a lack of consistent logging from stored procedures - if there is any logging at all. In general, we tend to leave it up to the application to record any errors, but in systems with many different applications, it can be tedious to collect exception information and figure out which stored procedures are having issues. And more generically, it is difficult to determine which stored procedures are being used at all, without running a server-side trace for a significant amount of time.
Simple way to export SQL Server data to Text Files
Since the transition from DTS to SSIS, I have found myself looking for alternative ways to do simple tasks such as exporting data from SQL Server into text files. SSIS is a far more powerful tool than DTS, but I generally try to stay away from it for simple operations. With the combination of a few T-SQL commands this tip shows you a simple way to export data to text files.
SQL Server Data Mining Add ins for Office 2007 to Predict Database Growth Part 2
In my previous article, Using SQL Server Data Mining Add-Ins for Office 2007 to Predict Database Growth Trends, I showed how I used the Analyze option within Excel and the Data Mining Tools for SQL Server to predict database growth. Now, I want to show you another easier way to pull information to help predict database growth again using the Data Mining Tools.
SQL Server Data Mining with Office 2007 to Predict Database Growth Part 1
As part of your DBA duties, you should be analyzing the size of your database so you can prepare for growth. This may be for new purchases of disk sub-systems, consolidation, pre-allocating enough space for your database to minimize fragmentation or maybe to maximize performance. With all of the necessary tasks that need to be done on a daily basis very few DBAs have the time to be proactive. In this tip we will look at how you can use some of the SQL Server data mining tools to help you predict database growth.
SQL Server Database Mail Cleanup Procedures
I have been using Database Mail for a while and it is causing a lot of growth in my msdb database. What is the best way to delete the excess data stored in the msdb due to Database Mail?
SQL Server Database Migration Checklist
Many times we get requests to migrate a single user database or multiple user databases to different server. At the time of migrating databases, there are chances that we can miss some important steps, for example trustworthy property, dependent jobs, linked server, logins etc. There are also chances we are missing important DBA items which can hurt SQL Server performance. In this tip, I cover tasks that should be followed when moving databases from one server to another server.
SQL Server does not reclaim space used when inserts fail
SQL Server has an issue when an INSERT fails that includes LOB data, SQL Server allocates space for the insert but does not reclaim the space on the failure therefore taking up more space than necessary. In this tip I walk through an example of this issue and ways to resolve it.
SQL Server High Availability Options
I need to implement a high availability solution for one of my SQL Server databases, but I’m not sure of which option to implement to satisfy my company’s needs. Check out this tip to learn about the five native SQL Server High Availability options.
SQL Server Lazy Writer Issue Warning No Free Buffers Found
This tip is intended to help someone who is or will be facing a no free buffer found issue with the SQL Server LazyWriter. Recently, we experienced some unknown issues with our SQL Server and after inspecting the error log file we noticed the following errors: "SQL Server has encountered 50 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file..." and "LazyWriter: warning, no free buffers found." These error messages were followed by a SQL Server memory dump file like SQLDump0030.mdmp in the file system.
SQL Server restart validation checklist
Many times there is the need to restart the SQL Server services, this could be a reboot of the server after patching, service pack installation or due to some other reason. Sometimes we may face issues after the restart for example the SPN is not registered, a database is offline etc... For the solution you can use the below standard SQL Server validation checklist after you restart your SQL services.
SQL Server Third Party Application Requirements Document and Checklist
In August 2009 I wrote a tip concerning checklists for third-party applications running against Microsoft SQL Server. I thought it was a good time to revisit the topic and provide an added bonus: a requirements document that you can download and customize for your own uses.
SQL Server Transaction Log Grows And Fills Up Drive
I had a production problem recently, the transaction log grew 1 GB every minute and filled up the entire drive. The error message I got was: "The transaction log for database 'MyDB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". My SQL Server is clustered and the issue occured after a node failover occured. In this tip I cover steps that I took to resolve this problem.
SQL Services Status Check - An Evolution Part 2
In my last tip Checking SQL Service Status - An Evolution (part I), I spoke about the important need to be able to monitor and keep track of the status of all necessary SQL Server Services. I also showed you one method using t-sql, xp_cmdshell and an OS component called SC.exe (service control). Sometimes, using OS components or even xp_cmdshell is not feasible for all sorts of reasons, including security, or limited DBA access. In this tip we look at other techniques to get the same information.
SQL Services Status Check - An Evolution Part 3
In this series we discussed how to monitor and keep track of the status of all related SQL Server Services. SQL Server has introduced with its imminent 2012 release, some new DMVs (Dynamic Management Views) that make this even easier.
Start, Stop, Pause and Query Windows SQL Server Services Remotely
Sometimes SQL Server services or any other Windows service does not start due to some issue after a server reboot. When you try to login to the server you find that you do not have remote login rights to that server or remote login is disabled for that server. Here is a solution to start the service remotely. This tip is also useful if you want to start a number of services in a particular order.
Third Party Application Checklist for SQL Server
It is a pretty good bet that if you are a Database Administrator you are dealing with third-party vendors who have supplied databases for you to support within your environment. Just about all of us have to deal with a mix of in-house-developed and externally-supplied databases and the vendor sales, technical, and support staff that come along with them. In this tip I present a checklist of questions to have your vendors answer to know exactly what the SQL Server impact will be.
Using INSTEAD OF triggers in SQL Server for DML operations
I have created some views to provide limited data access for an application. Several of these views are composed of more than one table and there are problems while performing insert, delete, or update operations on multi-base table views. I am required to seamlessly manage such problems, so that developers do not get direct access to the base tables for DML operations. In SQL Server 2000 and onwards there are INSTEAD OF triggers that can be used to carry out such tasks. Although these types of triggers can be used in a number of scenarios their primary function is to perform DML operations through such views. This tip shows how to use INSTEAD OF triggers to handle these DML operations on views.
Using PowerShell to Register All SQL Instances Into Central Management Server
Managing multiple SQL Servers has its challenges. One of the big challenges is having to collect data from each of the servers you manage and figure out which servers need attention. SQL Server has introduced a new feature called Central Management repository which can be used to manage multiple instances. One of the challenges to using CMS is that you have to register all the SQL Servers manually into CMS. The below article will guide you on how to register hundreds of servers in a second for SQL Server CMS.
Using SQL Server meta data to list tables that make up views
One of the issues I often face is the need to find views that are already established for certain tables. This maybe for other developers, end users or even for myself. I could search the system tables to find this or explore each view, but are there other ways to easily find a list of all tables that are used for a view or even a list of all views that a table is tied to?
Using SQLCMD to Execute Multiple SQL Server Scripts
My company has a number of SQL installation scripts that build a fresh copy of our database. Each script relates to a specific SQL task. One script builds the database, another builds the tables, etc. I call SQLCMD repeatedly to execute each script. Is it possible to concatenate these scripts together and execute them with a single call to SQLCMD?
