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 oth
In an earlier tip (Building a SQL Server Data Dictionary with the Extended Properties and Third Party Tools) I outlined the value of using the extended properties across some of the industry leading data modeling tools. What if I do not have access to an
In this article we look at the SQL Server DMVs that can be used to identify missing index information that SQL Server collects based on executed queries.
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 simp
I am running an application that issues queries directly to the database from within its source code. I have noticed that the database server will get low on available memory and that CPU activity is higher than I would like. What could be the problem?
In a previous tip, SQL Server 2005 SysAdmin Login Auditing, I wrote about how to use a LOGON trigger to capture all logon activity. One of the things you need to aware of is that if you create a logon trigger and there is some bad code you are going to p
My company uses a vendor purchased application to process orders. Lately, one of the vendor application queries has been running very slowly and is starting to cause problems. My testing has determined that the SQL statement can benefit from a query hint.
I've seen so many different options and scripts for determining free space, consumed space, and total space allocated for databases in Microsoft SQL Server. Problem is none ever seem to give me all the information. I need to run one script to see this i
SQL Server provides numerous system data types to store dates, character based data, numeric data, etc. However there are some situations when a customized data type is needed for consistency across an application. Some examples could be phone numbers in
I want to audit whenever a member of the sysadmin role logs on to my SQL Server. Auditing all successful logins provides that information, however, it logs every connection. The sysadmin logins are being lost amidst all the noise. How can I just track the
In a tip on How to setup SQL Server alerts and email operator notifications, you have seen how you can configure email alert notifications in your SQL Server instance. There are cases where we cannot use SQL Server alerts like in a SQL Server 2005 Express
Learn how to debug a SQL Server CLR function using the debugging tools in Visual Studio.
While creating T-SQL scripts with lengthy T-SQL lines you have one of two options; either use the horizontal scroll bar or create line breaks at various points to make the line readable without having to use the scroll bar. Scrolling or having to break t
I am trying to come up with a list of Job Schedules, so I can make sure that my Jobs are staggered. Is there a way to query the system tables so I do not have to manually keep track of the schedules?
In a previous tip on Disaster Recovery Procedures in SQL Server, we have learned about SQL Server disaster recovery procedure. There are other ways to increase availability with SQL Server Partitioned Tables with Multiple Filegroups.
I have an application that has a specified database connection that I either can't or don't want to change. However, I need to move the database to a new server. How can I do this easily without breaking other things that maybe using this connection and/o
A data dictionary is a collection of descriptions about the data and objects in a data model for the benefit of programmers and users who need to refer to them when building the application, reports, etc. It is ideal to create a data dictionary while wor
Recently I constructed a new backup process that I want to institute globally across all my SQL Server 2005 instances. This backup process will not only backup all the databases I point it towards, but at the same time will script out the backup commands
Learn about the date and time data type changes that were first introduced in SQL Server 2008.
Reducing the amount of storage that SQL Server uses is not as high of a priority as it used to be, but when tables grow to millions of rows a few bytes per row can have significant effect. For very large databases the storage issue gets more critical for
To continue the series on disaster recovery scenarios in SQL Server, let us look at Isolating Critical Objects. We tend to forget that filegroups can be used as a means to improve database availability.
Microsoft provides a good set of basic queries into their system tables. However, some of these procedures lack some additional information that I am interested in and I find myself constantly issuing queries to get this additional data. Is there a way I
There are a lot of scripts out there that will allow you to use the msdb to auto-generate restore scripts, but what if you cannot access the msdb database. What is the easiest way to make sure I have the proper restore scripts on hand without relying on t
Recently while creating an archival job I noticed that job was taking way too long to complete. To help troubleshoot I executed the stored procedure directly and it was also taking a long time to fetch the rows from the view that I was using. Based on t