Viewing 15 posts - 11,761 through 11,775 (of 49,552 total)
It rolls back, same as any other data modification when stopped part way through.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2013 at 1:07 pm
Rather use impersonation and just have that login having the permissions. Shouldn't be SA though. I don't think you can get the DB in a login trigger, it fires too...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2013 at 9:20 am
It's needed for a couple of small things like transaction rollbacks and crash recovery on restart (ensuring a consistent database)
If there was no transaction log, then any failure in any...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2013 at 8:59 am
You're welcome to your opinions, just don't expect them to held as universal definitions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2013 at 5:24 am
Yes, that's why I said 'Leaving it to you to change the variables, fetch statements, etc.'
If you change the cursor definition, you'll have to change the fetch statements, add variables,...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2013 at 5:23 am
The filter is on the join and it's an outer join. So the filter is done before the join then the left join joins in the matching rows, while also...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2013 at 4:33 am
SQLSteve (9/6/2013)
Cheers for that. Where should we be setting @objectID though?
Look at the first changes, to the cursor
Should the last part be i.object_id = t.object_id ?
It is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2013 at 4:31 am
If I'm moving databases from one instance to another, I'll use backup/restore. It definitely is a way to move databases.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2013 at 4:12 am
Two places that need to change.
First, the cursor definition
declare FindFragment cursor for
SELECT object_id, name FROM #IndexFrag WHERE (avg_fragmentation_In_Percent > 5) and (avg_fragmentation_In_Percent < 40) and (name not like 'null')
because index...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2013 at 3:40 am
Or better yet, create a good clustered index and don't drop it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2013 at 3:01 am
You did not need to take the instance down.
After running the alter database, take the database offline, ensure the files really are in the correct place and that SQL has...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2013 at 2:21 am
Using ALTER DATABASE to move a user database file doesn't require a service restart.
Run the ALTER DATABASE
Take that DB offline
Go to the file system
Move the database's files to the new...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2013 at 1:34 am
Login triggers fire early in the connection process, before the switch to the requested DB happens. That's why you need the 3-part table name and why you only see master.
As...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2013 at 1:32 am
Then...
INSERT INTO <database name>.<schema_name>.Audit_Log...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 5, 2013 at 4:03 pm
and what messages (in the error log) does the revised trigger above produce?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 5, 2013 at 3:47 pm
Viewing 15 posts - 11,761 through 11,775 (of 49,552 total)