Scripting Out the Database / Object Level Permissions in SQL Server

USE Master  -- Use the required database name here
GO
SET NOCOUNT ON;

PRINT 'USE ['+DB_NAME()+']';
PRINT 'GO'

/********************************************************************************/
/**************** Create a new user and map it with login ***********************/
/********************************************************************************/

PRINT '/*************************************************************/'
PRINT '/************** Create User Script ***************************/'
PRINT '/*************************************************************/'

SELECT 'CREATE USER [' + NAME + '] FOR LOGIN [' + NAME + ']'
FROM sys.database_principals
WHERE [Type] IN ('U','S')
AND
[NAME] NOT IN ('dbo','guest','sys','INFORMATION_SCHEMA')

GO
-- Troubleshooting User creation issues
PRINT '/***'+CHAR(10)+
'--Error 15023: User or role <XXXX> is already exists in the database.'+CHAR(10)+
'--Then Execute the below code can fix the issue'+CHAR(10)+
'EXEC sp_change_users_login ''Auto_Fix'',''<Failed User>'''+CHAR(10)+
'GO **/'

/************************************************************************/
/************  Script the User Role Information *************************/
/************************************************************************/

PRINT '/**********************************************************/'
PRINT '/************** Create User-Role Script *******************/'
PRINT '/**********************************************************/'

