by Saleem Hakani | Feb 1, 2021 | Uncategorized
DBA’s and Developers often have a need to query the current state of database file size and growth information. This script allows you to obtain file-growth information from single or all databases. It provides the following information:
1. Local Server Name
2. Database Name
3. Logical File Name
4. Initial File Size
5. File Growth Value/Percentage
6. Any Growth Restrictions
Please create and run the below stored procedure without any parameters to obtain help on how to execute this stored procedure.
CREATE Procedure [dbo].[SQLOPS_FileGrowthREPORT] (@Cmd Varchar(20)=null, @DBName Varchar(255)='ALL') as
SET NOCOUNT ON
--Author: Saleem Hakani (http://sqlcommunity.com)
--Desc: This SPROC provides you with filegrowth information for all databases of your local SQL Server instance.
If (@Cmd is null or @Cmd <> 'Execute')
Begin
Print 'Get file growth details of all databases for all files from your local SQL Server instance.'
Print ' ========================================================================='
Print 'Author: Saleem Hakani (http://sqlcommunity.com)'
Print ' '
Print 'Syntax: Exec SQLOPS_FileGrowthREPORT <@cmd>'
Print '@Cmd can be '
Print ' '
Print 'To obtain help for this stored procedure:'
Print 'Example: Exec SQLOPS_FileGrowthREPORT ''Help'';'
Print ' '
Print 'To obtain file growth information for all data and log files for all databases:'
Print 'Exec SQLOPS_FileGrowthREPORT ''Execute'';'
Print ' '
Return
End
If (@Cmd='Execute' and @DBName='ALL')
Begin
Select
UPPER(@@ServerName) as 'SERVER_NAME',
UPPER(DB_Name(Database_ID)) as 'DATABASE_NAME',
UPPER(Name) as LOGICAL_FILENAME,
SIZE/128 as 'CURRENT/INITIAL_FILESIZE_IN_MB',
(
CASE is_percent_Growth
WHEN '1' THEN convert(Varchar,Growth) +' %'
ELSE Convert(Varchar,Growth/128) +' MB' End) as 'FILE_GROWTH',
(
CASE max_size
WHEN 0 THEN 'RESTRICTED'
WHEN -1 THEN 'UNRESTRICTED'
WHEN 268435456 THEN 'UPTO 2 TB'
ELSE 'RESTRICTED ('+convert(varchar,Max_Size/128)+' MB)'
END
) as 'RESTRICTIONS'
FROM sys.master_files
RETURN
End
If (@Cmd='Execute' and @DBName<>'ALL')
Begin
IF Not Exists (Select Name from Sys.Databases Where Name=@DBName)
Begin
Print 'Database ['+@DBName+'] does not exist. Please enter a valid database name.'
RETURN
End
If Exists (Select Name from Sys.Databases Where Name=@DBName)
Begin
Select
UPPER(@@ServerName) as 'SERVER_NAME',
UPPER(DB_Name(Database_ID)) as 'DATABASE_NAME',
UPPER(Name) as LOGICAL_FILENAME,
SIZE/128 as 'CURRENT/INITIAL_FILESIZE_IN_MB',
(
CASE is_percent_Growth
WHEN '1' THEN convert(Varchar,Growth) +' %'
ELSE Convert(Varchar,Growth/128) +' MB' End) as 'FILE_GROWTH',
(
CASE max_size
WHEN 0 THEN 'RESTRICTED'
WHEN -1 THEN 'UNRESTRICTED'
WHEN 268435456 THEN 'UPTO 2 TB'
ELSE 'RESTRICTED ('+convert(varchar,Max_Size/128)+' MB)'
END
) as 'RESTRICTIONS'
from sys.master_files Where Database_ID=db_ID(@DBName)
--where Max_Size not in (-1, 268435456)
RETURN
END
END
GO
by Saleem Hakani | Feb 1, 2021 | Uncategorized
Setting the database to the right compatibility level is very important as it can have negative impact on the performance and behavior of your databases. I’ve seen many customers upgrade their SQL Servers from older versions to newer but forget to upgrade the compatibility of their databases. This means that even though you have an upgraded version of SQL Server, your database cannot benefit from any new or enhanced features of SQL Server since your database is running on an old compatibility level.
Incompatible database could also lead to inefficient query plans. Therefore, it is important that you upgrade the compatibility of databases right after the upgrade or right after you restore the database from older version to a newer version.
This script can help scan thru all the databases and provide you with a list of current and recommended database compatibility level.
CREATE Procedure [dbo].[SQLOPS_dbCompatibility] (@cmd Varchar(20)=null) as
--Author: Saleem Hakani (Http://sqlcommunity.com)
--Description: This procedure will check for database level compatibility and will report any anomoly.
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
If (@cmd is null or @cmd='Help' or @cmd <> 'Execute')
Begin
Print 'SQLOPS_dbCompatibility: Checks for database compatibility and reports if the database compatibility is not configured correctly'
Print '=================================================================='
Print 'Author: Saleem Hakani (Http://sqlcommunity.com)'
Print ' '
Print 'Syntax: Exec SQLOPS_dbCompatibility <@cmd>;'
Print '@cmd can be ''HELP'' (To get help on this stored procedure) or it can be ''EXECUTE'' (to execute this stored procedure)'
Print ' '
Print 'Example: To get help about this stored procedure'
Print 'Exec SQLOPS_dbCompatibility ''Help'';'
Print ' '
Print 'Example: To execute this stored procedure'
Print 'Exec SQLOPS_dbCompatibility ''Execute'';'
Print ' '
Return
End
If (@Cmd='Execute')
Begin
Declare @SQLVersion int
SELECT @SQLVersion = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);
BEGIN
DECLARE @DB_Compat int
SELECT @DB_Compat = COUNT([compatibility_level]) FROM master.sys.databases (NOLOCK) WHERE [compatibility_level] <> @SQLVersion * 10;
IF @DB_Compat >= 1
BEGIN
Select 'Compatibility_Level' AS [Information], name AS [Database_Name], Ltrim(str([compatibility_level]))+' (Please upgrade to '+ltrim(str(@SQLVersion))+'0)' AS [Compatibility_Level]
FROM master.sys.databases (NOLOCK)
WHERE [compatibility_level] <> @SQLVersion * 10;
END
ELSE
BEGIN
SELECT 'Compatibility level for all databases' AS [Check], '[OK]' AS [Results]
END
END
End
GO
by Saleem Hakani | Feb 1, 2021 | Uncategorized
DBAs and Developers at times may need to detach and attach the databases for various reasons including the following:
1. Moving files to other drives
2. Server migration
2. Upgrades,
3. system maintenance, etc.
This script takes away the headache of remembering the filename or folder location of every single data or log file for every single user databases on your server. It allows you to generate script to detach and attach one or all databases avoiding the need to remember the long hard-coded file path after detaching the database.
CREATE Procedure [dbo].[SQLOPS_AttachDETACH] (@Cmd Varchar(20)=null, @DBName Varchar(50)='ALL') as
Set NOCOUNT ON
--------------------------------------------
--Author: Saleem Hakani (Http://sqlcommunity.com)
--Compatible with SQL Server 2000, 2005, 2008, 2008 R2, 2012, 2014, 2016, 2017 +
--Desc: This procedure can be used to generate detach and attach script for one or all the databases
--Note: This SPROC also takes care of the 16 file limitation with SP_Attach_DB system stored proceure.'
--------------------------------------------
If (@Cmd is null or @Cmd <> 'Execute')
Begin
Print 'HELP for SQLOPS_AttachDETACH'
Print '============================'
Print 'Author: Saleem Hakani (Http://sqlcommunity.com)'
Print ' '
Print 'This procedure can be used to generate detach and attach script for one or all the databases.'
Print 'It also takes care of the 16 files limitation by that SP_attach_db system sproc has.'
Print ' '
Print 'SYNTAX: Exec SQLOPS_AttachDETACH <@cmd>, <@DBName>'
Print '@cmd: Can be either ''Help'' (to get help) or ''Execute'' (to execute this sproc)'
Print '@DBName: Can either be the name of the database or ''ALL'' (default) which means run this on all databases'
Print ' '
Print 'Example: To generate a script for attaching and detaching a specific database (Ex: SQLOPS database)'
Print 'Exec SQLOPS_AttachDETACH ''Execute'',''SQLOPS'';'
Print ' '
Print 'Example: To generate a script for attaching and detaching all user databases from local SQL Server'
Print 'Exec SQLOPS_AttachDETACH ''Execute'',''ALL'''
Print ' '
Print 'To obtain help'
Print 'Exec SQLOPS_AttachDETACH ''HELP'''
Return
End
If (@cmd='Execute' and @DBName='All')
Begin
Exec SP_MSForEachDB 'Exec [SQLOPS_AttachDETACH] ''Execute'',[?]'
RETURN
End
If (@cmd='Execute' and @DBName <> 'All')
Begin
If NOT EXISTS (SELECT NAME from Sys.Databases Where Name=@DBName)
Begin
Print 'Database ['+@DBName+'] does not exist. Please enter a valid database name.'
RETURN
End
If Exists (SELECT NAME from Sys.Databases Where Name=@DBName)
Begin
Declare @a Varchar(8000),@b Varchar(8000),@c Varchar(8000),@d Varchar(8000)
Declare @FileID Int,@str int,@Count1 int
Create Table ##Count1 (Count1 int)
Declare @FileName Varchar(500)
Select @str=0
Print '--------------------------------------------------------------------'
Print ''
Print 'SP_Detach_DB ['+Ltrim(@DBName)+']'
Print 'Go'
Print ''
Print 'Create Database ['+Ltrim(@DBName)+'] ON PRIMARY'
Exec('Insert into ##count1 Select Count(*) from '+@DBName+'..SysFiles')
Select @Count1=Count1 from ##Count1
Exec('Declare CheckTab Cursor For Select FileID,FileName from '+@DBName+'..SysFiles Order by FileID')
Open CheckTab
Fetch Next from CheckTab into @FileID,@FileName
While (@@Fetch_Status=0)
Begin
Select @Str=@str+1
If @Str < @Count1
Begin
Select @b='(FileName='''+Ltrim(Rtrim(@FileName))+'''),'
End
If @Str = @Count1
Begin
Select @b='(FileName='''+Ltrim(Rtrim(@FileName))+''') FOR ATTACH'
End
Print @b
Fetch Next from CheckTab into @FileID,@FileName
End
Print 'Go'
Drop Table ##Count1
Close CheckTab
Deallocate CheckTab
End
RETURN
END
GO
by Saleem Hakani | Feb 1, 2021 | Uncategorized
Index Statistics are important for query optimizer to generate optimal query plans. If indexes have outdated or stale statistics, your query’s performance will suffer. It is important to update the statistics to make sure query optimizer always generates the best and optimal plan.
This script allows you to report and update outdated statistics.
In reporting mode, this script provides the following information:
1. Name of the Database
2. Table Name
3. Index Name
4. Last date when Statistics was Updated
5. Row Count of the Table
6. Number of Rows Changed
7. Percentage of Row Changes Occurred Since Last time Statistics were Updated
8. Script for you to manually execute Update Statistics on individual objects
Create Procedure [dbo].[SQLOPS_UpdateStatistics] (@cmd Varchar(10)=null,@DBName Varchar(255)=null,@Update Varchar(5)='NoFix') as
SET NOCOUNT ON
--Author: Saleem Hakani (Http://sqlcommunity.com)
IF (@cmd is null or @cmd <> 'Execute' or @DBName is null)
Begin
Print 'SQLOPS_UpdateStatistics (Author: Saleem Hakani)'
Print '=========================================='
Print ' '
Print 'This procedure allows you to report and/or update outdated/stale statistics for individual indexes,'
Print 'for all indexes in a databases and for all indexes across all user databases. When generating the report, it provides:'
Print '1. Name of the Database'
Print '2. Table Name'
Print '3. Index Name'
Print '4. Last date when Statistics was Updated'
Print '5. Row Count of the Table'
Print '6. Number of Rows Changed'
Print '7. Percentage of Row Changes Occurred Since Last time Statistics were Updated'
Print '8. Script for you to manually execute Update Statistics on individual objects'
Print ' '
Print 'Syntax: SQLOPS_UpdateStatistics <@Cmd>, <@DBName>, <@Update>'
Print '@Cmd: Can be EXECUTE (To execute this SPROC) or HELP (To obtain this Help)'
Print '@DBName: Can be the name of an individual database or ''ALL'' for all databases'
Print '@Update: Can be ''Fix'' (to Update Statistics) or ''NoFix'' (Just provide the Report)'
Print ' '
Print 'To Get a list of outdated statistics from SQLOPS database:'
Print 'Exec SQLOPS_UpdateStatistics ''Execute'',''SQLOPS'''
Print ' '
Print 'To Get a List and to fix Outdated Statistics from SQLOPS database:'
Print 'Exec SQLOPS_UpdateStatistics ''EXECUTE'',''SQLOPS'',''Fix'''
Print ' '
Print 'To Get a List of Outdated Statistics from all User Databases:'
Print 'Exec SQLOPS_UpdateStatistics ''Execute'',''ALL'''
Print ' '
Print 'To Get a list and to fix Outdated Statistics for all User Databases'
Print 'Exec SQLOPS_UpdateStatistics ''Execute'',''ALL'', ''Fix'''
Return
End
Declare
@DBNameALL Varchar(255),
@SQLStr1 NVarchar(4000),
@SQLStr NVarchar(4000),
@ExedbSTR NVARCHAR(4000)
Declare @DBStats Table
(
DBName Varchar(255),
TableName Varchar(255),
IndexName Varchar(255),
Statistics_Last_Update Varchar(30),
ROW_COUNT BigInt,
ROWS_CHANGED BIGINT,
Percent_Changed Decimal(16,2),
Script Varchar(2000)
)
If (@cmd ='Execute') and (@DBName <> 'ALL')
Begin
IF Not Exists (Select Name from Sys.Databases where Name=@DBName)
Begin
Print 'Database ['+Upper(@DBName)+'] Does Not Exist in the System. Please Re-Enter a Valid Database Name'
Return
End
Set @SQLStr='Use ['+@DBName+'];
SELECT db_Name() as DB_Name
,SYSSCH.name+''.''+SYSTAB.name AS TableName
,SYSIND.name AS IndexName
,ISNULL(Convert(Varchar,STATS_DATE(SYSIND.id,SYSIND.indid)),''DATE NOT FOUND'') AS ''Statistics_Last_Updated''
,SYSIND.rowcnt AS ''RowCount''
,SYSIND.rowmodctr AS ''Rows_Changed''
,CAST((CAST(SYSIND.rowmodctr AS DECIMAL(28,8))/CAST(SYSIND.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) AS ''%_Changed''
,''USE [''+DB_Name()+'']; Update Statistics ''+SYSSCH.NAME+''.''+SYSTAB.Name+'' [''+SYSIND.name+''];'' as ''Script''
FROM sys.sysindexes SYSIND
INNER JOIN sys.tables SYSTAB ON SYSTAB.[object_id] = SYSIND.[id]
INNER JOIN sys.schemas SYSSCH ON SYSSCH.[schema_id] = SYSTAB.[schema_id]
WHERE SYSIND.id > 100
AND SYSIND.indid > 0
AND SYSIND.rowcnt >= 500
AND SYSIND.rowmodctr > 0
ORDER BY 7 DESC'
Insert into @DBStats Exec SP_ExecuteSQL @SQLSTR
End
--If All Databases
If (@cmd='Execute' and @DBName = 'ALL')
Begin
Declare CheckDB Cursor for Select Name from Sys.Databases where Name not in ('Master','Model','MSDB','TempDB') and State=0
Open CheckDB
Fetch Next from CheckDB into @DBNameALL
While (@@FETCH_STATUS=0)
Begin
Set @SQLStr1='Use ['+@DBNameALL+']; SELECT db_Name() as DB_Name
,SYSSCH.name+''.''+SYSTAB.name AS TableName
,SYSIND.name AS IndexName
,ISNULL(Convert(Varchar,STATS_DATE(SYSIND.id,SYSIND.indid)),''DATE NOT FOUND'') AS ''Statistics_Last_Updated''
,SYSIND.rowcnt AS ''RowCount''
,SYSIND.rowmodctr AS ''Rows_Changed''
,CAST((CAST(SYSIND.rowmodctr AS DECIMAL(28,8))/CAST(SYSIND.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) AS ''%_Changed''
,''USE [''+DB_Name()+'']; Update Statistics ''+SYSSCH.NAME+''.''+SYSTAB.Name+'' [''+SYSIND.name+''];'' as ''Script''
FROM sys.sysindexes SYSIND
INNER JOIN sys.tables SYSTAB ON SYSTAB.[object_id] = SYSIND.[id]
INNER JOIN sys.schemas SYSSCH ON SYSSCH.[schema_id] = SYSTAB.[schema_id]
WHERE SYSIND.id > 100
AND SYSIND.indid > 0
AND SYSIND.rowcnt >= 500
AND SYSIND.rowmodctr > 0
ORDER BY 7 DESC'
Insert into @DBStats Exec SP_ExecuteSQL @SQLSTR1
Fetch Next from CheckDB into @DBNameALL
End
Close CheckDB
Deallocate CheckDB
End
If (@Update='Fix')
Begin
If (@DBName<>'ALL')
Begin
Declare CheckDBFix Cursor for Select Script from @DBStats
End
If (@DBName='ALL')
Begin
Declare CheckDBFix Cursor for Select Script from @DBStats
End
Open CheckDBFix
Fetch Next from CheckDBFix into @ExedbSTR
While (@@FETCH_STATUS=0)
Begin
Exec SP_ExecuteSQL @ExedbSTR
Print 'Executed: '+@ExedbSTR
Fetch Next from CheckDBFix into @ExedbSTR
End
Close CheckDBFix
Deallocate CheckDBFix
End
If (@Update <> 'Fix')
Begin
If (Select count(*) from @DBStats) >=1
Begin
Select * from @DBStats order by 7 DESC
Return
End
If (Select count(*) from @DBStats) = 0
Begin
Print 'There are no statistics to be updated.'
Return
End
Return
End
GO
by Saleem Hakani | Feb 1, 2021 | Uncategorized
You are a proud and trusted DBA of your organization who is responsible for maintaining and managing SQL Servers in the production environment. To prevent any unauthorized access to your production environment, you have decided to perform the following steps that are kind of best practices to secure your company’s SQL Servers from any unauthorized access:
- You have removed any and all built-in administrators account from SQL Server logins
- You have removed all users (Except SA) that were part of SysAdmin server role (including any Windows accounts and SQL Server logins)
- You have set an extremely complex SA password which may be hard for others to guess or remember
For day-to-day operations on SQL Server, you use your domain user account which has DBO permissions on user databases but doesn’t have SYSADMIN privileges on the system. You have not documented the SA password anywhere to prevent others from knowing the SA password. (Also, its not a good practice to document the password)
Since you have set the SA password to be complex and you have been using your domain account and not the SA account for all your daily database related activity on SQL Server, the UNTHINKABLE has happened you forgot your SQL Servers SA password.
You are the only person who knew the SA password in your team and now you don’t remember what it was and you need to make some server level configuration changes to your production SQL Servers.
What will you do now?
- You will try logging in as SA with all possible passwords you have in your mind.
- You will look for the SA password on your computer hard-drive or in your emails (If you had stored it in some file which is a bad practice)
- Try to restore MASTER database from database backup. However, this will not help because you will run in to the same issue as you don’t remember the SA password.
- Rebuild Master database. This may not help as you will lose all system/server level configurations and settings including logins, permissions and any server level objects.
- Re-install SQL Server and attach all user databases. This may not work as you may experience same issues that you would experience with #4.
- All your attempts to login in to the system using SA password have failed and now it is time for you to call Microsoft CSS (Microsoft Customer Support Services)
Hacking SQL Server using a Backdoor
Many users in the community and in different events have asked me about this and Id like to share with you a backdoor to SQL Server which may help you gain SYSADMIN access to your production SQL Servers. However, that would mean your Windows account will need to be a member of the local administrators group on Windows Servers where SQL Server Services are running.
Important: SQL Server allows any member of Local Administrators group to connect to SQL Server with SYSADMIN privileges.
How to take control of SQL Server when SA is lost:
Step 1: Start the SQL Server instance using single user mode from command prompt by launching the command prompt as an administrator. (Note: You can also start SQL Server 2012 using minimal configuration which will also put SQL Server in single user mode)Step 2
Step 2: From the command prompt (Run as Administrator) type: SQLServr.exe “m (or SQLServr.exe “f) and let the SQL Server database engine start. Make sure you do not close this command prompt window. You can locate SQLServr.exe in the Binn folder of your environmental path. If you don’t have SQL Server Binn folder in your environmental path, you can always navigate to the Binn folder of your SQL Server machine.
Usually the Binn folder is located @ C:\Program Files\Microsoft SQL Server\MSSQL<Ver>.MSSQLSERVER\MSSQL\Binn>
Step 3: Once SQL Server service has been started in single user mode or with minimal configuration, you can now open up another command line window as an administrator and use SQLCMD command from command prompt to connect to SQL Server instance:
-- Login from Command Prompt using SQLCMD to your
SQLCMD -S SALEEMHAKANI
You will now be logged in to SQL Server. Keep in mind that you are now logged in as an Admin on SALEEMHAKANI SQL Server instance.
Step 4: Once you are logged into the SQL Server using SQLCMD from the command prompt, you have the option of creating a new account and granting server level permission. Let’s now create a new SQL login called SALEEM_SQL and then add this login to SysAdmin server role.
-- Create new SQL Login
CREATE LOGIN SALEEM_SQL WITH PASSWORD='$@L649$@m'
GO
Step 5: Once the new login SALEEM_SQL has been created, lets now add this login to System Admin server role on SQL Server.
-- Add Login to SYSADMIN server role in SQL Server
SP_ADDSRVROLEMEMBER 'SALEEM_SQL','SYSADMIN'
GO
Step 6: Once the above steps are successfully performed, the next step is to stop and start SQL Server services using regular startup options. (This time you will not need “f or “m)
Step 7: Log in to the SQL Server management studio or from the command prompt using SALEEM_SQL account and its respective password.
Step 8: You now have system admin access to your SQL Server instance. You should immediately reset the SA password and take control of your production SQL Servers.
Recent Comments