SQL online code formatting

Here is a free tool: http://poorsql.com/

Delete backup files older than X days - SQL Server Backups

We use Litespeed for backup compression on one of our production servers and our backup file can only store 2 days worth of full backups and log backups. I have tried scheduling a job to delete the backup files using SQL Server maintenance plan - while it works well with the native backup files, for some reason the job doesn't delete litespeed backups. I did some search and it seems that I am not alone.

I found this thread which has an alternative way of doing this. It is simple. Create a bat file with the below -

forfiles -p "E:\your\backup_drive" -s -m *.* -d -number of days -c "cmd /c del @path"

 If you would like to delete files that are older than 2 days, then it would look like this

forfiles -p "E:\Backups" -s -m *.* -d -2 -c "cmd /c del @path"

For this to work, you need to have forfiles.exe in WINDOWS\systems32 folder.

There are a couple of other ways to delete files older than x days - powershell and VB script.

EDIT: Ah, How did I miss this before? here is a detailed article on mssqltips:

PHP and SQL Server

Via Pinal's Blog:

The PHP on Windows and SQL Server Training Kit includes a comprehensive set of technical content including demos and hands-on labs to help you understand how to build PHP applications using Windows, IIS 7.5 and SQL Server 2008 R2. This release includes the following:

PHP & SQL Server Demos

Integrating SQL Server Geo-Spatial with PHP
SQL Server Reporting Services and PHP

Primary Key Constraint - duplicate values

Msg 1505, Level 16, State 1, Line 1The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.PTO_CHATTER' and the index name 'pk_chatter'. The duplicate key value is (100283).Msg 1750, Level 16, State 0, Line 1  Could not create constraint.   See previous errors.The statement has been terminated.

One of our developers was trying to make a certain column in a table a primary key and encountered the above error.  The column had duplicate values in it. A column which has duplicates can not be a primary key. Here is a query to find whether the column has duplicate values.

SELECT EMPLID,
COUNT(*)
FROM
dbo.PTO_CHATTER
GROUP BY EMPLID
HAVING
COUNT(*) > 1

SQL Server 2011 - DENALI

..can be downloaded from here. Well, nothing new and exciting here. If you have installed SQL Server 2008, you will find this installation familiar. As was expected, the UI is a bit glossier now.

If you want to play around with the next gen SQL Server, here are the sample databases

SQL Server myths and misconceptions: Paul Randal

Here is a nice collection of misconceptions and explanations about SQL Server by Paul Randal. If you are a SQL Server novice, I think this will help you a lot in diving deep down into the subject -

Download common SQL Server Myths

The more magical things seem....

.....That would be implementing some level of eventual consistency the hard way, but to be honest, that is my ultimate goal after all: knowing how things work.

I prefer having to know how a few things are supposed to work in order to get them to work, than having them work easily at the cost of hiding the implementation details from me altogether. I tend to avoid tools that do too much for me. The more magical things seem, the less likely I am to be able to fix them

--- Jonas Galvez

Detect Fragmentation SQL Server 2005

SELECT
db.name AS databaseName
, ps.OBJECT_ID AS objectID
, ps.index_id AS indexID
, ps.partition_number AS partitionNumber
, ps.avg_fragmentation_in_percent AS fragmentation
, ps.page_count
FROM sys.databases db
INNER JOIN sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, N'Limited') ps
ON db.database_id = ps.database_id
WHERE ps.index_id > 0 
AND ps.page_count > 100 
AND ps.avg_fragmentation_in_percent > 30
OPTION (MaxDop 1);

The above script tries to find fragmented indexes in ALL the databases. If you want to run this against a specific database, replace ps.database_id with the database id from sys.databases

Make checklists

Wise words: "A short pencil is better than a long memory"

DBAs - Make checklists!

Buck Woody: Seek first to understand, then to be understood

Worth remembering - From Buck Woody's blog

One of the most important (and most difficult) lessons for a technical professional to learn is to not jump to the solution. Perhaps you’ve done this, or had it happen to you. As the person you’re “listening” to is speaking, your mind is performing a B-Tree lookup on possible solutions, and when the final node of the B-Tree in your mind is reached, you blurt out the “only” solution there is to the problem, whether they are done or not.

There are two issues here – both of them fatal if you don’t factor them in. First, your B-Tree may not be complete, or correct. That of course leads to an incorrect response, which blows your credibility. People will not trust you if this happens often.

The second danger is that the person may modify their entire problem with a single word or phrase. I once had a client explain a detailed problem to me – and I just KNEW the answer. Then they said at the end “well, that’s what it used to do, anyway. Now it doesn’t do that anymore.” Which of course negated my entire solution – happily I had kept my mouth shut until they finished.

So practice listening, rather than waiting for your turn to speak. Let the person finish, let them get the concept out, give them your full attention. They’ll appreciate the courtesy, you’ll look more intelligent, and you both may find the right answer to the problem.