SELECT 'EXEC sp_AddRoleMember ''' + DBRole.NAME + ''', ''' + DBP.NAME + ''''
FROM sys.database_principals DBP
INNER JOIN sys.database_role_members DBM ON DBM.member_principal_id = DBP.principal_id
INNER JOIN sys.database_principals DBRole ON DBRole.principal_id = DBM.role_principal_id
WHERE DBP.NAME <> 'dbo'

GO

/***************************************************************************/
/************  Script Database Level Permission ****************************/
/***************************************************************************/

PRINT '/*************************************************************/'
PRINT '/************** Database Level Permission ********************/'
PRINT '/*************************************************************/'

SELECT CASE WHEN DBP.state <> 'W' THEN DBP.state_desc ELSE 'GRANT' END
+ SPACE(1) + DBP.permission_name + SPACE(1)
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USR.name) COLLATE database_default
+ CASE WHEN DBP.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END + ';'
FROM sys.database_permissions AS DBP
INNER JOIN sys.database_principals AS USR ON DBP.grantee_principal_id = USR.principal_id
WHERE DBP.major_id = 0 and USR.name <> 'dbo'
ORDER BY DBP.permission_name ASC, DBP.state_desc ASC


/***************************************************************************/
/************  Script Object Level Permission ******************************/
/***************************************************************************/

PRINT '/*************************************************************/'
PRINT '/************** Object Level Permission **********************/'
PRINT '/*************************************************************/'

SELECT CASE WHEN DBP.state <> 'W' THEN DBP.state_desc ELSE 'GRANT' END
+ SPACE(1) + DBP.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(OBJ.schema_id)) + '.' + QUOTENAME(OBJ.name)
+ CASE WHEN CL.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(CL.name) + ')' END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USR.name) COLLATE database_default
+ CASE WHEN DBP.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END + ';'
FROM sys.database_permissions AS DBP
INNER JOIN sys.objects AS OBJ ON DBP.major_id = OBJ.[object_id]
INNER JOIN sys.database_principals AS USR ON DBP.grantee_principal_id = USR.principal_id
LEFT JOIN sys.columns AS CL ON CL.column_id = DBP.minor_id AND CL.[object_id] = DBP.major_id
ORDER BY DBP.permission_name ASC, DBP.state_desc ASC

SET NOCOUNT OFF;

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Here’s the script:

-- ************************************************************************************************************************
SET NOCOUNT ON
-- Scripting Out the Logins To Be Created
SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
      CASE 
     WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = ' 
      + CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END
     ELSE ' FROM WINDOWS WITH'
    END 
    +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]
FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL
  ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
  AND SP.name NOT LIKE '##%##'
  AND SP.name NOT LIKE 'NT AUTHORITY%'
  AND SP.name NOT LIKE 'NT SERVICE%'
  AND SP.name <> ('sa');

-- Scripting Out the Role Membership to Be Added
SELECT 
'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''
' AS [-- Server Roles the Logins Need to be Added --]
FROM master.sys.server_role_members SRM
 JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
 JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S','G','U')
  AND SL.name NOT LIKE '##%##'
  AND SL.name NOT LIKE 'NT AUTHORITY%'
  AND SL.name NOT LIKE 'NT SERVICE%'
  AND SL.name <> ('sa');


-- Scripting out the Permissions to Be Granted
SELECT 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
  THEN SrvPerm.state_desc 
  ELSE 'GRANT' 
 END
    + ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' + 
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
  THEN '' 
  ELSE ' WITH GRANT OPTION' 
 END collate database_default AS [-- Server Level Permissions to Be Granted --] 
FROM sys.server_permissions AS SrvPerm 
 JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id 
WHERE   SP.type IN ( 'S', 'U', 'G' ) 
  AND SP.name NOT LIKE '##%##'
  AND SP.name NOT LIKE 'NT AUTHORITY%'
  AND SP.name NOT LIKE 'NT SERVICE%'
  AND SP.name <> ('sa');

SET NOCOUNT OFF

Upgrade SQL Server Evaluation edition to license Edition of SQL Server

The SQL Server Evaluation Edition is a great way to get a fully functional and free instance of SQL Server for learning and developing solutions. The edition has a built in expiry of 6 months from the time that you install it.

One of my client doesn't have licensed key for SQL Server and they have planned to add key later. To apply licensed key to an existing evaluation edition of SQL Server is different from windows. Follow the below procedure.

In below screenshot, SQL Server is Enterprise evaluation edition.

Upgrade SQL Server Evaluation edition to license Edition of SQL Server

Step 1:

 Mount SQL Server licensed setup file to the server.

 Launch SQL Server "setup.exe", select maintenance tab and select edition upgrade.

Step 2:

Enter the licensed product Key and click next

Step 3:

Continue with running Edition Upgrade Rules

Step 4:

Select the instance name that you want to upgrade

Step 5:

Select upgrade to proceed with upgrade process and close it once successfully upgraded

In below screenshot, SQL Server Edition is successfully upgraded.

Editions Of SQL Server

There are different editions of SQL Server with different features. Which will provide wide range of options to choose from to match our business requirements.

• Enterprise: This is the high-end edition with the full features. It supports systems up to 2 TB RAM. The maximum size of the database is 524 PB.

• Standard: It has less features than Enterprise, but it is a good choice when advanced functions (such as data compression, compressed backups, indexed views, etc.) are not required for the business. It supports systems up to 4 CPU and 64 GB RAM.

• Workgroup: it is suitable for remote offices of a larger company. It supports systems up to 2 CPU and 4 GB RAM.

• Web: it is designed for web applications. It supports 4 CPU without memory limitations.

• Developer: similar to Enterprise, but licensed to only one user for development, testing and demo. It can be easily upgraded to Enterprise without reinstallation.

• Express: free entry-level database. It can utilize only 1 CPU and 1 GB memory, the maximum size of the database is 10 GB.

• Compact: free embedded database for mobile application development. The maximum size of the database is 4 GB.

Log File Architecture in SQL Server

 Whenever any query is processed, the data will be passed to Data file. Below is the process how a query is processed in SQL Server/ Importance of Log File Architecture:

• Database has Data file and Log file.
• The query statement is passed to the buffer cache and log cache.
• The data in the buffer cache is called as Dirty Data or Dirty Blocks.
• Buffer cache contains the dirty data (the updated data corresponding to the query given in the application).
• The process of writing the data from buffer cache to the data files of the database in the form of chuncks is
  called as Checkpoint Process.
• Each chunck contains 512 KB.
• The query is written into the log file from the log cache.
• If any type of failure occurs while writing data to the data file, then the query in the log file is executed at the last commit transaction processed (refer commit process down) and the remaining data is written to the database whenever we start the server.
• This process of writing data to the database after a failure from the log file is called as Recovery.
• Procedure Cache contains the execution plan.
• Context Cache contains the data of the stored procedure.
• Server Level Data Structure contains the Server level information.

Commit Process:

• As soon as commit statement is written to the log file it throws a token to the user that commit is completed successfully (Ex. 1 row affected), this process is called as Commit Process.

What is an execution plan?

Every day, out in the various discussion boards devoted to Microsoft SQL Server, the same types of questions come up again and again: Why is this query running slow? Is my index getting used? Why isn’t my index getting used? Why does this query run faster than this query?. The correct response is probably different in each case, but in order to arrive at the answer you have to ask the same return question in each case: have you looked at the execution plan? An execution plan, simply put, is the result of the query optimizer’s attempt to calculate the most efficient way to implement the request represented by the T-SQL query you submitted.

Execution plans can tell you how a query will be executed, or how a query was executed. They are, therefore, the DBA’s primary means of troubleshooting a poorly performing query. Rather than guess at why a given query is performing thousands of scans, putting your I/O through the roof, you can use the execution plan to identify the exact piece of SQL code that is causing the problem. For example, it may be scanning an entire table-worth of data when, with the proper index, it could simply backpack out only the rows you need. All this and more is displayed in the execution plan.

The aim of this chapter is to enable you to capture actual and estimated execution plans, in either graphical, text or XML for ­mat, and to understand the basics of how to interpret them. In order to do this, we’ll cover the following topics:

• A brief backgrounder on the query optimizer – execution plans are a result of the optimizer’s calculations so it’s useful to know at least a little bit about what the optimizer does, and how it works.
• Actual and Estimated execution plans – what they are and how they differ
• Capturing and interpreting the different visual execution plan formats – we’ll investigate graphical, text and XML execution plans for a very basic SELECT query
• Automating execution plan capture – using the SQL Server Profiler tool

Shrink Operation- Internally what happens?

Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of the file, data pages at end of the file can be deallocated and returned to the file system.

Limitations and Restrictions:

• The database cannot be made smaller than the minimum size of the database. The minimum size is the size specified when the database was originally created, or the last explicit size set by using a file-size-changing operation, such as DBCC SHRINKFILE. 
• 
  
• For example, if a database was originally created with a size of 10 MB and grew to 100 MB, the smallest size the database could be reduced to is 10 MB, even if all the data in the database has been deleted.
• 
  
• You cannot shrink a database while the database is being backed up. Conversely, you cannot backup a database while a shrink operation on the database is in process.

• DBCC SHRINKDATABASE will fail when it encounters an xVelocity memory optimized columnstore index. Work completed before encountering the columnstore index will succeed so the database might be smaller. 
• 
  
• To complete DBCC SHRINKDATABASE, disable all columnstore indexes before executing
•  
• DBCC SHRINKDATABASE, and then rebuild the columnstore indexes.

Recommendations:

• To view the current amount of free (unallocated) space in the database.
• Consider the following information when you plan to shrink a database: 
• A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
• Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
• A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.
• Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.

AlwaysON - Prerequisites -Every SQL DBA must know!

Before implementing your AlwaysOn Availability Group, make sure you have everything in your environment ready to go. There are several prerequisites that need to be done to ensure a successful deployment. These prerequisites focus on your Windows environment, the SQL instances, and the databases to be included in your AlwaysOn group.

Windows Level:

Windows Server 2008 R2. Windows Server 2008R2 is the minimum Windows Server edition allowed for deploying AlwaysOn. However, it is strongly recommended that you use

Windows Server 2012 instead, as there are many issues and headaches associated with 2008R2.

Windows Server Failover Cluster. WSFC must be installed on every replica (primary and secondaries) in the AlwaysOn group. This is done through Server Manager > Add Roles & Features > Add Failover Clustering. And each SQL Server that hosts an availability group replica must reside on separate nodes of a single WSFC cluster.

.Net 3.5.1 or greater. If .net 3.5.1 or greater is not already installed on your primary and secondary replicas, you will need to install this. This can be installed at the same time you install your Windows Failover Cluster (via Server Manager > Add Roles & Features > Add Failover Clustering):

Windows Domain. All replicas in your AlwaysOn group must be in the same windows domain. They must be able to communicate with each other.

No domain controller. None of your replicas can be a domain controller. AlwaysOn groups are not supported on a domain controller.

Windows Firewall. Most likely you will need to adjust your Windows Firewall setting to allow the replicas to communicate with each other.

Sufficient Disk Space. You don’t have to have identical hardware on each replica, but you do need enough disk space to hold all of your databases, and to account for the growth of those databases. As your databases on the primary replica grow, the replicated databases on your secondaries will also grow the same amount. And if you have other databases on your secondary replicas that are not part of your AlwaysOn group, your disk space must account for the size and growth of those databases as well.

Sufficient Resources. Again, you don’t have to have identical hardware on each of your replicas, but you need to have sufficient resources on your secondaries to handle the same workload as your primary. If you’re thinking that “we’ll use our good, powerful server as the primary replica, and use the slower, weaker server as a secondary” with the thought that if we do have to fail over, we will just know up front that the performance will be a little slower while we get the primary server back up and running… well guess what. You’re right. It will be slower performance if you have to fail over. But it will also be slower if you don’t fail over! That’s because the primary sends transactions to the secondary, and with synchronous data replication, the primary must wait for the secondary to harden the log before it can move on to the next task. So your primary will only operate as fast as your slowest secondary. Your AlwaysOn group is only as fast and strong as your weakest link. So be very familiar with your workload, and try and make sure your replicas are equally yoked.

Instance:

SQL Server 2012 or 2014 Enterprise Edition. For testing or development purposes, you can install Developer or Evaluation edition, but for a production environment, you must install Enterprise edition. Standard edition will not work.

Database Collation. Databases in your AlwaysOn group must use the same collation…they cannot differ across replicas.

SQL Server Collation. All replicas in your AlwaysOn group must use the same SQL Server collation.

Active Directory Services. No replicas can run Active Directory services. This is not supported with AlwaysOn.

Database Mirroring Endpoint. Each instance needs a database mirroring endpoint. If you have more than one instance on your server, you will need to create an additional endpoint so the instances can communicate with each other.

SQL Service Account. Your account that runs SQL Services must be a domain account. Do not use the local machine service account. The SQL service account must be able to access every replica, and therefore must be a domain account.

Also, this account must belong to the Administrators Group on each of the replicas.

And this SQL service account needs connect permissions (given through SSMS). Right click on the SQL Service login to open the properties dialog box, go to the Securables page, and make sure the ‘Connect SQL’ Grant box is checked. Do this on all your instances

Databases:

Full Recovery Model. Make sure your databases are in Full Recovery Mode, not Simple or Bulk Logged. Also, these databases must be taken out of any tlog backup maintenance process while the AlwaysOn group is being created.

User database. Databases included in your AlwaysOn group must be user databases. System databases cannot participate in AlwaysOn Availability Groups.

Read/Write database. Read-only databases cannot belong to an AlwaysOn group.

Multi-user database. Databases must be in multi-user mode, they can’t be in single user mode.

Don’t use AUTO_CLOSE. Check the properties of your databases, and make sure this option is set to ‘False’.

DB in only one Availability Group. Databases may only belong to one availability group at a time. You can have more than one AlwaysOn Availability Group on your instance, but databases cannot belong to more than one group.

Not configured for database mirroring. Your databases cannot be enabled for database mirroring. Make sure this feature is not enabled.

Full Backups. Make sure full backups of each of your databases are made prior to installing AlwaysOn.

Allow Remote Connections. This can be done in SSMS either through the Instance properties, or by using sp_configure, which ever you prefer.

EXEC sp_configure ‘remote access’, 1;

GO

RECONFIGURE;

GO

SUSPECT DATABASE in SQL Server

EXEC sp_resetstatus 'DBname';

ALTER DATABASE DBname SET EMERGENCY

DBCC checkdb('DBname')

ALTER DATABASE DBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('DBname', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE DBname SET MULTI_USER

ALTER DATABASE DBname SET SINGLE_USER

DBCC CheckDB ('DBname', REPAIR_REBUILD)

ALTER DATABASE yourDBname SET MULTI_USER

Application is running slow

1. Check with the application team from when it is running slow.

2. Check my pinging the server.

3. Check network latency (contacting with N/W admin).

4. Check improper responsiveness or OS related issue (Wintel team).

5. If nothing is found, we can ask app team, to verify whether the application is running from other machine or not.

6. Check for Blocking.

7. Kill the process with approval  .

Database Issues - Troubleshooting

1. Offline

i. ALTER DATABASE <DBNAME> SET ONLINE

ii. EXEC SP_RESET STATUS

iii. Before doing this make sure that we inform the DB Users that we are going to work on it to make necessary troubleshooting steps.

iv. Reason can be found in SQL Server Logs why/how/who has taken the database offline.

User Unable to connect

Slow response from the DB when user connected

Suspect

DB Corrupt

DB is in Restoring state

SQL Server is running slow

1. Check number of connections(SP_Who2)

2. Check the processor usage not above 80% of utilization

3. Check the memory usage not above 40-45% of utilization

4. Check the Disk utilization using Perfmon

5. Use profiler to check for the users and current SQL activities and jobs running which might be a problem

6. Finally run UPDATE_STATISTICS command to update the indexes 

7. Need to check fragmentation and rebuild or reorg the indexes if it is required.  

Disk drive full-Troubleshooting

1. We will purge the old data or files

2. We will raise request to Wintel Team to add more space

3. If database log file occupied more space will shrink the log file process

Performance issues

1. CPU Utilization We will check the performance of CPU usage of the server in Task Manager

2. Memory We will check memory manager counters

3. Disk IO We will check Disk IO counters

4. Blocking

i. We will identify if blocking is really present or not? 

Select * from sys.sysprocesses where blocked<>0

ii. After identifying the SPID's, find out which SPID is doing what task 

DBCC INPUTBUFFER (SPID1) 

DBCC INPUTBUFFER (SPID2)

iii. Identify which SPID belongs to which application/login/network address 

Select * from sys.sysprocesses where spid=<SPID1>

iv. Priority should be chosen based on Time started, CPU Time, Memory usage, IO Usage 

sp_who2 

v. If confused on the priority immediately escalate to respective application Team Lead/Application Contact Person. Also approach any escalation in your team.

5. Deadlock

1. As per the request we will enable traces and find the deadlock issues. 

DBCC traceon(1204) 

DBCC traceon(1222)

2. Please stop the trace, once monitoring is completed. Else it would lead to performance issues. 

DBCC traceoff(1204) DBCC traceoff(1222)

3. Enable Traces or Profiler so that the deadlock occurrence reason can be found Runàprofiler (2000)/profiler 90 (2005)

4. DBCC TRACEON(1204) – will write error to SQL Server Log`s

5. DBCC TRACEON (1222,-1)- will write error to XML file

6. Finding details through Profiler Profileràlocksàdeadlock chain àdeadlock graphàsel batch completedàsel batch stared àRPC completed. Inform Application team that they have to run the query again so that you can enable the trace and then find the reason for deadlock (if it occurs again). Explain application team that DBA Team can troubleshoot only when trace/profiler are enabled.

7. If trace 1204 is enabled information is written into SQL Server logs. · SPID of both processes Sp_who sp_who2 select *From sys.sysprocess · what queries they were running dbcc inputbuffer(spid) · For how long that deadlock occurred By default 5 seconds detected the deadlock · Which process was killed Dead loc priorities

8. Finally once the information is handy work with Application team in modifying/code enhancements in the queries used for the application.

9. Additional option is to set event "Deadlock Graph" under Locks segment in Profiler and monitor the process, object, query and much more additional information about the deadlock. Profiler -àlocksàdeadlock chainàdeadlock Graph

10. Additional Points: DBA/App Team/Developers can control the deadlock priority using SET DEADLOCK_PRIORITY [LOW|NORMAL|HIGH]

How is the instance performing Configuration Tools >> SQL Server Configuration Manager >> SQL Server Services, and check if SQL Server service status is “Running”. 7. How many logins/sessions are connected SELECT login_name, count(session_id) as session_count FROM sys.dm_exec_sessions GROUP BY login_name 8. What is the current load on the system Open Task Manager, switch to Performance tab, you will find your entire four cores are listed.

1. Run a single task; watch the changes of the CPU Usage History of every core.

 
2. You can find the load may switch between cores.

 
3. Switch to Processes tab, right-click the task’s process, chooses Set Affinity. You will find all four cores are selected.

 
4. You can uncheck three cores, click Ok.

 
5. Switch back to Performance tab, check if the task load switches between cores. You may close and rerun the task to watch the changes.

Sql server stopped

1. Ping the server and check the status

2. Check whether services are running well - services.msc

3. Make sure the TCP end points are configured properly 

4. Firewall could be one of the reasons.

5. Check with the authentication & user privileges.

6. Restart the instance after confirming the Trace Flags.-t3608 (optional)

7. This can have many reasons and right reason can be found only when we look into Event viewer and what has caused SQL Server down.  

SQL_Login_Expired_script (T-SQL Script to find When will a SQL login password expire?)

-- When will a SQL login password expire?

SELECT SL.name AS LoginName
      ,LOGINPROPERTY (SL.name, 'PasswordLastSetTime') AS PasswordLastSetTime
      ,LOGINPROPERTY (SL.name, 'DaysUntilExpiration') AS DaysUntilExpiration
        ,DATEADD(dd, CONVERT(int, LOGINPROPERTY (SL.name, 'DaysUntilExpiration'))
                   , CONVERT(datetime, LOGINPROPERTY (SL.name, 'PasswordLastSetTime'))) AS PasswordExpiration
      ,SL.is_policy_checked AS IsPolicyChecked
      ,LOGINPROPERTY (SL.name, 'IsExpired') AS IsExpired
      ,LOGINPROPERTY (SL.name, 'IsMustChange') AS IsMustChange
      ,LOGINPROPERTY (SL.name, 'IsLocked') AS IsLocked
      ,LOGINPROPERTY (SL.name, 'LockoutTime') AS LockoutTime
      ,LOGINPROPERTY (SL.name, 'BadPasswordCount') AS BadPasswordCount
      ,LOGINPROPERTY (SL.name, 'BadPasswordTime') AS BadPasswordTime
      ,LOGINPROPERTY (SL.name, 'HistoryLength') AS HistoryLength
FROM sys.sql_logins AS SL
WHERE is_expiration_checked = 1
ORDER BY LOGINPROPERTY (SL.name, 'PasswordLastSetTime') DESC

To change all databases to Simple Recovery except system databases

USE MASTER

go
declare
      @isql varchar(2000),
      @dbname varchar(64)
     
      declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')
      open c1
      fetch next from c1 into @dbname
      While @@fetch_status <> -1
            begin     
            select @isql = 'ALTER DATABASE @dbname SET RECOVERY simple'
            select @isql = replace(@isql,'@dbname',@dbname)
            print @isql
            exec(@isql)
           
            fetch next from c1 into @dbname
            end
      close c1
      deallocate c1

ORPHAN USERS FIND AND FIX SCRIPT in SQL Server

Orphan Users find and fix: after the migration need to find and if is there any orphan users need to fix

EXEC sp_change_users_login 'Report'

--Use below code to fix the Orphan User issue

DECLARE @username varchar(25)
DECLARE fixusers CURSOR
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END
CLOSE fixusers
DEALLOCATE fixusers

Unable to Restore the backup files in SQL Server

1. Check whether the Backup is valid or not Restore verifyonly from disk = ‘Backup path’

2. We will check the Base Backup is restored first or not

3. Check the LSN sequence 4. Check user has required permissions

5. Check the disk space

6. We will check basing upon the error message.

Services Failed to start in windows

1. SCM – start instance (run: sqlservermanager.msc)

2. OS services (run: services.msc)

3. SAC – Surface Area Configuration (2005)

4. On the command line: Run – net start service name Find the service name in services.msc

5. Using object explorer (Right click on the instance and try to start)

6. Startup parameters have incorrect file path locations. Right click the instance -> Properties -> General Tab -> startup parameters

7. Need to check whether Services are disabled or not Services.msc

8. We will check basing upon the error message 

Log File full-Troubleshooting

1. Check the log file size by using DBCC sqlperf(logspace), we can increase log size up to 2TB.

2. Purging the old files.

3. Take a Log Backup if database in full recovery model  i. Backup log dbname to disk =’path’

4. Run the checkpoint if database in simple recovery model.

5. Increase the LOG file size or Enable Auto Growth (Make sure it’s not set to Maximum).

6. Add one more T-Log file from  another drive.

7. Shrink the file Use dbname Go DBCC shrinkfile (<transactionlogname>,1).

8. Request windows team to add more space.

9. If the recovery model is Full change to Simple 10. Find the long running transactions and kill after approval.  

DBA Regular usage scripts-SQL DBA

1. On which drive the  database files(mdf & ldf) are located?

use master

select *from sysaltfiles where filename like 'd%'

                      (OR)

select *from master..sysaltfiles where filename like 'd%'

2.How to shrinklog file if it is full?

backup log dbname with no_log

dbcc shrinkfile(2)

3. how to see open transations in a database?

  dbcc opentran

4. HOW TO see if perticlular process is blocked?

Use master

select * from sysprocessess where blocked>0 and spid=57

5. why buffer cache hit ratio <90?

 if the bottlenecks are there

 then the number of pages read in to the cache will vary

 and the ratio of finding the data by the server in the cache will decrease

6 how to find attribute of perticula file?

  Execute spFileDetails 'c:\autoexec.bat'

7. how to see the status of database?

  dbcc showfilestats

8.How to see the  Cpu usage?(if buffer cache hit raio<90)

 select *from sys.sysprocesses order by cpu asc

9.How to find out the info of a specific table on a database?

 select * from sysobjects where name like '%table_name%'

10. What is Undo File? Why it is required?

 Undo file is needed in standby state because while restoring the log backup, uncommited transactions will be recoreded to the undo file and only commited transactions will be written to disk there by making users to read the database. When you restore next tlog backup SQL server will fetch the uncommited transactions from undo file and check with the new tlog backup whether the same is commited or not. If its commited the transactions will be written to disk else it will be stored in undo file until it gets commited or rolledback.

11.How to get the data the from linked server?

 Select * from LinkedServer.DBName.SchemaName.TableName

12.How to find the service pack version?

SELECT SERVERPROPERTY('PRODUCTLEVEL')

13.How to see the collation setting for server level?

SP_HELPSORT

14.How to see log usage info in a database?

select * from  sys.databases where name like '%log_reuse_wait_desc%'

15.How to see the connection info by using DMV'S?

Select * from  sys.dm_exec_sessions

16. How to see the transaction isolation level?

DBCC USEROPTIONS

17.SQL Server 2000 database compatible level to SQL Server 2005

EXEC sp_dbcmptlevel AdventureWorks, 90;

18.How to see the memory ocupation of eache query running under Query Analyzer

select * from sys.dm_exec_query_memory_grants

19.How to clear the data from buffer cashe

DBCC DROPCLEANBUFFERS

20.How to see how much space tempdb is allocated for perticulas task

Select * from sys.dm_db_session_space_usage          

                  OR

sys.dm_db_task_space_usage 

21.The following DMV query can be used to get useful information about the index usage for all objects in all databases

Select * from from sys.dm_db_index_usage_stats 

order by object_id, index_id

22.The following query to list all the schedulers and look at the number of runnable tasks.

 Select * from from 

    sys.dm_os_schedulers where   scheduler_id < 255

23.Which Query is taking more cpu time 

select * from sys.dm_exec_query_stats 

24.How to find whether any active requests are running in parallel for a given session by using the following query.

select * from sys.dm_exec_requests 

25. if sql server installation failed in sql2000 whrere can i see tha failed info?

 sqlstp.log

26. How to see the lock info?

 select * from sys.dm_tran_locks

27. How to open cluadmin from windows 2008 server?

   cluadmin.msc

28. How to backup by using T-sql Query?

backup database DB name to disk = ‘path\full.bak’ with compression,stats=1

backup database DB name to disk = ‘path\diff.bak’ with compression,stats=1

backup log DB name to disk = ‘path\log_trn’ with compression,stats=1

29. How to Restore by using T-sql Query?

Restore database dbname from disk =’path\full.bak’ with move logical name to ‘path.mdf’,move  logical name to ‘path log.ldf’,norecovery,stats=1

Restore database DB name from disk =’path\diff.bak’ with norecovery,stats=1

Restore log DB name from disk = ‘path\log.trn’ with norecovery,stats=1

29.How to find Logical names?

Restore filelistonly from disk = ‘path’

T-SQL – Checking SQL Server Version and Service Packs

Often, you will need to check for SQL Server versions and service packs. Some of the features that you are using are only available from a service pack onward.

Microsoft provides the function server property to query for that kind of information.

SELECT
CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
AS VARCHAR(17)) AS 'Computer physical name',
CAST(SERVERPROPERTY('ServerName')
AS VARCHAR(17)) AS 'Server name',
CAST(SERVERPROPERTY('MachineName')
AS VARCHAR(17)) AS 'Machine name',
CAST(SERVERPROPERTY('Edition')
AS
VARCHAR(25)) AS 'Server edition',
CAST(SERVERPROPERTY('InstanceName')
AS VARCHAR(17)) AS 'Instance name',
CAST(SERVERPROPERTY('LicenseType')
AS VARCHAR(15)) AS 'License type',
CAST(SERVERPROPERTY('ProductVersion')
AS VARCHAR(15)) AS 'Version',
CAST(SERVERPROPERTY('ProductLevel')
AS VARCHAR(15)) AS 'Service pack';

Go

T-SQL – whether the databases are configured for mirroring or not

If you want to check whether the databases are configured for mirroring or not, then below script would be a great help.

SELECT

A.name,
CASE

   WHEN B.mirroring_state is NULL THEN 'Mirroring is not configured'

   ELSE 'Mirroring is configured'

END as MirroringState

FROM

sys.databases A

INNER JOIN sys.database_mirroring B

ON A.database_id=B.database_id

WHERE a.database_id > 4

ORDER BY A.NAME

SQL Server service does not start successfully because of a logon failure

When you restart Microsoft SQL Server or SQL Server Agent, the service may fail to start with the following error message:

Error 1069: The service did not start due to a logon failure.

Cause

This problem occurs because the password for the SQL Server startup service account or the SQL Server Agent startup service account is not correct. This problem can occur when the password for the account is changed but the password information was not updated for the SQL Server service or the SQL Server Agent service.

Workaround

To solve this problem, type the correct password in the Microsoft SQL Server service account on the SQL Server host computer.

To correct the password in Microsoft Windows 2000 Server and Microsoft Windows XP:

• Click Start, point to Settings, and then click Control Panel.
• Double-click Administrative Tools, and then double-click Services.
• Use one of the following steps based on your instance type:

• For a default instance of SQL Server, double-click MSSQLServer, and then click the Log On tab.
• For a named instance of SQL Server, double-click MSSQL$YourInstanceName, and then click the Log On tab. Replace YourInstanceName with the actual name of the SQL Server instance.
• For a default instance of SQL Server Agent, double-click SQLAgent, and then click the Log On tab.
• For a named instance of SQL Server Agent, double-click SQLAgent$YourInstanceName, and then click the Log On tab. Replace YourInstanceName with the actual name of the SQL Server instance.
• Type the correct password in the Password and Confirm password textbox, and then click OK.

• To correct the password in Microsoft Windows NT 4.0:

• Click Start, point to Settings, and then click Control Panel.
• Use one of the following steps based on your instance type:
• For a default instance of SQL Server, double-click MSSQLServer, and then click the Log On tab.
• For a named instance of SQL Server, double-click MSSQL$YourInstanceName, and then click the Log On tab. Replace YourInstanceName with the actual name of the SQL Server instance.
• For a default instance of SQL Server Agent, double-click SQLAgent, and then click the Log On tab.
• For a named instance of SQL Server Agent, double-click SQLAgent$YourInstanceName, and then click the Log On tab. Replace YourInstanceName with the actual name of the SQL Server instance.
• Type the correct password in the Password and Confirm password textbox, and then click OK.
• NOTE: Changing the password through the Services applet allows SQL Server to start successfully; however, if Microsoft Search (Full-Text Service) is installed, it requires a subsequent password change through SQL Enterprise Manager (SEM).