Out of disk space…Log file grown too big?

Posted on May 17, 2012 by Steve Schneider

It’s early in the morning, and you’ve got disk space problems… log file, right?  Yep.   OK, we need a script that will tell us what we need to know ASAP.  Since i experienced this issue multiple times…and since they always happened on Sunday morning…just before i was going to church (how much more inconvenient can it get?)…i created just such a script.  Using two CTE’s i first gather the information i need about the database data files, and then the database log files.  Since there can be multiple physical files for either type, i max and sum the needed values before grouping.  Then, i combine that data with sys.database information.  The result gives me almost all i need to know in a single picture…i’m ready then, with a simple “xp_fixeddrives” to get straight to fixing things. Continue reading →

Posted in SQL 2005, SQL 2008, SQL Administration, Uncategorized | Leave a comment

Find the users and their roles in a Database

Posted on April 2, 2012 by Steve Schneider

Do you need to know all the users for a single database…and also to know what roles they have? Try this SQL:

select m.Name, isnull(p.name,”) as Role
From sys.database_principals m left join
sys.database_role_members r on r.member_principal_id = m.principal_id left outer join
sys.database_principals p on p.principal_id = r.role_principal_id
where m.type =’s’
order by Role desc, m.name

Posted in SQL 2005, SQL 2008, SQL Administration | Leave a comment

When were your statistics last updated?

Posted on March 30, 2012 by Steve Schneider

Introduced in sql 2005 the following query will tell you the date your statistics were last update for each of indexes of a table… Continue reading →

Posted in Performance Tuning, SQL 2005, SQL 2008 | Leave a comment

Improved EventLogs in Win 2008

Posted on March 29, 2012 by Steve Schneider

Recently at one client location we had numerous unexplained cluster failovers. I had SQL Server and Analysis Services running on the clusters. When analysis was done on the failover, the analysts found that the “FlightRecorder” showed errors immediately before the failover. The Flight Recorder got the blame for the failover. When i was brought in, i took a closer look at the log and i noted serveral things: Continue reading →

Posted in SQL Administration | Leave a comment

Determine “Locked Paged” use across your domain…

Posted on February 28, 2012 by Steve Schneider

So, building on my previous blog entry, now, let’s open a multi-server query and run this script that follows.  It will tell you for each server it’s version and how much memory is in locked pages. Continue reading →

Posted in SQL 2005, SQL 2008, SQL Administration | Leave a comment

Programmatically act on SQL Version

Posted on February 28, 2012 by Steve Schneider

I often have scripts that run across multiple versions of SQL … more and more 2008′s and less and less 2000′s.  In doing so, i need to take different actions based on that version.  Here’s the simple technique that i have developed … it currently depends on Microsoft continueing to use periods in their version naming scheme… i don’t think that that will change soon!

select  ltrim(substring(@@version,CHARINDEX(‘.’,@@version)-2,2))

Steve

Posted in SQL 2000, SQL 2005, SQL 2008, SQL Administration | Leave a comment

MSDB db too big?

Posted on January 10, 2012 by Steve Schneider

Microsoft provides “sp_delete_backuphistory ’6/1/2011′“.   However, this can take a very long time unless you also add indexes to the backup table.  This is what did the trick for me:

Create Nonclustered index idx_backupset_finishdate ON backupset (backup_finish_date) INCLUDE (backup_set_id, media_set_id)

What was taking 20 minutes now takes 1 minute.

Enjoy!

Posted in Performance Tuning, SQL 2005, SQL 2008, SQL Administration | Leave a comment

Impersonation, Execute As & Remote queries

Posted on December 30, 2011 by Steve Schneider

This week i had a developer creating a process (job) where a database user was to have authority to query remote machines.  To debug the process the developer needed to assume the role of the user running in the application.  In the past i used the “Execute As Login“.  This had problems this time. Continue reading →

Posted in SQL 2005, SQL 2008, SQL Administration | Leave a comment

DB mail Query parameter not working

Posted on December 23, 2011 by Steve Schneider

On one of my servers the query parameter just wasn’t working.  I kept getting this error:

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504
Query execution failed: Msg 208, Level 16, State 1, Server UFOsqlAB, Line 1
Invalid object name ‘CountySalesTax’.

What to do?  Continue reading →

Posted in SQL 2005, SQL 2008 | Leave a comment

SQL Server’s Auto Growth settings

Posted on December 20, 2011 by Steve Schneider

Over the last week I’ve been finding an unusual number of log file growths.  I did some investigation and found a good script to help me track what was the current status for file growths.  It is as follows: Continue reading →

Posted in SQL 2005, SQL 2008, SQL Administration | Leave a comment