Tag: SQL Server
-
AlwaysOn : Manually add a new replica node using a custom backup path with custom credentials
Management Studio wizards are powerful and can do many things automatically… but sometimes you have to do something manually. In this case we have to do manually backup and restore steps before running the ‘Add node Wizard’ in Join mode. This because the add process cannot impersonate a custom user to access a remote backup…
-
Monitoring Wait Events of a single Session or Query in SQL Server
Using sys.dm_os_wait_stat is not useful for deep troubleshooting because this view contains wait events for ALL processes/queries running on your instance since last restart. Using command “DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR)” we can reset this view with no restart but on big and stressed instances with many processes and applications running this isn’t a real good…
-
How to script logins with the original password HASH and original SID
A quick tip useful to avoid problems during migrations. This query allow to script instance users with orginal SID and password HASH. This can be very useful to transfer logins to a new or different instance. All informations come from master database catalog. You have to be member of sysadmin role to run correctly this…
-
Massive SQL Server Database Moving Using Detach – Attach: The Complete Procedure
This is a complete configurable metascript, prepared to create detach statements, file move statement and re-attach statements for every database in your instance. You have only to configure the final destination for datafiles and the database list you want to migrate. The final generated script for every database selected will be something like this (remembar…
-
Analyze SQL Server database historical growth: MONTLY size changes
The most simple way to analyze database historical growth is quering database backup catalog. SQL Server catalog stores informations about every single database backup in msdb..backupset. If you don’t have other instruments to collect historical database size this is a good point to start for a capacity planning This time we take the max size…
-
Analyze SQL Server database historical growth: DAILY size changes
The most simple way to analyze database historical growth is quering database backup catalog. SQL Server catalog stores informations about every single database backup in msdb..backupset. If you don’t have other instruments to collect historical database size this is a good point to start for a capacity planning. Remembar only this: – msdb..backupset stores historical…
-
Massive Database Migration between SQL Server instances: the complete procedure v.2.0 *UPDATED*
(05/04/2014) Procedure Upgrades : – Added compresson to reduce bandwith,space necessary and transfer time – Reduced stat value for very large database – Added backup type parameter to choose from FULL,FULL_COPYONLY or DIFFERENTIAL backup (for bigger database migration) – Added Maxtransfersize and Buffercount parameters to improve backup performance (warning – this needs more memory!) –…