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
Create failed for Availability Group Listener, Error: 41066, 19471
Error: Cannot bring the Windows server fail over cluster (WSFC) resources online. (Error Code 5942). The WSFC service may not be running or may not be accessible in its currents states, or the WSFC resources may not be in a state that could accept the request. For information about this error code see “system error... » read more
Adding Listener to Availability Group
Need the following… Host name of Listener, host name need to be setup first in AD Port, default to 1433 Network Mode, use Static IP IP Address, set to specific IP Address Note: For Network Mode, IP Address/Subnet are automatically detected using DHCP option. If you have a specific IP address, use Static IP option.... » read more
Database Stuck in Recovery Pending Mode
Cause: When your database partition is full, the Microsoft SQL server sets the status of the database that can no longer grow to “Recovery Pending”. This status means that SQL can’t open the database and can’t lock the database files. This status is comparable to a database in offline mode. Another cause is with problem... » read more