Very annoying error and I haven’t seen clear concise instructions for getting this to work. I will attempt to do that here.
- Open Administrative Tools > Component Services
- Navigate to DCOM Config and open the properties for MsDtsServer100
- Click on the Security tab
- Add the appropriate users/groups that you need and assign their permissions to BOTH “Launch and Activation Permissions” AND “Access Permisions” (Both of whom, select Customize then Edit)
- Click Ok to save changes and close those windows.
- Right click on Computer, select Manage.
- Navigate to System Tools > Local Users and Groups > Groups
- Double click on Distributed COM Users
- Add the users/groups you wanted to give permissions to.
Being the parent of a child in the terrible 2′s can be a daunting task; they are expressing the individuality and are testing the boundaries. My daughter was refusing to eat, it was just an act of defiance.
I then remembered something from the book the Art of War, take the middle ground. If she won’t eat food, maybe I could get her to just hold the food. Obviously, it should be something easy for a 2 year old to hold. In this case I used pancakes. I told her to help daddy by holding a pancake for me.
In a minute, she ate the pancake. I asked her to hold another one for me, and wouldn’t you know that she ate that one too!
So I hope this helps to feed your defiant 2 year old as well!
I’m currently studying for the 70-450 exam to obtain my MCITP in SQL Server Database Administration. I wanted to get a better understanding for SQL Server’s transaction log. I wanted to see if I could corrupt a databases MDF and recover data even after the last t-log backup. This is where the tail backup comes in.
The tail log backup can recover your data even if the MDF is corrupted. This is because it stores all the transactions since the last full backup and t-log backup. If the MDF is corrupted your t-log should still be able to be backed up, unless that is corrupted too. If the database is not truly corrupt, a backup log statement with the norecovery option will work just fine. This puts your database into recovery mode and prevents users from going into the database, preserving the state of the database so the restoration can be performed.
Unfortunately this does not work when the database is corrupted. You have to use the option continue_after_error, this forces the backup to occur.
You can then restore all data back to the database using your full and t-log backups.
Immediately after bringing the database online, be sure to make a full backup. This way you have a good restore point and if you routinely are backing up your t-logs, your next transactional backup will work just fine.
All I can say is I have never really needed to search for a percent sign or square brackets or a caret in a query until today. GooBling (Googling and Binging) didn’t return any useful results. Of course Kim Tripp, one of the sage SQL Server gurus always recommends checking Books Online first, shame on me for not checking there first but this wasn’t entirely obvious. Check out the details here: http://msdn.microsoft.com/en-us/library/ms179859.aspx
You can specify an escape character using a very, now, obvious escape character. An escape character placed in front of a pattern matching character will force SQL Server to recognize the pattern matching character as a normal character.
SELECT * FROM table WHERE col1 LIKE ‘%|%%’ ESCAPE ‘|’
The above query will return all rows where col1 contains a percent sign.
57% of all VM launches in Virtual PC are failures. I’m joking, don’t believe everything you read on the internet (or politicians for that matter). To that end, take my solution into consideration but a warning for you, there is a distinct chance you will lose data in this case, especially if you never save changes from the Undo Disk.
If you are having problems booting your VM in Virtual PC, I have a trick fro those of you who have the Undo Disk enabled. If you cannot boot, you can go into settings of the VM > Close in the left pane > Select Prompt in the right pane.
Try to stop the VM and it will prompt you for an action when you close. Select “Turn Off and Delete Changes”. When it closes it deletes everything in your undo disk and it will revert your VM to a time when, hopefully it was booting correctly.
Note: any changes made since the last time you committed changes from the undo disk will be gone forever. The idea here is to recover the VM to a working state. I do not want to hear that your VM is dead, you have been warned.
So if you are like me, chances are you have that one server where the reindexing or reorganizing is taking a long time to finish. I came up with a solution that has helped me reduce the time it takes to maintain my indexes.
Rebuilding indexes basically recreates an index from scratch (retrieving the data from the table again) whereas reorganizing is a process that relocates the pages of an index.
Rebuilding an Index
Rebuilding an index is typically an offline process, unless you have the Enterprise edition of SQL Server with the online flag set (check BOL for your particular version). While non-clustered indexes are being rebuilt, the table is available. However, rebuilding clustered indexes results in the the table being offline.
Reorganizing an Index
Reorganizing is an online process. It can reduce your fragmentation, but doesn’t always. Remember it is only moving pages around and not actually recreating the pages from data in the table.
Based on what I have read from various experts of SQL Server, rebuilding is recommended for indexes over 30 percent fragmentation. Under 30 percent, the index should be reorganized.
It is also recommended to not do anything to the indexes that are under 100 pages in size as they will not gain anything from a rebuild or reorganize. Of course, sometimes you might need to rebuild a small index, if for example, one of the pages has data corruption.
What the Script Does
Based on the best practices I mention above, it scans the sys.indexes view of every database on your SQL Server instance and then creates (and executes) rebuild or reogranize command for each index that has more than 5% fragmentation and more than 100 pages in size.
It uses the server default fill factor, which you can set in the properties of server instance.It currently DOES rebuild clustered indexes, so if you are running this be sure to run it in your maintenance window.
I encourage you to read through and understand the script as well as test it in your test environment before running it on production to get a feel for its behavior.
Let me know if it helps you out, I know it has helped me get more done in my maintenance window.
DECLARE @rebuildThreshold FLOAT;
SET @rebuildThreshold = 30.0;
CREATE TABLE #indices (
DECLARE @db SYSNAME;
DECLARE @sql VARCHAR(2000);
DECLARE curs CURSOR
WHERE name<>’tempdb’ AND state_desc <> ‘OFFLINE’ AND is_read_only = 0
FETCH NEXT FROM curs INTO @db;
SET @sql = ‘SELECT ”’ + @db + ”’,
”['' + SCHEMA_NAME(schema_id) + ''].['' + OBJECT_NAME(i.object_id, DB_ID(''' + @db + ''')) + '']”,
i.name AS indexname,
FROM sys.dm_db_index_physical_stats(DB_ID(”’ + @db + ”’), NULL, NULL, NULL, NULL) ips
INNER JOIN [' + @db + '].sys.indexes i ON ips.object_id=i.object_id AND
INNER JOIN [' + @db + '].sys.objects o ON i.object_id = o.object_id
WHERE page_count>=100 AND
avg_fragmentation_in_percent > 5′;
INSERT INTO #indices
FETCH NEXT FROM curs INTO @db;
DECLARE curs2 CURSOR
SELECT CASE WHEN fragmentation<@rebuildThreshold THEN ‘ALTER INDEX ‘ + indexname + ‘ ON [' + dbname + '].’ + tablename + ‘ REORGANIZE;’
ELSE ‘ALTER INDEX ‘ + indexname + ‘ ON [' + dbname + '].’ + tablename + ‘ REBUILD;’
WHERE indexname IS NOT NULL;
FETCH NEXT FROM curs2 INTO @sql;
FETCH NEXT FROM curs2 INTO @sql;
DROP TABLE #indices
There are some very important distinctions between these that I feel I need to explain.
First and foremost delete is used for deleting a limited number of rows from a table. This is not to be used for deleting all rows in a table.
Another important note is that this is a logged process meaning it writes this delete into the transaction log. Which is good if you ever need to recover those rows.
Also deleting rows does not reset or reseed, more technically, any identity columns. So deleting the last row will not decrement the identity column.
So this function is to delete all rows in the table.
Unlike Delete, this operation is very efficient, mainly because it is not a logged process. Use this for very specific reasons, testing, repeating processes, etc.
Also it will reseed the identity column in your table. So for example, it will delete all the rows in your table and the identity will be reset to its initial value, by default this is the number 1.