Administration

Sort by: Most Recent | Most Viewed | Most Commented

  • Whats running on my SQL Server? Part 1

    SQL Server 2005 introduced some exciting new tools for viewing what statements are running on your database instance. At the base of these tools are the new dynamic management views (DMV) that allows you to view SQL statements executing based on connection. Management Studio uses these views to supply the end user with some pretty informative reports for executions. Today Im going to take a look a couple of the views that are the cornerstone of acquiring this information. The view I'll look at...

  • Fix for: Cannot open user default database. Login Failed.

    In this article I will show you some options to recover from the error "Cannot open user default database. Login Failed". How does this happen? This happens when a login has a default database set, and that database is deleted. When you open any of the Microsoft database tools (like SQL Server Management Studio or Query Anaylzer), it automatically attaches you to the default database. The problem is, that if that database has been deleted, you get the following error message. There are...

  • The permissions granted to user 'someDomain\someUser' are insufficient for performing this operation. (rsAccessDenied)

    This is a very short blog post related to the administration of the SQL Server Reporting Services. This particular post is related to an installation of SQL Server 2008 reporting services on Windows Vista. Problem: After attempting to access the http://localhost/reportserver the first time after SQL Server installation on Windows Vista, the SQL Server Reporting Services show the following error: "Reporting Services Error: The permissions granted to user 'Adelso-PC\Adelso' are insufficient...

  • Sql2005SsmsExpressFacet - How to continue your SQL 2008 installation

    Today I faced the following problem : I wanted to install SQL Server 2008 on my machine, but the installation failed with the error: "The SQL Server 2005 Express Tools are installed. To continue, remove the SQL Server 2005 Express Tools." History : I had installed the Visual Studio 2005, including the SQL Express. After that I had installed the SQL Server 2005. And now I wanted to have an instance of SQL 2008. Great plan, however it didn't go as smooth as intended. Solution : I went...

  • Managing the Transaction Log for the Accidental DBA

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase “The Accidental DBA” and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server... So if you are “The Accidental DBA”, or, simply new to SQL Server then there are a few concepts we can share to make your life easier. Based on the frequency of questions we see in Experts-Exchange, I thought a 'light' discussion about the Transaction Log would be...

  • Managing Fragmentation for the Accidental DBA

    by Mark Wills We often here about Fragmentation, and generally have an idea that it is about broken bits, or bad for performance, or at least, is generally not a good thing. But what does it really mean ? By way of analogy, think of the humble Telephone Directory. It is nicely laid out, sequentially in alphabetical sequence by Name. Think about receiving the next edition, not by replacing the directory, but by giving you the added or altered pages which you append to the end. After a few updates...

  • Why is my Log File So Big?

    So, you've ran into a problem. Everything has been running smoothly on your SQL Server database until recently. You have noticed that your drives are out of space, or when you're browsing the files on your system that the log files (typically with an extension of .ldf) are HUGE...as big as or bigger than your data files. This, of course, doesn't make sense to you since you're doing nighly backups and everything until lately seems to have been working properly. So, what could be the...

  • Enable and Disable Batches of SQL Agent Jobs

    In the following system stored procedure I allow a list of SQL Server Agent job names to be passed as an XML document. The procedure accepts the list of Job names, parses them out, and will enable or disable them based on the @enabled parameter passed into the procedure. This procedure is VERY handy for those situations where you need to disable a batch of jobs, and then enable them later on. It saves you from having to remember which jobs you disabled, along with saving you from having to go into...

  • Getting Table Row Counts

    Here's a quick and easy script you can use in SQL Server 2005 to return the number of rows in a table, along with the index size on the table, the reserved space, and the data space for the table. If you're familiar with the system stored procedure sp_spaceused, this information may seem familiar to you. I took some of this code from that procedure, but am presenting it in such a way so that it works for all tables in the database at the same time. This makes it much easier to query, and...

  • How to easily DROP and CREATE foreign key constraints

    Here's a helpful bit of code I use a lot to generate the statements to DROP and CREATE all of the foreign key constraints on a database. This is useful for going through and using TRUNCATE on tables that would normally not allow truncation due to the foreign key constraints defined. USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /********************************************************************************************* Copyright SQLServerNation.com * Author: Tim Chapman ...
Copyright SQL Server Nation 2010
Powered by Community Server (Non-Commercial Edition), by Telligent Systems