Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Archives: February 2012

A Script A Day - Day 27 - Missing Database Backups

Today’s script is one I have used many times in the past to check for missing backups.  The script will return the database name and the last full backup date of all databases that are in the FULL recovery model and have not had a FULL database backup in the…

Read more

0 comments, 926 reads

Posted in SQL Server 365 on 29 February 2012

A Script A Day - Day 26 - RESTART a Database Restore

Today’s script is another one based on database restores.  There is a little known clause of the RESTORE DATABASE command called RESTART.  The name of this clause is deceptive, it does not RESTART anything and should actually be called RESUME or CONTINUE.  What it allows you to do is to…

Read more

0 comments, 1,010 reads

Posted in SQL Server 365 on 29 February 2012

A Script A Day - Day 25 - Performance Counters

Today’s script is based on performance counters and in particular the sys.dm_performance_counters DMV.  There are many ways in which to view and collect performance counters the most common of which is to use perfmon.  Powershell is another method, I am currently working on a project where I will be collecting…

Read more

0 comments, 1,065 reads

Posted in SQL Server 365 on 29 February 2012

A Script A Day - Day 24 - Object Qualification

Today’s script is one I use as an example to explain that there is method behind my standards that some people initially see as madness.  I’m sure some people think I am trying to make their job harder and that I get a kick out of telling them to “go…

Read more

0 comments, 648 reads

Posted in SQL Server 365 on 28 February 2012

A Script A Day - Day 23 - Find an Object In Job Steps

Today’s script is one which saves me a lot of time on occasion.  On large systems with lets say more than 100 jobs it is esential to manage them properly and the smallest thing like naming conventions help a lot.  So what if you want to check if an object…

Read more

0 comments, 388 reads

Posted in SQL Server 365 on 27 February 2012

A Script A Day - Day 22 - Database Backup

Today’s script follows on from Day 16’s script which was about database restores.  In a backwards way the script is a simple backup database script with two backup commands.  For all my production servers I have custom maintenance routines which take care of transaction log and full backups along with…

Read more

0 comments, 344 reads

Posted in SQL Server 365 on 27 February 2012

A Script A Day - Day 21 - The HAVING Clause

Today’s post is about the HAVING clause which specifies a search condition for a group or an aggregate. HAVING is typically used in a GROUP BY clause and the two biggest uses I have for HAVING are SUM() for reporting and COUNT() for hunting down invalid data.  In the script…

Read more

0 comments, 287 reads

Posted in SQL Server 365 on 27 February 2012

A Script A Day - Day 20 - User Database VLF Count

Today’s script will execute DBCC LOGINFO for all user databases.  This is a script I use to monitor the number of Virtual Log Files (VLF’s).  Kimberly Tripp has a great article on VLF’s here.

/*
      -----------------------------------------------------------------
      User Database VLF Count
      -----------------------------------------------------------------
     
      For more SQL…

Read more

0 comments, 1,129 reads

Posted in SQL Server 365 on 25 February 2012

A Script A Day - Day 19 - Remove Virtual Subscriptions

Today’s script is to help replication performance.  It was something I learned from my resident replication expert Paul Anderton.  Below is a description of virtual subscriptions.

If Immediate_Sync is set when a publication is created then virtual subscriptions can occur.  These can affect the "Distribution Clean Up: distribution" SQL job…

Read more

0 comments, 1,251 reads

Posted in SQL Server 365 on 25 February 2012

A Script A Day - Day 18 - Viewing System Configuration

Today’s script is something I’ve learned today, it’s cool learning new stuff!  When viewing server configurations in the past I’ve used the below;

EXEC sp_configure 'show advanced options',1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure;
GO

This is all fine when making changes to the…

Read more

0 comments, 338 reads

Posted in SQL Server 365 on 23 February 2012

A Script A Day - Day 17 - Possible Poor Indexes

Today’s script is one I’ve taken from a job I use to collect possible poor indexes.  I’ve tweeked it slightly so you can choose the database to run it against. 

It goes without saying that you should never just remove an index without some considerable investigation doesn't it???!!!

/*
      -----------------------------------------------------------------
     …

Read more

0 comments, 334 reads

Posted in SQL Server 365 on 23 February 2012

A Script A Day - Day 16 - Database Restore

Today’s script is one I have used more times that I care to remember.  As a DBA database backups and restores are your bread and butter, they are second nature (or should be;).  By this I mean good old T-SQL not using SSMS, I can honestly say I have never…

Read more

0 comments, 312 reads

Posted in SQL Server 365 on 22 February 2012

A Script A Day - Day 15 - Untrusted Check Constraints

Today’s script comes about because of a recent discussion about check constraints.  The discussion was about the use of WITH NOCHECK and the fact that I don’t like it being used.  My view is that if you are adding a check constraint then you are doing so for a reason…

Read more

0 comments, 497 reads

Posted in SQL Server 365 on 22 February 2012

A Script A Day - Day 14 - Upgrading to SQL 2012

Today’s script is one I have used to test one possible upgrade method from SQL Server 2008 to SQL Server 2012. If truth be told this would be my prefered upgrade method I’ll explain why…

I have database mirroring in production on SQL Server 2008 two physical servers in an…

Read more

0 comments, 499 reads

Posted in SQL Server 365 on 21 February 2012

A Script A Day - Day 13 - Chris' Top Tip - Batches

Today’s post is pretty cool.  So you have been reading up about something on the World Wide Web or you want to test something that has come to light, you create a table and want to insert a million records.  In the past I imagine some of you have used…

Read more

0 comments, 246 reads

Posted in SQL Server 365 on 21 February 2012

A Script A Day - Day 12 - Granting Role Membership to All Principals


Today's script will grant membership in the db_owner fixed database role in all online read/writable user databases to all principals not in the sysadmin fixed server role.  I have found this script useful when setting up development servers where all developers are given this level of access.  This could however be…

Read more

0 comments, 488 reads

Posted in SQL Server 365 on 20 February 2012

A Script A Day - Day 11 - User Permissions By Database

Today's script will return you permissions granted to a user for every online database.  The script uses EXECUTE AS so you will need permission to impersonate the user and  also uses the fn_my_permissions function so is limited by its functionality.  I have used this script to hunt down what I…

Read more

0 comments, 521 reads

Posted in SQL Server 365 on 20 February 2012

Sorry...

I've been offline for a week to take some annual leave and recharge my batteries.  To make up for the 9 lost days in the A Script A Day series I will be posting multiple scripts each day (starting today) until I have caught up.


Cheers


Chris

Read more

0 comments, 226 reads

Posted in SQL Server 365 on 20 February 2012

A Script A Day - Day 10 - Hosts on vCentre

If you use VMWare for your virtualisation environment all virtual machines are managed by one or more vCentre management servers.  These servers can use a SQL Server database in the back end.  By default the database is called VCDB but could be called anything really.

Todays script assumes that you…

Read more

0 comments, 320 reads

Posted in SQL Server 365 on 10 February 2012

A Script A Day - Day 9 - Index Compression Estimations

Today's script provides amongst other information Index compression estimates based on existing index information.  All you need to do is set the database context and specify a schema, table and compression type.  This script has been useful several times in the past so hopefully you will also find it useful.

Read more

0 comments, 645 reads

Posted in SQL Server 365 on 9 February 2012

Older posts