|
|
|
Get Your Own Blog
If you would like to blog on SQLServerCentral.com then send an email to
webmaster@sqlservercentral.com.
Contact the author
All Blogs
Feeds
Archives
for this blog
Let me set the scene, one of our internal IT SQL Servers which stores a whole host of performance metrics has over the last few months’ experienced tremendous growth as we have started to monitor more metrics on more servers. A routine we have in place to collect and report… Read more
Today I was asked for "a list of all tables in all databases" on a particular instance of SQL Server. Knowing what was actually required was all "User" tables in all "Accessible" "User" databases I wrote the script below . It will return the ServerName, DatabaseName, SchemaName and TableName of… Read more
Today’s post is something I thought I would share as there is an awful lot of incorrect commands I have seen (and some even tried) in the past around rebuilding system databases. Now first off let me explain that rebuilding system databases is no straight forward task. I would avoid… Read more
If you create a maintenance plan (SQL 9.0.3042) to backup databases and select All databases as below;
Then at some point in the future if one of the databases is taken offline the job created by the maintenance plan will fail.
From what I have seen the job will still… Read more
In this modern age of technology DBA’s face an ever increasing demand from businesses; our databases must perform, be secure, highly available and scalable and equally as important recoverable. There is an obsession with the 5 9’s within the industry, and rightly so, but just how resilient is your recovery… Read more
Well that's it, my "A Script A Day" series is finished. I hope you found the scripts I provided useful, I've used all the scripts in real world DBA tasks. I tried to provide a variety of scripts covering a number of different areas. This series I feel will appeal more to…Read more
Today’s script is also one I used in my migration on Wednesday. It again uses string manipulation to generate a script, this time the restore database script. Now granted this quick script wouldn't work if there are any secondary data files and is reliant on the logical file names and file locations… Read more
Today’s script is one that I used earlier this week. On Thursday I migrated a server from SQL Server 200 to SQL Server 2008 R2. Now I love migrations, I don’t think I’m weird but I buzz off the addrenaline rush when working under pressure and I get a great… Read more
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
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
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
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
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
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
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
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
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
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
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
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
Newer posts
Older posts
|
Subscribe to this blog
