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!
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
On one of my servers the query parameter just wasn’t working. I kept getting this error:
What to do? Continue reading
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
How much code has gotten into production? How many different objects have this code? Isn’t that also a measure of just how ‘big’ your responsibility is? If you have a massive application with thousands of stored procs and functions…guess who put them there? Guess who has to navigate thru them to find just the one that needs to be corrected? That’s right, you…the DBA. So I decided it would be nice to know how many lines of code had ‘slipped in’ to my production machines. Continue reading
I have a lot of servers. Full-Text operations sort of slipped up on me. Then, a developer asked for assistance…in particular who else was using full-text that he could ask for guidance? I couldn’t tell him. That got me started to find a way to see just how wide-spread full-text had become. Continue reading
Many may know that the following query can get information about the history of reports being called.
SELECT ss.UserName, ss.Format, ss.TimeStart, cat.Name, ss.Parameters, CONVERT(nvarchar(10), ss.TimeStart, 101) AS rundate FROM reportServer.dbo.ExecutionLog AS ss INNER JOIN Catalog AS cat ON ss.ReportID = cat.ItemID
You might also get: “The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer.” To resolve this… Continue reading
How often are you deleting users and you get the warning that this user owns a schema and the schema is going to get dropped also…and are you sure you want to do this? … And no, you’re not really sure about the schema? Continue reading
Suppose you had a 4 character field (prefx), and you wanted to find all those records where the 2nd and 4th characters were numeric, with the other two being alphabetic. Here’s a neat, simple solution:
select * from TableName where prefx like‘[A-Z][A-Z][A-Z][0-9]‘
