Tag: SQL Server
-
AlwaysOn : Manually add a new replica node using a custom backup path with custom credentials
SQL Server Management Studio’s AlwaysOn Availability Groups wizard covers most common scenarios well — but it has a significant limitation: it cannot impersonate a custom user to access a remote backup path with non-default credentials. When your backup share requires a specific domain account or a custom username/password combination, you need to perform the initial…
-
Monitoring Wait Events of a single Session or Query in SQL Server
When troubleshooting a slow query or a specific application session, the instance-level wait statistics in sys.dm_os_wait_stats are too coarse: they aggregate wait events from all processes since the last restart. Resetting the view with DBCC SQLPERF is also problematic on busy instances where hundreds of other sessions generate noise. SQL Server 2008’s Extended Events framework…
-
How to script logins with the original password HASH and original SID
When migrating SQL Server instances, one of the most error-prone steps is transferring SQL logins to the destination. Simply scripting a login with CREATE LOGIN … WITH PASSWORD won’t preserve the original password hash or the original SID — and mismatched SIDs between instance logins and database users will break application connectivity even if the…
-
Massive SQL Server Database Moving Using Detach – Attach: The Complete Procedure
Moving SQL Server databases to a new volume — whether to rebalance I/O across storage, migrate to faster disks, or reclaim space — requires a methodical approach to avoid downtime surprises. The detach/attach method is the fastest option when you can afford a brief offline window: no backup involved, no network transfer overhead. This metascript…
-
Analyze SQL Server database historical growth: MONTLY size changes
When the daily backup history is too granular for a high-level capacity planning overview, a monthly view gives a cleaner picture of long-term growth trends. This query aggregates backup data from msdb..backupset by month, taking the peak size reached in each month to calculate the net monthly change. This complements the daily report: use the…
-
Analyze SQL Server database historical growth: DAILY size changes
Capacity planning starts with understanding how your databases grow over time. The simplest source of historical size data in SQL Server is the backup catalog: msdb..backupset records information about every backup taken on the instance. If you don’t have a dedicated monitoring tool, this is a reliable starting point. Two important caveats before using this…
-
Massive Database Migration between SQL Server instances: the complete procedure v.2.0 *UPDATED*
A complete T-SQL based procedure to migrate SQL Server instances — including logins, databases, and file relocations — with no external tools required. Changelog (05/04/2014) Added compression to reduce bandwidth, space and transfer time Reduced stat value for very large databases Added backup type parameter: FULL, FULL_COPYONLY or DIFFERENTIAL Added Maxtransfersize and Buffercount parameters to…