This blog is to learn and share SQL DBA and Azure SQL knowledge among people by Bandaru Ajeyudu & Sudheer Thota
--Delete Bakups older than 1 day in SQL Server
DECLARE @DeleteDate datetime
SET @DeleteDate = DateAdd(day, -1, GetDate())
EXECUTE master.sys.xp_delete_file 0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)
N'C:\Backup\msdb', -- folder path (trailing slash)
N'bak', -- file extension which needs to be deleted (no dot)
@DeleteDate, -- date prior which to delete
1 -- subfolder flag (1 = include files in first subfolder level, 0 = not)
SELECT name, compatibility_level
FROM sys.databases where compatibility_level not in(120) -- SQL Server 2012 compatibility_level is 120
---------------------------------
SELECT name, compatibility_level
FROM sys.databases
---------------------------
/* Start Of Code */
DECLARE @SQL VARCHAR(max) = ''
, @CompLevel int = 120 -- Need to change the compatibility_level as per SQL Server version
SELECT @SQL += 'ALTER DATABASE ' + quotename(NAME) + ' SET COMPATIBILITY_LEVEL = ' + cast(@CompLevel as char (3)) + ';' + CHAR(10) + CHAR(13)
FROM sys.databases
WHERE
--COMPATIBILITY_LEVEL not in(120)
--and
compatibility_level <> @CompLevel
PRINT @SQL
--EXEC (@SQL)
/* End Of Code */
Today one of my friend asked me that she has created a stored procedure in a database, suddenly she has recognized she has forgotten the database in which the procedure was created. We suggested him to use the below query which searches each and every database in the server and returns the database name and the object name that is specified in where clause.
SP_MSFOREACHDB searches the objects in all databases in the server.
Example:
SP_MSFOREACHDB'USE ?select ''?'' Database_Name , Name Object_name from sys.procedures WHERE name LIKE ''USP_Test''use Database_Name;
go
SELECT dPrinc.name AS [Members],dRole.name AS [Database Role Name]
FROM sys.database_role_members AS dRo
JOIN sys.database_principals AS dPrinc
ON dRo.member_principal_id = dPrinc.principal_id
JOIN sys.database_principals AS dRole
ON dRo.role_principal_id = dRole.principal_id;
Update stats:
Updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases, you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.
Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile. We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. The specific tradeoffs depend on your application. UPDATE STATISTICS can use tempdb to sort the sample of rows for building statistics.
Indexing Optimize:
SQL Server Indexes are special data structures associated with tables or views that help speed up the query.
Reorganize or rebuild a fragmented index in SQL Server by using SQL Server Management Studio or Transact-SQL. The SQL Server Database Engine automatically modifies indexes whenever insert, update, or delete operations are made to the underlying data. Over time, these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly, especially scan operations.
You can remedy index fragmentation by reorganizing or rebuilding an index.