Re-create Index After Table Modification on Large Table
Example Stats: 85 million records = 4 to 11 minutes
Running out of disk space when updating a large table
When updating a large table using transaction, make sure there is enough space for the log file. This includes … BEGIN TRANSACTION Alter column in a table Create index Tips … Shrink database data and log files beforehand Break up the query into multiple queries Adding more space to the drive where the log file... » read more
Transaction Log is Full
Error: The transaction log for database ‘xxxxx’ is full due to ‘ACTIVE_TRANSACTION’. Fix: Backup the transaction log. Set database recovery model from “Full” to “Simple”. If database setup for Always On, make sure Always On is working and synchronizing on all secondary database servers. Backup Transaction Log If you don’t care about the transaction log... » read more
SQL Server Job Overlap
The SQL Server agent checks whether the job is already running before starting a new iteration. If you have long running job and its schedule comes up, it would be skipped until the next interval.
Transaction Log for TempDB is Full
Issue: The transaction log for database ‘tempdb’ is full due to ‘ACTIVE_TRANSACTION’ Fix: Restart server to clear up tempdb. Tempdb database is part of SQL Server System database and gets created every time SQL Server Service starts. Tempdb stores temporary operations (like sorting and grouping data output etc) and tables, it stores lots of information in... » read more
Log Shipping vs Always On
AlwaysOn high availability group, HAG, is easier to maintain than log shipping. An AlwaysOn HAG gives you the flexibility of being able to fail over back and forth between your AlwaysOn replicas. Now lets take a look at Log Shipping. Without doing a cut and paste from the Microsoft books online there are five steps... » read more
Always On and SQL Service Account
Account Setup: Use the same account to run all SQL Server service for all node in the cluster. Account has Administrator permissions on all servers in node. Set in “Local Users and Groups”. Account has sysadmin SQL permission on all servers in node. Set in “Local Security Policy”. Account has log on as service policy... » read more
SQL Server Service Account
AlwaysOn Cluster Setup: Use the same account to run all SQL Server service for all node in the cluster. Account has Administrator permissions on all servers in node. Account has sysadmin SQL permission on all servers in node. Account has log on as service policy on all servers in node. Note: Always use SQL Server... » read more
Database States
Database State Definitions The following table defines the database states. State Definition ONLINE Database is available for access. The primary filegroup is online, although the undo phase of recovery may not have been completed. OFFLINE Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken.... » read more
Set Database State
Example The set state command should be a fast process. If it takes a while, stop the process, restart server and SQL server, and then try again. <db_state_option> Controls the state of the database. OFFLINEThe database is closed, shut down cleanly, and marked offline. The database can’t be modified while it’s offline. ONLINEThe database is... » read more